[jira] Commented: (DERBY-4803) Sequences do not work in INSERT/SELECT

["Rick Hillegas (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-4803?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12910295#action_12910295 ] 

Rick Hillegas commented on DERBY-4803:
--------------------------------------

The following script shows this behavior:

connect 'jdbc:derby:memory:db;create=true';

create sequence sequence1;

create table t1( a int );
create table t2( a int, b int );

insert into t1( a ) values ( 1 );

insert into t2
select next value for sequence1, a from t1;


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