Execution of PS for a query with GROUP BY returns wrong result

Bug #993459 reported by Igor Babaev on 2012-05-02
6
This bug affects 1 person
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
                   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)

Changed in maria:
status: New → Incomplete
status: Incomplete → Confirmed
importance: Undecided → High
assignee: nobody → Oleksandr "Sanja" Byelkin (sanja-byelkin)
milestone: none → 5.2
Elena Stepanova (elenst) on 2012-05-10
tags: added: wrong-result
Changed in maria:
status: Confirmed → In Progress

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.

Changed in maria:
status: In Progress → Won't Fix
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers