Hello.
This is a bug:
http://bugs.mysql.com/bug.php?id=16249
Leo wrote:
> Hi All,
>
> I got a problem with a query that involved datetime field.
> the table structure goes something like this :
>
> CREATE TABLE `price_log` ( `Item`
> char(20) NOT NULL default '', `Started`
> datetime NOT NULL default '0000-00-00 00:00:00', `Price` decimal(16,3)
> NOT NULL default '0.000', PRIMARY KEY
> (`Item`,`Started`) ) ENGINE=MyISAM;
> insert into price_log values
> ("A1","2005-11-01 08:00:00",1000),
> ("A1","2005-11-15 00:00:00",2000),
> ("A1","2005-12-12 08:00:00",3000),
> ("A2","2005-12-01 08:00:00",1000);
>
> when i execute this query :
>
> select *
> from price_log
> where
> item like "A%"
> and started<="2005-12-01 24:00:00";
>
> it will return this result set :
> Item Started Price ------ ------------------- --------
> A1 2005-11-01 08:00:00 1000.000
> A1 2005-11-15 00:00:00 2000.000
> A2 2005-12-01 08:00:00 1000.000
>
>
> but, when i limited to certain item, like this :
>
> select *
> from price_log
> where
> item="A1"
> and started<="2005-12-01 24:00:00";
>
> it return an empty set.
>
> What is going wrong?
> I know it should return 2 rows, as the previous sql result set got two
> rows of item 'A1'.
>
> is it because the value of "2005-12-01 24:00:00" which ofcourse is not a
> valid date time value.
> but anyhow, the first query was succeded ??
>
> im using mysql 4.1.11, 4.1.15, 4.0.1 .. and the result were all the same.
>
> Thanks for any comment.
>
>
> -
> Leo
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
