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 [at] 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 [at] m.gmane.org

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 <serg [at] mysql.com>
/ /|_/ / // /\ \/ /_/ / /__ 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 [at] m.gmane.org

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 [at] nm-sol.com) - tel. +358 40 7348034
Noromaa Solutions - see http://www.nm-sol.com/


> -----Original Message-----
> From: Sergei Golubchik [mailto:serg [at] mysql.com]
> Sent: Thursday, June 12, 2003 7:16 PM
> To: Mikko Noromaa
> Cc: bugs [at] 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 <serg [at] mysql.com>
> / /|_/ / // /\ \/ /_/ / /__ 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 [at] m.gmane.org

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 <serg [at] mysql.com>
/ /|_/ / // /\ \/ /_/ / /__ 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 [at] m.gmane.org

> 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 [at] nm-sol.com) - tel. +358 40 7348034
Noromaa Solutions - see http://www.nm-sol.com/


> -----Original Message-----
> From: Sergei Golubchik [mailto:serg [at] mysql.com]
> Sent: Sunday, June 29, 2003 4:23 PM
> To: Mikko Noromaa
> Cc: bugs [at] 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 <serg [at] mysql.com>
> / /|_/ / // /\ \/ /_/ / /__ 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 [at] m.gmane.org