Hi!
>>>>> "Sinisa" == Sinisa Milivojevic <[EMAIL PROTECTED]> writes:
Sinisa> Arturs Aboltins writes:
>> From: [EMAIL PROTECTED]
>> To: [EMAIL PROTECTED]
>> Subject: MYSQL bug with if and datetime fields
>>
>> >Description:
>> In queries where datetime field is in "WHERE" condition, the same
>> field
>> in "IF" statement causes incorrect query result.
>> >How-To-Repeat:
mysql> select * from test ;
>> +------+---------------------+
>> | id | dt |
>> +------+---------------------+
>> | 1 | 2001-08-14 00:00:00 |
>> | 2 | 2001-08-15 00:00:00 |
>> | 3 | 2001-08-16 00:00:00 |
>> +------+---------------------+
>> 3 rows in set (0.00 sec)
>>
mysql> select * from test where dt='2001-08-14 00:00:00' and dt =
>> if(id=1,'2001-08-14 00:00:00','1999-08-15');
>> Empty set (0.95 sec)
>>
Sinisa> Please try the above with 3.23.41. I think that it was fixed
Sinisa> meanwhile.
<cut>
The reason the above is failing is that the WHERE is optimized to:
t1.dt = 20010814000000 and 20010814000000 = if((t1.id = 1),'2001-08-14
00:00:00','1999-08-15')
Because 'if' returns a string in this case, the later query doesn't
match :(
I will try to disable this with optimization for datetime in the
future.
In the mean time, you can fid this by either doing:
select * from test where dt=20010814000000 and
dt=if(id=1,20010814000000,19990815000000);
or:
select * from test where dt='2001-08-14 00:00:00' and concat(dt) =
if(id=1,'2001-08-14 00:00:00','1999-08-15');
The extra concat() will disable this optimization.
Regards,
Monty
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
