[
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.
