github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/testutils/reduce/reducesql/testdata/ungrouped-column (about)

     1  contains
     2  subquery uses ungrouped column "col1" from outer query
     3  ----
     4  
     5  reduce
     6  CREATE TABLE table0 (col0 STRING NOT NULL, col1 FLOAT4 NULL, col2 REGTYPE NULL, col3 INT8 NOT NULL, col4 BIT(44), col5 TIME NOT NULL, FAMILY fam0 (col2), FAMILY fam1 (col4), FAMILY fam2 (col5), FAMILY fam3 (col0), FAMILY fam4 (col3), FAMILY fam5 (col1), PRIMARY KEY (col2));
     7  
     8  CREATE TABLE table1 (col0 DECIMAL NOT NULL, col1 BYTES NULL, col2 REGPROCEDURE NOT NULL, col3 UUID NOT NULL, col4 REGPROC NULL, col5 INT4 NULL, col6 CHAR, col7 REGTYPE, FAMILY fam0 (col1), FAMILY fam1 (col2, col0), FAMILY fam2 (col6, col5), FAMILY fam3 (col4), FAMILY fam4 (col7, col3), PRIMARY KEY (col1 DESC), UNIQUE (col0 ASC, col3 ASC, col6, col2 DESC, col4 ASC, col7), UNIQUE (col7 ASC, col3 DESC, col1, col5, col0 ASC, col4 DESC, col2, col6 DESC), UNIQUE (col3, col7, col4 ASC, col0, col5 ASC, col1), INDEX (col2, col5 ASC, col0 DESC), UNIQUE (col7 ASC, col6), INDEX (col0 ASC, col5 ASC, col1 ASC, col2 ASC, col6 DESC, col7 DESC), UNIQUE (col1, col5, col3 DESC), UNIQUE (col4 DESC, col0 DESC, col5 DESC, col2));
     9  
    10  WITH
    11  	with_273 (col_3485, col_3486, col_3487, col_3488, col_3489, col_3490, col_3491, col_3492, col_3493)
    12  		AS (
    13  			SELECT
    14  				(-6623365040095722935):::INT8 AS col_3485,
    15  				false AS col_3486,
    16  				tab_1229.col1 AS col_3487,
    17  				tab_1229.col6 AS col_3488,
    18  				'1993-06-15':::DATE AS col_3489,
    19  				'`rV':::STRING AS col_3490,
    20  				tab_1229.col4 AS col_3491,
    21  				B'0110011001100' AS col_3492,
    22  				tab_1229.col0 AS col_3493
    23  			FROM
    24  				(
    25  					SELECT
    26  						tab_1222.col5 AS col_3477,
    27  						tab_1222.col4 AS col_3478,
    28  						max(tab_1222.col3::UUID)::UUID AS col_3479,
    29  						stddev(tab_1222.col5::INT8)::DECIMAL AS col_3480
    30  					FROM
    31  						defaultdb.public.table1 AS tab_1222
    32  					WHERE
    33  						false
    34  					GROUP BY
    35  						tab_1222.col0, tab_1222.col4, tab_1222.col5, tab_1222.col3
    36  					HAVING
    37  						inet_same_family(((SELECT set_masklen('4ac:ded4:393a:a371:7690:9d0f:4817:3371/43':::INET::INET, tab_1226.col5::INT8)::INET AS col_3476 FROM defaultdb.public.table1 AS tab_1223 RIGHT JOIN (SELECT tab_1222.col1 AS col_3470, tab_1222.col0 AS col_3471, tab_1222.col3 AS col_3472 LIMIT 5:::INT8) AS tab_1224 (col_3473, col_3474, col_3475) JOIN defaultdb.public.table0 AS tab_1225 RIGHT JOIN defaultdb.public.table1 AS tab_1226 ON NULL FULL JOIN defaultdb.public.table1 AS tab_1227 ON false ON similar_to_escape(tab_1222.col6::STRING, NULL::STRING, tab_1222.col6::STRING)::BOOL ON inet_contains_or_equals(set_masklen('198f:60f5:287a:8163:c091:2a95:afdc:ae8b/108':::INET::INET, (-4475677368810664623):::INT8::INT8)::INET::INET, '6d38:61ce:1af7:9283:cf0d:beb2:23e0:d7f/109':::INET::INET)::BOOL ORDER BY tab_1226.col7 DESC LIMIT 1:::INT8)::INET - tab_1222.col5::INT8)::INET::INET, NULL::INET)::BOOL
    38  				)
    39  					AS tab_1228 (col_3481, col_3482, col_3483, col_3484),
    40  				defaultdb.public.table1 AS tab_1229,
    41  				defaultdb.public.table1 AS tab_1230
    42  		)
    43  SELECT
    44  	'c':::STRING AS col_3494,
    45  	e'\x00':::STRING AS col_3495,
    46  	tab_1232.col2 AS col_3496,
    47  	tab_1232.col3 AS col_3497,
    48  	3.4028234663852886e+38:::FLOAT8 AS col_3498,
    49  	NULL AS col_3499
    50  FROM
    51  	defaultdb.public.table1 AS tab_1231,
    52  	defaultdb.public.table0 AS tab_1232,
    53  	with_273,
    54  	defaultdb.public.table0 AS tab_1233
    55  WHERE
    56  	with_273.col_3486
    57  ORDER BY
    58  	tab_1233.col0 DESC, tab_1232.col4 ASC, with_273.col_3490 ASC
    59  LIMIT
    60  	23:::INT8;
    61  ----
    62  ----
    63  CREATE TABLE table1 (col1 BYTES, col5 INT4);
    64  
    65  SELECT
    66  	NULL
    67  FROM
    68  	table1 AS tab_1222
    69  HAVING
    70  	inet_same_family(
    71  		(
    72  			SELECT
    73  				NULL
    74  			FROM
    75  				(SELECT tab_1222.col1)
    76  					AS tab_1224 (col_3473)
    77  		)::INET
    78  		- tab_1222.col5,
    79  		NULL::INET
    80  	);
    81  ----
    82  ----