Generate Always and SQLIntegrityConstraintViolationException

[wbecker]

I don't understand how this can be happening, but it is:

I've created at a table:

CREATE TABLE Foo (
       id INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1)
     , PRIMARY KEY (id)
);

This works fine and I have added some data to it by using:

INSERT INTO Foo () VALUE (DEFAULT)

This works fine for a bit, but eventually it stops working and tells me:
java.sql.SQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL080122133352260' defined on 'Foo'.

How could I possibly have duplicate values if the only way I add to it is by using the above query in a PreparedStatement?

Now, I have found a way around it, which seems very dodgy. I can just recreate the PreparedStatement and then run the insert again and it works.

Is this a bug, expected behaviour or am I doing something wrong?

Cheers,
Will

[Knut Anders Hatlen]

wbecker <wbecker@...> writes:

> I don't understand how this can be happening, but it is:
>
> I've created at a table:
>
> CREATE TABLE Foo (
>        id INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT
> BY 1)
>      , PRIMARY KEY (id)
> );
>
> This works fine and I have added some data to it by using:
>
> INSERT INTO Foo () VALUE (DEFAULT)
>
> This works fine for a bit, but eventually it stops working and tells me:
> java.sql.SQLIntegrityConstraintViolationException: The statement was aborted
> because it would have caused a duplicate key value in a unique or primary
> key constraint or unique index identified by 'SQL080122133352260' defined on
> 'Foo'.
>
> How could I possibly have duplicate values if the only way I add to it is by
> using the above query in a PreparedStatement?
>
> Now, I have found a way around it, which seems very dodgy. I can just
> recreate the PreparedStatement and then run the insert again and it works.
>
> Is this a bug, expected behaviour or am I doing something wrong?

This looks like a bug to me. It would be great if you could log a bug
report in JIRA (see http://db.apache.org/derby/DerbyBugGuidelines.html
for instructions) so that we can keep track of it.

Thanks,

--
Knut Anders

[wbecker]

>This looks like a bug to me. It would be great if you could log a bug
>report in JIRA (see http://db.apache.org/derby/DerbyBugGuidelines.html
>for instructions) so that we can keep track of it.

Thanks,

I'll try to find a way such that it is easily reproduceable. It only happens after I do a lot of different things while I am using both embedded derby as well as jetty at the same time. Why can't they make bugs easier to make?

[Knut Anders Hatlen]

wbecker <wbecker@...> writes:

>>This looks like a bug to me. It would be great if you could log a bug
>>report in JIRA (see http://db.apache.org/derby/DerbyBugGuidelines.html
>>for instructions) so that we can keep track of it.
>
> Thanks,
>
> I'll try to find a way such that it is easily reproduceable. It only happens
> after I do a lot of different things while I am using both embedded derby as
> well as jetty at the same time. Why can't they make bugs easier to make?

Hi,

It was pretty easily reproducible in ij. The second execution of the
insert statement failed.

ij version 10.4
ij> connect 'jdbc:derby:db;create=true';
ij> create table t (id int primary key generated always as identity);
0 rows inserted/updated/deleted
ij> prepare p as 'insert into t(id) values (default)';
ij> execute p;
1 row inserted/updated/deleted
ij> execute p;
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL080122114921800' defined on 'T'.

Hope this helps,

--
Knut Anders

[wbecker]

That makes my job easier, cheers!

Knut Anders Hatlen wrote:

wbecker <[e-mail]> writes:

>>This looks like a bug to me. It would be great if you could log a bug
>>report in JIRA (see http://db.apache.org/derby/DerbyBugGuidelines.html
>>for instructions) so that we can keep track of it.
>
> Thanks,
>
> I'll try to find a way such that it is easily reproduceable. It only happens
> after I do a lot of different things while I am using both embedded derby as
> well as jetty at the same time. Why can't they make bugs easier to make?

Hi,

It was pretty easily reproducible in ij. The second execution of the
insert statement failed.

ij version 10.4
ij> connect 'jdbc:derby:db;create=true';
ij> create table t (id int primary key generated always as identity);
0 rows inserted/updated/deleted
ij> prepare p as 'insert into t(id) values (default)';
ij> execute p;
1 row inserted/updated/deleted
ij> execute p;
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL080122114921800' defined on 'T'.

Hope this helps,

--
Knut Anders

[Dyre Tjeldvoll]

Knut Anders Hatlen <Knut.Hatlen@...> writes:

