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