Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Prohibit ability to cast timestamps that out of valid range to varchar [CORE4789] #5088

Closed
firebird-automations opened this issue May 13, 2015 · 7 comments
Closed

Prohibit ability to cast timestamps that out of valid range to varchar [CORE4789] #5088

firebird-automations opened this issue May 13, 2015 · 7 comments
Assignees
@asfernandes
Labels
fix-version: 2.5.5 fix-version: 3.0 Beta 2 priority: minor qa: done successfully type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

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.

Commits: a2b2d63 45dbb01 4a48682 2e932a5 FirebirdSQL/fbt-repository@483b08e FirebirdSQL/fbt-repository@5122471 FirebirdSQL/fbt-repository@26e352f FirebirdSQL/fbt-repository@5d4d9bc

====== Test Details ======

Block for 2.5 is simplified vs 3.0: no bigint values as arguments to dateadd().
This test is also related to CORE2174
@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

description:
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

-- no OK.

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.

=>

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 2 [ 10586 ]

Fix Version: 2.5.5 [ 10670 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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 )

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

Test Details: Block for 2.5 is simplified vs 3.0: no bigint values as arguments to dateadd().

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

Test Details: Block for 2.5 is simplified vs 3.0: no bigint values as arguments to dateadd(). => Block for 2.5 is simplified vs 3.0: no bigint values as arguments to dateadd().
This test is also related to CORE2174

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@asfernandes asfernandes

Labels
fix-version: 2.5.5 fix-version: 3.0 Beta 2 priority: minor qa: done successfully type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@asfernandes @firebird-automations