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

[Igor Babaev]

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)

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

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.