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] Commented: (DERBY-4803) Sequences do not work in INSERT/SELECT
Date Thu, 16 Sep 2010 20:14:33 GMT

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


Mime
View raw message