db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-4803) Sequences do not work in INSERT/SELECT
Date Tue, 21 Sep 2010 18:58:36 GMT

     [ https://issues.apache.org/jira/browse/DERBY-4803?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Rick Hillegas updated DERBY-4803:
---------------------------------

    Attachment: derby-4803-01-aa-simpleInsertSelect.diff

Attaching derby-4803-01-aa-simpleInsertSelect.diff. This fixes the bug for a simple insert/select
case. The pre-existing test cases in SequenceTest pass cleanly. I will run the full regression
test suite.

The problem is that the NextSequenceNode is needlessly re-bound for INSERT...SELECT statements.
On the second attempt to bind the statement, we discover that the sequence has already been
seen and this triggers the overly conservative check for illegal conditions discussed in DERBY-4513.
The fix is to short-circuit the second, needless binding of the NextSequenceNode.

DK, could you post the INSERT statement which disclosed this bug? I would like to verify whether
this patch fixes your problem or whether I have merely fixed a related test case. Thanks.

Touches the following files:

------

M      java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java

Short-circuit redundant bindings of NEXT VALUE FOR clauses.

------


M      java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java

Test-case for this bug.


> Sequences do not work in INSERT/SELECT
> --------------------------------------
>
>                 Key: DERBY-4803
>                 URL: https://issues.apache.org/jira/browse/DERBY-4803
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.6.1.0
>         Environment: Mac OS X, Derby Network Server 10.6.1.0
>            Reporter: DK
>         Attachments: derby-4803-01-aa-simpleInsertSelect.diff
>
>
> Using sequence in SELECT works fine whereas the same SELECT query used in INSERT/SELECT
results in "The statement references the following sequence more than once" error. This happens
even though the SELECT in question returns exactly 1 row of data.
> The Reference Manual states 1. " NEXT VALUE FOR expression may occur in the following
places: SELECT statement: As part of the expression defining a returned column in a SELECT
list" and 2. " NEXT VALUE expression may not appear in any of these situations: CASE expression,
WHERE clause, 
> ORDER BY clause, Aggregate expression, ROW_NUMBER function, DISTINCT select list".
> Nowhere a restriction on INSERT/SELECT is mentioned. Additionally, other databases (i.e.
Oracle) support use of sequences in INSERT/SELECT.
> Therefore, I consider it a bug.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message