Two EXTRACT conditions in WHERE cause no results

Description:
MySQL returns no rows when I include two EXTRACT() conditions in a
WHERE clause. Single EXTRACT() conditions work fine.

How-To-Repeat:

CREATE DATABASE TestDb;
USE TestDb;
CREATE TABLE ServerStatistics(
StatID INTEGER,
StatTime DATETIME
);
INSERT INTO ServerStatistics(StatID, StatTime) VALUES(1, '2002-12-15');
INSERT INTO ServerStatistics(StatID, StatTime) VALUES(2, '2003-05-30');
INSERT INTO ServerStatistics(StatID, StatTime) VALUES(3, '2003-06-10');

SELECT *
FROM ServerStatistics SS
WHERE EXTRACT(MONTH FROM SS.StatTime)=EXTRACT(MONTH FROM '2003-6-12');

SELECT *
FROM ServerStatistics SS
WHERE EXTRACT(YEAR FROM SS.StatTime)=EXTRACT(YEAR FROM '2003-6-12');

SELECT *
FROM ServerStatistics SS
WHERE EXTRACT(YEAR FROM SS.StatTime)=EXTRACT(YEAR FROM '2003-6-12')
AND EXTRACT(MONTH FROM SS.StatTime)=EXTRACT(MONTH FROM '2003-6-12');

The first two queries return rows 3, and 2+3 as expected. The last query,
however, should return row 3, but instead it returns an empty set.


Synopsis: Two EXTRACT conditions in WHERE cause no results
MySQL support: none
Severity: serious
Category: mysqld
Class: sw-bug
Release: 4.0.12-nt

Exectutable: mysqld-nt
Environment: Pentium 4 2.4 GHz, 512 MB RAM
System: WinXP Professional
Compiler: -
Architecture: i

--

Mikko Noromaa (mikkon@nm-sol.com) - tel. +358 40 7348034
Noromaa Solutions - see http://www.nm-sol.com/



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Two EXTRACT conditions in WHERE cause no results

Hi!

On Jun 12, Mikko Noromaa wrote:
> Description:
> MySQL returns no rows when I include two EXTRACT() conditions in a
> WHERE clause. Single EXTRACT() conditions work fine.

Thank you for the bugreport.
The bug is fixed in the MySQL-4.0.14.

Regards,
Sergei

--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: Two EXTRACT conditions in WHERE cause no results

Hi,

EXTRACT seems to be broken in many ways. You might want to check that the
following works with the new fixed MySQL-4.0.14 version. Any idea when that
version will be out?


CREATE DATABASE TestDb;
USE TestDb;
CREATE TABLE ServerStatistics(
StatID INTEGER,
StatTime DATETIME
);
INSERT INTO ServerStatistics(StatID, StatTime) VALUES(1, '2002-12-15');
INSERT INTO ServerStatistics(StatID, StatTime) VALUES(2, '2003-05-30');
INSERT INTO ServerStatistics(StatID, StatTime) VALUES(3, '2003-06-10');

SELECT EXTRACT(DAY FROM SS.StatTime)
FROM ServerStatistics SS
WHERE EXTRACT(YEAR_MONTH FROM SS.StatTime)=EXTRACT(YEAR_MONTH FROM
'2003-6-12')
GROUP BY EXTRACT(DAY FROM SS.StatTime);

DROP DATABASE TestDb;


This query should return two rows (10 and 20), but it returns only 10. If
you remove the WHERE clause, it returns 10,15,30 as expected.

--

Mikko Noromaa (mikkon@nm-sol.com) - tel. +358 40 7348034
Noromaa Solutions - see http://www.nm-sol.com/


> -----Original Message-----
> From: Sergei Golubchik [mailto:serg@mysql.com]
> Sent: Thursday, June 12, 2003 7:16 PM
> To: Mikko Noromaa
> Cc: bugs@lists.mysql.com
> Subject: Re: Two EXTRACT conditions in WHERE cause no results
>
>
> Hi!
>
> On Jun 12, Mikko Noromaa wrote:
> > Description:
> > MySQL returns no rows when I include two EXTRACT()
> conditions in a
> > WHERE clause. Single EXTRACT() conditions work fine.
>
> Thank you for the bugreport.
> The bug is fixed in the MySQL-4.0.14.
>
> Regards,
> Sergei
>
> --
> MySQL Development Team
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Sergei Golubchik
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
> /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
> <___/
>


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Two EXTRACT conditions in WHERE cause no results

