inconsistency with how ADDDATE works

From: Philip.Shiels@jrc.it
To: mysql@lists.mysql.com
Subject: inconsistency with how ADDDATE works

>Description:
When I run the following SELECTs they all return 0 rows:
select start_date, days, ADDDATE(start_date, INTERVAL days DAY) AS
startDate FROM Event HAVING startDate >= "2002-9-1" AND startDate <=
"2002-9-30";
select start_date, days, ADDDATE(start_date, INTERVAL days DAY) AS
startDate FROM Event HAVING startDate >= "2002-9-01" AND startDate <=
"2002-9-30";
select start_date, days, ADDDATE(start_date, INTERVAL days DAY) AS
startDate FROM Event HAVING startDate >= "2002-09-1" AND startDate <=
"2002-9-30";

While the following returns the 1 correct row:
select start_date, days, ADDDATE(start_date, INTERVAL days DAY) AS
startDate FROM Event HAVING startDate >= "2002-09-01" AND startDate <=
"2002-9-30";

The only difference between the 4 selects about is the first value of date
being compared:
2002-9-1, 2002-9-01, 2002-09-1 all fail while
2002-09-01 works.

What is also interesting is that the second date 2002-9-30 does not need
the month zero padded. The following also works just fine:
select start_date, days FROM Event WHERE start_date >= "2002-8-1" AND
start_date <= "2002-8-31";
Note that the date elements do not have any zero padding.

I've tried using DATE_ADD and MySQL version 3.23.35, 3.23.43 and 3.23.51
all on SuSE linux 7.1 (intel 686).

I've also tried using WHERE with the ADDDATE function instead of using the
AS alias + HAVING with the same result.
>How-To-Repeat:
CREATE TABLE Event (
object_id char(24) NOT NULL default '',
start_date date NOT NULL default '0000-00-00',
start_time time NOT NULL default '00:00:00',
days int(11) default NULL,
hours int(11) default NULL,
minutes int(11) default NULL,
PRIMARY KEY (object_id)
) TYPE=MyISAM;
INSERT INTO Event VALUES
('MY------0000000000000030','2002-08-06','02:00:00',28,1,0);
>Fix:
zero padd the month & day elements of the date

>Submitter-Id:
>Originator: Philip Shiels
>Organization:
------------------------------------------------------------ -------------
Philip Shiels Phone: +39 (0)332 78 5762
TP 270 Fax: +39 (0)332 78 9185
JRC Ispra E-mail: Philip.Shiels@jrc.it
Italy
20120

GIST: http://gist.jrc.it Download: ftp://gist.jrc.it
Docs: http://gist-doc.jrc.it
Tutorial: http://gist.jrc.it:8080 Themes: http://gist-themes.jrc.it
>
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis: odd date behaviour with ADDDATE
>Severity: serious
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.51 (Source distribution)
>Server: /usr/gist/local/bin/mysqladmin Ver 8.23 Distrib 3.23.51, for
pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 3.23.51
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 1 day 1 hour 19 min 2 sec

Threads: 1 Questions: 67 Slow queries: 0 Opens: 7 Flush tables: 1
Open tables: 1 Queries per second avg: 0.001
>Environment:

System: Linux sod 2.4.4-4GB #1 Fri May 18 14:11:12 GMT 2001 i686 unknown
Architecture: i686

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs
gcc version 2.95.3 20010315 (SuSE)
Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS=''
LIBC:
-rwxr-xr-x 1 root root 1343073 May 11 2001 /lib/libc.so.6
-rw-r--r-- 1 root root 24539184 May 11 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 May 11 2001 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/gist/local/depot/mysql-3.23.51


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12336@lists.mysql.com
To unsubscribe, e-mail

Re: inconsistency with how ADDDATE works

