You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by wbecker <wb...@gmail.com> on 2008/01/22 04:38:17 UTC
Generate Always and SQLIntegrityConstraintViolationException
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
--
View this message in context: http://www.nabble.com/Generate-Always-and-SQLIntegrityConstraintViolationException-tp15012038p15012038.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Generate Always and SQLIntegrityConstraintViolationException
Posted by Dy...@Sun.COM.
Daniel John Debrunner <dj...@apache.org> writes:
> Dyre.Tjeldvoll@Sun.COM wrote:
>> Daniel John Debrunner <dj...@apache.org> 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
Re: Generate Always and SQLIntegrityConstraintViolationException
Posted by Daniel John Debrunner <dj...@apache.org>.
Dyre.Tjeldvoll@Sun.COM wrote:
> Daniel John Debrunner <dj...@apache.org> 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.
Re: Generate Always and SQLIntegrityConstraintViolationException
Posted by Dy...@Sun.COM.
Daniel John Debrunner <dj...@apache.org> writes:
> Dyre.Tjeldvoll@Sun.COM 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
Re: Generate Always and SQLIntegrityConstraintViolationException
Posted by Daniel John Debrunner <dj...@apache.org>.
Dyre.Tjeldvoll@Sun.COM 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.
Re: Generate Always and SQLIntegrityConstraintViolationException
Posted by Dy...@Sun.COM.
Dyre.Tjeldvoll@Sun.COM writes:
> Knut Anders Hatlen <Kn...@Sun.COM> writes:
>
>> wbecker <wb...@gmail.com> 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
Re: Generate Always and SQLIntegrityConstraintViolationException
Posted by Dy...@Sun.COM.
Knut Anders Hatlen <Kn...@Sun.COM> writes:
> wbecker <wb...@gmail.com> 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
Re: Generate Always and SQLIntegrityConstraintViolationException
Posted by wbecker <wb...@gmail.com>.
That makes my job easier, cheers!
Knut Anders Hatlen wrote:
>
> wbecker <wb...@gmail.com> 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
>
>
--
View this message in context: http://www.nabble.com/Generate-Always-and-SQLIntegrityConstraintViolationException-tp15012038p15016502.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Generate Always and SQLIntegrityConstraintViolationException
Posted by wbecker <wb...@gmail.com>.
>>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
--
View this message in context: http://www.nabble.com/Generate-Always-and-SQLIntegrityConstraintViolationException-tp15012038p15034167.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Generate Always and SQLIntegrityConstraintViolationException
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
wbecker <wb...@gmail.com> 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
Re: Generate Always and SQLIntegrityConstraintViolationException
Posted by wbecker <wb...@gmail.com>.
>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?
--
View this message in context: http://www.nabble.com/Generate-Always-and-SQLIntegrityConstraintViolationException-tp15012038p15016234.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Generate Always and SQLIntegrityConstraintViolationException
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
wbecker <wb...@gmail.com> 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