|
||||
0001 -- 0002 -- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group 0003 -- 0004 -- 0005 -- JOIN 0006 -- Test JOIN clauses 0007 -- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/join.sql 0008 -- 0009 0010 -- There are 2 dimensions we want to test 0011 -- 1. run with broadcast hash join, sort merge join or shuffle hash join. 0012 -- 2. run with whole-stage-codegen, operator codegen or no codegen. 0013 0014 --CONFIG_DIM1 spark.sql.autoBroadcastJoinThreshold=10485760 0015 --CONFIG_DIM1 spark.sql.autoBroadcastJoinThreshold=-1,spark.sql.join.preferSortMergeJoin=true 0016 --CONFIG_DIM1 spark.sql.autoBroadcastJoinThreshold=-1,spark.sql.join.preferSortMergeJoin=false 0017 0018 --CONFIG_DIM2 spark.sql.codegen.wholeStage=true 0019 --CONFIG_DIM2 spark.sql.codegen.wholeStage=false,spark.sql.codegen.factoryMode=CODEGEN_ONLY 0020 --CONFIG_DIM2 spark.sql.codegen.wholeStage=false,spark.sql.codegen.factoryMode=NO_CODEGEN 0021 0022 CREATE OR REPLACE TEMPORARY VIEW INT4_TBL AS SELECT * FROM 0023 (VALUES (0), (123456), (-123456), (2147483647), (-2147483647)) 0024 AS v(f1); 0025 CREATE OR REPLACE TEMPORARY VIEW INT8_TBL AS SELECT * FROM 0026 (VALUES 0027 (123, 456), 0028 (123, 4567890123456789), 0029 (4567890123456789, 123), 0030 (4567890123456789, 4567890123456789), 0031 (4567890123456789, -4567890123456789)) 0032 AS v(q1, q2); 0033 CREATE OR REPLACE TEMPORARY VIEW FLOAT8_TBL AS SELECT * FROM 0034 (VALUES (0.0), (1004.30), (-34.84), 0035 (cast('1.2345678901234e+200' as double)), (cast('1.2345678901234e-200' as double))) 0036 AS v(f1); 0037 CREATE OR REPLACE TEMPORARY VIEW TEXT_TBL AS SELECT * FROM 0038 (VALUES ('doh!'), ('hi de ho neighbor')) 0039 AS v(f1); 0040 CREATE OR REPLACE TEMPORARY VIEW tenk2 AS SELECT * FROM tenk1; 0041 0042 CREATE TABLE J1_TBL ( 0043 i integer, 0044 j integer, 0045 t string 0046 ) USING parquet; 0047 0048 CREATE TABLE J2_TBL ( 0049 i integer, 0050 k integer 0051 ) USING parquet; 0052 0053 0054 INSERT INTO J1_TBL VALUES (1, 4, 'one'); 0055 INSERT INTO J1_TBL VALUES (2, 3, 'two'); 0056 INSERT INTO J1_TBL VALUES (3, 2, 'three'); 0057 INSERT INTO J1_TBL VALUES (4, 1, 'four'); 0058 INSERT INTO J1_TBL VALUES (5, 0, 'five'); 0059 INSERT INTO J1_TBL VALUES (6, 6, 'six'); 0060 INSERT INTO J1_TBL VALUES (7, 7, 'seven'); 0061 INSERT INTO J1_TBL VALUES (8, 8, 'eight'); 0062 INSERT INTO J1_TBL VALUES (0, NULL, 'zero'); 0063 INSERT INTO J1_TBL VALUES (NULL, NULL, 'null'); 0064 INSERT INTO J1_TBL VALUES (NULL, 0, 'zero'); 0065 0066 INSERT INTO J2_TBL VALUES (1, -1); 0067 INSERT INTO J2_TBL VALUES (2, 2); 0068 INSERT INTO J2_TBL VALUES (3, -3); 0069 INSERT INTO J2_TBL VALUES (2, 4); 0070 INSERT INTO J2_TBL VALUES (5, -5); 0071 INSERT INTO J2_TBL VALUES (5, -5); 0072 INSERT INTO J2_TBL VALUES (0, NULL); 0073 INSERT INTO J2_TBL VALUES (NULL, NULL); 0074 INSERT INTO J2_TBL VALUES (NULL, 0); 0075 0076 -- [SPARK-20856] Do not need onerow because it only used for test statement using nested joins 0077 -- useful in some tests below 0078 -- create temp table onerow(); 0079 -- insert into onerow default values; 0080 -- analyze onerow; 0081 0082 0083 -- 0084 -- CORRELATION NAMES 0085 -- Make sure that table/column aliases are supported 0086 -- before diving into more complex join syntax. 0087 -- 0088 0089 SELECT '' AS `xxx`, * 0090 FROM J1_TBL AS tx; 0091 0092 SELECT '' AS `xxx`, * 0093 FROM J1_TBL tx; 0094 0095 SELECT '' AS `xxx`, * 0096 FROM J1_TBL AS t1 (a, b, c); 0097 0098 SELECT '' AS `xxx`, * 0099 FROM J1_TBL t1 (a, b, c); 0100 0101 SELECT '' AS `xxx`, * 0102 FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e); 0103 0104 -- [SPARK-28377] Fully support correlation names in the FROM clause 0105 -- SELECT '' AS "xxx", t1.a, t2.e 0106 -- FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e) 0107 -- WHERE t1.a = t2.d; 0108 0109 0110 -- 0111 -- CROSS JOIN 0112 -- Qualifications are not allowed on cross joins, 0113 -- which degenerate into a standard unqualified inner join. 0114 -- 0115 0116 SELECT '' AS `xxx`, * 0117 FROM J1_TBL CROSS JOIN J2_TBL; 0118 0119 -- ambiguous column 0120 SELECT '' AS `xxx`, i, k, t 0121 FROM J1_TBL CROSS JOIN J2_TBL; 0122 0123 -- resolve previous ambiguity by specifying the table name 0124 SELECT '' AS `xxx`, t1.i, k, t 0125 FROM J1_TBL t1 CROSS JOIN J2_TBL t2; 0126 0127 SELECT '' AS `xxx`, ii, tt, kk 0128 FROM (J1_TBL CROSS JOIN J2_TBL) 0129 AS tx (ii, jj, tt, ii2, kk); 0130 0131 -- [SPARK-28377] Fully support correlation names in the FROM clause 0132 -- SELECT '' AS `xxx`, tx.ii, tx.jj, tx.kk 0133 -- FROM (J1_TBL t1 (a, b, c) CROSS JOIN J2_TBL t2 (d, e)) 0134 -- AS tx (ii, jj, tt, ii2, kk); 0135 0136 SELECT '' AS `xxx`, * 0137 FROM J1_TBL CROSS JOIN J2_TBL a CROSS JOIN J2_TBL b; 0138 0139 0140 -- 0141 -- 0142 -- Inner joins (equi-joins) 0143 -- 0144 -- 0145 0146 -- 0147 -- Inner joins (equi-joins) with USING clause 0148 -- The USING syntax changes the shape of the resulting table 0149 -- by including a column in the USING clause only once in the result. 0150 -- 0151 0152 -- Inner equi-join on specified column 0153 SELECT '' AS `xxx`, * 0154 FROM J1_TBL INNER JOIN J2_TBL USING (i); 0155 0156 -- Same as above, slightly different syntax 0157 SELECT '' AS `xxx`, * 0158 FROM J1_TBL JOIN J2_TBL USING (i); 0159 0160 SELECT '' AS `xxx`, * 0161 FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, d) USING (a) 0162 ORDER BY a, d; 0163 0164 -- [SPARK-28377] Fully support correlation names in the FROM clause 0165 -- SELECT '' AS `xxx`, * 0166 -- FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b) 0167 -- ORDER BY b, t1.a; 0168 0169 0170 -- 0171 -- NATURAL JOIN 0172 -- Inner equi-join on all columns with the same name 0173 -- 0174 0175 SELECT '' AS `xxx`, * 0176 FROM J1_TBL NATURAL JOIN J2_TBL; 0177 0178 SELECT '' AS `xxx`, * 0179 FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d); 0180 0181 SELECT '' AS `xxx`, * 0182 FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a); 0183 0184 -- [SPARK-28377] Fully support correlation names in the FROM clause 0185 -- mismatch number of columns 0186 -- currently, Postgres will fill in with underlying names 0187 -- SELECT '' AS `xxx`, * 0188 -- FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a); 0189 0190 0191 -- 0192 -- Inner joins (equi-joins) 0193 -- 0194 0195 SELECT '' AS `xxx`, * 0196 FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i); 0197 0198 SELECT '' AS `xxx`, * 0199 FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k); 0200 0201 0202 -- 0203 -- Non-equi-joins 0204 -- 0205 0206 SELECT '' AS `xxx`, * 0207 FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i <= J2_TBL.k); 0208 0209 0210 -- 0211 -- Outer joins 0212 -- Note that OUTER is a noise word 0213 -- 0214 0215 SELECT '' AS `xxx`, * 0216 FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i) 0217 ORDER BY i, k, t; 0218 0219 SELECT '' AS `xxx`, * 0220 FROM J1_TBL LEFT JOIN J2_TBL USING (i) 0221 ORDER BY i, k, t; 0222 0223 SELECT '' AS `xxx`, * 0224 FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i); 0225 0226 SELECT '' AS `xxx`, * 0227 FROM J1_TBL RIGHT JOIN J2_TBL USING (i); 0228 0229 SELECT '' AS `xxx`, * 0230 FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i) 0231 ORDER BY i, k, t; 0232 0233 SELECT '' AS `xxx`, * 0234 FROM J1_TBL FULL JOIN J2_TBL USING (i) 0235 ORDER BY i, k, t; 0236 0237 SELECT '' AS `xxx`, * 0238 FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1); 0239 0240 SELECT '' AS `xxx`, * 0241 FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1); 0242 0243 -- 0244 -- semijoin selectivity for <> 0245 -- 0246 -- explain (costs off) 0247 -- select * from int4_tbl i4, tenk1 a 0248 -- where exists(select * from tenk1 b 0249 -- where a.twothousand = b.twothousand and a.fivethous <> b.fivethous) 0250 -- and i4.f1 = a.tenthous; 0251 0252 0253 -- 0254 -- More complicated constructs 0255 -- 0256 0257 -- 0258 -- Multiway full join 0259 -- 0260 0261 CREATE TABLE t1 (name STRING, n INTEGER) USING parquet; 0262 CREATE TABLE t2 (name STRING, n INTEGER) USING parquet; 0263 CREATE TABLE t3 (name STRING, n INTEGER) USING parquet; 0264 0265 INSERT INTO t1 VALUES ( 'bb', 11 ); 0266 INSERT INTO t2 VALUES ( 'bb', 12 ); 0267 INSERT INTO t2 VALUES ( 'cc', 22 ); 0268 INSERT INTO t2 VALUES ( 'ee', 42 ); 0269 INSERT INTO t3 VALUES ( 'bb', 13 ); 0270 INSERT INTO t3 VALUES ( 'cc', 23 ); 0271 INSERT INTO t3 VALUES ( 'dd', 33 ); 0272 0273 SELECT * FROM t1 FULL JOIN t2 USING (name) FULL JOIN t3 USING (name); 0274 0275 -- 0276 -- Test interactions of join syntax and subqueries 0277 -- 0278 0279 -- Basic cases (we expect planner to pull up the subquery here) 0280 SELECT * FROM 0281 (SELECT * FROM t2) as s2 0282 INNER JOIN 0283 (SELECT * FROM t3) s3 0284 USING (name); 0285 0286 SELECT * FROM 0287 (SELECT * FROM t2) as s2 0288 LEFT JOIN 0289 (SELECT * FROM t3) s3 0290 USING (name); 0291 0292 SELECT * FROM 0293 (SELECT * FROM t2) as s2 0294 FULL JOIN 0295 (SELECT * FROM t3) s3 0296 USING (name); 0297 0298 -- Cases with non-nullable expressions in subquery results; 0299 -- make sure these go to null as expected 0300 SELECT * FROM 0301 (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 0302 NATURAL INNER JOIN 0303 (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; 0304 0305 SELECT * FROM 0306 (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 0307 NATURAL LEFT JOIN 0308 (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; 0309 0310 SELECT * FROM 0311 (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 0312 NATURAL FULL JOIN 0313 (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; 0314 0315 SELECT * FROM 0316 (SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1 0317 NATURAL INNER JOIN 0318 (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 0319 NATURAL INNER JOIN 0320 (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; 0321 0322 SELECT * FROM 0323 (SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1 0324 NATURAL FULL JOIN 0325 (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 0326 NATURAL FULL JOIN 0327 (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; 0328 0329 SELECT * FROM 0330 (SELECT name, n as s1_n FROM t1) as s1 0331 NATURAL FULL JOIN 0332 (SELECT * FROM 0333 (SELECT name, n as s2_n FROM t2) as s2 0334 NATURAL FULL JOIN 0335 (SELECT name, n as s3_n FROM t3) as s3 0336 ) ss2; 0337 0338 SELECT * FROM 0339 (SELECT name, n as s1_n FROM t1) as s1 0340 NATURAL FULL JOIN 0341 (SELECT * FROM 0342 (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 0343 NATURAL FULL JOIN 0344 (SELECT name, n as s3_n FROM t3) as s3 0345 ) ss2; 0346 0347 -- Constants as join keys can also be problematic 0348 SELECT * FROM 0349 (SELECT name, n as s1_n FROM t1) as s1 0350 FULL JOIN 0351 (SELECT name, 2 as s2_n FROM t2) as s2 0352 ON (s1_n = s2_n); 0353 0354 0355 -- Test for propagation of nullability constraints into sub-joins 0356 0357 create or replace temporary view x as select * from 0358 (values (1,11), (2,22), (3,null), (4,44), (5,null)) 0359 as v(x1, x2); 0360 0361 create or replace temporary view y as select * from 0362 (values (1,111), (2,222), (3,333), (4,null)) 0363 as v(y1, y2); 0364 0365 select * from x; 0366 select * from y; 0367 0368 select * from x left join y on (x1 = y1 and x2 is not null); 0369 select * from x left join y on (x1 = y1 and y2 is not null); 0370 0371 select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) 0372 on (x1 = xx1); 0373 select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) 0374 on (x1 = xx1 and x2 is not null); 0375 select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) 0376 on (x1 = xx1 and y2 is not null); 0377 select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) 0378 on (x1 = xx1 and xx2 is not null); 0379 -- these should NOT give the same answers as above 0380 select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) 0381 on (x1 = xx1) where (x2 is not null); 0382 select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) 0383 on (x1 = xx1) where (y2 is not null); 0384 select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) 0385 on (x1 = xx1) where (xx2 is not null); 0386 0387 -- 0388 -- regression test: check for bug with propagation of implied equality 0389 -- to outside an IN 0390 -- 0391 select count(*) from tenk1 a where unique1 in 0392 (select unique1 from tenk1 b join tenk1 c using (unique1) 0393 where b.unique2 = 42); 0394 0395 -- 0396 -- regression test: check for failure to generate a plan with multiple 0397 -- degenerate IN clauses 0398 -- 0399 select count(*) from tenk1 x where 0400 x.unique1 in (select a.f1 from int4_tbl a,float8_tbl b where a.f1=b.f1) and 0401 x.unique1 = 0 and 0402 x.unique1 in (select aa.f1 from int4_tbl aa,float8_tbl bb where aa.f1=bb.f1); 0403 0404 -- try that with GEQO too 0405 -- begin; 0406 -- set geqo = on; 0407 -- set geqo_threshold = 2; 0408 select count(*) from tenk1 x where 0409 x.unique1 in (select a.f1 from int4_tbl a,float8_tbl b where a.f1=b.f1) and 0410 x.unique1 = 0 and 0411 x.unique1 in (select aa.f1 from int4_tbl aa,float8_tbl bb where aa.f1=bb.f1); 0412 -- rollback; 0413 0414 -- Skip this test because table b inherits from table a and we do not support this feature, see inherits.sql 0415 -- 0416 -- regression test: be sure we cope with proven-dummy append rels 0417 -- 0418 -- explain (costs off) 0419 -- select aa, bb, unique1, unique1 0420 -- from tenk1 right join b on aa = unique1 0421 -- where bb < bb and bb is null; 0422 0423 -- select aa, bb, unique1, unique1 0424 -- from tenk1 right join b on aa = unique1 0425 -- where bb < bb and bb is null; 0426 0427 -- 0428 -- regression test: check handling of empty-FROM subquery underneath outer join 0429 -- 0430 -- explain (costs off) 0431 -- select * from int8_tbl i1 left join (int8_tbl i2 join 0432 -- (select 123 as x) ss on i2.q1 = x) on i1.q2 = i2.q2 0433 -- order by 1, 2; 0434 0435 select * from int8_tbl i1 left join (int8_tbl i2 join 0436 (select 123 as x) ss on i2.q1 = x) on i1.q2 = i2.q2 0437 order by 1, 2; 0438 0439 -- 0440 -- regression test: check a case where join_clause_is_movable_into() gives 0441 -- an imprecise result, causing an assertion failure 0442 -- 0443 select count(*) 0444 from 0445 (select t3.tenthous as x1, coalesce(t1.stringu1, t2.stringu1) as x2 0446 from tenk1 t1 0447 left join tenk1 t2 on t1.unique1 = t2.unique1 0448 join tenk1 t3 on t1.unique2 = t3.unique2) ss, 0449 tenk1 t4, 0450 tenk1 t5 0451 where t4.thousand = t5.unique1 and ss.x1 = t4.tenthous and ss.x2 = t5.stringu1; 0452 0453 -- 0454 -- regression test: check a case where we formerly missed including an EC 0455 -- enforcement clause because it was expected to be handled at scan level 0456 -- 0457 -- explain (costs off) 0458 -- select a.f1, b.f1, t.thousand, t.tenthous from 0459 -- tenk1 t, 0460 -- (select sum(f1)+1 as f1 from int4_tbl i4a) a, 0461 -- (select sum(f1) as f1 from int4_tbl i4b) b 0462 -- where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous; 0463 0464 select a.f1, b.f1, t.thousand, t.tenthous from 0465 tenk1 t, 0466 (select sum(f1)+1 as f1 from int4_tbl i4a) a, 0467 (select sum(f1) as f1 from int4_tbl i4b) b 0468 where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous; 0469 0470 -- 0471 -- check a case where we formerly got confused by conflicting sort orders 0472 -- in redundant merge join path keys 0473 -- 0474 -- explain (costs off) 0475 -- select * from 0476 -- j1_tbl full join 0477 -- (select * from j2_tbl order by j2_tbl.i desc, j2_tbl.k asc) j2_tbl 0478 -- on j1_tbl.i = j2_tbl.i and j1_tbl.i = j2_tbl.k; 0479 0480 select * from 0481 j1_tbl full join 0482 (select * from j2_tbl order by j2_tbl.i desc, j2_tbl.k asc) j2_tbl 0483 on j1_tbl.i = j2_tbl.i and j1_tbl.i = j2_tbl.k; 0484 0485 -- 0486 -- a different check for handling of redundant sort keys in merge joins 0487 -- 0488 -- explain (costs off) 0489 -- select count(*) from 0490 -- (select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x 0491 -- left join 0492 -- (select * from tenk1 y order by y.unique2) y 0493 -- on x.thousand = y.unique2 and x.twothousand = y.hundred and x.fivethous = y.unique2; 0494 0495 select count(*) from 0496 (select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x 0497 left join 0498 (select * from tenk1 y order by y.unique2) y 0499 on x.thousand = y.unique2 and x.twothousand = y.hundred and x.fivethous = y.unique2; 0500 0501 0502 -- 0503 -- Clean up 0504 -- 0505 0506 DROP TABLE t1; 0507 DROP TABLE t2; 0508 DROP TABLE t3; 0509 0510 DROP TABLE J1_TBL; 0511 DROP TABLE J2_TBL; 0512 0513 -- Both DELETE and UPDATE allow the specification of additional tables 0514 -- to "join" against to determine which rows should be modified. 0515 0516 -- CREATE TEMP TABLE t1 (a int, b int); 0517 -- CREATE TEMP TABLE t2 (a int, b int); 0518 -- CREATE TEMP TABLE t3 (x int, y int); 0519 0520 -- INSERT INTO t1 VALUES (5, 10); 0521 -- INSERT INTO t1 VALUES (15, 20); 0522 -- INSERT INTO t1 VALUES (100, 100); 0523 -- INSERT INTO t1 VALUES (200, 1000); 0524 -- INSERT INTO t2 VALUES (200, 2000); 0525 -- INSERT INTO t3 VALUES (5, 20); 0526 -- INSERT INTO t3 VALUES (6, 7); 0527 -- INSERT INTO t3 VALUES (7, 8); 0528 -- INSERT INTO t3 VALUES (500, 100); 0529 0530 -- DELETE FROM t3 USING t1 table1 WHERE t3.x = table1.a; 0531 -- SELECT * FROM t3; 0532 -- DELETE FROM t3 USING t1 JOIN t2 USING (a) WHERE t3.x > t1.a; 0533 -- SELECT * FROM t3; 0534 -- DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y; 0535 -- SELECT * FROM t3; 0536 0537 -- Test join against inheritance tree 0538 0539 -- create temp table t2a () inherits (t2); 0540 0541 -- insert into t2a values (200, 2001); 0542 0543 -- select * from t1 left join t2 on (t1.a = t2.a); 0544 0545 -- Test matching of column name with wrong alias 0546 0547 -- select t1.x from t1 join t3 on (t1.a = t3.x); 0548 0549 -- 0550 -- regression test for 8.1 merge right join bug 0551 -- 0552 0553 create or replace temporary view tt1 as select * from 0554 (values (1, 11), (2, NULL)) 0555 as v(tt1_id, joincol); 0556 0557 create or replace temporary view tt2 as select * from 0558 (values (21, 11), (22, 11)) 0559 as v(tt2_id, joincol); 0560 0561 -- set enable_hashjoin to off; 0562 -- set enable_nestloop to off; 0563 0564 -- these should give the same results 0565 0566 select tt1.*, tt2.* from tt1 left join tt2 on tt1.joincol = tt2.joincol; 0567 0568 select tt1.*, tt2.* from tt2 right join tt1 on tt1.joincol = tt2.joincol; 0569 0570 -- reset enable_hashjoin; 0571 -- reset enable_nestloop; 0572 0573 -- 0574 -- regression test for bug #13908 (hash join with skew tuples & nbatch increase) 0575 -- 0576 0577 -- set work_mem to '64kB'; 0578 -- set enable_mergejoin to off; 0579 0580 -- explain (costs off) 0581 -- select count(*) from tenk1 a, tenk1 b 0582 -- where a.hundred = b.thousand and (b.fivethous % 10) < 10; 0583 select count(*) from tenk1 a, tenk1 b 0584 where a.hundred = b.thousand and (b.fivethous % 10) < 10; 0585 0586 -- reset work_mem; 0587 -- reset enable_mergejoin; 0588 0589 -- 0590 -- regression test for 8.2 bug with improper re-ordering of left joins 0591 -- 0592 0593 create or replace temporary view tt3 as select * from 0594 (SELECT cast(x.id as int), repeat('xyzzy', 100) FROM range(1,10001) x) 0595 as v(f1, f2); 0596 -- create index tt3i on tt3(f1); 0597 -- analyze tt3; 0598 0599 create or replace temporary view tt4 as select * from 0600 (values (0), (1), (9999)) 0601 as v(f1); 0602 -- analyze tt4; 0603 0604 SELECT a.f1 0605 FROM tt4 a 0606 LEFT JOIN ( 0607 SELECT b.f1 0608 FROM tt3 b LEFT JOIN tt3 c ON (b.f1 = c.f1) 0609 WHERE c.f1 IS NULL 0610 ) AS d ON (a.f1 = d.f1) 0611 WHERE d.f1 IS NULL; 0612 0613 -- 0614 -- regression test for proper handling of outer joins within antijoins 0615 -- 0616 0617 -- create temp table tt4x(c1 int, c2 int, c3 int); 0618 0619 -- explain (costs off) 0620 -- select * from tt4x t1 0621 -- where not exists ( 0622 -- select 1 from tt4x t2 0623 -- left join tt4x t3 on t2.c3 = t3.c1 0624 -- left join ( select t5.c1 as c1 0625 -- from tt4x t4 left join tt4x t5 on t4.c2 = t5.c1 0626 -- ) a1 on t3.c2 = a1.c1 0627 -- where t1.c1 = t2.c2 0628 -- ); 0629 0630 -- 0631 -- regression test for problems of the sort depicted in bug #3494 0632 -- 0633 0634 create or replace temporary view tt5 as select * from 0635 (values (1, 10), (1, 11)) 0636 as v(f1, f2); 0637 create or replace temporary view tt6 as select * from 0638 (values (1, 9), (1, 2), (2, 9)) 0639 as v(f1, f2); 0640 0641 select * from tt5,tt6 where tt5.f1 = tt6.f1 and tt5.f1 = tt5.f2 - tt6.f2; 0642 0643 -- 0644 -- regression test for problems of the sort depicted in bug #3588 0645 -- 0646 0647 create or replace temporary view xx as select * from 0648 (values (1), (2), (3)) 0649 as v(pkxx); 0650 create or replace temporary view yy as select * from 0651 (values (101, 1), (201, 2), (301, NULL)) 0652 as v(pkyy, pkxx); 0653 0654 select yy.pkyy as yy_pkyy, yy.pkxx as yy_pkxx, yya.pkyy as yya_pkyy, 0655 xxa.pkxx as xxa_pkxx, xxb.pkxx as xxb_pkxx 0656 from yy 0657 left join (SELECT * FROM yy where pkyy = 101) as yya ON yy.pkyy = yya.pkyy 0658 left join xx xxa on yya.pkxx = xxa.pkxx 0659 left join xx xxb on coalesce (xxa.pkxx, 1) = xxb.pkxx; 0660 0661 -- 0662 -- regression test for improper pushing of constants across outer-join clauses 0663 -- (as seen in early 8.2.x releases) 0664 -- 0665 0666 create or replace temporary view zt1 as select * from 0667 (values (53)) 0668 as v(f1); 0669 create or replace temporary view zt2 as select * from 0670 (values (53)) 0671 as v(f2); 0672 create or replace temporary view zt3(f3 int) using parquet; 0673 0674 select * from 0675 zt2 left join zt3 on (f2 = f3) 0676 left join zt1 on (f3 = f1) 0677 where f2 = 53; 0678 0679 create temp view zv1 as select *,'dummy' AS junk from zt1; 0680 0681 select * from 0682 zt2 left join zt3 on (f2 = f3) 0683 left join zv1 on (f3 = f1) 0684 where f2 = 53; 0685 0686 -- 0687 -- regression test for improper extraction of OR indexqual conditions 0688 -- (as seen in early 8.3.x releases) 0689 -- 0690 0691 select a.unique2, a.ten, b.tenthous, b.unique2, b.hundred 0692 from tenk1 a left join tenk1 b on a.unique2 = b.tenthous 0693 where a.unique1 = 42 and 0694 ((b.unique2 is null and a.ten = 2) or b.hundred = 3); 0695 0696 -- 0697 -- test proper positioning of one-time quals in EXISTS (8.4devel bug) 0698 -- 0699 -- prepare foo(bool) as 0700 -- select count(*) from tenk1 a left join tenk1 b 0701 -- on (a.unique2 = b.unique1 and exists 0702 -- (select 1 from tenk1 c where c.thousand = b.unique2 and $1)); 0703 -- execute foo(true); 0704 -- execute foo(false); 0705 0706 -- 0707 -- test for sane behavior with noncanonical merge clauses, per bug #4926 0708 -- 0709 0710 -- begin; 0711 0712 -- set enable_mergejoin = 1; 0713 -- set enable_hashjoin = 0; 0714 -- set enable_nestloop = 0; 0715 0716 create or replace temporary view a (i integer) using parquet; 0717 create or replace temporary view b (x integer, y integer) using parquet; 0718 0719 select * from a left join b on i = x and i = y and x = i; 0720 0721 -- rollback; 0722 0723 -- 0724 -- test handling of merge clauses using record_ops 0725 -- 0726 -- begin; 0727 0728 -- create type mycomptype as (id int, v bigint); 0729 0730 -- create temp table tidv (idv mycomptype); 0731 -- create index on tidv (idv); 0732 0733 -- explain (costs off) 0734 -- select a.idv, b.idv from tidv a, tidv b where a.idv = b.idv; 0735 0736 -- set enable_mergejoin = 0; 0737 0738 -- explain (costs off) 0739 -- select a.idv, b.idv from tidv a, tidv b where a.idv = b.idv; 0740 0741 -- rollback; 0742 0743 -- 0744 -- test NULL behavior of whole-row Vars, per bug #5025 0745 -- 0746 select t1.q2, count(t2.*) 0747 from int8_tbl t1 left join int8_tbl t2 on (t1.q2 = t2.q1) 0748 group by t1.q2 order by 1; 0749 0750 select t1.q2, count(t2.*) 0751 from int8_tbl t1 left join (select * from int8_tbl) t2 on (t1.q2 = t2.q1) 0752 group by t1.q2 order by 1; 0753 0754 -- [SPARK-28330] Enhance query limit 0755 -- select t1.q2, count(t2.*) 0756 -- from int8_tbl t1 left join (select * from int8_tbl offset 0) t2 on (t1.q2 = t2.q1) 0757 -- group by t1.q2 order by 1; 0758 0759 select t1.q2, count(t2.*) 0760 from int8_tbl t1 left join 0761 (select q1, case when q2=1 then 1 else q2 end as q2 from int8_tbl) t2 0762 on (t1.q2 = t2.q1) 0763 group by t1.q2 order by 1; 0764 0765 -- 0766 -- test incorrect failure to NULL pulled-up subexpressions 0767 -- 0768 -- begin; 0769 create or replace temporary view a as select * from 0770 (values ('p'), ('q')) 0771 as v(code); 0772 create or replace temporary view b as select * from 0773 (values ('p', 1), ('p', 2)) 0774 as v(a, num); 0775 create or replace temporary view c as select * from 0776 (values ('A', 'p'), ('B', 'q'), ('C', null)) 0777 as v(name, a); 0778 0779 select c.name, ss.code, ss.b_cnt, ss.const 0780 from c left join 0781 (select a.code, coalesce(b_grp.cnt, 0) as b_cnt, -1 as const 0782 from a left join 0783 (select count(1) as cnt, b.a from b group by b.a) as b_grp 0784 on a.code = b_grp.a 0785 ) as ss 0786 on (c.a = ss.code) 0787 order by c.name; 0788 0789 -- rollback; 0790 0791 -- 0792 -- test incorrect handling of placeholders that only appear in targetlists, 0793 -- per bug #6154 0794 -- 0795 SELECT * FROM 0796 ( SELECT 1 as key1 ) sub1 0797 LEFT JOIN 0798 ( SELECT sub3.key3, sub4.value2, COALESCE(sub4.value2, 66) as value3 FROM 0799 ( SELECT 1 as key3 ) sub3 0800 LEFT JOIN 0801 ( SELECT sub5.key5, COALESCE(sub6.value1, 1) as value2 FROM 0802 ( SELECT 1 as key5 ) sub5 0803 LEFT JOIN 0804 ( SELECT 2 as key6, 42 as value1 ) sub6 0805 ON sub5.key5 = sub6.key6 0806 ) sub4 0807 ON sub4.key5 = sub3.key3 0808 ) sub2 0809 ON sub1.key1 = sub2.key3; 0810 0811 -- test the path using join aliases, too 0812 SELECT * FROM 0813 ( SELECT 1 as key1 ) sub1 0814 LEFT JOIN 0815 ( SELECT sub3.key3, value2, COALESCE(value2, 66) as value3 FROM 0816 ( SELECT 1 as key3 ) sub3 0817 LEFT JOIN 0818 ( SELECT sub5.key5, COALESCE(sub6.value1, 1) as value2 FROM 0819 ( SELECT 1 as key5 ) sub5 0820 LEFT JOIN 0821 ( SELECT 2 as key6, 42 as value1 ) sub6 0822 ON sub5.key5 = sub6.key6 0823 ) sub4 0824 ON sub4.key5 = sub3.key3 0825 ) sub2 0826 ON sub1.key1 = sub2.key3; 0827 0828 -- 0829 -- test case where a PlaceHolderVar is used as a nestloop parameter 0830 -- 0831 0832 -- EXPLAIN (COSTS OFF) 0833 -- SELECT qq, unique1 0834 -- FROM 0835 -- ( SELECT COALESCE(q1, 0) AS qq FROM int8_tbl a ) AS ss1 0836 -- FULL OUTER JOIN 0837 -- ( SELECT COALESCE(q2, -1) AS qq FROM int8_tbl b ) AS ss2 0838 -- USING (qq) 0839 -- INNER JOIN tenk1 c ON qq = unique2; 0840 0841 SELECT qq, unique1 0842 FROM 0843 ( SELECT COALESCE(q1, 0) AS qq FROM int8_tbl a ) AS ss1 0844 FULL OUTER JOIN 0845 ( SELECT COALESCE(q2, -1) AS qq FROM int8_tbl b ) AS ss2 0846 USING (qq) 0847 INNER JOIN tenk1 c ON qq = unique2; 0848 0849 -- 0850 -- nested nestloops can require nested PlaceHolderVars 0851 -- 0852 0853 create or replace temporary view nt1 as select * from 0854 (values(1,true,true), (2,true,false), (3,false,false)) 0855 as v(id, a1, a2); 0856 create or replace temporary view nt2 as select * from 0857 (values(1,1,true,true), (2,2,true,false), (3,3,false,false)) 0858 as v(id, nt1_id, b1, b2); 0859 create or replace temporary view nt3 as select * from 0860 (values(1,1,true), (2,2,false), (3,3,true)) 0861 as v(id, nt2_id, c1); 0862 -- explain (costs off) 0863 -- select nt3.id 0864 -- from nt3 as nt3 0865 -- left join 0866 -- (select nt2.*, (nt2.b1 and ss1.a3) AS b3 0867 -- from nt2 as nt2 0868 -- left join 0869 -- (select nt1.*, (nt1.id is not null) as a3 from nt1) as ss1 0870 -- on ss1.id = nt2.nt1_id 0871 -- ) as ss2 0872 -- on ss2.id = nt3.nt2_id 0873 -- where nt3.id = 1 and ss2.b3; 0874 0875 select nt3.id 0876 from nt3 as nt3 0877 left join 0878 (select nt2.*, (nt2.b1 and ss1.a3) AS b3 0879 from nt2 as nt2 0880 left join 0881 (select nt1.*, (nt1.id is not null) as a3 from nt1) as ss1 0882 on ss1.id = nt2.nt1_id 0883 ) as ss2 0884 on ss2.id = nt3.nt2_id 0885 where nt3.id = 1 and ss2.b3; 0886 0887 -- [SPARK-28379] Correlated scalar subqueries must be aggregated 0888 -- 0889 -- test case where a PlaceHolderVar is propagated into a subquery 0890 -- 0891 0892 -- explain (costs off) 0893 -- select * from 0894 -- int8_tbl t1 left join 0895 -- (select q1 as x, 42 as y from int8_tbl t2) ss 0896 -- on t1.q2 = ss.x 0897 -- where 0898 -- 1 = (select 1 from int8_tbl t3 where ss.y is not null limit 1) 0899 -- order by 1,2; 0900 0901 -- select * from 0902 -- int8_tbl t1 left join 0903 -- (select q1 as x, 42 as y from int8_tbl t2) ss 0904 -- on t1.q2 = ss.x 0905 -- where 0906 -- 1 = (select 1 from int8_tbl t3 where ss.y is not null limit 1) 0907 -- order by 1,2; 0908 0909 -- 0910 -- test the corner cases FULL JOIN ON TRUE and FULL JOIN ON FALSE 0911 -- 0912 select * from int4_tbl a full join int4_tbl b on true; 0913 select * from int4_tbl a full join int4_tbl b on false; 0914 0915 -- 0916 -- test for ability to use a cartesian join when necessary 0917 -- 0918 0919 -- explain (costs off) 0920 -- select * from 0921 -- tenk1 join int4_tbl on f1 = twothousand, 0922 -- int4(sin(1)) q1, 0923 -- int4(sin(0)) q2 0924 -- where q1 = thousand or q2 = thousand; 0925 0926 -- explain (costs off) 0927 -- select * from 0928 -- tenk1 join int4_tbl on f1 = twothousand, 0929 -- int4(sin(1)) q1, 0930 -- int4(sin(0)) q2 0931 -- where thousand = (q1 + q2); 0932 0933 -- 0934 -- test ability to generate a suitable plan for a star-schema query 0935 -- 0936 0937 -- explain (costs off) 0938 -- select * from 0939 -- tenk1, int8_tbl a, int8_tbl b 0940 -- where thousand = a.q1 and tenthous = b.q1 and a.q2 = 1 and b.q2 = 2; 0941 0942 -- 0943 -- test a corner case in which we shouldn't apply the star-schema optimization 0944 -- 0945 0946 -- explain (costs off) 0947 -- select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from 0948 -- tenk1 t1 0949 -- inner join int4_tbl i1 0950 -- left join (select v1.x2, v2.y1, 11 AS d1 0951 -- from (select 1,0 from onerow) v1(x1,x2) 0952 -- left join (select 3,1 from onerow) v2(y1,y2) 0953 -- on v1.x1 = v2.y2) subq1 0954 -- on (i1.f1 = subq1.x2) 0955 -- on (t1.unique2 = subq1.d1) 0956 -- left join tenk1 t2 0957 -- on (subq1.y1 = t2.unique1) 0958 -- where t1.unique2 < 42 and t1.stringu1 > t2.stringu2; 0959 0960 -- [SPARK-20856] support statement using nested joins 0961 -- select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from 0962 -- tenk1 t1 0963 -- inner join int4_tbl i1 0964 -- left join (select v1.x2, v2.y1, 11 AS d1 0965 -- from (select 1,0 from onerow) v1(x1,x2) 0966 -- left join (select 3,1 from onerow) v2(y1,y2) 0967 -- on v1.x1 = v2.y2) subq1 0968 -- on (i1.f1 = subq1.x2) 0969 -- on (t1.unique2 = subq1.d1) 0970 -- left join tenk1 t2 0971 -- on (subq1.y1 = t2.unique1) 0972 -- where t1.unique2 < 42 and t1.stringu1 > t2.stringu2; 0973 0974 -- variant that isn't quite a star-schema case 0975 0976 -- [SPARK-16452] basic INFORMATION_SCHEMA support 0977 -- select ss1.d1 from 0978 -- tenk1 as t1 0979 -- inner join tenk1 as t2 0980 -- on t1.tenthous = t2.ten 0981 -- inner join 0982 -- int8_tbl as i8 0983 -- left join int4_tbl as i4 0984 -- inner join (select 64::information_schema.cardinal_number as d1 0985 -- from tenk1 t3, 0986 -- lateral (select abs(t3.unique1) + random()) ss0(x) 0987 -- where t3.fivethous < 0) as ss1 0988 -- on i4.f1 = ss1.d1 0989 -- on i8.q1 = i4.f1 0990 -- on t1.tenthous = ss1.d1 0991 -- where t1.unique1 < i4.f1; 0992 0993 -- this variant is foldable by the remove-useless-RESULT-RTEs code 0994 0995 -- explain (costs off) 0996 -- select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from 0997 -- tenk1 t1 0998 -- inner join int4_tbl i1 0999 -- left join (select v1.x2, v2.y1, 11 AS d1 1000 -- from (values(1,0)) v1(x1,x2) 1001 -- left join (values(3,1)) v2(y1,y2) 1002 -- on v1.x1 = v2.y2) subq1 1003 -- on (i1.f1 = subq1.x2) 1004 -- on (t1.unique2 = subq1.d1) 1005 -- left join tenk1 t2 1006 -- on (subq1.y1 = t2.unique1) 1007 -- where t1.unique2 < 42 and t1.stringu1 > t2.stringu2; 1008 1009 -- [SPARK-20856] support statement using nested joins 1010 -- select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from 1011 -- tenk1 t1 1012 -- inner join int4_tbl i1 1013 -- left join (select v1.x2, v2.y1, 11 AS d1 1014 -- from (values(1,0)) v1(x1,x2) 1015 -- left join (values(3,1)) v2(y1,y2) 1016 -- on v1.x1 = v2.y2) subq1 1017 -- on (i1.f1 = subq1.x2) 1018 -- on (t1.unique2 = subq1.d1) 1019 -- left join tenk1 t2 1020 -- on (subq1.y1 = t2.unique1) 1021 -- where t1.unique2 < 42 and t1.stringu1 > t2.stringu2; 1022 1023 -- 1024 -- test extraction of restriction OR clauses from join OR clause 1025 -- (we used to only do this for indexable clauses) 1026 -- 1027 1028 -- explain (costs off) 1029 -- select * from tenk1 a join tenk1 b on 1030 -- (a.unique1 = 1 and b.unique1 = 2) or (a.unique2 = 3 and b.hundred = 4); 1031 -- explain (costs off) 1032 -- select * from tenk1 a join tenk1 b on 1033 -- (a.unique1 = 1 and b.unique1 = 2) or (a.unique2 = 3 and b.ten = 4); 1034 -- explain (costs off) 1035 -- select * from tenk1 a join tenk1 b on 1036 -- (a.unique1 = 1 and b.unique1 = 2) or 1037 -- ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4); 1038 1039 -- 1040 -- test placement of movable quals in a parameterized join tree 1041 -- 1042 1043 -- explain (costs off) 1044 -- select * from tenk1 t1 left join 1045 -- (tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2) 1046 -- on t1.hundred = t2.hundred and t1.ten = t3.ten 1047 -- where t1.unique1 = 1; 1048 1049 -- explain (costs off) 1050 -- select * from tenk1 t1 left join 1051 -- (tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2) 1052 -- on t1.hundred = t2.hundred and t1.ten + t2.ten = t3.ten 1053 -- where t1.unique1 = 1; 1054 1055 -- explain (costs off) 1056 -- select count(*) from 1057 -- tenk1 a join tenk1 b on a.unique1 = b.unique2 1058 -- left join tenk1 c on a.unique2 = b.unique1 and c.thousand = a.thousand 1059 -- join int4_tbl on b.thousand = f1; 1060 1061 select count(*) from 1062 tenk1 a join tenk1 b on a.unique1 = b.unique2 1063 left join tenk1 c on a.unique2 = b.unique1 and c.thousand = a.thousand 1064 join int4_tbl on b.thousand = f1; 1065 1066 -- explain (costs off) 1067 -- select b.unique1 from 1068 -- tenk1 a join tenk1 b on a.unique1 = b.unique2 1069 -- left join tenk1 c on b.unique1 = 42 and c.thousand = a.thousand 1070 -- join int4_tbl i1 on b.thousand = f1 1071 -- right join int4_tbl i2 on i2.f1 = b.tenthous 1072 -- order by 1; 1073 1074 select b.unique1 from 1075 tenk1 a join tenk1 b on a.unique1 = b.unique2 1076 left join tenk1 c on b.unique1 = 42 and c.thousand = a.thousand 1077 join int4_tbl i1 on b.thousand = f1 1078 right join int4_tbl i2 on i2.f1 = b.tenthous 1079 order by 1; 1080 1081 -- explain (costs off) 1082 -- select * from 1083 -- ( 1084 -- select unique1, q1, coalesce(unique1, -1) + q1 as fault 1085 -- from int8_tbl left join tenk1 on (q2 = unique2) 1086 -- ) ss 1087 -- where fault = 122 1088 -- order by fault; 1089 1090 select * from 1091 ( 1092 select unique1, q1, coalesce(unique1, -1) + q1 as fault 1093 from int8_tbl left join tenk1 on (q2 = unique2) 1094 ) ss 1095 where fault = 122 1096 order by fault; 1097 1098 -- explain (costs off) 1099 -- select * from 1100 -- (values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys) 1101 -- left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x 1102 -- left join unnest(v1ys) as u1(u1y) on u1y = v2y; 1103 1104 -- [SPARK-28382] Array Functions: unnest 1105 -- select * from 1106 -- (values (1, array(10,20)), (2, array(20,30))) as v1(v1x,v1ys) 1107 -- left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x 1108 -- left join unnest(v1ys) as u1(u1y) on u1y = v2y; 1109 1110 -- 1111 -- test handling of potential equivalence clauses above outer joins 1112 -- 1113 1114 -- explain (costs off) 1115 -- select q1, unique2, thousand, hundred 1116 -- from int8_tbl a left join tenk1 b on q1 = unique2 1117 -- where coalesce(thousand,123) = q1 and q1 = coalesce(hundred,123); 1118 1119 select q1, unique2, thousand, hundred 1120 from int8_tbl a left join tenk1 b on q1 = unique2 1121 where coalesce(thousand,123) = q1 and q1 = coalesce(hundred,123); 1122 1123 -- explain (costs off) 1124 -- select f1, unique2, case when unique2 is null then f1 else 0 end 1125 -- from int4_tbl a left join tenk1 b on f1 = unique2 1126 -- where (case when unique2 is null then f1 else 0 end) = 0; 1127 1128 select f1, unique2, case when unique2 is null then f1 else 0 end 1129 from int4_tbl a left join tenk1 b on f1 = unique2 1130 where (case when unique2 is null then f1 else 0 end) = 0; 1131 1132 -- 1133 -- another case with equivalence clauses above outer joins (bug #8591) 1134 -- 1135 1136 -- explain (costs off) 1137 -- select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand) 1138 -- from tenk1 a left join tenk1 b on b.thousand = a.unique1 left join tenk1 c on c.unique2 = coalesce(b.twothousand, a.twothousand) 1139 -- where a.unique2 < 10 and coalesce(b.twothousand, a.twothousand) = 44; 1140 1141 select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand) 1142 from tenk1 a left join tenk1 b on b.thousand = a.unique1 left join tenk1 c on c.unique2 = coalesce(b.twothousand, a.twothousand) 1143 where a.unique2 < 10 and coalesce(b.twothousand, a.twothousand) = 44; 1144 1145 -- 1146 -- check handling of join aliases when flattening multiple levels of subquery 1147 -- 1148 1149 -- explain (verbose, costs off) 1150 -- select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from 1151 -- (values (0),(1)) foo1(join_key) 1152 -- left join 1153 -- (select join_key, bug_field from 1154 -- (select ss1.join_key, ss1.bug_field from 1155 -- (select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1 1156 -- ) foo2 1157 -- left join 1158 -- (select unique2 as join_key from tenk1 i2) ss2 1159 -- using (join_key) 1160 -- ) foo3 1161 -- using (join_key); 1162 1163 1164 -- [SPARK-28377] Fully support correlation names in the FROM clause 1165 -- select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from 1166 -- (values (0),(1)) foo1(join_key) 1167 -- left join 1168 -- (select join_key, bug_field from 1169 -- (select ss1.join_key, ss1.bug_field from 1170 -- (select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1 1171 -- ) foo2 1172 -- left join 1173 -- (select unique2 as join_key from tenk1 i2) ss2 1174 -- using (join_key) 1175 -- ) foo3 1176 -- using (join_key); 1177 1178 -- [SPARK-20856] Support statement using nested joins 1179 -- 1180 -- test successful handling of nested outer joins with degenerate join quals 1181 -- 1182 1183 -- explain (verbose, costs off) 1184 -- select t1.* from 1185 -- text_tbl t1 1186 -- left join (select *, '***'::text as d1 from int8_tbl i8b1) b1 1187 -- left join int8_tbl i8 1188 -- left join (select *, null::int as d2 from int8_tbl i8b2) b2 1189 -- on (i8.q1 = b2.q1) 1190 -- on (b2.d2 = b1.q2) 1191 -- on (t1.f1 = b1.d1) 1192 -- left join int4_tbl i4 1193 -- on (i8.q2 = i4.f1); 1194 1195 -- select t1.* from 1196 -- text_tbl t1 1197 -- left join (select *, string('***') as d1 from int8_tbl i8b1) b1 1198 -- left join int8_tbl i8 1199 -- left join (select *, int(null) as d2 from int8_tbl i8b2) b2 1200 -- on (i8.q1 = b2.q1) 1201 -- on (b2.d2 = b1.q2) 1202 -- on (t1.f1 = b1.d1) 1203 -- left join int4_tbl i4 1204 -- on (i8.q2 = i4.f1); 1205 1206 -- explain (verbose, costs off) 1207 -- select t1.* from 1208 -- text_tbl t1 1209 -- left join (select *, '***'::text as d1 from int8_tbl i8b1) b1 1210 -- left join int8_tbl i8 1211 -- left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2) b2 1212 -- on (i8.q1 = b2.q1) 1213 -- on (b2.d2 = b1.q2) 1214 -- on (t1.f1 = b1.d1) 1215 -- left join int4_tbl i4 1216 -- on (i8.q2 = i4.f1); 1217 1218 -- select t1.* from 1219 -- text_tbl t1 1220 -- left join (select *, string('***') as d1 from int8_tbl i8b1) b1 1221 -- left join int8_tbl i8 1222 -- left join (select *, int(null) as d2 from int8_tbl i8b2, int4_tbl i4b2) b2 1223 -- on (i8.q1 = b2.q1) 1224 -- on (b2.d2 = b1.q2) 1225 -- on (t1.f1 = b1.d1) 1226 -- left join int4_tbl i4 1227 -- on (i8.q2 = i4.f1); 1228 1229 -- explain (verbose, costs off) 1230 -- select t1.* from 1231 -- text_tbl t1 1232 -- left join (select *, '***'::text as d1 from int8_tbl i8b1) b1 1233 -- left join int8_tbl i8 1234 -- left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2 1235 -- where q1 = f1) b2 1236 -- on (i8.q1 = b2.q1) 1237 -- on (b2.d2 = b1.q2) 1238 -- on (t1.f1 = b1.d1) 1239 -- left join int4_tbl i4 1240 -- on (i8.q2 = i4.f1); 1241 1242 -- select t1.* from 1243 -- text_tbl t1 1244 -- left join (select *, string('***') as d1 from int8_tbl i8b1) b1 1245 -- left join int8_tbl i8 1246 -- left join (select *, int(null) as d2 from int8_tbl i8b2, int4_tbl i4b2 1247 -- where q1 = f1) b2 1248 -- on (i8.q1 = b2.q1) 1249 -- on (b2.d2 = b1.q2) 1250 -- on (t1.f1 = b1.d1) 1251 -- left join int4_tbl i4 1252 -- on (i8.q2 = i4.f1); 1253 1254 -- explain (verbose, costs off) 1255 -- select * from 1256 -- text_tbl t1 1257 -- inner join int8_tbl i8 1258 -- on i8.q2 = 456 1259 -- right join text_tbl t2 1260 -- on t1.f1 = 'doh!' 1261 -- left join int4_tbl i4 1262 -- on i8.q1 = i4.f1; 1263 1264 select * from 1265 text_tbl t1 1266 inner join int8_tbl i8 1267 on i8.q2 = 456 1268 right join text_tbl t2 1269 on t1.f1 = 'doh!' 1270 left join int4_tbl i4 1271 on i8.q1 = i4.f1; 1272 1273 -- [SPARK-27877] ANSI SQL: LATERAL derived table(T491) 1274 -- 1275 -- test for appropriate join order in the presence of lateral references 1276 -- 1277 1278 -- explain (verbose, costs off) 1279 -- select * from 1280 -- text_tbl t1 1281 -- left join int8_tbl i8 1282 -- on i8.q2 = 123, 1283 -- lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss 1284 -- where t1.f1 = ss.f1; 1285 1286 -- select * from 1287 -- text_tbl t1 1288 -- left join int8_tbl i8 1289 -- on i8.q2 = 123, 1290 -- lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss 1291 -- where t1.f1 = ss.f1; 1292 1293 -- explain (verbose, costs off) 1294 -- select * from 1295 -- text_tbl t1 1296 -- left join int8_tbl i8 1297 -- on i8.q2 = 123, 1298 -- lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1, 1299 -- lateral (select ss1.* from text_tbl t3 limit 1) as ss2 1300 -- where t1.f1 = ss2.f1; 1301 1302 -- select * from 1303 -- text_tbl t1 1304 -- left join int8_tbl i8 1305 -- on i8.q2 = 123, 1306 -- lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1, 1307 -- lateral (select ss1.* from text_tbl t3 limit 1) as ss2 1308 -- where t1.f1 = ss2.f1; 1309 1310 -- explain (verbose, costs off) 1311 -- select 1 from 1312 -- text_tbl as tt1 1313 -- inner join text_tbl as tt2 on (tt1.f1 = 'foo') 1314 -- left join text_tbl as tt3 on (tt3.f1 = 'foo') 1315 -- left join text_tbl as tt4 on (tt3.f1 = tt4.f1), 1316 -- lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1 1317 -- where tt1.f1 = ss1.c0; 1318 1319 -- select 1 from 1320 -- text_tbl as tt1 1321 -- inner join text_tbl as tt2 on (tt1.f1 = 'foo') 1322 -- left join text_tbl as tt3 on (tt3.f1 = 'foo') 1323 -- left join text_tbl as tt4 on (tt3.f1 = tt4.f1), 1324 -- lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1 1325 -- where tt1.f1 = ss1.c0; 1326 1327 -- 1328 -- check a case in which a PlaceHolderVar forces join order 1329 -- 1330 1331 -- explain (verbose, costs off) 1332 -- select ss2.* from 1333 -- int4_tbl i41 1334 -- left join int8_tbl i8 1335 -- join (select i42.f1 as c1, i43.f1 as c2, 42 as c3 1336 -- from int4_tbl i42, int4_tbl i43) ss1 1337 -- on i8.q1 = ss1.c2 1338 -- on i41.f1 = ss1.c1, 1339 -- lateral (select i41.*, i8.*, ss1.* from text_tbl limit 1) ss2 1340 -- where ss1.c2 = 0; 1341 1342 -- select ss2.* from 1343 -- int4_tbl i41 1344 -- left join int8_tbl i8 1345 -- join (select i42.f1 as c1, i43.f1 as c2, 42 as c3 1346 -- from int4_tbl i42, int4_tbl i43) ss1 1347 -- on i8.q1 = ss1.c2 1348 -- on i41.f1 = ss1.c1, 1349 -- lateral (select i41.*, i8.*, ss1.* from text_tbl limit 1) ss2 1350 -- where ss1.c2 = 0; 1351 1352 -- 1353 -- test successful handling of full join underneath left join (bug #14105) 1354 -- 1355 1356 -- explain (costs off) 1357 -- select * from 1358 -- (select 1 as id) as xx 1359 -- left join 1360 -- (tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id)) 1361 -- on (xx.id = coalesce(yy.id)); 1362 1363 select * from 1364 (select 1 as id) as xx 1365 left join 1366 (tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id)) 1367 on (xx.id = coalesce(yy.id)); 1368 1369 -- 1370 -- test ability to push constants through outer join clauses 1371 -- 1372 1373 -- explain (costs off) 1374 -- select * from int4_tbl a left join tenk1 b on f1 = unique2 where f1 = 0; 1375 1376 -- explain (costs off) 1377 -- select * from tenk1 a full join tenk1 b using(unique2) where unique2 = 42; 1378 1379 -- 1380 -- test that quals attached to an outer join have correct semantics, 1381 -- specifically that they don't re-use expressions computed below the join; 1382 -- we force a mergejoin so that coalesce(b.q1, 1) appears as a join input 1383 -- 1384 1385 -- set enable_hashjoin to off; 1386 -- set enable_nestloop to off; 1387 1388 -- explain (verbose, costs off) 1389 -- select a.q2, b.q1 1390 -- from int8_tbl a left join int8_tbl b on a.q2 = coalesce(b.q1, 1) 1391 -- where coalesce(b.q1, 1) > 0; 1392 select a.q2, b.q1 1393 from int8_tbl a left join int8_tbl b on a.q2 = coalesce(b.q1, 1) 1394 where coalesce(b.q1, 1) > 0; 1395 1396 -- reset enable_hashjoin; 1397 -- reset enable_nestloop; 1398 1399 -- Skip these test because it only test explain 1400 -- 1401 -- test join removal 1402 -- 1403 1404 -- begin; 1405 1406 -- CREATE TEMP TABLE a (id int PRIMARY KEY, b_id int); 1407 -- CREATE TEMP TABLE b (id int PRIMARY KEY, c_id int); 1408 -- CREATE TEMP TABLE c (id int PRIMARY KEY); 1409 -- CREATE TEMP TABLE d (a int, b int); 1410 -- INSERT INTO a VALUES (0, 0), (1, NULL); 1411 -- INSERT INTO b VALUES (0, 0), (1, NULL); 1412 -- INSERT INTO c VALUES (0), (1); 1413 -- INSERT INTO d VALUES (1,3), (2,2), (3,1); 1414 1415 -- all three cases should be optimizable into a simple seqscan 1416 -- explain (costs off) SELECT a.* FROM a LEFT JOIN b ON a.b_id = b.id; 1417 -- explain (costs off) SELECT b.* FROM b LEFT JOIN c ON b.c_id = c.id; 1418 -- explain (costs off) 1419 -- SELECT a.* FROM a LEFT JOIN (b left join c on b.c_id = c.id) 1420 -- ON (a.b_id = b.id); 1421 1422 -- check optimization of outer join within another special join 1423 -- explain (costs off) 1424 -- select id from a where id in ( 1425 -- select b.id from b left join c on b.id = c.id 1426 -- ); 1427 1428 -- check that join removal works for a left join when joining a subquery 1429 -- that is guaranteed to be unique by its GROUP BY clause 1430 -- explain (costs off) 1431 -- select d.* from d left join (select * from b group by b.id, b.c_id) s 1432 -- on d.a = s.id and d.b = s.c_id; 1433 1434 -- similarly, but keying off a DISTINCT clause 1435 -- explain (costs off) 1436 -- select d.* from d left join (select distinct * from b) s 1437 -- on d.a = s.id and d.b = s.c_id; 1438 1439 -- join removal is not possible when the GROUP BY contains a column that is 1440 -- not in the join condition. (Note: as of 9.6, we notice that b.id is a 1441 -- primary key and so drop b.c_id from the GROUP BY of the resulting plan; 1442 -- but this happens too late for join removal in the outer plan level.) 1443 -- explain (costs off) 1444 -- select d.* from d left join (select * from b group by b.id, b.c_id) s 1445 -- on d.a = s.id; 1446 1447 -- similarly, but keying off a DISTINCT clause 1448 -- explain (costs off) 1449 -- select d.* from d left join (select distinct * from b) s 1450 -- on d.a = s.id; 1451 1452 -- check join removal works when uniqueness of the join condition is enforced 1453 -- by a UNION 1454 -- explain (costs off) 1455 -- select d.* from d left join (select id from a union select id from b) s 1456 -- on d.a = s.id; 1457 1458 -- check join removal with a cross-type comparison operator 1459 -- explain (costs off) 1460 -- select i8.* from int8_tbl i8 left join (select f1 from int4_tbl group by f1) i4 1461 -- on i8.q1 = i4.f1; 1462 1463 -- check join removal with lateral references 1464 -- explain (costs off) 1465 -- select 1 from (select a.id FROM a left join b on a.b_id = b.id) q, 1466 -- lateral generate_series(1, q.id) gs(i) where q.id = gs.i; 1467 1468 -- rollback; 1469 1470 create or replace temporary view parent as select * from 1471 (values (1, 10), (2, 20), (3, 30)) 1472 as v(k, pd); 1473 create or replace temporary view child as select * from 1474 (values (1, 100), (4, 400)) 1475 as v(k, cd); 1476 1477 -- this case is optimizable 1478 select p.* from parent p left join child c on (p.k = c.k); 1479 -- explain (costs off) 1480 -- select p.* from parent p left join child c on (p.k = c.k); 1481 1482 -- this case is not 1483 select p.*, linked from parent p 1484 left join (select c.*, true as linked from child c) as ss 1485 on (p.k = ss.k); 1486 -- explain (costs off) 1487 -- select p.*, linked from parent p 1488 -- left join (select c.*, true as linked from child c) as ss 1489 -- on (p.k = ss.k); 1490 1491 -- check for a 9.0rc1 bug: join removal breaks pseudoconstant qual handling 1492 select p.* from 1493 parent p left join child c on (p.k = c.k) 1494 where p.k = 1 and p.k = 2; 1495 -- explain (costs off) 1496 -- select p.* from 1497 -- parent p left join child c on (p.k = c.k) 1498 -- where p.k = 1 and p.k = 2; 1499 1500 select p.* from 1501 (parent p left join child c on (p.k = c.k)) join parent x on p.k = x.k 1502 where p.k = 1 and p.k = 2; 1503 -- explain (costs off) 1504 -- select p.* from 1505 -- (parent p left join child c on (p.k = c.k)) join parent x on p.k = x.k 1506 -- where p.k = 1 and p.k = 2; 1507 1508 -- bug 5255: this is not optimizable by join removal 1509 -- begin; 1510 1511 create or replace temporary view a as select * from 1512 (values (0), (1)) 1513 as v(id); 1514 create or replace temporary view b as select * from 1515 (values (0, 0), (1, NULL)) 1516 as v(id, a_id); 1517 1518 SELECT * FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0); 1519 SELECT b.* FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0); 1520 1521 -- rollback; 1522 1523 -- another join removal bug: this is not optimizable, either 1524 -- begin; 1525 1526 create or replace temporary view innertab as select * from 1527 (values (123L, 42L)) 1528 as v(id, dat1); 1529 1530 SELECT * FROM 1531 (SELECT 1 AS x) ss1 1532 LEFT JOIN 1533 (SELECT q1, q2, COALESCE(dat1, q1) AS y 1534 FROM int8_tbl LEFT JOIN innertab ON q2 = id) ss2 1535 ON true; 1536 1537 -- rollback; 1538 1539 -- another join removal bug: we must clean up correctly when removing a PHV 1540 -- begin; 1541 1542 -- create temp table uniquetbl (f1 text unique); 1543 1544 -- explain (costs off) 1545 -- select t1.* from 1546 -- uniquetbl as t1 1547 -- left join (select *, '***'::text as d1 from uniquetbl) t2 1548 -- on t1.f1 = t2.f1 1549 -- left join uniquetbl t3 1550 -- on t2.d1 = t3.f1; 1551 1552 -- explain (costs off) 1553 -- select t0.* 1554 -- from 1555 -- text_tbl t0 1556 -- left join 1557 -- (select case t1.ten when 0 then 'doh!'::text else null::text end as case1, 1558 -- t1.stringu2 1559 -- from tenk1 t1 1560 -- join int4_tbl i4 ON i4.f1 = t1.unique2 1561 -- left join uniquetbl u1 ON u1.f1 = t1.string4) ss 1562 -- on t0.f1 = ss.case1 1563 -- where ss.stringu2 !~* ss.case1; 1564 1565 -- [SPARK-27987] Support POSIX Regular Expressions 1566 -- select t0.* 1567 -- from 1568 -- text_tbl t0 1569 -- left join 1570 -- (select case t1.ten when 0 then 'doh!'::text else null::text end as case1, 1571 -- t1.stringu2 1572 -- from tenk1 t1 1573 -- join int4_tbl i4 ON i4.f1 = t1.unique2 1574 -- left join uniquetbl u1 ON u1.f1 = t1.string4) ss 1575 -- on t0.f1 = ss.case1 1576 -- where ss.stringu2 !~* ss.case1; 1577 1578 -- rollback; 1579 1580 -- bug #8444: we've historically allowed duplicate aliases within aliased JOINs 1581 1582 select * from 1583 int8_tbl x join (int4_tbl x cross join int4_tbl y) j on q1 = f1; -- error 1584 select * from 1585 int8_tbl x join (int4_tbl x cross join int4_tbl y) j on q1 = y.f1; -- error 1586 select * from 1587 int8_tbl x join (int4_tbl x cross join int4_tbl y(ff)) j on q1 = f1; -- ok 1588 1589 -- 1590 -- Test hints given on incorrect column references are useful 1591 -- 1592 1593 select t1.uunique1 from 1594 tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, prefer "t1" suggestion 1595 select t2.uunique1 from 1596 tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, prefer "t2" suggestion 1597 select uunique1 from 1598 tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, suggest both at once 1599 1600 -- Skip this test because it is a PostgreSQL specific case 1601 -- 1602 -- Take care to reference the correct RTE 1603 -- 1604 1605 -- -- select atts.relid::regclass, s.* from pg_stats s join 1606 -- -- pg_attribute a on s.attname = a.attname and s.tablename = 1607 -- -- a.attrelid::regclass::text join (select unnest(indkey) attnum, 1608 -- -- indexrelid from pg_index i) atts on atts.attnum = a.attnum where 1609 -- schemaname != 'pg_catalog'; 1610 1611 -- [SPARK-27877] ANSI SQL: LATERAL derived table(T491) 1612 -- 1613 -- Test LATERAL 1614 -- 1615 1616 -- select unique2, x.* 1617 -- from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x; 1618 -- explain (costs off) 1619 -- select unique2, x.* 1620 -- from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x; 1621 -- select unique2, x.* 1622 -- from int4_tbl x, lateral (select unique2 from tenk1 where f1 = unique1) ss; 1623 -- explain (costs off) 1624 -- select unique2, x.* 1625 -- from int4_tbl x, lateral (select unique2 from tenk1 where f1 = unique1) ss; 1626 -- explain (costs off) 1627 -- select unique2, x.* 1628 -- from int4_tbl x cross join lateral (select unique2 from tenk1 where f1 = unique1) ss; 1629 -- select unique2, x.* 1630 -- from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 = unique1) ss on true; 1631 -- explain (costs off) 1632 -- select unique2, x.* 1633 -- from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 = unique1) ss on true; 1634 1635 -- [SPARK-27877] ANSI SQL: LATERAL derived table(T491) 1636 -- check scoping of lateral versus parent references 1637 -- the first of these should return int8_tbl.q2, the second int8_tbl.q1 1638 -- select *, (select r from (select q1 as q2) x, (select q2 as r) y) from int8_tbl; 1639 -- select *, (select r from (select q1 as q2) x, lateral (select q2 as r) y) from int8_tbl; 1640 1641 -- lateral with function in FROM 1642 -- select count(*) from tenk1 a, lateral generate_series(1,two) g; 1643 -- explain (costs off) 1644 -- select count(*) from tenk1 a, lateral generate_series(1,two) g; 1645 -- explain (costs off) 1646 -- select count(*) from tenk1 a cross join lateral generate_series(1,two) g; 1647 -- don't need the explicit LATERAL keyword for functions 1648 -- explain (costs off) 1649 -- select count(*) from tenk1 a, generate_series(1,two) g; 1650 1651 -- lateral with UNION ALL subselect 1652 -- explain (costs off) 1653 -- select * from generate_series(100,200) g, 1654 -- lateral (select * from int8_tbl a where g = q1 union all 1655 -- select * from int8_tbl b where g = q2) ss; 1656 -- select * from generate_series(100,200) g, 1657 -- lateral (select * from int8_tbl a where g = q1 union all 1658 -- select * from int8_tbl b where g = q2) ss; 1659 1660 -- lateral with VALUES 1661 -- explain (costs off) 1662 -- select count(*) from tenk1 a, 1663 -- tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x; 1664 -- select count(*) from tenk1 a, 1665 -- tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x; 1666 1667 -- lateral with VALUES, no flattening possible 1668 -- explain (costs off) 1669 -- select count(*) from tenk1 a, 1670 -- tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x; 1671 -- select count(*) from tenk1 a, 1672 -- tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x; 1673 1674 -- lateral injecting a strange outer join condition 1675 -- explain (costs off) 1676 -- select * from int8_tbl a, 1677 -- int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z) 1678 -- on x.q2 = ss.z 1679 -- order by a.q1, a.q2, x.q1, x.q2, ss.z; 1680 -- select * from int8_tbl a, 1681 -- int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z) 1682 -- on x.q2 = ss.z 1683 -- order by a.q1, a.q2, x.q1, x.q2, ss.z; 1684 1685 -- lateral reference to a join alias variable 1686 -- select * from (select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1, 1687 -- lateral (select x) ss2(y); 1688 -- select * from (select f1 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1, 1689 -- lateral (values(x)) ss2(y); 1690 -- select * from ((select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1) j, 1691 -- lateral (select x) ss2(y); 1692 1693 -- lateral references requiring pullup 1694 -- select * from (values(1)) x(lb), 1695 -- lateral generate_series(lb,4) x4; 1696 -- select * from (select f1/1000000000 from int4_tbl) x(lb), 1697 -- lateral generate_series(lb,4) x4; 1698 -- select * from (values(1)) x(lb), 1699 -- lateral (values(lb)) y(lbcopy); 1700 -- select * from (values(1)) x(lb), 1701 -- lateral (select lb from int4_tbl) y(lbcopy); 1702 -- select * from 1703 -- int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1, 1704 -- lateral (values(x.q1,y.q1,y.q2)) v(xq1,yq1,yq2); 1705 -- select * from 1706 -- int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1, 1707 -- lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2); 1708 -- select x.* from 1709 -- int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1, 1710 -- lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2); 1711 -- select v.* from 1712 -- (int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1) 1713 -- left join int4_tbl z on z.f1 = x.q2, 1714 -- lateral (select x.q1,y.q1 union all select x.q2,y.q2) v(vx,vy); 1715 -- select v.* from 1716 -- (int8_tbl x left join (select q1,(select coalesce(q2,0)) q2 from int8_tbl) y on x.q2 = y.q1) 1717 -- left join int4_tbl z on z.f1 = x.q2, 1718 -- lateral (select x.q1,y.q1 union all select x.q2,y.q2) v(vx,vy); 1719 -- select v.* from 1720 -- (int8_tbl x left join (select q1,(select coalesce(q2,0)) q2 from int8_tbl) y on x.q2 = y.q1) 1721 -- left join int4_tbl z on z.f1 = x.q2, 1722 -- lateral (select x.q1,y.q1 from onerow union all select x.q2,y.q2 from onerow) v(vx,vy); 1723 1724 -- explain (verbose, costs off) 1725 -- select * from 1726 -- int8_tbl a left join 1727 -- lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1; 1728 -- select * from 1729 -- int8_tbl a left join 1730 -- lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1; 1731 -- explain (verbose, costs off) 1732 -- select * from 1733 -- int8_tbl a left join 1734 -- lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1; 1735 -- select * from 1736 -- int8_tbl a left join 1737 -- lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1; 1738 1739 -- lateral can result in join conditions appearing below their 1740 -- real semantic level 1741 -- explain (verbose, costs off) 1742 -- select * from int4_tbl i left join 1743 -- lateral (select * from int2_tbl j where i.f1 = j.f1) k on true; 1744 -- select * from int4_tbl i left join 1745 -- lateral (select * from int2_tbl j where i.f1 = j.f1) k on true; 1746 -- explain (verbose, costs off) 1747 -- select * from int4_tbl i left join 1748 -- lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true; 1749 -- select * from int4_tbl i left join 1750 -- lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true; 1751 -- explain (verbose, costs off) 1752 -- select * from int4_tbl a, 1753 -- lateral ( 1754 -- select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2) 1755 -- ) ss; 1756 -- select * from int4_tbl a, 1757 -- lateral ( 1758 -- select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2) 1759 -- ) ss; 1760 1761 -- lateral reference in a PlaceHolderVar evaluated at join level 1762 -- explain (verbose, costs off) 1763 -- select * from 1764 -- int8_tbl a left join lateral 1765 -- (select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from 1766 -- int8_tbl b cross join int8_tbl c) ss 1767 -- on a.q2 = ss.bq1; 1768 -- select * from 1769 -- int8_tbl a left join lateral 1770 -- (select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from 1771 -- int8_tbl b cross join int8_tbl c) ss 1772 -- on a.q2 = ss.bq1; 1773 1774 -- case requiring nested PlaceHolderVars 1775 -- explain (verbose, costs off) 1776 -- select * from 1777 -- int8_tbl c left join ( 1778 -- int8_tbl a left join (select q1, coalesce(q2,42) as x from int8_tbl b) ss1 1779 -- on a.q2 = ss1.q1 1780 -- cross join 1781 -- lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2 1782 -- ) on c.q2 = ss2.q1, 1783 -- lateral (select ss2.y offset 0) ss3; 1784 1785 -- case that breaks the old ph_may_need optimization 1786 -- explain (verbose, costs off) 1787 -- select c.*,a.*,ss1.q1,ss2.q1,ss3.* from 1788 -- int8_tbl c left join ( 1789 -- int8_tbl a left join 1790 -- (select q1, coalesce(q2,f1) as x from int8_tbl b, int4_tbl b2 1791 -- where q1 < f1) ss1 1792 -- on a.q2 = ss1.q1 1793 -- cross join 1794 -- lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2 1795 -- ) on c.q2 = ss2.q1, 1796 -- lateral (select * from int4_tbl i where ss2.y > f1) ss3; 1797 1798 -- check processing of postponed quals (bug #9041) 1799 -- explain (verbose, costs off) 1800 -- select * from 1801 -- (select 1 as x offset 0) x cross join (select 2 as y offset 0) y 1802 -- left join lateral ( 1803 -- select * from (select 3 as z offset 0) z where z.z = x.x 1804 -- ) zz on zz.z = y.y; 1805 1806 -- check dummy rels with lateral references (bug #15694) 1807 -- explain (verbose, costs off) 1808 -- select * from int8_tbl i8 left join lateral 1809 -- (select *, i8.q2 from int4_tbl where false) ss on true; 1810 -- explain (verbose, costs off) 1811 -- select * from int8_tbl i8 left join lateral 1812 -- (select *, i8.q2 from int4_tbl i1, int4_tbl i2 where false) ss on true; 1813 1814 -- check handling of nested appendrels inside LATERAL 1815 -- select * from 1816 -- ((select 2 as v) union all (select 3 as v)) as q1 1817 -- cross join lateral 1818 -- ((select * from 1819 -- ((select 4 as v) union all (select 5 as v)) as q3) 1820 -- union all 1821 -- (select q1.v) 1822 -- ) as q2; 1823 1824 -- check we don't try to do a unique-ified semijoin with LATERAL 1825 -- explain (verbose, costs off) 1826 -- select * from 1827 -- (values (0,9998), (1,1000)) v(id,x), 1828 -- lateral (select f1 from int4_tbl 1829 -- where f1 = any (select unique1 from tenk1 1830 -- where unique2 = v.x offset 0)) ss; 1831 -- select * from 1832 -- (values (0,9998), (1,1000)) v(id,x), 1833 -- lateral (select f1 from int4_tbl 1834 -- where f1 = any (select unique1 from tenk1 1835 -- where unique2 = v.x offset 0)) ss; 1836 1837 -- check proper extParam/allParam handling (this isn't exactly a LATERAL issue, 1838 -- but we can make the test case much more compact with LATERAL) 1839 -- explain (verbose, costs off) 1840 -- select * from (values (0), (1)) v(id), 1841 -- lateral (select * from int8_tbl t1, 1842 -- lateral (select * from 1843 -- (select * from int8_tbl t2 1844 -- where q1 = any (select q2 from int8_tbl t3 1845 -- where q2 = (select greatest(t1.q1,t2.q2)) 1846 -- and (select v.id=0)) offset 0) ss2) ss 1847 -- where t1.q1 = ss.q2) ss0; 1848 1849 -- select * from (values (0), (1)) v(id), 1850 -- lateral (select * from int8_tbl t1, 1851 -- lateral (select * from 1852 -- (select * from int8_tbl t2 1853 -- where q1 = any (select q2 from int8_tbl t3 1854 -- where q2 = (select greatest(t1.q1,t2.q2)) 1855 -- and (select v.id=0)) offset 0) ss2) ss 1856 -- where t1.q1 = ss.q2) ss0; 1857 1858 -- test some error cases where LATERAL should have been used but wasn't 1859 select f1,g from int4_tbl a, (select f1 as g) ss; 1860 select f1,g from int4_tbl a, (select a.f1 as g) ss; 1861 select f1,g from int4_tbl a cross join (select f1 as g) ss; 1862 select f1,g from int4_tbl a cross join (select a.f1 as g) ss; 1863 -- SQL:2008 says the left table is in scope but illegal to access here 1864 -- select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true; 1865 -- select f1,g from int4_tbl a full join lateral generate_series(0, a.f1) g on true; 1866 -- check we complain about ambiguous table references 1867 -- select * from 1868 -- int8_tbl x cross join (int4_tbl x cross join lateral (select x.f1) ss); 1869 -- LATERAL can be used to put an aggregate into the FROM clause of its query 1870 -- select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss; 1871 1872 -- check behavior of LATERAL in UPDATE/DELETE 1873 1874 -- create temp table xx1 as select f1 as x1, -f1 as x2 from int4_tbl; 1875 1876 -- error, can't do this: 1877 -- update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss; 1878 -- update xx1 set x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss; 1879 -- can't do it even with LATERAL: 1880 -- update xx1 set x2 = f1 from lateral (select * from int4_tbl where f1 = x1) ss; 1881 -- we might in future allow something like this, but for now it's an error: 1882 -- update xx1 set x2 = f1 from xx1, lateral (select * from int4_tbl where f1 = x1) ss; 1883 1884 -- also errors: 1885 -- delete from xx1 using (select * from int4_tbl where f1 = x1) ss; 1886 -- delete from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss; 1887 -- delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss; 1888 1889 -- [SPARK-25411] Implement range partition in Spark 1890 -- 1891 -- test LATERAL reference propagation down a multi-level inheritance hierarchy 1892 -- produced for a multi-level partitioned table hierarchy. 1893 -- 1894 -- create table join_pt1 (a int, b int, c varchar) partition by range(a); 1895 -- create table join_pt1p1 partition of join_pt1 for values from (0) to (100) partition by range(b); 1896 -- create table join_pt1p2 partition of join_pt1 for values from (100) to (200); 1897 -- create table join_pt1p1p1 partition of join_pt1p1 for values from (0) to (100); 1898 -- insert into join_pt1 values (1, 1, 'x'), (101, 101, 'y'); 1899 -- create table join_ut1 (a int, b int, c varchar); 1900 -- insert into join_ut1 values (101, 101, 'y'), (2, 2, 'z'); 1901 -- explain (verbose, costs off) 1902 -- select t1.b, ss.phv from join_ut1 t1 left join lateral 1903 -- (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv 1904 -- from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss 1905 -- on t1.a = ss.t2a order by t1.a; 1906 -- select t1.b, ss.phv from join_ut1 t1 left join lateral 1907 -- (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv 1908 -- from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss 1909 -- on t1.a = ss.t2a order by t1.a; 1910 -- 1911 -- drop table join_pt1; 1912 -- drop table join_ut1; 1913 -- 1914 -- test that foreign key join estimation performs sanely for outer joins 1915 -- 1916 1917 -- begin; 1918 1919 -- create table fkest (a int, b int, c int unique, primary key(a,b)); 1920 -- create table fkest1 (a int, b int, primary key(a,b)); 1921 1922 -- insert into fkest select x/10, x%10, x from generate_series(1,1000) x; 1923 -- insert into fkest1 select x/10, x%10 from generate_series(1,1000) x; 1924 1925 -- alter table fkest1 1926 -- add constraint fkest1_a_b_fkey foreign key (a,b) references fkest; 1927 1928 -- analyze fkest; 1929 -- analyze fkest1; 1930 1931 -- explain (costs off) 1932 -- select * 1933 -- from fkest f 1934 -- left join fkest1 f1 on f.a = f1.a and f.b = f1.b 1935 -- left join fkest1 f2 on f.a = f2.a and f.b = f2.b 1936 -- left join fkest1 f3 on f.a = f3.a and f.b = f3.b 1937 -- where f.c = 1; 1938 1939 -- rollback; 1940 1941 -- Skip these test because it only test explain 1942 -- 1943 -- test planner's ability to mark joins as unique 1944 -- 1945 1946 -- create table j1 (id int primary key); 1947 -- create table j2 (id int primary key); 1948 -- create table j3 (id int); 1949 1950 -- insert into j1 values(1),(2),(3); 1951 -- insert into j2 values(1),(2),(3); 1952 -- insert into j3 values(1),(1); 1953 1954 -- analyze j1; 1955 -- analyze j2; 1956 -- analyze j3; 1957 1958 -- ensure join is properly marked as unique 1959 -- explain (verbose, costs off) 1960 -- select * from j1 inner join j2 on j1.id = j2.id; 1961 1962 -- ensure join is not unique when not an equi-join 1963 -- explain (verbose, costs off) 1964 -- select * from j1 inner join j2 on j1.id > j2.id; 1965 1966 -- ensure non-unique rel is not chosen as inner 1967 -- explain (verbose, costs off) 1968 -- select * from j1 inner join j3 on j1.id = j3.id; 1969 1970 -- ensure left join is marked as unique 1971 -- explain (verbose, costs off) 1972 -- select * from j1 left join j2 on j1.id = j2.id; 1973 1974 -- ensure right join is marked as unique 1975 -- explain (verbose, costs off) 1976 -- select * from j1 right join j2 on j1.id = j2.id; 1977 1978 -- ensure full join is marked as unique 1979 -- explain (verbose, costs off) 1980 -- select * from j1 full join j2 on j1.id = j2.id; 1981 1982 -- a clauseless (cross) join can't be unique 1983 -- explain (verbose, costs off) 1984 -- select * from j1 cross join j2; 1985 1986 -- ensure a natural join is marked as unique 1987 -- explain (verbose, costs off) 1988 -- select * from j1 natural join j2; 1989 1990 -- ensure a distinct clause allows the inner to become unique 1991 -- explain (verbose, costs off) 1992 -- select * from j1 1993 -- inner join (select distinct id from j3) j3 on j1.id = j3.id; 1994 1995 -- ensure group by clause allows the inner to become unique 1996 -- explain (verbose, costs off) 1997 -- select * from j1 1998 -- inner join (select id from j3 group by id) j3 on j1.id = j3.id; 1999 2000 -- drop table if exists j1; 2001 -- drop table if exists j2; 2002 -- drop table if exists j3; 2003 2004 -- test more complex permutations of unique joins 2005 2006 CREATE TABLE j1 (id1 int, id2 int) USING parquet; 2007 CREATE TABLE j2 (id1 int, id2 int) USING parquet; 2008 -- create table j3 (id1 int, id2 int) using parquet; 2009 2010 INSERT INTO j1 values(1,1),(1,2); 2011 INSERT INTO j2 values(1,1); 2012 -- insert into j3 values(1,1); 2013 2014 -- analyze j1; 2015 -- analyze j2; 2016 -- analyze j3; 2017 2018 -- ensure there's no unique join when not all columns which are part of the 2019 -- unique index are seen in the join clause 2020 -- explain (verbose, costs off) 2021 -- select * from j1 2022 -- inner join j2 on j1.id1 = j2.id1; 2023 2024 -- ensure proper unique detection with multiple join quals 2025 -- explain (verbose, costs off) 2026 -- select * from j1 2027 -- inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2; 2028 2029 -- ensure we don't detect the join to be unique when quals are not part of the 2030 -- join condition 2031 -- explain (verbose, costs off) 2032 -- select * from j1 2033 -- inner join j2 on j1.id1 = j2.id1 where j1.id2 = 1; 2034 2035 -- as above, but for left joins. 2036 -- explain (verbose, costs off) 2037 -- select * from j1 2038 -- left join j2 on j1.id1 = j2.id1 where j1.id2 = 1; 2039 2040 -- validate logic in merge joins which skips mark and restore. 2041 -- it should only do this if all quals which were used to detect the unique 2042 -- are present as join quals, and not plain quals. 2043 -- set enable_nestloop to 0; 2044 -- set enable_hashjoin to 0; 2045 -- set enable_sort to 0; 2046 2047 -- create indexes that will be preferred over the PKs to perform the join 2048 -- create index j1_id1_idx on j1 (id1) where id1 % 1000 = 1; 2049 -- create index j2_id1_idx on j2 (id1) where id1 % 1000 = 1; 2050 2051 -- need an additional row in j2, if we want j2_id1_idx to be preferred 2052 INSERT INTO j2 values(1,2); 2053 -- analyze j2; 2054 2055 -- explain (costs off) select * from j1 2056 -- inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 2057 -- where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1; 2058 2059 select * from j1 2060 inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 2061 where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1; 2062 2063 -- reset enable_nestloop; 2064 -- reset enable_hashjoin; 2065 -- reset enable_sort; 2066 2067 drop table j1; 2068 drop table j2; 2069 -- drop table j3; 2070 2071 -- Skip these tests because it only test explain 2072 -- check that semijoin inner is not seen as unique for a portion of the outerrel 2073 -- explain (verbose, costs off) 2074 -- select t1.unique1, t2.hundred 2075 -- from onek t1, tenk1 t2 2076 -- where exists (select 1 from tenk1 t3 2077 -- where t3.thousand = t1.unique1 and t3.tenthous = t2.hundred) 2078 -- and t1.unique1 < 1; 2079 2080 -- ... unless it actually is unique 2081 -- create table j3 as select unique1, tenthous from onek; 2082 -- vacuum analyze j3; 2083 -- create unique index on j3(unique1, tenthous); 2084 2085 -- explain (verbose, costs off) 2086 -- select t1.unique1, t2.hundred 2087 -- from onek t1, tenk1 t2 2088 -- where exists (select 1 from j3 2089 -- where j3.unique1 = t1.unique1 and j3.tenthous = t2.hundred) 2090 -- and t1.unique1 < 1; 2091 2092 -- drop table j3;
[ Source navigation ] | [ Diff markup ] | [ Identifier search ] | [ general search ] |
This page was automatically generated by the 2.1.0 LXR engine. The LXR team |