Execution of PS for a query with GROUP BY returns wrong result
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Won't Fix
|
High
|
Oleksandr "Sanja" Byelkin |
Bug Description
The following sequence of commands returns a wrong result in MariaDb 5.2 (but not in 5.3/5.5) :
PREPARE s1 FROM "
SELECT c1, t2.c2, count(c3)
FROM ( SELECT 3 as c2 FROM dual WHERE @x = 1
( SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
WHERE t2.c2 = t1.c2
GROUP BY c1, c2";
SET @x = 1;
SELECT c1, t2.c2, count(c3)
FROM ( SELECT 3 as c2 FROM dual WHERE @x = 1
UNION SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 ) AS t1,
( SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
WHERE t2.c2 = t1.c2
GROUP BY c1, c2;
EXECUTE s1;
SET @x = 2;
SELECT c1, t2.c2, count(c3)
FROM ( SELECT 3 as c2 FROM dual WHERE @x = 1
( SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
WHERE t2.c2 = t1.c2
GROUP BY c1, c2;
EXECUTE s1;
SET @x = 1;
SELECT c1, t2.c2, count(c3)
FROM ( SELECT 3 as c2 FROM dual WHERE @x = 1
( SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual
UNION SELECT '2012-03-01 02:00:00', 3, 2 FROM dual
UNION SELECT '2012-03-01 01:00:00', 2, 1 FROM dual ) AS t2
WHERE t2.c2 = t1.c2
GROUP BY c1, c2;
EXECUTE s1;
For the last statement we have:
MariaDB [test]> EXECUTE s1;
+------
| c1 | c2 | count(c3) |
+------
| 2012-03-01 01:00:00 | 3 | 2 |
| 2012-03-01 02:00:00 | 3 | 1 |
+------
though the expected result is:
MariaDB [test]> EXECUTE s1;
+------
| c1 | c2 | count(c3) |
+------
| 2012-03-01 01:00:00 | 2 | 1 |
| 2012-03-01 01:00:00 | 3 | 1 |
| 2012-03-01 02:00:00 | 3 | 1 |
+------
(see also bug #13805127 for mysql-5.5)
Changed in maria: | |
status: | New → Incomplete |
status: | Incomplete → Confirmed |
importance: | Undecided → High |
assignee: | nobody → Oleksandr "Sanja" Byelkin (sanja-byelkin) |
milestone: | none → 5.2 |
tags: | added: wrong-result |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Won't Fix |
We do not have problem in 5.3 because we postpone execution of constant ORDER BY/GROUP BY with subqueries but in 5.2 we have special condition in remove_const() which evaluates and removes constant expression with subqueries.
IMHO it will be better do not fix 5.2 and we have in fixed in 5.3 and up due to other order of OPTIMIZATION/ EXECUTION for subqueries.