db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject 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/
Date Wed, 02 Oct 2013 09:58:43 GMT
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)");
     }
 }



Mime
View raw message