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

Sqlsubtype incorrect on timestamp math, constant arithmetic [CORE152] #481

Closed
firebird-automations opened this issue May 30, 2006 · 13 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Bill Oliver (verbguy)

Assigned to: Claudio Valderrama C. (robocop)

Is related to QA72

SFID: 1497603#⁠
Submitted By: verbguy

Sqlsubtype isn't being set correctly when timestamp
math is performed. In this case, the result is
defined as NUMERIC(18,9), and the sqlsubtype should
be 1. Claudio confirmed this with the following
elegant test case:

SQL> set sqlda_display;
SQL> select current_timestamp - current_timestamp
from rdb$database;

INPUT SQLDA version: 1 sqln: 10 sqld: 0

OUTPUT SQLDA version: 1 sqln: 20 sqld: 1
01: sqltype: 580 INT64 sqlscale: -9
sqlsubtype: 0 sqllen: 8
: name: (0) alias: (0)
: table: (0) owner: (0)

=====================
0.000000000

He also tested this against original Firebird 1.0,
and believes this to be present in all versions of
Firebird. Certainly it is present in Vulcan.

Claudio also said:
And yes, the sqlsubtype should be set, depending on
whether we consider the result numeric or decimal.
Probably Borland was thinking in implementing the
interval data type or they simply forgot the detail.

Claudio also provided this comment for constant
arithmetic:

Should we set the sub_type for constants?

SQL> select cast(1.2 as numeric(2,1)) from
rdb$database;

INPUT SQLDA version: 1 sqln: 10 sqld: 0

OUTPUT SQLDA version: 1 sqln: 20 sqld: 1
01: sqltype: 500 SHORT sqlscale: -1
sqlsubtype: 1 sqllen:
2
: name: (4)CAST alias: (4)CAST
: table: (0) owner: (0)

CAST

1\.2

Ok, an explicit cast forced the sub_type.

SQL> select cast(1.2 as numeric(2,1)) - 1.2 from
rdb$database;

INPUT SQLDA version: 1 sqln: 10 sqld: 0

OUTPUT SQLDA version: 1 sqln: 20 sqld: 1
01: sqltype: 580 INT64 sqlscale: -1
sqlsubtype: 0 sqllen:
8
: name: (0) alias: (0)
: table: (0) owner: (0)

=====================
0.0

This depends on (1). If the constant is assigned a
sub_type, I don't see why this subtraction wouldn't
preserve it. We simply set the sub_type to zero in
the relevant code.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Claudio Valderrama C. [ robocop ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.1 [ 10041 ]

SF_ID: 1497603 =>

@firebird-automations
Copy link
Collaborator Author

Commented by: Bill Oliver (verbguy)

This has been fixed in FB head. I also back-ported Claudio's changes to Vulcan. Timestamp subtraction now appears correct WRT the subtype.

On a related note, sub_type of literal numbers may need to be resolved one day. Claudio points out:

I mean we should resolve some day what's the sub_type (if any) of this:

select 1.5 from rdb$database;
-> no subtype now

select 1.5 + cast(1.5 as numeric(5,1)) from rdb$database
-> the cast activates the subtype, but the sum with the literal wipes it out.

That can be taken up at a later date if desired.

@firebird-automations
Copy link
Collaborator Author

Modified by: Bill Oliver (verbguy)

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

resolution: Fixed [ 1 ]

SF_ID: 1497603 =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Fix confirmed for 2.1 Alpha 1. Test added.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

SF_ID: 1497603 =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Reopened to update ticket information.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Closed [ 6 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Fix Version: 2.1 Alpha 1 [ 10150 ]

SF_ID: 1497603 =>

Fix Version: 2.1.0 [ 10041 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Reopened [ 4 ] => Closed [ 6 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA72 [ QA72 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 10176 ] => Firebird [ 14391 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant