diff options
Diffstat (limited to '')
35 files changed, 913 insertions, 58 deletions
diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out index 46c827f..f3eabc0 100644 --- a/src/test/regress/expected/create_procedure.out +++ b/src/test/regress/expected/create_procedure.out @@ -148,7 +148,19 @@ CALL ptest4a(a, b); -- error, not supported $$; ERROR: calling procedures with output arguments is not supported in SQL functions CONTEXT: SQL function "ptest4b" -DROP PROCEDURE ptest4a; +-- we used to get confused by a single output argument that is composite +CREATE PROCEDURE ptest4c(INOUT comp int8_tbl) +LANGUAGE SQL +AS $$ +SELECT ROW(1, 2); +$$; +CALL ptest4c(NULL); + comp +------- + (1,2) +(1 row) + +DROP PROCEDURE ptest4a, ptest4c; -- named and default parameters CREATE OR REPLACE PROCEDURE ptest5(a int, b text, c int default 100) LANGUAGE SQL diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index 3350535..25e7163 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -894,24 +894,29 @@ ERROR: column "no_column" of relation "ft1" does not exist ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column; NOTICE: column "no_column" of relation "ft1" does not exist, skipping ALTER FOREIGN TABLE ft1 DROP COLUMN c9; +ALTER FOREIGN TABLE ft1 ADD COLUMN c11 serial; ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema; ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR ERROR: relation "ft1" does not exist +ALTER SEQUENCE foreign_schema.ft1_c11_seq SET SCHEMA public; -- ERROR +ERROR: cannot move an owned sequence into another schema +DETAIL: Sequence "ft1_c11_seq" is linked to table "ft1". ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1; ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; \d foreign_schema.foreign_table_1 - Foreign table "foreign_schema.foreign_table_1" - Column | Type | Collation | Nullable | Default | FDW options -------------------+---------+-----------+----------+---------+-------------------------------- - foreign_column_1 | integer | | not null | | ("param 1" 'val1') - c2 | text | | | | (param2 'val2', param3 'val3') - c3 | date | | | | - c4 | integer | | | 0 | - c5 | integer | | | | - c6 | integer | | not null | | - c7 | integer | | | | (p1 'v1', p2 'v2') - c8 | text | | | | (p2 'V2') - c10 | integer | | | | (p1 'v1') + Foreign table "foreign_schema.foreign_table_1" + Column | Type | Collation | Nullable | Default | FDW options +------------------+---------+-----------+----------+-------------------------------------------------+-------------------------------- + foreign_column_1 | integer | | not null | | ("param 1" 'val1') + c2 | text | | | | (param2 'val2', param3 'val3') + c3 | date | | | | + c4 | integer | | | 0 | + c5 | integer | | | | + c6 | integer | | not null | | + c7 | integer | | | | (p1 'v1', p2 'v2') + c8 | text | | | | (p2 'V2') + c10 | integer | | | | (p1 'v1') + c11 | integer | | not null | nextval('foreign_schema.ft1_c11_seq'::regclass) | Check constraints: "ft1_c2_check" CHECK (c2 <> ''::text) "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out index 2dc9b00..7353529 100644 --- a/src/test/regress/expected/horology.out +++ b/src/test/regress/expected/horology.out @@ -375,6 +375,8 @@ SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' SELECT timestamp without time zone '2000-01-01' - interval '2483590 days' AS "out of range"; ERROR: timestamp out of range +SELECT timestamp without time zone '294276-12-31 23:59:59' + interval '9223372036854775807 microseconds' AS "out of range"; +ERROR: timestamp out of range SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days"; 106751991 Days ------------------ @@ -637,6 +639,8 @@ SELECT timestamp with time zone '1999-12-01' + interval '1 month - 1 second' AS SELECT timestamp with time zone '2000-01-01' - interval '2483590 days' AS "out of range"; ERROR: timestamp out of range +SELECT timestamp with time zone '294276-12-31 23:59:59 UTC' + interval '9223372036854775807 microseconds' AS "out of range"; +ERROR: timestamp out of range SELECT (timestamp with time zone 'today' = (timestamp with time zone 'yesterday' + interval '1 day')) as "True"; True ------ diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out index 5f03d8e..cc77723 100644 --- a/src/test/regress/expected/identity.out +++ b/src/test/regress/expected/identity.out @@ -365,6 +365,78 @@ SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regcl ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error ERROR: identity column type must be smallint, integer, or bigint +-- check that unlogged propagates to sequence +CREATE UNLOGGED TABLE itest17 (a int NOT NULL, b text); +ALTER TABLE itest17 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; +ALTER TABLE itest17 ADD COLUMN c int GENERATED ALWAYS AS IDENTITY; +\d itest17 + Unlogged table "public.itest17" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------ + a | integer | | not null | generated always as identity + b | text | | | + c | integer | | not null | generated always as identity + +\d itest17_a_seq + Unlogged sequence "public.itest17_a_seq" + Type | Start | Minimum | Maximum | Increment | Cycles? | Cache +---------+-------+---------+------------+-----------+---------+------- + integer | 1 | 1 | 2147483647 | 1 | no | 1 +Sequence for identity column: public.itest17.a + +\d itest17_c_seq + Unlogged sequence "public.itest17_c_seq" + Type | Start | Minimum | Maximum | Increment | Cycles? | Cache +---------+-------+---------+------------+-----------+---------+------- + integer | 1 | 1 | 2147483647 | 1 | no | 1 +Sequence for identity column: public.itest17.c + +CREATE TABLE itest18 (a int NOT NULL, b text); +ALTER TABLE itest18 SET UNLOGGED, ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; +\d itest18 + Unlogged table "public.itest18" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------ + a | integer | | not null | generated always as identity + b | text | | | + +\d itest18_a_seq + Unlogged sequence "public.itest18_a_seq" + Type | Start | Minimum | Maximum | Increment | Cycles? | Cache +---------+-------+---------+------------+-----------+---------+------- + integer | 1 | 1 | 2147483647 | 1 | no | 1 +Sequence for identity column: public.itest18.a + +ALTER TABLE itest18 SET LOGGED; +\d itest18 + Table "public.itest18" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------ + a | integer | | not null | generated always as identity + b | text | | | + +\d itest18_a_seq + Sequence "public.itest18_a_seq" + Type | Start | Minimum | Maximum | Increment | Cycles? | Cache +---------+-------+---------+------------+-----------+---------+------- + integer | 1 | 1 | 2147483647 | 1 | no | 1 +Sequence for identity column: public.itest18.a + +ALTER TABLE itest18 SET UNLOGGED; +\d itest18 + Unlogged table "public.itest18" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------ + a | integer | | not null | generated always as identity + b | text | | | + +\d itest18_a_seq + Unlogged sequence "public.itest18_a_seq" + Type | Start | Minimum | Maximum | Increment | Cycles? | Cache +---------+-------+---------+------------+-----------+---------+------- + integer | 1 | 1 | 2147483647 | 1 | no | 1 +Sequence for identity column: public.itest18.a + -- kinda silly to change property in the same command, but it should work ALTER TABLE itest3 ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY, diff --git a/src/test/regress/expected/index_including.out b/src/test/regress/expected/index_including.out index 8651068..ea8b245 100644 --- a/src/test/regress/expected/index_including.out +++ b/src/test/regress/expected/index_including.out @@ -398,3 +398,28 @@ Indexes: "tbl_c1_c2_c3_c4_key" UNIQUE CONSTRAINT, btree (c1, c2) INCLUDE (c3, c4) DROP TABLE tbl; +/* + * 10. Test coverage for names stored as cstrings in indexes + */ +CREATE TABLE nametbl (c1 int, c2 name, c3 float); +CREATE INDEX nametbl_c1_c2_idx ON nametbl (c2, c1) INCLUDE (c3); +INSERT INTO nametbl VALUES(1, 'two', 3.0); +VACUUM nametbl; +SET enable_seqscan = 0; +-- Ensure we get an index only scan plan +EXPLAIN (COSTS OFF) SELECT c2, c1, c3 FROM nametbl WHERE c2 = 'two' AND c1 = 1; + QUERY PLAN +---------------------------------------------------- + Index Only Scan using nametbl_c1_c2_idx on nametbl + Index Cond: ((c2 = 'two'::name) AND (c1 = 1)) +(2 rows) + +-- Validate the results look sane +SELECT c2, c1, c3 FROM nametbl WHERE c2 = 'two' AND c1 = 1; + c2 | c1 | c3 +-----+----+---- + two | 1 | 3 +(1 row) + +RESET enable_seqscan; +DROP TABLE nametbl; diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out index dd4354f..cf4b522 100644 --- a/src/test/regress/expected/insert.out +++ b/src/test/regress/expected/insert.out @@ -180,7 +180,121 @@ Rules: drop table inserttest2; drop table inserttest; -drop type insert_test_type; +-- Make the same tests with domains over the array and composite fields +create domain insert_pos_ints as int[] check (value[1] > 0); +create domain insert_test_domain as insert_test_type + check ((value).if2[1] is not null); +create table inserttesta (f1 int, f2 insert_pos_ints); +create table inserttestb (f3 insert_test_domain, f4 insert_test_domain[]); +insert into inserttesta (f2[1], f2[2]) values (1,2); +insert into inserttesta (f2[1], f2[2]) values (3,4), (5,6); +insert into inserttesta (f2[1], f2[2]) select 7,8; +insert into inserttesta (f2[1], f2[2]) values (1,default); -- not supported +ERROR: cannot set an array element to DEFAULT +LINE 1: insert into inserttesta (f2[1], f2[2]) values (1,default); + ^ +insert into inserttesta (f2[1], f2[2]) values (0,2); +ERROR: value for domain insert_pos_ints violates check constraint "insert_pos_ints_check" +insert into inserttesta (f2[1], f2[2]) values (3,4), (0,6); +ERROR: value for domain insert_pos_ints violates check constraint "insert_pos_ints_check" +insert into inserttesta (f2[1], f2[2]) select 0,8; +ERROR: value for domain insert_pos_ints violates check constraint "insert_pos_ints_check" +insert into inserttestb (f3.if1, f3.if2) values (1,array['foo']); +insert into inserttestb (f3.if1, f3.if2) values (1,'{foo}'), (2,'{bar}'); +insert into inserttestb (f3.if1, f3.if2) select 3, '{baz,quux}'; +insert into inserttestb (f3.if1, f3.if2) values (1,default); -- not supported +ERROR: cannot set a subfield to DEFAULT +LINE 1: insert into inserttestb (f3.if1, f3.if2) values (1,default); + ^ +insert into inserttestb (f3.if1, f3.if2) values (1,array[null]); +ERROR: value for domain insert_test_domain violates check constraint "insert_test_domain_check" +insert into inserttestb (f3.if1, f3.if2) values (1,'{null}'), (2,'{bar}'); +ERROR: value for domain insert_test_domain violates check constraint "insert_test_domain_check" +insert into inserttestb (f3.if1, f3.if2) select 3, '{null,quux}'; +ERROR: value for domain insert_test_domain violates check constraint "insert_test_domain_check" +insert into inserttestb (f3.if2[1], f3.if2[2]) values ('foo', 'bar'); +insert into inserttestb (f3.if2[1], f3.if2[2]) values ('foo', 'bar'), ('baz', 'quux'); +insert into inserttestb (f3.if2[1], f3.if2[2]) select 'bear', 'beer'; +insert into inserttestb (f3, f4[1].if2[1], f4[1].if2[2]) values (row(1,'{x}'), 'foo', 'bar'); +insert into inserttestb (f3, f4[1].if2[1], f4[1].if2[2]) values (row(1,'{x}'), 'foo', 'bar'), (row(2,'{y}'), 'baz', 'quux'); +insert into inserttestb (f3, f4[1].if2[1], f4[1].if2[2]) select row(1,'{x}')::insert_test_domain, 'bear', 'beer'; +select * from inserttesta; + f1 | f2 +----+------- + | {1,2} + | {3,4} + | {5,6} + | {7,8} +(4 rows) + +select * from inserttestb; + f3 | f4 +------------------+------------------------ + (1,{foo}) | + (1,{foo}) | + (2,{bar}) | + (3,"{baz,quux}") | + (,"{foo,bar}") | + (,"{foo,bar}") | + (,"{baz,quux}") | + (,"{bear,beer}") | + (1,{x}) | {"(,\"{foo,bar}\")"} + (1,{x}) | {"(,\"{foo,bar}\")"} + (2,{y}) | {"(,\"{baz,quux}\")"} + (1,{x}) | {"(,\"{bear,beer}\")"} +(12 rows) + +-- also check reverse-listing +create table inserttest2 (f1 bigint, f2 text); +create rule irule1 as on insert to inserttest2 do also + insert into inserttestb (f3.if2[1], f3.if2[2]) + values (new.f1,new.f2); +create rule irule2 as on insert to inserttest2 do also + insert into inserttestb (f4[1].if1, f4[1].if2[2]) + values (1,'fool'),(new.f1,new.f2); +create rule irule3 as on insert to inserttest2 do also + insert into inserttestb (f4[1].if1, f4[1].if2[2]) + select new.f1, new.f2; +\d+ inserttest2 + Table "public.inserttest2" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+--------+-----------+----------+---------+----------+--------------+------------- + f1 | bigint | | | | plain | | + f2 | text | | | | extended | | +Rules: + irule1 AS + ON INSERT TO inserttest2 DO INSERT INTO inserttestb (f3.if2[1], f3.if2[2]) + VALUES (new.f1, new.f2) + irule2 AS + ON INSERT TO inserttest2 DO INSERT INTO inserttestb (f4[1].if1, f4[1].if2[2]) VALUES (1,'fool'::text), (new.f1,new.f2) + irule3 AS + ON INSERT TO inserttest2 DO INSERT INTO inserttestb (f4[1].if1, f4[1].if2[2]) SELECT new.f1, + new.f2 + +drop table inserttest2; +drop table inserttesta; +drop table inserttestb; +drop domain insert_pos_ints; +drop domain insert_test_domain; +-- Verify that multiple inserts to subfields of a domain-over-container +-- check the domain constraints only on the finished value +create domain insert_nnarray as int[] + check (value[1] is not null and value[2] is not null); +create domain insert_test_domain as insert_test_type + check ((value).if1 is not null and (value).if2 is not null); +create table inserttesta (f1 insert_nnarray); +insert into inserttesta (f1[1]) values (1); -- fail +ERROR: value for domain insert_nnarray violates check constraint "insert_nnarray_check" +insert into inserttesta (f1[1], f1[2]) values (1, 2); +create table inserttestb (f1 insert_test_domain); +insert into inserttestb (f1.if1) values (1); -- fail +ERROR: value for domain insert_test_domain violates check constraint "insert_test_domain_check" +insert into inserttestb (f1.if1, f1.if2) values (1, '{foo}'); +drop table inserttesta; +drop table inserttestb; +drop domain insert_nnarray; +drop type insert_test_type cascade; +NOTICE: drop cascades to type insert_test_domain -- direct partition inserts should check partition bound constraint create table range_parted ( a text, diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 867c6d2..b356153 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -5949,6 +5949,37 @@ select * from 3 | 3 (6 rows) +-- check for generation of join EC conditions at wrong level (bug #18429) +explain (costs off) +select * from ( + select arrayd.ad, coalesce(c.hundred, 0) as h + from unnest(array[1]) as arrayd(ad) + left join lateral ( + select hundred from tenk1 where unique2 = arrayd.ad + ) c on true +) c2 +where c2.h * c2.ad = c2.h * (c2.ad + 1); + QUERY PLAN +------------------------------------------------------------------------------------------------------- + Nested Loop Left Join + Filter: ((COALESCE(tenk1.hundred, 0) * arrayd.ad) = (COALESCE(tenk1.hundred, 0) * (arrayd.ad + 1))) + -> Function Scan on unnest arrayd + -> Index Scan using tenk1_unique2 on tenk1 + Index Cond: (unique2 = arrayd.ad) +(5 rows) + +select * from ( + select arrayd.ad, coalesce(c.hundred, 0) as h + from unnest(array[1]) as arrayd(ad) + left join lateral ( + select hundred from tenk1 where unique2 = arrayd.ad + ) c on true +) c2 +where c2.h * c2.ad = c2.h * (c2.ad + 1); + ad | h +----+--- +(0 rows) + -- check the number of columns specified SELECT * FROM (int8_tbl i cross join int4_tbl j) ss(a,b,c,d); ERROR: join expression "ss" has 3 columns available but 4 columns specified diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index 60cbdee..bde091a 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -92,10 +92,39 @@ WHERE t1.unique1 < 1000; 1000 | 9.5000000000000000 (1 row) +SET enable_mergejoin TO off; +-- Test for varlena datatype with expr evaluation +CREATE TABLE expr_key (x numeric, t text); +INSERT INTO expr_key (x, t) +SELECT d1::numeric, d1::text FROM ( + SELECT round((d / pi())::numeric, 7) AS d1 FROM generate_series(1, 20) AS d +) t; +-- duplicate rows so we get some cache hits +INSERT INTO expr_key SELECT * FROM expr_key; +CREATE INDEX expr_key_idx_x_t ON expr_key (x, t); +VACUUM ANALYZE expr_key; +-- Ensure we get we get a cache miss and hit for each of the 20 distinct values +SELECT explain_memoize(' +SELECT * FROM expr_key t1 INNER JOIN expr_key t2 +ON t1.x = t2.t::numeric AND t1.t::numeric = t2.x;', false); + explain_memoize +------------------------------------------------------------------------------------------- + Nested Loop (actual rows=80 loops=N) + -> Seq Scan on expr_key t1 (actual rows=40 loops=N) + -> Memoize (actual rows=2 loops=N) + Cache Key: t1.x, (t1.t)::numeric + Cache Mode: logical + Hits: 20 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB + -> Index Only Scan using expr_key_idx_x_t on expr_key t2 (actual rows=2 loops=N) + Index Cond: (x = (t1.t)::numeric) + Filter: (t1.x = (t)::numeric) + Heap Fetches: N +(10 rows) + +DROP TABLE expr_key; -- Reduce work_mem and hash_mem_multiplier so that we see some cache evictions SET work_mem TO '64kB'; SET hash_mem_multiplier TO 1.0; -SET enable_mergejoin TO off; -- Ensure we get some evictions. We're unable to validate the hits and misses -- here as the number of entries that fit in the cache at once will vary -- between different machines. diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index edc0043..63a829e 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -3,6 +3,7 @@ -- CREATE USER regress_merge_privs; CREATE USER regress_merge_no_privs; +CREATE USER regress_merge_none; DROP TABLE IF EXISTS target; NOTICE: table "target" does not exist, skipping DROP TABLE IF EXISTS source; @@ -159,6 +160,14 @@ ERROR: cannot execute MERGE on relation "mv" DETAIL: This operation is not supported for materialized views. DROP MATERIALIZED VIEW mv; -- permissions +SET SESSION AUTHORIZATION regress_merge_none; +MERGE INTO target +USING (SELECT 1) AS s +ON true +WHEN MATCHED THEN + DO NOTHING; +ERROR: permission denied for table target +SET SESSION AUTHORIZATION regress_merge_privs; MERGE INTO target USING source2 ON target.tid = source2.sid @@ -1474,6 +1483,56 @@ WHEN MATCHED AND t.a < 10 THEN DROP TABLE ex_msource, ex_mtarget; DROP FUNCTION explain_merge(text); +-- EXPLAIN SubPlans and InitPlans +CREATE TABLE src (a int, b int, c int, d int); +CREATE TABLE tgt (a int, b int, c int, d int); +CREATE TABLE ref (ab int, cd int); +EXPLAIN (verbose, costs off) +MERGE INTO tgt t +USING (SELECT *, (SELECT count(*) FROM ref r + WHERE r.ab = s.a + s.b + AND r.cd = s.c - s.d) cnt + FROM src s) s +ON t.a = s.a AND t.b < s.cnt +WHEN MATCHED AND t.c > s.cnt THEN + UPDATE SET (b, c) = (SELECT s.b, s.cnt); + QUERY PLAN +------------------------------------------------------------------------------------- + Merge on public.tgt t + -> Hash Join + Output: t.ctid, s.a, s.b, s.c, s.d, s.ctid + Hash Cond: (t.a = s.a) + Join Filter: (t.b < (SubPlan 1)) + -> Seq Scan on public.tgt t + Output: t.ctid, t.a, t.b + -> Hash + Output: s.a, s.b, s.c, s.d, s.ctid + -> Seq Scan on public.src s + Output: s.a, s.b, s.c, s.d, s.ctid + SubPlan 1 + -> Aggregate + Output: count(*) + -> Seq Scan on public.ref r + Output: r.ab, r.cd + Filter: ((r.ab = (s.a + s.b)) AND (r.cd = (s.c - s.d))) + SubPlan 4 + -> Aggregate + Output: count(*) + -> Seq Scan on public.ref r_2 + Output: r_2.ab, r_2.cd + Filter: ((r_2.ab = (s.a + s.b)) AND (r_2.cd = (s.c - s.d))) + SubPlan 3 (returns $9,$10) + -> Result + Output: s.b, $8 + InitPlan 2 (returns $8) + -> Aggregate + Output: count(*) + -> Seq Scan on public.ref r_1 + Output: r_1.ab, r_1.cd + Filter: ((r_1.ab = (s.a + s.b)) AND (r_1.cd = (s.c - s.d))) +(32 rows) + +DROP TABLE src, tgt, ref; -- Subqueries BEGIN; MERGE INTO sq_target t @@ -2244,3 +2303,4 @@ DROP TABLE source, source2; DROP FUNCTION merge_trigfunc(); DROP USER regress_merge_privs; DROP USER regress_merge_no_privs; +DROP USER regress_merge_none; diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index c5bc6c3..fa1f2f2 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -1169,6 +1169,57 @@ select * from boolpart where a is not unknown; t (2 rows) +-- try some other permutations with a NULL partition instead of a DEFAULT +delete from boolpart where a is null; +create table boolpart_null partition of boolpart for values in (null); +insert into boolpart values(null); +explain (costs off) select * from boolpart where a is not true; + QUERY PLAN +-------------------------------------------- + Append + -> Seq Scan on boolpart_f boolpart_1 + Filter: (a IS NOT TRUE) + -> Seq Scan on boolpart_null boolpart_2 + Filter: (a IS NOT TRUE) +(5 rows) + +explain (costs off) select * from boolpart where a is not true and a is not false; + QUERY PLAN +-------------------------------------------------- + Seq Scan on boolpart_null boolpart + Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE)) +(2 rows) + +explain (costs off) select * from boolpart where a is not false; + QUERY PLAN +-------------------------------------------- + Append + -> Seq Scan on boolpart_t boolpart_1 + Filter: (a IS NOT FALSE) + -> Seq Scan on boolpart_null boolpart_2 + Filter: (a IS NOT FALSE) +(5 rows) + +select * from boolpart where a is not true; + a +--- + f + +(2 rows) + +select * from boolpart where a is not true and a is not false; + a +--- + +(1 row) + +select * from boolpart where a is not false; + a +--- + t + +(2 rows) + -- inverse boolean partitioning - a seemingly unlikely design, but we've got -- code for it, so we'd better test it. create table iboolpart (a bool) partition by list ((not a)); @@ -1315,11 +1366,37 @@ select * from iboolpart where a is not unknown; f (2 rows) +-- Try some other permutations with a NULL partition instead of a DEFAULT +delete from iboolpart where a is null; +create table iboolpart_null partition of iboolpart for values in (null); +insert into iboolpart values(null); +-- Pruning shouldn't take place for these. Just check the result is correct +select * from iboolpart where a is not true; + a +--- + f + +(2 rows) + +select * from iboolpart where a is not true and a is not false; + a +--- + +(1 row) + +select * from iboolpart where a is not false; + a +--- + t + +(2 rows) + create table boolrangep (a bool, b bool, c int) partition by range (a,b,c); create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100); create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100); create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50); create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100); +create table boolrangep_null partition of boolrangep default; -- try a more complex case that's been known to trip up pruning in the past explain (costs off) select * from boolrangep where not a and not b and c = 25; QUERY PLAN @@ -1328,6 +1405,32 @@ explain (costs off) select * from boolrangep where not a and not b and c = 25; Filter: ((NOT a) AND (NOT b) AND (c = 25)) (2 rows) +-- ensure we prune boolrangep_tf +explain (costs off) select * from boolrangep where a is not true and not b and c = 25; + QUERY PLAN +------------------------------------------------------------ + Append + -> Seq Scan on boolrangep_ff1 boolrangep_1 + Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25)) + -> Seq Scan on boolrangep_ff2 boolrangep_2 + Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25)) + -> Seq Scan on boolrangep_ft boolrangep_3 + Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25)) + -> Seq Scan on boolrangep_null boolrangep_4 + Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25)) +(9 rows) + +-- ensure we prune everything apart from boolrangep_tf and boolrangep_null +explain (costs off) select * from boolrangep where a is not false and not b and c = 25; + QUERY PLAN +------------------------------------------------------------- + Append + -> Seq Scan on boolrangep_tf boolrangep_1 + Filter: ((a IS NOT FALSE) AND (NOT b) AND (c = 25)) + -> Seq Scan on boolrangep_null boolrangep_2 + Filter: ((a IS NOT FALSE) AND (NOT b) AND (c = 25)) +(5 rows) + -- test scalar-to-array operators create table coercepart (a varchar) partition by list (a); create table coercepart_ab partition of coercepart for values in ('ab'); @@ -2709,6 +2812,7 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a; Index Cond: (a = 1) -> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0 loops=1) Recheck Cond: (a = 1) + Heap Blocks: exact=1 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) Index Cond: (a = 1) -> Materialize (actual rows=1 loops=1) @@ -2724,9 +2828,10 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a; Index Cond: (a = 1) -> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1) Recheck Cond: (a = 1) + Heap Blocks: exact=1 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1) Index Cond: (a = 1) -(34 rows) +(36 rows) table ab; a | b diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index 08e42f1..78d3bc0 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -2390,11 +2390,9 @@ select namedparmcursor_test7(); ERROR: division by zero CONTEXT: SQL expression "42/0 AS p1, 77 AS p2" PL/pgSQL function namedparmcursor_test7() line 6 at OPEN --- check that line comments work correctly within the argument list (there --- is some special handling of this case in the code: the newline after the --- comment must be preserved when the argument-evaluating query is --- constructed, otherwise the comment effectively comments out the next --- argument, too) +-- check that line comments work correctly within the argument list +-- (this used to require a special hack in the code; it no longer does, +-- but let's keep the test anyway) create function namedparmcursor_test8() returns int4 as $$ declare c1 cursor (p1 int, p2 int) for diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out index e2e62db..6b16e75 100644 --- a/src/test/regress/expected/rangefuncs.out +++ b/src/test/regress/expected/rangefuncs.out @@ -2485,3 +2485,19 @@ select * from [{"id": "1"}] | 1 (1 row) +-- check detection of mismatching record types with a const-folded expression +with a(b) as (values (row(1,2,3))) +select * from a, coalesce(b) as c(d int, e int); -- fail +ERROR: function return row and query-specified return row do not match +DETAIL: Returned row contains 3 attributes, but query expects 2. +with a(b) as (values (row(1,2,3))) +select * from a, coalesce(b) as c(d int, e int, f int, g int); -- fail +ERROR: function return row and query-specified return row do not match +DETAIL: Returned row contains 3 attributes, but query expects 4. +with a(b) as (values (row(1,2,3))) +select * from a, coalesce(b) as c(d int, e int, f float); -- fail +ERROR: function return row and query-specified return row do not match +DETAIL: Returned type integer at ordinal position 3, but query expects double precision. +select * from int8_tbl, coalesce(row(1)) as (a int, b int); -- fail +ERROR: function return row and query-specified return row do not match +DETAIL: Returned row contains 1 attribute, but query expects 2. diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 225204c..0a1e17e 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2453,10 +2453,7 @@ pg_stats_ext| SELECT cn.nspname AS schemaname, array_agg(pg_mcv_list_items.frequency) AS most_common_freqs, array_agg(pg_mcv_list_items.base_frequency) AS most_common_base_freqs FROM pg_mcv_list_items(sd.stxdmcv) pg_mcv_list_items(index, "values", nulls, frequency, base_frequency)) m ON ((sd.stxdmcv IS NOT NULL))) - WHERE ((NOT (EXISTS ( SELECT 1 - FROM (unnest(s.stxkeys) k(k) - JOIN pg_attribute a ON (((a.attrelid = s.stxrelid) AND (a.attnum = k.k)))) - WHERE (NOT has_column_privilege(c.oid, a.attnum, 'select'::text))))) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid)))); + WHERE (pg_has_role(c.relowner, 'USAGE'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid)))); pg_stats_ext_exprs| SELECT cn.nspname AS schemaname, c.relname AS tablename, sn.nspname AS statistics_schemaname, @@ -2529,7 +2526,8 @@ pg_stats_ext_exprs| SELECT cn.nspname AS schemaname, LEFT JOIN pg_namespace cn ON ((cn.oid = c.relnamespace))) LEFT JOIN pg_namespace sn ON ((sn.oid = s.stxnamespace))) JOIN LATERAL ( SELECT unnest(pg_get_statisticsobjdef_expressions(s.oid)) AS expr, - unnest(sd.stxdexpr) AS a) stat ON ((stat.expr IS NOT NULL))); + unnest(sd.stxdexpr) AS a) stat ON ((stat.expr IS NOT NULL))) + WHERE (pg_has_role(c.relowner, 'USAGE'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid)))); pg_tables| SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index 67cae4a..69e5704 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -3266,10 +3266,53 @@ SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not le (0 rows) DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak +-- privilege checks for pg_stats_ext and pg_stats_ext_exprs +RESET SESSION AUTHORIZATION; +CREATE TABLE stats_ext_tbl (id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, col TEXT); +INSERT INTO stats_ext_tbl (col) VALUES ('secret'), ('secret'), ('very secret'); +CREATE STATISTICS s_col ON id, col FROM stats_ext_tbl; +CREATE STATISTICS s_expr ON mod(id, 2), lower(col) FROM stats_ext_tbl; +ANALYZE stats_ext_tbl; +-- unprivileged role should not have access +SET SESSION AUTHORIZATION regress_stats_user1; +SELECT statistics_name, most_common_vals FROM pg_stats_ext x + WHERE tablename = 'stats_ext_tbl' ORDER BY ROW(x.*); + statistics_name | most_common_vals +-----------------+------------------ +(0 rows) + +SELECT statistics_name, most_common_vals FROM pg_stats_ext_exprs x + WHERE tablename = 'stats_ext_tbl' ORDER BY ROW(x.*); + statistics_name | most_common_vals +-----------------+------------------ +(0 rows) + +-- give unprivileged role ownership of table +RESET SESSION AUTHORIZATION; +ALTER TABLE stats_ext_tbl OWNER TO regress_stats_user1; +-- unprivileged role should now have access +SET SESSION AUTHORIZATION regress_stats_user1; +SELECT statistics_name, most_common_vals FROM pg_stats_ext x + WHERE tablename = 'stats_ext_tbl' ORDER BY ROW(x.*); + statistics_name | most_common_vals +-----------------+------------------------------------------- + s_col | {{1,secret},{2,secret},{3,"very secret"}} + s_expr | {{0,secret},{1,secret},{1,"very secret"}} +(2 rows) + +SELECT statistics_name, most_common_vals FROM pg_stats_ext_exprs x + WHERE tablename = 'stats_ext_tbl' ORDER BY ROW(x.*); + statistics_name | most_common_vals +-----------------+------------------ + s_expr | {secret} + s_expr | {1} +(2 rows) + -- Tidy up DROP OPERATOR <<< (int, int); DROP FUNCTION op_leak(int, int); RESET SESSION AUTHORIZATION; +DROP TABLE stats_ext_tbl; DROP SCHEMA tststats CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table tststats.priv_test_tbl diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out index 79f8180..2ee8712 100644 --- a/src/test/regress/expected/timestamp.out +++ b/src/test/regress/expected/timestamp.out @@ -699,6 +699,13 @@ SELECT date_bin('5 min'::interval, timestamp '2020-02-01 01:01:01', timestamp '2 Sat Feb 01 00:57:30 2020 (1 row) +-- test roundoff edge case when source < origin +SELECT date_bin('30 minutes'::interval, timestamp '2024-02-01 15:00:00', timestamp '2024-02-01 17:00:00'); + date_bin +-------------------------- + Thu Feb 01 15:00:00 2024 +(1 row) + -- disallow intervals with months or years SELECT date_bin('5 months'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01'); ERROR: timestamps cannot be binned into intervals containing months or years @@ -710,6 +717,13 @@ ERROR: stride must be greater than zero -- disallow negative intervals SELECT date_bin('-2 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00'); ERROR: stride must be greater than zero +-- test overflow cases +select date_bin('15 minutes'::interval, timestamp '294276-12-30', timestamp '4000-12-20 BC'); +ERROR: interval out of range +select date_bin('200000000 days'::interval, '2024-02-01'::timestamp, '2024-01-01'::timestamp); +ERROR: interval out of range +select date_bin('365000 days'::interval, '4400-01-01 BC'::timestamp, '4000-01-01 BC'::timestamp); +ERROR: timestamp out of range -- Test casting within a BETWEEN qualifier SELECT d1 - timestamp without time zone '1997-01-02' AS diff FROM TIMESTAMP_TBL diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out index eba8419..790cae1 100644 --- a/src/test/regress/expected/timestamptz.out +++ b/src/test/regress/expected/timestamptz.out @@ -743,6 +743,13 @@ SELECT date_bin('5 min'::interval, timestamptz '2020-02-01 01:01:01+00', timesta Fri Jan 31 16:57:30 2020 PST (1 row) +-- test roundoff edge case when source < origin +SELECT date_bin('30 minutes'::interval, timestamptz '2024-02-01 15:00:00', timestamptz '2024-02-01 17:00:00'); + date_bin +------------------------------ + Thu Feb 01 15:00:00 2024 PST +(1 row) + -- disallow intervals with months or years SELECT date_bin('5 months'::interval, timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00'); ERROR: timestamps cannot be binned into intervals containing months or years @@ -754,6 +761,13 @@ ERROR: stride must be greater than zero -- disallow negative intervals SELECT date_bin('-2 days'::interval, timestamp with time zone '1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01 00:00:00+00'); ERROR: stride must be greater than zero +-- test overflow cases +select date_bin('15 minutes'::interval, timestamptz '294276-12-30', timestamptz '4000-12-20 BC'); +ERROR: interval out of range +select date_bin('200000000 days'::interval, '2024-02-01'::timestamptz, '2024-01-01'::timestamptz); +ERROR: interval out of range +select date_bin('365000 days'::interval, '4400-01-01 BC'::timestamptz, '4000-01-01 BC'::timestamptz); +ERROR: timestamp out of range -- Test casting within a BETWEEN qualifier SELECT d1 - timestamp with time zone '1997-01-02' AS diff FROM TIMESTAMPTZ_TBL diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 723e8b7..4dd95e2 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -1745,6 +1745,10 @@ select * from parent; select * from child; update parent set val1 = 'b' where aid = 1; -- should fail ERROR: tuple to be updated was already modified by an operation triggered by the current command HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. +merge into parent p using (values (1)) as v(id) on p.aid = v.id + when matched then update set val1 = 'b'; -- should fail +ERROR: tuple to be updated or deleted was already modified by an operation triggered by the current command +HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. select * from parent; select * from child; aid | val1 | val2 | val3 | val4 | bcnt -----+------+------+------+------+------ @@ -1759,6 +1763,10 @@ select * from parent; select * from child; delete from parent where aid = 1; -- should fail ERROR: tuple to be deleted was already modified by an operation triggered by the current command HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. +merge into parent p using (values (1)) as v(id) on p.aid = v.id + when matched then delete; -- should fail +ERROR: tuple to be updated or deleted was already modified by an operation triggered by the current command +HINT: Consider using an AFTER trigger instead of a BEFORE trigger to propagate changes to other rows. select * from parent; select * from child; aid | val1 | val2 | val3 | val4 | bcnt -----+------+------+------+------+------ diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out index 1f23baa..f8914c0 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -3481,13 +3481,13 @@ EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, salary, - count(empno) OVER (ORDER BY salary DESC) c + count(1) OVER (ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; - QUERY PLAN ---------------------------------------------------------- + QUERY PLAN +------------------------------------------- WindowAgg - Run Condition: (count(empsalary.empno) OVER (?) <= 3) + Run Condition: (count(1) OVER (?) <= 3) -> Sort Sort Key: empsalary.salary DESC -> Seq Scan on empsalary @@ -3496,7 +3496,7 @@ WHERE c <= 3; SELECT * FROM (SELECT empno, salary, - count(empno) OVER (ORDER BY salary DESC) c + count(1) OVER (ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; empno | salary | c @@ -3608,19 +3608,19 @@ WHERE rn < 3; -> Seq Scan on empsalary (6 rows) --- likewise with count(empno) instead of row_number() +-- likewise with count(1) instead of row_number() EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, depname, salary, - count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c + count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; QUERY PLAN ------------------------------------------------------------ WindowAgg - Run Condition: (count(empsalary.empno) OVER (?) <= 3) + Run Condition: (count(1) OVER (?) <= 3) -> Sort Sort Key: empsalary.depname, empsalary.salary DESC -> Seq Scan on empsalary @@ -3631,7 +3631,7 @@ SELECT * FROM (SELECT empno, depname, salary, - count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c + count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; empno | depname | salary | c @@ -3653,13 +3653,13 @@ SELECT * FROM (SELECT empno, depname, salary, - count(empno) OVER () c + count(1) OVER () c FROM empsalary) emp WHERE c = 1; - QUERY PLAN --------------------------------------------------------- + QUERY PLAN +------------------------------------------ WindowAgg - Run Condition: (count(empsalary.empno) OVER (?) = 1) + Run Condition: (count(1) OVER (?) = 1) -> Seq Scan on empsalary (3 rows) @@ -3667,7 +3667,7 @@ WHERE c = 1; EXPLAIN (COSTS OFF) SELECT * FROM (SELECT *, - count(salary) OVER (PARTITION BY depname || '') c1, -- w1 + count(1) OVER (PARTITION BY depname || '') c1, -- w1 row_number() OVER (PARTITION BY depname) rn, -- w2 count(*) OVER (PARTITION BY depname) c2, -- w2 count(*) OVER (PARTITION BY '' || depname) c3 -- w3 @@ -3678,7 +3678,7 @@ SELECT * FROM Subquery Scan on e -> WindowAgg Filter: ((row_number() OVER (?)) <= 1) - Run Condition: (count(empsalary.salary) OVER (?) <= 3) + Run Condition: (count(1) OVER (?) <= 3) -> Sort Sort Key: (((empsalary.depname)::text || ''::text)) -> WindowAgg @@ -3694,7 +3694,7 @@ SELECT * FROM -- Ensure we correctly filter out all of the run conditions from each window SELECT * FROM (SELECT *, - count(salary) OVER (PARTITION BY depname || '') c1, -- w1 + count(1) OVER (PARTITION BY depname || '') c1, -- w1 row_number() OVER (PARTITION BY depname) rn, -- w2 count(*) OVER (PARTITION BY depname) c2, -- w2 count(*) OVER (PARTITION BY '' || depname) c3 -- w3 @@ -3765,6 +3765,24 @@ WHERE c = 1; -> Seq Scan on empsalary (6 rows) +-- Ensure we don't use a run condition when the WindowFunc arg contains a Var +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT empno, + salary, + count(empno) OVER (ORDER BY empno DESC) c + FROM empsalary) emp +WHERE c = 1; + QUERY PLAN +---------------------------------------------- + Subquery Scan on emp + Filter: (emp.c = 1) + -> WindowAgg + -> Sort + Sort Key: empsalary.empno DESC + -> Seq Scan on empsalary +(6 rows) + -- Ensure we don't use a run condition when the WindowFunc contains subplans EXPLAIN (COSTS OFF) SELECT * FROM diff --git a/src/test/regress/sql/create_procedure.sql b/src/test/regress/sql/create_procedure.sql index 75cc0fc..50a4d88 100644 --- a/src/test/regress/sql/create_procedure.sql +++ b/src/test/regress/sql/create_procedure.sql @@ -90,7 +90,16 @@ AS $$ CALL ptest4a(a, b); -- error, not supported $$; -DROP PROCEDURE ptest4a; +-- we used to get confused by a single output argument that is composite +CREATE PROCEDURE ptest4c(INOUT comp int8_tbl) +LANGUAGE SQL +AS $$ +SELECT ROW(1, 2); +$$; + +CALL ptest4c(NULL); + +DROP PROCEDURE ptest4a, ptest4c; -- named and default parameters diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql index eefb860..aa147b1 100644 --- a/src/test/regress/sql/foreign_data.sql +++ b/src/test/regress/sql/foreign_data.sql @@ -419,8 +419,10 @@ ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@'); ALTER FOREIGN TABLE ft1 DROP COLUMN no_column; -- ERROR ALTER FOREIGN TABLE ft1 DROP COLUMN IF EXISTS no_column; ALTER FOREIGN TABLE ft1 DROP COLUMN c9; +ALTER FOREIGN TABLE ft1 ADD COLUMN c11 serial; ALTER FOREIGN TABLE ft1 SET SCHEMA foreign_schema; ALTER FOREIGN TABLE ft1 SET TABLESPACE ts; -- ERROR +ALTER SEQUENCE foreign_schema.ft1_c11_seq SET SCHEMA public; -- ERROR ALTER FOREIGN TABLE foreign_schema.ft1 RENAME c1 TO foreign_column_1; ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; \d foreign_schema.foreign_table_1 diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql index 4065348..7d6153a 100644 --- a/src/test/regress/sql/horology.sql +++ b/src/test/regress/sql/horology.sql @@ -87,6 +87,7 @@ SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '106000000 days' SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '107000000 days' AS "Jan 20, 288244"; SELECT timestamp without time zone 'Jan 1, 4713 BC' + interval '109203489 days' AS "Dec 31, 294276"; SELECT timestamp without time zone '2000-01-01' - interval '2483590 days' AS "out of range"; +SELECT timestamp without time zone '294276-12-31 23:59:59' + interval '9223372036854775807 microseconds' AS "out of range"; SELECT timestamp without time zone '12/31/294276' - timestamp without time zone '12/23/1999' AS "106751991 Days"; -- Shorthand values @@ -119,6 +120,7 @@ SELECT timestamp with time zone '1999-03-01' - interval '1 second' AS "Feb 28"; SELECT timestamp with time zone '2000-03-01' - interval '1 second' AS "Feb 29"; SELECT timestamp with time zone '1999-12-01' + interval '1 month - 1 second' AS "Dec 31"; SELECT timestamp with time zone '2000-01-01' - interval '2483590 days' AS "out of range"; +SELECT timestamp with time zone '294276-12-31 23:59:59 UTC' + interval '9223372036854775807 microseconds' AS "out of range"; SELECT (timestamp with time zone 'today' = (timestamp with time zone 'yesterday' + interval '1 day')) as "True"; SELECT (timestamp with time zone 'today' = (timestamp with time zone 'tomorrow' - interval '1 day')) as "True"; diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql index 9b8db2e..91d2e44 100644 --- a/src/test/regress/sql/identity.sql +++ b/src/test/regress/sql/identity.sql @@ -214,6 +214,24 @@ SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regcl ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error +-- check that unlogged propagates to sequence +CREATE UNLOGGED TABLE itest17 (a int NOT NULL, b text); +ALTER TABLE itest17 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; +ALTER TABLE itest17 ADD COLUMN c int GENERATED ALWAYS AS IDENTITY; +\d itest17 +\d itest17_a_seq +\d itest17_c_seq +CREATE TABLE itest18 (a int NOT NULL, b text); +ALTER TABLE itest18 SET UNLOGGED, ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; +\d itest18 +\d itest18_a_seq +ALTER TABLE itest18 SET LOGGED; +\d itest18 +\d itest18_a_seq +ALTER TABLE itest18 SET UNLOGGED; +\d itest18 +\d itest18_a_seq + -- kinda silly to change property in the same command, but it should work ALTER TABLE itest3 ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY, diff --git a/src/test/regress/sql/index_including.sql b/src/test/regress/sql/index_including.sql index 44b3400..ad9cbdd 100644 --- a/src/test/regress/sql/index_including.sql +++ b/src/test/regress/sql/index_including.sql @@ -217,3 +217,22 @@ ALTER TABLE tbl ALTER c1 TYPE bigint; ALTER TABLE tbl ALTER c3 TYPE bigint; \d tbl DROP TABLE tbl; + +/* + * 10. Test coverage for names stored as cstrings in indexes + */ +CREATE TABLE nametbl (c1 int, c2 name, c3 float); +CREATE INDEX nametbl_c1_c2_idx ON nametbl (c2, c1) INCLUDE (c3); +INSERT INTO nametbl VALUES(1, 'two', 3.0); +VACUUM nametbl; +SET enable_seqscan = 0; + +-- Ensure we get an index only scan plan +EXPLAIN (COSTS OFF) SELECT c2, c1, c3 FROM nametbl WHERE c2 = 'two' AND c1 = 1; + +-- Validate the results look sane +SELECT c2, c1, c3 FROM nametbl WHERE c2 = 'two' AND c1 = 1; + +RESET enable_seqscan; + +DROP TABLE nametbl;
\ No newline at end of file diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql index bdcffd0..2b086ee 100644 --- a/src/test/regress/sql/insert.sql +++ b/src/test/regress/sql/insert.sql @@ -105,7 +105,84 @@ create rule irule3 as on insert to inserttest2 do also drop table inserttest2; drop table inserttest; -drop type insert_test_type; + +-- Make the same tests with domains over the array and composite fields + +create domain insert_pos_ints as int[] check (value[1] > 0); + +create domain insert_test_domain as insert_test_type + check ((value).if2[1] is not null); + +create table inserttesta (f1 int, f2 insert_pos_ints); +create table inserttestb (f3 insert_test_domain, f4 insert_test_domain[]); + +insert into inserttesta (f2[1], f2[2]) values (1,2); +insert into inserttesta (f2[1], f2[2]) values (3,4), (5,6); +insert into inserttesta (f2[1], f2[2]) select 7,8; +insert into inserttesta (f2[1], f2[2]) values (1,default); -- not supported +insert into inserttesta (f2[1], f2[2]) values (0,2); +insert into inserttesta (f2[1], f2[2]) values (3,4), (0,6); +insert into inserttesta (f2[1], f2[2]) select 0,8; + +insert into inserttestb (f3.if1, f3.if2) values (1,array['foo']); +insert into inserttestb (f3.if1, f3.if2) values (1,'{foo}'), (2,'{bar}'); +insert into inserttestb (f3.if1, f3.if2) select 3, '{baz,quux}'; +insert into inserttestb (f3.if1, f3.if2) values (1,default); -- not supported +insert into inserttestb (f3.if1, f3.if2) values (1,array[null]); +insert into inserttestb (f3.if1, f3.if2) values (1,'{null}'), (2,'{bar}'); +insert into inserttestb (f3.if1, f3.if2) select 3, '{null,quux}'; + +insert into inserttestb (f3.if2[1], f3.if2[2]) values ('foo', 'bar'); +insert into inserttestb (f3.if2[1], f3.if2[2]) values ('foo', 'bar'), ('baz', 'quux'); +insert into inserttestb (f3.if2[1], f3.if2[2]) select 'bear', 'beer'; + +insert into inserttestb (f3, f4[1].if2[1], f4[1].if2[2]) values (row(1,'{x}'), 'foo', 'bar'); +insert into inserttestb (f3, f4[1].if2[1], f4[1].if2[2]) values (row(1,'{x}'), 'foo', 'bar'), (row(2,'{y}'), 'baz', 'quux'); +insert into inserttestb (f3, f4[1].if2[1], f4[1].if2[2]) select row(1,'{x}')::insert_test_domain, 'bear', 'beer'; + +select * from inserttesta; +select * from inserttestb; + +-- also check reverse-listing +create table inserttest2 (f1 bigint, f2 text); +create rule irule1 as on insert to inserttest2 do also + insert into inserttestb (f3.if2[1], f3.if2[2]) + values (new.f1,new.f2); +create rule irule2 as on insert to inserttest2 do also + insert into inserttestb (f4[1].if1, f4[1].if2[2]) + values (1,'fool'),(new.f1,new.f2); +create rule irule3 as on insert to inserttest2 do also + insert into inserttestb (f4[1].if1, f4[1].if2[2]) + select new.f1, new.f2; +\d+ inserttest2 + +drop table inserttest2; +drop table inserttesta; +drop table inserttestb; +drop domain insert_pos_ints; +drop domain insert_test_domain; + +-- Verify that multiple inserts to subfields of a domain-over-container +-- check the domain constraints only on the finished value + +create domain insert_nnarray as int[] + check (value[1] is not null and value[2] is not null); + +create domain insert_test_domain as insert_test_type + check ((value).if1 is not null and (value).if2 is not null); + +create table inserttesta (f1 insert_nnarray); +insert into inserttesta (f1[1]) values (1); -- fail +insert into inserttesta (f1[1], f1[2]) values (1, 2); + +create table inserttestb (f1 insert_test_domain); +insert into inserttestb (f1.if1) values (1); -- fail +insert into inserttestb (f1.if1, f1.if2) values (1, '{foo}'); + +drop table inserttesta; +drop table inserttestb; +drop domain insert_nnarray; +drop type insert_test_type cascade; -- direct partition inserts should check partition bound constraint create table range_parted ( diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 1113e98..11a8570 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -2029,6 +2029,25 @@ select * from (select q1.v) ) as q2; +-- check for generation of join EC conditions at wrong level (bug #18429) +explain (costs off) +select * from ( + select arrayd.ad, coalesce(c.hundred, 0) as h + from unnest(array[1]) as arrayd(ad) + left join lateral ( + select hundred from tenk1 where unique2 = arrayd.ad + ) c on true +) c2 +where c2.h * c2.ad = c2.h * (c2.ad + 1); +select * from ( + select arrayd.ad, coalesce(c.hundred, 0) as h + from unnest(array[1]) as arrayd(ad) + left join lateral ( + select hundred from tenk1 where unique2 = arrayd.ad + ) c on true +) c2 +where c2.h * c2.ad = c2.h * (c2.ad + 1); + -- check the number of columns specified SELECT * FROM (int8_tbl i cross join int4_tbl j) ss(a,b,c,d); diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql index d66acae..7b02a82 100644 --- a/src/test/regress/sql/memoize.sql +++ b/src/test/regress/sql/memoize.sql @@ -57,10 +57,31 @@ LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1 OFFSET 0) t2 WHERE t1.unique1 < 1000; +SET enable_mergejoin TO off; + +-- Test for varlena datatype with expr evaluation +CREATE TABLE expr_key (x numeric, t text); +INSERT INTO expr_key (x, t) +SELECT d1::numeric, d1::text FROM ( + SELECT round((d / pi())::numeric, 7) AS d1 FROM generate_series(1, 20) AS d +) t; + +-- duplicate rows so we get some cache hits +INSERT INTO expr_key SELECT * FROM expr_key; + +CREATE INDEX expr_key_idx_x_t ON expr_key (x, t); +VACUUM ANALYZE expr_key; + +-- Ensure we get we get a cache miss and hit for each of the 20 distinct values +SELECT explain_memoize(' +SELECT * FROM expr_key t1 INNER JOIN expr_key t2 +ON t1.x = t2.t::numeric AND t1.t::numeric = t2.x;', false); + +DROP TABLE expr_key; + -- Reduce work_mem and hash_mem_multiplier so that we see some cache evictions SET work_mem TO '64kB'; SET hash_mem_multiplier TO 1.0; -SET enable_mergejoin TO off; -- Ensure we get some evictions. We're unable to validate the hits and misses -- here as the number of entries that fit in the cache at once will vary -- between different machines. diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index 66cb75a..f6b8b9f 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -4,6 +4,8 @@ CREATE USER regress_merge_privs; CREATE USER regress_merge_no_privs; +CREATE USER regress_merge_none; + DROP TABLE IF EXISTS target; DROP TABLE IF EXISTS source; CREATE TABLE target (tid integer, balance integer) @@ -118,6 +120,14 @@ DROP MATERIALIZED VIEW mv; -- permissions +SET SESSION AUTHORIZATION regress_merge_none; +MERGE INTO target +USING (SELECT 1) AS s +ON true +WHEN MATCHED THEN + DO NOTHING; + +SET SESSION AUTHORIZATION regress_merge_privs; MERGE INTO target USING source2 ON target.tid = source2.sid @@ -938,6 +948,23 @@ WHEN MATCHED AND t.a < 10 THEN DROP TABLE ex_msource, ex_mtarget; DROP FUNCTION explain_merge(text); +-- EXPLAIN SubPlans and InitPlans +CREATE TABLE src (a int, b int, c int, d int); +CREATE TABLE tgt (a int, b int, c int, d int); +CREATE TABLE ref (ab int, cd int); + +EXPLAIN (verbose, costs off) +MERGE INTO tgt t +USING (SELECT *, (SELECT count(*) FROM ref r + WHERE r.ab = s.a + s.b + AND r.cd = s.c - s.d) cnt + FROM src s) s +ON t.a = s.a AND t.b < s.cnt +WHEN MATCHED AND t.c > s.cnt THEN + UPDATE SET (b, c) = (SELECT s.b, s.cnt); + +DROP TABLE src, tgt, ref; + -- Subqueries BEGIN; MERGE INTO sq_target t @@ -1471,3 +1498,4 @@ DROP TABLE source, source2; DROP FUNCTION merge_trigfunc(); DROP USER regress_merge_privs; DROP USER regress_merge_no_privs; +DROP USER regress_merge_none; diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index ff7e535..6977efd 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -178,6 +178,19 @@ select * from boolpart where a is not true and a is not false; select * from boolpart where a is unknown; select * from boolpart where a is not unknown; +-- try some other permutations with a NULL partition instead of a DEFAULT +delete from boolpart where a is null; +create table boolpart_null partition of boolpart for values in (null); +insert into boolpart values(null); + +explain (costs off) select * from boolpart where a is not true; +explain (costs off) select * from boolpart where a is not true and a is not false; +explain (costs off) select * from boolpart where a is not false; + +select * from boolpart where a is not true; +select * from boolpart where a is not true and a is not false; +select * from boolpart where a is not false; + -- inverse boolean partitioning - a seemingly unlikely design, but we've got -- code for it, so we'd better test it. create table iboolpart (a bool) partition by list ((not a)); @@ -204,15 +217,32 @@ select * from iboolpart where a is not true and a is not false; select * from iboolpart where a is unknown; select * from iboolpart where a is not unknown; +-- Try some other permutations with a NULL partition instead of a DEFAULT +delete from iboolpart where a is null; +create table iboolpart_null partition of iboolpart for values in (null); +insert into iboolpart values(null); + +-- Pruning shouldn't take place for these. Just check the result is correct +select * from iboolpart where a is not true; +select * from iboolpart where a is not true and a is not false; +select * from iboolpart where a is not false; + create table boolrangep (a bool, b bool, c int) partition by range (a,b,c); create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100); create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100); create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50); create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100); +create table boolrangep_null partition of boolrangep default; -- try a more complex case that's been known to trip up pruning in the past explain (costs off) select * from boolrangep where not a and not b and c = 25; +-- ensure we prune boolrangep_tf +explain (costs off) select * from boolrangep where a is not true and not b and c = 25; + +-- ensure we prune everything apart from boolrangep_tf and boolrangep_null +explain (costs off) select * from boolrangep where a is not false and not b and c = 25; + -- test scalar-to-array operators create table coercepart (a varchar) partition by list (a); create table coercepart_ab partition of coercepart for values in ('ab'); diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index 588c331..cfff49c 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -2047,11 +2047,9 @@ begin end $$ language plpgsql; select namedparmcursor_test7(); --- check that line comments work correctly within the argument list (there --- is some special handling of this case in the code: the newline after the --- comment must be preserved when the argument-evaluating query is --- constructed, otherwise the comment effectively comments out the next --- argument, too) +-- check that line comments work correctly within the argument list +-- (this used to require a special hack in the code; it no longer does, +-- but let's keep the test anyway) create function namedparmcursor_test8() returns int4 as $$ declare c1 cursor (p1 int, p2 int) for diff --git a/src/test/regress/sql/rangefuncs.sql b/src/test/regress/sql/rangefuncs.sql index 63351e1..3c47c98 100644 --- a/src/test/regress/sql/rangefuncs.sql +++ b/src/test/regress/sql/rangefuncs.sql @@ -815,3 +815,13 @@ select * from from unnest(array['{"lectures": [{"id": "1"}]}'::jsonb]) as unnested_modules(module)) as ss, jsonb_to_recordset(ss.lecture) as j (id text); + +-- check detection of mismatching record types with a const-folded expression + +with a(b) as (values (row(1,2,3))) +select * from a, coalesce(b) as c(d int, e int); -- fail +with a(b) as (values (row(1,2,3))) +select * from a, coalesce(b) as c(d int, e int, f int, g int); -- fail +with a(b) as (values (row(1,2,3))) +select * from a, coalesce(b) as c(d int, e int, f float); -- fail +select * from int8_tbl, coalesce(row(1)) as (a int, b int); -- fail diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index f0ee415..76291ef 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -1649,9 +1649,36 @@ SET SESSION AUTHORIZATION regress_stats_user1; SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak +-- privilege checks for pg_stats_ext and pg_stats_ext_exprs +RESET SESSION AUTHORIZATION; +CREATE TABLE stats_ext_tbl (id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, col TEXT); +INSERT INTO stats_ext_tbl (col) VALUES ('secret'), ('secret'), ('very secret'); +CREATE STATISTICS s_col ON id, col FROM stats_ext_tbl; +CREATE STATISTICS s_expr ON mod(id, 2), lower(col) FROM stats_ext_tbl; +ANALYZE stats_ext_tbl; + +-- unprivileged role should not have access +SET SESSION AUTHORIZATION regress_stats_user1; +SELECT statistics_name, most_common_vals FROM pg_stats_ext x + WHERE tablename = 'stats_ext_tbl' ORDER BY ROW(x.*); +SELECT statistics_name, most_common_vals FROM pg_stats_ext_exprs x + WHERE tablename = 'stats_ext_tbl' ORDER BY ROW(x.*); + +-- give unprivileged role ownership of table +RESET SESSION AUTHORIZATION; +ALTER TABLE stats_ext_tbl OWNER TO regress_stats_user1; + +-- unprivileged role should now have access +SET SESSION AUTHORIZATION regress_stats_user1; +SELECT statistics_name, most_common_vals FROM pg_stats_ext x + WHERE tablename = 'stats_ext_tbl' ORDER BY ROW(x.*); +SELECT statistics_name, most_common_vals FROM pg_stats_ext_exprs x + WHERE tablename = 'stats_ext_tbl' ORDER BY ROW(x.*); + -- Tidy up DROP OPERATOR <<< (int, int); DROP FUNCTION op_leak(int, int); RESET SESSION AUTHORIZATION; +DROP TABLE stats_ext_tbl; DROP SCHEMA tststats CASCADE; DROP USER regress_stats_user1; diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql index ebc969f..bc37803 100644 --- a/src/test/regress/sql/timestamp.sql +++ b/src/test/regress/sql/timestamp.sql @@ -259,6 +259,9 @@ FROM ( -- shift bins using the origin parameter: SELECT date_bin('5 min'::interval, timestamp '2020-02-01 01:01:01', timestamp '2020-02-01 00:02:30'); +-- test roundoff edge case when source < origin +SELECT date_bin('30 minutes'::interval, timestamp '2024-02-01 15:00:00', timestamp '2024-02-01 17:00:00'); + -- disallow intervals with months or years SELECT date_bin('5 months'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01'); SELECT date_bin('5 years'::interval, timestamp '2020-02-01 01:01:01', timestamp '2001-01-01'); @@ -269,6 +272,11 @@ SELECT date_bin('0 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp -- disallow negative intervals SELECT date_bin('-2 days'::interval, timestamp '1970-01-01 01:00:00' , timestamp '1970-01-01 00:00:00'); +-- test overflow cases +select date_bin('15 minutes'::interval, timestamp '294276-12-30', timestamp '4000-12-20 BC'); +select date_bin('200000000 days'::interval, '2024-02-01'::timestamp, '2024-01-01'::timestamp); +select date_bin('365000 days'::interval, '4400-01-01 BC'::timestamp, '4000-01-01 BC'::timestamp); + -- Test casting within a BETWEEN qualifier SELECT d1 - timestamp without time zone '1997-01-02' AS diff FROM TIMESTAMP_TBL diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql index a107abc..875479e 100644 --- a/src/test/regress/sql/timestamptz.sql +++ b/src/test/regress/sql/timestamptz.sql @@ -234,6 +234,9 @@ FROM ( -- shift bins using the origin parameter: SELECT date_bin('5 min'::interval, timestamptz '2020-02-01 01:01:01+00', timestamptz '2020-02-01 00:02:30+00'); +-- test roundoff edge case when source < origin +SELECT date_bin('30 minutes'::interval, timestamptz '2024-02-01 15:00:00', timestamptz '2024-02-01 17:00:00'); + -- disallow intervals with months or years SELECT date_bin('5 months'::interval, timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00'); SELECT date_bin('5 years'::interval, timestamp with time zone '2020-02-01 01:01:01+00', timestamp with time zone '2001-01-01+00'); @@ -244,6 +247,11 @@ SELECT date_bin('0 days'::interval, timestamp with time zone '1970-01-01 01:00:0 -- disallow negative intervals SELECT date_bin('-2 days'::interval, timestamp with time zone '1970-01-01 01:00:00+00' , timestamp with time zone '1970-01-01 00:00:00+00'); +-- test overflow cases +select date_bin('15 minutes'::interval, timestamptz '294276-12-30', timestamptz '4000-12-20 BC'); +select date_bin('200000000 days'::interval, '2024-02-01'::timestamptz, '2024-01-01'::timestamptz); +select date_bin('365000 days'::interval, '4400-01-01 BC'::timestamptz, '4000-01-01 BC'::timestamptz); + -- Test casting within a BETWEEN qualifier SELECT d1 - timestamp with time zone '1997-01-02' AS diff FROM TIMESTAMPTZ_TBL diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 46795a9..6c9e066 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -1186,9 +1186,13 @@ insert into child values (10, 1, 'b'); select * from parent; select * from child; update parent set val1 = 'b' where aid = 1; -- should fail +merge into parent p using (values (1)) as v(id) on p.aid = v.id + when matched then update set val1 = 'b'; -- should fail select * from parent; select * from child; delete from parent where aid = 1; -- should fail +merge into parent p using (values (1)) as v(id) on p.aid = v.id + when matched then delete; -- should fail select * from parent; select * from child; -- replace the trigger function with one that restarts the deletion after diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql index 253c1b7..2a5aa38 100644 --- a/src/test/regress/sql/window.sql +++ b/src/test/regress/sql/window.sql @@ -1068,14 +1068,14 @@ EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, salary, - count(empno) OVER (ORDER BY salary DESC) c + count(1) OVER (ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; SELECT * FROM (SELECT empno, salary, - count(empno) OVER (ORDER BY salary DESC) c + count(1) OVER (ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; @@ -1131,13 +1131,13 @@ SELECT empno, depname FROM FROM empsalary) emp WHERE rn < 3; --- likewise with count(empno) instead of row_number() +-- likewise with count(1) instead of row_number() EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, depname, salary, - count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c + count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; @@ -1146,7 +1146,7 @@ SELECT * FROM (SELECT empno, depname, salary, - count(empno) OVER (PARTITION BY depname ORDER BY salary DESC) c + count(1) OVER (PARTITION BY depname ORDER BY salary DESC) c FROM empsalary) emp WHERE c <= 3; @@ -1157,7 +1157,7 @@ SELECT * FROM (SELECT empno, depname, salary, - count(empno) OVER () c + count(1) OVER () c FROM empsalary) emp WHERE c = 1; @@ -1165,7 +1165,7 @@ WHERE c = 1; EXPLAIN (COSTS OFF) SELECT * FROM (SELECT *, - count(salary) OVER (PARTITION BY depname || '') c1, -- w1 + count(1) OVER (PARTITION BY depname || '') c1, -- w1 row_number() OVER (PARTITION BY depname) rn, -- w2 count(*) OVER (PARTITION BY depname) c2, -- w2 count(*) OVER (PARTITION BY '' || depname) c3 -- w3 @@ -1175,7 +1175,7 @@ SELECT * FROM -- Ensure we correctly filter out all of the run conditions from each window SELECT * FROM (SELECT *, - count(salary) OVER (PARTITION BY depname || '') c1, -- w1 + count(1) OVER (PARTITION BY depname || '') c1, -- w1 row_number() OVER (PARTITION BY depname) rn, -- w2 count(*) OVER (PARTITION BY depname) c2, -- w2 count(*) OVER (PARTITION BY '' || depname) c3 -- w3 @@ -1215,6 +1215,15 @@ SELECT * FROM FROM empsalary) emp WHERE c = 1; +-- Ensure we don't use a run condition when the WindowFunc arg contains a Var +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT empno, + salary, + count(empno) OVER (ORDER BY empno DESC) c + FROM empsalary) emp +WHERE c = 1; + -- Ensure we don't use a run condition when the WindowFunc contains subplans EXPLAIN (COSTS OFF) SELECT * FROM |