Bug #45228 Queries with WHERE <col_1> AND <condition> GROUP BY <col_2> produce bad results
Submitted: 31 May 2009 22:56 Modified: 24 Oct 2009 8:45
Reporter: Patrick Crews Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.4 OS:Any
Assigned to: CPU Architecture:Any
Tags: GROUP BY, Optimizer, RQG, subquery

[31 May 2009 22:56] Patrick Crews 
Description:
Queries of the pattern:
SELECT <column_1> FROM TABLE WHERE <column_name> AND <condition> GROUP BY <column_2>

are returning differing results on 5.1-bugteam and 5.4.  The difference in results has been:
1)  Same number of rows, but differing by one value
2)  5.4 is missing a value returned by 5.1 (2 vs. 3 rows)

The <condition> portion can consist either of simple conditionals such as `date_key` > '2004-06-10' or more complex statements such as <column_name> AND <condition> (see below):

# 17:10:19 Query: SELECT DISTINCT GRANDPARENT1 . `varchar_nokey` AS G1 FROM C AS GRANDPARENT1 LEFT JOIN B AS GRANDPARENT2 USING ( `pk` ) WHERE GRANDPARENT1 . `int_key` IN ( SELECT DISTINCT PARENT1 . `pk` AS P1 FROM C AS PARENT1 LEFT JOIN BB AS PARENT2 ON ( PARENT1 . `pk` < PARENT2 . `pk` ) WHERE ( ( PARENT1 . `datetime_nokey` > GRANDPARENT1 . `datetime_key` ) OR NOT GRANDPARENT1 . `time_key` IS UNKNOWN )  ) AND ( GRANDPARENT1 . `pk` >= 0 AND GRANDPARENT1 . `date_key` > '2004-06-10' ) GROUP BY GRANDPARENT1 . `time_key`  ORDER BY GRANDPARENT1 . `time_key`  failed: result content mismatch between servers.
--- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen3502-server0.dump        2009-05-31 17:10:19.000000000 -0400
+++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen3502-server1.dump        2009-05-31 17:10:19.000000000 -0400
@@ -1,3 +1,3 @@
+
 d
-n
 p
# 17:10:22 Simplified query:  SELECT `varchar_nokey`  FROM C  WHERE `int_key`  AND `pk`  AND `date_key`  > '2004-06-10' GROUP  BY `time_key`

How to repeat:
Run the RQG:
./runall.pl \
  --basedir1=<path>/mysql-5.1-bugteam \
  --basedir2=<path>/mysql-azalea \
  --grammar=conf/subquery_semijoin_nested.yy \
  --threads=1 \
  --queries=1000 \
  --mysqld2=--init-file=<path>/mysql-test-extra-6.0/mysql-test/gentest/init/no_subquery.sql \
  --validator=ResultsetComparatorSimplify \
  --queries=100000

Or you should be able to create a more basic grammar focusing on the failure pattern.

Suggested fix:
Ensure this query pattern is processed correctly.
[24 Sep 2009 8:45] Susanne Ebrecht 
Is this still repeatable for you?
[25 Sep 2009 18:06] Patrick Crews 
No, it currently can't be repeated for me.

I would recommend adding a test case for this, but (being unrepeatable) we don't have the automagic of an rqg-produced test case.
[24 Oct 2009 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".