From 6eb9c5a5657d1fe77b55cc261450f3538d35a94d Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 14:19:15 +0200 Subject: Adding upstream version 13.4. Signed-off-by: Daniel Baumann --- src/test/regress/expected/subselect.out | 1746 +++++++++++++++++++++++++++++++ 1 file changed, 1746 insertions(+) create mode 100644 src/test/regress/expected/subselect.out (limited to 'src/test/regress/expected/subselect.out') diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out new file mode 100644 index 0000000..b81923f --- /dev/null +++ b/src/test/regress/expected/subselect.out @@ -0,0 +1,1746 @@ +-- +-- SUBSELECT +-- +SELECT 1 AS one WHERE 1 IN (SELECT 1); + one +----- + 1 +(1 row) + +SELECT 1 AS zero WHERE 1 NOT IN (SELECT 1); + zero +------ +(0 rows) + +SELECT 1 AS zero WHERE 1 IN (SELECT 2); + zero +------ +(0 rows) + +-- Check grammar's handling of extra parens in assorted contexts +SELECT * FROM (SELECT 1 AS x) ss; + x +--- + 1 +(1 row) + +SELECT * FROM ((SELECT 1 AS x)) ss; + x +--- + 1 +(1 row) + +(SELECT 2) UNION SELECT 2; + ?column? +---------- + 2 +(1 row) + +((SELECT 2)) UNION SELECT 2; + ?column? +---------- + 2 +(1 row) + +SELECT ((SELECT 2) UNION SELECT 2); + ?column? +---------- + 2 +(1 row) + +SELECT (((SELECT 2)) UNION SELECT 2); + ?column? +---------- + 2 +(1 row) + +SELECT (SELECT ARRAY[1,2,3])[1]; + array +------- + 1 +(1 row) + +SELECT ((SELECT ARRAY[1,2,3]))[2]; + array +------- + 2 +(1 row) + +SELECT (((SELECT ARRAY[1,2,3])))[3]; + array +------- + 3 +(1 row) + +-- Set up some simple test tables +CREATE TABLE SUBSELECT_TBL ( + f1 integer, + f2 integer, + f3 float +); +INSERT INTO SUBSELECT_TBL VALUES (1, 2, 3); +INSERT INTO SUBSELECT_TBL VALUES (2, 3, 4); +INSERT INTO SUBSELECT_TBL VALUES (3, 4, 5); +INSERT INTO SUBSELECT_TBL VALUES (1, 1, 1); +INSERT INTO SUBSELECT_TBL VALUES (2, 2, 2); +INSERT INTO SUBSELECT_TBL VALUES (3, 3, 3); +INSERT INTO SUBSELECT_TBL VALUES (6, 7, 8); +INSERT INTO SUBSELECT_TBL VALUES (8, 9, NULL); +SELECT '' AS eight, * FROM SUBSELECT_TBL; + eight | f1 | f2 | f3 +-------+----+----+---- + | 1 | 2 | 3 + | 2 | 3 | 4 + | 3 | 4 | 5 + | 1 | 1 | 1 + | 2 | 2 | 2 + | 3 | 3 | 3 + | 6 | 7 | 8 + | 8 | 9 | +(8 rows) + +-- Uncorrelated subselects +SELECT '' AS two, f1 AS "Constant Select" FROM SUBSELECT_TBL + WHERE f1 IN (SELECT 1); + two | Constant Select +-----+----------------- + | 1 + | 1 +(2 rows) + +SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL + WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL); + six | Uncorrelated Field +-----+-------------------- + | 1 + | 2 + | 3 + | 1 + | 2 + | 3 +(6 rows) + +SELECT '' AS six, f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL + WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE + f2 IN (SELECT f1 FROM SUBSELECT_TBL)); + six | Uncorrelated Field +-----+-------------------- + | 1 + | 2 + | 3 + | 1 + | 2 + | 3 +(6 rows) + +SELECT '' AS three, f1, f2 + FROM SUBSELECT_TBL + WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL + WHERE f3 IS NOT NULL); + three | f1 | f2 +-------+----+---- + | 1 | 2 + | 6 | 7 + | 8 | 9 +(3 rows) + +-- Correlated subselects +SELECT '' AS six, f1 AS "Correlated Field", f2 AS "Second Field" + FROM SUBSELECT_TBL upper + WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f1 = upper.f1); + six | Correlated Field | Second Field +-----+------------------+-------------- + | 1 | 2 + | 2 | 3 + | 3 | 4 + | 1 | 1 + | 2 | 2 + | 3 | 3 +(6 rows) + +SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" + FROM SUBSELECT_TBL upper + WHERE f1 IN + (SELECT f2 FROM SUBSELECT_TBL WHERE CAST(upper.f2 AS float) = f3); + six | Correlated Field | Second Field +-----+------------------+-------------- + | 2 | 4 + | 3 | 5 + | 1 | 1 + | 2 | 2 + | 3 | 3 +(5 rows) + +SELECT '' AS six, f1 AS "Correlated Field", f3 AS "Second Field" + FROM SUBSELECT_TBL upper + WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL + WHERE f2 = CAST(f3 AS integer)); + six | Correlated Field | Second Field +-----+------------------+-------------- + | 1 | 3 + | 2 | 4 + | 3 | 5 + | 6 | 8 +(4 rows) + +SELECT '' AS five, f1 AS "Correlated Field" + FROM SUBSELECT_TBL + WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL + WHERE f3 IS NOT NULL); + five | Correlated Field +------+------------------ + | 2 + | 3 + | 1 + | 2 + | 3 +(5 rows) + +-- +-- Use some existing tables in the regression test +-- +SELECT '' AS eight, ss.f1 AS "Correlated Field", ss.f3 AS "Second Field" + FROM SUBSELECT_TBL ss + WHERE f1 NOT IN (SELECT f1+1 FROM INT4_TBL + WHERE f1 != ss.f1 AND f1 < 2147483647); + eight | Correlated Field | Second Field +-------+------------------+-------------- + | 2 | 4 + | 3 | 5 + | 2 | 2 + | 3 | 3 + | 6 | 8 + | 8 | +(6 rows) + +select q1, float8(count(*)) / (select count(*) from int8_tbl) +from int8_tbl group by q1 order by q1; + q1 | ?column? +------------------+---------- + 123 | 0.4 + 4567890123456789 | 0.6 +(2 rows) + +-- Unspecified-type literals in output columns should resolve as text +SELECT *, pg_typeof(f1) FROM + (SELECT 'foo' AS f1 FROM generate_series(1,3)) ss ORDER BY 1; + f1 | pg_typeof +-----+----------- + foo | text + foo | text + foo | text +(3 rows) + +-- ... unless there's context to suggest differently +explain (verbose, costs off) select '42' union all select '43'; + QUERY PLAN +---------------------------- + Append + -> Result + Output: '42'::text + -> Result + Output: '43'::text +(5 rows) + +explain (verbose, costs off) select '42' union all select 43; + QUERY PLAN +-------------------- + Append + -> Result + Output: 42 + -> Result + Output: 43 +(5 rows) + +-- check materialization of an initplan reference (bug #14524) +explain (verbose, costs off) +select 1 = all (select (select 1)); + QUERY PLAN +----------------------------------- + Result + Output: (SubPlan 2) + SubPlan 2 + -> Materialize + Output: ($0) + InitPlan 1 (returns $0) + -> Result + Output: 1 + -> Result + Output: $0 +(10 rows) + +select 1 = all (select (select 1)); + ?column? +---------- + t +(1 row) + +-- +-- Check EXISTS simplification with LIMIT +-- +explain (costs off) +select * from int4_tbl o where exists + (select 1 from int4_tbl i where i.f1=o.f1 limit null); + QUERY PLAN +------------------------------------ + Hash Semi Join + Hash Cond: (o.f1 = i.f1) + -> Seq Scan on int4_tbl o + -> Hash + -> Seq Scan on int4_tbl i +(5 rows) + +explain (costs off) +select * from int4_tbl o where not exists + (select 1 from int4_tbl i where i.f1=o.f1 limit 1); + QUERY PLAN +------------------------------------ + Hash Anti Join + Hash Cond: (o.f1 = i.f1) + -> Seq Scan on int4_tbl o + -> Hash + -> Seq Scan on int4_tbl i +(5 rows) + +explain (costs off) +select * from int4_tbl o where exists + (select 1 from int4_tbl i where i.f1=o.f1 limit 0); + QUERY PLAN +-------------------------------------- + Seq Scan on int4_tbl o + Filter: (SubPlan 1) + SubPlan 1 + -> Limit + -> Seq Scan on int4_tbl i + Filter: (f1 = o.f1) +(6 rows) + +-- +-- Test cases to catch unpleasant interactions between IN-join processing +-- and subquery pullup. +-- +select count(*) from + (select 1 from tenk1 a + where unique1 IN (select hundred from tenk1 b)) ss; + count +------- + 100 +(1 row) + +select count(distinct ss.ten) from + (select ten from tenk1 a + where unique1 IN (select hundred from tenk1 b)) ss; + count +------- + 10 +(1 row) + +select count(*) from + (select 1 from tenk1 a + where unique1 IN (select distinct hundred from tenk1 b)) ss; + count +------- + 100 +(1 row) + +select count(distinct ss.ten) from + (select ten from tenk1 a + where unique1 IN (select distinct hundred from tenk1 b)) ss; + count +------- + 10 +(1 row) + +-- +-- Test cases to check for overenthusiastic optimization of +-- "IN (SELECT DISTINCT ...)" and related cases. Per example from +-- Luca Pireddu and Michael Fuhr. +-- +CREATE TEMP TABLE foo (id integer); +CREATE TEMP TABLE bar (id1 integer, id2 integer); +INSERT INTO foo VALUES (1); +INSERT INTO bar VALUES (1, 1); +INSERT INTO bar VALUES (2, 2); +INSERT INTO bar VALUES (3, 1); +-- These cases require an extra level of distinct-ing above subquery s +SELECT * FROM foo WHERE id IN + (SELECT id2 FROM (SELECT DISTINCT id1, id2 FROM bar) AS s); + id +---- + 1 +(1 row) + +SELECT * FROM foo WHERE id IN + (SELECT id2 FROM (SELECT id1,id2 FROM bar GROUP BY id1,id2) AS s); + id +---- + 1 +(1 row) + +SELECT * FROM foo WHERE id IN + (SELECT id2 FROM (SELECT id1, id2 FROM bar UNION + SELECT id1, id2 FROM bar) AS s); + id +---- + 1 +(1 row) + +-- These cases do not +SELECT * FROM foo WHERE id IN + (SELECT id2 FROM (SELECT DISTINCT ON (id2) id1, id2 FROM bar) AS s); + id +---- + 1 +(1 row) + +SELECT * FROM foo WHERE id IN + (SELECT id2 FROM (SELECT id2 FROM bar GROUP BY id2) AS s); + id +---- + 1 +(1 row) + +SELECT * FROM foo WHERE id IN + (SELECT id2 FROM (SELECT id2 FROM bar UNION + SELECT id2 FROM bar) AS s); + id +---- + 1 +(1 row) + +-- +-- Test case to catch problems with multiply nested sub-SELECTs not getting +-- recalculated properly. Per bug report from Didier Moens. +-- +CREATE TABLE orderstest ( + approver_ref integer, + po_ref integer, + ordercanceled boolean +); +INSERT INTO orderstest VALUES (1, 1, false); +INSERT INTO orderstest VALUES (66, 5, false); +INSERT INTO orderstest VALUES (66, 6, false); +INSERT INTO orderstest VALUES (66, 7, false); +INSERT INTO orderstest VALUES (66, 1, true); +INSERT INTO orderstest VALUES (66, 8, false); +INSERT INTO orderstest VALUES (66, 1, false); +INSERT INTO orderstest VALUES (77, 1, false); +INSERT INTO orderstest VALUES (1, 1, false); +INSERT INTO orderstest VALUES (66, 1, false); +INSERT INTO orderstest VALUES (1, 1, false); +CREATE VIEW orders_view AS +SELECT *, +(SELECT CASE + WHEN ord.approver_ref=1 THEN '---' ELSE 'Approved' + END) AS "Approved", +(SELECT CASE + WHEN ord.ordercanceled + THEN 'Canceled' + ELSE + (SELECT CASE + WHEN ord.po_ref=1 + THEN + (SELECT CASE + WHEN ord.approver_ref=1 + THEN '---' + ELSE 'Approved' + END) + ELSE 'PO' + END) +END) AS "Status", +(CASE + WHEN ord.ordercanceled + THEN 'Canceled' + ELSE + (CASE + WHEN ord.po_ref=1 + THEN + (CASE + WHEN ord.approver_ref=1 + THEN '---' + ELSE 'Approved' + END) + ELSE 'PO' + END) +END) AS "Status_OK" +FROM orderstest ord; +SELECT * FROM orders_view; + approver_ref | po_ref | ordercanceled | Approved | Status | Status_OK +--------------+--------+---------------+----------+----------+----------- + 1 | 1 | f | --- | --- | --- + 66 | 5 | f | Approved | PO | PO + 66 | 6 | f | Approved | PO | PO + 66 | 7 | f | Approved | PO | PO + 66 | 1 | t | Approved | Canceled | Canceled + 66 | 8 | f | Approved | PO | PO + 66 | 1 | f | Approved | Approved | Approved + 77 | 1 | f | Approved | Approved | Approved + 1 | 1 | f | --- | --- | --- + 66 | 1 | f | Approved | Approved | Approved + 1 | 1 | f | --- | --- | --- +(11 rows) + +DROP TABLE orderstest cascade; +NOTICE: drop cascades to view orders_view +-- +-- Test cases to catch situations where rule rewriter fails to propagate +-- hasSubLinks flag correctly. Per example from Kyle Bateman. +-- +create temp table parts ( + partnum text, + cost float8 +); +create temp table shipped ( + ttype char(2), + ordnum int4, + partnum text, + value float8 +); +create temp view shipped_view as + select * from shipped where ttype = 'wt'; +create rule shipped_view_insert as on insert to shipped_view do instead + insert into shipped values('wt', new.ordnum, new.partnum, new.value); +insert into parts (partnum, cost) values (1, 1234.56); +insert into shipped_view (ordnum, partnum, value) + values (0, 1, (select cost from parts where partnum = '1')); +select * from shipped_view; + ttype | ordnum | partnum | value +-------+--------+---------+--------- + wt | 0 | 1 | 1234.56 +(1 row) + +create rule shipped_view_update as on update to shipped_view do instead + update shipped set partnum = new.partnum, value = new.value + where ttype = new.ttype and ordnum = new.ordnum; +update shipped_view set value = 11 + from int4_tbl a join int4_tbl b + on (a.f1 = (select f1 from int4_tbl c where c.f1=b.f1)) + where ordnum = a.f1; +select * from shipped_view; + ttype | ordnum | partnum | value +-------+--------+---------+------- + wt | 0 | 1 | 11 +(1 row) + +select f1, ss1 as relabel from + (select *, (select sum(f1) from int4_tbl b where f1 >= a.f1) as ss1 + from int4_tbl a) ss; + f1 | relabel +-------------+------------ + 0 | 2147607103 + 123456 | 2147607103 + -123456 | 2147483647 + 2147483647 | 2147483647 + -2147483647 | 0 +(5 rows) + +-- +-- Test cases involving PARAM_EXEC parameters and min/max index optimizations. +-- Per bug report from David Sanchez i Gregori. +-- +select * from ( + select max(unique1) from tenk1 as a + where exists (select 1 from tenk1 as b where b.thousand = a.unique2) +) ss; + max +------ + 9997 +(1 row) + +select * from ( + select min(unique1) from tenk1 as a + where not exists (select 1 from tenk1 as b where b.unique2 = 10000) +) ss; + min +----- + 0 +(1 row) + +-- +-- Test that an IN implemented using a UniquePath does unique-ification +-- with the right semantics, as per bug #4113. (Unfortunately we have +-- no simple way to ensure that this test case actually chooses that type +-- of plan, but it does in releases 7.4-8.3. Note that an ordering difference +-- here might mean that some other plan type is being used, rendering the test +-- pointless.) +-- +create temp table numeric_table (num_col numeric); +insert into numeric_table values (1), (1.000000000000000000001), (2), (3); +create temp table float_table (float_col float8); +insert into float_table values (1), (2), (3); +select * from float_table + where float_col in (select num_col from numeric_table); + float_col +----------- + 1 + 2 + 3 +(3 rows) + +select * from numeric_table + where num_col in (select float_col from float_table); + num_col +------------------------- + 1 + 1.000000000000000000001 + 2 + 3 +(4 rows) + +-- +-- Test case for bug #4290: bogus calculation of subplan param sets +-- +create temp table ta (id int primary key, val int); +insert into ta values(1,1); +insert into ta values(2,2); +create temp table tb (id int primary key, aval int); +insert into tb values(1,1); +insert into tb values(2,1); +insert into tb values(3,2); +insert into tb values(4,2); +create temp table tc (id int primary key, aid int); +insert into tc values(1,1); +insert into tc values(2,2); +select + ( select min(tb.id) from tb + where tb.aval = (select ta.val from ta where ta.id = tc.aid) ) as min_tb_id +from tc; + min_tb_id +----------- + 1 + 3 +(2 rows) + +-- +-- Test case for 8.3 "failed to locate grouping columns" bug +-- +create temp table t1 (f1 numeric(14,0), f2 varchar(30)); +select * from + (select distinct f1, f2, (select f2 from t1 x where x.f1 = up.f1) as fs + from t1 up) ss +group by f1,f2,fs; + f1 | f2 | fs +----+----+---- +(0 rows) + +-- +-- Test case for bug #5514 (mishandling of whole-row Vars in subselects) +-- +create temp table table_a(id integer); +insert into table_a values (42); +create temp view view_a as select * from table_a; +select view_a from view_a; + view_a +-------- + (42) +(1 row) + +select (select view_a) from view_a; + view_a +-------- + (42) +(1 row) + +select (select (select view_a)) from view_a; + view_a +-------- + (42) +(1 row) + +select (select (a.*)::text) from view_a a; + a +------ + (42) +(1 row) + +-- +-- Check that whole-row Vars reading the result of a subselect don't include +-- any junk columns therein +-- +select q from (select max(f1) from int4_tbl group by f1 order by f1) q; + q +--------------- + (-2147483647) + (-123456) + (0) + (123456) + (2147483647) +(5 rows) + +with q as (select max(f1) from int4_tbl group by f1 order by f1) + select q from q; + q +--------------- + (-2147483647) + (-123456) + (0) + (123456) + (2147483647) +(5 rows) + +-- +-- Test case for sublinks pulled up into joinaliasvars lists in an +-- inherited update/delete query +-- +begin; -- this shouldn't delete anything, but be safe +delete from road +where exists ( + select 1 + from + int4_tbl cross join + ( select f1, array(select q1 from int8_tbl) as arr + from text_tbl ) ss + where road.name = ss.f1 ); +rollback; +-- +-- Test case for sublinks pushed down into subselects via join alias expansion +-- +select + (select sq1) as qq1 +from + (select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy + from int8_tbl) sq0 + join + int4_tbl i4 on dummy = i4.f1; + qq1 +----- +(0 rows) + +-- +-- Test case for subselect within UPDATE of INSERT...ON CONFLICT DO UPDATE +-- +create temp table upsert(key int4 primary key, val text); +insert into upsert values(1, 'val') on conflict (key) do update set val = 'not seen'; +insert into upsert values(1, 'val') on conflict (key) do update set val = 'seen with subselect ' || (select f1 from int4_tbl where f1 != 0 limit 1)::text; +select * from upsert; + key | val +-----+---------------------------- + 1 | seen with subselect 123456 +(1 row) + +with aa as (select 'int4_tbl' u from int4_tbl limit 1) +insert into upsert values (1, 'x'), (999, 'y') +on conflict (key) do update set val = (select u from aa) +returning *; + key | val +-----+---------- + 1 | int4_tbl + 999 | y +(2 rows) + +-- +-- Test case for cross-type partial matching in hashed subplan (bug #7597) +-- +create temp table outer_7597 (f1 int4, f2 int4); +insert into outer_7597 values (0, 0); +insert into outer_7597 values (1, 0); +insert into outer_7597 values (0, null); +insert into outer_7597 values (1, null); +create temp table inner_7597(c1 int8, c2 int8); +insert into inner_7597 values(0, null); +select * from outer_7597 where (f1, f2) not in (select * from inner_7597); + f1 | f2 +----+---- + 1 | 0 + 1 | +(2 rows) + +-- +-- Similar test case using text that verifies that collation +-- information is passed through by execTuplesEqual() in nodeSubplan.c +-- (otherwise it would error in texteq()) +-- +create temp table outer_text (f1 text, f2 text); +insert into outer_text values ('a', 'a'); +insert into outer_text values ('b', 'a'); +insert into outer_text values ('a', null); +insert into outer_text values ('b', null); +create temp table inner_text (c1 text, c2 text); +insert into inner_text values ('a', null); +insert into inner_text values ('123', '456'); +select * from outer_text where (f1, f2) not in (select * from inner_text); + f1 | f2 +----+---- + b | a + b | +(2 rows) + +-- +-- Another test case for cross-type hashed subplans: comparison of +-- inner-side values must be done with appropriate operator +-- +explain (verbose, costs off) +select 'foo'::text in (select 'bar'::name union all select 'bar'::name); + QUERY PLAN +------------------------------------- + Result + Output: (hashed SubPlan 1) + SubPlan 1 + -> Append + -> Result + Output: 'bar'::name + -> Result + Output: 'bar'::name +(8 rows) + +select 'foo'::text in (select 'bar'::name union all select 'bar'::name); + ?column? +---------- + f +(1 row) + +-- +-- Test case for premature memory release during hashing of subplan output +-- +select '1'::text in (select '1'::name union all select '1'::name); + ?column? +---------- + t +(1 row) + +-- +-- Test that we don't try to use a hashed subplan if the simplified +-- testexpr isn't of the right shape +-- +-- this fails by default, of course +select * from int8_tbl where q1 in (select c1 from inner_text); +ERROR: operator does not exist: bigint = text +LINE 1: select * from int8_tbl where q1 in (select c1 from inner_tex... + ^ +HINT: No operator matches the given name and argument types. You might need to add explicit type casts. +begin; +-- make an operator to allow it to succeed +create function bogus_int8_text_eq(int8, text) returns boolean +language sql as 'select $1::text = $2'; +create operator = (procedure=bogus_int8_text_eq, leftarg=int8, rightarg=text); +explain (costs off) +select * from int8_tbl where q1 in (select c1 from inner_text); + QUERY PLAN +-------------------------------- + Seq Scan on int8_tbl + Filter: (hashed SubPlan 1) + SubPlan 1 + -> Seq Scan on inner_text +(4 rows) + +select * from int8_tbl where q1 in (select c1 from inner_text); + q1 | q2 +-----+------------------ + 123 | 456 + 123 | 4567890123456789 +(2 rows) + +-- inlining of this function results in unusual number of hash clauses, +-- which we can still cope with +create or replace function bogus_int8_text_eq(int8, text) returns boolean +language sql as 'select $1::text = $2 and $1::text = $2'; +explain (costs off) +select * from int8_tbl where q1 in (select c1 from inner_text); + QUERY PLAN +-------------------------------- + Seq Scan on int8_tbl + Filter: (hashed SubPlan 1) + SubPlan 1 + -> Seq Scan on inner_text +(4 rows) + +select * from int8_tbl where q1 in (select c1 from inner_text); + q1 | q2 +-----+------------------ + 123 | 456 + 123 | 4567890123456789 +(2 rows) + +-- inlining of this function causes LHS and RHS to be switched, +-- which we can't cope with, so hashing should be abandoned +create or replace function bogus_int8_text_eq(int8, text) returns boolean +language sql as 'select $2 = $1::text'; +explain (costs off) +select * from int8_tbl where q1 in (select c1 from inner_text); + QUERY PLAN +-------------------------------------- + Seq Scan on int8_tbl + Filter: (SubPlan 1) + SubPlan 1 + -> Materialize + -> Seq Scan on inner_text +(5 rows) + +select * from int8_tbl where q1 in (select c1 from inner_text); + q1 | q2 +-----+------------------ + 123 | 456 + 123 | 4567890123456789 +(2 rows) + +rollback; -- to get rid of the bogus operator +-- +-- Test case for planner bug with nested EXISTS handling +-- +select a.thousand from tenk1 a, tenk1 b +where a.thousand = b.thousand + and exists ( select 1 from tenk1 c where b.hundred = c.hundred + and not exists ( select 1 from tenk1 d + where a.thousand = d.thousand ) ); + thousand +---------- +(0 rows) + +-- +-- Check that nested sub-selects are not pulled up if they contain volatiles +-- +explain (verbose, costs off) + select x, x from + (select (select now()) as x from (values(1),(2)) v(y)) ss; + QUERY PLAN +--------------------------- + Values Scan on "*VALUES*" + Output: $0, $1 + InitPlan 1 (returns $0) + -> Result + Output: now() + InitPlan 2 (returns $1) + -> Result + Output: now() +(8 rows) + +explain (verbose, costs off) + select x, x from + (select (select random()) as x from (values(1),(2)) v(y)) ss; + QUERY PLAN +---------------------------------- + Subquery Scan on ss + Output: ss.x, ss.x + -> Values Scan on "*VALUES*" + Output: $0 + InitPlan 1 (returns $0) + -> Result + Output: random() +(7 rows) + +explain (verbose, costs off) + select x, x from + (select (select now() where y=y) as x from (values(1),(2)) v(y)) ss; + QUERY PLAN +---------------------------------------------------------------------- + Values Scan on "*VALUES*" + Output: (SubPlan 1), (SubPlan 2) + SubPlan 1 + -> Result + Output: now() + One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) + SubPlan 2 + -> Result + Output: now() + One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) +(10 rows) + +explain (verbose, costs off) + select x, x from + (select (select random() where y=y) as x from (values(1),(2)) v(y)) ss; + QUERY PLAN +---------------------------------------------------------------------------- + Subquery Scan on ss + Output: ss.x, ss.x + -> Values Scan on "*VALUES*" + Output: (SubPlan 1) + SubPlan 1 + -> Result + Output: random() + One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1) +(8 rows) + +-- +-- Test rescan of a hashed subplan (the use of random() is to prevent the +-- sub-select from being pulled up, which would result in not hashing) +-- +explain (verbose, costs off) +select sum(ss.tst::int) from + onek o cross join lateral ( + select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst, + random() as r + from onek i where i.unique1 = o.unique1 ) ss +where o.ten = 0; + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Aggregate + Output: sum((((hashed SubPlan 1)))::integer) + -> Nested Loop + Output: ((hashed SubPlan 1)) + -> Seq Scan on public.onek o + Output: o.unique1, o.unique2, o.two, o.four, o.ten, o.twenty, o.hundred, o.thousand, o.twothousand, o.fivethous, o.tenthous, o.odd, o.even, o.stringu1, o.stringu2, o.string4 + Filter: (o.ten = 0) + -> Index Scan using onek_unique1 on public.onek i + Output: (hashed SubPlan 1), random() + Index Cond: (i.unique1 = o.unique1) + SubPlan 1 + -> Seq Scan on public.int4_tbl + Output: int4_tbl.f1 + Filter: (int4_tbl.f1 <= $0) +(14 rows) + +select sum(ss.tst::int) from + onek o cross join lateral ( + select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst, + random() as r + from onek i where i.unique1 = o.unique1 ) ss +where o.ten = 0; + sum +----- + 100 +(1 row) + +-- +-- Test rescan of a SetOp node +-- +explain (costs off) +select count(*) from + onek o cross join lateral ( + select * from onek i1 where i1.unique1 = o.unique1 + except + select * from onek i2 where i2.unique1 = o.unique2 + ) ss +where o.ten = 1; + QUERY PLAN +------------------------------------------------------------------------------ + Aggregate + -> Nested Loop + -> Seq Scan on onek o + Filter: (ten = 1) + -> Subquery Scan on ss + -> HashSetOp Except + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Index Scan using onek_unique1 on onek i1 + Index Cond: (unique1 = o.unique1) + -> Subquery Scan on "*SELECT* 2" + -> Index Scan using onek_unique1 on onek i2 + Index Cond: (unique1 = o.unique2) +(13 rows) + +select count(*) from + onek o cross join lateral ( + select * from onek i1 where i1.unique1 = o.unique1 + except + select * from onek i2 where i2.unique1 = o.unique2 + ) ss +where o.ten = 1; + count +------- + 100 +(1 row) + +-- +-- Test rescan of a RecursiveUnion node +-- +explain (costs off) +select sum(o.four), sum(ss.a) from + onek o cross join lateral ( + with recursive x(a) as + (select o.four as a + union + select a + 1 from x + where a < 10) + select * from x + ) ss +where o.ten = 1; + QUERY PLAN +--------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on onek o + Filter: (ten = 1) + -> CTE Scan on x + CTE x + -> Recursive Union + -> Result + -> WorkTable Scan on x x_1 + Filter: (a < 10) +(10 rows) + +select sum(o.four), sum(ss.a) from + onek o cross join lateral ( + with recursive x(a) as + (select o.four as a + union + select a + 1 from x + where a < 10) + select * from x + ) ss +where o.ten = 1; + sum | sum +------+------ + 1700 | 5350 +(1 row) + +-- +-- Check we don't misoptimize a NOT IN where the subquery returns no rows. +-- +create temp table notinouter (a int); +create temp table notininner (b int not null); +insert into notinouter values (null), (1); +select * from notinouter where a not in (select b from notininner); + a +--- + + 1 +(2 rows) + +-- +-- Check we behave sanely in corner case of empty SELECT list (bug #8648) +-- +create temp table nocolumns(); +select exists(select * from nocolumns); + exists +-------- + f +(1 row) + +-- +-- Check behavior with a SubPlan in VALUES (bug #14924) +-- +select val.x + from generate_series(1,10) as s(i), + lateral ( + values ((select s.i + 1)), (s.i + 101) + ) as val(x) +where s.i < 10 and (select val.x) < 110; + x +----- + 2 + 102 + 3 + 103 + 4 + 104 + 5 + 105 + 6 + 106 + 7 + 107 + 8 + 108 + 9 + 109 + 10 +(17 rows) + +-- another variant of that (bug #16213) +explain (verbose, costs off) +select * from +(values + (3 not in (select * from (values (1), (2)) ss1)), + (false) +) ss; + QUERY PLAN +---------------------------------------- + Values Scan on "*VALUES*" + Output: "*VALUES*".column1 + SubPlan 1 + -> Values Scan on "*VALUES*_1" + Output: "*VALUES*_1".column1 +(5 rows) + +select * from +(values + (3 not in (select * from (values (1), (2)) ss1)), + (false) +) ss; + column1 +--------- + t + f +(2 rows) + +-- +-- Check sane behavior with nested IN SubLinks +-- +explain (verbose, costs off) +select * from int4_tbl where + (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in + (select ten from tenk1 b); + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Nested Loop Semi Join + Output: int4_tbl.f1 + Join Filter: (CASE WHEN (hashed SubPlan 1) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten) + -> Seq Scan on public.int4_tbl + Output: int4_tbl.f1 + -> Seq Scan on public.tenk1 b + Output: b.unique1, b.unique2, b.two, b.four, b.ten, b.twenty, b.hundred, b.thousand, b.twothousand, b.fivethous, b.tenthous, b.odd, b.even, b.stringu1, b.stringu2, b.string4 + SubPlan 1 + -> Index Only Scan using tenk1_unique1 on public.tenk1 a + Output: a.unique1 +(10 rows) + +select * from int4_tbl where + (case when f1 in (select unique1 from tenk1 a) then f1 else null end) in + (select ten from tenk1 b); + f1 +---- + 0 +(1 row) + +-- +-- Check for incorrect optimization when IN subquery contains a SRF +-- +explain (verbose, costs off) +select * from int4_tbl o where (f1, f1) in + (select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1); + QUERY PLAN +------------------------------------------------------------------- + Nested Loop Semi Join + Output: o.f1 + Join Filter: (o.f1 = "ANY_subquery".f1) + -> Seq Scan on public.int4_tbl o + Output: o.f1 + -> Materialize + Output: "ANY_subquery".f1, "ANY_subquery".g + -> Subquery Scan on "ANY_subquery" + Output: "ANY_subquery".f1, "ANY_subquery".g + Filter: ("ANY_subquery".f1 = "ANY_subquery".g) + -> Result + Output: i.f1, ((generate_series(1, 50)) / 10) + -> ProjectSet + Output: generate_series(1, 50), i.f1 + -> HashAggregate + Output: i.f1 + Group Key: i.f1 + -> Seq Scan on public.int4_tbl i + Output: i.f1 +(19 rows) + +select * from int4_tbl o where (f1, f1) in + (select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1); + f1 +---- + 0 +(1 row) + +-- +-- check for over-optimization of whole-row Var referencing an Append plan +-- +select (select q from + (select 1,2,3 where f1 > 0 + union all + select 4,5,6.0 where f1 <= 0 + ) q ) +from int4_tbl; + q +----------- + (4,5,6.0) + (1,2,3) + (4,5,6.0) + (1,2,3) + (4,5,6.0) +(5 rows) + +-- +-- Check for sane handling of a lateral reference in a subquery's quals +-- (most of the complication here is to prevent the test case from being +-- flattened too much) +-- +explain (verbose, costs off) +select * from + int4_tbl i4, + lateral ( + select i4.f1 > 1 as b, 1 as id + from (select random() order by 1) as t1 + union all + select true as b, 2 as id + ) as t2 +where b and f1 >= 0; + QUERY PLAN +-------------------------------------------- + Nested Loop + Output: i4.f1, ((i4.f1 > 1)), (1) + -> Seq Scan on public.int4_tbl i4 + Output: i4.f1 + Filter: (i4.f1 >= 0) + -> Append + -> Subquery Scan on t1 + Output: (i4.f1 > 1), 1 + Filter: (i4.f1 > 1) + -> Sort + Output: (random()) + Sort Key: (random()) + -> Result + Output: random() + -> Result + Output: true, 2 +(16 rows) + +select * from + int4_tbl i4, + lateral ( + select i4.f1 > 1 as b, 1 as id + from (select random() order by 1) as t1 + union all + select true as b, 2 as id + ) as t2 +where b and f1 >= 0; + f1 | b | id +------------+---+---- + 0 | t | 2 + 123456 | t | 1 + 123456 | t | 2 + 2147483647 | t | 1 + 2147483647 | t | 2 +(5 rows) + +-- +-- Check that volatile quals aren't pushed down past a DISTINCT: +-- nextval() should not be called more than the nominal number of times +-- +create temp sequence ts1; +select * from + (select distinct ten from tenk1) ss + where ten < 10 + nextval('ts1') + order by 1; + ten +----- + 0 + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 +(10 rows) + +select nextval('ts1'); + nextval +--------- + 11 +(1 row) + +-- +-- Check that volatile quals aren't pushed down past a set-returning function; +-- while a nonvolatile qual can be, if it doesn't reference the SRF. +-- +create function tattle(x int, y int) returns bool +volatile language plpgsql as $$ +begin + raise notice 'x = %, y = %', x, y; + return x > y; +end$$; +explain (verbose, costs off) +select * from + (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss + where tattle(x, 8); + QUERY PLAN +---------------------------------------------------------- + Subquery Scan on ss + Output: ss.x, ss.u + Filter: tattle(ss.x, 8) + -> ProjectSet + Output: 9, unnest('{1,2,3,11,12,13}'::integer[]) + -> Result +(6 rows) + +select * from + (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss + where tattle(x, 8); +NOTICE: x = 9, y = 8 +NOTICE: x = 9, y = 8 +NOTICE: x = 9, y = 8 +NOTICE: x = 9, y = 8 +NOTICE: x = 9, y = 8 +NOTICE: x = 9, y = 8 + x | u +---+---- + 9 | 1 + 9 | 2 + 9 | 3 + 9 | 11 + 9 | 12 + 9 | 13 +(6 rows) + +-- if we pretend it's stable, we get different results: +alter function tattle(x int, y int) stable; +explain (verbose, costs off) +select * from + (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss + where tattle(x, 8); + QUERY PLAN +---------------------------------------------------- + ProjectSet + Output: 9, unnest('{1,2,3,11,12,13}'::integer[]) + -> Result + One-Time Filter: tattle(9, 8) +(4 rows) + +select * from + (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss + where tattle(x, 8); +NOTICE: x = 9, y = 8 + x | u +---+---- + 9 | 1 + 9 | 2 + 9 | 3 + 9 | 11 + 9 | 12 + 9 | 13 +(6 rows) + +-- although even a stable qual should not be pushed down if it references SRF +explain (verbose, costs off) +select * from + (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss + where tattle(x, u); + QUERY PLAN +---------------------------------------------------------- + Subquery Scan on ss + Output: ss.x, ss.u + Filter: tattle(ss.x, ss.u) + -> ProjectSet + Output: 9, unnest('{1,2,3,11,12,13}'::integer[]) + -> Result +(6 rows) + +select * from + (select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss + where tattle(x, u); +NOTICE: x = 9, y = 1 +NOTICE: x = 9, y = 2 +NOTICE: x = 9, y = 3 +NOTICE: x = 9, y = 11 +NOTICE: x = 9, y = 12 +NOTICE: x = 9, y = 13 + x | u +---+--- + 9 | 1 + 9 | 2 + 9 | 3 +(3 rows) + +drop function tattle(x int, y int); +-- +-- Test that LIMIT can be pushed to SORT through a subquery that just projects +-- columns. We check for that having happened by looking to see if EXPLAIN +-- ANALYZE shows that a top-N sort was used. We must suppress or filter away +-- all the non-invariant parts of the EXPLAIN ANALYZE output. +-- +create table sq_limit (pk int primary key, c1 int, c2 int); +insert into sq_limit values + (1, 1, 1), + (2, 2, 2), + (3, 3, 3), + (4, 4, 4), + (5, 1, 1), + (6, 2, 2), + (7, 3, 3), + (8, 4, 4); +create function explain_sq_limit() returns setof text language plpgsql as +$$ +declare ln text; +begin + for ln in + explain (analyze, summary off, timing off, costs off) + select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3 + loop + ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); + return next ln; + end loop; +end; +$$; +select * from explain_sq_limit(); + explain_sq_limit +---------------------------------------------------------------- + Limit (actual rows=3 loops=1) + -> Subquery Scan on x (actual rows=3 loops=1) + -> Sort (actual rows=3 loops=1) + Sort Key: sq_limit.c1, sq_limit.pk + Sort Method: top-N heapsort Memory: xxx + -> Seq Scan on sq_limit (actual rows=8 loops=1) +(6 rows) + +select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3; + pk | c2 +----+---- + 1 | 1 + 5 | 1 + 2 | 2 +(3 rows) + +drop function explain_sq_limit(); +drop table sq_limit; +-- +-- Ensure that backward scan direction isn't propagated into +-- expression subqueries (bug #15336) +-- +begin; +declare c1 scroll cursor for + select * from generate_series(1,4) i + where i <> all (values (2),(3)); +move forward all in c1; +fetch backward all in c1; + i +--- + 4 + 1 +(2 rows) + +commit; +-- +-- Tests for CTE inlining behavior +-- +-- Basic subquery that can be inlined +explain (verbose, costs off) +with x as (select * from (select f1 from subselect_tbl) ss) +select * from x where f1 = 1; + QUERY PLAN +---------------------------------- + Seq Scan on public.subselect_tbl + Output: subselect_tbl.f1 + Filter: (subselect_tbl.f1 = 1) +(3 rows) + +-- Explicitly request materialization +explain (verbose, costs off) +with x as materialized (select * from (select f1 from subselect_tbl) ss) +select * from x where f1 = 1; + QUERY PLAN +------------------------------------------ + CTE Scan on x + Output: x.f1 + Filter: (x.f1 = 1) + CTE x + -> Seq Scan on public.subselect_tbl + Output: subselect_tbl.f1 +(6 rows) + +-- Stable functions are safe to inline +explain (verbose, costs off) +with x as (select * from (select f1, now() from subselect_tbl) ss) +select * from x where f1 = 1; + QUERY PLAN +----------------------------------- + Seq Scan on public.subselect_tbl + Output: subselect_tbl.f1, now() + Filter: (subselect_tbl.f1 = 1) +(3 rows) + +-- Volatile functions prevent inlining +explain (verbose, costs off) +with x as (select * from (select f1, random() from subselect_tbl) ss) +select * from x where f1 = 1; + QUERY PLAN +---------------------------------------------- + CTE Scan on x + Output: x.f1, x.random + Filter: (x.f1 = 1) + CTE x + -> Seq Scan on public.subselect_tbl + Output: subselect_tbl.f1, random() +(6 rows) + +-- SELECT FOR UPDATE cannot be inlined +explain (verbose, costs off) +with x as (select * from (select f1 from subselect_tbl for update) ss) +select * from x where f1 = 1; + QUERY PLAN +-------------------------------------------------------------------- + CTE Scan on x + Output: x.f1 + Filter: (x.f1 = 1) + CTE x + -> Subquery Scan on ss + Output: ss.f1 + -> LockRows + Output: subselect_tbl.f1, subselect_tbl.ctid + -> Seq Scan on public.subselect_tbl + Output: subselect_tbl.f1, subselect_tbl.ctid +(10 rows) + +-- Multiply-referenced CTEs are inlined only when requested +explain (verbose, costs off) +with x as (select * from (select f1, now() as n from subselect_tbl) ss) +select * from x, x x2 where x.n = x2.n; + QUERY PLAN +------------------------------------------- + Merge Join + Output: x.f1, x.n, x2.f1, x2.n + Merge Cond: (x.n = x2.n) + CTE x + -> Seq Scan on public.subselect_tbl + Output: subselect_tbl.f1, now() + -> Sort + Output: x.f1, x.n + Sort Key: x.n + -> CTE Scan on x + Output: x.f1, x.n + -> Sort + Output: x2.f1, x2.n + Sort Key: x2.n + -> CTE Scan on x x2 + Output: x2.f1, x2.n +(16 rows) + +explain (verbose, costs off) +with x as not materialized (select * from (select f1, now() as n from subselect_tbl) ss) +select * from x, x x2 where x.n = x2.n; + QUERY PLAN +---------------------------------------------------------------------------- + Result + Output: subselect_tbl.f1, now(), subselect_tbl_1.f1, now() + One-Time Filter: (now() = now()) + -> Nested Loop + Output: subselect_tbl.f1, subselect_tbl_1.f1 + -> Seq Scan on public.subselect_tbl + Output: subselect_tbl.f1, subselect_tbl.f2, subselect_tbl.f3 + -> Materialize + Output: subselect_tbl_1.f1 + -> Seq Scan on public.subselect_tbl subselect_tbl_1 + Output: subselect_tbl_1.f1 +(11 rows) + +-- Multiply-referenced CTEs can't be inlined if they contain outer self-refs +explain (verbose, costs off) +with recursive x(a) as + ((values ('a'), ('b')) + union all + (with z as not materialized (select * from x) + select z.a || z1.a as a from z cross join z as z1 + where length(z.a || z1.a) < 5)) +select * from x; + QUERY PLAN +---------------------------------------------------------- + CTE Scan on x + Output: x.a + CTE x + -> Recursive Union + -> Values Scan on "*VALUES*" + Output: "*VALUES*".column1 + -> Nested Loop + Output: (z.a || z1.a) + Join Filter: (length((z.a || z1.a)) < 5) + CTE z + -> WorkTable Scan on x x_1 + Output: x_1.a + -> CTE Scan on z + Output: z.a + -> CTE Scan on z z1 + Output: z1.a +(16 rows) + +with recursive x(a) as + ((values ('a'), ('b')) + union all + (with z as not materialized (select * from x) + select z.a || z1.a as a from z cross join z as z1 + where length(z.a || z1.a) < 5)) +select * from x; + a +------ + a + b + aa + ab + ba + bb + aaaa + aaab + aaba + aabb + abaa + abab + abba + abbb + baaa + baab + baba + babb + bbaa + bbab + bbba + bbbb +(22 rows) + +explain (verbose, costs off) +with recursive x(a) as + ((values ('a'), ('b')) + union all + (with z as not materialized (select * from x) + select z.a || z.a as a from z + where length(z.a || z.a) < 5)) +select * from x; + QUERY PLAN +-------------------------------------------------------- + CTE Scan on x + Output: x.a + CTE x + -> Recursive Union + -> Values Scan on "*VALUES*" + Output: "*VALUES*".column1 + -> WorkTable Scan on x x_1 + Output: (x_1.a || x_1.a) + Filter: (length((x_1.a || x_1.a)) < 5) +(9 rows) + +with recursive x(a) as + ((values ('a'), ('b')) + union all + (with z as not materialized (select * from x) + select z.a || z.a as a from z + where length(z.a || z.a) < 5)) +select * from x; + a +------ + a + b + aa + bb + aaaa + bbbb +(6 rows) + +-- Check handling of outer references +explain (verbose, costs off) +with x as (select * from int4_tbl) +select * from (with y as (select * from x) select * from y) ss; + QUERY PLAN +----------------------------- + Seq Scan on public.int4_tbl + Output: int4_tbl.f1 +(2 rows) + +explain (verbose, costs off) +with x as materialized (select * from int4_tbl) +select * from (with y as (select * from x) select * from y) ss; + QUERY PLAN +------------------------------------- + CTE Scan on x + Output: x.f1 + CTE x + -> Seq Scan on public.int4_tbl + Output: int4_tbl.f1 +(5 rows) + +-- Ensure that we inline the currect CTE when there are +-- multiple CTEs with the same name +explain (verbose, costs off) +with x as (select 1 as y) +select * from (with x as (select 2 as y) select * from x) ss; + QUERY PLAN +------------- + Result + Output: 2 +(2 rows) + +-- Row marks are not pushed into CTEs +explain (verbose, costs off) +with x as (select * from subselect_tbl) +select * from x for update; + QUERY PLAN +---------------------------------------------------------------- + Seq Scan on public.subselect_tbl + Output: subselect_tbl.f1, subselect_tbl.f2, subselect_tbl.f3 +(2 rows) + -- cgit v1.2.3