Philip Shiels writes:
> From: Philip.Shiels@jrc.it
> To: mysql@lists.mysql.com
> Subject: inconsistency with how ADDDATE works
>
> >Description:
> When I run the following SELECTs they all return 0 rows:
> select start_date, days, ADDDATE(start_date, INTERVAL days DAY) AS
> startDate FROM Event HAVING startDate >= "2002-9-1" AND startDate <=
> "2002-9-30";
> select start_date, days, ADDDATE(start_date, INTERVAL days DAY) AS
> startDate FROM Event HAVING startDate >= "2002-9-01" AND startDate <=
> "2002-9-30";
> select start_date, days, ADDDATE(start_date, INTERVAL days DAY) AS
> startDate FROM Event HAVING startDate >= "2002-09-1" AND startDate <=
> "2002-9-30";
>
> While the following returns the 1 correct row:
> select start_date, days, ADDDATE(start_date, INTERVAL days DAY) AS
> startDate FROM Event HAVING startDate >= "2002-09-01" AND startDate <=
> "2002-9-30";
>
> The only difference between the 4 selects about is the first value of date
> being compared:
> 2002-9-1, 2002-9-01, 2002-09-1 all fail while
> 2002-09-01 works.
>

Hi!

For the moment all DATE / DATETIME constants have to be fully ANSI
compliant, including zerofilling.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12338@lists.mysql.com
To unsubscribe, e-mail

Re: inconsistency with how ADDDATE works

Philip Shiels writes:
> Hi,
> I have a couple of observations below.
>
> > For the moment all DATE / DATETIME constants have to be fully ANSI
> > compliant, including zerofilling.
>
> So why does it work for some and not for others? I have no problem (well
> not much of one) with zero padding, but it is the fact that it doesn't
> need to be zero padded for all date handling.
> e.g. this works - no zero pading:
> select start_date, days FROM Event WHERE start_date >= "2002-8-1" AND
> start_date <= "2002-8-31";
>
> But what I really think is an inconsistency and is inconsistent with your
> statement on zero filling is this:
> select start_date, days FROM Event HAVING ADDDATE(start_date, INTERVAL
> days DAY) >= "2002-9-1" AND ADDDATE(start_date, INTERVAL days DAY) <=
> "2002-9-31";
>
> What is different between the value start_date in the DB and the return
> value of ADDDATE ? As far as I can tell they both return zero-filled dates
> but one gets the row from the DB and the other doesn't?
>
> Philip

Hi!

At present, fixing the above constants when the argument is function
is very difficult.

When it is a field type, MySQL can easily determine which type are
constants supposed to be.

With functions, it is now quite difficult to the same, as constants
are fixed before function is ever executed.

Also, do not forget that there are many functions that can return
different result types.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12345@lists.mysql.com
To unsubscribe, e-mail

Re: inconsistency with how ADDDATE works

OK, no problem, I understand the problems in mixed-type environments.

Taking what you said into account I tried a couple of things. I installed
4.0.2 and used CAST to tell the DB what I expect ADDDATE to return - it
still fails and here are the selects I tried.

TREAT RETURN AS A DATE
select start_date, days FROM Event HAVING CAST(ADDDATE(start_date,
INTERVAL days DAY) AS DATE) >= "2002-9-1" AND ADDDATE(start_date, INTERVAL
days DAY) <= "2002-9-31";
AND
select start_date, days, CAST(ADDDATE(start_date, INTERVAL days DAY) AS
DATE) AS sd FROM Event HAVING sd >= "2002-9-1" AND sd <= "2002-9-31";


AS ABOVE BUT ALSO CAST THE LITERALS AS DATES
select start_date, days, CAST(ADDDATE(start_date, INTERVAL days DAY) AS
DATE) AS sd FROM Event HAVING sd >= CAST("2002-9-1" AS DATE) AND sd <=
CAST("2002-9-31" AS DATE);

mysql --version
mysql Ver 12.10 Distrib 4.0.2-alpha, for dec-osf5.1 (alphaev6)

Hope this helps,
ciao,
Philip

