error in comparing date with datetime

Description:
a datetime with no time-information should be equal to a date
if de date-parts are identical.
How-To-Repeat:
create table t (d1 date, d2 datetime);
insert into t values (20040320,20040320);
select d2=d1 from t;
+-------+
| d2=d1 |
+-------+
| 0 |
+-------+
Fix:
select d2=d1+interval 0 second from t;
+-------------------------+
| d2=d1+interval 0 second |
+-------------------------+
| 1 |
+-------------------------+

Submitter-Id:
Originator: paul vd berg
Organization: InterAction database,
Dept Social pharmacy, Pharmacoepidemiology & Pharmacotherapeutics
University of Groningen
MySQL support: [none]
Synopsis: error in comparing date with datetime
Severity: [ non-critical ] (one line)
Priority: [ medium ] (one line)
Category: mysql
Class: [ sw-bug ] (one line)
Release: mysql-4.0.16 (Source distribution)
Server: /usr/bin/mysqladmin Ver 8.40 Distrib 4.0.16, 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 4.0.16-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 13 hours 21 min 16 sec

Threads: 2 Questions: 233 Slow queries: 0 Opens: 141 Flush tables: 1 Open tables: 61 Queries per second avg: 0.005
C compiler: 2.95.4
C++ compiler: 2.95.4
Environment: debian woody
System: Linux datapot 2.4.18 #1 Tue Mar 4 13:41:06 CET 2003 i686 unknown
Architecture: i686

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
gcc version 2.95.4 20011002 (Debian prerelease)
Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Apr 29 2003 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x 1 root root 1153784 Apr 8 2003 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2391002 Apr 8 2003 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Apr 8 2003 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-raid' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server' '--with-vio' '--with-openssl' '--without-docs' '--without-bench' '--without-readline' '--with-extra-charsets=all' '--with-berkeley-db' '--with-innodb'



--
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: error in comparing date with datetime

Paul B van den Berg writes:
> Description:
> a datetime with no time-information should be equal to a date
> if de date-parts are identical.
> How-To-Repeat:
> create table t (d1 date, d2 datetime);
> insert into t values (20040320,20040320);
> select d2=d1 from t;
> +-------+
> | d2=d1 |
> +-------+
> | 0 |
> +-------+
> Fix:
> select d2=d1+interval 0 second from t;
> +-------------------------+
> | d2=d1+interval 0 second |
> +-------------------------+
> | 1 |
> +-------------------------+
>

Hi!

Yes, the above is expected behaviour.

You can not compare different column types unless you do some type of
conversion.

It can be done as above or with CAST() commands.

--

Sincerely,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Full time Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus

Meet the MySQL at User Conference ! (April 14-16, 2004)
http://www.mysql.com/uc2004/


--
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: error in comparing date with datetime

On Tue, 23 Mar 2004 20:21:50 +0200
Sinisa Milivojevic wrote:
>Paul B van den Berg writes:
>> Description:
>> a datetime with no time-information should be equal to a date
>> if de date-parts are identical.
>> How-To-Repeat:
>> create table t (d1 date, d2 datetime);
>> insert into t values (20040320,20040320);
>> select d2=d1 from t;
>> +-------+
>> | d2=d1 |
>> +-------+
>> | 0 |
>> +-------+
>> Fix:
>> select d2=d1+interval 0 second from t;
>> +-------------------------+
>> | d2=d1+interval 0 second |
>> +-------------------------+
>> | 1 |
>> +-------------------------+
>>
>
>Hi!
>
>Yes, the above is expected behaviour.
>
>You can not compare different column types unless you do some type of
>conversion.
>
>It can be done as above or with CAST() commands.
>
>--
>
>Sincerely,
>
>--
>For technical support contracts, go to https://order.mysql.com/?ref=msmi
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB
>/_/ /_/\_, /___/\___\_\___/ Full time Developer and Support Coordinator
> <___/ www.mysql.com Larnaca, Cyprus
>
>Meet the MySQL at User Conference ! (April 14-16, 2004)
>http://www.mysql.com/uc2004/
>

It took me some time, but now I have found the latest draft SQL
specification
at http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/DocumentsView?open view
(I don't want to pay for the official document).
Document 32N1006-WD9075-02-FOUNDATION-2003-09.PDF states:
[quote]
3.1.6.5 comparable (of a pair of values):
Capable of being compared, according to the rules of Subclause 8.2,
"". In most, but not all, cases, the values of a data
type
can be compared one with another. For the specification of comparability of
individual data types, see Subclause 4.2, "Character strings", through
Subclause
4.10, "Collection types".

4.6.2 Datetimes
Table 2, "Fields in datetime values", specifies the fields that can make up
a
datetime value; a datetime value is made up of a subset of those fields. Not
all
of the fields shown are required to be in the subset, but every field that
appears in the table between the first included primary field and the last
included primary field shall also be included. If either time zone field is
in
the subset, then both of them shall be included.

Table 2 -- Fields in datetime values
Keyword Meaning
YEAR Year
MONTH Month within year
DAY Day within month
HOUR Hour within day
MINUTE Minute within hour
SECOND Second and possibly fraction of a second within
minute
TIMEZONE_HOUR Hour value of time zone displacement
TIMEZONE_MINUTE Minute value of time zone displacement

There is an ordering of the significance of s. This
is,
from most significant to least significant:
YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.

There are three classes of datetime data types defined within this part of
ISO/IEC 9075:
-- DATE -- contains the s YEAR, MONTH, and DAY.
-- TIME -- contains the s HOUR, MINUTE, and SECOND.
-- TIMESTAMP -- contains the s YEAR, MONTH, DAY,
HOUR,
MINUTE, and SECOND.

Items of type datetime are comparable only if they have the same
s.

[endquote]

I was wrong in my initial email where I stated that a date and a datetime
should
be equal. However, the SQL-specification states that a date and a datetime
cannot be compared at all.
Shouldn't MySQL report an error if you try to do it?

Regards, Paul
--
Paul B van den Berg, Manager InterAction Database
Social Pharmacy, Pharmacoepidemiology & Pharmacotherapeutics,
University of Groningen tel +31 50 3633331 fax +31 50 3632772

--
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: error in comparing date with datetime

Hi!

On May 03, P.B.van.den.Berg wrote:
> On Tue, 23 Mar 2004 20:21:50 +0200
> Sinisa Milivojevic wrote:
> >Paul B van den Berg writes:
>
> I was wrong in my initial email where I stated that a date and a
> datetime should be equal. However, the SQL-specification states that a
> date and a datetime cannot be compared at all.
> Shouldn't MySQL report an error if you try to do it?

Strictly speaking, yes. Same for comparison of a string and an integer.
But MySQL was always very forgiving in this regard, and always tried to
perform an implicit type cast if possible. It was one of the original
"easy of use" features. Though we do care about SQL compliance much more
now than five years ago :) backward compatibility and ease of use are
good things too. In this particular case it means that we're considering
adding a switch (or, rather, an "sql_mode") to disable automatic type
casts.

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