MySQLdb select

[Sibylle Koczian]

It's a bug. I think it is a bug in MySQL. I'm using 4.0.18 on Debian and
an interactive session shows the problem;

"""
andy47@vetinari:~$ mysql [snip]
Database changed
mysql> select count(*) from stock_prices where price_date = '2004-07-30';
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from stock_prices where price_date = '2004-07-30
00:00:00';
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
"""

When using '=' the two forms of date are identical, but if we switch to
using 'in';

"""
mysql> select count(*) from stock_prices where price_date in
('2004-07-30');
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from stock_prices where price_date in
('2004-07-30 00:00:00');
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

mysql>
"""

Ta-da. Of course, this may have already been notified to MySQL AB, I'd
check their web site (http://www.mysql.com) or try one of their mailing
lists.

Will do. Thank you. I never thought of trying this directly in MySQL,
always took it for a problem between MySQL and Python. I'll check the
MySQL newsgroup first, as it's in German.

Koczian

 

[Sibylle Koczian]

It's a bug. I think it is a bug in MySQL. I'm using 4.0.18 on Debian
and an interactive session shows the problem;

[snip]

Ta-da. Of course, this may have already been notified to MySQL AB, I'd
check their web site (http://www.mysql.com) or try one of their
mailing lists.

Will do. Thank you. I never thought of trying this directly in MySQL,
always took it for a problem between MySQL and Python. I'll check the
MySQL newsgroup first, as it's in German.

Not really a bug, possibly no very good design decision (not new with
MySQL). From the manual:

"13.1.3 Comparison Functions and Operators
[snip]
MySQL compares values using the following rules:
[snip]
* If one of the arguments is a TIMESTAMP or DATETIME column and the
other argument is a constant, the constant is converted to a timestamp
before the comparison is performed. This is done to be more
ODBC-friendly. Note that the is not done for arguments in IN()! To be
safe, always use complete datetime/date/time string when doing comparisons."

If a date column is treated the same way, it's clear: "=" compares the
date column with a timestamp with time part 0 and gets true; IN compares
the date with a string containing '00:00:00' and gets false.

Regards,
Koczian