| Bug #29052 | DATE columns handle strings and numbers differently in WHERE clause | ||
|---|---|---|---|
| Submitted: | 12 Jun 2007 14:59 | Modified: | 12 Jun 2007 17:17 |
| Reporter: | Craig Holmquist | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.0.41 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[12 Jun 2007 15:07]
MySQL Verification Team
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
c:\build\5.0>bin\mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.44-nt Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE DATABASE testdate;
Query OK, 1 row affected (0.00 sec)
mysql> USE testdate;
Database changed
mysql> CREATE TABLE testdate (d DATE NOT NULL);
Query OK, 0 rows affected (0.13 sec)
mysql> INSERT INTO testdate VALUES ('2000-01-01');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM testdate WHERE d = 20000101000000;
+------------+
| d |
+------------+
| 2000-01-01 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM testdate WHERE d = '20000101000000';
+------------+
| d |
+------------+
| 2000-01-01 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM testdate WHERE d = '2000-01-01 00:00:00';
+------------+
| d |
+------------+
| 2000-01-01 |
+------------+
1 row in set (0.00 sec)
mysql>
[12 Jun 2007 17:17]
Craig Holmquist
Okay, this issue is probably the same as #21103 (which is patched in 5.0.44).
Description: Run the following SQL on MySQL 5.0.41. CREATE DATABASE testdate; USE testdate; CREATE TABLE testdate (d DATE NOT NULL); INSERT INTO testdate VALUES ('2000-01-01'); SELECT * FROM testdate WHERE d = 20000101000000; SELECT * FROM testdate WHERE d = '20000101000000'; SELECT * FROM testdate WHERE d = '2000-01-01 00:00:00'; The first SELECT returns the row; the other two don't. I've determined: 1. This only affects DATE columns, not DATETIME or TIME columns. 2. It comes up when string includes a "time" portion. 3. The time portion can be included when inserting or updating the row without any ill effects (for example, "INSERT INTO testdate VALUES ('20000101000000');" is equivalent to the above INSERT statement). 4. The ALLOW_INVALID_DATES mode doesn't affect this. 5. This bug does NOT occur in MySQL 4.0.16. How to repeat: See above. Suggested fix: Handle the strings 'YYYYMMDDhhmmss' and 'YYYY-MM-DD hh:mm:ss' the same as the number YYYYMMDDhhmmss.