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/02 11:58:43 UTC
svn commit: r1528401 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: kahatlen
Date: Wed Oct 2 09:58:43 2013
New Revision: 1528401
URL: http://svn.apache.org/r1528401
Log:
DERBY-534: Support use of the WHEN clause in CREATE TRIGGER statements
Forbid CREATE TRIGGER statements whose WHEN clause contains a
parameter marker or returns a non-BOOLEAN value.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/logop.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/paramij.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/select.out
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/compile/CreateTriggerNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java?rev=1528401&r1=1528400&r2=1528401&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java Wed Oct 2 09:58:43 2013
@@ -398,6 +398,9 @@ class CreateTriggerNode extends DDLState
whenClause = whenClause.bindExpression(
new FromList(cm), new SubqueryList(cm),
new ArrayList<AggregateNode>(0));
+
+ // The WHEN clause must be a BOOLEAN expression.
+ whenClause.checkIsBoolean();
}
}
finally
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=1528401&r1=1528400&r2=1528401&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Wed Oct 2 09:58:43 2013
@@ -11164,6 +11164,9 @@ triggerDefinition() throws StandardExcep
// no params in trigger action
HasNodeVisitor visitor = new HasNodeVisitor(ParameterNode.class);
actionNode.accept(visitor);
+ if (whenClause != null) {
+ whenClause.accept(visitor);
+ }
if (visitor.hasNode())
{
throw StandardException.newException(SQLState.LANG_NO_PARAMS_IN_TRIGGER_ACTION);
Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?rev=1528401&r1=1528400&r2=1528401&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Wed Oct 2 09:58:43 2013
@@ -1663,7 +1663,7 @@ Guide.
<msg>
<name>42X19.S.1</name>
- <text>The WHERE or HAVING clause or CHECK CONSTRAINT definition is a '{0}' expression. It must be a BOOLEAN expression.</text>
+ <text>The WHERE, WHEN or HAVING clause or CHECK CONSTRAINT definition is a '{0}' expression. It must be a BOOLEAN expression.</text>
<arg>dataType</arg>
</msg>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/logop.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/logop.out?rev=1528401&r1=1528400&r2=1528401&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/logop.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/logop.out Wed Oct 2 09:58:43 2013
@@ -397,7 +397,7 @@ false
ij> -- negative testing
-- non boolean where clauses
select * from s where 1;
-ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
+ERROR 42X19: The WHERE, WHEN or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
ij> select * from s where 1 and (1=1);
ERROR 42Y94: An AND or OR has a non-boolean operand. The operands of AND and OR must evaluate to TRUE, FALSE, or UNKNOWN.
ij> select * from s where (1=1) and 1;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/paramij.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/paramij.out?rev=1528401&r1=1528400&r2=1528401&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/paramij.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/paramij.out Wed Oct 2 09:58:43 2013
@@ -86,11 +86,11 @@ C11
ij> -- negative tests
-- In following test cases, there is no way to ensure where with ? will result in a boolean value
prepare p1 as 'select * from t1 where c11';
-ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
+ERROR 42X19: The WHERE, WHEN or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where c11+1';
-ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
+ERROR 42X19: The WHERE, WHEN or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where 1';
-ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
+ERROR 42X19: The WHERE, WHEN or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where ?';
ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is an untyped parameter expression. It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where ? for update';
@@ -104,15 +104,15 @@ ERROR 42X19: The WHERE or HAVING clause
ij> prepare p1 as 'select * from t1 where not ?';
ERROR 42X36: The 'not' operator is not allowed to take a ? parameter as an operand.
ij> prepare p1 as 'select * from t1 where lower(?)';
-ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'VARCHAR' expression. It must be a BOOLEAN expression.
+ERROR 42X19: The WHERE, WHEN or HAVING clause or CHECK CONSTRAINT definition is a 'VARCHAR' expression. It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where lower(?) and 1=1';
ERROR 42Y94: An AND or OR has a non-boolean operand. The operands of AND and OR must evaluate to TRUE, FALSE, or UNKNOWN.
ij> prepare p1 as 'select * from t1 where {fn length(?)}';
-ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
+ERROR 42X19: The WHERE, WHEN or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where {fn locate(?,''a'',1)}';
-ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
+ERROR 42X19: The WHERE, WHEN or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where cast(? as int)';
-ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
+ERROR 42X19: The WHERE, WHEN or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
ij> prepare p1 as 'select * from t1 where (?||''1'')';
-ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'LONG VARCHAR' expression. It must be a BOOLEAN expression.
+ERROR 42X19: The WHERE, WHEN or HAVING clause or CHECK CONSTRAINT definition is a 'LONG VARCHAR' expression. It must be a BOOLEAN expression.
ij>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/select.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/select.out?rev=1528401&r1=1528400&r2=1528401&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/select.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/select.out Wed Oct 2 09:58:43 2013
@@ -88,7 +88,7 @@ ij> -- negative testing
-- non-boolean where clause
-- (only put here due to small size of this test)
select * from t where i;
-ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
+ERROR 42X19: The WHERE, WHEN or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression.
ij> -- invalid correlation name for "*"
select asdf.* from t;
ERROR 42X10: 'ASDF' is not an exposed table name in the scope in which it appears.
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=1528401&r1=1528400&r2=1528401&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 2 09:58:43 2013
@@ -44,7 +44,10 @@ public class TriggerWhenClauseTest exten
*/
private static List<Integer> procedureCalls;
+ private static final String SYNTAX_ERROR = "42X01";
private static final String REFERENCES_SESSION_SCHEMA = "XCL51";
+ private static final String NOT_BOOLEAN = "42X19";
+ private static final String HAS_PARAMETER = "42Y27";
public TriggerWhenClauseTest(String name) {
super(name);
@@ -133,6 +136,32 @@ public class TriggerWhenClauseTest exten
+ "when (exists (select * from new where x > 5)) "
+ "insert into t2 values 'Executed tr11'");
+ // Scalar subqueries are allowed in the WHEN clause, but they need an
+ // extra set of parantheses.
+ //
+ // The first set of parantheses is required by the WHEN clause syntax
+ // itself: WHEN ( <search condition> )
+ //
+ // The second set of parantheses is required by <search condition>.
+ // Follow this path through the SQL standard's syntax rules:
+ // <search condition> -> <boolean value expression>
+ // -> <boolean term> -> <boolean factor> -> <boolean test>
+ // -> <boolean primary> -> <boolean predicand>
+ // -> <nonparenthesized value expression primary>
+ // -> <scalar subquery> -> <subquery> -> <left paren>
+ assertCompileError(SYNTAX_ERROR,
+ "create trigger tr12 after insert on t1 "
+ + "when (values true) insert into t2 values 'Executed tr12'");
+ assertCompileError(SYNTAX_ERROR,
+ "create trigger tr13 after insert on t1 "
+ + "when (select true from sysibm.sysdummy1) "
+ + "insert into t2 values 'Executed tr13'");
+ s.execute("create trigger tr12 after insert on t1 "
+ + "when ((values true)) insert into t2 values 'Executed tr12'");
+ s.execute("create trigger tr13 after insert on t1 "
+ + "when ((select true from sysibm.sysdummy1)) "
+ + "insert into t2 values 'Executed tr13'");
+
// Now fire the triggers and verify the results.
assertUpdateCount(s, 3, "insert into t1 values 1, 2, 3");
JDBC.assertFullResultSet(
@@ -143,6 +172,8 @@ public class TriggerWhenClauseTest exten
{ "Executed tr03", "1" },
{ "Executed tr07", "1" },
{ "Executed tr10", "2" },
+ { "Executed tr12", "1" },
+ { "Executed tr13", "1" },
});
// Empty t2 before firing the triggers again.
@@ -160,6 +191,8 @@ public class TriggerWhenClauseTest exten
{ "Executed tr07", "1" },
{ "Executed tr10", "1" },
{ "Executed tr11", "1" },
+ { "Executed tr12", "1" },
+ { "Executed tr13", "1" },
});
}
@@ -261,19 +294,58 @@ public class TriggerWhenClauseTest exten
}
/**
- * Test that CREATE TRIGGER fails if the WHEN clause references a table
- * in the SESSION schema.
+ * Test various illegal WHEN clauses.
*/
- public void testSessionSchema() throws SQLException {
+ public void testIllegalWhenClauses() throws SQLException {
Statement s = createStatement();
s.execute("declare global temporary table temptable (x int) "
+ "not logged");
s.execute("create table t1(x int)");
s.execute("create table t2(x int)");
+ s.execute("create procedure int_proc(i int) language java "
+ + "parameter style java external name '"
+ + getClass().getName() + ".intProcedure' no sql");
+ // CREATE TRIGGER should fail if the WHEN clause references a table
+ // in the SESSION schema.
assertCompileError(REFERENCES_SESSION_SCHEMA,
"create trigger tr1 after insert on t1 "
+ "when (exists (select * from session.temptable)) "
+ "insert into t2 values 1");
+
+ // The WHEN clause expression must be BOOLEAN.
+ assertCompileError(NOT_BOOLEAN,
+ "create trigger tr after insert on t1 "
+ + "when (1) insert into t2 values 1");
+ assertCompileError(NOT_BOOLEAN,
+ "create trigger tr after update on t1 "
+ + "when ('abc') insert into t2 values 1");
+ assertCompileError(NOT_BOOLEAN,
+ "create trigger tr after delete on t1 "
+ + "when ((values 1)) insert into t2 values 1");
+ assertCompileError(NOT_BOOLEAN,
+ "create trigger tr no cascade before insert on t1 "
+ + "when ((select ibmreqd from sysibm.sysdummy1)) "
+ + "call int_proc(1)");
+ assertCompileError(NOT_BOOLEAN,
+ "create trigger tr no cascade before insert on t1 "
+ + "when ((select ibmreqd from sysibm.sysdummy1)) "
+ + "call int_proc(1)");
+ assertCompileError(NOT_BOOLEAN,
+ "create trigger tr no cascade before update on t1 "
+ + "referencing old as old for each row "
+ + "when (old.x) call int_proc(1)");
+
+ // Dynamic parameters (?) are not allowed in the WHEN clause.
+ assertCompileError(HAS_PARAMETER,
+ "create trigger tr no cascade before delete on t1 "
+ + "when (?) call int_proc(1)");
+ assertCompileError(HAS_PARAMETER,
+ "create trigger tr after insert on t1 "
+ + "when (cast(? as boolean)) call int_proc(1)");
+ assertCompileError(HAS_PARAMETER,
+ "create trigger tr after delete on t1 "
+ + "when ((select true from sysibm.sysdummy where ibmreqd = ?)) "
+ + "call int_proc(1)");
}
}