> wbecker <wbecker@...> writes:
>
>> I don't understand how this can be happening, but it is:
>>
>> I've created at a table:
>>
>> CREATE TABLE Foo (
>>        id INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT
>> BY 1)
>>      , PRIMARY KEY (id)
>> );
>>
>> This works fine and I have added some data to it by using:
>>
>> INSERT INTO Foo () VALUE (DEFAULT)
>>
>> This works fine for a bit, but eventually it stops working and tells me:
>> java.sql.SQLIntegrityConstraintViolationException: The statement was aborted
>> because it would have caused a duplicate key value in a unique or primary
>> key constraint or unique index identified by 'SQL080122133352260' defined on
>> 'Foo'.
>>
>> How could I possibly have duplicate values if the only way I add to it is by
>> using the above query in a PreparedStatement?
>>
>> Now, I have found a way around it, which seems very dodgy. I can just
>> recreate the PreparedStatement and then run the insert again and it works.
>>
>> Is this a bug, expected behaviour or am I doing something wrong?
>
> This looks like a bug to me. It would be great if you could log a bug
> report in JIRA (see http://db.apache.org/derby/DerbyBugGuidelines.html
> for instructions) so that we can keep track of it.

And you might as well check the regression box right away, since it is a
regression caused by DERBY-827. It works in revision <= 540920.

--
dt

[Dyre Tjeldvoll]

Dyre.Tjeldvoll@... writes:

> Knut Anders Hatlen <Knut.Hatlen@...> writes:
>
>> wbecker <wbecker@...> writes:
>>
>>> I don't understand how this can be happening, but it is:
>>>
>>> I've created at a table:
>>>
>>> CREATE TABLE Foo (
>>>        id INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT
>>> BY 1)
>>>      , PRIMARY KEY (id)
>>> );
>>>
>>> This works fine and I have added some data to it by using:
>>>
>>> INSERT INTO Foo () VALUE (DEFAULT)
>>>
>>> This works fine for a bit, but eventually it stops working and tells me:
>>> java.sql.SQLIntegrityConstraintViolationException: The statement was aborted
>>> because it would have caused a duplicate key value in a unique or primary
>>> key constraint or unique index identified by 'SQL080122133352260' defined on
>>> 'Foo'.
>>>
>>> How could I possibly have duplicate values if the only way I add to it is by
>>> using the above query in a PreparedStatement?
>>>
>>> Now, I have found a way around it, which seems very dodgy. I can just
>>> recreate the PreparedStatement and then run the insert again and it works.
>>>
>>> Is this a bug, expected behaviour or am I doing something wrong?
>>
>> This looks like a bug to me. It would be great if you could log a bug
>> report in JIRA (see http://db.apache.org/derby/DerbyBugGuidelines.html
>> for instructions) so that we can keep track of it.
>
> And you might as well check the regression box right away, since it is a
> regression caused by DERBY-827. It works in revision <= 540920.

I'm currently running the tests with the following patch which seems to
fix the repro:

Index: java/engine/org/apache/derby/impl/sql/execute/RowResultSet.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/execute/RowResultSet.java     (revision 614214)
+++ java/engine/org/apache/derby/impl/sql/execute/RowResultSet.java     (working copy)
@@ -183,6 +183,7 @@
                        if (SanityManager.DEBUG)
                                SanityManager.DEBUG("CloseRepeatInfo","Close of RowResultSet repeated");
 
+        cachedRow = null;
                closeTime += getElapsedMillis(beginTime);
        }


If the tests pass, I'm ready to attach it to a Jira issue.

--
dt

[Daniel John Debrunner-2]

Dyre.Tjeldvoll@... wrote:

> I'm currently running the tests with the following patch which seems to
> fix the repro:
>
> Index: java/engine/org/apache/derby/impl/sql/execute/RowResultSet.java
> ===================================================================
> --- java/engine/org/apache/derby/impl/sql/execute/RowResultSet.java     (revision 614214)
> +++ java/engine/org/apache/derby/impl/sql/execute/RowResultSet.java     (working copy)
> @@ -183,6 +183,7 @@
>                         if (SanityManager.DEBUG)
>                                 SanityManager.DEBUG("CloseRepeatInfo","Close of RowResultSet repeated");
>  
> +        cachedRow = null;
>                 closeTime += getElapsedMillis(beginTime);
>         }
>
>
> If the tests pass, I'm ready to attach it to a Jira issue.

