Bug #43578 MyISAM&Maria gives wrong rows with range access ORDER BY DESC on date index
Submitted: 12 Mar 2009 9:33 Modified: 9 Oct 2009 19:33
Reporter: Nidhi Shrotriya Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:6.0.11-bzr,5.4 OS:Any
Assigned to: CPU Architecture:Any
Tags: engine_condition_pushdown, ICP, index_condition_pushdown, optimizer_switch
Triage: Triaged: D2 (Serious)

[12 Mar 2009 9:33] Nidhi Shrotriya
Description:
MyISAM, Maria:
--------------------------
SELECT ADDTIME(NOW(),'1 01:01:01') as test_time;
test_time
2009-02-26 13:21:14
SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
c1      c2      c3
2009-02-28      2009-02-28      6
2009-02-27      2009-02-27      5
2009-02-26      2009-02-26      4

Innodb, Falcon:
----------------------
SELECT ADDTIME(NOW(),'1 01:01:01') as test_time;
test_time
2009-02-26 13:21:14
SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;
c1      c2      c3
2009-02-28      2009-02-28      6
2009-02-27      2009-02-27      5

I guess Innodb and Falcon are doing right here and 
2009-02-26      2009-02-26      4

shouldn't have been returned.

How to repeat:
CREATE TABLE t1(c1 DATE NOT NULL PRIMARY KEY, c2 DATE NULL, c3 INT, INDEX idx2(c2));
SET TIMESTAMP=1235553613; #'2009-02-25'
INSERT INTO t1 VALUES(NOW(),NOW(),3),(ADDTIME(NOW(),'1 01:01:01'),ADDTIME(NOW(),'1 01:01:01'),4),(ADDTIME(NOW(),'2 01:01:01'),ADDTIME(NOW(),'2 01:01:01'),5),(ADDTIME(NOW(),'3 01:01:01'),ADDTIME(NOW(),'3 01:01:01'),6);
SELECT * FROM t1 WHERE c1 > ADDTIME(NOW(),'1 01:01:01') ORDER BY c1 DESC;

Run the above set of commands with different engines.
[12 Mar 2009 10:34] Sveta Smirnova
Thank you for the report.

Verified as described.

In version 5.1 and earlier MyISAM returns same results as InnoDb and Falcon: 2 rows
[13 Mar 2009 7:35] Nidhi Shrotriya
Attached another test case for Maria giving wrong results. MyISAM is doing fine here.
[13 Mar 2009 7:35] Nidhi Shrotriya
Test Case

Attachment: date_bug_maria_43578.test (application/octet-stream, text), 1.93 KiB.

[8 Jul 2009 15:02] Tomas Ulin
changed to server optimizer type as it is ICP related
[8 Oct 2009 12:57] Guilhem Bichot
cannot repeat with 6.0-codebase-bugfixing
[8 Oct 2009 14:35] Guilhem Bichot
asked Nidhi to re-test
[9 Oct 2009 19:33] Guilhem Bichot
This was fixed by revid:mattias.jonsson@sun.com-20090828115631-9zjv27kc3xmgvd5x.
The changes introduced by this revision are visible with
bzr diff -c revid:mattias.jonsson@sun.com-20090828115631-9zjv27kc3xmgvd5x
And the revisions composing this merge revision are visible with
bzr log -r revid:mattias.jonsson@sun.com-20090828115631-9zjv27kc3xmgvd5x
This mentions the fixes for BUG#46362 and BUG#20577, which are about the TO_DAYS() function and modified opt_range.cc and item_timefunc.cc, so could explain why the present bug#43578, about dates, was fixed.
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
[19 Oct 2009 8:18] Guilhem Bichot
Nidhi couldn't repeat.
[31 Oct 2009 8:20] 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:33] 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:04] 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)