Unexpected results when using GROUP BY GROUPING SETS and bind variables - Mailing list pgsql-general
| From | Aner Perez |
|---|---|
| Subject | Unexpected results when using GROUP BY GROUPING SETS and bind variables |
| Date | |
| Msg-id | CAP-7WhNwyiHV=CpnMisFvASJ3HFzGEC2Lavd4tA-LQu-LTY=1w@mail.gmail.com Whole thread Raw |
| Responses |
Re: Unexpected results when using GROUP BY GROUPING SETS and bind variables
|
| List | pgsql-general |
I wrote a query for a report that needed to summarize table data for different subsets of an ARRAY column. Once I had a working query, I recreated it in my code using an SQL query builder (the awesome jOOQ in this case). Unfortunately the output from the generated SQL was different from the handwritten query. Some of the grouping columns had null in them even though the original query showed the correct values.
After some help from the jOOQ users mailing list, I found that the generated query I wrote was inadvertently using bind variables for the array indexes used in the GROUPING SETS. This apparently affects how the query is executed and leads to unexpected output.
I wrote an SQL script runnable in psql to demonstrate the issue. The script creates a table with sample data and then executes 2 queries against it. The first one has everything inlined and produces the expected output. The second one uses a prepared statement and bind variables to reproduce the issue.
I'm not really sure if this is a bug in query validation or a bug in executing the query or if it's just unreasonable expectations on my part but the output of the 2 queries seems to violate the principle of least surprise.
- Aner
-- bind-group-by-test.sql --
CREATE TABLE bind_group_by (
path text [] PRIMARY KEY,
value int
);
INSERT INTO bind_group_by (path, value) VALUES
(ARRAY ['A1', 'B1', 'C1', 'D1'], 0),
(ARRAY ['A1', 'B1', 'C1', 'D2'], 1),
(ARRAY ['A1', 'B1', 'C1', 'D3'], 2),
(ARRAY ['A1', 'B1', 'C2', 'D1'], 3),
(ARRAY ['A1', 'B1', 'C2', 'D2'], 4),
(ARRAY ['A1', 'B1', 'C2', 'D3'], 5),
(ARRAY ['A1', 'B1', 'C3', 'D1'], 6),
(ARRAY ['A1', 'B1', 'C3', 'D2'], 7),
(ARRAY ['A1', 'B1', 'C3', 'D3'], 8),
(ARRAY ['A1', 'B2', 'C1', 'D1'], 9),
(ARRAY ['A1', 'B2', 'C1', 'D2'], 10),
(ARRAY ['A1', 'B2', 'C1', 'D3'], 11),
(ARRAY ['A1', 'B2', 'C2', 'D1'], 12),
(ARRAY ['A1', 'B2', 'C2', 'D2'], 13),
(ARRAY ['A1', 'B2', 'C2', 'D3'], 14),
(ARRAY ['A1', 'B2', 'C3', 'D1'], 15),
(ARRAY ['A1', 'B2', 'C3', 'D2'], 16),
(ARRAY ['A1', 'B2', 'C3', 'D3'], 17),
(ARRAY ['A1', 'B3', 'C1', 'D1'], 18),
(ARRAY ['A1', 'B3', 'C1', 'D2'], 19),
(ARRAY ['A1', 'B3', 'C1', 'D3'], 20),
(ARRAY ['A1', 'B3', 'C2', 'D1'], 21),
(ARRAY ['A1', 'B3', 'C2', 'D2'], 22),
(ARRAY ['A1', 'B3', 'C2', 'D3'], 23),
(ARRAY ['A1', 'B3', 'C3', 'D1'], 24),
(ARRAY ['A1', 'B3', 'C3', 'D2'], 25),
(ARRAY ['A1', 'B3', 'C3', 'D3'], 26),
(ARRAY ['A2', 'B1', 'C1', 'D1'], 27),
(ARRAY ['A2', 'B1', 'C1', 'D2'], 28),
(ARRAY ['A2', 'B1', 'C1', 'D3'], 29),
(ARRAY ['A2', 'B1', 'C2', 'D1'], 30),
(ARRAY ['A2', 'B1', 'C2', 'D2'], 31),
(ARRAY ['A2', 'B1', 'C2', 'D3'], 32),
(ARRAY ['A2', 'B1', 'C3', 'D1'], 33),
(ARRAY ['A2', 'B1', 'C3', 'D2'], 34),
(ARRAY ['A2', 'B1', 'C3', 'D3'], 35),
(ARRAY ['A2', 'B2', 'C1', 'D1'], 36),
(ARRAY ['A2', 'B2', 'C1', 'D2'], 37),
(ARRAY ['A2', 'B2', 'C1', 'D3'], 38),
(ARRAY ['A2', 'B2', 'C2', 'D1'], 39),
(ARRAY ['A2', 'B2', 'C2', 'D2'], 40),
(ARRAY ['A2', 'B2', 'C2', 'D3'], 41),
(ARRAY ['A2', 'B2', 'C3', 'D1'], 42),
(ARRAY ['A2', 'B2', 'C3', 'D2'], 43),
(ARRAY ['A2', 'B2', 'C3', 'D3'], 44),
(ARRAY ['A2', 'B3', 'C1', 'D1'], 45),
(ARRAY ['A2', 'B3', 'C1', 'D2'], 46),
(ARRAY ['A2', 'B3', 'C1', 'D3'], 47),
(ARRAY ['A2', 'B3', 'C2', 'D1'], 48),
(ARRAY ['A2', 'B3', 'C2', 'D2'], 49),
(ARRAY ['A2', 'B3', 'C2', 'D3'], 50),
(ARRAY ['A2', 'B3', 'C3', 'D1'], 51),
(ARRAY ['A2', 'B3', 'C3', 'D2'], 52),
(ARRAY ['A2', 'B3', 'C3', 'D3'], 53),
(ARRAY ['A3', 'B1', 'C1', 'D1'], 54),
(ARRAY ['A3', 'B1', 'C1', 'D2'], 55),
(ARRAY ['A3', 'B1', 'C1', 'D3'], 56),
(ARRAY ['A3', 'B1', 'C2', 'D1'], 57),
(ARRAY ['A3', 'B1', 'C2', 'D2'], 58),
(ARRAY ['A3', 'B1', 'C2', 'D3'], 59),
(ARRAY ['A3', 'B1', 'C3', 'D1'], 60),
(ARRAY ['A3', 'B1', 'C3', 'D2'], 61),
(ARRAY ['A3', 'B1', 'C3', 'D3'], 62),
(ARRAY ['A3', 'B2', 'C1', 'D1'], 63),
(ARRAY ['A3', 'B2', 'C1', 'D2'], 64),
(ARRAY ['A3', 'B2', 'C1', 'D3'], 65),
(ARRAY ['A3', 'B2', 'C2', 'D1'], 66),
(ARRAY ['A3', 'B2', 'C2', 'D2'], 67),
(ARRAY ['A3', 'B2', 'C2', 'D3'], 68),
(ARRAY ['A3', 'B2', 'C3', 'D1'], 69),
(ARRAY ['A3', 'B2', 'C3', 'D2'], 70),
(ARRAY ['A3', 'B2', 'C3', 'D3'], 71),
(ARRAY ['A3', 'B3', 'C1', 'D1'], 72),
(ARRAY ['A3', 'B3', 'C1', 'D2'], 73),
(ARRAY ['A3', 'B3', 'C1', 'D3'], 74),
(ARRAY ['A3', 'B3', 'C2', 'D1'], 75),
(ARRAY ['A3', 'B3', 'C2', 'D2'], 76),
(ARRAY ['A3', 'B3', 'C2', 'D3'], 77),
(ARRAY ['A3', 'B3', 'C3', 'D1'], 78),
(ARRAY ['A3', 'B3', 'C3', 'D2'], 79),
(ARRAY ['A3', 'B3', 'C3', 'D3'], 80);
SELECT 'inlined' AS query;
SELECT path[1], path[2], path[3], path, sum(value)
FROM bind_group_by
GROUP BY GROUPING SETS (
(path[1], path[2], path[3], path),
(path[1], path[2], path[3]),
(path[1], path[2]),
(path[1]),
()
)
ORDER BY 1, 2, 3, 4
;
SELECT 'prepared' AS query;
PREPARE prepared_group_by (int, int, int, int, int, int, int, int, int, int, int, int) AS
SELECT path[$1], path[$2], path[$3], path, sum(value)
FROM bind_group_by
GROUP BY GROUPING SETS (
(path[$4], path[$5], path[$6], path),
(path[$7], path[$8], path[$9]),
(path[$10], path[$11]),
(path[$12]),
()
)
ORDER BY 1, 2, 3, 4
;
EXECUTE prepared_group_by (1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 1);
-- cleanup
DEALLOCATE prepared_group_by;
DROP TABLE bind_group_by;
path text [] PRIMARY KEY,
value int
);
INSERT INTO bind_group_by (path, value) VALUES
(ARRAY ['A1', 'B1', 'C1', 'D1'], 0),
(ARRAY ['A1', 'B1', 'C1', 'D2'], 1),
(ARRAY ['A1', 'B1', 'C1', 'D3'], 2),
(ARRAY ['A1', 'B1', 'C2', 'D1'], 3),
(ARRAY ['A1', 'B1', 'C2', 'D2'], 4),
(ARRAY ['A1', 'B1', 'C2', 'D3'], 5),
(ARRAY ['A1', 'B1', 'C3', 'D1'], 6),
(ARRAY ['A1', 'B1', 'C3', 'D2'], 7),
(ARRAY ['A1', 'B1', 'C3', 'D3'], 8),
(ARRAY ['A1', 'B2', 'C1', 'D1'], 9),
(ARRAY ['A1', 'B2', 'C1', 'D2'], 10),
(ARRAY ['A1', 'B2', 'C1', 'D3'], 11),
(ARRAY ['A1', 'B2', 'C2', 'D1'], 12),
(ARRAY ['A1', 'B2', 'C2', 'D2'], 13),
(ARRAY ['A1', 'B2', 'C2', 'D3'], 14),
(ARRAY ['A1', 'B2', 'C3', 'D1'], 15),
(ARRAY ['A1', 'B2', 'C3', 'D2'], 16),
(ARRAY ['A1', 'B2', 'C3', 'D3'], 17),
(ARRAY ['A1', 'B3', 'C1', 'D1'], 18),
(ARRAY ['A1', 'B3', 'C1', 'D2'], 19),
(ARRAY ['A1', 'B3', 'C1', 'D3'], 20),
(ARRAY ['A1', 'B3', 'C2', 'D1'], 21),
(ARRAY ['A1', 'B3', 'C2', 'D2'], 22),
(ARRAY ['A1', 'B3', 'C2', 'D3'], 23),
(ARRAY ['A1', 'B3', 'C3', 'D1'], 24),
(ARRAY ['A1', 'B3', 'C3', 'D2'], 25),
(ARRAY ['A1', 'B3', 'C3', 'D3'], 26),
(ARRAY ['A2', 'B1', 'C1', 'D1'], 27),
(ARRAY ['A2', 'B1', 'C1', 'D2'], 28),
(ARRAY ['A2', 'B1', 'C1', 'D3'], 29),
(ARRAY ['A2', 'B1', 'C2', 'D1'], 30),
(ARRAY ['A2', 'B1', 'C2', 'D2'], 31),
(ARRAY ['A2', 'B1', 'C2', 'D3'], 32),
(ARRAY ['A2', 'B1', 'C3', 'D1'], 33),
(ARRAY ['A2', 'B1', 'C3', 'D2'], 34),
(ARRAY ['A2', 'B1', 'C3', 'D3'], 35),
(ARRAY ['A2', 'B2', 'C1', 'D1'], 36),
(ARRAY ['A2', 'B2', 'C1', 'D2'], 37),
(ARRAY ['A2', 'B2', 'C1', 'D3'], 38),
(ARRAY ['A2', 'B2', 'C2', 'D1'], 39),
(ARRAY ['A2', 'B2', 'C2', 'D2'], 40),
(ARRAY ['A2', 'B2', 'C2', 'D3'], 41),
(ARRAY ['A2', 'B2', 'C3', 'D1'], 42),
(ARRAY ['A2', 'B2', 'C3', 'D2'], 43),
(ARRAY ['A2', 'B2', 'C3', 'D3'], 44),
(ARRAY ['A2', 'B3', 'C1', 'D1'], 45),
(ARRAY ['A2', 'B3', 'C1', 'D2'], 46),
(ARRAY ['A2', 'B3', 'C1', 'D3'], 47),
(ARRAY ['A2', 'B3', 'C2', 'D1'], 48),
(ARRAY ['A2', 'B3', 'C2', 'D2'], 49),
(ARRAY ['A2', 'B3', 'C2', 'D3'], 50),
(ARRAY ['A2', 'B3', 'C3', 'D1'], 51),
(ARRAY ['A2', 'B3', 'C3', 'D2'], 52),
(ARRAY ['A2', 'B3', 'C3', 'D3'], 53),
(ARRAY ['A3', 'B1', 'C1', 'D1'], 54),
(ARRAY ['A3', 'B1', 'C1', 'D2'], 55),
(ARRAY ['A3', 'B1', 'C1', 'D3'], 56),
(ARRAY ['A3', 'B1', 'C2', 'D1'], 57),
(ARRAY ['A3', 'B1', 'C2', 'D2'], 58),
(ARRAY ['A3', 'B1', 'C2', 'D3'], 59),
(ARRAY ['A3', 'B1', 'C3', 'D1'], 60),
(ARRAY ['A3', 'B1', 'C3', 'D2'], 61),
(ARRAY ['A3', 'B1', 'C3', 'D3'], 62),
(ARRAY ['A3', 'B2', 'C1', 'D1'], 63),
(ARRAY ['A3', 'B2', 'C1', 'D2'], 64),
(ARRAY ['A3', 'B2', 'C1', 'D3'], 65),
(ARRAY ['A3', 'B2', 'C2', 'D1'], 66),
(ARRAY ['A3', 'B2', 'C2', 'D2'], 67),
(ARRAY ['A3', 'B2', 'C2', 'D3'], 68),
(ARRAY ['A3', 'B2', 'C3', 'D1'], 69),
(ARRAY ['A3', 'B2', 'C3', 'D2'], 70),
(ARRAY ['A3', 'B2', 'C3', 'D3'], 71),
(ARRAY ['A3', 'B3', 'C1', 'D1'], 72),
(ARRAY ['A3', 'B3', 'C1', 'D2'], 73),
(ARRAY ['A3', 'B3', 'C1', 'D3'], 74),
(ARRAY ['A3', 'B3', 'C2', 'D1'], 75),
(ARRAY ['A3', 'B3', 'C2', 'D2'], 76),
(ARRAY ['A3', 'B3', 'C2', 'D3'], 77),
(ARRAY ['A3', 'B3', 'C3', 'D1'], 78),
(ARRAY ['A3', 'B3', 'C3', 'D2'], 79),
(ARRAY ['A3', 'B3', 'C3', 'D3'], 80);
SELECT 'inlined' AS query;
SELECT path[1], path[2], path[3], path, sum(value)
FROM bind_group_by
GROUP BY GROUPING SETS (
(path[1], path[2], path[3], path),
(path[1], path[2], path[3]),
(path[1], path[2]),
(path[1]),
()
)
ORDER BY 1, 2, 3, 4
;
SELECT 'prepared' AS query;
PREPARE prepared_group_by (int, int, int, int, int, int, int, int, int, int, int, int) AS
SELECT path[$1], path[$2], path[$3], path, sum(value)
FROM bind_group_by
GROUP BY GROUPING SETS (
(path[$4], path[$5], path[$6], path),
(path[$7], path[$8], path[$9]),
(path[$10], path[$11]),
(path[$12]),
()
)
ORDER BY 1, 2, 3, 4
;
EXECUTE prepared_group_by (1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 1);
-- cleanup
DEALLOCATE prepared_group_by;
DROP TABLE bind_group_by;
pgsql-general by date: