Bug #28632 Comparison of DATE columns - difference between 4.1 and 5.0

• Submitted: 23 May 2007 21:36
• Modified: 24 May 2007 8:59
• Reporter: Nicolas Moldavsky
• Status: Can't repeat
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.0.41
• OS: Linux
• CPU Architecture: Any
• Tags: comparison, date

[23 May 2007 21:36] Nicolas Moldavsky

Description:
When using a DATE column if you compare it to a valid literal that has hour, minute and second values, the behaviour is different between 4.1 and 5.0.  The change is not documented in the "Upgrading from 4.1 to 5.0" section.

I.e, if you compare a column that has '2007-05-15' as a value to '2007-05-15 00:00:00', in 4.1 they are equal, but in 5.0 they are not.  I tried the different SQL_MODEs to see if it would work the 4.1 way with any of them, but could not find any.  Specifically, the MYSQL40 mode does not bring the same behaviour as in 4.1.

How to repeat:
This is the behaviour in 4.1:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 120203 to server version: 4.1.22-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table a (b date not null);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into a values ('2007-05-15');
Query OK, 1 row affected (0.00 sec)

mysql> select * from a where b >='2007-05-15';
+------------+
| b          |
+------------+
| 2007-05-15 |
+------------+
1 row in set (0.00 sec)

mysql> select * from a where b >='2007-05-15 00:00:00';
+------------+
| b          |
+------------+
| 2007-05-15 |
+------------+
1 row in set (0.00 sec)

mysql> select * from a where b ='2007-05-15 00:00:00';
+------------+
| b          |
+------------+
| 2007-05-15 |
+------------+
1 row in set (0.00 sec)

--------------

This is in 5.0.41:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 83
Server version: 5.0.41-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table a (b date not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into a values ('2007-05-15');
Query OK, 1 row affected (0.00 sec)

mysql> select * from a where b >='2007-05-15';
+------------+
| b          |
+------------+
| 2007-05-15 |
+------------+
1 row in set (0.00 sec)

mysql> select * from a where b >='2007-05-15 00:00:00';
Empty set (0.00 sec)

mysql> select * from a where b ='2007-05-15 00:00:00';
Empty set (0.00 sec)

mysql> set sql_mode=MYSQL40;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from a where b ='2007-05-15 00:00:00';
Empty set (0.00 sec)

[24 May 2007 8:59] Valeriy Kravchuk

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour described with latest 5.0.44-BK:

openxs@suse:~/dbs/5.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: 5.0.44-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table a (b date not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into a values ('2007-05-15');
Query OK, 1 row affected (0.01 sec)

mysql> select * from a where b >='2007-05-15';
+------------+
| b          |
+------------+
| 2007-05-15 |
+------------+
1 row in set (0.00 sec)

mysql> select * from a where b >='2007-05-15 00:00:00';
+------------+
| b          |
+------------+
| 2007-05-15 |
+------------+
1 row in set (0.00 sec)

So, this bug is already fixed somehow.