That just defeats the purpose of cachedRow, which is the ability to
re-use the same result across multiple executions (opens).

I think the real bug is that canCacheRow is being passed in as true for
the row of (default) when it should be false if the default column
definition does not translate to a constant over time. With a default of
CURRENT TIMESTAMP does the same problem occur?

Dan.

[Dyre Tjeldvoll]

Daniel John Debrunner <djd@...> writes:

> Dyre.Tjeldvoll@... wrote:
>
>> I'm currently running the tests with the following patch which seems to
>> fix the repro:
>>
>> Index: java/engine/org/apache/derby/impl/sql/execute/RowResultSet.java
>> ===================================================================
>> --- java/engine/org/apache/derby/impl/sql/execute/RowResultSet.java     (revision 614214)
>> +++ java/engine/org/apache/derby/impl/sql/execute/RowResultSet.java     (working copy)
>> @@ -183,6 +183,7 @@
>>                         if (SanityManager.DEBUG)
>>                                 SanityManager.DEBUG("CloseRepeatInfo","Close of RowResultSet repeated");
>>  +        cachedRow = null;
>>                 closeTime += getElapsedMillis(beginTime);
>>         }
>>
>>
>> If the tests pass, I'm ready to attach it to a Jira issue.
>
> That just defeats the purpose of cachedRow, which is the ability to
> re-use the same result across multiple executions (opens).

Bummer...

> I think the real bug is that canCacheRow is being passed in as true
> for the row of (default) when it should be false if the default column
> definition does not translate to a constant over time.

So the compiler actually generates incorrect parameters for the
RowResultSet constructor in this case?

> With a default of CURRENT TIMESTAMP does the same problem occur?

Nope. Here is the result using an unpatched trunk:

ij version 10.4
ij> connect 'jdbc:derby:/tmp/db4;create=true';
ij> create table time(ts timestamp with default CURRENT_TIMESTAMP);
0 rows inserted/updated/deleted
ij> prepare p as 'insert into time(ts) values (default)';
ij> execute p;
1 row inserted/updated/deleted
ij> execute p;
1 row inserted/updated/deleted
ij> execute p;
1 row inserted/updated/deleted
ij> select * from time;
TS                        
--------------------------
2008-01-22 18:00:48.944  
2008-01-22 18:00:48.951  
2008-01-22 18:00:48.952  

3 rows selected


But I see in the debugger that canCacheRow is false in this case,
whereas it was true in previous case...

--
dt

[Daniel John Debrunner-2]

Dyre.Tjeldvoll@... wrote:
> Daniel John Debrunner <djd@...> writes:
>> I think the real bug is that canCacheRow is being passed in as true
>> for the row of (default) when it should be false if the default column
>> definition does not translate to a constant over time.
>
> So the compiler actually generates incorrect parameters for the
> RowResultSet constructor in this case?

That's my guess.

Dan.

[Dyre Tjeldvoll]

Daniel John Debrunner <djd@...> writes:

> Dyre.Tjeldvoll@... wrote:
>> Daniel John Debrunner <djd@...> writes:
>>> I think the real bug is that canCacheRow is being passed in as true
>>> for the row of (default) when it should be false if the default column
>>> definition does not translate to a constant over time.
>>
>> So the compiler actually generates incorrect parameters for the
>> RowResultSet constructor in this case?
>
> That's my guess.

Good guess :)

I've traced it to RowResultSetNode.canWeCacheResults() (no
big surprise) which returns false in the CURRENT_TIMESTAMP case, and
true in the identity case.

canWeCacheResults() use a rather tricky visitor pattern, but I think it
ends up returning true because the ResultColumn.expression points to a
NumericConstantNode, and NumericConstantNode.getOrderableVariantType()
(actually inherited from ConstantNode)
returns Qualifier.CONSTANT.

CurrentDatetimeOperatorNode.getOrderableVariantType() on the other hand
returns Qualifier.QUERY_INVARIANT, which is then transformed to
Qualifier.SCAN_INVARIANT in ResultColumn.getOrderableVariantType().

Not sure where the default identity information should be detected in
all of this though...


--
dt

[wbecker]

>>This looks like a bug to me. It would be great if you could log a bug
>>report in JIRA (see http://db.apache.org/derby/DerbyBugGuidelines.html
>>for instructions) so that we can keep track of it.
>

I created the bug for this here:
https://issues.apache.org/jira/browse/DERBY-3343

Cheers,
Will