Issue Details (XML | Word | Printable)

Key: CORE-4789
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Minor Minor
Assignee: Adriano dos Santos Fernandes
Reporter: Pavel Zotov
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Prohibit ability to cast timestamps that out of valid range to varchar

Created: 13/May/15 01:38 PM   Updated: 27/May/15 04:56 PM
Component/s: None
Affects Version/s: None
Fix Version/s: 3.0 Beta 2, 2.5.5

QA Status: Done successfully
Test Details:
Block for 2.5 is simplified vs 3.0: no bigint values as arguments to dateadd().
This test is also related to CORE-2174


 Description  « Hide

SQL> SELECT datediff(hour from timestamp '01.01.0100 00:00:00.0000' to timestamp '31.12.9999 23:59:59.9999' ) FROM RDB$DATABASE;

             DATEDIFF
=====================
             86781599


SQL> select dateadd( 86781599 hour to timestamp '01.01.0100 00:00:00.0000') from rdb$database;

                  DATEADD
=========================
9999-12-31 23:00:00.0000


SQL> select dateadd( 867815990 hour to timestamp '01.01.0100 00:00:00.0000') from rdb$database;

                  DATEADD
=========================
Statement failed, SQLSTATE = 22008
value exceeds the range for valid timestamps

-- here it works fine.

But:


SQL> select cast(dateadd( 867815990 hour to timestamp '01.01.0100 00:00:00.0000') as varchar(32)) from rdb$database;

CAST
================================
99099-12-24 14:00:00.0000

SQL> select cast(dateadd( 86781599000 hour to timestamp '01.01.0100 00:00:00.0000') as varchar(32)) from rdb$database;

CAST
================================
1080682-01-16 08:00:00.0000

SQL> select cast(dateadd( 8678159900000 hour to timestamp '01.01.0100 00:00:00.0000') as varchar(32)) from rdb$database;

CAST
================================
-802195--01--13 12:16:02.2528


Casting of such timestamps is allowed but can lead to meaningless output. It will be nice to completely forbit any operations with date if it's value out of range.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Pavel Zotov added a comment - 15/May/15 06:04 AM
What is minimum value of DATE & TIMESTAMP ?
I thought that it's 01-jan-0100 AD, but:

set list on;
select
    date '01.01.0001' d1
   ,dateadd(-36159 day to date '01.01.0100') d2
   ,timestamp '01.01.0001 00:00:00.000' t1
   ,dateadd( -3124137600000 millisecond to timestamp '01.01.0100 00:00:00.0000') t2
from rdb$database;

Output:
======
D1 0001-01-01
D2 0001-01-01
T1 0001-01-01 00:00:00.0000
T2 0001-01-01 00:00:00.0000

Pavel Zotov added a comment - 15/May/15 08:27 PM
One more question: when I issue this

echo show version; select dateadd( 1 second to cast('00:00:00' as time) ) from rdb$database rows 1; | isql host/port:alias

-- then I get:

1) on snapshot that was built before fix for this ticket was committed:

ISQL Version: WI-T3.0.0.31836 Firebird 3.0 Beta 2
Server version:
Firebird/Linux/AMD/Intel/x64 (access method), version "LI-T3.0.0.31827 Firebird 3.0 Beta 2"
Firebird/Linux/AMD/Intel/x64 (remote server), version "LI-T3.0.0.31827 Firebird 3.0 Beta 2/tcp (oel64)/P13"
Firebird/Windows/Intel/i386 (remote interface), version "WI-T3.0.0.31836 Firebird 3.0 Beta 2/tcp (csprog)/P13"
on disk structure version 12.0

      DATEADD
=============
00:00:01.0000

2) on current snapshot:

ISQL Version: WI-T3.0.0.31836 Firebird 3.0 Beta 2
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.31836 Firebird 3.0 Beta 2"
Firebird/Windows/Intel/i386 (remote server), version "WI-T3.0.0.31836 Firebird 3.0 Beta 2/tcp (csprog)/P13"
Firebird/Windows/Intel/i386 (remote interface), version "WI-T3.0.0.31836 Firebird 3.0 Beta 2/tcp (csprog)/P13"
on disk structure version 12.0

      DATEADD
=============
Statement failed, SQLSTATE = 22008
value exceeds the range for valid timestamps


(this also is displayed by fault here: http://web.firebirdsql.org/download/prerelease/results/3.0.0.31836/bugs.core_1173.html )