Issue Details (XML | Word | Printable)

Key: CORE-152
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Claudio Valderrama C.
Reporter: Bill Oliver
Votes: 0
Watchers: 0
Operations

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

Sqlsubtype incorrect on timestamp math, constant arithmetic

Created: 30/May/06 12:00 AM   Updated: 29/May/15 05:01 PM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 2.1 Alpha 1

Issue Links:
Relate
 

SF_ID: 1497603
QA Status: Done successfully


 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Bill Oliver added a comment - 31/Aug/06 11:31 AM
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.

Pavel Cisar added a comment - 21/Apr/07 01:55 PM
Fix confirmed for 2.1 Alpha 1. Test added.

Pavel Cisar added a comment - 26/Apr/07 11:41 AM
Reopened to update ticket information.