| Bug #9321 | col IN (<datetime>) does not work like col = <datetime> | ||
|---|---|---|---|
| Submitted: | 21 Mar 2005 22:42 | Modified: | 11 Jun 2005 2:21 |
| Reporter: | Nicolas Moldavsky | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.10a | OS: | Linux (RHEL 3) |
| Assigned to: | CPU Architecture: | Any | |
[23 May 2005 13:50]
Michael Widenius
This is in a sence not a bug, but it something that should be documented better. (We mention something regarding this in the section 'Problems using DATE Columns', but not enough) The reason for this is that when you compare a date, time, timestamp or datetime to constant string with <, <=, =, >=, > or BETWEEN MySQL converts the string to an internal long integer for faster comparision (and also for a bit more 'relaxed' string checking). This conversion is not done in the following cases: - When you compare two columns - When you compare a date/time/timestamp/datetime column to an expression - When you use any other comparison method than the above (like IN or STRCMP) In these cases the comparison is done by converting the objects to strings and doing a string comparison. In other words, to keep things safe assume strings are compared and strings and use the appropriate string functions if you want to compare a datetime to a string.
[11 Jun 2005 2:21]
Paul DuBois
I've added Monty's description to: http://dev.mysql.com/doc/mysql/en/using-date.html
Description: When using datetime columns, col = <datetime value> does not work exactly like col IN (<datetime value>). For example, the expression col = '2005-01-01' matches a row with the value '2005-01-01 00:00:00'. However, col in ('2005-01-01') does not. After reading the section on the IN operand I assume that this is not the specified behaviour. That is to say, col IN (constant) should be equal to col = constant. Tested it on one Xeon system using RHEL, MySQL 4.1.10a and also on a Pentium4 running 4.1.8. How to repeat: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17525 to server version: 4.1.10a-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create temporary table a (b datetime not null); Query OK, 0 rows affected (0.02 sec) mysql> insert into a values ('2005-03-21'); Query OK, 1 row affected (0.00 sec) mysql> select * From a where b='2005-03-21'; +---------------------+ | b | +---------------------+ | 2005-03-21 00:00:00 | +---------------------+ 1 row in set (0.00 sec) mysql> select * From a where b in ('2005-03-21'); Empty set (0.00 sec) mysql> select * From a where b in ('2005-03-21 00:00'); Empty set (0.00 sec) mysql> select * From a where b in ('2005-03-21 00:00:00'); +---------------------+ | b | +---------------------+ | 2005-03-21 00:00:00 | +---------------------+ 1 row in set (0.00 sec)