db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r1532666 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/conn/GenericStatementContext.java testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java
Date Wed, 16 Oct 2013 07:36:11 GMT
Author: kahatlen
Date: Wed Oct 16 07:36:10 2013
New Revision: 1532666

URL: http://svn.apache.org/r1532666
Log:
DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements

Fix incorrect null check when merging subqueryTrackingArray and
materializedSubqueries in GenericStatementContext.setTopResultSet().
Used to cause NullPointerException in some cases when a WHEN clause
contained a subquery.

Add more tests for scalar subqueries in WHEN clauses.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/conn/GenericStatementContext.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/conn/GenericStatementContext.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/conn/GenericStatementContext.java?rev=1532666&r1=1532665&r2=1532666&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/conn/GenericStatementContext.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/conn/GenericStatementContext.java
Wed Oct 16 07:36:10 2013
@@ -353,7 +353,7 @@ final class GenericStatementContext 
 				}
 				for (int index = 0; index < subqueryTrackingArray.length; index++)
 				{
-					if (this.subqueryTrackingArray[index] != null)
+                    if (this.materializedSubqueries[index] != null)
 					{
 						subqueryTrackingArray[index] = this.materializedSubqueries[index];
 					}

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java?rev=1532666&r1=1532665&r2=1532666&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerWhenClauseTest.java
Wed Oct 16 07:36:10 2013
@@ -58,6 +58,8 @@ public class TriggerWhenClauseTest exten
     private static final String NO_TABLE_PERMISSION = "42500";
     private static final String USER_EXCEPTION = "38000";
     private static final String JAVA_EXCEPTION = "XJ001";
+    private static final String NOT_SINGLE_COLUMN = "42X39";
+    private static final String NON_SCALAR_QUERY = "21000";
 
     public TriggerWhenClauseTest(String name) {
         super(name);
@@ -209,17 +211,17 @@ public class TriggerWhenClauseTest exten
     }
 
     /**
-     * A row trigger whose WHEN clause contains a subquery, could cause a
-     * NullPointerException. This test case is disabled until the bug is fixed.
+     * A row trigger whose WHEN clause contains a subquery, used to cause a
+     * NullPointerException in some situations.
      */
-    public void xtestSubqueryInWhenClauseNPE() throws SQLException {
+    public void testSubqueryInWhenClauseNPE() throws SQLException {
         Statement s = createStatement();
         s.execute("create table t1(x int)");
         s.execute("create table t2(x int)");
         s.execute("create trigger tr1 after insert on t1 for each row "
                 + "when ((values true)) insert into t2 values 1");
 
-        // This statement results in a NullPointerException.
+        // This statement used to result in a NullPointerException.
         s.execute("insert into t1 values 1,2,3");
     }
 
@@ -359,6 +361,15 @@ public class TriggerWhenClauseTest exten
                 "create trigger tr after delete on t1 "
                 + "when ((select true from sysibm.sysdummy where ibmreqd = ?)) "
                 + "call int_proc(1)");
+
+        // Subqueries in the WHEN clause must have a single column
+        assertCompileError(NOT_SINGLE_COLUMN,
+                "create trigger tr no cascade before insert on t1 "
+                + "when ((values (true, false))) call int_proc(1)");
+        assertCompileError(NOT_SINGLE_COLUMN,
+                "create trigger tr after update of x on t1 "
+                + "when ((select tablename, schemaid from sys.systables)) "
+                + "call int_proc(1)");
     }
 
     /**
@@ -743,4 +754,40 @@ public class TriggerWhenClauseTest exten
         JDBC.assertFullResultSet(s.executeQuery("select * from t2 order by x"),
                                  new String[][] {{"1"}, {"2"}, {"3"}});
     }
+
+    /**
+     * Test that scalar subqueries are allowed, and that non-scalar subqueries
+     * result in exceptions when the trigger fires.
+     */
+    public void testScalarSubquery() throws SQLException {
+        Statement s = createStatement();
+        s.execute("create table t1(x int)");
+        s.execute("create table t2(x int)");
+        s.execute("create table t3(x int)");
+
+        s.execute("insert into t3 values 0,1,2,2");
+
+        s.execute("create trigger tr1 after insert on t1 "
+                + "referencing new as new for each row "
+                + "when ((select x > 0 from t3 where x = new.x)) "
+                + "insert into t2 values 1");
+
+        // Subquery returns no rows, so the trigger should not fire.
+        s.execute("insert into t1 values 42");
+        assertTableRowCount("T2", 0);
+
+        // Subquery returns a single value, which is false, so the trigger
+        // should not fire.
+        s.execute("insert into t1 values 0");
+        assertTableRowCount("T2", 0);
+
+        // Subquery returns a single value, which is true, so the trigger
+        // should fire.
+        s.execute("insert into t1 values 1");
+        assertTableRowCount("T2", 1);
+
+        // Subquery returns multiple values, so an error should be raised.
+        assertStatementError(NON_SCALAR_QUERY, s, "insert into t1 values 2");
+        assertTableRowCount("T2", 1);
+    }
 }



Mime
View raw message