| Bug #28336 | Date comparison changed, not mentioned in manual | ||
|---|---|---|---|
| Submitted: | 9 May 2007 16:35 | Modified: | 19 Dec 2007 13:13 |
| Reporter: | Tonci Grgin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S2 (Serious) |
| Version: | 5.0.38 up | OS: | Any |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
| Tags: | regression | ||
[16 May 2007 18:01]
x bill
I am seeing the same behavior on
Mandriva Linux Cooker.
MySQL version PRE-5.0.41 would discard/ignore
the extra characters on the DATE datatype.
5.0.41 and later seem to use them.
Here is another test case:
create table payment ( caldate date );
insert into payment( caldate ) values ('2007-05-16 13:11:11');
MySQL PRE- 5.0.41 :
mysql> select * from payment where
-> payment.caldate >= '2007-05-16 00:00:00' AND
-> payment.caldate <= '2007-05-16 00:00:00';
+------------+
| caldate |
+------------+
| 2007-05-16 |
+------------+
1 row in set (0.00 sec)
MySQL 5.0.41 and later:
mysql> select * from payment where
-> payment.caldate >= '2007-05-16 00:00:00' AND
-> payment.caldate <= '2007-05-16 00:00:00';
Empty set (0.00 sec)
This change breaks a lot of things in our application.
[18 May 2007 18:46]
Mike Benoit
This also breaks a lot of things in our application too. Please fix it ASAP!
[5 Jun 2007 13:59]
Evgeny Potemkin
mysql> create table asd ( x date );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into asd values ('2007-02-12');
Query OK, 1 row affected (0.00 sec)
mysql> insert into asd values ('20070213');
Query OK, 1 row affected (0.00 sec)
mysql> select * from asd where x = '20070212';
+------------+
| x |
+------------+
| 2007-02-12 |
+------------+
1 row in set (0.00 sec)
mysql> select * from asd where x = '20070212000000';
+------------+
| x |
+------------+
| 2007-02-12 |
+------------+
1 row in set (0.00 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.44-debug |
+--------------+
1 row in set (0.00 sec)
[5 Jun 2007 14:01]
Evgeny Potemkin
The very same server:
mysql> create table payment ( caldate date );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into payment( caldate ) values ('2007-05-16 13:11:11');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from payment where
-> payment.caldate >= '2007-05-16 00:00:00' AND
-> payment.caldate <= '2007-05-16 00:00:00';
+------------+
| caldate |
+------------+
| 2007-05-16 |
+------------+
1 row in set (0.00 sec)
[8 Jun 2007 20:23]
Jakub Wasielewski
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 194459
Server version: 5.0.40 Gentoo Linux mysql-5.0.40
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create table asd ( x date );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into asd values ('2007-02-12');
Query OK, 1 row affected (0.00 sec)
mysql> insert into asd values ('20070213');
Query OK, 1 row affected (0.00 sec)
mysql> select * from asd where x = '20070212';
+------------+
| x |
+------------+
| 2007-02-12 |
+------------+
1 row in set (0.00 sec)
mysql> select * from asd where x = '20070212000000';
Empty set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.40 |
+-----------+
1 row in set (0.00 sec)
So, in which version it was fixed?
[19 Dec 2007 13:13]
Tonci Grgin
Not repeatable with 5.0.54BK on XP Pro SP2 localhost:
--
Server version: 5.0.54-max-nt-log Source distribution
--
mysql> drop table if exists asd;
Query OK, 0 rows affected (0.01 sec)
mysql> create table asd ( x date );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into asd values ('2007-02-12');
Query OK, 1 row affected (0.00 sec)
mysql> insert into asd values ('20070213');
Query OK, 1 row affected (0.00 sec)
mysql> select * from asd where x = '20070212';
+------------+
| x |
+------------+
| 2007-02-12 |
+------------+
1 row in set (0.00 sec)
mysql> select * from asd where x = '20070212000000';
+------------+
| x |
+------------+
| 2007-02-12 |
+------------+
1 row in set (0.02 sec)
Description: Servers before 5.0.38 return 2 rows, 5.0.38 and up return 1 for same test case. SQL modes are NULL on all servers used. How to repeat: drop table if exists asd; create table asd ( x date ); insert into asd values ('2007-02-12'); insert into asd values ('20070213'); select * from asd where x = '20070212'; select * from asd where x = '20070212000000'; 1) Server version: 5.0.34 Source distribution mysql> select * from asd where x = '20070212'; +------------+ | x | +------------+ | 2007-02-12 | +------------+ 1 row in set (0.00 sec) mysql> select * from asd where x = '20070212000000'; +------------+ | x | +------------+ | 2007-02-12 | +------------+ 1 row in set (0.00 sec) Same in 5.0.36 & 5.0.37 --------- 2) Server version: 5.0.38-log Source distribution mysql> select * from asd where x = '20070212'; +------------+ | x | +------------+ | 2007-02-12 | +------------+ 1 row in set (0.02 sec) mysql> select * from asd where x = '20070212000000'; Empty set (0.00 sec) ----------------------- Server version: 5.0.40-nt Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select * from asd where x = '20070212'; +------------+ | x | +------------+ | 2007-02-12 | +------------+ 1 row in set (0.00 sec) mysql> select * from asd where x = '20070212000000'; Empty set (0.00 sec) Tested also with 5.0.42BK on Suse Linux, same bug as in Windows version.