Oracle時間型別資料為0的bug

開發組在資料遷移時，報告發現一些數值為'0000/00/00'的date資料，導致資料遷移失敗。

這個問題有點奇怪，因為在Oracle中，date型別的資料的取值範圍是從-4712/12/31到9999/12/31之間，並且年份不能為0。也就是說'0000/00/00'是一個非法資料，不為oracle所接受。

 

SQL> select to_date('0000-00-00', 'yyyy-mm-dd') from dual;

select to_date('0000-00-00', 'yyyy-mm-dd') from dual

ORA-01843: not a valid month

SQL> select to_date('0000-01-01', 'yyyy-mm-dd') from dual;

select to_date('0000-01-01', 'yyyy-mm-dd') from dual

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

 

但為什麼在資料中還是出現了'0000/00/00'呢？對此問題稍微研究了一下，發現oracle在date型別的資料問題上確實存在一些bug，通過一些特殊方法還是能使date型別儲存'0000/00/00'資料。先看以下操作，這是bug之一。

 

SQL> select date '0000-01-01' from dual;

DATE'0000-01-01'

----------------

0/0/0000

SQL> select date '0000-11-22' from dual;

DATE'0000-11-22'

----------------

0/0/0000

 

在使用date關鍵字時，時間格式是羅馬格式。此時，我們發現oracle沒有對年份是否為0進行校驗。並且，只要年份為0，資料都會被轉變為'0000/00/00'。

 

再看另外一種情況，

 

SQL> select to_date('0001-01-01', 'yyyy-mm-dd')-365 from dual;

TO_DATE('0001-01-01','YYYY-MM-

------------------------------

0/0/0000

SQL> select to_date('0001-01-01', 'yyyy-mm-dd')-360 from dual;

TO_DATE('0001-01-01','YYYY-MM-

------------------------------

0/0/0000

 

可以看到，oracle對時間表示式的結果也沒有校驗年份是否為0，結合上面的bug，只要計算結果年份為0，無論月、日數值，結果都為'0000/00/00'。

 

再看第三種情況，就更加特殊了：只要對100到1500年之內的所有整百年的日期進行計算，如果結果為2月29的話，結果都為'0000/00/00'。

 

SQL> select date '0099-2-28' +1 from dual;

DATE'0099-2-28'+1

-----------------

3/1/0099

SQL> select date '0100-2-28' +1 from dual;

DATE'0100-2-28'+1

-----------------

0/0/0000

SQL> select date '1000-2-28' +1 from dual;

DATE'1000-2-28'+1

-----------------

0/0/0000

SQL> select date '1000-2-28' +2 from dual;

DATE'1000-2-28'+2

-----------------

3/1/1000

SQL> select date '1000-2-27' +2 from dual;

DATE'1000-2-27'+2

-----------------

0/0/0000

SQL> select date '1500-2-28' +1 from dual;

DATE'1500-2-28'+1

-----------------

0/0/0000

SQL> select date '1600-2-28' +1 from dual;

DATE'1600-2-28'+1

-----------------

2/29/1600

 

最後一種情況，如果日期表示式的結果小於0，結果都為'0000/00/00'。

 

SQL> select date '-0001-11-11' +1 from dual;

DATE'-0001-11-11'+1

-------------------

0/0/0000

SQL> select date '-4712-11-11' +15 from dual;

DATE'-4712-11-11'+15

--------------------

0/0/0000

SQL> select date '-1111-10-11' +0 from dual;

DATE'-1111-10-11'+0

-------------------

0/0/0000

SQL> select to_date('01/01/01', 'yyyy/mm/dd') - 900 from dual;

TO_DATE('01/01/01','YYYY/MM/DD

------------------------------

0/0/0000

 

順便要說的是，以上結果不僅可以被查詢出來，而且也能被儲存在欄位型別為date的表中。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8183550/viewspace-662772/，如需轉載，請註明出處，否則將追究法律責任。