Skip to content

Commit f7a97b6

Browse files
bmomjianbmomjian
committed
Update query_id computation
Properly fix: - the "ONLY" in FROM [ONLY] isn't hashed - the agglevelsup field in GROUPING isn't hashed - WITH TIES not being hashed (new in PG 13) - "DISTINCT" in "GROUP BY [DISTINCT]" isn't hashed (new in PG 14) Reported-by: Julien Rouhaud Discussion: https://postgr.es/m/20210425081119.ulyzxqz23ueh3wuj@nol
1 parent 5df6aea commit f7a97b6

File tree

3 files changed

+207
-0
lines changed

3 files changed

+207
-0
lines changed

‎contrib/pg_stat_statements/expected/pg_stat_statements.out

Lines changed: 151 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -916,4 +916,155 @@ SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%
916916
$$ LANGUAGE plpgsql | | |
917917
(3 rows)
918918

919+
-- FROM [ONLY]
920+
CREATE TABLE tbl_inh(id integer);
921+
CREATE TABLE tbl_inh_1() INHERITS (tbl_inh);
922+
INSERT INTO tbl_inh_1 SELECT 1;
923+
SELECT * FROM tbl_inh;
924+
id
925+
----
926+
1
927+
(1 row)
928+
929+
SELECT * FROM ONLY tbl_inh;
930+
id
931+
----
932+
(0 rows)
933+
934+
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FROM%tbl_inh%';
935+
count
936+
-------
937+
2
938+
(1 row)
939+
940+
-- WITH TIES
941+
CREATE TABLE limitoption AS SELECT 0 AS val FROM generate_series(1, 10);
942+
SELECT *
943+
FROM limitoption
944+
WHERE val < 2
945+
ORDER BY val
946+
FETCH FIRST 2 ROWS WITH TIES;
947+
val
948+
-----
949+
0
950+
0
951+
0
952+
0
953+
0
954+
0
955+
0
956+
0
957+
0
958+
0
959+
(10 rows)
960+
961+
SELECT *
962+
FROM limitoption
963+
WHERE val < 2
964+
ORDER BY val
965+
FETCH FIRST 2 ROW ONLY;
966+
val
967+
-----
968+
0
969+
0
970+
(2 rows)
971+
972+
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FETCH FIRST%';
973+
count
974+
-------
975+
2
976+
(1 row)
977+
978+
-- GROUP BY [DISTINCT]
979+
SELECT a, b, c
980+
FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
981+
GROUP BY ROLLUP(a, b), rollup(a, c)
982+
ORDER BY a, b, c;
983+
a | b | c
984+
---+---+---
985+
1 | 2 | 3
986+
1 | 2 |
987+
1 | 2 |
988+
1 | | 3
989+
1 | | 3
990+
1 | |
991+
1 | |
992+
1 | |
993+
4 | | 6
994+
4 | | 6
995+
4 | | 6
996+
4 | |
997+
4 | |
998+
4 | |
999+
4 | |
1000+
4 | |
1001+
7 | 8 | 9
1002+
7 | 8 |
1003+
7 | 8 |
1004+
7 | | 9
1005+
7 | | 9
1006+
7 | |
1007+
7 | |
1008+
7 | |
1009+
| |
1010+
(25 rows)
1011+
1012+
SELECT a, b, c
1013+
FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
1014+
GROUP BY DISTINCT ROLLUP(a, b), rollup(a, c)
1015+
ORDER BY a, b, c;
1016+
a | b | c
1017+
---+---+---
1018+
1 | 2 | 3
1019+
1 | 2 |
1020+
1 | | 3
1021+
1 | |
1022+
4 | | 6
1023+
4 | | 6
1024+
4 | |
1025+
4 | |
1026+
7 | 8 | 9
1027+
7 | 8 |
1028+
7 | | 9
1029+
7 | |
1030+
| |
1031+
(13 rows)
1032+
1033+
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%GROUP BY%ROLLUP%';
1034+
count
1035+
-------
1036+
2
1037+
(1 row)
1038+
1039+
-- GROUPING SET agglevelsup
1040+
SELECT (
1041+
SELECT (
1042+
SELECT GROUPING(a,b) FROM (VALUES (1)) v2(c)
1043+
) FROM (VALUES (1,2)) v1(a,b) GROUP BY (a,b)
1044+
) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f);
1045+
grouping
1046+
----------
1047+
0
1048+
0
1049+
0
1050+
(3 rows)
1051+
1052+
SELECT (
1053+
SELECT (
1054+
SELECT GROUPING(e,f) FROM (VALUES (1)) v2(c)
1055+
) FROM (VALUES (1,2)) v1(a,b) GROUP BY (a,b)
1056+
) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f);
1057+
grouping
1058+
----------
1059+
3
1060+
0
1061+
1
1062+
(3 rows)
1063+
1064+
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
1065+
count
1066+
-------
1067+
2
1068+
(1 row)
1069+
9191070
DROP EXTENSION pg_stat_statements;

