| Bug #43101 | MyISAM&Maria gives rows for <>NULL and >NULL with LIMIT clause | ||
|---|---|---|---|
| Submitted: | 23 Feb 2009 8:21 | Modified: | 18 Nov 2009 6:45 |
| Reporter: | Nidhi Shrotriya | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 6.0.10,6.0.11-bzr | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | Contribution, index_condition_pushdown, optimizer_switch | ||
[23 Feb 2009 8:21]
Nidhi Shrotriya
[23 Feb 2009 16:48]
Valeriy Kravchuk
Thank you for the bug report. Verified just as described with latest 6.0.11 from bzr:
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` time NOT NULL,
`c2` time DEFAULT NULL,
`c3` date DEFAULT NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `c2` (`c2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2 DESC;
Empty set (0.00 sec)
mysql> SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2 DESC LIMIT 2;
+-----------+-----------+------------+
| c1 | c2 | c3 |
+-----------+-----------+------------+
| 838:59:59 | 838:59:59 | 2009-01-21 |
| 825:23:00 | 825:23:00 | 2009-01-05 |
+-----------+-----------+------------+
2 rows in set (0.00 sec)
mysql> explain SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2 DESC\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: c2
key: NULL
key_len: NULL
ref: NULL
rows: 24
Extra: Using where; Using filesort
1 row in set (0.00 sec)
mysql> explain SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2 DESC LIMIT 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: c2
key: c2
key_len: 4
ref: NULL
rows: 23
Extra: Using index condition
1 row in set (0.00 sec)
mysql> alter table t1 engine=InnoDB;
Query OK, 24 rows affected (0.40 sec)
Records: 24 Duplicates: 0 Warnings: 0
mysql> explain SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2 DESC\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: c2
key: NULL
key_len: NULL
ref: NULL
rows: 24
Extra: Using where; Using filesort
1 row in set (0.00 sec)
mysql> explain SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2 DESC LIMIT 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: c2
key: c2
key_len: 4
ref: NULL
rows: 22
Extra: Using index condition
1 row in set (0.00 sec)
mysql> SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2 DESC;
Empty set (0.01 sec)
mysql> SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2 DESC LIMIT 2;
Empty set (0.00 sec)
mysql> select version();
+--------------------+
| version() |
+--------------------+
| 6.0.11-alpha-debug |
+--------------------+
1 row in set (0.00 sec)
[23 Feb 2009 16:49]
Valeriy Kravchuk
Script to load test data
Attachment: 43101.sql (application/octet-stream, text), 1.39 KiB.
[23 Feb 2009 16:52]
Valeriy Kravchuk
5.1.33 is not affected, so this is a regression.
[16 Mar 2009 12:15]
Nidhi Shrotriya
Same happens with Maria.
[19 May 2009 12:23]
Guilhem Bichot
Note that the bug goes away if removing HA_DO_INDEX_COND_PUSHDOWN from MyISAM's (and probably Maria, haven't tested) ha_myisam::index_flags. Which is why this is 6.0-specific: it looks like a bug related to Index Condition Pushdown.
[19 May 2009 12:31]
Guilhem Bichot
To sum up the bug's symptom: with MyISAM SELECT * FROM t1 WHERE c2 <> NULL ORDER BY c2 DESC LIMIT 2; #bad c1 c2 c3 838:59:59 838:59:59 2009-01-21 825:23:00 825:23:00 2009-01-05 SELECT * FROM t1 WHERE c2 <> NULL; #correct c1 c2 c3 (anything <> NULL is always false, so there should never be rows).
[10 Jun 2009 9:31]
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/75989 3351 Narayanan V 2009-06-10 Bug#43101 MyISAM&Maria gives rows for <>NULL and >NULL with LIMIT clause MyIsam gives rows with <> NULL and > NULL. Anything <> NULL is always false, so there should never be rows. This was happening because during a reverse index scan MyIsam did not apply the pushed index condition. The current patch modifies the reverse index scan code apply the pushed index condition. @ mysql-test/r/subselect3.result Bug#43101 MyISAM&Maria gives rows for <>NULL and >NULL with LIMIT clause After this change the test case now gives the correct number of rows during both the forward and the reverse scans. @ storage/myisam/mi_rprev.c Bug#43101 MyISAM&Maria gives rows for <>NULL and >NULL with LIMIT clause The reverse index scan code has been modified to apply the pushed index condition the same way this is happening during forward scans.
[15 Jun 2009 9:49]
V Venkateswaran
A solution for this bug was given by Sergey Petrunia, and the patch for this solution has already been committed http://lists.mysql.com/commits/75989
[8 Jul 2009 15:01]
Tomas Ulin
changed to server optimizer type as it is ICP related
[8 Oct 2009 12:26]
Guilhem Bichot
updating tags as per a previous post of mine, bug goes away when removing HA_DO_INDEX_COND_PUSHDOWN from MyISAM's ha_myisam::index_flags
[18 Nov 2009 6:45]
Jørgen Løland
Duplicate of BUG#43618. Bug cause: Pushed conditions were not checked when indexes were scanned backwards.
