Bug #45226 extra rows returned for queries using IN,GROUP BY and HAVING clauses
Submitted: 31 May 2009 22:42 Modified: 24 Oct 2009 8:44
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, having, IN, Optimizer, RQG, subquery

[31 May 2009 22:42] Patrick Crews 
Description:
Queries of the pattern:
SELECT `pk` <alias> FROM <table> WHERE <col_list> IN (subquery) AND <condition_1> GROUP BY `pk` HAVING <alias>+<condition_2>;
are failing.

These failures are fairly rare (2 out of 100,000 queries), and the failure tends to be rows are returned in 5.4 when no rows are returned in 5.1:

# 17:04:53 Query: SELECT  GRANDPARENT1 . `pk` AS G1 FROM C AS GRANDPARENT1 WHERE ( GRANDPARENT1 . `int_nokey` , GRANDPARENT1 . `int_key` ) IN ( SELECT DISTINCT PARENT1 . `int_nokey` AS P1 , PARENT1 . `int_nokey` AS P2 FROM BB AS PARENT1 LEFT JOIN B AS PARENT2 ON ( PARENT1 . `varchar_nokey` > PARENT2 . `varchar_key` )  ORDER BY PARENT1 . `pk` ) AND ( ( GRANDPARENT1 . `int_nokey` <> 0 OR GRANDPARENT1 . `varchar_nokey` < 't' ) AND GRANDPARENT1 . `int_key` <= 0 ) GROUP BY GRANDPARENT1 . `pk` HAVING G1 = 4  failed: result length mismatch between servers (0 vs. 2)
--- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen3502-server0.dump        2009-05-31 17:04:53.000000000 -0400
+++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen3502-server1.dump        2009-05-31 17:04:53.000000000 -0400
@@ -0,0 +1,2 @@
+18
+20
# 17:04:58 Simplified query:  SELECT `pk` G1  FROM C  WHERE ( `int_nokey`  , `int_key`  )  IN (  SELECT `int_nokey`  , `int_nokey`  )  AND  0  OR `varchar_nokey`  < 't' AND `int_key`  <=  0  GROUP  BY `pk`  HAVING G1  =  4

Can provide the other example I have as needed.

How to repeat:
Run the Random Query Generator:
./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

You can also create a simple grammar focusing  on the failure pattern.

Suggested fix:
Ensure proper query processing.
[24 Sep 2009 8:44] Susanne Ebrecht 
Is this still repeatable for you?
[25 Sep 2009 18:08] Patrick Crews 
I cannot repeat this failure any more.

I would recommend trying to add a test case, but we don't have automated test cases from the RQG (as we can't reproduce the bug)
[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".