Mysql, años bisiestos y divisiones por cero

[jachguate]

Al parecer, mySQL tiene problemas con los años bisiestos. Si usas controles Data-Aware o sentencias parametrizadas desde delphi, ¡suerte que delphi te ayuda!, pero si estas construyendo sentencias SQL de puro texto (probablemente para importar datos desde otras fuentes, ¡mucho cuidado!

Este es el resultado de un experimento realizado sobre oracle, PostgreSQL, DB2, firebird y mysql:

Años bisiestos
Oracle:

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

Resultado de la prueba: satisfactorio.

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)

Resultado de la prueba: satisfactorio.

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.

Resultado de la prueba: satisfactorio.

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

Resultado de la prueba: satisfactorio.

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)

Resultado de la prueba: NO satisfactorio.

Alguién ha indicado que si se usa fechas en formato ISO trabajaría:

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

Esto genera un warning, pero deja la fila con fecha 0000-00-00.
Resultado de la prueba: NO satisfactorio.

División por cero
Oracle

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

Resultado de la prueba: satisfactorio.

PostgreSQL

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

Resultado de la prueba: satisfactorio.

DB2:

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

Resultado de la prueba: satisfactorio.

Firebird:

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

Resultado de la prueba: satisfactorio.

MySQL

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

Resultado de la prueba: NO satisfactorio.

Las versiones utilizadas para el experimento son:
DB2 9.5,
Oracle 10g and 11g,
PosgreSQL 8.2, 8.3
Firebird 2
MySQL 5.0.45.

enlace