Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
None
-
None
-
None
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)