‎contrib/pg_stat_statements/sql/pg_stat_statements.sql

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -385,4 +385,56 @@ END;
385385
$$ LANGUAGE plpgsql;
386386
SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
387387

388+
-- FROM [ONLY]
389+
CREATE TABLE tbl_inh(id integer);
390+
CREATE TABLE tbl_inh_1() INHERITS (tbl_inh);
391+
INSERT INTO tbl_inh_1 SELECT 1;
392+
393+
SELECT * FROM tbl_inh;
394+
SELECT * FROM ONLY tbl_inh;
395+
396+
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FROM%tbl_inh%';
397+
398+
-- WITH TIES
399+
CREATE TABLE limitoption AS SELECT 0 AS val FROM generate_series(1, 10);
400+
SELECT *
401+
FROM limitoption
402+
WHERE val < 2
403+
ORDER BY val
404+
FETCH FIRST 2 ROWS WITH TIES;
405+
406+
SELECT *
407+
FROM limitoption
408+
WHERE val < 2
409+
ORDER BY val
410+
FETCH FIRST 2 ROW ONLY;
411+
412+
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%FETCH FIRST%';
413+
414+
-- GROUP BY [DISTINCT]
415+
SELECT a, b, c
416+
FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
417+
GROUP BY ROLLUP(a, b), rollup(a, c)
418+
ORDER BY a, b, c;
419+
SELECT a, b, c
420+
FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c)
421+
GROUP BY DISTINCT ROLLUP(a, b), rollup(a, c)
422+
ORDER BY a, b, c;
423+
424+
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%GROUP BY%ROLLUP%';
425+
426+
-- GROUPING SET agglevelsup
427+
SELECT (
428+
SELECT (
429+
SELECT GROUPING(a,b) FROM (VALUES (1)) v2(c)
430+
) FROM (VALUES (1,2)) v1(a,b) GROUP BY (a,b)
431+
) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f);
432+
SELECT (
433+
SELECT (
434+
SELECT GROUPING(e,f) FROM (VALUES (1)) v2(c)
435+
) FROM (VALUES (1,2)) v1(a,b) GROUP BY (a,b)
436+
) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f);
437+
438+
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
439+
388440
DROP EXTENSION pg_stat_statements;

‎src/backend/utils/misc/queryjumble.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -230,13 +230,15 @@ JumbleQueryInternal(JumbleState *jstate, Query *query)
230230
JumbleExpr(jstate, (Node *) query->onConflict);
231231
JumbleExpr(jstate, (Node *) query->returningList);
232232
JumbleExpr(jstate, (Node *) query->groupClause);
233+
APP_JUMB(query->groupDistinct);
233234
JumbleExpr(jstate, (Node *) query->groupingSets);
234235
JumbleExpr(jstate, query->havingQual);
235236
JumbleExpr(jstate, (Node *) query->windowClause);
236237
JumbleExpr(jstate, (Node *) query->distinctClause);
237238
JumbleExpr(jstate, (Node *) query->sortClause);
238239
JumbleExpr(jstate, query->limitOffset);
239240
JumbleExpr(jstate, query->limitCount);
241+
APP_JUMB(query->limitOption);
240242
JumbleRowMarks(jstate, query->rowMarks);
241243
JumbleExpr(jstate, query->setOperations);
242244
}
@@ -259,6 +261,7 @@ JumbleRangeTable(JumbleState *jstate, List *rtable)
259261
case RTE_RELATION:
260262
APP_JUMB(rte->relid);
261263
JumbleExpr(jstate, (Node *) rte->tablesample);
264+
APP_JUMB(rte->inh);
262265
break;
263266
case RTE_SUBQUERY:
264267
JumbleQueryInternal(jstate, rte->subquery);
@@ -399,6 +402,7 @@ JumbleExpr(JumbleState *jstate, Node *node)
399402
GroupingFunc *grpnode = (GroupingFunc *) node;
400403

401404
JumbleExpr(jstate, (Node *) grpnode->refs);
405+
APP_JUMB(grpnode->agglevelsup);
402406
}
403407
break;
404408
case T_WindowFunc:

0 commit comments

Comments
 (0)