svn commit: r999908 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java

[rh...@apache.org]

Author: rhillegas
Date: Wed Sep 22 12:37:32 2010
New Revision: 999908

URL: http://svn.apache.org/viewvc?rev=999908&view=rev
Log:
DERBY-4803: Make it possible to use sequences in INSERT...SELECT statements.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java?rev=999908&r1=999907&r2=999908&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java Wed Sep 22 12:37:32 2010
@@ -69,6 +69,14 @@ public class NextSequenceNode extends Va
             Vector aggregateVector, boolean forQueryRewrite)
             throws StandardException
     {
+        //
+        // Higher level bind() logic may try to redundantly re-bind this node. Unfortunately,
+        // that causes us to think that the sequence is being referenced more than once
+        // in the same statement. If the sequence generator is already filled in, then
+        // this node has already been bound and we can exit quickly. See DERBY-4803.
+        //
+        if ( sequenceDescriptor != null ) { return this; }
+        
         CompilerContext cc = getCompilerContext();
         
         if ( (cc.getReliability() & CompilerContext.NEXT_VALUE_FOR_ILLEGAL) != 0 )

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java?rev=999908&r1=999907&r2=999908&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java Wed Sep 22 12:37:32 2010
@@ -518,5 +518,31 @@ public class SequenceTest extends Genera
 
     }
 
+    /**
+     * Verify that you can use sequences in insert statements driven
+     * by selects. See DERBY-4803.
+     */
+    public void test_14_insertSelect() throws Exception
+    {
+        Connection conn = openUserConnection(ALPHA);
 
+        goodStatement( conn, "create sequence sequence_is" );
+        goodStatement( conn, "create table tis_1( a int )" );
+        goodStatement( conn, "create table tis_2( a int, b int )" );
+        goodStatement( conn, "insert into tis_1( a ) values ( 1 ), ( 2 )" );
+        goodStatement( conn, "insert into tis_2 select next value for sequence_is, a from tis_1" );
+
+        assertResults
+            (
+             conn,
+             "select * from tis_2 order by b",
+             new String[][]
+             {
+                 { "-2147483648", "1" },
+                 { "-2147483647", "2" },
+             },
+             true
+             );
+    }
+    
 }