Hi!

On Jun 12, Mikko Noromaa wrote:
> Hi,
>
> EXTRACT seems to be broken in many ways. You might want to check that the
> following works with the new fixed MySQL-4.0.14 version.
>
> CREATE DATABASE TestDb;
> USE TestDb;
> CREATE TABLE ServerStatistics(StatID INTEGER, StatTime DATETIME);
> INSERT INTO ServerStatistics VALUES(1, '2002-12-15');
> INSERT INTO ServerStatistics VALUES(2, '2003-05-30');
> INSERT INTO ServerStatistics VALUES(3, '2003-06-10');
>
> SELECT EXTRACT(DAY FROM SS.StatTime) FROM ServerStatistics SS
> WHERE EXTRACT(YEAR_MONTH FROM SS.StatTime)=EXTRACT(YEAR_MONTH FROM
> '2003-6-12') GROUP BY EXTRACT(DAY FROM SS.StatTime);
>
> This query should return two rows (10 and 20), but it returns only 10. If
> you remove the WHERE clause, it returns 10,15,30 as expected.

Why should it return two rows ?
Condition

EXTRACT(YEAR_MONTH FROM SS.StatTime)=EXTRACT(YEAR_MONTH FROM '2003-6-12')

matches only one row '2003-06-10'. So, 10 is the only correct result.

Regards,
Sergei

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

RE: Two EXTRACT conditions in WHERE cause no results

> Why should it return two rows ?
> Condition
>
> EXTRACT(YEAR_MONTH FROM SS.StatTime)=EXTRACT(YEAR_MONTH FROM
> '2003-6-12')
>
> matches only one row '2003-06-10'. So, 10 is the only correct result.

True. Perhaps I had an extra row with StatTime as '2003-06-20' in my test
table. Anyway, I've worked around this issue a long time ago.

--

Mikko Noromaa (mikkon@nm-sol.com) - tel. +358 40 7348034
Noromaa Solutions - see http://www.nm-sol.com/


> -----Original Message-----
> From: Sergei Golubchik [mailto:serg@mysql.com]
> Sent: Sunday, June 29, 2003 4:23 PM
> To: Mikko Noromaa
> Cc: bugs@lists.mysql.com
> Subject: Re: Two EXTRACT conditions in WHERE cause no results
>
>
> Hi!
>
> On Jun 12, Mikko Noromaa wrote:
> > Hi,
> >
> > EXTRACT seems to be broken in many ways. You might want to
> check that the
> > following works with the new fixed MySQL-4.0.14 version.
> >
> > CREATE DATABASE TestDb;
> > USE TestDb;
> > CREATE TABLE ServerStatistics(StatID INTEGER,
> StatTime DATETIME);
> > INSERT INTO ServerStatistics VALUES(1, '2002-12-15');
> > INSERT INTO ServerStatistics VALUES(2, '2003-05-30');
> > INSERT INTO ServerStatistics VALUES(3, '2003-06-10');
> >
> > SELECT EXTRACT(DAY FROM SS.StatTime) FROM ServerStatistics SS
> > WHERE EXTRACT(YEAR_MONTH FROM SS.StatTime)=EXTRACT(YEAR_MONTH FROM
> > '2003-6-12') GROUP BY EXTRACT(DAY FROM SS.StatTime);
> >
> > This query should return two rows (10 and 20), but it
> returns only 10. If
> > you remove the WHERE clause, it returns 10,15,30 as expected.
>
> Why should it return two rows ?
> Condition
>
> EXTRACT(YEAR_MONTH FROM SS.StatTime)=EXTRACT(YEAR_MONTH FROM
> '2003-6-12')
>
> matches only one row '2003-06-10'. So, 10 is the only correct result.
>
> Regards,
> Sergei
>
> --
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Sergei Golubchik
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
> /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
> <___/ www.mysql.com
>


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org