You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Myrna van Lunteren <m....@gmail.com> on 2005/11/25 17:08:05 UTC
fn timestampdiff gives error...
Hi,
Is this a bug? It seems to me it is...
In ij:
ij> create table tab1(TMSTCOL1 TIMESTAMP, TMSTCOL2 TIMESTAMP);
0 rows inserted/updated/deleted
ij> INSERT INTO tab1 VALUES ({ts '1994-06-12 11:20:10'},{ts '1996-05-10
10:07:05'});
1 row inserted/updated/deleted
ij> SELECT {FN TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND,TMSTCOL1,TMSTCOL2)} FROM
tab1;
1
-----------
ERROR 22003: The resulting value is outside the range for the data type
INTEGER.
Thx,
Myrna
Re: fn timestampdiff gives error...
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Myrna van Lunteren wrote:
>
> Well, I can see that smaller differences are tested (test
> lang/timestampArith.java) but these values were pulled out of Sun's
> (jdbc) compliance test suite, and I thought one shouldn't modify those
> values? Lance, you may know more about that detail...
>
> Should timestampdiff be able to handle the range of the two timestamps
> in this particular query?
These tests would only get run by the test suite if it shows the
function being supported by the backend.
I have not done the math to determine if the values are in range. The
dml is allowed to be modified once assuming the issue is a range issue
based on what a given backend supports
>
> Myrna
>
> On 11/28/05, *Lance J. Andersen* <Lance.Andersen@sun.com
> <ma...@sun.com>> wrote:
>
> Myrna,
>
> Try having the timestamp values closer together and give it a go.
>
> -lance
>
>
> Myrna van Lunteren wrote:
>
>> Hi,
>>
>> Is this a bug? It seems to me it is...
>>
>> In ij:
>> ij> create table tab1(TMSTCOL1 TIMESTAMP, TMSTCOL2 TIMESTAMP);
>> 0 rows inserted/updated/deleted
>> ij> INSERT INTO tab1 VALUES ({ts '1994-06-12 11:20:10'},{ts
>> '1996-05-10 10:07:05'});
>> 1 row inserted/updated/deleted
>> ij> SELECT {FN
>> TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND,TMSTCOL1,TMSTCOL2)} FROM tab1;
>> 1
>> -----------
>> ERROR 22003: The resulting value is outside the range for the
>> data type INTEGER.
>>
>> Thx,
>> Myrna
>
>
Re: fn timestampdiff gives error...
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Daniel John Debrunner wrote:
>Lance J. Andersen wrote:
>
>
>
>>JDBC/ODBC define that the return type range for the TIMESTAMPDIFF
>>function is an INTEGER regardless of what the backend engine supports.
>>
>>
>
>Just to be picky, do you actually see 'INTEGER' anywhere, or just 'integer'?
>
>
Interesting question which sadly is not clearly articulated anywhere so
i am afraid milage may vary. Guess I will clarify this for JDBC 4
>I would see SQL BIGINT, Types.BIGINT and long as all of type integer.
>
>Because if 'integer' means only SQL INTEGER or Types.INTEGER, then the
>numeric functions floor and mod should not work against BIGINT values.
>
>I'd always assummed that these types in appendix C of JDBC 3.0 meant:
>
>integer - any integral type
>float - any imprecise type
>number - any number type
>
>Dan.
>
>
>
>
Re: fn timestampdiff gives error...
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Lance J. Andersen wrote:
> JDBC/ODBC define that the return type range for the TIMESTAMPDIFF
> function is an INTEGER regardless of what the backend engine supports.
Just to be picky, do you actually see 'INTEGER' anywhere, or just 'integer'?
I would see SQL BIGINT, Types.BIGINT and long as all of type integer.
Because if 'integer' means only SQL INTEGER or Types.INTEGER, then the
numeric functions floor and mod should not work against BIGINT values.
I'd always assummed that these types in appendix C of JDBC 3.0 meant:
integer - any integral type
float - any imprecise type
number - any number type
Dan.
Re: fn timestampdiff gives error...
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Daniel John Debrunner wrote:
>Myrna van Lunteren wrote:
>
>
>>Well, I can see that smaller differences are tested (test
>>lang/timestampArith.java) but these values were pulled out of Sun's
>>(jdbc) compliance test suite, and I thought one shouldn't modify those
>>values? Lance, you may know more about that detail...
>>
>>Should timestampdiff be able to handle the range of the two timestamps
>>in this particular query?
>>
>>
>
>So one year is roughly 3.1e7 seconds, which is 3.1e16 fractions of a
>second. ODBC defines SQL_TSI_FRAC_SECOND as " fractional seconds are
>expressed in billionths of a second". I assume this is US billion, 1e-9.
>
>So 2 years (roughly the example you have) should be 6.2e16, outside the
>range for an INTEGER but within the range for a BIGINT.
>
>Is this function always returning an INTEGER?
>
>My guess is that it's up to the engine to define the range it handles,
>not ODBC or JDBC.
>
>
JDBC/ODBC define that the return type range for the TIMESTAMPDIFF
function is an INTEGER regardless of what the backend engine supports.
>Dan.
>
>
>
>>Myrna
>>
>>On 11/28/05, *Lance J. Andersen* <Lance.Andersen@sun.com
>><ma...@sun.com>> wrote:
>>
>> Myrna,
>>
>> Try having the timestamp values closer together and give it a go.
>>
>> -lance
>>
>>
>> Myrna van Lunteren wrote:
>>
>>
>>
>>> Hi,
>>>
>>> Is this a bug? It seems to me it is...
>>>
>>> In ij:
>>> ij> create table tab1(TMSTCOL1 TIMESTAMP, TMSTCOL2 TIMESTAMP);
>>> 0 rows inserted/updated/deleted
>>> ij> INSERT INTO tab1 VALUES ({ts '1994-06-12 11:20:10'},{ts
>>> '1996-05-10 10:07:05'});
>>> 1 row inserted/updated/deleted
>>> ij> SELECT {FN
>>> TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND,TMSTCOL1,TMSTCOL2)} FROM tab1;
>>> 1
>>> -----------
>>> ERROR 22003: The resulting value is outside the range for the data
>>> type INTEGER.
>>>
>>> Thx,
>>> Myrna
>>>
>>>
>>
>>
>
>
>
>
Re: fn timestampdiff gives error...
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Daniel John Debrunner wrote:
>Daniel John Debrunner wrote:
>
>
>
>
>>Is this function always returning an INTEGER?
>>
>>My guess is that it's up to the engine to define the range it handles,
>>not ODBC or JDBC.
>>
>>
>
>Though maybe I'm wrong, ODBC does say:
>
>"Returns the integer number of intervals of type interval"
>
>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbctime__date__and_interval_functions.asp
>
>Thus, the example in the test would be out of range for any conforming
>implementation.
>
>
The dml for the tests in question are allowed to be modified allowing
for range issues for a given backend.
The tests check to see if an implementation indicates it supports the
function and if it does not, the tests pass as you are not required to
support this function.
So the best thing to do is tweak the dml and rerun the test in this suite.
>Dan.
>
>
>
>
Re: fn timestampdiff gives error...
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Daniel John Debrunner wrote:
> Is this function always returning an INTEGER?
>
> My guess is that it's up to the engine to define the range it handles,
> not ODBC or JDBC.
Though maybe I'm wrong, ODBC does say:
"Returns the integer number of intervals of type interval"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbctime__date__and_interval_functions.asp
Thus, the example in the test would be out of range for any conforming
implementation.
Dan.
Re: fn timestampdiff gives error...
Posted by Myrna van Lunteren <m....@gmail.com>.
Thanks all, for your input, I'll go tweak those values.
Myrna
On 11/28/05, Satheesh Bandaram <sa...@sourcery.org> wrote:
>
> Yes, Timestampdiff is coded to return an INT, which has the following
> range. May be it could return a BIGINT if argument is of type
> SQL_TSI_FRAC_SECOND. INT
> 2147483647 (ava.lang.Integer.MAX_VALUE)
> -2147483648 (java.lang.Integer.MIN_VALUE)
>
> So, the query is exceeding the range.
>
> Satheesh
>
> Daniel John Debrunner wrote:
>
> Myrna van Lunteren wrote:
>
>
> Well, I can see that smaller differences are tested (test
> lang/timestampArith.java) but these values were pulled out of Sun's
> (jdbc) compliance test suite, and I thought one shouldn't modify those
> values? Lance, you may know more about that detail...
>
> Should timestampdiff be able to handle the range of the two timestamps
> in this particular query?
>
>
> So one year is roughly 3.1e7 seconds, which is 3.1e16 fractions of a
> second. ODBC defines SQL_TSI_FRAC_SECOND as " fractional seconds are
> expressed in billionths of a second". I assume this is US billion, 1e-9.
>
> So 2 years (roughly the example you have) should be 6.2e16, outside the
> range for an INTEGER but within the range for a BIGINT.
>
> Is this function always returning an INTEGER?
>
> My guess is that it's up to the engine to define the range it handles,
> not ODBC or JDBC.
>
> Dan.
>
>
>
> Myrna
>
> On 11/28/05, *Lance J. Andersen* <La...@sun.com> <La...@sun.com>> wrote:
>
> Myrna,
>
> Try having the timestamp values closer together and give it a go.
>
> -lance
>
>
> Myrna van Lunteren wrote:
>
>
>
> Hi,
>
> Is this a bug? It seems to me it is...
>
> In ij:
> ij> create table tab1(TMSTCOL1 TIMESTAMP, TMSTCOL2 TIMESTAMP);
> 0 rows inserted/updated/deleted
> ij> INSERT INTO tab1 VALUES ({ts '1994-06-12 11:20:10'},{ts
> '1996-05-10 10:07:05'});
> 1 row inserted/updated/deleted
> ij> SELECT {FN
> TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND,TMSTCOL1,TMSTCOL2)} FROM tab1;
> 1
> -----------
> ERROR 22003: The resulting value is outside the range for the data
> type INTEGER.
>
> Thx,
> Myrna
>
>
>
>
>
Re: fn timestampdiff gives error...
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Myrna van Lunteren wrote:
>
> Well, I can see that smaller differences are tested (test
> lang/timestampArith.java) but these values were pulled out of Sun's
> (jdbc) compliance test suite, and I thought one shouldn't modify those
> values? Lance, you may know more about that detail...
>
> Should timestampdiff be able to handle the range of the two timestamps
> in this particular query?
So one year is roughly 3.1e7 seconds, which is 3.1e16 fractions of a
second. ODBC defines SQL_TSI_FRAC_SECOND as " fractional seconds are
expressed in billionths of a second". I assume this is US billion, 1e-9.
So 2 years (roughly the example you have) should be 6.2e16, outside the
range for an INTEGER but within the range for a BIGINT.
Is this function always returning an INTEGER?
My guess is that it's up to the engine to define the range it handles,
not ODBC or JDBC.
Dan.
> Myrna
>
> On 11/28/05, *Lance J. Andersen* <Lance.Andersen@sun.com
> <ma...@sun.com>> wrote:
>
> Myrna,
>
> Try having the timestamp values closer together and give it a go.
>
> -lance
>
>
> Myrna van Lunteren wrote:
>
>> Hi,
>>
>> Is this a bug? It seems to me it is...
>>
>> In ij:
>> ij> create table tab1(TMSTCOL1 TIMESTAMP, TMSTCOL2 TIMESTAMP);
>> 0 rows inserted/updated/deleted
>> ij> INSERT INTO tab1 VALUES ({ts '1994-06-12 11:20:10'},{ts
>> '1996-05-10 10:07:05'});
>> 1 row inserted/updated/deleted
>> ij> SELECT {FN
>> TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND,TMSTCOL1,TMSTCOL2)} FROM tab1;
>> 1
>> -----------
>> ERROR 22003: The resulting value is outside the range for the data
>> type INTEGER.
>>
>> Thx,
>> Myrna
>
>
Re: fn timestampdiff gives error...
Posted by Myrna van Lunteren <m....@gmail.com>.
Well, I can see that smaller differences are tested (test
lang/timestampArith.java) but these values were pulled out of Sun's (jdbc)
compliance test suite, and I thought one shouldn't modify those values?
Lance, you may know more about that detail...
Should timestampdiff be able to handle the range of the two timestamps in
this particular query?
Myrna
On 11/28/05, Lance J. Andersen <La...@sun.com> wrote:
>
> Myrna,
>
> Try having the timestamp values closer together and give it a go.
>
> -lance
>
> Myrna van Lunteren wrote:
>
> Hi,
>
> Is this a bug? It seems to me it is...
>
> In ij:
> ij> create table tab1(TMSTCOL1 TIMESTAMP, TMSTCOL2 TIMESTAMP);
> 0 rows inserted/updated/deleted
> ij> INSERT INTO tab1 VALUES ({ts '1994-06-12 11:20:10'},{ts '1996-05-10
> 10:07:05'});
> 1 row inserted/updated/deleted
> ij> SELECT {FN TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND,TMSTCOL1,TMSTCOL2)} FROM
> tab1;
> 1
> -----------
> ERROR 22003: The resulting value is outside the range for the data type
> INTEGER.
>
> Thx,
> Myrna
>
>
Re: fn timestampdiff gives error...
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Myrna,
Try having the timestamp values closer together and give it a go.
-lance
Myrna van Lunteren wrote:
> Hi,
>
> Is this a bug? It seems to me it is...
>
> In ij:
> ij> create table tab1(TMSTCOL1 TIMESTAMP, TMSTCOL2 TIMESTAMP);
> 0 rows inserted/updated/deleted
> ij> INSERT INTO tab1 VALUES ({ts '1994-06-12 11:20:10'},{ts
> '1996-05-10 10:07:05'});
> 1 row inserted/updated/deleted
> ij> SELECT {FN TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND,TMSTCOL1,TMSTCOL2)}
> FROM tab1;
> 1
> -----------
> ERROR 22003: The resulting value is outside the range for the data
> type INTEGER.
>
> Thx,
> Myrna