You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ka...@apache.org on 2013/10/16 09:36:11 UTC
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
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);
+ }
}