diff options
Diffstat (limited to 'src/test/regress/expected')
| -rw-r--r-- | src/test/regress/expected/aggregates.out | 2 | ||||
| -rw-r--r-- | src/test/regress/expected/bit.out | 17 | ||||
| -rw-r--r-- | src/test/regress/expected/create_procedure.out | 14 | ||||
| -rw-r--r-- | src/test/regress/expected/foreign_data.out | 29 | ||||
| -rw-r--r-- | src/test/regress/expected/horology.out | 4 | ||||
| -rw-r--r-- | src/test/regress/expected/identity.out | 72 | ||||
| -rw-r--r-- | src/test/regress/expected/index_including.out | 25 | ||||
| -rw-r--r-- | src/test/regress/expected/insert.out | 116 | ||||
| -rw-r--r-- | src/test/regress/expected/memoize.out | 31 | ||||
| -rw-r--r-- | src/test/regress/expected/merge.out | 60 | ||||
| -rw-r--r-- | src/test/regress/expected/partition_prune.out | 107 | ||||
| -rw-r--r-- | src/test/regress/expected/plpgsql.out | 8 | ||||
| -rw-r--r-- | src/test/regress/expected/rangefuncs.out | 16 | ||||
| -rw-r--r-- | src/test/regress/expected/rules.out | 8 | ||||
| -rw-r--r-- | src/test/regress/expected/stats_ext.out | 43 | ||||
| -rw-r--r-- | src/test/regress/expected/timestamp.out | 14 | ||||
| -rw-r--r-- | src/test/regress/expected/timestamptz.out | 14 | ||||
| -rw-r--r-- | src/test/regress/expected/triggers.out | 8 | ||||
| -rw-r--r-- | src/test/regress/expected/window.out | 94 |
19 files changed, 613 insertions, 69 deletions
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index f635c5a..68fd716 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -1930,7 +1930,7 @@ select string_agg(v, decode('ee', 'hex')) from bytea_test_table; drop table bytea_test_table; -- Test parallel string_agg and array_agg -create table pagg_test (x int, y int); +create table pagg_test (x int, y int) with (autovacuum_enabled = off); insert into pagg_test select (case x % 4 when 1 then null else x end), x % 10 from generate_series(1,5000) x; diff --git a/src/test/regress/expected/bit.out b/src/test/regress/expected/bit.out index 98c2655..e17cbf4 100644 --- a/src/test/regress/expected/bit.out +++ b/src/test/regress/expected/bit.out @@ -40,6 +40,23 @@ SELECT * FROM VARBIT_TABLE; 01010101010 (4 rows) +-- Literals with syntax errors +SELECT b' 0'; +ERROR: " " is not a valid binary digit +LINE 1: SELECT b' 0'; + ^ +SELECT b'0 '; +ERROR: " " is not a valid binary digit +LINE 1: SELECT b'0 '; + ^ +SELECT x' 0'; +ERROR: " " is not a valid hexadecimal digit +LINE 1: SELECT x' 0'; + ^ +SELECT x'0 '; +ERROR: " " is not a valid hexadecimal digit +LINE 1: SELECT x'0 '; + ^ -- Concatenation SELECT v, b, (v || b) AS concat FROM BIT_TABLE, VARBIT_TABLE diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out index f2a677f..6ab09d7 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 5b30ee4..6ed50fd 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -892,24 +892,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 f3cda4a..e48534b 100644 --- a/src/test/regress/expected/horology.out +++ b/src/test/regress/expected/horology.out @@ -484,6 +484,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 ------------------ @@ -746,6 +748,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/memoize.out b/src/test/regress/expected/memoize.out index f520243..5be2cc9 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 f87905f..bfdf59a 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) +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 @@ -2248,3 +2307,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 a820385..4f88e7b 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 272f5d2..fb5a0d8 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 fbb840e..397a8b3 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 7fd81e6..09a2556 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2497,10 +2497,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, @@ -2573,7 +2570,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 a430153..b4c8561 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -3281,10 +3281,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 c64bcb7..ef45d28 100644 --- a/src/test/regress/expected/timestamp.out +++ b/src/test/regress/expected/timestamp.out @@ -736,6 +736,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 @@ -747,6 +754,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 0dd2fe2..db56fcf 100644 --- a/src/test/regress/expected/timestamptz.out +++ b/src/test/regress/expected/timestamptz.out @@ -780,6 +780,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 @@ -791,6 +798,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 78e9030..7f774e5 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 8612788..1a9cc66 100644 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@ -3577,13 +3577,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 @@ -3592,7 +3592,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 @@ -3704,19 +3704,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 @@ -3727,7 +3727,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 @@ -3749,13 +3749,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) @@ -3763,7 +3763,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 @@ -3775,7 +3775,7 @@ SELECT * FROM Subquery Scan on e -> WindowAgg Filter: (((row_number() OVER (?)) <= 1) AND ((ntile(2) OVER (?)) < 2)) - Run Condition: (count(empsalary.salary) OVER (?) <= 3) + Run Condition: (count(1) OVER (?) <= 3) -> Sort Sort Key: (((empsalary.depname)::text || ''::text)) -> WindowAgg @@ -3791,7 +3791,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 @@ -3804,32 +3804,6 @@ SELECT * FROM sales | 3 | 4800 | 08-01-2007 | 3 | 1 | 3 | 3 | 1 (2 rows) --- Ensure we remove references to reduced outer joins as nulling rels in run --- conditions -EXPLAIN (COSTS OFF) -SELECT 1 FROM - (SELECT ntile(e2.salary) OVER (PARTITION BY e1.depname) AS c - FROM empsalary e1 LEFT JOIN empsalary e2 ON TRUE - WHERE e1.empno = e2.empno) s -WHERE s.c = 1; - QUERY PLAN ---------------------------------------------------------- - Subquery Scan on s - Filter: (s.c = 1) - -> WindowAgg - Run Condition: (ntile(e2.salary) OVER (?) <= 1) - -> Sort - Sort Key: e1.depname - -> Merge Join - Merge Cond: (e1.empno = e2.empno) - -> Sort - Sort Key: e1.empno - -> Seq Scan on empsalary e1 - -> Sort - Sort Key: e2.empno - -> Seq Scan on empsalary e2 -(14 rows) - -- Tests to ensure we don't push down the run condition when it's not valid to -- do so. -- Ensure we don't push down when the frame options show that the window @@ -3889,6 +3863,42 @@ 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) + +-- As above but with ntile(). +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT empno, + salary, + ntile(empno::int) OVER (ORDER BY empno DESC) nt + FROM empsalary) emp +WHERE nt = 1; + QUERY PLAN +---------------------------------------------- + Subquery Scan on emp + Filter: (emp.nt = 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 |