[DERBY-4803] Sequences do not work in INSERT/SELECT

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.6.1.0
• Fix Version/s: 10.7.1.1
• Component/s: SQL
• Environment: Mac OS X, Derby Network Server 10.6.1.0
• Urgency: Normal
• Issue & fix info: Repro attached
• Assignee: Richard N. Hillegas
• Reporter: Dima Korobskiy
• Created: 16/Sep/10 19:59
• Updated: 17/Jun/13 09:19
• Resolved: 22/Sep/10 12:42

Description

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.

Issue Links

• is related to: DERBY-4513 Forbid NEXT VALUE FOR clause in certain contexts