diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/modules/test_bitmapset/test_bitmapset.c | 4 | ||||
| -rw-r--r-- | src/test/regress/expected/aggregates.out | 51 | ||||
| -rw-r--r-- | src/test/regress/expected/case.out | 21 | ||||
| -rw-r--r-- | src/test/regress/expected/explain.out | 8 | ||||
| -rw-r--r-- | src/test/regress/expected/generated_virtual.out | 25 | ||||
| -rw-r--r-- | src/test/regress/expected/groupingsets.out | 3 | ||||
| -rw-r--r-- | src/test/regress/expected/inherit.out | 28 | ||||
| -rw-r--r-- | src/test/regress/expected/join.out | 143 | ||||
| -rw-r--r-- | src/test/regress/expected/merge.out | 3 | ||||
| -rw-r--r-- | src/test/regress/expected/partition_aggregate.out | 21 | ||||
| -rw-r--r-- | src/test/regress/expected/partition_join.out | 35 | ||||
| -rw-r--r-- | src/test/regress/expected/partition_prune.out | 141 | ||||
| -rw-r--r-- | src/test/regress/expected/predicate.out | 33 | ||||
| -rw-r--r-- | src/test/regress/expected/rowsecurity.out | 13 | ||||
| -rw-r--r-- | src/test/regress/expected/rowtypes.out | 6 | ||||
| -rw-r--r-- | src/test/regress/expected/select.out | 7 | ||||
| -rw-r--r-- | src/test/regress/expected/subselect.out | 17 | ||||
| -rw-r--r-- | src/test/regress/expected/tsrf.out | 6 | ||||
| -rw-r--r-- | src/test/regress/sql/explain.sql | 1 |
19 files changed, 340 insertions, 226 deletions
diff --git a/src/test/modules/test_bitmapset/test_bitmapset.c b/src/test/modules/test_bitmapset/test_bitmapset.c index 61f256f65a4..5bc4daa23f1 100644 --- a/src/test/modules/test_bitmapset/test_bitmapset.c +++ b/src/test/modules/test_bitmapset/test_bitmapset.c @@ -84,8 +84,8 @@ PG_FUNCTION_INFO_V1(test_random_operations); } while (0) /* Encode/Decode to/from TEXT and Bitmapset */ -#define BITMAPSET_TO_TEXT(bms) (text *) CStringGetTextDatum(nodeToString((bms))) -#define TEXT_TO_BITMAPSET(str) (Bitmapset *) stringToNode(TextDatumGetCString((Datum) (str))) +#define BITMAPSET_TO_TEXT(bms) cstring_to_text(nodeToString(bms)) +#define TEXT_TO_BITMAPSET(str) ((Bitmapset *) stringToNode(text_to_cstring(str))) /* * Individual test functions for each bitmapset API function diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index c35288eecde..1f24f6ffd1f 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -959,11 +959,12 @@ explain (costs off) QUERY PLAN ------------------------------------------------------------ Result + Replaces: MinMaxAggregate InitPlan 1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: (unique1 IS NOT NULL) -(5 rows) +(6 rows) select min(unique1) from tenk1; min @@ -976,11 +977,12 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------- Result + Replaces: MinMaxAggregate InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: (unique1 IS NOT NULL) -(5 rows) +(6 rows) select max(unique1) from tenk1; max @@ -993,11 +995,12 @@ explain (costs off) QUERY PLAN ------------------------------------------------------------------------ Result + Replaces: MinMaxAggregate InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 < 42)) -(5 rows) +(6 rows) select max(unique1) from tenk1 where unique1 < 42; max @@ -1010,11 +1013,12 @@ explain (costs off) QUERY PLAN ------------------------------------------------------------------------ Result + Replaces: MinMaxAggregate InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42)) -(5 rows) +(6 rows) select max(unique1) from tenk1 where unique1 > 42; max @@ -1033,11 +1037,12 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------------- Result + Replaces: MinMaxAggregate InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > 42000)) -(5 rows) +(6 rows) select max(unique1) from tenk1 where unique1 > 42000; max @@ -1052,11 +1057,12 @@ explain (costs off) QUERY PLAN ---------------------------------------------------------------------------- Result + Replaces: MinMaxAggregate InitPlan 1 -> Limit -> Index Only Scan Backward using tenk1_thous_tenthous on tenk1 Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL)) -(5 rows) +(6 rows) select max(tenthous) from tenk1 where thousand = 33; max @@ -1069,11 +1075,12 @@ explain (costs off) QUERY PLAN -------------------------------------------------------------------------- Result + Replaces: MinMaxAggregate InitPlan 1 -> Limit -> Index Only Scan using tenk1_thous_tenthous on tenk1 Index Cond: ((thousand = 33) AND (tenthous IS NOT NULL)) -(5 rows) +(6 rows) select min(tenthous) from tenk1 where thousand = 33; min @@ -1090,11 +1097,12 @@ explain (costs off) Seq Scan on int4_tbl SubPlan 2 -> Result + Replaces: MinMaxAggregate InitPlan 1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 > int4_tbl.f1)) -(7 rows) +(8 rows) select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt from int4_tbl; @@ -1119,7 +1127,8 @@ explain (costs off) -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) -> Result -(7 rows) + Replaces: MinMaxAggregate +(8 rows) select distinct max(unique2) from tenk1; max @@ -1138,7 +1147,8 @@ explain (costs off) -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) -> Result -(7 rows) + Replaces: MinMaxAggregate +(8 rows) select max(unique2) from tenk1 order by 1; max @@ -1157,7 +1167,8 @@ explain (costs off) -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) -> Result -(7 rows) + Replaces: MinMaxAggregate +(8 rows) select max(unique2) from tenk1 order by max(unique2); max @@ -1176,7 +1187,8 @@ explain (costs off) -> Index Only Scan Backward using tenk1_unique2 on tenk1 Index Cond: (unique2 IS NOT NULL) -> Result -(7 rows) + Replaces: MinMaxAggregate +(8 rows) select max(unique2) from tenk1 order by max(unique2)+1; max @@ -1196,7 +1208,8 @@ explain (costs off) Index Cond: (unique2 IS NOT NULL) -> ProjectSet -> Result -(8 rows) + Replaces: MinMaxAggregate +(9 rows) select max(unique2), generate_series(1,3) as g from tenk1 order by g desc; max | g @@ -1212,12 +1225,13 @@ explain (costs off) QUERY PLAN ---------------------------------------------------- Result + Replaces: MinMaxAggregate InitPlan 1 -> Limit -> Result One-Time Filter: (100 IS NOT NULL) -> Seq Scan on tenk1 -(6 rows) +(7 rows) select max(100) from tenk1; max @@ -1243,6 +1257,7 @@ explain (costs off) QUERY PLAN --------------------------------------------------------------------------------------------- Result + Replaces: MinMaxAggregate InitPlan 1 -> Limit -> Merge Append @@ -1265,7 +1280,7 @@ explain (costs off) -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest_8 Index Cond: (f1 IS NOT NULL) -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest_9 -(23 rows) +(24 rows) select min(f1), max(f1) from minmaxtest; min | max @@ -1304,7 +1319,8 @@ explain (costs off) -> Sort Sort Key: ((InitPlan 1).col1), ((InitPlan 2).col1) -> Result -(26 rows) + Replaces: MinMaxAggregate +(27 rows) select distinct min(f1), max(f1) from minmaxtest; min | max @@ -1334,7 +1350,8 @@ explain (costs off) -> Seq Scan on int4_tbl t1 Filter: ((f1 IS NOT NULL) AND (f1 = t0.f1)) -> Result -(9 rows) + Replaces: MinMaxAggregate +(10 rows) select f1, (select distinct min(t1.f1) from int4_tbl t1 where t1.f1 = t0.f1) from int4_tbl t0; diff --git a/src/test/regress/expected/case.out b/src/test/regress/expected/case.out index efee7fc4317..4c77f7e3961 100644 --- a/src/test/regress/expected/case.out +++ b/src/test/regress/expected/case.out @@ -266,27 +266,30 @@ SELECT * -- Tests for constant subexpression simplification explain (costs off) SELECT * FROM CASE_TBL WHERE NULLIF(1, 2) = 2; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------ Result + Replaces: Scan on case_tbl One-Time Filter: false -(2 rows) +(3 rows) explain (costs off) SELECT * FROM CASE_TBL WHERE NULLIF(1, 1) IS NOT NULL; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------ Result + Replaces: Scan on case_tbl One-Time Filter: false -(2 rows) +(3 rows) explain (costs off) SELECT * FROM CASE_TBL WHERE NULLIF(1, null) = 2; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------ Result + Replaces: Scan on case_tbl One-Time Filter: false -(2 rows) +(3 rows) -- -- Examples of updates involving tables diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out index c53bf9c8aa3..7c1f26b182c 100644 --- a/src/test/regress/expected/explain.out +++ b/src/test/regress/expected/explain.out @@ -54,6 +54,14 @@ set jit = off; -- enabled. set track_io_timing = off; -- Simple cases +explain (costs off) select 1 as a, 2 as b having false; + QUERY PLAN +-------------------------- + Result + Replaces: Aggregate + One-Time Filter: false +(3 rows) + select explain_filter('explain select * from int8_tbl i8'); explain_filter --------------------------------------------------------- diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index d8645192351..c861bd36c5a 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -1570,17 +1570,18 @@ where coalesce(t2.b, 1) = 2 or t1.a is null; -- Ensure that the generation expressions are wrapped into PHVs if needed explain (verbose, costs off) select t2.* from gtest32 t1 left join gtest32 t2 on false; - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------- Nested Loop Left Join - Output: a, (a * 2), (20), (COALESCE(a, 100)), e + Output: t2.a, (t2.a * 2), (20), (COALESCE(t2.a, 100)), t2.e Join Filter: false -> Seq Scan on generated_virtual_tests.gtest32 t1 Output: t1.a, t1.b, t1.c, t1.d, t1.e -> Result - Output: a, e, 20, COALESCE(a, 100) + Output: t2.a, t2.e, 20, COALESCE(t2.a, 100) + Replaces: Scan on t2 One-Time Filter: false -(8 rows) +(9 rows) select t2.* from gtest32 t1 left join gtest32 t2 on false; a | b | c | d | e @@ -1642,20 +1643,22 @@ set constraint_exclusion to on; -- should get a dummy Result, not a seq scan explain (costs off) select * from gtest33 where b < 10; - QUERY PLAN --------------------------- + QUERY PLAN +----------------------------- Result + Replaces: Scan on gtest33 One-Time Filter: false -(2 rows) +(3 rows) -- should get a dummy Result, not a seq scan explain (costs off) select * from gtest33 where b is null; - QUERY PLAN --------------------------- + QUERY PLAN +----------------------------- Result + Replaces: Scan on gtest33 One-Time Filter: false -(2 rows) +(3 rows) reset constraint_exclusion; drop table gtest33; diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out index 35e4cb47ebe..210bbe307a7 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -591,11 +591,12 @@ explain (costs off) QUERY PLAN ------------------------------------------------------------ Result + Replaces: MinMaxAggregate InitPlan 1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: (unique1 IS NOT NULL) -(5 rows) +(6 rows) -- Views with GROUPING SET queries CREATE VIEW gstest_view AS select a, b, grouping(a,b), sum(c), count(*), max(c) diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 5b5055babdc..031dd87424a 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -580,8 +580,9 @@ update some_tab set a = a + 1 where false; Update on public.some_tab -> Result Output: (some_tab.a + 1), NULL::oid, NULL::tid + Replaces: Scan on some_tab One-Time Filter: false -(4 rows) +(5 rows) update some_tab set a = a + 1 where false; explain (verbose, costs off) @@ -592,8 +593,9 @@ update some_tab set a = a + 1 where false returning b, a; Output: some_tab.b, some_tab.a -> Result Output: (some_tab.a + 1), NULL::oid, NULL::tid + Replaces: Scan on some_tab One-Time Filter: false -(5 rows) +(6 rows) update some_tab set a = a + 1 where false returning b, a; b | a @@ -699,8 +701,9 @@ explain update parted_tab set a = 2 where false; -------------------------------------------------------- Update on parted_tab (cost=0.00..0.00 rows=0 width=0) -> Result (cost=0.00..0.00 rows=0 width=10) + Replaces: Scan on parted_tab One-Time Filter: false -(3 rows) +(4 rows) drop table parted_tab; -- Check UPDATE with multi-level partitioned inherited target @@ -1756,6 +1759,7 @@ explain (verbose, costs off) select min(1-id) from matest0; --------------------------------------------------------------------------------- Result Output: (InitPlan 1).col1 + Replaces: MinMaxAggregate InitPlan 1 -> Limit Output: ((1 - matest0.id)) @@ -1779,7 +1783,7 @@ explain (verbose, costs off) select min(1-id) from matest0; -> Index Scan using matest3i on public.matest3 matest0_4 Output: matest0_4.id, (1 - matest0_4.id) Index Cond: ((1 - matest0_4.id) IS NOT NULL) -(25 rows) +(26 rows) select min(1-id) from matest0; min @@ -1943,6 +1947,7 @@ SELECT min(x) FROM QUERY PLAN -------------------------------------------------------------------- Result + Replaces: MinMaxAggregate InitPlan 1 -> Limit -> Merge Append @@ -1951,7 +1956,7 @@ SELECT min(x) FROM Index Cond: (unique1 IS NOT NULL) -> Index Only Scan using tenk1_unique2 on tenk1 b Index Cond: (unique2 IS NOT NULL) -(9 rows) +(10 rows) explain (costs off) SELECT min(y) FROM @@ -1961,6 +1966,7 @@ SELECT min(y) FROM QUERY PLAN -------------------------------------------------------------------- Result + Replaces: MinMaxAggregate InitPlan 1 -> Limit -> Merge Append @@ -1969,7 +1975,7 @@ SELECT min(y) FROM Index Cond: (unique1 IS NOT NULL) -> Index Only Scan using tenk1_unique2 on tenk1 b Index Cond: (unique2 IS NOT NULL) -(9 rows) +(10 rows) -- XXX planner doesn't recognize that index on unique2 is sufficiently sorted explain (costs off) @@ -3087,11 +3093,12 @@ explain (costs off) select * from range_list_parted where a between 3 and 23 and /* Should select no rows because range partition key cannot be null */ explain (costs off) select * from range_list_parted where a is null; - QUERY PLAN --------------------------- + QUERY PLAN +--------------------------------------- Result + Replaces: Scan on range_list_parted One-Time Filter: false -(2 rows) +(3 rows) /* Should only select rows from the null-accepting partition */ explain (costs off) select * from range_list_parted where b is null; @@ -3252,6 +3259,7 @@ explain (costs off) select min(a), max(a) from parted_minmax where b = '12345'; QUERY PLAN ------------------------------------------------------------------------------------------------ Result + Replaces: MinMaxAggregate InitPlan 1 -> Limit -> Index Only Scan using parted_minmax1i on parted_minmax1 parted_minmax @@ -3260,7 +3268,7 @@ explain (costs off) select min(a), max(a) from parted_minmax where b = '12345'; -> Limit -> Index Only Scan Backward using parted_minmax1i on parted_minmax1 parted_minmax_1 Index Cond: ((a IS NOT NULL) AND (b = '12345'::text)) -(9 rows) +(10 rows) select min(a), max(a) from parted_minmax where b = '12345'; min | max diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 04079268b98..cd37f549b5a 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2264,11 +2264,12 @@ explain (costs off) select aa, bb, unique1, unique1 from tenk1 right join b_star on aa = unique1 where bb < bb and bb is null; - QUERY PLAN --------------------------- + QUERY PLAN +----------------------------------- Result + Replaces: Join on tenk1, b_star One-Time Filter: false -(2 rows) +(3 rows) select aa, bb, unique1, unique1 from tenk1 right join b_star on aa = unique1 @@ -2386,11 +2387,12 @@ order by t1.unique1; Index Cond: (unique1 < 10) SubPlan 2 -> Result + Replaces: MinMaxAggregate InitPlan 1 -> Limit -> Index Only Scan using tenk1_unique1 on tenk1 Index Cond: ((unique1 IS NOT NULL) AND (unique1 = t2.unique1)) -(19 rows) +(20 rows) -- Ensure we get the expected result select t1.unique1,t2.unique1 from tenk1 t1 @@ -2655,8 +2657,8 @@ select * from int8_tbl t1 left join (int8_tbl t2 left join int8_tbl t3 full join int8_tbl t4 on false on false) left join int8_tbl t5 on t2.q1 = t5.q1 on t2.q2 = 123; - QUERY PLAN --------------------------------------------------- + QUERY PLAN +---------------------------------------------------- Nested Loop Left Join -> Seq Scan on int8_tbl t1 -> Materialize @@ -2667,9 +2669,10 @@ on t2.q2 = 123; -> Seq Scan on int8_tbl t2 Filter: (q2 = 123) -> Result + Replaces: Join on t3, t4 One-Time Filter: false -> Seq Scan on int8_tbl t5 -(12 rows) +(13 rows) explain (costs off) select * from int8_tbl t1 @@ -4148,9 +4151,9 @@ select * from t t1 QUERY PLAN ---------------------------------------------------------- Nested Loop Left Join - Output: t1.i, (1), t2.i2, i3, t4.i4 + Output: t1.i, (1), t2.i2, t3.i3, t4.i4 -> Nested Loop Left Join - Output: t1.i, t2.i2, (1), i3 + Output: t1.i, t2.i2, (1), t3.i3 Join Filter: false -> Hash Left Join Output: t1.i, t2.i2, (1) @@ -4163,7 +4166,8 @@ select * from t t1 -> Seq Scan on pg_temp.t t2 Output: t2.i2, 1 -> Result - Output: i3 + Output: t3.i3 + Replaces: Scan on t3 One-Time Filter: false -> Memoize Output: t4.i4 @@ -4172,7 +4176,7 @@ select * from t t1 -> Index Only Scan using t_pkey on pg_temp.t t4 Output: t4.i4 Index Cond: (t4.i4 > (1)) -(25 rows) +(26 rows) explain (verbose, costs off) select * from @@ -4362,8 +4366,9 @@ from int4_tbl t1 ------------------------------------------------------------------------------------------------------------------- Result Output: (current_database())::information_schema.sql_identifier, (c.relname)::information_schema.sql_identifier + Replaces: Join on t1, t2, a, c, nc, t, nt, bt, nbt One-Time Filter: false -(3 rows) +(4 rows) -- Test handling of qual pushdown to appendrel members with non-Var outputs explain (verbose, costs off) @@ -4435,11 +4440,12 @@ select unique1 from tenk1, lateral f_immutable_int4(1) x where x = unique1; explain (costs off) select unique1 from tenk1, lateral f_immutable_int4(1) x where x in (select 17); - QUERY PLAN --------------------------- + QUERY PLAN +--------------------------- Result + Replaces: Scan on tenk1 One-Time Filter: false -(2 rows) +(3 rows) explain (costs off) select unique1, x from tenk1 join f_immutable_int4(1) x on unique1 = x; @@ -4485,11 +4491,12 @@ select unique1, x from tenk1 full join f_immutable_int4(1) x on unique1 = x; -- check that pullup of a const function allows further const-folding explain (costs off) select unique1 from tenk1, f_immutable_int4(1) x where x = 42; - QUERY PLAN --------------------------- + QUERY PLAN +--------------------------- Result + Replaces: Scan on tenk1 One-Time Filter: false -(2 rows) +(3 rows) -- test inlining of immutable functions with PlaceHolderVars explain (costs off) @@ -5345,8 +5352,9 @@ left join Join Filter: false -> Result -> Result + Replaces: Join on c, n One-Time Filter: false -(5 rows) +(6 rows) -- check handling of apparently-commutable outer joins with non-commutable -- joins between them @@ -5540,12 +5548,13 @@ select 1 from right join (select 1 as z) as ss2 on true) on false, lateral (select i4.f1, ss1.n from int8_tbl as i8 limit 1) as ss3; - QUERY PLAN --------------------------- + QUERY PLAN +----------------------------------------------- Result Output: 1 + Replaces: Join on i4, ss3, x1, x2, *RESULT* One-Time Filter: false -(3 rows) +(4 rows) select 1 from int4_tbl as i4 @@ -5574,11 +5583,12 @@ select 1 from t t1 on false where t3.a = coalesce(t5.a,1)) as s2 on true; - QUERY PLAN --------------------------- + QUERY PLAN +-------------------------------------------- Result + Replaces: Join on t1, s1, t2, t3, t4, t5 One-Time Filter: false -(2 rows) +(3 rows) rollback; -- @@ -5975,14 +5985,15 @@ from int4_tbl as t1 inner join int8_tbl as t7 on null) on t5.q1 = t7.q2) on false; - QUERY PLAN --------------------------------- + QUERY PLAN +-------------------------------------------------- Nested Loop Left Join Join Filter: false -> Seq Scan on int4_tbl t1 -> Result + Replaces: Join on t2, t3, t4, t5, t7, t6 One-Time Filter: false -(5 rows) +(6 rows) -- variant with Var rather than PHV coming from t6 explain (costs off) @@ -5997,14 +6008,15 @@ from int4_tbl as t1 inner join int8_tbl as t7 on null) on t5.q1 = t7.q2) on false; - QUERY PLAN --------------------------------- + QUERY PLAN +-------------------------------------------------- Nested Loop Left Join Join Filter: false -> Seq Scan on int4_tbl t1 -> Result + Replaces: Join on t2, t3, t4, t5, t7, t6 One-Time Filter: false -(5 rows) +(6 rows) -- per further discussion of bug #17781 explain (costs off) @@ -6054,15 +6066,16 @@ select * from int8_tbl t1 left join (int8_tbl t2 inner join int8_tbl t3 on false left join int8_tbl t4 on t2.q2 = t4.q2) on t1.q1 = t2.q1; - QUERY PLAN --------------------------------------- + QUERY PLAN +-------------------------------------------- Hash Left Join - Hash Cond: (t1.q1 = q1) + Hash Cond: (t1.q1 = t2.q1) -> Seq Scan on int8_tbl t1 -> Hash -> Result + Replaces: Join on t2, t3, t4 One-Time Filter: false -(6 rows) +(7 rows) -- deduce constant-false from an EquivalenceClass explain (costs off) @@ -6070,15 +6083,16 @@ select * from int8_tbl t1 left join (int8_tbl t2 inner join int8_tbl t3 on (t2.q1-t3.q2) = 0 and (t2.q1-t3.q2) = 1 left join int8_tbl t4 on t2.q2 = t4.q2) on t1.q1 = t2.q1; - QUERY PLAN --------------------------------------- + QUERY PLAN +-------------------------------------------- Hash Left Join - Hash Cond: (t1.q1 = q1) + Hash Cond: (t1.q1 = t2.q1) -> Seq Scan on int8_tbl t1 -> Hash -> Result + Replaces: Join on t2, t3, t4 One-Time Filter: false -(6 rows) +(7 rows) -- pseudoconstant based on an outer-level Param explain (costs off) @@ -6317,8 +6331,9 @@ select p.* from QUERY PLAN -------------------------- Result + Replaces: Scan on p One-Time Filter: false -(2 rows) +(3 rows) select p.* from (parent p left join child c on (p.k = c.k)) join parent x on p.k = x.k @@ -6334,8 +6349,9 @@ select p.* from QUERY PLAN -------------------------- Result + Replaces: Join on p, x One-Time Filter: false -(2 rows) +(3 rows) -- bug 5255: this is not optimizable by join removal begin; @@ -6400,15 +6416,16 @@ SELECT q2 FROM QUERY PLAN ------------------------------------------------------ Nested Loop Left Join - Output: q2 + Output: int8_tbl.q2 Join Filter: NULL::boolean Filter: (('constant'::text) >= ('constant'::text)) -> Seq Scan on public.int4_tbl Output: int4_tbl.f1 -> Result - Output: q2, 'constant'::text + Output: int8_tbl.q2, 'constant'::text + Replaces: Scan on int8_tbl One-Time Filter: false -(9 rows) +(10 rows) -- join removal bug #17786: check that OR conditions are cleaned up EXPLAIN (COSTS OFF) @@ -6427,8 +6444,9 @@ FROM int4_tbl Filter: ((tenk1.unique1 = (42)) OR (tenk1.unique2 = (42))) -> Seq Scan on tenk1 -> Result + Replaces: Scan on int8_tbl One-Time Filter: false -(9 rows) +(10 rows) rollback; -- another join removal bug: we must clean up correctly when removing a PHV @@ -6878,8 +6896,9 @@ where q1.x = q2.y; QUERY PLAN -------------------------- Result + Replaces: Scan on sj One-Time Filter: false -(2 rows) +(3 rows) -- We can't use a cross-EC generated self join qual because of current logic of -- the generate_join_implied_equalities routine. @@ -7703,11 +7722,12 @@ select 1 from emp1 full join on true where false) s on true where false; - QUERY PLAN --------------------------- + QUERY PLAN +---------------------------------- Result + Replaces: Join on emp1, t1, t3 One-Time Filter: false -(2 rows) +(3 rows) select 1 from emp1 full join (select * from emp1 t1 join @@ -7936,8 +7956,9 @@ where false; -------------------------- Result Output: 1 + Replaces: Scan on ss One-Time Filter: false -(3 rows) +(4 rows) -- -- Test LATERAL @@ -8866,31 +8887,33 @@ select * from int4_tbl t1, explain (verbose, costs off) select * from int8_tbl i8 left join lateral (select *, i8.q2 from int4_tbl where false) ss on true; - QUERY PLAN --------------------------------------- + QUERY PLAN +---------------------------------------------- Nested Loop Left Join - Output: i8.q1, i8.q2, f1, (i8.q2) + Output: i8.q1, i8.q2, int4_tbl.f1, (i8.q2) Join Filter: false -> Seq Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 -> Result - Output: f1, i8.q2 + Output: int4_tbl.f1, i8.q2 + Replaces: Scan on int4_tbl One-Time Filter: false -(8 rows) +(9 rows) explain (verbose, costs off) select * from int8_tbl i8 left join lateral (select *, i8.q2 from int4_tbl i1, int4_tbl i2 where false) ss on true; - QUERY PLAN ------------------------------------------ + QUERY PLAN +----------------------------------------------- Nested Loop Left Join - Output: i8.q1, i8.q2, f1, f1, (i8.q2) + Output: i8.q1, i8.q2, i1.f1, i2.f1, (i8.q2) -> Seq Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 -> Result - Output: f1, f1, i8.q2 + Output: i1.f1, i2.f1, i8.q2 + Replaces: Join on i1, i2 One-Time Filter: false -(7 rows) +(8 rows) -- check handling of nested appendrels inside LATERAL select * from diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index cf2219df754..44df626c40c 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -2426,8 +2426,9 @@ MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid Output: t.tid, t.ctid -> Result Output: t.tid, t.ctid + Replaces: Scan on t One-Time Filter: false -(12 rows) +(13 rows) MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid WHEN NOT MATCHED THEN INSERT VALUES (s.sid); diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out index 5f2c0cf5786..cb12bf53719 100644 --- a/src/test/regress/expected/partition_aggregate.out +++ b/src/test/regress/expected/partition_aggregate.out @@ -146,13 +146,14 @@ SELECT c, a, count(*) FROM pagg_tab GROUP BY a, c; -- Test when input relation for grouping is dummy EXPLAIN (COSTS OFF) SELECT c, sum(a) FROM pagg_tab WHERE 1 = 2 GROUP BY c; - QUERY PLAN --------------------------------- + QUERY PLAN +------------------------------------ HashAggregate Group Key: c -> Result + Replaces: Scan on pagg_tab One-Time Filter: false -(4 rows) +(5 rows) SELECT c, sum(a) FROM pagg_tab WHERE 1 = 2 GROUP BY c; c | sum @@ -161,12 +162,13 @@ SELECT c, sum(a) FROM pagg_tab WHERE 1 = 2 GROUP BY c; EXPLAIN (COSTS OFF) SELECT c, sum(a) FROM pagg_tab WHERE c = 'x' GROUP BY c; - QUERY PLAN --------------------------------- + QUERY PLAN +------------------------------------ GroupAggregate -> Result + Replaces: Scan on pagg_tab One-Time Filter: false -(3 rows) +(4 rows) SELECT c, sum(a) FROM pagg_tab WHERE c = 'x' GROUP BY c; c | sum @@ -804,15 +806,16 @@ SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOI -- Empty join relation because of empty outer side, no partitionwise agg plan EXPLAIN (COSTS OFF) SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x = 1 AND x = 2) a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x, a.y ORDER BY 1, 2; - QUERY PLAN --------------------------------------- + QUERY PLAN +---------------------------------------------- GroupAggregate Group Key: pagg_tab1.y -> Sort Sort Key: pagg_tab1.y -> Result + Replaces: Join on b, pagg_tab1 One-Time Filter: false -(6 rows) +(7 rows) SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x = 1 AND x = 2) a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x, a.y ORDER BY 1, 2; x | y | count diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index 24e06845f92..713828be335 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -1609,26 +1609,28 @@ SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, pl -- joins where one of the relations is proven empty EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a = 1 AND t1.a = 2; - QUERY PLAN --------------------------- + QUERY PLAN +---------------------------- Result + Replaces: Join on t1, t2 One-Time Filter: false -(2 rows) +(3 rows) EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 LEFT JOIN prt2 t2 ON t1.a = t2.b; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------ Result + Replaces: Join on t2, prt1 One-Time Filter: false -(2 rows) +(3 rows) EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b, prt1 t3 WHERE t2.b = t3.a; QUERY PLAN -------------------------------------------------- Hash Left Join - Hash Cond: (t2.b = a) + Hash Cond: (t2.b = prt1.a) -> Append -> Hash Join Hash Cond: (t3_1.a = t2_1.b) @@ -1647,17 +1649,18 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 -> Seq Scan on prt2_p3 t2_3 -> Hash -> Result + Replaces: Scan on prt1 One-Time Filter: false -(21 rows) +(22 rows) EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b; QUERY PLAN -------------------------------------------- Sort - Sort Key: a, t2.b + Sort Key: prt1.a, t2.b -> Hash Left Join - Hash Cond: (t2.b = a) + Hash Cond: (t2.b = prt1.a) -> Append -> Seq Scan on prt2_p1 t2_1 Filter: (a = 0) @@ -1667,8 +1670,9 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 Filter: (a = 0) -> Hash -> Result + Replaces: Scan on prt1 One-Time Filter: false -(14 rows) +(15 rows) -- -- tests for hash partitioned tables. @@ -2242,10 +2246,10 @@ SELECT COUNT(*) FROM prt1_l t1 LEFT JOIN LATERAL -- join with one side empty EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.b = t2.a AND t1.c = t2.c; - QUERY PLAN -------------------------------------------------------------------------- + QUERY PLAN +---------------------------------------------------------------------------------------------- Hash Left Join - Hash Cond: ((t2.b = a) AND (t2.a = b) AND ((t2.c)::text = (c)::text)) + Hash Cond: ((t2.b = prt1_l.a) AND (t2.a = prt1_l.b) AND ((t2.c)::text = (prt1_l.c)::text)) -> Append -> Seq Scan on prt2_l_p1 t2_1 -> Seq Scan on prt2_l_p2_p1 t2_2 @@ -2254,8 +2258,9 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2) -> Seq Scan on prt2_l_p3_p2 t2_5 -> Hash -> Result + Replaces: Scan on prt1_l One-Time Filter: false -(11 rows) +(12 rows) -- Test case to verify proper handling of subqueries in a partitioned delete. -- The weird-looking lateral join is just there to force creation of a diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index 68ecd951809..7499cdb2cdf 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -627,8 +627,9 @@ explain (costs off) select * from rlp3 where a = 20; /* empty */ QUERY PLAN -------------------------- Result + Replaces: Scan on rlp3 One-Time Filter: false -(2 rows) +(3 rows) -- redundant clauses are eliminated explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */ @@ -670,8 +671,9 @@ explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */ QUERY PLAN -------------------------- Result + Replaces: Scan on rlp One-Time Filter: false -(2 rows) +(3 rows) explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15); QUERY PLAN @@ -1254,25 +1256,28 @@ select * from boolpart where a is not unknown; -- check that all partitions are pruned when faced with conflicting clauses explain (costs off) select * from boolpart where a is not unknown and a is unknown; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------ Result + Replaces: Scan on boolpart One-Time Filter: false -(2 rows) +(3 rows) explain (costs off) select * from boolpart where a is false and a is unknown; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------ Result + Replaces: Scan on boolpart One-Time Filter: false -(2 rows) +(3 rows) explain (costs off) select * from boolpart where a is true and a is unknown; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------ Result + Replaces: Scan on boolpart One-Time Filter: false -(2 rows) +(3 rows) -- inverse boolean partitioning - a seemingly unlikely design, but we've got -- code for it, so we'd better test it. @@ -1568,11 +1573,12 @@ explain (costs off) select * from coercepart where a = any ('{ab,null}'); (2 rows) explain (costs off) select * from coercepart where a = any (null::text[]); - QUERY PLAN --------------------------- + QUERY PLAN +-------------------------------- Result + Replaces: Scan on coercepart One-Time Filter: false -(2 rows) +(3 rows) explain (costs off) select * from coercepart where a = all ('{ab}'); QUERY PLAN @@ -1582,25 +1588,28 @@ explain (costs off) select * from coercepart where a = all ('{ab}'); (2 rows) explain (costs off) select * from coercepart where a = all ('{ab,bc}'); - QUERY PLAN --------------------------- + QUERY PLAN +-------------------------------- Result + Replaces: Scan on coercepart One-Time Filter: false -(2 rows) +(3 rows) explain (costs off) select * from coercepart where a = all ('{ab,null}'); - QUERY PLAN --------------------------- + QUERY PLAN +-------------------------------- Result + Replaces: Scan on coercepart One-Time Filter: false -(2 rows) +(3 rows) explain (costs off) select * from coercepart where a = all (null::text[]); - QUERY PLAN --------------------------- + QUERY PLAN +-------------------------------- Result + Replaces: Scan on coercepart One-Time Filter: false -(2 rows) +(3 rows) drop table coercepart; CREATE TABLE part (a INT, b INT) PARTITION BY LIST (a); @@ -1772,8 +1781,9 @@ explain (costs off) select * from lp where a <> 'a' and a is null; QUERY PLAN -------------------------- Result + Replaces: Scan on lp One-Time Filter: false -(2 rows) +(3 rows) explain (costs off) select * from lp where (a <> 'a' and a <> 'd') or a is null; QUERY PLAN @@ -1866,22 +1876,24 @@ create table lparted_by_int2 (a smallint) partition by list (a); create table lparted_by_int2_1 partition of lparted_by_int2 for values in (1); create table lparted_by_int2_16384 partition of lparted_by_int2 for values in (16384); explain (costs off) select * from lparted_by_int2 where a = 100_000_000_000_000; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------------- Result + Replaces: Scan on lparted_by_int2 One-Time Filter: false -(2 rows) +(3 rows) create table rparted_by_int2 (a smallint) partition by range (a); create table rparted_by_int2_1 partition of rparted_by_int2 for values from (1) to (10); create table rparted_by_int2_16384 partition of rparted_by_int2 for values from (10) to (16384); -- all partitions pruned explain (costs off) select * from rparted_by_int2 where a > 100_000_000_000_000; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------------- Result + Replaces: Scan on rparted_by_int2 One-Time Filter: false -(2 rows) +(3 rows) create table rparted_by_int2_maxvalue partition of rparted_by_int2 for values from (16384) to (maxvalue); -- all partitions but rparted_by_int2_maxvalue pruned @@ -2131,8 +2143,9 @@ explain (costs off) select * from hp where a = 1 and b = 'abcde' and QUERY PLAN -------------------------- Result + Replaces: Scan on hp One-Time Filter: false -(2 rows) +(3 rows) -- -- Test runtime partition pruning @@ -3390,11 +3403,12 @@ select * from stable_qual_pruning where a < '2000-02-01'::timestamptz; explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning where a = any(array['2010-02-01', '2020-01-01']::timestamp[]); - QUERY PLAN ------------------------------------ + QUERY PLAN +----------------------------------------- Result (actual rows=0.00 loops=1) + Replaces: Scan on stable_qual_pruning One-Time Filter: false -(2 rows) +(3 rows) explain (analyze, costs off, summary off, timing off, buffers off) select * from stable_qual_pruning @@ -3642,6 +3656,7 @@ explain (analyze, costs off, summary off, timing off, buffers off) select * from Sort Key: ma_test.b InitPlan 2 -> Result (actual rows=1.00 loops=1) + Replaces: MinMaxAggregate InitPlan 1 -> Limit (actual rows=1.00 loops=1) -> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=1.00 loops=1) @@ -3656,7 +3671,7 @@ explain (analyze, costs off, summary off, timing off, buffers off) select * from -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual rows=10.00 loops=1) Filter: (a >= (InitPlan 2).col1) Index Searches: 1 -(18 rows) +(19 rows) reset enable_seqscan; reset enable_sort; @@ -3678,11 +3693,12 @@ explain (costs off) select * from pp_arrpart where a = '{1}'; (2 rows) explain (costs off) select * from pp_arrpart where a = '{1, 2}'; - QUERY PLAN --------------------------- + QUERY PLAN +-------------------------------- Result + Replaces: Scan on pp_arrpart One-Time Filter: false -(2 rows) +(3 rows) explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}'); QUERY PLAN @@ -3764,11 +3780,12 @@ explain (costs off) select * from pp_enumpart where a = 'blue'; (2 rows) explain (costs off) select * from pp_enumpart where a = 'black'; - QUERY PLAN --------------------------- + QUERY PLAN +--------------------------------- Result + Replaces: Scan on pp_enumpart One-Time Filter: false -(2 rows) +(3 rows) drop table pp_enumpart; drop type pp_colors; @@ -3785,11 +3802,12 @@ explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype; (2 rows) explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype; - QUERY PLAN --------------------------- + QUERY PLAN +-------------------------------- Result + Replaces: Scan on pp_recpart One-Time Filter: false -(2 rows) +(3 rows) drop table pp_recpart; drop type pp_rectype; @@ -3805,11 +3823,12 @@ explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range; (2 rows) explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------------- Result + Replaces: Scan on pp_intrangepart One-Time Filter: false -(2 rows) +(3 rows) drop table pp_intrangepart; -- @@ -4125,19 +4144,21 @@ explain (costs off) update listp1 set a = 1 where a = 2; -- constraint exclusion enabled set constraint_exclusion to 'on'; explain (costs off) select * from listp1 where a = 2; - QUERY PLAN --------------------------- + QUERY PLAN +---------------------------- Result + Replaces: Scan on listp1 One-Time Filter: false -(2 rows) +(3 rows) explain (costs off) update listp1 set a = 1 where a = 2; - QUERY PLAN --------------------------------- + QUERY PLAN +---------------------------------- Update on listp1 -> Result + Replaces: Scan on listp1 One-Time Filter: false -(3 rows) +(4 rows) reset constraint_exclusion; reset enable_partition_pruning; @@ -4524,18 +4545,20 @@ create table hp_contradict_test (a int, b int) partition by hash (a part_test_in create table hp_contradict_test_p1 partition of hp_contradict_test for values with (modulus 2, remainder 0); create table hp_contradict_test_p2 partition of hp_contradict_test for values with (modulus 2, remainder 1); explain (costs off) select * from hp_contradict_test where a is null and a === 1 and b === 1; - QUERY PLAN --------------------------- + QUERY PLAN +---------------------------------------- Result + Replaces: Scan on hp_contradict_test One-Time Filter: false -(2 rows) +(3 rows) explain (costs off) select * from hp_contradict_test where a === 1 and b === 1 and a is null; - QUERY PLAN --------------------------- + QUERY PLAN +---------------------------------------- Result + Replaces: Scan on hp_contradict_test One-Time Filter: false -(2 rows) +(3 rows) drop table hp_contradict_test; drop operator class part_test_int4_ops2 using hash; diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out index 1aff0b59ff8..304b6868b90 100644 --- a/src/test/regress/expected/predicate.out +++ b/src/test/regress/expected/predicate.out @@ -36,8 +36,9 @@ SELECT * FROM pred_tab t WHERE t.a IS NULL; QUERY PLAN -------------------------- Result + Replaces: Scan on t One-Time Filter: false -(2 rows) +(3 rows) -- Ensure the IS_NULL qual is not reduced to constant-FALSE on nullable -- columns @@ -77,8 +78,9 @@ SELECT * FROM pred_tab t WHERE t.a IS NULL OR t.c IS NULL; QUERY PLAN -------------------------- Result + Replaces: Scan on t One-Time Filter: false -(2 rows) +(3 rows) -- Ensure the OR clause is not reduced to constant-FALSE when not all branches -- are provably false @@ -139,8 +141,9 @@ SELECT * FROM pred_tab t1 Join Filter: false -> Seq Scan on pred_tab t1 -> Result + Replaces: Scan on t2 One-Time Filter: false -(5 rows) +(6 rows) -- Ensure the IS_NULL qual is not reduced to constant-FALSE when the column is -- nullable by an outer join @@ -209,8 +212,9 @@ SELECT * FROM pred_tab t1 Join Filter: false -> Seq Scan on pred_tab t1 -> Result + Replaces: Scan on t2 One-Time Filter: false -(5 rows) +(6 rows) -- Ensure the OR clause is not reduced to constant-FALSE when a column is -- made nullable from an outer join @@ -267,17 +271,18 @@ SELECT * FROM pred_tab t1 LEFT JOIN pred_tab t2 ON EXISTS (SELECT 1 FROM pred_tab t3, pred_tab t4, pred_tab t5, pred_tab t6 WHERE t1.a = t3.a AND t6.a IS NULL); - QUERY PLAN -------------------------------------- + QUERY PLAN +-------------------------------------------- Nested Loop Left Join Join Filter: (InitPlan 1).col1 InitPlan 1 -> Result + Replaces: Join on t3, t4, t5, t6 One-Time Filter: false -> Seq Scan on pred_tab t1 -> Materialize -> Seq Scan on pred_tab t2 -(8 rows) +(9 rows) DROP TABLE pred_tab; -- Validate we handle IS NULL and IS NOT NULL quals correctly with inheritance @@ -418,20 +423,22 @@ SET constraint_exclusion TO ON; -- Ensure that we get a dummy plan EXPLAIN (COSTS OFF) SELECT * FROM pred_tab1, pred_tab2 WHERE pred_tab2.a IS NULL; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------------------ Result + Replaces: Join on pred_tab1, pred_tab2 One-Time Filter: false -(2 rows) +(3 rows) -- Ensure that we get a dummy plan EXPLAIN (COSTS OFF) SELECT * FROM pred_tab2, pred_tab1 WHERE pred_tab1.a IS NULL OR pred_tab1.b < 2; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------------------ Result + Replaces: Join on pred_tab2, pred_tab1 One-Time Filter: false -(2 rows) +(3 rows) RESET constraint_exclusion; DROP TABLE pred_tab1; diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 8c879509313..7153ebba521 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -3606,8 +3606,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM t1; QUERY PLAN -------------------------- Result + Replaces: Scan on t1 One-Time Filter: false -(2 rows) +(3 rows) SET SESSION AUTHORIZATION regress_rls_bob; SELECT * FROM t1; @@ -3619,8 +3620,9 @@ EXPLAIN (COSTS OFF) SELECT * FROM t1; QUERY PLAN -------------------------- Result + Replaces: Scan on t1 One-Time Filter: false -(2 rows) +(3 rows) -- -- COPY TO/FROM @@ -4524,11 +4526,12 @@ SELECT * FROM rls_tbl WHERE a <<< 1000; (0 rows) EXPLAIN (COSTS OFF) SELECT * FROM rls_tbl WHERE a <<< 1000 or a <<< 900; - QUERY PLAN --------------------------- + QUERY PLAN +----------------------------- Result + Replaces: Scan on rls_tbl One-Time Filter: false -(2 rows) +(3 rows) RESET SESSION AUTHORIZATION; CREATE TABLE rls_child_tbl () INHERITS (rls_tbl); diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out index d84122881af..dd52d96d50f 100644 --- a/src/test/regress/expected/rowtypes.out +++ b/src/test/regress/expected/rowtypes.out @@ -1325,8 +1325,9 @@ where false; -------------------------- Result Output: (a).f1, (a).f2 + Replaces: Scan on ss One-Time Filter: false -(3 rows) +(4 rows) explain (verbose, costs off) with cte(c) as materialized (select row(1, 2)), @@ -1350,11 +1351,12 @@ where false; ----------------------------------- Result Output: (cte.c).f1 + Replaces: Scan on cte One-Time Filter: false CTE cte -> Result Output: '(1,2)'::record -(6 rows) +(7 rows) -- -- Tests for component access / FieldSelect diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out index bab0cc93ff5..2cdb78d1e9a 100644 --- a/src/test/regress/expected/select.out +++ b/src/test/regress/expected/select.out @@ -962,10 +962,11 @@ create table list_parted_tbl (a int,b int) partition by list (a); create table list_parted_tbl1 partition of list_parted_tbl for values in (1) partition by list(b); explain (costs off) select * from list_parted_tbl; - QUERY PLAN --------------------------- + QUERY PLAN +------------------------------------- Result + Replaces: Scan on list_parted_tbl One-Time Filter: false -(2 rows) +(3 rows) drop table list_parted_tbl; diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 76ffb2b9027..47b2af7b2e1 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -1998,17 +1998,18 @@ create temp table json_tab (a int); insert into json_tab values (1); explain (verbose, costs off) select * from json_tab t1 left join (select json_array(1, a) from json_tab t2) s on false; - QUERY PLAN ---------------------------------------------------- + QUERY PLAN +------------------------------------------------------ Nested Loop Left Join - Output: t1.a, (JSON_ARRAY(1, a RETURNING json)) + Output: t1.a, (JSON_ARRAY(1, t2.a RETURNING json)) Join Filter: false -> Seq Scan on pg_temp.json_tab t1 Output: t1.a -> Result - Output: JSON_ARRAY(1, a RETURNING json) + Output: JSON_ARRAY(1, t2.a RETURNING json) + Replaces: Scan on t2 One-Time Filter: false -(8 rows) +(9 rows) select * from json_tab t1 left join (select json_array(1, a) from json_tab t2) s on false; a | json_array @@ -2899,12 +2900,13 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd); -> Subquery Scan on unnamed_subquery Filter: (b.hundred = unnamed_subquery.min) -> Result + Replaces: MinMaxAggregate InitPlan 1 -> Limit -> Index Scan using tenk2_hundred on tenk2 c Index Cond: (hundred IS NOT NULL) Filter: (odd = b.odd) -(16 rows) +(17 rows) -- -- Test VALUES to ARRAY (VtA) transformation @@ -3067,8 +3069,9 @@ EXPLAIN (COSTS OFF) EXECUTE test(NULL, 3.14, NULL); QUERY PLAN -------------------------- Result + Replaces: Scan on onek One-Time Filter: false -(2 rows) +(3 rows) EXPLAIN (COSTS OFF) EXECUTE test(NULL, 3.14, '-1.5'); QUERY PLAN diff --git a/src/test/regress/expected/tsrf.out b/src/test/regress/expected/tsrf.out index d47b5f6ec57..c4f7b187f5b 100644 --- a/src/test/regress/expected/tsrf.out +++ b/src/test/regress/expected/tsrf.out @@ -91,8 +91,9 @@ SELECT unnest(ARRAY[1, 2]) FROM few WHERE false; ProjectSet Output: unnest('{1,2}'::integer[]) -> Result + Replaces: Scan on few One-Time Filter: false -(4 rows) +(5 rows) SELECT unnest(ARRAY[1, 2]) FROM few WHERE false; unnest @@ -107,8 +108,9 @@ SELECT * FROM few f1, ------------------------------------------------ Result Output: f1.id, f1.dataa, f1.datab, ss.unnest + Replaces: Join on f1, ss One-Time Filter: false -(3 rows) +(4 rows) SELECT * FROM few f1, (SELECT unnest(ARRAY[1,2]) FROM few f2 WHERE false OFFSET 0) ss; diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql index 7842d25ded3..ebdab42604b 100644 --- a/src/test/regress/sql/explain.sql +++ b/src/test/regress/sql/explain.sql @@ -61,6 +61,7 @@ set track_io_timing = off; -- Simple cases +explain (costs off) select 1 as a, 2 as b having false; select explain_filter('explain select * from int8_tbl i8'); select explain_filter('explain (analyze, buffers off) select * from int8_tbl i8'); select explain_filter('explain (analyze, buffers off, verbose) select * from int8_tbl i8'); |