MySQL Bugs: #75436: wrong results

Bug #75436	wrong results
Submitted:	7 Jan 2015 15:02	Modified:	31 Jan 2020 13:33
Reporter:	Richard Kojedzinszky	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5.41-log	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Wrong results returned when using between and aggregation and key on datetime column.

How to repeat:
mysql> create table dttest (ts datetime, key (ts));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into dttest values ('2014-01-01');
Query OK, 1 row affected (0.01 sec)

mysql> select min(ts), count(*) from dttest where ts between cast('2013-01-01 12:00:00' as datetime) and cast('2015-01-01 12:00:00' as datetime);
+---------+----------+
| min(ts) | count(*) |
+---------+----------+
| NULL    |        0 |
+---------+----------+
1 row in set (0.00 sec)

-- a little research with version 5.5.41

SELECT '2014-01-01' BETWEEN CAST('2013-01-01 12:00:00' AS DATETIME) AND CAST('2015-01-01 12:00:00' AS DATETIME); -- returns "1" (true)

CREATE TABLE dttest (ts DATETIME, KEY (ts));
INSERT INTO dttest VALUES ('2014-01-01');
SELECT MIN(ts), COUNT(*) FROM dttest WHERE ts BETWEEN CAST('2013-01-01 12:00:00' AS DATETIME) AND CAST('2015-01-01 12:00:00' AS DATETIME); -- as reported

-- omitting the key
CREATE TABLE dttest2 (ts DATETIME);
INSERT INTO dttest2 VALUES ('2014-01-01');
SELECT MIN(ts), COUNT(*) FROM dttest2 WHERE ts BETWEEN CAST('2013-01-01 12:00:00' AS DATETIME) AND CAST('2015-01-01 12:00:00' AS DATETIME);
/*
returns 

MIN(ts)              COUNT(*)  
-------------------  ----------
2014-01-01 00:00:00           1

*/
or
(not the first bug reported with indexes on datetime/timestamp/etc. values, I believe).

-- Peter
-- not an Oracle/MySQL person

Also omitting explicit "CAST AS DATETIME" works as expected both with and without the key.

ie.
SELECT MIN(ts), COUNT(*) FROM dttest WHERE ts BETWEEN '2013-01-01 12:00:00' AND '2015-01-01 12:00:00';

Actually, we have functions in place of cast()s which return datetimes, that is why I wrote that cast. That looks like:

select min(ts), count(*) from dttest where ts between func1(x) and func2(y);

On 5.6.22 it is OK (with and without key, with and without explicit CAST).  

I also notice that if I EXPLAIN the query, 'key_length' is reported as "6" on 5.6.22 and "9" on 5.5.41 (whatever that means.  But what is stored in the indexes is not identical. Maybe this just indicates changed InnoDB format).

5.1.71 is also OK.

5.5.41-log also buggy. But if we leave the count(*) column, we also get the expected result.

Thank you for the bug report.

C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.74-Win X64 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 > USE test
Database changed
mysql 5.1 > create table dttest (ts datetime, key (ts));
Query OK, 0 rows affected (0.06 sec)

mysql 5.1 > insert into dttest values ('2014-01-01');
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >  select min(ts), count(*) from dttest where ts between cast('2013-01-01 12:00:00' as datetime) and cast('2015-01-01 12:00:00' as datetime);
+---------------------+----------+
| min(ts)             | count(*) |
+---------------------+----------+
| 2014-01-01 00:00:00 |        1 |
+---------------------+----------+
1 row in set (0.00 sec)

mysql 5.1 > exit
Bye

C:\dbs>net start mysqld55
The MySQLD55 service is starting.
The MySQLD55 service was started successfully.

C:\dbs>55

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.42 Source distribution 2014.12.02

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > USE test
Database changed
mysql 5.5 > create table dttest (ts datetime, key (ts));
Query OK, 0 rows affected (0.08 sec)

mysql 5.5 > insert into dttest values ('2014-01-01');
Query OK, 1 row affected (0.03 sec)

mysql 5.5 >  select min(ts), count(*) from dttest where ts between cast('2013-01-01 12:00:00' as datetime) and cast('2015-01-01 12:00:00' as datetime);
+---------+----------+
| min(ts) | count(*) |
+---------+----------+
| NULL    |        0 |
+---------+----------+
1 row in set (0.00 sec)

mysql 5.5 > exit
Bye

C:\dbs>net start mysqld56
The MySQLD56 service is starting..
The MySQLD56 service was started successfully.

C:\dbs>56

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.23 Source distribution 2014.12.02

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > USE test
Database changed
mysql 5.6 > create table dttest (ts datetime, key (ts));
Query OK, 0 rows affected (0.25 sec)

mysql 5.6 > insert into dttest values ('2014-01-01');
Query OK, 1 row affected (0.01 sec)

mysql 5.6 >  select min(ts), count(*) from dttest where ts between cast('2013-01-01 12:00:00' as datetime) and cast('2015-01-01 12:00:00' as datetime);
+---------------------+----------+
| min(ts)             | count(*) |
+---------------------+----------+
| 2014-01-01 00:00:00 |        1 |
+---------------------+----------+
1 row in set (0.00 sec)

mysql 5.6 >

This was fixed in 5.6.