[Firebird-devel] [FB-Tracker] Created: (CORE-6241) Values greater than number of days between 01.01.0001 and 31.12.9999 (=3652058) can be added or subtracted from DATE

[Pavel Zotov (JIRA)]

Values greater than number of days between 01.01.0001 and 31.12.9999 (=3652058) 
can be added or subtracted from DATE
--------------------------------------------------------------------------------------------------------------------

                 Key: CORE-6241
                 URL: http://tracker.firebirdsql.org/browse/CORE-6241
             Project: Firebird Core
          Issue Type: Improvement
          Components: Engine
    Affects Versions: 3.0.5, 2.5.9, 4.0 Beta 1
            Reporter: Pavel Zotov
            Priority: Minor


Consider following statements. Some of them PASS but they obviously are 
pointless and must fail (checked on 2.5.9, 3.0.6, 4.0.0 -- result is the same).

set list on;

-- J4YI:
-- select datediff( day from date '01.02.2020'  to date '31.12.9999') from 
rdb$database; -- 2914603
-- select datediff( day from date '01.01.0001'  to date '01.02.2020') from 
rdb$database; -- 737455
-- select datediff( day from date '01.01.0001'  to date '31.12.9999') from 
rdb$database; -- 3652058
-- set echo on;

-- #################  1. ADDITION ##################
                                                                                
               
select date '01.02.2020' + 2914603 from rdb$database; -- OK, as expected: 
9999-12-31

select date '01.02.2020' + 2914604 from rdb$database; -- OK, as expected: 
SQLSTATE = 22008 (value exceeds the range for valid dates)

select date '01.02.2020' + 2147483647 from rdb$database; -- OK, as expected: 
SQLSTATE = 22008

select date '01.02.2020' + 2147483648 from rdb$database; -- OK, as expected: 
SQLSTATE = 22008

select date '01.02.2020' + 4294229840 from rdb$database; -- OK, as expected: 
SQLSTATE = 22008

select date '01.02.2020' + 4294229841 from rdb$database; -- ???? 0001-01-01; 
NB: 4294967296 - 4294229841 = 737455  -- days since 01.01.0001 to 01.02.2020

select date '01.02.2020' + 9223372036854775807 from rdb$database; -- ???? 
2020-01-31


-- ###############  2. SUBTRACTION #################

select date '01.02.2020' - 737455 from rdb$database; -- OK, as expected: 
0001-01-01

select date '01.02.2020' - 737456 from rdb$database; -- OK, as expected: 
SQLSTATE = 22008 (value exceeds the range for valid dates)

select date '01.02.2020' - 2147483648 from rdb$database; -- OK, as expected: 
SQLSTATE = 22008

select date '01.02.2020' - 4292052692 from rdb$database; -- OK, as expected: 
SQLSTATE = 22008

select date '01.02.2020' - 4292052693 from rdb$database; -- ???? 9999-12-31; 
NB: 4294967296 - 4292052693 = 2914603  -- days since 01.02.2020 to 31.12.9999

select date '01.02.2020' - 9223372036854775807 from rdb$database; -- ???? 
2020-02-02

select date '01.02.2020' + -9223372036854775808 from rdb$database; -- ???? 
2020-02-01

PS.

IMO, this is not a bug from practice point of view (i can not imagine that such 
expressions can be in use in any apps ). But compiler (or engine ?) for sure 
must decline all terms that we attempt to add/subtract from DATE with 
magniotude more that allowed by built-in limits of this type.



-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        


Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel