>Description:
I have found some inconsistencies (one may call them bugs ;-) in the
current MySQL 3.23 version. I tested this on Linux and Solaris 2.8
so this should not be platform specific...
The result differs, if one uses the constant value "0000-00-00" or
if one uses a column which has the value "0000-00-00".
The following functions are affected: DAYOFMONTH, DAYOFYEAR, MONTH,
QUARTER, WEEK, YEAR, YEARWEEK, TO_DAYS, DATE_FORMAT and EXTRACT.
Not tested: DATE_ADD, DATE_SUB, ADDDATE and SUBDATE.
>How-To-Repeat:
CREATE TABLE xx ( Gueltig DATE NOT NULL );
INSERT INTO xx VALUES ( "0000-00-00" );
SELECT DAYOFYEAR("0000-00-00"),Gueltig,DAYOFYEAR(Gueltig) FROM xx;
+-------------------------+------------+--------------------+
| DAYOFYEAR("0000-00-00") | Gueltig | DAYOFYEAR(Gueltig) |
+-------------------------+------------+--------------------+
| NULL | 0000-00-00 | -730484 |
+-------------------------+------------+--------------------+
1 row in set (0.00 sec)
SELECT DAYOFMONTH("0000-00-00"),Gueltig,DAYOFMONTH(Gueltig) FROM xx;
+--------------------------+------------+---------------------+
| DAYOFMONTH("0000-00-00") | Gueltig | DAYOFMONTH(Gueltig) |
+--------------------------+------------+---------------------+
| NULL | 0000-00-00 | 0 |
+--------------------------+------------+---------------------+
1 row in set (0.00 sec)
and so on...
>Fix:
don't know :-(
except SELECT IF(Gueltig="0000-00-00",NULL,DAYOFYEAR(Gueltig)) FROM xx;
but this would mean to check all programs...
>Submitter-Id: <submitter ID>
>Originator:
>Organization:
>MySQL support: none
>Synopsis: bugs / inconsistencies with date functions
>Severity: non-critical
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.43 (Source distribution)
>Server: /usr/local/bin/mysqladmin Ver 8.21 Distrib 3.23.43, 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.43-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 11 days 21 hours 4 min 9 sec
Threads: 7 Questions: 18875287 Slow queries: 3198 Opens: 63795 Flush tables: 992
Open tables: 63 Queries per second avg: 18.393
>Environment:
System: Linux cleopatra 2.2.19 #7 Sat Oct 6 02:37:48 CEST 2001 i686 unknown
Architecture: i686
Some paths: /usr/local/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.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
-rwxr-xr-x 1 root root 4060896 Jan 17 2001 /lib/libc.so.6
-rw-r--r-- 1 root root 19186278 Jan 17 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Jan 17 2001 /usr/lib/libc.so
Configure command: ./configure --without-bench --without-docs
Perl: This is perl, version 5.005_03 built for i586-linux
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
