[MDEV-2522] LP:993459 - Execution of PS for a query with GROUP BY returns wrong result Created: 2012-05-02  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Igor Babaev Assignee: Oleksandr Byelkin
Resolution: Won't Fix Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug993459.xml    

 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)

 Comments   
Comment by Oleksandr Byelkin [ 2012-05-16 ]

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.

Comment by Rasmus Johansson (Inactive) [ 2012-05-17 ]

Launchpad bug id: 993459

Generated at Thu Feb 08 06:42:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.