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