| Bug #43249 | Innodb returns zero time for the time column with <=> NULL order by limit | ||
|---|---|---|---|
| Submitted: | 27 Feb 2009 7:05 | Modified: | 18 Dec 2009 12:15 |
| 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, 6.0-codebase | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | Contribution, index_condition_pushdown, optimizer_switch | ||
[27 Feb 2009 7:42]
Valeriy Kravchuk
Thank you for the bug report. Verified just as described with recent 6.0.11 from bzr:
openxs@suse:/home2/openxs/dbs/6.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.11-alpha Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE t1(c1 TIME NOT NULL, c2 TIME NULL, c3 DATE, PRIMARY KEY(c1), UNIQUE INDEX(c2)) engine=innodb;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO t1 VALUES('8:29:45',NULL,'2009-02-01');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2;
+----------+----------+------------+
| c1 | c2 | c3 |
+----------+----------+------------+
| 08:29:45 | 00:00:00 | 2009-02-01 |
+----------+----------+------------+
1 row in set (0.03 sec)
mysql> explain SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: c2
key: c2
key_len: 4
ref: const
rows: 1
Extra: Using index condition
1 row in set (0.00 sec)
mysql> alter table t1 engine=MyISAM;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2;
+----------+------+------------+
| c1 | c2 | c3 |
+----------+------+------------+
| 08:29:45 | NULL | 2009-02-01 |
+----------+------+------------+
1 row in set (0.00 sec)
mysql> explain SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: system
possible_keys: c2
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
1 row in set (0.00 sec)
There is no such problem in 5.x, so this is a regression bug.
[12 Mar 2009 13:17]
Valeriy Kravchuk
Bug #43589 was marked as a duplicate of this one.
[22 Jul 2009 7:58]
Philip Stoev
This appears to be an InnoDB ICP bug. No longer repeatable in Azalea after ICP for Innodb has been disabled.
[8 Oct 2009 12:48]
Guilhem Bichot
I cannot reproduce it, even after re-enabling DS-MRR and ICP in InnoDB (i.e. undoing http://lists.mysql.com/commits/76684 ). When we focus on ICP/MRR bugs we will retest this again.
[10 Oct 2009 12:44]
Guilhem Bichot
sent Nidhi instructions about how to re-test this.
[13 Oct 2009 14:59]
Guilhem Bichot
As in the email sent to Nidhi on Oct 10:
=============================
I cannot repeat this bug, could you please re-test it?
The bug depended on Index Condition Pushdown which is currently disabled in InnoDB, so you will have to take some extra steps to test this bug:
- take the *latest* mysql-6.0-codebase-bugfixing from bk-internal (**)
- build a *debug* binary
- prefix your queries with this SQL SET statement:
set session debug="+d,optimizer_innodb_icp";
which will re-enable Index Condition Pushdown in InnoDB.
Please let me know your results.
[13 Oct 2009 15:00]
Guilhem Bichot
Nidhi, when you have provided feedback, please re-assign to Evgeny.
[15 Oct 2009 10:58]
Nidhi Shrotriya
I am still able to reproduce it with mysql-6.0-codebase-bugfixing revno: 3654.
mysql> set session debug="+d,optimizer_innodb_icp";
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%debug%';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| debug | d,optimizer_innodb_icp |
| debug_sync | OFF |
+---------------+------------------------+
2 rows in set (0.01 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t1(c1 TIME NOT NULL, c2 TIME NULL, c3 DATE, PRIMARY KEY(c1), UNIQUE INDEX(c2)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t1 VALUES('8:29:45',NULL,'2009-02-01');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2;
+----------+----------+------------+
| c1 | c2 | c3 |
+----------+----------+------------+
| 08:29:45 | 00:00:00 | 2009-02-01 |
+----------+----------+------------+
1 row in set (0.00 sec)
[15 Oct 2009 11:10]
Nidhi Shrotriya
Strange, as you do no. of times :) in the order as below. mysql> SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2; +----------+----------+------------+ | c1 | c2 | c3 | +----------+----------+------------+ | 08:29:45 | 00:00:00 | 2009-02-01 | +----------+----------+------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2; +----------+------+------------+ | c1 | c2 | c3 | +----------+------+------------+ | 08:29:45 | NULL | 2009-02-01 | +----------+------+------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2; +----------+----------+------------+ | c1 | c2 | c3 | +----------+----------+------------+ | 08:29:45 | 00:00:00 | 2009-02-01 | +----------+----------+------------+ 1 row in set (0.01 sec) mysql> SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2; +----------+------+------------+ | c1 | c2 | c3 | +----------+------+------------+ | 08:29:45 | NULL | 2009-02-01 | +----------+------+------------+ 1 row in set (0.00 sec)
[19 Oct 2009 8:25]
Guilhem Bichot
Thanks Nidhi for finding the magic that the query needs to be run twice. Then I can repeat it, the second query gives bad results (0 instead of NULL in c2):
--source include/have_debug.inc
--source include/have_innodb.inc
set session debug="+d,optimizer_innodb_icp";
CREATE TABLE t1(c1 TIME NOT NULL, c2 TIME NULL, c3 DATE, PRIMARY
KEY(c1), UNIQUE INDEX(c2)) engine=innodb;
INSERT INTO t1 VALUES('8:29:45',NULL,'2009-02-01');
# first time, good results:
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2;
# second time, bad results:
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2;
drop table `t1`;
The testcase in suite/optimizer_unfixed_bugs/t/bug43249.test should be updated (by me) when I have a chance.
[19 Oct 2009 20:39]
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/87376 3655 Guilhem Bichot 2009-10-19 BUG#43249 "Innodb returns zero time for the time column with <=> NULL order by limit" is now repeatable, updating its testcase.
[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-20091019122048-gcfxd4qy82ythfp2) (merge vers: 6.0.14-alpha) (pib:13)
[30 Nov 2009 20:00]
Sergey Petrunya
Note: the fix from here: http://igors-notes.blogspot.com/2009/08/in-defense-of-mrr.html (submitted under SCA at some point) fixes the problem.
[1 Dec 2009 10:46]
Sergey Petrunya
SCAs are not actually needed. BUG#37208 contains the fix, which was produced while being a MySQL LLC employee.
[18 Dec 2009 12:15]
Jørgen Løland
Duplicate of BUG#42580
Description: As described in How to repeat section. How to repeat: CREATE TABLE t1(c1 TIME NOT NULL, c2 TIME NULL, c3 DATE, PRIMARY KEY(c1), UNIQUE INDEX(c2)) engine=innodb; INSERT INTO t1 VALUES('8:29:45',NULL,'2009-02-01' MyISAM: ---------------- SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2; c1 c2 c3 08:29:45 NULL 2009-02-01 Innodb: ----------- SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c2 LIMIT 2; c1 c2 c3 08:29:45 00:00:00 2009-02-01 Happens only with LIMIT clause.