| Bug #42681 | Maria returns duplicate rows with range access on 'date type | ||
|---|---|---|---|
| Submitted: | 9 Feb 2009 6:19 | Modified: | 7 May 2009 8:31 |
| Reporter: | Nidhi Shrotriya | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Maria storage engine | Severity: | S3 (Non-critical) |
| Version: | 6.0.10 | OS: | Any |
| Assigned to: | Guilhem Bichot | CPU Architecture: | Any |
[9 Feb 2009 6:19]
Nidhi Shrotriya
[9 Feb 2009 6:29]
Nidhi Shrotriya
SELECT * FROM t1; c1 c2 c3 c4 0000-00-00 0000-00-00 2008-01-04 00:00:00 2008-01-05 00:00:00 1983-09-05 1983-09-05 1983-09-06 13:28:00 1983-09-06 13:28:00 1983-09-07 1983-09-07 1983-09-08 00:00:00 1983-09-08 00:00:00 1998-12-28 1998-12-28 1998-12-28 00:00:00 1998-12-28 00:00:00 1998-12-29 1998-12-29 1998-12-29 00:00:00 1998-12-29 00:00:00 1998-12-30 1998-12-30 1998-12-30 11:30:45 1998-12-30 11:30:45 1998-12-31 1998-12-31 1998-12-31 11:30:45 1998-12-31 11:30:45 2007-05-23 2007-05-23 2007-05-24 09:15:28 2007-05-24 09:15:28 2007-05-25 2007-05-25 2007-05-26 00:00:00 2007-05-26 00:00:00 2008-01-01 NULL 2008-01-02 00:00:00 2008-01-03 00:00:00 2008-01-17 NULL NULL 2009-01-29 11:11:27 2009-01-29 2009-01-29 2009-01-29 11:11:27 2009-01-29 00:00:00 SELECT * FROM t1 WHERE c2 > '1998-12-30 11:30:45' ORDER BY c1,c2; c1 c2 c3 c4 1998-12-31 1998-12-31 1998-12-31 11:30:45 1998-12-31 11:30:45 1998-12-31 1998-12-31 1998-12-31 11:30:45 1998-12-31 11:30:45 2007-05-23 2007-05-23 2007-05-24 09:15:28 2007-05-24 09:15:28 2007-05-25 2007-05-25 2007-05-26 00:00:00 2007-05-26 00:00:00 2009-01-29 2009-01-29 2009-01-29 11:11:27 2009-01-29 00:00:00 duplicate rows.
[9 Feb 2009 7:15]
Sveta Smirnova
Thank you for the report. Verified as described.
[9 Feb 2009 7:16]
Sveta Smirnova
test case
Attachment: bug42681.test (application/octet-stream, text), 1.81 KiB.
[12 Mar 2009 9:17]
Guilhem Bichot
goes away when removing HA_DO_INDEX_COND_PUSHDOWN from ha_maria.h
[13 Mar 2009 7:53]
Nidhi Shrotriya
Test Case
Attachment: date_bug_maria_42681.test (application/octet-stream, text), 3.19 KiB.
[13 Mar 2009 14:11]
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/69152 2726 Guilhem Bichot 2009-03-13 Fix for multiple symptoms sharing the same cause: BUG#42297 Maria: crash in multi-range-read code BUG#42298 Maria: SELECT with join returns no rows BUG#42299 Maria: SELECT using cp1251-table returns no rows BUG#42681 Maria returns duplicate rows with range access on 'date type BUG#42683 Maria returns wrong results for <= NULL and <> NULL BUG#43527 Maria returns no rows on multi range access with limit clause BUG#43530 Maria has Issues with range select <>, < with -ve range values on signed index BUG#43552 Maria returned wrong rows with range access on float BUG#43620 Maria throws 'Got error 176 from storage engine' on a range query BUG#43623 Maria returns no rows with date index on range access >, >=, BETWEEN @ mysql-test/suite/maria/r/maria.result after fixing the bug, we can see one more row in the result. Ah, if we had paid attention to maria.result when we added this straight_join test, we would have caught the bug immediately. @ mysql-test/suite/maria/r/maria4.result result @ mysql-test/suite/maria/t/maria4.test test for fixed bugs. All its pieces would fail (errno 176, missing rows, too many rows) without the entire bugfix of ma_rkey.c @ storage/maria/ma_rkey.c Because of missing (), icp_res was inverted compared to the result of ma_check_index_cond(), which wasn't desired (0==0 -> 1, 1==0 -> 0). We would go to "err:" wrongly and thus pick up the value of my_errno which was left from previous functions (for example, 176 left by the ha_tina CSV log write at start of statement!); sometimes the errno would be returned to client, sometimes it would just cause a matching row to be missed. This fixed BUG#42297 BUG#42298. But was not enough for BUG#43552: - icp_res==2 was not converted to "key not found", causing non-matching rows to be returned. Now the usage of icp_res is closer to ma_rnext.c and ma_rnext_same.c.
[3 Apr 2009 14:52]
Bugs System
Pushed into 6.0.11-alpha (revid:guilhem@mysql.com-20090402210815-lu17n4kj8c73cfe8) (version source revid:guilhem@mysql.com-20090313141043-73a6mr7hsrqm3djc) (merge vers: 6.0.11-alpha) (pib:6)
[7 May 2009 8:31]
MC Brown
A note has been added to the 6.0.11 changelog: When performing SELECT queries on tables containing TIMESTAMP or DATETIME colums with indexes using a WHERE clause comparing the field value to NULL using the <= or <> operators, the wrong information would be returned.