Sinisa Milivojevic wrote:
> Philip Shiels writes:
>
>>Hi,
>> I have a couple of observations below.
>>
>> > For the moment all DATE / DATETIME constants have to be fully ANSI
>> > compliant, including zerofilling.
>>
>>So why does it work for some and not for others? I have no problem (well
>>not much of one) with zero padding, but it is the fact that it doesn't
>>need to be zero padded for all date handling.
>>e.g. this works - no zero pading:
>>select start_date, days FROM Event WHERE start_date >= "2002-8-1" AND
>>start_date <= "2002-8-31";
>>
>>But what I really think is an inconsistency and is inconsistent with your
>>statement on zero filling is this:
>>select start_date, days FROM Event HAVING ADDDATE(start_date, INTERVAL
>>days DAY) >= "2002-9-1" AND ADDDATE(start_date, INTERVAL days DAY) <=
>>"2002-9-31";
>>
>>What is different between the value start_date in the DB and the return
>>value of ADDDATE ? As far as I can tell they both return zero-filled dates
>>but one gets the row from the DB and the other doesn't?
>>
>>Philip
>
>
> Hi!
>
> At present, fixing the above constants when the argument is function
> is very difficult.
>
> When it is a field type, MySQL can easily determine which type are
> constants supposed to be.
>
> With functions, it is now quite difficult to the same, as constants
> are fixed before function is ever executed.
>
> Also, do not forget that there are many functions that can return
> different result types.
>

--
------------------------------------------------------------ -------------
Philip Shiels Phone: +39 (0)332 78 5762
TP 270 Fax: +39 (0)332 78 9185
JRC Ispra E-mail: Philip.Shiels@jrc.it
Italy
20120

GIST: http://gist.jrc.it Download: ftp://gist.jrc.it
Docs: http://gist-doc.jrc.it
Tutorial: http://gist.jrc.it:8080 Themes: http://gist-themes.jrc.it


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12348@lists.mysql.com
To unsubscribe, e-mail

Re: inconsistency with how ADDDATE works

Philip Shiels writes:
> OK, no problem, I understand the problems in mixed-type environments.
>
> Taking what you said into account I tried a couple of things. I installed
> 4.0.2 and used CAST to tell the DB what I expect ADDDATE to return - it
> still fails and here are the selects I tried.
>
> TREAT RETURN AS A DATE
> select start_date, days FROM Event HAVING CAST(ADDDATE(start_date,
> INTERVAL days DAY) AS DATE) >= "2002-9-1" AND ADDDATE(start_date, INTERVAL
> days DAY) <= "2002-9-31";
> AND
> select start_date, days, CAST(ADDDATE(start_date, INTERVAL days DAY) AS
> DATE) AS sd FROM Event HAVING sd >= "2002-9-1" AND sd <= "2002-9-31";
>
>
> AS ABOVE BUT ALSO CAST THE LITERALS AS DATES
> select start_date, days, CAST(ADDDATE(start_date, INTERVAL days DAY) AS
> DATE) AS sd FROM Event HAVING sd >= CAST("2002-9-1" AS DATE) AND sd <=
> CAST("2002-9-31" AS DATE);
>
> mysql --version
> mysql Ver 12.10 Distrib 4.0.2-alpha, for dec-osf5.1 (alphaev6)
>
> Hope this helps,
> ciao,
> Philip
>
> --
> ------------------------------------------------------------ -------------
> Philip Shiels Phone: +39 (0)332 78 5762
> TP 270 Fax: +39 (0)332 78 9185
> JRC Ispra E-mail: Philip.Shiels@jrc.it
> Italy
> 20120
>
> GIST: http://gist.jrc.it Download: ftp://gist.jrc.it
> Docs: http://gist-doc.jrc.it
> Tutorial: http://gist.jrc.it:8080 Themes: http://gist-themes.jrc.it


Using CAST() has nothing to do with the above problem. CAST() is there
only to supply the SQL type for the CREATE from SELECT command.

To speak in the terms of MySQL server internals, this changes the
object of the class Field. But for the above constants and ADDDATE
function, that Field is NULL, until ADDDATE is executed. But as I said
before, constants are fixed only once in the code, prior to function
execution. Otherwise, MySQL would have been slower.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12350@lists.mysql.com
To unsubscribe, e-mail