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]

On Mon, 05 Jan 2004 17:44:23 -0000, Don Crossman <dc*******@nospam.email.com>
wrote:

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?

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 (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Pedro Graca]

Don Crossman wrote:

Two rows:
2004-01-01 08:00:00
2004-02-01 08:00:00 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.

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]

Andy Hassall <an**@andyh.co.uk> wrote in
news:32********************************@4ax.com:

On Mon, 05 Jan 2004 17:44:23 -0000, Don Crossman
<dc*******@nospam.email.com> wrote:

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?

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.

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

Thank you, gentlemen.

[Don Crossman]

Don Crossman <dc*******@nospam.email.com> wrote in
news:Xn*********************************@216.168.3 .44:

Andy Hassall <an**@andyh.co.uk> wrote in
news:32********************************@4ax.com:

On Mon, 05 Jan 2004 17:44:23 -0000, Don Crossman
<dc*******@nospam.email.com> wrote:

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?

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.

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

Thank you, gentlemen.

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!