MySQL Bugs: #43578: MyISAM&Maria gives wrong rows with range access ORDER BY DESC on date index

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

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.

Thank you for the report.

Verified as described.

In version 5.1 and earlier MyISAM returns same results as InnoDb and Falcon: 2 rows

Attached another test case for Maria giving wrong results. MyISAM is doing fine here.

Test Case

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

changed to server optimizer type as it is ICP related

cannot repeat with 6.0-codebase-bugfixing

asked Nidhi to re-test

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.

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

Nidhi couldn't repeat.

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)

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

Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)

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)