Tuesday, February 05, 2008

MySQL, Oracle, DB2, PgSQL and Firebird versus Leap Years and Division by Zero

Oracle first:


SQL> CREATE TABLE leaptest (thedate date);

Table created.

SQL> INSERT INTO leaptest VALUES ('28-feb-2008');

1 row created.

SQL> INSERT INTO leaptest VALUES ('29-feb-2008');

1 row created.

SQL> INSERT INTO leaptest VALUES ('30-feb-2008');
INSERT INTO leaptest VALUES ('30-feb-2008')
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


SQL> INSERT INTO leaptest VALUES ('29-feb-2007');
INSERT INTO leaptest VALUES ('29-feb-2007')
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

SQL> SELECT * from leaptest;

THEDATE
---------
28-FEB-08
29-FEB-08


As you can see, Oracle knows 2008 is a leap year, and that 2007 is not.

Let's try another good database, PostgreSQL:

goods=> CREATE TABLE leaptest (thedate date);
CREATE TABLE
goods=> INSERT INTO leaptest VALUES ('28-feb-2008');
INSERT 0 1
goods=> INSERT INTO leaptest VALUES ('29-feb-2008');
INSERT 0 1
goods=> INSERT INTO leaptest VALUES ('30-feb-2008');
ERROR: date/time field value out of range: "30-feb-2008"
goods=> INSERT INTO leaptest VALUES ('29-feb-2007');
ERROR: date/time field value out of range: "29-feb-2007"
goods=> SELECT * FROM leaptest;
thedate
------------
2008-02-28
2008-02-29
(2 rows)


No problems here either. What about IBM DB2?

db2 => CREATE TABLE leaptest (thedate date)
DB20000I The SQL command completed successfully.
db2 => INSERT INTO cmihai.leaptest VALUES ('2008-02-28')
DB20000I The SQL command completed successfully.
db2 => INSERT INTO cmihai.leaptest VALUES ('2008-02-29')
DB20000I The SQL command completed successfully.
db2 => INSERT INTO cmihai.leaptest VALUES ('2008-02-30')
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0181N The string representation of a datetime value is out of range.
SQLSTATE=22007
db2 => INSERT INTO cmihai.leaptest VALUES ('2007-02-29')
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0181N The string representation of a datetime value is out of range.
SQLSTATE=22007
db2 => SELECT * FROM cmihai.leaptest

THEDATE
----------
02/28/2008
02/29/2008

2 record(s) selected.


Let's even try Firebird:

SQL> CREATE TABLE leaptest (thedate date);
SQL> INSERT INTO leaptest VALUES ('28-feb-2008');
SQL> INSERT INTO leaptest VALUES ('29-feb-2008');
SQL> INSERT INTO leaptest VALUES ('30-feb-2008');
Statement failed, SQLCODE = -413
conversion error from string "30-feb-2008"
SQL> INSERT INTO leaptest VALUES ('29-feb-2007');
Statement failed, SQLCODE = -413
conversion error from string "29-feb-2007"
SQL> SELECT * FROM leaptest;

THEDATE
===========
2008-02-28
2008-02-29


Well now, let's try MySQL:

mysql> CREATE TABLE leaptest (thedate date);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO leaptest VALUES ('28-feb-2008');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO leaptest VALUES ('29-feb-2008');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO leaptest VALUES ('30-feb-2008');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO leaptest VALUES ('29-feb-2007');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM leaptest;
+------------+
| thedate |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
+------------+
4 rows in set (0.00 sec)

As you can see, MySQL happily takes the input, but when we try to read it.. surprise surprise, your data isn't there! It should respond with an error code..

As Komal Shah points out, that's only due to an invalid date format, using ISO dates dates works fine.

INSERT INTO leaptest VALUES ('2008-02-28');

An invalid date will still generate a warning and a 0000-00-00 entry.

Here's another fine piece of MySQL behavior:
Division by 0:
Well, let's first see how Oracle and PgSQL handle this:

Oracle
SQL> SELECT 0/0 FROM dual;
SELECT 0/0 FROM dual
*
ERROR at line 1:
ORA-01476: divisor is equal to zero


PostgreSQL
goods=> SELECT 0/0;
ERROR: division by zero


IBM DB2:

db2 => SELECT 0/0 FROM cmihai.leaptest
SQL0801N Division by zero was attempted. SQLSTATE=22012


Firebird:

SQL> SELECT 0/0 FROM leaptest;

=====================
Statement failed, SQLCODE = -802
arithmetic exception, numeric overflow, or string truncation
SQL> SELECT 0/1 FROM leaptest;


=====================
0
0
SQL> SELECT 1+1 FROM leaptest;


=====================
2
2



MySQL
mysql> SELECT 0/0;
+------+
| 0/0 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

So there you have it, MySQL unexpected behavior.

If anyone cares, tested versions: DB2 9.5, Oracle 10g and 11g, PosgreSQL 8.2, 8.3 and Firebird 2. MySQL was 5.0.45.

7 comments:

User1 said...

Very Nice.

Komal Shah said...

You need to insert using ISO dates, ie '2008-02-28'.

INSERT INTO leaptest VALUES ('2008-02-28'); works fine

I got this valuable information from #mysql on irc.

Komal Shah said...

You need to insert using ISO dates,
ie '2008-02-28'

INSERT INTO leaptest VALUES ('2008-02-28'); works fine

I got this valuable information from #mysql on irc

cmihai said...

Yes, you are correct, it just spits a warning and enters 0000-00-00 in case of invalid date formats or invalid dates.

gigiduru said...

Well Mihai,

What would you expect from piece of crap dbms?!
The standard reply from Mysql fanboys is that you should put the database in sql_mode=strict_all_tables. However, in the most enterprise environments you cannot really do this once the application got already built on the default settings.
The best move you could is to download the latest and greatest PostgreSQL and 99.999% of your problems might go away.

fabiano said...

Or choose Firebird and you are 100% ok :-)

nikola said...

for Firebird
SQL> SELECT 0/0 FROM leaptest; ???

it should be:
SQL> SELECT 0/0 FROM RDB$DATABASE;