1-- 2-- JOIN 3-- Test JOIN clauses 4-- 5 6CREATE TABLE J1_TBL ( 7 i integer, 8 j integer, 9 t text 10); 11 12CREATE TABLE J2_TBL ( 13 i integer, 14 k integer 15); 16 17 18INSERT INTO J1_TBL VALUES (1, 4, 'one'); 19INSERT INTO J1_TBL VALUES (2, 3, 'two'); 20INSERT INTO J1_TBL VALUES (3, 2, 'three'); 21INSERT INTO J1_TBL VALUES (4, 1, 'four'); 22INSERT INTO J1_TBL VALUES (5, 0, 'five'); 23INSERT INTO J1_TBL VALUES (6, 6, 'six'); 24INSERT INTO J1_TBL VALUES (7, 7, 'seven'); 25INSERT INTO J1_TBL VALUES (8, 8, 'eight'); 26INSERT INTO J1_TBL VALUES (0, NULL, 'zero'); 27INSERT INTO J1_TBL VALUES (NULL, NULL, 'null'); 28INSERT INTO J1_TBL VALUES (NULL, 0, 'zero'); 29 30INSERT INTO J2_TBL VALUES (1, -1); 31INSERT INTO J2_TBL VALUES (2, 2); 32INSERT INTO J2_TBL VALUES (3, -3); 33INSERT INTO J2_TBL VALUES (2, 4); 34INSERT INTO J2_TBL VALUES (5, -5); 35INSERT INTO J2_TBL VALUES (5, -5); 36INSERT INTO J2_TBL VALUES (0, NULL); 37INSERT INTO J2_TBL VALUES (NULL, NULL); 38INSERT INTO J2_TBL VALUES (NULL, 0); 39 40-- useful in some tests below 41create temp table onerow(); 42insert into onerow default values; 43analyze onerow; 44 45 46-- 47-- CORRELATION NAMES 48-- Make sure that table/column aliases are supported 49-- before diving into more complex join syntax. 50-- 51 52SELECT * 53 FROM J1_TBL AS tx; 54 55SELECT * 56 FROM J1_TBL tx; 57 58SELECT * 59 FROM J1_TBL AS t1 (a, b, c); 60 61SELECT * 62 FROM J1_TBL t1 (a, b, c); 63 64SELECT * 65 FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e); 66 67SELECT t1.a, t2.e 68 FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e) 69 WHERE t1.a = t2.d; 70 71 72-- 73-- CROSS JOIN 74-- Qualifications are not allowed on cross joins, 75-- which degenerate into a standard unqualified inner join. 76-- 77 78SELECT * 79 FROM J1_TBL CROSS JOIN J2_TBL; 80 81-- ambiguous column 82SELECT i, k, t 83 FROM J1_TBL CROSS JOIN J2_TBL; 84 85-- resolve previous ambiguity by specifying the table name 86SELECT t1.i, k, t 87 FROM J1_TBL t1 CROSS JOIN J2_TBL t2; 88 89SELECT ii, tt, kk 90 FROM (J1_TBL CROSS JOIN J2_TBL) 91 AS tx (ii, jj, tt, ii2, kk); 92 93SELECT tx.ii, tx.jj, tx.kk 94 FROM (J1_TBL t1 (a, b, c) CROSS JOIN J2_TBL t2 (d, e)) 95 AS tx (ii, jj, tt, ii2, kk); 96 97SELECT * 98 FROM J1_TBL CROSS JOIN J2_TBL a CROSS JOIN J2_TBL b; 99 100 101-- 102-- 103-- Inner joins (equi-joins) 104-- 105-- 106 107-- 108-- Inner joins (equi-joins) with USING clause 109-- The USING syntax changes the shape of the resulting table 110-- by including a column in the USING clause only once in the result. 111-- 112 113-- Inner equi-join on specified column 114SELECT * 115 FROM J1_TBL INNER JOIN J2_TBL USING (i); 116 117-- Same as above, slightly different syntax 118SELECT * 119 FROM J1_TBL JOIN J2_TBL USING (i); 120 121SELECT * 122 FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, d) USING (a) 123 ORDER BY a, d; 124 125SELECT * 126 FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b) 127 ORDER BY b, t1.a; 128 129-- test join using aliases 130SELECT * FROM J1_TBL JOIN J2_TBL USING (i) WHERE J1_TBL.t = 'one'; -- ok 131SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one'; -- ok 132SELECT * FROM (J1_TBL JOIN J2_TBL USING (i)) AS x WHERE J1_TBL.t = 'one'; -- error 133SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.i = 1; -- ok 134SELECT * FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE x.t = 'one'; -- error 135SELECT * FROM (J1_TBL JOIN J2_TBL USING (i) AS x) AS xx WHERE x.i = 1; -- error (XXX could use better hint) 136SELECT * FROM J1_TBL a1 JOIN J2_TBL a2 USING (i) AS a1; -- error 137SELECT x.* FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one'; 138SELECT ROW(x.*) FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one'; 139SELECT row_to_json(x.*) FROM J1_TBL JOIN J2_TBL USING (i) AS x WHERE J1_TBL.t = 'one'; 140 141-- 142-- NATURAL JOIN 143-- Inner equi-join on all columns with the same name 144-- 145 146SELECT * 147 FROM J1_TBL NATURAL JOIN J2_TBL; 148 149SELECT * 150 FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d); 151 152SELECT * 153 FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a); 154 155-- mismatch number of columns 156-- currently, Postgres will fill in with underlying names 157SELECT * 158 FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a); 159 160 161-- 162-- Inner joins (equi-joins) 163-- 164 165SELECT * 166 FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.i); 167 168SELECT * 169 FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = J2_TBL.k); 170 171 172-- 173-- Non-equi-joins 174-- 175 176SELECT * 177 FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i <= J2_TBL.k); 178 179 180-- 181-- Outer joins 182-- Note that OUTER is a noise word 183-- 184 185SELECT * 186 FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i) 187 ORDER BY i, k, t; 188 189SELECT * 190 FROM J1_TBL LEFT JOIN J2_TBL USING (i) 191 ORDER BY i, k, t; 192 193SELECT * 194 FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i); 195 196SELECT * 197 FROM J1_TBL RIGHT JOIN J2_TBL USING (i); 198 199SELECT * 200 FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i) 201 ORDER BY i, k, t; 202 203SELECT * 204 FROM J1_TBL FULL JOIN J2_TBL USING (i) 205 ORDER BY i, k, t; 206 207SELECT * 208 FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1); 209 210SELECT * 211 FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1); 212 213-- 214-- semijoin selectivity for <> 215-- 216explain (costs off) 217select * from int4_tbl i4, tenk1 a 218where exists(select * from tenk1 b 219 where a.twothousand = b.twothousand and a.fivethous <> b.fivethous) 220 and i4.f1 = a.tenthous; 221 222 223-- 224-- More complicated constructs 225-- 226 227-- 228-- Multiway full join 229-- 230 231CREATE TABLE t1 (name TEXT, n INTEGER); 232CREATE TABLE t2 (name TEXT, n INTEGER); 233CREATE TABLE t3 (name TEXT, n INTEGER); 234 235INSERT INTO t1 VALUES ( 'bb', 11 ); 236INSERT INTO t2 VALUES ( 'bb', 12 ); 237INSERT INTO t2 VALUES ( 'cc', 22 ); 238INSERT INTO t2 VALUES ( 'ee', 42 ); 239INSERT INTO t3 VALUES ( 'bb', 13 ); 240INSERT INTO t3 VALUES ( 'cc', 23 ); 241INSERT INTO t3 VALUES ( 'dd', 33 ); 242 243SELECT * FROM t1 FULL JOIN t2 USING (name) FULL JOIN t3 USING (name); 244 245-- 246-- Test interactions of join syntax and subqueries 247-- 248 249-- Basic cases (we expect planner to pull up the subquery here) 250SELECT * FROM 251(SELECT * FROM t2) as s2 252INNER JOIN 253(SELECT * FROM t3) s3 254USING (name); 255 256SELECT * FROM 257(SELECT * FROM t2) as s2 258LEFT JOIN 259(SELECT * FROM t3) s3 260USING (name); 261 262SELECT * FROM 263(SELECT * FROM t2) as s2 264FULL JOIN 265(SELECT * FROM t3) s3 266USING (name); 267 268-- Cases with non-nullable expressions in subquery results; 269-- make sure these go to null as expected 270SELECT * FROM 271(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 272NATURAL INNER JOIN 273(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; 274 275SELECT * FROM 276(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 277NATURAL LEFT JOIN 278(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; 279 280SELECT * FROM 281(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 282NATURAL FULL JOIN 283(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; 284 285SELECT * FROM 286(SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1 287NATURAL INNER JOIN 288(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 289NATURAL INNER JOIN 290(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; 291 292SELECT * FROM 293(SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1 294NATURAL FULL JOIN 295(SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 296NATURAL FULL JOIN 297(SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3; 298 299SELECT * FROM 300(SELECT name, n as s1_n FROM t1) as s1 301NATURAL FULL JOIN 302 (SELECT * FROM 303 (SELECT name, n as s2_n FROM t2) as s2 304 NATURAL FULL JOIN 305 (SELECT name, n as s3_n FROM t3) as s3 306 ) ss2; 307 308SELECT * FROM 309(SELECT name, n as s1_n FROM t1) as s1 310NATURAL FULL JOIN 311 (SELECT * FROM 312 (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 313 NATURAL FULL JOIN 314 (SELECT name, n as s3_n FROM t3) as s3 315 ) ss2; 316 317-- Constants as join keys can also be problematic 318SELECT * FROM 319 (SELECT name, n as s1_n FROM t1) as s1 320FULL JOIN 321 (SELECT name, 2 as s2_n FROM t2) as s2 322ON (s1_n = s2_n); 323 324 325-- Test for propagation of nullability constraints into sub-joins 326 327create temp table x (x1 int, x2 int); 328insert into x values (1,11); 329insert into x values (2,22); 330insert into x values (3,null); 331insert into x values (4,44); 332insert into x values (5,null); 333 334create temp table y (y1 int, y2 int); 335insert into y values (1,111); 336insert into y values (2,222); 337insert into y values (3,333); 338insert into y values (4,null); 339 340select * from x; 341select * from y; 342 343select * from x left join y on (x1 = y1 and x2 is not null); 344select * from x left join y on (x1 = y1 and y2 is not null); 345 346select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) 347on (x1 = xx1); 348select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) 349on (x1 = xx1 and x2 is not null); 350select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) 351on (x1 = xx1 and y2 is not null); 352select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) 353on (x1 = xx1 and xx2 is not null); 354-- these should NOT give the same answers as above 355select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) 356on (x1 = xx1) where (x2 is not null); 357select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) 358on (x1 = xx1) where (y2 is not null); 359select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) 360on (x1 = xx1) where (xx2 is not null); 361 362-- 363-- regression test: check for bug with propagation of implied equality 364-- to outside an IN 365-- 366select count(*) from tenk1 a where unique1 in 367 (select unique1 from tenk1 b join tenk1 c using (unique1) 368 where b.unique2 = 42); 369 370-- 371-- regression test: check for failure to generate a plan with multiple 372-- degenerate IN clauses 373-- 374select count(*) from tenk1 x where 375 x.unique1 in (select a.f1 from int4_tbl a,float8_tbl b where a.f1=b.f1) and 376 x.unique1 = 0 and 377 x.unique1 in (select aa.f1 from int4_tbl aa,float8_tbl bb where aa.f1=bb.f1); 378 379-- try that with GEQO too 380begin; 381set geqo = on; 382set geqo_threshold = 2; 383select count(*) from tenk1 x where 384 x.unique1 in (select a.f1 from int4_tbl a,float8_tbl b where a.f1=b.f1) and 385 x.unique1 = 0 and 386 x.unique1 in (select aa.f1 from int4_tbl aa,float8_tbl bb where aa.f1=bb.f1); 387rollback; 388 389-- 390-- regression test: be sure we cope with proven-dummy append rels 391-- 392explain (costs off) 393select aa, bb, unique1, unique1 394 from tenk1 right join b on aa = unique1 395 where bb < bb and bb is null; 396 397select aa, bb, unique1, unique1 398 from tenk1 right join b on aa = unique1 399 where bb < bb and bb is null; 400 401-- 402-- regression test: check handling of empty-FROM subquery underneath outer join 403-- 404explain (costs off) 405select * from int8_tbl i1 left join (int8_tbl i2 join 406 (select 123 as x) ss on i2.q1 = x) on i1.q2 = i2.q2 407order by 1, 2; 408 409select * from int8_tbl i1 left join (int8_tbl i2 join 410 (select 123 as x) ss on i2.q1 = x) on i1.q2 = i2.q2 411order by 1, 2; 412 413-- 414-- regression test: check a case where join_clause_is_movable_into() gives 415-- an imprecise result, causing an assertion failure 416-- 417select count(*) 418from 419 (select t3.tenthous as x1, coalesce(t1.stringu1, t2.stringu1) as x2 420 from tenk1 t1 421 left join tenk1 t2 on t1.unique1 = t2.unique1 422 join tenk1 t3 on t1.unique2 = t3.unique2) ss, 423 tenk1 t4, 424 tenk1 t5 425where t4.thousand = t5.unique1 and ss.x1 = t4.tenthous and ss.x2 = t5.stringu1; 426 427-- 428-- regression test: check a case where we formerly missed including an EC 429-- enforcement clause because it was expected to be handled at scan level 430-- 431explain (costs off) 432select a.f1, b.f1, t.thousand, t.tenthous from 433 tenk1 t, 434 (select sum(f1)+1 as f1 from int4_tbl i4a) a, 435 (select sum(f1) as f1 from int4_tbl i4b) b 436where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous; 437 438select a.f1, b.f1, t.thousand, t.tenthous from 439 tenk1 t, 440 (select sum(f1)+1 as f1 from int4_tbl i4a) a, 441 (select sum(f1) as f1 from int4_tbl i4b) b 442where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous; 443 444-- 445-- check a case where we formerly got confused by conflicting sort orders 446-- in redundant merge join path keys 447-- 448explain (costs off) 449select * from 450 j1_tbl full join 451 (select * from j2_tbl order by j2_tbl.i desc, j2_tbl.k asc) j2_tbl 452 on j1_tbl.i = j2_tbl.i and j1_tbl.i = j2_tbl.k; 453 454select * from 455 j1_tbl full join 456 (select * from j2_tbl order by j2_tbl.i desc, j2_tbl.k asc) j2_tbl 457 on j1_tbl.i = j2_tbl.i and j1_tbl.i = j2_tbl.k; 458 459-- 460-- a different check for handling of redundant sort keys in merge joins 461-- 462explain (costs off) 463select count(*) from 464 (select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x 465 left join 466 (select * from tenk1 y order by y.unique2) y 467 on x.thousand = y.unique2 and x.twothousand = y.hundred and x.fivethous = y.unique2; 468 469select count(*) from 470 (select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x 471 left join 472 (select * from tenk1 y order by y.unique2) y 473 on x.thousand = y.unique2 and x.twothousand = y.hundred and x.fivethous = y.unique2; 474 475 476-- 477-- Clean up 478-- 479 480DROP TABLE t1; 481DROP TABLE t2; 482DROP TABLE t3; 483 484DROP TABLE J1_TBL; 485DROP TABLE J2_TBL; 486 487-- Both DELETE and UPDATE allow the specification of additional tables 488-- to "join" against to determine which rows should be modified. 489 490CREATE TEMP TABLE t1 (a int, b int); 491CREATE TEMP TABLE t2 (a int, b int); 492CREATE TEMP TABLE t3 (x int, y int); 493 494INSERT INTO t1 VALUES (5, 10); 495INSERT INTO t1 VALUES (15, 20); 496INSERT INTO t1 VALUES (100, 100); 497INSERT INTO t1 VALUES (200, 1000); 498INSERT INTO t2 VALUES (200, 2000); 499INSERT INTO t3 VALUES (5, 20); 500INSERT INTO t3 VALUES (6, 7); 501INSERT INTO t3 VALUES (7, 8); 502INSERT INTO t3 VALUES (500, 100); 503 504DELETE FROM t3 USING t1 table1 WHERE t3.x = table1.a; 505SELECT * FROM t3; 506DELETE FROM t3 USING t1 JOIN t2 USING (a) WHERE t3.x > t1.a; 507SELECT * FROM t3; 508DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y; 509SELECT * FROM t3; 510 511-- Test join against inheritance tree 512 513create temp table t2a () inherits (t2); 514 515insert into t2a values (200, 2001); 516 517select * from t1 left join t2 on (t1.a = t2.a); 518 519-- Test matching of column name with wrong alias 520 521select t1.x from t1 join t3 on (t1.a = t3.x); 522 523-- 524-- regression test for 8.1 merge right join bug 525-- 526 527CREATE TEMP TABLE tt1 ( tt1_id int4, joincol int4 ); 528INSERT INTO tt1 VALUES (1, 11); 529INSERT INTO tt1 VALUES (2, NULL); 530 531CREATE TEMP TABLE tt2 ( tt2_id int4, joincol int4 ); 532INSERT INTO tt2 VALUES (21, 11); 533INSERT INTO tt2 VALUES (22, 11); 534 535set enable_hashjoin to off; 536set enable_nestloop to off; 537 538-- these should give the same results 539 540select tt1.*, tt2.* from tt1 left join tt2 on tt1.joincol = tt2.joincol; 541 542select tt1.*, tt2.* from tt2 right join tt1 on tt1.joincol = tt2.joincol; 543 544reset enable_hashjoin; 545reset enable_nestloop; 546 547-- 548-- regression test for bug #13908 (hash join with skew tuples & nbatch increase) 549-- 550 551set work_mem to '64kB'; 552set enable_mergejoin to off; 553set enable_memoize to off; 554 555explain (costs off) 556select count(*) from tenk1 a, tenk1 b 557 where a.hundred = b.thousand and (b.fivethous % 10) < 10; 558select count(*) from tenk1 a, tenk1 b 559 where a.hundred = b.thousand and (b.fivethous % 10) < 10; 560 561reset work_mem; 562reset enable_mergejoin; 563reset enable_memoize; 564 565-- 566-- regression test for 8.2 bug with improper re-ordering of left joins 567-- 568 569create temp table tt3(f1 int, f2 text); 570insert into tt3 select x, repeat('xyzzy', 100) from generate_series(1,10000) x; 571create index tt3i on tt3(f1); 572analyze tt3; 573 574create temp table tt4(f1 int); 575insert into tt4 values (0),(1),(9999); 576analyze tt4; 577 578SELECT a.f1 579FROM tt4 a 580LEFT JOIN ( 581 SELECT b.f1 582 FROM tt3 b LEFT JOIN tt3 c ON (b.f1 = c.f1) 583 WHERE c.f1 IS NULL 584) AS d ON (a.f1 = d.f1) 585WHERE d.f1 IS NULL; 586 587-- 588-- regression test for proper handling of outer joins within antijoins 589-- 590 591create temp table tt4x(c1 int, c2 int, c3 int); 592 593explain (costs off) 594select * from tt4x t1 595where not exists ( 596 select 1 from tt4x t2 597 left join tt4x t3 on t2.c3 = t3.c1 598 left join ( select t5.c1 as c1 599 from tt4x t4 left join tt4x t5 on t4.c2 = t5.c1 600 ) a1 on t3.c2 = a1.c1 601 where t1.c1 = t2.c2 602); 603 604-- 605-- regression test for problems of the sort depicted in bug #3494 606-- 607 608create temp table tt5(f1 int, f2 int); 609create temp table tt6(f1 int, f2 int); 610 611insert into tt5 values(1, 10); 612insert into tt5 values(1, 11); 613 614insert into tt6 values(1, 9); 615insert into tt6 values(1, 2); 616insert into tt6 values(2, 9); 617 618select * from tt5,tt6 where tt5.f1 = tt6.f1 and tt5.f1 = tt5.f2 - tt6.f2; 619 620-- 621-- regression test for problems of the sort depicted in bug #3588 622-- 623 624create temp table xx (pkxx int); 625create temp table yy (pkyy int, pkxx int); 626 627insert into xx values (1); 628insert into xx values (2); 629insert into xx values (3); 630 631insert into yy values (101, 1); 632insert into yy values (201, 2); 633insert into yy values (301, NULL); 634 635select yy.pkyy as yy_pkyy, yy.pkxx as yy_pkxx, yya.pkyy as yya_pkyy, 636 xxa.pkxx as xxa_pkxx, xxb.pkxx as xxb_pkxx 637from yy 638 left join (SELECT * FROM yy where pkyy = 101) as yya ON yy.pkyy = yya.pkyy 639 left join xx xxa on yya.pkxx = xxa.pkxx 640 left join xx xxb on coalesce (xxa.pkxx, 1) = xxb.pkxx; 641 642-- 643-- regression test for improper pushing of constants across outer-join clauses 644-- (as seen in early 8.2.x releases) 645-- 646 647create temp table zt1 (f1 int primary key); 648create temp table zt2 (f2 int primary key); 649create temp table zt3 (f3 int primary key); 650insert into zt1 values(53); 651insert into zt2 values(53); 652 653select * from 654 zt2 left join zt3 on (f2 = f3) 655 left join zt1 on (f3 = f1) 656where f2 = 53; 657 658create temp view zv1 as select *,'dummy'::text AS junk from zt1; 659 660select * from 661 zt2 left join zt3 on (f2 = f3) 662 left join zv1 on (f3 = f1) 663where f2 = 53; 664 665-- 666-- regression test for improper extraction of OR indexqual conditions 667-- (as seen in early 8.3.x releases) 668-- 669 670select a.unique2, a.ten, b.tenthous, b.unique2, b.hundred 671from tenk1 a left join tenk1 b on a.unique2 = b.tenthous 672where a.unique1 = 42 and 673 ((b.unique2 is null and a.ten = 2) or b.hundred = 3); 674 675-- 676-- test proper positioning of one-time quals in EXISTS (8.4devel bug) 677-- 678prepare foo(bool) as 679 select count(*) from tenk1 a left join tenk1 b 680 on (a.unique2 = b.unique1 and exists 681 (select 1 from tenk1 c where c.thousand = b.unique2 and $1)); 682execute foo(true); 683execute foo(false); 684 685-- 686-- test for sane behavior with noncanonical merge clauses, per bug #4926 687-- 688 689begin; 690 691set enable_mergejoin = 1; 692set enable_hashjoin = 0; 693set enable_nestloop = 0; 694 695create temp table a (i integer); 696create temp table b (x integer, y integer); 697 698select * from a left join b on i = x and i = y and x = i; 699 700rollback; 701 702-- 703-- test handling of merge clauses using record_ops 704-- 705begin; 706 707create type mycomptype as (id int, v bigint); 708 709create temp table tidv (idv mycomptype); 710create index on tidv (idv); 711 712explain (costs off) 713select a.idv, b.idv from tidv a, tidv b where a.idv = b.idv; 714 715set enable_mergejoin = 0; 716set enable_hashjoin = 0; 717 718explain (costs off) 719select a.idv, b.idv from tidv a, tidv b where a.idv = b.idv; 720 721rollback; 722 723-- 724-- test NULL behavior of whole-row Vars, per bug #5025 725-- 726select t1.q2, count(t2.*) 727from int8_tbl t1 left join int8_tbl t2 on (t1.q2 = t2.q1) 728group by t1.q2 order by 1; 729 730select t1.q2, count(t2.*) 731from int8_tbl t1 left join (select * from int8_tbl) t2 on (t1.q2 = t2.q1) 732group by t1.q2 order by 1; 733 734select t1.q2, count(t2.*) 735from int8_tbl t1 left join (select * from int8_tbl offset 0) t2 on (t1.q2 = t2.q1) 736group by t1.q2 order by 1; 737 738select t1.q2, count(t2.*) 739from int8_tbl t1 left join 740 (select q1, case when q2=1 then 1 else q2 end as q2 from int8_tbl) t2 741 on (t1.q2 = t2.q1) 742group by t1.q2 order by 1; 743 744-- 745-- test incorrect failure to NULL pulled-up subexpressions 746-- 747begin; 748 749create temp table a ( 750 code char not null, 751 constraint a_pk primary key (code) 752); 753create temp table b ( 754 a char not null, 755 num integer not null, 756 constraint b_pk primary key (a, num) 757); 758create temp table c ( 759 name char not null, 760 a char, 761 constraint c_pk primary key (name) 762); 763 764insert into a (code) values ('p'); 765insert into a (code) values ('q'); 766insert into b (a, num) values ('p', 1); 767insert into b (a, num) values ('p', 2); 768insert into c (name, a) values ('A', 'p'); 769insert into c (name, a) values ('B', 'q'); 770insert into c (name, a) values ('C', null); 771 772select c.name, ss.code, ss.b_cnt, ss.const 773from c left join 774 (select a.code, coalesce(b_grp.cnt, 0) as b_cnt, -1 as const 775 from a left join 776 (select count(1) as cnt, b.a from b group by b.a) as b_grp 777 on a.code = b_grp.a 778 ) as ss 779 on (c.a = ss.code) 780order by c.name; 781 782rollback; 783 784-- 785-- test incorrect handling of placeholders that only appear in targetlists, 786-- per bug #6154 787-- 788SELECT * FROM 789( SELECT 1 as key1 ) sub1 790LEFT JOIN 791( SELECT sub3.key3, sub4.value2, COALESCE(sub4.value2, 66) as value3 FROM 792 ( SELECT 1 as key3 ) sub3 793 LEFT JOIN 794 ( SELECT sub5.key5, COALESCE(sub6.value1, 1) as value2 FROM 795 ( SELECT 1 as key5 ) sub5 796 LEFT JOIN 797 ( SELECT 2 as key6, 42 as value1 ) sub6 798 ON sub5.key5 = sub6.key6 799 ) sub4 800 ON sub4.key5 = sub3.key3 801) sub2 802ON sub1.key1 = sub2.key3; 803 804-- test the path using join aliases, too 805SELECT * FROM 806( SELECT 1 as key1 ) sub1 807LEFT JOIN 808( SELECT sub3.key3, value2, COALESCE(value2, 66) as value3 FROM 809 ( SELECT 1 as key3 ) sub3 810 LEFT JOIN 811 ( SELECT sub5.key5, COALESCE(sub6.value1, 1) as value2 FROM 812 ( SELECT 1 as key5 ) sub5 813 LEFT JOIN 814 ( SELECT 2 as key6, 42 as value1 ) sub6 815 ON sub5.key5 = sub6.key6 816 ) sub4 817 ON sub4.key5 = sub3.key3 818) sub2 819ON sub1.key1 = sub2.key3; 820 821-- 822-- test case where a PlaceHolderVar is used as a nestloop parameter 823-- 824 825EXPLAIN (COSTS OFF) 826SELECT qq, unique1 827 FROM 828 ( SELECT COALESCE(q1, 0) AS qq FROM int8_tbl a ) AS ss1 829 FULL OUTER JOIN 830 ( SELECT COALESCE(q2, -1) AS qq FROM int8_tbl b ) AS ss2 831 USING (qq) 832 INNER JOIN tenk1 c ON qq = unique2; 833 834SELECT qq, unique1 835 FROM 836 ( SELECT COALESCE(q1, 0) AS qq FROM int8_tbl a ) AS ss1 837 FULL OUTER JOIN 838 ( SELECT COALESCE(q2, -1) AS qq FROM int8_tbl b ) AS ss2 839 USING (qq) 840 INNER JOIN tenk1 c ON qq = unique2; 841 842-- 843-- nested nestloops can require nested PlaceHolderVars 844-- 845 846create temp table nt1 ( 847 id int primary key, 848 a1 boolean, 849 a2 boolean 850); 851create temp table nt2 ( 852 id int primary key, 853 nt1_id int, 854 b1 boolean, 855 b2 boolean, 856 foreign key (nt1_id) references nt1(id) 857); 858create temp table nt3 ( 859 id int primary key, 860 nt2_id int, 861 c1 boolean, 862 foreign key (nt2_id) references nt2(id) 863); 864 865insert into nt1 values (1,true,true); 866insert into nt1 values (2,true,false); 867insert into nt1 values (3,false,false); 868insert into nt2 values (1,1,true,true); 869insert into nt2 values (2,2,true,false); 870insert into nt2 values (3,3,false,false); 871insert into nt3 values (1,1,true); 872insert into nt3 values (2,2,false); 873insert into nt3 values (3,3,true); 874 875explain (costs off) 876select nt3.id 877from nt3 as nt3 878 left join 879 (select nt2.*, (nt2.b1 and ss1.a3) AS b3 880 from nt2 as nt2 881 left join 882 (select nt1.*, (nt1.id is not null) as a3 from nt1) as ss1 883 on ss1.id = nt2.nt1_id 884 ) as ss2 885 on ss2.id = nt3.nt2_id 886where nt3.id = 1 and ss2.b3; 887 888select nt3.id 889from nt3 as nt3 890 left join 891 (select nt2.*, (nt2.b1 and ss1.a3) AS b3 892 from nt2 as nt2 893 left join 894 (select nt1.*, (nt1.id is not null) as a3 from nt1) as ss1 895 on ss1.id = nt2.nt1_id 896 ) as ss2 897 on ss2.id = nt3.nt2_id 898where nt3.id = 1 and ss2.b3; 899 900-- 901-- test case where a PlaceHolderVar is propagated into a subquery 902-- 903 904explain (costs off) 905select * from 906 int8_tbl t1 left join 907 (select q1 as x, 42 as y from int8_tbl t2) ss 908 on t1.q2 = ss.x 909where 910 1 = (select 1 from int8_tbl t3 where ss.y is not null limit 1) 911order by 1,2; 912 913select * from 914 int8_tbl t1 left join 915 (select q1 as x, 42 as y from int8_tbl t2) ss 916 on t1.q2 = ss.x 917where 918 1 = (select 1 from int8_tbl t3 where ss.y is not null limit 1) 919order by 1,2; 920 921-- 922-- variant where a PlaceHolderVar is needed at a join, but not above the join 923-- 924 925explain (costs off) 926select * from 927 int4_tbl as i41, 928 lateral 929 (select 1 as x from 930 (select i41.f1 as lat, 931 i42.f1 as loc from 932 int8_tbl as i81, int4_tbl as i42) as ss1 933 right join int4_tbl as i43 on (i43.f1 > 1) 934 where ss1.loc = ss1.lat) as ss2 935where i41.f1 > 0; 936 937select * from 938 int4_tbl as i41, 939 lateral 940 (select 1 as x from 941 (select i41.f1 as lat, 942 i42.f1 as loc from 943 int8_tbl as i81, int4_tbl as i42) as ss1 944 right join int4_tbl as i43 on (i43.f1 > 1) 945 where ss1.loc = ss1.lat) as ss2 946where i41.f1 > 0; 947 948-- 949-- test the corner cases FULL JOIN ON TRUE and FULL JOIN ON FALSE 950-- 951select * from int4_tbl a full join int4_tbl b on true; 952select * from int4_tbl a full join int4_tbl b on false; 953 954-- 955-- test for ability to use a cartesian join when necessary 956-- 957 958create temp table q1 as select 1 as q1; 959create temp table q2 as select 0 as q2; 960analyze q1; 961analyze q2; 962 963explain (costs off) 964select * from 965 tenk1 join int4_tbl on f1 = twothousand, 966 q1, q2 967where q1 = thousand or q2 = thousand; 968 969explain (costs off) 970select * from 971 tenk1 join int4_tbl on f1 = twothousand, 972 q1, q2 973where thousand = (q1 + q2); 974 975-- 976-- test ability to generate a suitable plan for a star-schema query 977-- 978 979explain (costs off) 980select * from 981 tenk1, int8_tbl a, int8_tbl b 982where thousand = a.q1 and tenthous = b.q1 and a.q2 = 1 and b.q2 = 2; 983 984-- 985-- test a corner case in which we shouldn't apply the star-schema optimization 986-- 987 988explain (costs off) 989select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from 990 tenk1 t1 991 inner join int4_tbl i1 992 left join (select v1.x2, v2.y1, 11 AS d1 993 from (select 1,0 from onerow) v1(x1,x2) 994 left join (select 3,1 from onerow) v2(y1,y2) 995 on v1.x1 = v2.y2) subq1 996 on (i1.f1 = subq1.x2) 997 on (t1.unique2 = subq1.d1) 998 left join tenk1 t2 999 on (subq1.y1 = t2.unique1) 1000where t1.unique2 < 42 and t1.stringu1 > t2.stringu2; 1001 1002select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from 1003 tenk1 t1 1004 inner join int4_tbl i1 1005 left join (select v1.x2, v2.y1, 11 AS d1 1006 from (select 1,0 from onerow) v1(x1,x2) 1007 left join (select 3,1 from onerow) v2(y1,y2) 1008 on v1.x1 = v2.y2) subq1 1009 on (i1.f1 = subq1.x2) 1010 on (t1.unique2 = subq1.d1) 1011 left join tenk1 t2 1012 on (subq1.y1 = t2.unique1) 1013where t1.unique2 < 42 and t1.stringu1 > t2.stringu2; 1014 1015-- variant that isn't quite a star-schema case 1016 1017select ss1.d1 from 1018 tenk1 as t1 1019 inner join tenk1 as t2 1020 on t1.tenthous = t2.ten 1021 inner join 1022 int8_tbl as i8 1023 left join int4_tbl as i4 1024 inner join (select 64::information_schema.cardinal_number as d1 1025 from tenk1 t3, 1026 lateral (select abs(t3.unique1) + random()) ss0(x) 1027 where t3.fivethous < 0) as ss1 1028 on i4.f1 = ss1.d1 1029 on i8.q1 = i4.f1 1030 on t1.tenthous = ss1.d1 1031where t1.unique1 < i4.f1; 1032 1033-- this variant is foldable by the remove-useless-RESULT-RTEs code 1034 1035explain (costs off) 1036select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from 1037 tenk1 t1 1038 inner join int4_tbl i1 1039 left join (select v1.x2, v2.y1, 11 AS d1 1040 from (values(1,0)) v1(x1,x2) 1041 left join (values(3,1)) v2(y1,y2) 1042 on v1.x1 = v2.y2) subq1 1043 on (i1.f1 = subq1.x2) 1044 on (t1.unique2 = subq1.d1) 1045 left join tenk1 t2 1046 on (subq1.y1 = t2.unique1) 1047where t1.unique2 < 42 and t1.stringu1 > t2.stringu2; 1048 1049select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from 1050 tenk1 t1 1051 inner join int4_tbl i1 1052 left join (select v1.x2, v2.y1, 11 AS d1 1053 from (values(1,0)) v1(x1,x2) 1054 left join (values(3,1)) v2(y1,y2) 1055 on v1.x1 = v2.y2) subq1 1056 on (i1.f1 = subq1.x2) 1057 on (t1.unique2 = subq1.d1) 1058 left join tenk1 t2 1059 on (subq1.y1 = t2.unique1) 1060where t1.unique2 < 42 and t1.stringu1 > t2.stringu2; 1061 1062-- Here's a variant that we can't fold too aggressively, though, 1063-- or we end up with noplace to evaluate the lateral PHV 1064explain (verbose, costs off) 1065select * from 1066 (select 1 as x) ss1 left join (select 2 as y) ss2 on (true), 1067 lateral (select ss2.y as z limit 1) ss3; 1068select * from 1069 (select 1 as x) ss1 left join (select 2 as y) ss2 on (true), 1070 lateral (select ss2.y as z limit 1) ss3; 1071 1072-- Test proper handling of appendrel PHVs during useless-RTE removal 1073explain (costs off) 1074select * from 1075 (select 0 as z) as t1 1076 left join 1077 (select true as a) as t2 1078 on true, 1079 lateral (select true as b 1080 union all 1081 select a as b) as t3 1082where b; 1083 1084select * from 1085 (select 0 as z) as t1 1086 left join 1087 (select true as a) as t2 1088 on true, 1089 lateral (select true as b 1090 union all 1091 select a as b) as t3 1092where b; 1093 1094-- 1095-- test inlining of immutable functions 1096-- 1097create function f_immutable_int4(i integer) returns integer as 1098$$ begin return i; end; $$ language plpgsql immutable; 1099 1100-- check optimization of function scan with join 1101explain (costs off) 1102select unique1 from tenk1, (select * from f_immutable_int4(1) x) x 1103where x = unique1; 1104 1105explain (verbose, costs off) 1106select unique1, x.* 1107from tenk1, (select *, random() from f_immutable_int4(1) x) x 1108where x = unique1; 1109 1110explain (costs off) 1111select unique1 from tenk1, f_immutable_int4(1) x where x = unique1; 1112 1113explain (costs off) 1114select unique1 from tenk1, lateral f_immutable_int4(1) x where x = unique1; 1115 1116explain (costs off) 1117select unique1 from tenk1, lateral f_immutable_int4(1) x where x in (select 17); 1118 1119explain (costs off) 1120select unique1, x from tenk1 join f_immutable_int4(1) x on unique1 = x; 1121 1122explain (costs off) 1123select unique1, x from tenk1 left join f_immutable_int4(1) x on unique1 = x; 1124 1125explain (costs off) 1126select unique1, x from tenk1 right join f_immutable_int4(1) x on unique1 = x; 1127 1128explain (costs off) 1129select unique1, x from tenk1 full join f_immutable_int4(1) x on unique1 = x; 1130 1131-- check that pullup of a const function allows further const-folding 1132explain (costs off) 1133select unique1 from tenk1, f_immutable_int4(1) x where x = 42; 1134 1135-- test inlining of immutable functions with PlaceHolderVars 1136explain (costs off) 1137select nt3.id 1138from nt3 as nt3 1139 left join 1140 (select nt2.*, (nt2.b1 or i4 = 42) AS b3 1141 from nt2 as nt2 1142 left join 1143 f_immutable_int4(0) i4 1144 on i4 = nt2.nt1_id 1145 ) as ss2 1146 on ss2.id = nt3.nt2_id 1147where nt3.id = 1 and ss2.b3; 1148 1149drop function f_immutable_int4(int); 1150 1151-- test inlining when function returns composite 1152 1153create function mki8(bigint, bigint) returns int8_tbl as 1154$$select row($1,$2)::int8_tbl$$ language sql; 1155 1156create function mki4(int) returns int4_tbl as 1157$$select row($1)::int4_tbl$$ language sql; 1158 1159explain (verbose, costs off) 1160select * from mki8(1,2); 1161select * from mki8(1,2); 1162 1163explain (verbose, costs off) 1164select * from mki4(42); 1165select * from mki4(42); 1166 1167drop function mki8(bigint, bigint); 1168drop function mki4(int); 1169 1170-- 1171-- test extraction of restriction OR clauses from join OR clause 1172-- (we used to only do this for indexable clauses) 1173-- 1174 1175explain (costs off) 1176select * from tenk1 a join tenk1 b on 1177 (a.unique1 = 1 and b.unique1 = 2) or (a.unique2 = 3 and b.hundred = 4); 1178explain (costs off) 1179select * from tenk1 a join tenk1 b on 1180 (a.unique1 = 1 and b.unique1 = 2) or (a.unique2 = 3 and b.ten = 4); 1181explain (costs off) 1182select * from tenk1 a join tenk1 b on 1183 (a.unique1 = 1 and b.unique1 = 2) or 1184 ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4); 1185 1186-- 1187-- test placement of movable quals in a parameterized join tree 1188-- 1189 1190explain (costs off) 1191select * from tenk1 t1 left join 1192 (tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2) 1193 on t1.hundred = t2.hundred and t1.ten = t3.ten 1194where t1.unique1 = 1; 1195 1196explain (costs off) 1197select * from tenk1 t1 left join 1198 (tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2) 1199 on t1.hundred = t2.hundred and t1.ten + t2.ten = t3.ten 1200where t1.unique1 = 1; 1201 1202explain (costs off) 1203select count(*) from 1204 tenk1 a join tenk1 b on a.unique1 = b.unique2 1205 left join tenk1 c on a.unique2 = b.unique1 and c.thousand = a.thousand 1206 join int4_tbl on b.thousand = f1; 1207 1208select count(*) from 1209 tenk1 a join tenk1 b on a.unique1 = b.unique2 1210 left join tenk1 c on a.unique2 = b.unique1 and c.thousand = a.thousand 1211 join int4_tbl on b.thousand = f1; 1212 1213explain (costs off) 1214select b.unique1 from 1215 tenk1 a join tenk1 b on a.unique1 = b.unique2 1216 left join tenk1 c on b.unique1 = 42 and c.thousand = a.thousand 1217 join int4_tbl i1 on b.thousand = f1 1218 right join int4_tbl i2 on i2.f1 = b.tenthous 1219 order by 1; 1220 1221select b.unique1 from 1222 tenk1 a join tenk1 b on a.unique1 = b.unique2 1223 left join tenk1 c on b.unique1 = 42 and c.thousand = a.thousand 1224 join int4_tbl i1 on b.thousand = f1 1225 right join int4_tbl i2 on i2.f1 = b.tenthous 1226 order by 1; 1227 1228explain (costs off) 1229select * from 1230( 1231 select unique1, q1, coalesce(unique1, -1) + q1 as fault 1232 from int8_tbl left join tenk1 on (q2 = unique2) 1233) ss 1234where fault = 122 1235order by fault; 1236 1237select * from 1238( 1239 select unique1, q1, coalesce(unique1, -1) + q1 as fault 1240 from int8_tbl left join tenk1 on (q2 = unique2) 1241) ss 1242where fault = 122 1243order by fault; 1244 1245explain (costs off) 1246select * from 1247(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys) 1248left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x 1249left join unnest(v1ys) as u1(u1y) on u1y = v2y; 1250 1251select * from 1252(values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys) 1253left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x 1254left join unnest(v1ys) as u1(u1y) on u1y = v2y; 1255 1256-- 1257-- test handling of potential equivalence clauses above outer joins 1258-- 1259 1260explain (costs off) 1261select q1, unique2, thousand, hundred 1262 from int8_tbl a left join tenk1 b on q1 = unique2 1263 where coalesce(thousand,123) = q1 and q1 = coalesce(hundred,123); 1264 1265select q1, unique2, thousand, hundred 1266 from int8_tbl a left join tenk1 b on q1 = unique2 1267 where coalesce(thousand,123) = q1 and q1 = coalesce(hundred,123); 1268 1269explain (costs off) 1270select f1, unique2, case when unique2 is null then f1 else 0 end 1271 from int4_tbl a left join tenk1 b on f1 = unique2 1272 where (case when unique2 is null then f1 else 0 end) = 0; 1273 1274select f1, unique2, case when unique2 is null then f1 else 0 end 1275 from int4_tbl a left join tenk1 b on f1 = unique2 1276 where (case when unique2 is null then f1 else 0 end) = 0; 1277 1278-- 1279-- another case with equivalence clauses above outer joins (bug #8591) 1280-- 1281 1282explain (costs off) 1283select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand) 1284 from tenk1 a left join tenk1 b on b.thousand = a.unique1 left join tenk1 c on c.unique2 = coalesce(b.twothousand, a.twothousand) 1285 where a.unique2 < 10 and coalesce(b.twothousand, a.twothousand) = 44; 1286 1287select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand) 1288 from tenk1 a left join tenk1 b on b.thousand = a.unique1 left join tenk1 c on c.unique2 = coalesce(b.twothousand, a.twothousand) 1289 where a.unique2 < 10 and coalesce(b.twothousand, a.twothousand) = 44; 1290 1291-- 1292-- check handling of join aliases when flattening multiple levels of subquery 1293-- 1294 1295explain (verbose, costs off) 1296select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from 1297 (values (0),(1)) foo1(join_key) 1298left join 1299 (select join_key, bug_field from 1300 (select ss1.join_key, ss1.bug_field from 1301 (select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1 1302 ) foo2 1303 left join 1304 (select unique2 as join_key from tenk1 i2) ss2 1305 using (join_key) 1306 ) foo3 1307using (join_key); 1308 1309select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from 1310 (values (0),(1)) foo1(join_key) 1311left join 1312 (select join_key, bug_field from 1313 (select ss1.join_key, ss1.bug_field from 1314 (select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1 1315 ) foo2 1316 left join 1317 (select unique2 as join_key from tenk1 i2) ss2 1318 using (join_key) 1319 ) foo3 1320using (join_key); 1321 1322-- 1323-- test successful handling of nested outer joins with degenerate join quals 1324-- 1325 1326explain (verbose, costs off) 1327select t1.* from 1328 text_tbl t1 1329 left join (select *, '***'::text as d1 from int8_tbl i8b1) b1 1330 left join int8_tbl i8 1331 left join (select *, null::int as d2 from int8_tbl i8b2) b2 1332 on (i8.q1 = b2.q1) 1333 on (b2.d2 = b1.q2) 1334 on (t1.f1 = b1.d1) 1335 left join int4_tbl i4 1336 on (i8.q2 = i4.f1); 1337 1338select t1.* from 1339 text_tbl t1 1340 left join (select *, '***'::text as d1 from int8_tbl i8b1) b1 1341 left join int8_tbl i8 1342 left join (select *, null::int as d2 from int8_tbl i8b2) b2 1343 on (i8.q1 = b2.q1) 1344 on (b2.d2 = b1.q2) 1345 on (t1.f1 = b1.d1) 1346 left join int4_tbl i4 1347 on (i8.q2 = i4.f1); 1348 1349explain (verbose, costs off) 1350select t1.* from 1351 text_tbl t1 1352 left join (select *, '***'::text as d1 from int8_tbl i8b1) b1 1353 left join int8_tbl i8 1354 left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2) b2 1355 on (i8.q1 = b2.q1) 1356 on (b2.d2 = b1.q2) 1357 on (t1.f1 = b1.d1) 1358 left join int4_tbl i4 1359 on (i8.q2 = i4.f1); 1360 1361select t1.* from 1362 text_tbl t1 1363 left join (select *, '***'::text as d1 from int8_tbl i8b1) b1 1364 left join int8_tbl i8 1365 left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2) b2 1366 on (i8.q1 = b2.q1) 1367 on (b2.d2 = b1.q2) 1368 on (t1.f1 = b1.d1) 1369 left join int4_tbl i4 1370 on (i8.q2 = i4.f1); 1371 1372explain (verbose, costs off) 1373select t1.* from 1374 text_tbl t1 1375 left join (select *, '***'::text as d1 from int8_tbl i8b1) b1 1376 left join int8_tbl i8 1377 left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2 1378 where q1 = f1) b2 1379 on (i8.q1 = b2.q1) 1380 on (b2.d2 = b1.q2) 1381 on (t1.f1 = b1.d1) 1382 left join int4_tbl i4 1383 on (i8.q2 = i4.f1); 1384 1385select t1.* from 1386 text_tbl t1 1387 left join (select *, '***'::text as d1 from int8_tbl i8b1) b1 1388 left join int8_tbl i8 1389 left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2 1390 where q1 = f1) b2 1391 on (i8.q1 = b2.q1) 1392 on (b2.d2 = b1.q2) 1393 on (t1.f1 = b1.d1) 1394 left join int4_tbl i4 1395 on (i8.q2 = i4.f1); 1396 1397explain (verbose, costs off) 1398select * from 1399 text_tbl t1 1400 inner join int8_tbl i8 1401 on i8.q2 = 456 1402 right join text_tbl t2 1403 on t1.f1 = 'doh!' 1404 left join int4_tbl i4 1405 on i8.q1 = i4.f1; 1406 1407select * from 1408 text_tbl t1 1409 inner join int8_tbl i8 1410 on i8.q2 = 456 1411 right join text_tbl t2 1412 on t1.f1 = 'doh!' 1413 left join int4_tbl i4 1414 on i8.q1 = i4.f1; 1415 1416-- 1417-- test for appropriate join order in the presence of lateral references 1418-- 1419 1420explain (verbose, costs off) 1421select * from 1422 text_tbl t1 1423 left join int8_tbl i8 1424 on i8.q2 = 123, 1425 lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss 1426where t1.f1 = ss.f1; 1427 1428select * from 1429 text_tbl t1 1430 left join int8_tbl i8 1431 on i8.q2 = 123, 1432 lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss 1433where t1.f1 = ss.f1; 1434 1435explain (verbose, costs off) 1436select * from 1437 text_tbl t1 1438 left join int8_tbl i8 1439 on i8.q2 = 123, 1440 lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1, 1441 lateral (select ss1.* from text_tbl t3 limit 1) as ss2 1442where t1.f1 = ss2.f1; 1443 1444select * from 1445 text_tbl t1 1446 left join int8_tbl i8 1447 on i8.q2 = 123, 1448 lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1, 1449 lateral (select ss1.* from text_tbl t3 limit 1) as ss2 1450where t1.f1 = ss2.f1; 1451 1452explain (verbose, costs off) 1453select 1 from 1454 text_tbl as tt1 1455 inner join text_tbl as tt2 on (tt1.f1 = 'foo') 1456 left join text_tbl as tt3 on (tt3.f1 = 'foo') 1457 left join text_tbl as tt4 on (tt3.f1 = tt4.f1), 1458 lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1 1459where tt1.f1 = ss1.c0; 1460 1461select 1 from 1462 text_tbl as tt1 1463 inner join text_tbl as tt2 on (tt1.f1 = 'foo') 1464 left join text_tbl as tt3 on (tt3.f1 = 'foo') 1465 left join text_tbl as tt4 on (tt3.f1 = tt4.f1), 1466 lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1 1467where tt1.f1 = ss1.c0; 1468 1469-- 1470-- check a case in which a PlaceHolderVar forces join order 1471-- 1472 1473explain (verbose, costs off) 1474select ss2.* from 1475 int4_tbl i41 1476 left join int8_tbl i8 1477 join (select i42.f1 as c1, i43.f1 as c2, 42 as c3 1478 from int4_tbl i42, int4_tbl i43) ss1 1479 on i8.q1 = ss1.c2 1480 on i41.f1 = ss1.c1, 1481 lateral (select i41.*, i8.*, ss1.* from text_tbl limit 1) ss2 1482where ss1.c2 = 0; 1483 1484select ss2.* from 1485 int4_tbl i41 1486 left join int8_tbl i8 1487 join (select i42.f1 as c1, i43.f1 as c2, 42 as c3 1488 from int4_tbl i42, int4_tbl i43) ss1 1489 on i8.q1 = ss1.c2 1490 on i41.f1 = ss1.c1, 1491 lateral (select i41.*, i8.*, ss1.* from text_tbl limit 1) ss2 1492where ss1.c2 = 0; 1493 1494-- 1495-- test successful handling of full join underneath left join (bug #14105) 1496-- 1497 1498explain (costs off) 1499select * from 1500 (select 1 as id) as xx 1501 left join 1502 (tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id)) 1503 on (xx.id = coalesce(yy.id)); 1504 1505select * from 1506 (select 1 as id) as xx 1507 left join 1508 (tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id)) 1509 on (xx.id = coalesce(yy.id)); 1510 1511-- 1512-- test ability to push constants through outer join clauses 1513-- 1514 1515explain (costs off) 1516 select * from int4_tbl a left join tenk1 b on f1 = unique2 where f1 = 0; 1517 1518explain (costs off) 1519 select * from tenk1 a full join tenk1 b using(unique2) where unique2 = 42; 1520 1521-- 1522-- test that quals attached to an outer join have correct semantics, 1523-- specifically that they don't re-use expressions computed below the join; 1524-- we force a mergejoin so that coalesce(b.q1, 1) appears as a join input 1525-- 1526 1527set enable_hashjoin to off; 1528set enable_nestloop to off; 1529 1530explain (verbose, costs off) 1531 select a.q2, b.q1 1532 from int8_tbl a left join int8_tbl b on a.q2 = coalesce(b.q1, 1) 1533 where coalesce(b.q1, 1) > 0; 1534select a.q2, b.q1 1535 from int8_tbl a left join int8_tbl b on a.q2 = coalesce(b.q1, 1) 1536 where coalesce(b.q1, 1) > 0; 1537 1538reset enable_hashjoin; 1539reset enable_nestloop; 1540 1541-- 1542-- test join removal 1543-- 1544 1545begin; 1546 1547CREATE TEMP TABLE a (id int PRIMARY KEY, b_id int); 1548CREATE TEMP TABLE b (id int PRIMARY KEY, c_id int); 1549CREATE TEMP TABLE c (id int PRIMARY KEY); 1550CREATE TEMP TABLE d (a int, b int); 1551INSERT INTO a VALUES (0, 0), (1, NULL); 1552INSERT INTO b VALUES (0, 0), (1, NULL); 1553INSERT INTO c VALUES (0), (1); 1554INSERT INTO d VALUES (1,3), (2,2), (3,1); 1555 1556-- all three cases should be optimizable into a simple seqscan 1557explain (costs off) SELECT a.* FROM a LEFT JOIN b ON a.b_id = b.id; 1558explain (costs off) SELECT b.* FROM b LEFT JOIN c ON b.c_id = c.id; 1559explain (costs off) 1560 SELECT a.* FROM a LEFT JOIN (b left join c on b.c_id = c.id) 1561 ON (a.b_id = b.id); 1562 1563-- check optimization of outer join within another special join 1564explain (costs off) 1565select id from a where id in ( 1566 select b.id from b left join c on b.id = c.id 1567); 1568 1569-- check that join removal works for a left join when joining a subquery 1570-- that is guaranteed to be unique by its GROUP BY clause 1571explain (costs off) 1572select d.* from d left join (select * from b group by b.id, b.c_id) s 1573 on d.a = s.id and d.b = s.c_id; 1574 1575-- similarly, but keying off a DISTINCT clause 1576explain (costs off) 1577select d.* from d left join (select distinct * from b) s 1578 on d.a = s.id and d.b = s.c_id; 1579 1580-- join removal is not possible when the GROUP BY contains a column that is 1581-- not in the join condition. (Note: as of 9.6, we notice that b.id is a 1582-- primary key and so drop b.c_id from the GROUP BY of the resulting plan; 1583-- but this happens too late for join removal in the outer plan level.) 1584explain (costs off) 1585select d.* from d left join (select * from b group by b.id, b.c_id) s 1586 on d.a = s.id; 1587 1588-- similarly, but keying off a DISTINCT clause 1589explain (costs off) 1590select d.* from d left join (select distinct * from b) s 1591 on d.a = s.id; 1592 1593-- check join removal works when uniqueness of the join condition is enforced 1594-- by a UNION 1595explain (costs off) 1596select d.* from d left join (select id from a union select id from b) s 1597 on d.a = s.id; 1598 1599-- check join removal with a cross-type comparison operator 1600explain (costs off) 1601select i8.* from int8_tbl i8 left join (select f1 from int4_tbl group by f1) i4 1602 on i8.q1 = i4.f1; 1603 1604-- check join removal with lateral references 1605explain (costs off) 1606select 1 from (select a.id FROM a left join b on a.b_id = b.id) q, 1607 lateral generate_series(1, q.id) gs(i) where q.id = gs.i; 1608 1609rollback; 1610 1611create temp table parent (k int primary key, pd int); 1612create temp table child (k int unique, cd int); 1613insert into parent values (1, 10), (2, 20), (3, 30); 1614insert into child values (1, 100), (4, 400); 1615 1616-- this case is optimizable 1617select p.* from parent p left join child c on (p.k = c.k); 1618explain (costs off) 1619 select p.* from parent p left join child c on (p.k = c.k); 1620 1621-- this case is not 1622select p.*, linked from parent p 1623 left join (select c.*, true as linked from child c) as ss 1624 on (p.k = ss.k); 1625explain (costs off) 1626 select p.*, linked from parent p 1627 left join (select c.*, true as linked from child c) as ss 1628 on (p.k = ss.k); 1629 1630-- check for a 9.0rc1 bug: join removal breaks pseudoconstant qual handling 1631select p.* from 1632 parent p left join child c on (p.k = c.k) 1633 where p.k = 1 and p.k = 2; 1634explain (costs off) 1635select p.* from 1636 parent p left join child c on (p.k = c.k) 1637 where p.k = 1 and p.k = 2; 1638 1639select p.* from 1640 (parent p left join child c on (p.k = c.k)) join parent x on p.k = x.k 1641 where p.k = 1 and p.k = 2; 1642explain (costs off) 1643select p.* from 1644 (parent p left join child c on (p.k = c.k)) join parent x on p.k = x.k 1645 where p.k = 1 and p.k = 2; 1646 1647-- bug 5255: this is not optimizable by join removal 1648begin; 1649 1650CREATE TEMP TABLE a (id int PRIMARY KEY); 1651CREATE TEMP TABLE b (id int PRIMARY KEY, a_id int); 1652INSERT INTO a VALUES (0), (1); 1653INSERT INTO b VALUES (0, 0), (1, NULL); 1654 1655SELECT * FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0); 1656SELECT b.* FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0); 1657 1658rollback; 1659 1660-- another join removal bug: this is not optimizable, either 1661begin; 1662 1663create temp table innertab (id int8 primary key, dat1 int8); 1664insert into innertab values(123, 42); 1665 1666SELECT * FROM 1667 (SELECT 1 AS x) ss1 1668 LEFT JOIN 1669 (SELECT q1, q2, COALESCE(dat1, q1) AS y 1670 FROM int8_tbl LEFT JOIN innertab ON q2 = id) ss2 1671 ON true; 1672 1673rollback; 1674 1675-- another join removal bug: we must clean up correctly when removing a PHV 1676begin; 1677 1678create temp table uniquetbl (f1 text unique); 1679 1680explain (costs off) 1681select t1.* from 1682 uniquetbl as t1 1683 left join (select *, '***'::text as d1 from uniquetbl) t2 1684 on t1.f1 = t2.f1 1685 left join uniquetbl t3 1686 on t2.d1 = t3.f1; 1687 1688explain (costs off) 1689select t0.* 1690from 1691 text_tbl t0 1692 left join 1693 (select case t1.ten when 0 then 'doh!'::text else null::text end as case1, 1694 t1.stringu2 1695 from tenk1 t1 1696 join int4_tbl i4 ON i4.f1 = t1.unique2 1697 left join uniquetbl u1 ON u1.f1 = t1.string4) ss 1698 on t0.f1 = ss.case1 1699where ss.stringu2 !~* ss.case1; 1700 1701select t0.* 1702from 1703 text_tbl t0 1704 left join 1705 (select case t1.ten when 0 then 'doh!'::text else null::text end as case1, 1706 t1.stringu2 1707 from tenk1 t1 1708 join int4_tbl i4 ON i4.f1 = t1.unique2 1709 left join uniquetbl u1 ON u1.f1 = t1.string4) ss 1710 on t0.f1 = ss.case1 1711where ss.stringu2 !~* ss.case1; 1712 1713rollback; 1714 1715-- test case to expose miscomputation of required relid set for a PHV 1716explain (verbose, costs off) 1717select i8.*, ss.v, t.unique2 1718 from int8_tbl i8 1719 left join int4_tbl i4 on i4.f1 = 1 1720 left join lateral (select i4.f1 + 1 as v) as ss on true 1721 left join tenk1 t on t.unique2 = ss.v 1722where q2 = 456; 1723 1724select i8.*, ss.v, t.unique2 1725 from int8_tbl i8 1726 left join int4_tbl i4 on i4.f1 = 1 1727 left join lateral (select i4.f1 + 1 as v) as ss on true 1728 left join tenk1 t on t.unique2 = ss.v 1729where q2 = 456; 1730 1731-- and check a related issue where we miscompute required relids for 1732-- a PHV that's been translated to a child rel 1733create temp table parttbl (a integer primary key) partition by range (a); 1734create temp table parttbl1 partition of parttbl for values from (1) to (100); 1735insert into parttbl values (11), (12); 1736explain (costs off) 1737select * from 1738 (select *, 12 as phv from parttbl) as ss 1739 right join int4_tbl on true 1740where ss.a = ss.phv and f1 = 0; 1741 1742select * from 1743 (select *, 12 as phv from parttbl) as ss 1744 right join int4_tbl on true 1745where ss.a = ss.phv and f1 = 0; 1746 1747-- bug #8444: we've historically allowed duplicate aliases within aliased JOINs 1748 1749select * from 1750 int8_tbl x join (int4_tbl x cross join int4_tbl y) j on q1 = f1; -- error 1751select * from 1752 int8_tbl x join (int4_tbl x cross join int4_tbl y) j on q1 = y.f1; -- error 1753select * from 1754 int8_tbl x join (int4_tbl x cross join int4_tbl y(ff)) j on q1 = f1; -- ok 1755 1756-- 1757-- Test hints given on incorrect column references are useful 1758-- 1759 1760select t1.uunique1 from 1761 tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, prefer "t1" suggestion 1762select t2.uunique1 from 1763 tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, prefer "t2" suggestion 1764select uunique1 from 1765 tenk1 t1 join tenk2 t2 on t1.two = t2.two; -- error, suggest both at once 1766 1767-- 1768-- Take care to reference the correct RTE 1769-- 1770 1771select atts.relid::regclass, s.* from pg_stats s join 1772 pg_attribute a on s.attname = a.attname and s.tablename = 1773 a.attrelid::regclass::text join (select unnest(indkey) attnum, 1774 indexrelid from pg_index i) atts on atts.attnum = a.attnum where 1775 schemaname != 'pg_catalog'; 1776 1777-- 1778-- Test LATERAL 1779-- 1780 1781select unique2, x.* 1782from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x; 1783explain (costs off) 1784 select unique2, x.* 1785 from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x; 1786select unique2, x.* 1787from int4_tbl x, lateral (select unique2 from tenk1 where f1 = unique1) ss; 1788explain (costs off) 1789 select unique2, x.* 1790 from int4_tbl x, lateral (select unique2 from tenk1 where f1 = unique1) ss; 1791explain (costs off) 1792 select unique2, x.* 1793 from int4_tbl x cross join lateral (select unique2 from tenk1 where f1 = unique1) ss; 1794select unique2, x.* 1795from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 = unique1) ss on true; 1796explain (costs off) 1797 select unique2, x.* 1798 from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 = unique1) ss on true; 1799 1800-- check scoping of lateral versus parent references 1801-- the first of these should return int8_tbl.q2, the second int8_tbl.q1 1802select *, (select r from (select q1 as q2) x, (select q2 as r) y) from int8_tbl; 1803select *, (select r from (select q1 as q2) x, lateral (select q2 as r) y) from int8_tbl; 1804 1805-- lateral with function in FROM 1806select count(*) from tenk1 a, lateral generate_series(1,two) g; 1807explain (costs off) 1808 select count(*) from tenk1 a, lateral generate_series(1,two) g; 1809explain (costs off) 1810 select count(*) from tenk1 a cross join lateral generate_series(1,two) g; 1811-- don't need the explicit LATERAL keyword for functions 1812explain (costs off) 1813 select count(*) from tenk1 a, generate_series(1,two) g; 1814 1815-- lateral with UNION ALL subselect 1816explain (costs off) 1817 select * from generate_series(100,200) g, 1818 lateral (select * from int8_tbl a where g = q1 union all 1819 select * from int8_tbl b where g = q2) ss; 1820select * from generate_series(100,200) g, 1821 lateral (select * from int8_tbl a where g = q1 union all 1822 select * from int8_tbl b where g = q2) ss; 1823 1824-- lateral with VALUES 1825explain (costs off) 1826 select count(*) from tenk1 a, 1827 tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x; 1828select count(*) from tenk1 a, 1829 tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x; 1830 1831-- lateral with VALUES, no flattening possible 1832explain (costs off) 1833 select count(*) from tenk1 a, 1834 tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x; 1835select count(*) from tenk1 a, 1836 tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x; 1837 1838-- lateral injecting a strange outer join condition 1839explain (costs off) 1840 select * from int8_tbl a, 1841 int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z) 1842 on x.q2 = ss.z 1843 order by a.q1, a.q2, x.q1, x.q2, ss.z; 1844select * from int8_tbl a, 1845 int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z) 1846 on x.q2 = ss.z 1847 order by a.q1, a.q2, x.q1, x.q2, ss.z; 1848 1849-- lateral reference to a join alias variable 1850select * from (select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1, 1851 lateral (select x) ss2(y); 1852select * from (select f1 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1, 1853 lateral (values(x)) ss2(y); 1854select * from ((select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1) j, 1855 lateral (select x) ss2(y); 1856 1857-- lateral references requiring pullup 1858select * from (values(1)) x(lb), 1859 lateral generate_series(lb,4) x4; 1860select * from (select f1/1000000000 from int4_tbl) x(lb), 1861 lateral generate_series(lb,4) x4; 1862select * from (values(1)) x(lb), 1863 lateral (values(lb)) y(lbcopy); 1864select * from (values(1)) x(lb), 1865 lateral (select lb from int4_tbl) y(lbcopy); 1866select * from 1867 int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1, 1868 lateral (values(x.q1,y.q1,y.q2)) v(xq1,yq1,yq2); 1869select * from 1870 int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1, 1871 lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2); 1872select x.* from 1873 int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1, 1874 lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2); 1875select v.* from 1876 (int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1) 1877 left join int4_tbl z on z.f1 = x.q2, 1878 lateral (select x.q1,y.q1 union all select x.q2,y.q2) v(vx,vy); 1879select v.* from 1880 (int8_tbl x left join (select q1,(select coalesce(q2,0)) q2 from int8_tbl) y on x.q2 = y.q1) 1881 left join int4_tbl z on z.f1 = x.q2, 1882 lateral (select x.q1,y.q1 union all select x.q2,y.q2) v(vx,vy); 1883select v.* from 1884 (int8_tbl x left join (select q1,(select coalesce(q2,0)) q2 from int8_tbl) y on x.q2 = y.q1) 1885 left join int4_tbl z on z.f1 = x.q2, 1886 lateral (select x.q1,y.q1 from onerow union all select x.q2,y.q2 from onerow) v(vx,vy); 1887 1888explain (verbose, costs off) 1889select * from 1890 int8_tbl a left join 1891 lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1; 1892select * from 1893 int8_tbl a left join 1894 lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1; 1895explain (verbose, costs off) 1896select * from 1897 int8_tbl a left join 1898 lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1; 1899select * from 1900 int8_tbl a left join 1901 lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1; 1902 1903-- lateral can result in join conditions appearing below their 1904-- real semantic level 1905explain (verbose, costs off) 1906select * from int4_tbl i left join 1907 lateral (select * from int2_tbl j where i.f1 = j.f1) k on true; 1908select * from int4_tbl i left join 1909 lateral (select * from int2_tbl j where i.f1 = j.f1) k on true; 1910explain (verbose, costs off) 1911select * from int4_tbl i left join 1912 lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true; 1913select * from int4_tbl i left join 1914 lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true; 1915explain (verbose, costs off) 1916select * from int4_tbl a, 1917 lateral ( 1918 select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2) 1919 ) ss; 1920select * from int4_tbl a, 1921 lateral ( 1922 select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2) 1923 ) ss; 1924 1925-- lateral reference in a PlaceHolderVar evaluated at join level 1926explain (verbose, costs off) 1927select * from 1928 int8_tbl a left join lateral 1929 (select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from 1930 int8_tbl b cross join int8_tbl c) ss 1931 on a.q2 = ss.bq1; 1932select * from 1933 int8_tbl a left join lateral 1934 (select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from 1935 int8_tbl b cross join int8_tbl c) ss 1936 on a.q2 = ss.bq1; 1937 1938-- case requiring nested PlaceHolderVars 1939explain (verbose, costs off) 1940select * from 1941 int8_tbl c left join ( 1942 int8_tbl a left join (select q1, coalesce(q2,42) as x from int8_tbl b) ss1 1943 on a.q2 = ss1.q1 1944 cross join 1945 lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2 1946 ) on c.q2 = ss2.q1, 1947 lateral (select ss2.y offset 0) ss3; 1948 1949-- case that breaks the old ph_may_need optimization 1950explain (verbose, costs off) 1951select c.*,a.*,ss1.q1,ss2.q1,ss3.* from 1952 int8_tbl c left join ( 1953 int8_tbl a left join 1954 (select q1, coalesce(q2,f1) as x from int8_tbl b, int4_tbl b2 1955 where q1 < f1) ss1 1956 on a.q2 = ss1.q1 1957 cross join 1958 lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2 1959 ) on c.q2 = ss2.q1, 1960 lateral (select * from int4_tbl i where ss2.y > f1) ss3; 1961 1962-- check processing of postponed quals (bug #9041) 1963explain (verbose, costs off) 1964select * from 1965 (select 1 as x offset 0) x cross join (select 2 as y offset 0) y 1966 left join lateral ( 1967 select * from (select 3 as z offset 0) z where z.z = x.x 1968 ) zz on zz.z = y.y; 1969 1970-- check dummy rels with lateral references (bug #15694) 1971explain (verbose, costs off) 1972select * from int8_tbl i8 left join lateral 1973 (select *, i8.q2 from int4_tbl where false) ss on true; 1974explain (verbose, costs off) 1975select * from int8_tbl i8 left join lateral 1976 (select *, i8.q2 from int4_tbl i1, int4_tbl i2 where false) ss on true; 1977 1978-- check handling of nested appendrels inside LATERAL 1979select * from 1980 ((select 2 as v) union all (select 3 as v)) as q1 1981 cross join lateral 1982 ((select * from 1983 ((select 4 as v) union all (select 5 as v)) as q3) 1984 union all 1985 (select q1.v) 1986 ) as q2; 1987 1988-- check we don't try to do a unique-ified semijoin with LATERAL 1989explain (verbose, costs off) 1990select * from 1991 (values (0,9998), (1,1000)) v(id,x), 1992 lateral (select f1 from int4_tbl 1993 where f1 = any (select unique1 from tenk1 1994 where unique2 = v.x offset 0)) ss; 1995select * from 1996 (values (0,9998), (1,1000)) v(id,x), 1997 lateral (select f1 from int4_tbl 1998 where f1 = any (select unique1 from tenk1 1999 where unique2 = v.x offset 0)) ss; 2000 2001-- check proper extParam/allParam handling (this isn't exactly a LATERAL issue, 2002-- but we can make the test case much more compact with LATERAL) 2003explain (verbose, costs off) 2004select * from (values (0), (1)) v(id), 2005lateral (select * from int8_tbl t1, 2006 lateral (select * from 2007 (select * from int8_tbl t2 2008 where q1 = any (select q2 from int8_tbl t3 2009 where q2 = (select greatest(t1.q1,t2.q2)) 2010 and (select v.id=0)) offset 0) ss2) ss 2011 where t1.q1 = ss.q2) ss0; 2012 2013select * from (values (0), (1)) v(id), 2014lateral (select * from int8_tbl t1, 2015 lateral (select * from 2016 (select * from int8_tbl t2 2017 where q1 = any (select q2 from int8_tbl t3 2018 where q2 = (select greatest(t1.q1,t2.q2)) 2019 and (select v.id=0)) offset 0) ss2) ss 2020 where t1.q1 = ss.q2) ss0; 2021 2022-- test some error cases where LATERAL should have been used but wasn't 2023select f1,g from int4_tbl a, (select f1 as g) ss; 2024select f1,g from int4_tbl a, (select a.f1 as g) ss; 2025select f1,g from int4_tbl a cross join (select f1 as g) ss; 2026select f1,g from int4_tbl a cross join (select a.f1 as g) ss; 2027-- SQL:2008 says the left table is in scope but illegal to access here 2028select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true; 2029select f1,g from int4_tbl a full join lateral generate_series(0, a.f1) g on true; 2030-- check we complain about ambiguous table references 2031select * from 2032 int8_tbl x cross join (int4_tbl x cross join lateral (select x.f1) ss); 2033-- LATERAL can be used to put an aggregate into the FROM clause of its query 2034select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss; 2035 2036-- check behavior of LATERAL in UPDATE/DELETE 2037 2038create temp table xx1 as select f1 as x1, -f1 as x2 from int4_tbl; 2039 2040-- error, can't do this: 2041update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss; 2042update xx1 set x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss; 2043-- can't do it even with LATERAL: 2044update xx1 set x2 = f1 from lateral (select * from int4_tbl where f1 = x1) ss; 2045-- we might in future allow something like this, but for now it's an error: 2046update xx1 set x2 = f1 from xx1, lateral (select * from int4_tbl where f1 = x1) ss; 2047 2048-- also errors: 2049delete from xx1 using (select * from int4_tbl where f1 = x1) ss; 2050delete from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss; 2051delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss; 2052 2053-- 2054-- test LATERAL reference propagation down a multi-level inheritance hierarchy 2055-- produced for a multi-level partitioned table hierarchy. 2056-- 2057create table join_pt1 (a int, b int, c varchar) partition by range(a); 2058create table join_pt1p1 partition of join_pt1 for values from (0) to (100) partition by range(b); 2059create table join_pt1p2 partition of join_pt1 for values from (100) to (200); 2060create table join_pt1p1p1 partition of join_pt1p1 for values from (0) to (100); 2061insert into join_pt1 values (1, 1, 'x'), (101, 101, 'y'); 2062create table join_ut1 (a int, b int, c varchar); 2063insert into join_ut1 values (101, 101, 'y'), (2, 2, 'z'); 2064explain (verbose, costs off) 2065select t1.b, ss.phv from join_ut1 t1 left join lateral 2066 (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv 2067 from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss 2068 on t1.a = ss.t2a order by t1.a; 2069select t1.b, ss.phv from join_ut1 t1 left join lateral 2070 (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv 2071 from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss 2072 on t1.a = ss.t2a order by t1.a; 2073 2074drop table join_pt1; 2075drop table join_ut1; 2076 2077-- 2078-- test estimation behavior with multi-column foreign key and constant qual 2079-- 2080 2081begin; 2082 2083create table fkest (x integer, x10 integer, x10b integer, x100 integer); 2084insert into fkest select x, x/10, x/10, x/100 from generate_series(1,1000) x; 2085create unique index on fkest(x, x10, x100); 2086analyze fkest; 2087 2088explain (costs off) 2089select * from fkest f1 2090 join fkest f2 on (f1.x = f2.x and f1.x10 = f2.x10b and f1.x100 = f2.x100) 2091 join fkest f3 on f1.x = f3.x 2092 where f1.x100 = 2; 2093 2094alter table fkest add constraint fk 2095 foreign key (x, x10b, x100) references fkest (x, x10, x100); 2096 2097explain (costs off) 2098select * from fkest f1 2099 join fkest f2 on (f1.x = f2.x and f1.x10 = f2.x10b and f1.x100 = f2.x100) 2100 join fkest f3 on f1.x = f3.x 2101 where f1.x100 = 2; 2102 2103rollback; 2104 2105-- 2106-- test that foreign key join estimation performs sanely for outer joins 2107-- 2108 2109begin; 2110 2111create table fkest (a int, b int, c int unique, primary key(a,b)); 2112create table fkest1 (a int, b int, primary key(a,b)); 2113 2114insert into fkest select x/10, x%10, x from generate_series(1,1000) x; 2115insert into fkest1 select x/10, x%10 from generate_series(1,1000) x; 2116 2117alter table fkest1 2118 add constraint fkest1_a_b_fkey foreign key (a,b) references fkest; 2119 2120analyze fkest; 2121analyze fkest1; 2122 2123explain (costs off) 2124select * 2125from fkest f 2126 left join fkest1 f1 on f.a = f1.a and f.b = f1.b 2127 left join fkest1 f2 on f.a = f2.a and f.b = f2.b 2128 left join fkest1 f3 on f.a = f3.a and f.b = f3.b 2129where f.c = 1; 2130 2131rollback; 2132 2133-- 2134-- test planner's ability to mark joins as unique 2135-- 2136 2137create table j1 (id int primary key); 2138create table j2 (id int primary key); 2139create table j3 (id int); 2140 2141insert into j1 values(1),(2),(3); 2142insert into j2 values(1),(2),(3); 2143insert into j3 values(1),(1); 2144 2145analyze j1; 2146analyze j2; 2147analyze j3; 2148 2149-- ensure join is properly marked as unique 2150explain (verbose, costs off) 2151select * from j1 inner join j2 on j1.id = j2.id; 2152 2153-- ensure join is not unique when not an equi-join 2154explain (verbose, costs off) 2155select * from j1 inner join j2 on j1.id > j2.id; 2156 2157-- ensure non-unique rel is not chosen as inner 2158explain (verbose, costs off) 2159select * from j1 inner join j3 on j1.id = j3.id; 2160 2161-- ensure left join is marked as unique 2162explain (verbose, costs off) 2163select * from j1 left join j2 on j1.id = j2.id; 2164 2165-- ensure right join is marked as unique 2166explain (verbose, costs off) 2167select * from j1 right join j2 on j1.id = j2.id; 2168 2169-- ensure full join is marked as unique 2170explain (verbose, costs off) 2171select * from j1 full join j2 on j1.id = j2.id; 2172 2173-- a clauseless (cross) join can't be unique 2174explain (verbose, costs off) 2175select * from j1 cross join j2; 2176 2177-- ensure a natural join is marked as unique 2178explain (verbose, costs off) 2179select * from j1 natural join j2; 2180 2181-- ensure a distinct clause allows the inner to become unique 2182explain (verbose, costs off) 2183select * from j1 2184inner join (select distinct id from j3) j3 on j1.id = j3.id; 2185 2186-- ensure group by clause allows the inner to become unique 2187explain (verbose, costs off) 2188select * from j1 2189inner join (select id from j3 group by id) j3 on j1.id = j3.id; 2190 2191drop table j1; 2192drop table j2; 2193drop table j3; 2194 2195-- test more complex permutations of unique joins 2196 2197create table j1 (id1 int, id2 int, primary key(id1,id2)); 2198create table j2 (id1 int, id2 int, primary key(id1,id2)); 2199create table j3 (id1 int, id2 int, primary key(id1,id2)); 2200 2201insert into j1 values(1,1),(1,2); 2202insert into j2 values(1,1); 2203insert into j3 values(1,1); 2204 2205analyze j1; 2206analyze j2; 2207analyze j3; 2208 2209-- ensure there's no unique join when not all columns which are part of the 2210-- unique index are seen in the join clause 2211explain (verbose, costs off) 2212select * from j1 2213inner join j2 on j1.id1 = j2.id1; 2214 2215-- ensure proper unique detection with multiple join quals 2216explain (verbose, costs off) 2217select * from j1 2218inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2; 2219 2220-- ensure we don't detect the join to be unique when quals are not part of the 2221-- join condition 2222explain (verbose, costs off) 2223select * from j1 2224inner join j2 on j1.id1 = j2.id1 where j1.id2 = 1; 2225 2226-- as above, but for left joins. 2227explain (verbose, costs off) 2228select * from j1 2229left join j2 on j1.id1 = j2.id1 where j1.id2 = 1; 2230 2231-- validate logic in merge joins which skips mark and restore. 2232-- it should only do this if all quals which were used to detect the unique 2233-- are present as join quals, and not plain quals. 2234set enable_nestloop to 0; 2235set enable_hashjoin to 0; 2236set enable_sort to 0; 2237 2238-- create indexes that will be preferred over the PKs to perform the join 2239create index j1_id1_idx on j1 (id1) where id1 % 1000 = 1; 2240create index j2_id1_idx on j2 (id1) where id1 % 1000 = 1; 2241 2242-- need an additional row in j2, if we want j2_id1_idx to be preferred 2243insert into j2 values(1,2); 2244analyze j2; 2245 2246explain (costs off) select * from j1 2247inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 2248where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1; 2249 2250select * from j1 2251inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 2252where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1; 2253 2254-- Exercise array keys mark/restore B-Tree code 2255explain (costs off) select * from j1 2256inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 2257where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1 = any (array[1]); 2258 2259select * from j1 2260inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 2261where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1 = any (array[1]); 2262 2263-- Exercise array keys "find extreme element" B-Tree code 2264explain (costs off) select * from j1 2265inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 2266where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1 >= any (array[1,5]); 2267 2268select * from j1 2269inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 2270where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1 and j2.id1 >= any (array[1,5]); 2271 2272reset enable_nestloop; 2273reset enable_hashjoin; 2274reset enable_sort; 2275 2276drop table j1; 2277drop table j2; 2278drop table j3; 2279 2280-- check that semijoin inner is not seen as unique for a portion of the outerrel 2281explain (verbose, costs off) 2282select t1.unique1, t2.hundred 2283from onek t1, tenk1 t2 2284where exists (select 1 from tenk1 t3 2285 where t3.thousand = t1.unique1 and t3.tenthous = t2.hundred) 2286 and t1.unique1 < 1; 2287 2288-- ... unless it actually is unique 2289create table j3 as select unique1, tenthous from onek; 2290vacuum analyze j3; 2291create unique index on j3(unique1, tenthous); 2292 2293explain (verbose, costs off) 2294select t1.unique1, t2.hundred 2295from onek t1, tenk1 t2 2296where exists (select 1 from j3 2297 where j3.unique1 = t1.unique1 and j3.tenthous = t2.hundred) 2298 and t1.unique1 < 1; 2299 2300drop table j3; 2301