Resuelto el misterio del año 0000!.

Hoy, gracias a una discusión a tres bandas en twitter con Tony Doval @tonydoval, Xavier Picamal @Condebond y Elias Fernández @sailefm se ha resuelto por fin el misterio del año 0000 en algunas de mis bases de datos.

¿Quién localizó el bug? El premio es para Elias Fernández! (que se lleva mi más sincero "me quito el sombrero").

La cuestión es que el año 0000 no existe, aunque en algunas bases de datos he visto lo siguiente:

SQL> select * from zero_leap_year
  2  where to_char(date_year,'yyyy')='0000' and rownum<6;

DATE_YEAR
--------------------
30-DEC-0000 00:00:00
30-JAN-0000 00:00:00
30-DEC-0000 00:00:00
29-FEB-0000 00:00:00
30-JAN-0000 00:00:00

El año 0000 no existe. Del año 1 antes de Cristo se pasa al año 1 después de Cristo. Cualquier forma de insertar un año 0000 o una fecha 29-febrero en un año no bisiesto dará los siguientes errores:

SQL> insert into zero_leap_year values (to_date('20-02-0000 00:00:00','dd-mm-yyyy hh24:mi:ss'));
insert into zero_leap_year values (to_date('20-02-0000 00:00:00','dd-mm-yyyy hh24:mi:ss'))
                                           *
ERROR at line 1
ORA-01841 :(full) year must be between -4713 and +9999, and not be 0
SQL> insert into zero_leap_year values (to_date('29-02-2007 00:00:00','dd-mm-yyyy hh24:mi:ss'));
insert into zero_leap_year values (to_date('29-02-2007 00:00:00','dd-mm-yyyy hh24:mi:ss'))
                                           *
ERROR at line 1:
ORA-01839: date not valid for month specified

No obstante, estas filas misteriosas seguían apareciendo. Tanto en versión Oracle9i, Oracle10g y Oracle11g. ¿Cómo han podido colarse? Muy probablemente como Elias Fernandez encontró: partiendo de una fecha como, por ejemplo, 1-enero del año 1, restarle 1 día. Voilà! 

SQL> select (TO_DATE('01/01/0001 00:00:00', 'DD/MM/YYYY HH24:MI:SS') - 1) from dual;

(TO_DATE('01/01/0001
--------------------
31-DIC-0000 00:00:00


No sólo eso... ese año 0000 que no existe en la historia, según Oracle, es bisiesto!

SQL> select (TO_DATE('01/01/0001 00:00:00', 'DD/MM/YYYY HH24:MI:SS') - 307) from dual;

(TO_DATE('01/01/0001
--------------------
29-FEB-0000 00:00:00


Esto es un bug en toda regla!