Bug #45928 Differing query results depending on MRR and engine_condition_pushdown settings
Submitted: 3 Jul 2009 0:41 Modified: 8 Oct 2009 20:15
Reporter: Patrick Crews Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.4 OS:Any
Assigned to: CPU Architecture:Any
Tags: engine_condition_pushdown, ICP, mrr, optimizer_switch

[3 Jul 2009 0:41] Patrick Crews 
Description:
The following query produces differing results when run against two azalea servers - 
Both with the following optimizer_switch values set to off:
SET GLOBAL OPTIMIZER_SWITCH = 'materialization=off,semijoin=off,loosescan=off,firstmatch=off';
And the following variable settings:
# Server 0 : SET SESSION optimizer_use_mrr = 'force';
# Server 1 : SET SESSION optimizer_use_mrr = 'disable';

# Server 0 : SET SESSION engine_condition_pushdown = 'ON';
# Server 1 : SET SESSION engine_condition_pushdown = 0;

Failing query:
SELECT `time_nokey` G1  FROM BB  WHERE ( `varchar_nokey`  , `varchar_key`  )  IN (  SELECT `varchar_nokey`  , `varchar_nokey`  )  AND `varchar_key`  >= 'c' HAVING G1  ORDER  BY `pk`   ;

Diff:
@@ -1 +0,0 @@
-00:00:00

How to repeat:
Use the following test case:

--disable_warnings
DROP TABLE IF EXISTS BB;
--enable_warnings

CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `time_nokey` time NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,'00:00:00','i','i'),(11,'00:00:00','','');

# both servers
SET GLOBAL OPTIMIZER_SWITCH = 'materialization=off,semijoin=off,loosescan=off,firstmatch=off';

# Server 0 : SET SESSION optimizer_use_mrr = 'force';
# Server 1 : SET SESSION optimizer_use_mrr = 'disable';

# Server 0 : SET SESSION engine_condition_pushdown = 'ON';
# Server 1 : SET SESSION engine_condition_pushdown = 0;

 SELECT `time_nokey` G1  FROM BB  WHERE ( `varchar_nokey`  , `varchar_key`  )  IN (  SELECT `varchar_nokey`  , `varchar_nokey`  )  AND `varchar_key`  >= 'c' HAVING G1  ORDER  BY `pk`   ;

/*
Diff:

--- /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen65165-1246556033-server0.dump	2009-07-02 13:33:53.000000000 -0400
+++ /var/folders/Pt/PtJd7NDTGgyOk3+iDGXrQk+++TI/-Tmp-///randgen65165-1246556033-server1.dump	2009-07-02 13:33:53.000000000 -0400
@@ -1 +0,0 @@
-00:00:00

*/
DROP TABLE BB;

Suggested fix:
Ensure that these variable settings can't create different result sets
[8 Oct 2009 13:24] Guilhem Bichot 
cannot repeat with 6.0-codebase-bugfixing
[8 Oct 2009 14:35] Guilhem Bichot 
asked Patrick to re-test
[8 Oct 2009 20:15] Guilhem Bichot 
Patrick could not repeat it today.
[9 Oct 2009 19:38] Guilhem Bichot 
this was fixed by
epotemkin@mysql.com-20090903125059-z195j43fd6nvdws2
which was about
  Bug#45227 "Lost HAVING clause led to a wrong result.
  The per-table condition is stored in the select_cond variable for each table.
  When index condition pushdown is used ..."
We see the present bug is about HAVING, extra rows, index condition pushdown: makes sense.
I'll add a testcase for this bug.
[15 Oct 2009 9:00] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86904

3654 Guilhem Bichot	2009-10-15
      Testcases for those "can't repeat" bugs:
      BUG#45928 "Differing query results depending on MRR and engine_condition_pushdown settings"
      BUG#46077 "wrong result: HAVING + ORDER BY + MyISAM + ICP returns extra rows"
      both fixed by epotemkin@mysql.com-20090903125059-z195j43fd6nvdws2
      BUG#43578 "MyISAM&Maria gives wrong rows with range access ORDER BY DESC on date index"
      fixed by mattias.jonsson@sun.com-20090828115631-9zjv27kc3xmgvd5x
[31 Oct 2009 8:18] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091031081410-qkxmjsdzjmj840aq) (version source revid:guilhem@mysql.com-20091015085142-7zs7mouolfiy49fz) (merge vers: 6.0.14-alpha) (pib:13)
[14 May 2010 11:16] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/108312

3174 Guilhem Bichot	2010-05-14
      Backporting of guilhem@mysql.com-20091015085142-7zs7mouolfiy49fz :
        Testcases for those "can't repeat" bugs:
        BUG#45928 "Differing query results depending on MRR and engine_condition_pushdown settings"
        BUG#46077 "wrong result: HAVING + ORDER BY + MyISAM + ICP returns extra rows"
        both fixed by epotemkin@mysql.com-20090903125059-z195j43fd6nvdws2
        BUG#43578 "MyISAM&Maria gives wrong rows with range access ORDER BY DESC on date index"
        fixed by mattias.jonsson@sun.com-20090828115631-9zjv27kc3xmgvd5x
[16 Aug 2010 6:31] Bugs System 
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:21] Bugs System 
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)