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

[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)

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.