MySQL Extract BUG??

[Don Crossman]

Assume a MYSQL table, foo.

One column, bar datetime.

Two rows:
2004-01-01 08:00:00
2004-02-01 08:00:00

select * from foo where extract(day from bar)=1;
2 rows in set...

select * from foo where extract(month from bar)=2;
1 row in set...

select * from foo where extract(month from bar)=1 && extract(day from
bar)=1;
1 row in set...

select * from foo where extract(month from bar)=2 && extract(day from
bar)=1;
Empty set... SHOULD BE 1 ROW!!!

MySQL version 4.0.13, running on Windows 2000.

Am I doing something incredibly stupid, or does this just make no sense?

Thanks,
Don

[Andy Hassall]

Re: MySQL Extract BUG??

On Mon, 05 Jan 2004 17:44:23 -0000, Don Crossman <dcrossman@nosp am.email.com>
wrote:
[color=blue]
>Assume a MYSQL table, foo.
>
>One column, bar datetime.
>
>Two rows:
> 2004-01-01 08:00:00
> 2004-02-01 08:00:00
>
>select * from foo where extract(day from bar)=1;
> 2 rows in set...
>
>select * from foo where extract(month from bar)=2;
> 1 row in set...
>
>select * from foo where extract(month from bar)=1 && extract(day from
>bar)=1;
> 1 row in set...
>
>select * from foo where extract(month from bar)=2 && extract(day from
>bar)=1;
> Empty set... SHOULD BE 1 ROW!!!
>
>MySQL version 4.0.13, running on Windows 2000.
>
>Am I doing something incredibly stupid, or does this just make no sense?[/color]

mysql> select * from foo;
+---------------------+
| bar |
+---------------------+
| 2004-01-01 08:00:00 |
| 2004-02-01 08:00:00 |
+---------------------+
2 rows in set (0.00 sec)

mysql> select * from foo where extract(day from bar)=1;
+---------------------+
| bar |
+---------------------+
| 2004-01-01 08:00:00 |
| 2004-02-01 08:00:00 |
+---------------------+
2 rows in set (0.00 sec)

mysql> select * from foo where extract(month from bar)=2;
+---------------------+
| bar |
+---------------------+
| 2004-02-01 08:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from foo where extract(month from bar)=1
-> and extract(day from bar)=1;
+---------------------+
| bar |
+---------------------+
| 2004-01-01 08:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from foo where extract(month from bar)=2
-> and extract(day from bar)=1;
+---------------------+
| bar |
+---------------------+
| 2004-02-01 08:00:00 |
+---------------------+
1 row in set (0.00 sec)

4.0.16, Linux.

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Pedro Graca]

Re: MySQL Extract BUG??

Don Crossman wrote:[color=blue]
> Two rows:
> 2004-01-01 08:00:00
> 2004-02-01 08:00:00[/color]
[color=blue]
> select * from foo where extract(month from bar)=2 && extract(day from
> bar)=1;
> Empty set... SHOULD BE 1 ROW!!![/color]
[color=blue]
> MySQL version 4.0.13, running on Windows 2000.[/color]

Works fine for me.

$ mysql --version
mysql Ver 12.22 Distrib 4.0.16, for pc-linux-gnu (i686)


Have you tried
: select extract(month from bar), extract(day from bar) from foo;
: select * from foo where (extract(month from bar)=2) && (extract(day from bar)=1);
--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--

[Don Crossman]

Re: MySQL Extract BUG??

