public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: Tom Lane <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: Richard Guo <[email protected]>
Subject: Re: JumbleQuery ma treat different GROUP BY expr as the same
Date: Mon, 12 Jan 2026 16:20:44 +0800
Message-ID: <CACJufxFb2AjY5CS4iCOompuVjriRxUddkGCTdPRdpEHjG5tD=g@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CACJufxEy2W+tCqC7XuJ94r3ivWsM=onKJp94kRFx3hoARjBeFQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
hi.
while working on it, I guess I found another bug, below JumbleQuery will return
the same result:
SELECT FROM (VALUES (1::INT, 2::INT)) AS t(a, b) ORDER BY a, b;
SELECT a FROM (VALUES (1::INT, 2::INT)) AS t(a, b) ORDER BY a, b;
SELECT a, b FROM (VALUES (1::INT, 2::INT)) AS t(a, b) ORDER BY a, b;
so I think TargetEntry.resjunk should not be marked as query_jumble_ignore.
addRangeTableEntryForGroup
will make RangeTblEntry(RTE_GROUP) have a newly copied original groupexprs.
Query->targetList also has the original groupexprs.
but after parseCheckAggregates->substitute_grouped_columns.
Query->targetList Var node will point to the offset of the RTE_GROUP,
not the RTE_RELATION.
see src/backend/parser/parse_agg.c line 1543.
After parseCheckAggregates, JumbleQuery(Query->targetList) will produce the same
result as long as the grouping columns have the same list of data types.
JumbleQuery(Query->groupClause) will also produce the same result
as long as the grouping columns have the same list of data types.
Since only the RangeTblEntry(RTE_GROUP) have the original grouping expressions,
we can not mark the RangeTblEntry->groupexprs as query_jumble_ignore.
looking at, transformUpdateTargetList->transformTargetList
so i think it's OK to remove query_jumble_ignore from TargetEntry.resjunk for
INSERT/UPDATE/DELETE.
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v1-0001-Remove-query_jumble_ignore-from-the-fields-in-RangeTblEntry-and-T.patch (8.6K, 2-v1-0001-Remove-query_jumble_ignore-from-the-fields-in-RangeTblEntry-and-T.patch)
download | inline diff:
From 15623f1bea4b7ed6bf778113ad627765ef2f86a9 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 12 Jan 2026 16:17:37 +0800
Subject: [PATCH v1 1/1] Remove query_jumble_ignore from the fields in
RangeTblEntry and TargetEntry
discussion: https://postgr.es/m/CACJufxEy2W+tCqC7XuJ94r3ivWsM=onKJp94kRFx3hoARjBeFQ@mail.gmail.com
---
.../pg_stat_statements/expected/select.out | 112 +++++++++++++++++-
contrib/pg_stat_statements/sql/select.sql | 18 +++
src/include/nodes/parsenodes.h | 2 +-
src/include/nodes/primnodes.h | 2 +-
4 files changed, 130 insertions(+), 4 deletions(-)
diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out
index 75c896f3885..eafc54964dc 100644
--- a/contrib/pg_stat_statements/expected/select.out
+++ b/contrib/pg_stat_statements/expected/select.out
@@ -99,6 +99,22 @@ SELECT 2 AS "int" ORDER BY 1;
2
(1 row)
+SELECT FROM (VALUES (1::INT, 2::INT)) AS t(a, b) ORDER BY a, b;
+--
+(1 row)
+
+SELECT a FROM (VALUES (1::INT, 2::INT)) AS t(a, b) ORDER BY a, b;
+ a
+---
+ 1
+(1 row)
+
+SELECT a, b FROM (VALUES (1::INT, 2::INT)) AS t(a, b) ORDER BY a, b;
+ a | b
+---+---
+ 1 | 2
+(1 row)
+
/* this comment should not appear in the output */
SELECT 'hello'
-- but this one will appear
@@ -223,6 +239,9 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
1 | 2 | SELECT $1 AS i UNION SELECT $2 ORDER BY i
1 | 1 | SELECT $1 || $2
2 | 2 | SELECT DISTINCT $1 AS "int"
+ 1 | 1 | SELECT FROM (VALUES ($1::INT, $2::INT)) AS t(a, b) ORDER BY a, b
+ 1 | 1 | SELECT a FROM (VALUES ($1::INT, $2::INT)) AS t(a, b) ORDER BY a, b
+ 1 | 1 | SELECT a, b FROM (VALUES ($1::INT, $2::INT)) AS t(a, b) ORDER BY a, b
0 | 0 | SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C"
1 | 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
1 | 2 | WITH t(f) AS ( +
@@ -230,7 +249,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "C";
| | ) +
| | SELECT f FROM t ORDER BY f
1 | 1 | select $1::jsonb ? $2
-(17 rows)
+(20 rows)
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
t
@@ -459,6 +478,95 @@ SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FETCH FIRST%';
2
(1 row)
+-- GROUP BY, HAVING, GROUPING
+SELECT COUNT(*) FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY a;
+ count
+-------
+ 1
+(1 row)
+
+SELECT COUNT(*) FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY b;
+ count
+-------
+ 1
+(1 row)
+
+SELECT COUNT(*) FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY GROUPING SETS(a, ());
+ count
+-------
+ 1
+ 1
+(2 rows)
+
+SELECT COUNT(*) FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY GROUPING SETS(b, ());
+ count
+-------
+ 1
+ 1
+(2 rows)
+
+SELECT COUNT(*) FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY a HAVING a = 1;
+ count
+-------
+ 1
+(1 row)
+
+SELECT COUNT(*) FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY b HAVING b = 1;
+ count
+-------
+(0 rows)
+
+SELECT COUNT(*), a FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY a HAVING a = 1;
+ count | a
+-------+---
+ 1 | 1
+(1 row)
+
+SELECT COUNT(*), b FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY b HAVING b = 1;
+ count | b
+-------+---
+(0 rows)
+
+SELECT COUNT(*), a FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY a HAVING a = 1 ORDER BY a;
+ count | a
+-------+---
+ 1 | 1
+(1 row)
+
+SELECT COUNT(*), b FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY b HAVING b = 1 ORDER BY b;
+ count | b
+-------+---
+(0 rows)
+
+SELECT GROUPING(a) FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY a;
+ grouping
+----------
+ 0
+(1 row)
+
+SELECT GROUPING(b) FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY b;
+ grouping
+----------
+ 0
+(1 row)
+
+SELECT calls, query FROM pg_stat_statements WHERE query LIKE '%GROUP BY%' ORDER BY query COLLATE "C";
+ calls | query
+-------+----------------------------------------------------------------------------------------------------
+ 1 | SELECT COUNT(*) FROM (VALUES ($1::INT, $2::INT)) AS t(a, b) GROUP BY GROUPING SETS(a, ())
+ 1 | SELECT COUNT(*) FROM (VALUES ($1::INT, $2::INT)) AS t(a, b) GROUP BY GROUPING SETS(b, ())
+ 1 | SELECT COUNT(*) FROM (VALUES ($1::INT, $2::INT)) AS t(a, b) GROUP BY a
+ 1 | SELECT COUNT(*) FROM (VALUES ($1::INT, $2::INT)) AS t(a, b) GROUP BY a HAVING a = $3
+ 1 | SELECT COUNT(*) FROM (VALUES ($1::INT, $2::INT)) AS t(a, b) GROUP BY b
+ 1 | SELECT COUNT(*) FROM (VALUES ($1::INT, $2::INT)) AS t(a, b) GROUP BY b HAVING b = $3
+ 1 | SELECT COUNT(*), a FROM (VALUES ($1::INT, $2::INT)) AS t(a, b) GROUP BY a HAVING a = $3
+ 1 | SELECT COUNT(*), a FROM (VALUES ($1::INT, $2::INT)) AS t(a, b) GROUP BY a HAVING a = $3 ORDER BY a
+ 1 | SELECT COUNT(*), b FROM (VALUES ($1::INT, $2::INT)) AS t(a, b) GROUP BY b HAVING b = $3
+ 1 | SELECT COUNT(*), b FROM (VALUES ($1::INT, $2::INT)) AS t(a, b) GROUP BY b HAVING b = $3 ORDER BY b
+ 1 | SELECT GROUPING(a) FROM (VALUES ($1::INT, $2::INT)) AS t(a, b) GROUP BY a
+ 1 | SELECT GROUPING(b) FROM (VALUES ($1::INT, $2::INT)) AS t(a, b) GROUP BY b
+(12 rows)
+
-- GROUP BY [DISTINCT]
SELECT a, b, c
FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
@@ -548,7 +656,7 @@ SELECT (
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
count
-------
- 2
+ 4
(1 row)
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
diff --git a/contrib/pg_stat_statements/sql/select.sql b/contrib/pg_stat_statements/sql/select.sql
index 11662cde08c..33e0e63cd1e 100644
--- a/contrib/pg_stat_statements/sql/select.sql
+++ b/contrib/pg_stat_statements/sql/select.sql
@@ -31,6 +31,9 @@ SELECT DISTINCT 1 AS "int";
SELECT DISTINCT 2 AS "int";
SELECT 1 AS "int" ORDER BY 1;
SELECT 2 AS "int" ORDER BY 1;
+SELECT FROM (VALUES (1::INT, 2::INT)) AS t(a, b) ORDER BY a, b;
+SELECT a FROM (VALUES (1::INT, 2::INT)) AS t(a, b) ORDER BY a, b;
+SELECT a, b FROM (VALUES (1::INT, 2::INT)) AS t(a, b) ORDER BY a, b;
/* this comment should not appear in the output */
SELECT 'hello'
@@ -158,6 +161,21 @@ FETCH FIRST 2 ROW ONLY;
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FETCH FIRST%';
+-- GROUP BY, HAVING, GROUPING
+SELECT COUNT(*) FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY a;
+SELECT COUNT(*) FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY b;
+SELECT COUNT(*) FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY GROUPING SETS(a, ());
+SELECT COUNT(*) FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY GROUPING SETS(b, ());
+SELECT COUNT(*) FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY a HAVING a = 1;
+SELECT COUNT(*) FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY b HAVING b = 1;
+SELECT COUNT(*), a FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY a HAVING a = 1;
+SELECT COUNT(*), b FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY b HAVING b = 1;
+SELECT COUNT(*), a FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY a HAVING a = 1 ORDER BY a;
+SELECT COUNT(*), b FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY b HAVING b = 1 ORDER BY b;
+SELECT GROUPING(a) FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY a;
+SELECT GROUPING(b) FROM (VALUES (1::INT, 2::INT)) AS t(a, b) GROUP BY b;
+SELECT calls, query FROM pg_stat_statements WHERE query LIKE '%GROUP BY%' ORDER BY query COLLATE "C";
+
-- GROUP BY [DISTINCT]
SELECT a, b, c
FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index aac4bfc70d9..646d6ced763 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1294,7 +1294,7 @@ typedef struct RangeTblEntry
* Fields valid for a GROUP RTE (else NIL):
*/
/* list of grouping expressions */
- List *groupexprs pg_node_attr(query_jumble_ignore);
+ List *groupexprs;
/*
* Fields valid in all RTEs:
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 5211cadc258..174ebe92939 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -2248,7 +2248,7 @@ typedef struct TargetEntry
/* column's number in source table */
AttrNumber resorigcol pg_node_attr(query_jumble_ignore);
/* set to true to eliminate the attribute from final target list */
- bool resjunk pg_node_attr(query_jumble_ignore);
+ bool resjunk;
} TargetEntry;
--
2.34.1
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: JumbleQuery ma treat different GROUP BY expr as the same
In-Reply-To: <CACJufxFb2AjY5CS4iCOompuVjriRxUddkGCTdPRdpEHjG5tD=g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox