db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r803336 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ResultColumnList.java testing/org/apache/derbyTesting/functionTests/tests/lang/CheckConstraintTest.java
Date Wed, 12 Aug 2009 00:48:12 GMT
Author: bpendleton
Date: Wed Aug 12 00:48:11 2009
New Revision: 803336

URL: http://svn.apache.org/viewvc?rev=803336&view=rev
Log:
DERBY-4282: Strange behavior with "where current of" in CheckConstraintTest

This patch was contributed by Eranda Sooriyabandara (070468D at gmail dot com)

This patch addresses an issue involving the binding of check constraints
in queries which use the "where current of" construction to refer to the
current row of an open cursor. The update statement was failing with a
"column not found" error, complaining about the column referred to by the
check constraint.

The problem arises in the processing of ResultColumnList.expandToAll, which
is used to construct ResultColumn instances for the columns in the table;
these result column instances are then used when the check constraint
is bound. ResultColumnList.java was constructing the ResultColumn instances
as unnamed, which caused an internal name "SQLCOL1" to be generated, which
later then caused the check constraint binding to fail.

The solution is simply to ensure that the ResultColumn references that are
built by expandToAll are named according to the column names.

The patch also adds several new test cases.


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

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?rev=803336&r1=803335&r2=803336&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
Wed Aug 12 00:48:11 2009
@@ -4057,7 +4057,7 @@
 		ResultColumn	rc = (ResultColumn) nodeFactory.getNode
 			(
 				C_NodeTypes.RESULT_COLUMN,
-				null,
+				columnName,
 				nodeFactory.getNode
 				(
 					C_NodeTypes.COLUMN_REFERENCE,

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CheckConstraintTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CheckConstraintTest.java?rev=803336&r1=803335&r2=803336&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CheckConstraintTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CheckConstraintTest.java
Wed Aug 12 00:48:11 2009
@@ -424,88 +424,59 @@
         st.executeUpdate(
             " create index i1 on t1(c1)");
         
-        /* DERBY-4282  after fixing the issue DERBY-4282 this commented
-         * part can be uncomment
-           
-        PreparedStatement ps_c1 = prepareStatement(
-            "select * from t1 where c2 = 2 for update of c1");
-        
-        ResultSet c1 = ps_c1.executeQuery();
-        
-        //c1.next();  
-        
-        expRS=new String[][]{
-            {"2","2"}
-        };
-        JDBC.assertFullResultSet(c1, expRS);
-
+        Statement st1 = conn.createStatement();
+        st1.setCursorName("c1");
+        ResultSet rs1 = st1.executeQuery(
+                "select * from t1 where c2=2 for update of C1");
+        rs1.next();   
         setAutoCommit(false);
+        
         // this update should succeed
-             
-        assertUpdateCount(st, 1,  
-            "update t1 set c1 = c1 where current of c1");
-        //setAutoCommit(true);        
-        // this update should fail
+        assertUpdateCount(st,1,
+                "update t1 set c1 = c1 where current of \"c1\"");
         
+        // this update should fail
         assertStatementError("23513", st,
-            "update t1 set c1 = c1 + 1 where current of c1");
-        
-        c1.close();
-        ps_c1.close();
-        
-        PreparedStatement ps_c2 = prepareStatement(
-            "select * from t1 where c1 = 2 for update of c2");
-        
-        ResultSet c2 = ps_c2.executeQuery();
-        
-        c2.next(); 
-        
-        expRS=new String[][]{
-            {"2","2"}
-        };
-        JDBC.assertFullResultSet(c2, expRS);
-
-        
+            "update t1 set c1 = c1 + 1 where current of \"c1\"");
+        st1.close();
+        rs1.close();
+        
+        Statement st2 = conn.createStatement();
+        st2.setCursorName("c2");
+        ResultSet rs2 = st2.executeQuery(
+                "select * from t1 where c1 = 2 for update of c2");
+        rs2.next();   
+        setAutoCommit(false);
         // this update should succeed
-        
-        assertUpdateCount(st, 1,
-            "update t1 set c2 = c2 where current of c2");
+        assertUpdateCount(st,1,
+                "update t1 set c2 = c2 where current of \"c2\"");
         
         // this update should fail
-        
         assertStatementError("23513", st,
-            "update t1 set c2 = c2 + 1 where current of c2");
-        
-        c2.close();
-        ps_c2.close();
-        
-        PreparedStatement ps_c3 = prepareStatement(
-            "select * from t1 where c1 = 2 for update of c1, c2");
-        
-        ResultSet c3 = ps_c3.executeQuery();
-        
-        c3.next(); 
-        
-        expRS=new String[][]{
-            {"2","2"}
-        };
-        JDBC.assertFullResultSet(c3, expRS);
-
+            "update t1 set c2 = c2 + 1 where current of \"c2\"");
+        st2.close();
+        rs2.close();
+        
+        Statement st3 = conn.createStatement();
+        st3.setCursorName("c3");
+        ResultSet rs3 = st3.executeQuery(
+                "select * from t1 where c1 = 2 for update of c1, c2");
+        rs3.next();   
+        setAutoCommit(false);
         
         // this update should succeed
-        
         assertUpdateCount(st, 1,
-            "update t1 set c2 = c1, c1 = c2 where current of c3");
+            "update t1 set c2 = c1, c1 = c2 where current of \"c3\"");
         
         // this update should fail
-        
         assertStatementError("23513", st,
-            "update t1 set c2 = c2 + 1, c1 = c1 + 3 where current of c3");
+            "update t1 set c2 = c2 + 1, c1 = c1 + 3 where current of \"c3\"");
         
         // this update should succeed
-        
         assertUpdateCount(st, 1,
-            "update t1 set c2 = c1 + 3, c1 = c2 + 3 where current of c3");
+            "update t1 set c2 = c1 + 3, c1 = c2 + 3 where current of \"c3\"");
+        st3.close();
+        rs3.close();
         
         rs = st.executeQuery(
             " select * from t1");
@@ -523,9 +494,6 @@
         
         JDBC.assertFullResultSet(rs, expRS, true);
         
-        c3.close();
-        ps_c3.close();
-*/       
         conn.rollback();
         
         // complex expressions
@@ -892,4 +860,41 @@
         getConnection().rollback();
         st.close();
     }
-}
\ No newline at end of file
+    public void testJira4282() throws SQLException
+    {
+        // This test doesnt work properly in the embedded configuration.
+        // The intent of the test is to expose the DERBY-4282 problem, and
+        // this test case does do that in the client/server configuration, so
+        // we only run the test in that configuration. In the embedded
+        // configuration, the UPDATE statement unexpectedly gets a 
+        // "no current row" exception.
+        //
+        if (usingEmbedded())
+            return;
+
+        st = createStatement();
+
+        st.executeUpdate(
+            "create table t4282(c1 int, c2 int, constraint ck1 "
+            + "check(c1 = c2), constraint ck2 check(c2=c1))");
+
+        st.executeUpdate("insert into t4282 values (1,1),(2,2),(3,3),(4,4)");
+
+        Statement st1 = createStatement();
+        st1.setCursorName("c1");
+        ResultSet rs = st1.executeQuery("select * from t4282 for update");
+        assertTrue("Failed to retrieve row for update", rs.next());
+        // DERBY-4282 causes the next statement to fail with:
+        //
+        // Column 'C2' is either not in any table in the FROM list or
+        // appears within a join specification and is outside the scope
+        // of the join specification or appears in a HAVING clause and
+        // is not in the GROUP BY list. If this is a CREATE or ALTER TABLE
+        // statement then 'C2' is not a column in the target table. 
+        st.executeUpdate("update t4282 set c1 = c1 where current of \"c1\"");
+
+        // If we get here, all is well, and DERBY-4282 did not occur.
+        st1.close();
+        st.close();
+    }
+}



Mime
View raw message