Andy Hassall <andy@andyh.co. uk> wrote in
news:32djvvsgbp tfmnngj1so5es1l heuvevlh1@4ax.c om:
[color=blue]
> On Mon, 05 Jan 2004 17:44:23 -0000, Don Crossman
> <dcrossman@nosp am.email.com> wrote:
>[color=green]
>>Assume a MYSQL table, foo.
>>
>>One column, bar datetime.
>>
>>Two rows:
>> 2004-01-01 08:00:00
>> 2004-02-01 08:00:00
>>
>>select * from foo where extract(day from bar)=1;
>> 2 rows in set...
>>
>>select * from foo where extract(month from bar)=2;
>> 1 row in set...
>>
>>select * from foo where extract(month from bar)=1 && extract(day from
>>bar)=1;
>> 1 row in set...
>>
>>select * from foo where extract(month from bar)=2 && extract(day from
>>bar)=1;
>> Empty set... SHOULD BE 1 ROW!!!
>>
>>MySQL version 4.0.13, running on Windows 2000.
>>
>>Am I doing something incredibly stupid, or does this just make no
>>sense?[/color]
>
> mysql> select * from foo;
> +---------------------+
>| bar |
> +---------------------+
>| 2004-01-01 08:00:00 |
>| 2004-02-01 08:00:00 |
> +---------------------+
> 2 rows in set (0.00 sec)
>
> mysql> select * from foo where extract(day from bar)=1;
> +---------------------+
>| bar |
> +---------------------+
>| 2004-01-01 08:00:00 |
>| 2004-02-01 08:00:00 |
> +---------------------+
> 2 rows in set (0.00 sec)
>
> mysql> select * from foo where extract(month from bar)=2;
> +---------------------+
>| bar |
> +---------------------+
>| 2004-02-01 08:00:00 |
> +---------------------+
> 1 row in set (0.00 sec)
>
> mysql> select * from foo where extract(month from bar)=1
> -> and extract(day from bar)=1;
> +---------------------+
>| bar |
> +---------------------+
>| 2004-01-01 08:00:00 |
> +---------------------+
> 1 row in set (0.00 sec)
>
> mysql> select * from foo where extract(month from bar)=2
> -> and extract(day from bar)=1;
> +---------------------+
>| bar |
> +---------------------+
>| 2004-02-01 08:00:00 |
> +---------------------+
> 1 row in set (0.00 sec)
>
> 4.0.16, Linux.
>[/color]

It looks like I'm off to the upgrade farm. Either that, or it's a Windows
bug!

Thank you, gentlemen.

[Don Crossman]

Re: MySQL Extract BUG??

Don Crossman <dcrossman@nosp am.email.com> wrote in
news:Xns946848A 3D8EEdcrossmann ospamemail@216. 168.3.44:
[color=blue]
> Andy Hassall <andy@andyh.co. uk> wrote in
> news:32djvvsgbp tfmnngj1so5es1l heuvevlh1@4ax.c om:
>[color=green]
>> On Mon, 05 Jan 2004 17:44:23 -0000, Don Crossman
>> <dcrossman@nosp am.email.com> wrote:
>>[color=darkred]
>>>Assume a MYSQL table, foo.
>>>
>>>One column, bar datetime.
>>>
>>>Two rows:
>>> 2004-01-01 08:00:00
>>> 2004-02-01 08:00:00
>>>
>>>select * from foo where extract(day from bar)=1;
>>> 2 rows in set...
>>>
>>>select * from foo where extract(month from bar)=2;
>>> 1 row in set...
>>>
>>>select * from foo where extract(month from bar)=1 && extract(day from
>>>bar)=1;
>>> 1 row in set...
>>>
>>>select * from foo where extract(month from bar)=2 && extract(day from
>>>bar)=1;
>>> Empty set... SHOULD BE 1 ROW!!!
>>>
>>>MySQL version 4.0.13, running on Windows 2000.
>>>
>>>Am I doing something incredibly stupid, or does this just make no
>>>sense?[/color]
>>
>> mysql> select * from foo;
>> +---------------------+
>>| bar |
>> +---------------------+
>>| 2004-01-01 08:00:00 |
>>| 2004-02-01 08:00:00 |
>> +---------------------+
>> 2 rows in set (0.00 sec)
>>
>> mysql> select * from foo where extract(day from bar)=1;
>> +---------------------+
>>| bar |
>> +---------------------+
>>| 2004-01-01 08:00:00 |
>>| 2004-02-01 08:00:00 |
>> +---------------------+
>> 2 rows in set (0.00 sec)
>>
>> mysql> select * from foo where extract(month from bar)=2;
>> +---------------------+
>>| bar |
>> +---------------------+
>>| 2004-02-01 08:00:00 |
>> +---------------------+
>> 1 row in set (0.00 sec)
>>
>> mysql> select * from foo where extract(month from bar)=1
>> -> and extract(day from bar)=1;
>> +---------------------+
>>| bar |
>> +---------------------+
>>| 2004-01-01 08:00:00 |
>> +---------------------+
>> 1 row in set (0.00 sec)
>>
>> mysql> select * from foo where extract(month from bar)=2
>> -> and extract(day from bar)=1;
>> +---------------------+
>>| bar |
>> +---------------------+
>>| 2004-02-01 08:00:00 |
>> +---------------------+
>> 1 row in set (0.00 sec)
>>
>> 4.0.16, Linux.
>>[/color]
>
> It looks like I'm off to the upgrade farm. Either that, or it's a
> Windows bug!
>
> Thank you, gentlemen.
>[/color]

It apparently was a bug in the Windows version of 4.0.13. I just upgraded
to 4.0.17, and it all worked fine!