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:
None 
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
Triage: Triaged: D2 (Serious)

[27 Sep 2010 15:32] Dan V
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)
[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.