| Bug #57039 | queries using subtime in the where condition returns incorrect data | ||
|---|---|---|---|
| Submitted: | 27 Sep 2010 15:32 | Modified: | 16 Nov 2010 2:34 |
| Reporter: | Dan V | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S2 (Serious) |
| Version: | 5.5.5-m3-log, 5.5.7-bzr | OS: | Any (10.04) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
| Tags: | regression, subtime | ||
[27 Sep 2010 15:45]
Dan V
Modified subject to remove confusing double negative
[27 Sep 2010 16:57]
Valeriy Kravchuk
Verified just as described with current mysql-5.5 from bzr on Mac OS X:
macbook-pro:5.5 openxs$ 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: 5.5.7-rc-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `date_test` (
-> `date_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `date_date` datetime NOT NULL,
-> PRIMARY KEY (`date_id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.41 sec)
mysql> insert into date_test(date_id, date_date) values (1, '2008-01-03 00:00:00');
Query OK, 1 row affected (0.00 sec)
mysql> select * from date_test where date_date >= subtime(now(), "00:30:00");
+---------+---------------------+
| date_id | date_date |
+---------+---------------------+
| 1 | 2008-01-03 00:00:00 |
+---------+---------------------+
1 row in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2010-09-27 19:54:02 |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from date_test where date_date >= subtime('2010-09-27 19:54:02', "00:30:00");
Empty set (0.00 sec)
MySQL 5.1.x is NOT affected, so this is a regression bug:
macbook-pro:5.1 openxs$ 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 3
Server version: 5.1.52-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `date_test` (
-> `date_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `date_date` datetime NOT NULL,
-> PRIMARY KEY (`date_id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql> insert into date_test(date_id, date_date) values (1, '2008-01-03 00:00:00');
Query OK, 1 row affected (0.00 sec)
mysql> select * from date_test where date_date >= subtime(now(), "00:30:00");
Empty set (0.01 sec)
[27 Sep 2010 17:55]
Dan V
I should note that this bug was also not present in 5.4.3-log.
[28 Sep 2010 13:45]
Øystein Grøvlen
See also Bug#52824
[29 Sep 2010 14:28]
Øystein Grøvlen
A bzrfind search shows that this bug was introduced by: revno: 2875.54.17 revision-id: epotemkin@mysql.com-20091201212551-amf9utmatlvsbbar parent: epotemkin@mysql.com-20091201194139-lidh5pyyrbenqjb2 committer: Evgeny Potemkin <epotemkin@mysql.com> branch nick: 33546-bug-next-mr-bugfixing timestamp: Wed 2009-12-02 00:25:51 +0300 message: Bug#33546: Slowdown on re-evaluation of constant expressions. Constant expressions in WHERE/HAVING/ON clauses aren't cached and evaluated for each row. This causes slowdown of query execution especially if constant UDF/SP function are used. Now WHERE/HAVING/ON expressions are analyzed in the top-bottom direction with help of the compile function. When analyzer meets a constant item it sets a flag for the tree transformer to cache the item and doesn't allow tree walker to go deeper. Thus, the topmost item of a constant expression if cached. This is done after all other optimizations were applied to WHERE/HAVING/ON expressions A helper function called cache_const_exprs is added to the JOIN class. It calls compile method with caching analyzer and transformer on WHERE, HAVING, ON expressions if they're present. The cache_const_expr_analyzer and cache_const_expr_transformer functions are added to the Item class. The first one check if the item can be cached and the second caches it if so. A new Item_cache_datetime class is derived from the Item_cache class. It caches both int and string values of the underlying item independently to avoid DATETIME aware int-to-string conversion. Thus it completely relies on the ability of the underlying item to correctly convert DATETIME value from int to string and vice versa.
[1 Oct 2010 9:33]
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/119606 3210 Evgeny Potemkin 2010-10-01 Bug#57039: constant subtime expression returns incorrect result. The subtime function wasn't able to produce correct int representation of its result. For constant expressions the Item_datetime_cache is used to speedup evaluation and Item_datetime_cache expects underlying item to return correct int representation of DATETIME value. These two factors combined led to a wrong query result. Now the Item_func_add_time has function val_datetime which performs the calculation and saves result into given MYSQL_TIME struct, it also sets null_value to appropriate value. val_int and val_str member functions convert the result obtained from val_datetime to int or string respectively and returns it. @ mysql-test/r/func_time.result Added a test case for the bug#57039. @ mysql-test/t/func_time.test Added a test case for the bug#57039. @ sql/item_timefunc.cc Bug#57039: constant subtime expression returns incorrect result. Now the Item_func_add_time has function val_datetime which performs the calculation and saves result into given MYSQL_TIME struct, it also sets null_value to appropriate value. val_int and val_str member functions convert the result obtained from val_datetime to int or string respectively and returns it. @ sql/item_timefunc.h Bug#57039: constant subtime expression returns incorrect result.
[7 Oct 2010 7:09]
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/120190 3210 Evgeny Potemkin 2010-10-07 Bug#57039: constant subtime expression returns incorrect result. The subtime function wasn't able to produce correct int representation of its result. For constant expressions the Item_datetime_cache is used to speedup evaluation and Item_datetime_cache expects underlying item to return correct int representation of DATETIME value. These two factors combined led to a wrong query result. Now the Item_func_add_time has function val_datetime which performs the calculation and saves result into given MYSQL_TIME struct, it also sets null_value to appropriate value. val_int and val_str member functions convert the result obtained from val_datetime to int or string respectively and returns it. @ mysql-test/r/func_time.result Added a test case for the bug#57039. @ mysql-test/t/func_time.test Added a test case for the bug#57039. @ sql/item_timefunc.cc Bug#57039: constant subtime expression returns incorrect result. Now the Item_func_add_time has function val_datetime which performs the calculation and saves result into given MYSQL_TIME struct, it also sets null_value to appropriate value. val_int and val_str member functions convert the result obtained from val_datetime to int or string respectively and returns it. @ sql/item_timefunc.h Bug#57039: constant subtime expression returns incorrect result.
[9 Nov 2010 19:44]
Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)
[13 Nov 2010 16:17]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:34]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[16 Nov 2010 2:35]
Paul DuBois
Noted in 5.5.7 changelog. Constant SUBTIME() expressions could return incorrect results.
Description: Using subtime as a where constraint doesn't return expected results. See test case. How to repeat: CREATE TABLE `date_test` ( `date_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `date_date` datetime NOT NULL, PRIMARY KEY (`date_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; insert into date_test(date_id, date_date) values (1, '2008-01-03 00:00:00'); /*This is REALLY wrong*/ mysql> select * from date_test where date_date >= subtime(now(), "00:30:00"); +---------+---------------------+ | date_id | date_date | +---------+---------------------+ | 1 | 2008-01-03 00:00:00 | +---------+---------------------+ 1 row in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2010-09-27 15:27:07 | +---------------------+ 1 row in set (0.00 sec) mysql> select * from date_test where date_date >= subtime('2010-09-27 15:27:07', "00:30:00"); Empty set (0.00 sec) mysql> select subtime('2010-09-27 15:27:07', "00:30:00"); +--------------------------------------------+ | subtime('2010-09-27 15:27:07', "00:30:00") | +--------------------------------------------+ | 2010-09-27 14:57:07 | +--------------------------------------------+ 1 row in set (0.00 sec)