summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--src/test/regress/expected/aggregates.out2
-rw-r--r--src/test/regress/expected/bit.out17
-rw-r--r--src/test/regress/expected/create_procedure.out14
-rw-r--r--src/test/regress/expected/foreign_data.out29
-rw-r--r--src/test/regress/expected/horology.out4
-rw-r--r--src/test/regress/expected/identity.out72
-rw-r--r--src/test/regress/expected/index_including.out25
-rw-r--r--src/test/regress/expected/insert.out116
-rw-r--r--src/test/regress/expected/memoize.out31
-rw-r--r--src/test/regress/expected/merge.out60
-rw-r--r--src/test/regress/expected/partition_prune.out107
-rw-r--r--src/test/regress/expected/plpgsql.out8
-rw-r--r--src/test/regress/expected/rangefuncs.out16
-rw-r--r--src/test/regress/expected/rules.out8
-rw-r--r--src/test/regress/expected/stats_ext.out43
-rw-r--r--src/test/regress/expected/timestamp.out14
-rw-r--r--src/test/regress/expected/timestamptz.out14
-rw-r--r--src/test/regress/expected/triggers.out8
-rw-r--r--src/test/regress/expected/window.out94
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
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-02 15:07:49 +0000