Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Won't Fix
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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
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
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";
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
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;
SET @x = 2;
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
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;
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
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)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 993459
Re: Execution of PS for a query with GROUP BY returns wrong result
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.