| Bug #1754 | CAST and DateTime comparison problem in SELECT WHERE | ||
|---|---|---|---|
| Submitted: | 4 Nov 2003 23:27 | Modified: | 5 Nov 2003 5:06 |
| Reporter: | André Lehmann | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.0.16 | OS: | Windows (Windows XP) |
| Assigned to: | CPU Architecture: | Any | |
[5 Nov 2003 3:32]
André Lehmann
[OS corrected]
[5 Nov 2003 5:06]
Alexander Keremidarski
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php This is docummented behaviour of CAST function in 4.0, changed in 4.1 http://www.mysql.com/doc/en/Cast_Functions.html ... NOTE: In MysQL 4.0 the CAST() to DATE, DATETIME, or TIME only marks the column to be a specific type but doesn't change the value of the column. select version(), @d1:=cast("2000-1-1 12:30:0" as datetime) as d1, @d2:=cast("2000-01-01 12:30:00" as datetime) as d2, @d1 = @d2; +------------------+------------------+---------------------+-----------+ | version() | d1 | d2 | @d1 = @d2 | +------------------+------------------+---------------------+-----------+ | 4.0.17-debug-log | 2000-1-1 12:30:0 | 2000-01-01 12:30:00 | 0 | +------------------+------------------+---------------------+-----------+ select version(), @d1:=cast("2000-1-1 12:30:0" as datetime) as d1, @d2:=cast("2000-01-01 12:30:00" as datetime) as d2, @d1 = @d2; +-----------------------+---------------------+---------------------+-----------+ | version() | d1 | d2 | @d1 = @d2 | +-----------------------+---------------------+---------------------+-----------+ | 4.1.1-alpha-debug-log | 2000-01-01 12:30:00 | 2000-01-01 12:30:00 | 1 | +-----------------------+---------------------+---------------------+-----------+
Description: When comparing a timedate and the result of a cast into datetime in a WHERE statement, the result is not correct. How to repeat: Here is a transcript of a test case: C:\mysql\bin>mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.0.15-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create database testdt; Query OK, 1 row affected (0.00 sec) mysql> use testdt Database changed mysql> create table wdate ( ref_date datetime); Query OK, 0 rows affected (0.31 sec) mysql> insert into wdate values ('2000-1-1 12:30:0'); Query OK, 1 row affected (0.01 sec) mysql> create table tdate ( field1 float, an int, mois int, jour int, heure int, minute int); Query OK, 0 rows affected (0.03 sec) mysql> insert into tdate values (NULL, 2000,1,1,12,30); Query OK, 1 row affected (0.01 sec) mysql> update tdate, wdate set field1 = 1 where wdate.ref_date = cast(concat(an, '-',mois,'-',jour,' ',heure,':',minute,':0') as datetime); Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> Obviously (I think), field1 should have been updated.