Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5, 10.0, 10.1, 10.2, 10.3, 10.4
-
None
-
None
Description
Oracle and MariaDB return different results for TIMESTAMP+INTERVAL.
Example #1:
Oracle:
ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd'; |
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF6'; |
SELECT TIMESTAMP'1000-01-01 00:00:00' + INTERVAL '3287175 23:59:59.999999' DAY(7) TO SECOND AS c1 FROM DUAL; |
C1
|
---------------------------------------------------------------------------
|
9000-12-29 23:59:59.999999
|
MariaDB:
SELECT TIMESTAMP'1000-01-01 00:00:00' + INTERVAL '3287175 23:59:59.999999' DAY_MICROSECOND AS c1 FROM DUAL; |
+----------------------------+
|
| c1 |
|
+----------------------------+
|
| 9999-12-31 23:59:59.999999 |
|
+----------------------------+
|
PostgreSQL (yet another different result):
SELECT TIMESTAMP'1000-01-01 00:00:00' + INTERVAL '3287175 23:59:59.999999' DAY TO SECOND AS c1; |
c1
|
----------------------------
|
9999-12-25 23:59:59.999999
|
Example #2 (though the year of 0001 is outside of MariaDB DATETIME supported range):
Oracle:
ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd'; |
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF6'; |
SELECT TIMESTAMP'0001-01-01 00:00:00' + INTERVAL '3652058 23:59:59.999999' DAY(7) TO SECOND AS c1 FROM DUAL; |
C1
|
---------------------------------------------------------------------------
|
9999-12-29 23:59:59.999999
|
MariaDB
SELECT TIMESTAMP'0001-01-01 00:00:00' + INTERVAL '3652058 23:59:59.999999' DAY_MICROSECOND AS c1 FROM DUAL;
|
+----------------------------+
|
| c1 |
|
+----------------------------+
|
| 9999-12-31 23:59:59.999999 |
|
+----------------------------+
|
Attachments
Issue Links
- blocks
-
MDEV-11070 Providing compatibility to other databases - Phase 2
-
- Open
-
- relates to
-
MDEV-32496 TIMESTAMP variant to support YEAR range -4713 and +9999
-
- Open
-