Author: rhillegas
Date: Tue Nov 18 12:32:07 2008
New Revision: 718707
URL: http://svn.apache.org/viewvc?rev=718707&view=rev
Log:
DERBY-3948: Forbid references to generated columns in the NEW variable of BEFORE triggers.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.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=718707&r1=718706&r2=718707&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
Tue Nov 18 12:32:07 2008
@@ -34,6 +34,7 @@
import org.apache.derby.iapi.sql.conn.Authorizer;
import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
import org.apache.derby.iapi.sql.dictionary.ColumnDescriptor;
+import org.apache.derby.iapi.sql.dictionary.ColumnDescriptorList;
import org.apache.derby.iapi.sql.dictionary.DataDictionary;
import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor;
import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
@@ -369,6 +370,9 @@
{
validateReferencesClause(dd);
+ // the actions of before triggers may not reference generated columns
+ if ( isBefore ) { forbidActionsOnGenCols(); }
+
StringBuffer newText = new StringBuffer();
boolean regenNode = false;
int start = 0;
@@ -551,6 +555,64 @@
return sorted;
}
+ /*
+ * Forbid references to generated columns in the actions of BEFORE triggers.
+ * This is DERBY-3948, enforcing the following section of the SQL standard:
+ * part 2, section 11.39 (<trigger definition>), syntax rule 12c:
+ *
+ * <blockquote>
+ * 12) If BEFORE is specified, then:
+ * :
+ * c) The <triggered action> shall not contain a <field reference> that
+ * references a field in the new transition variable corresponding to a
+ * generated column of T.
+ * </blockquote>
+ */
+ private void forbidActionsOnGenCols()
+ throws StandardException
+ {
+ ColumnDescriptorList generatedColumns = triggerTableDescriptor.getGeneratedColumns();
+ int genColCount = generatedColumns.size();
+
+ if ( genColCount == 0 ) { return; }
+
+ CollectNodesVisitor visitor = new CollectNodesVisitor( ColumnReference.class
);
+
+ actionNode.accept( visitor );
+
+ Vector columnRefs = visitor.getList();
+ int colRefCount = columnRefs.size();
+
+ for ( int crf_idx = 0; crf_idx < colRefCount; crf_idx++ )
+ {
+ ColumnReference cr = (ColumnReference) columnRefs.get( crf_idx );
+ String colRefName = cr.getColumnName();
+ String tabRefName = cr.getTableName();
+
+ for ( int gc_idx = 0; gc_idx < genColCount; gc_idx++ )
+ {
+ String genColName = generatedColumns.elementAt( gc_idx ).getColumnName();
+
+ if ( genColName.equals( colRefName ) && equals( newTableName, tabRefName
) )
+ {
+ throw StandardException.newException( SQLState.LANG_GEN_COL_BEFORE_TRIG,
genColName );
+ }
+ }
+ }
+ }
+
+ /*
+ * Compare two strings.
+ */
+ private boolean equals( String left, String right )
+ {
+ if ( left == null ) { return (right == null); }
+ else
+ {
+ return left.equals( right );
+ }
+ }
+
/*
** Make sure the given column name is found in the trigger
** target table. Generate the appropriate SQL to get it.
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=718707&r1=718706&r2=718707&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 Tue Nov 18 12:32:07
2008
@@ -2026,6 +2026,12 @@
</msg>
<msg>
+ <name>42XAA</name>
+ <text>The NEW value of generated column '{0}' is mentioned in the BEFORE
action of a trigger. This is not allowed.</text>
+ <arg>columnName</arg>
+ </msg>
+
+ <msg>
<name>42Y00</name>
<text>Class '{0}' does not implement org.apache.derby.iapi.db.AggregateDefinition
and thus cannot be used as an aggregate expression.</text>
<arg>className</arg>
Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=718707&r1=718706&r2=718707&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
(original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
Tue Nov 18 12:32:07 2008
@@ -904,6 +904,7 @@
String LANG_GEN_COL_DEFAULT = "42XA7";
String LANG_GEN_COL_BAD_RENAME = "42XA8";
String LANG_NEEDS_DATATYPE = "42XA9";
+ String LANG_GEN_COL_BEFORE_TRIG = "42XAA";
String LANG_INVALID_USER_AGGREGATE_DEFINITION2 = "42Y00";
String LANG_INVALID_CHECK_CONSTRAINT = "42Y01";
// String LANG_NO_ALTER_TABLE_COMPRESS_ON_TARGET_TABLE = "42Y02";
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java?rev=718707&r1=718706&r2=718707&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
Tue Nov 18 12:32:07 2008
@@ -66,6 +66,7 @@
protected static final String ILLEGAL_ADD_DEFAULT = "42XA7";
protected static final String ILLEGAL_RENAME = "42XA8";
protected static final String NEED_EXPLICIT_DATATYPE = "42XA9";
+ protected static final String BAD_BEFORE_TRIGGER = "42XAA";
protected static final String NOT_NULL_VIOLATION = "23502";
protected static final String CONSTRAINT_VIOLATION = "23513";
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java?rev=718707&r1=718706&r2=718707&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java
Tue Nov 18 12:32:07 2008
@@ -710,7 +710,7 @@
"no cascade before insert on t1_trig\n" +
"referencing new as ar\n" +
"for each row\n" +
- "call report_proc( 'before_insert_row_trigger', ar.a, ar.b, ar.c )\n"
+ "call report_proc( 'before_insert_row_trigger', ar.a, ar.a, ar.c )\n"
);
goodStatement
(
@@ -728,7 +728,7 @@
"no cascade before update on t1_trig\n" +
"referencing old as br new as ar\n" +
"for each row\n" +
- "call wide_report_proc( 'before_update_row_trigger', br.a, br.b, br.c, ar.a,
ar.b, ar.c )\n"
+ "call wide_report_proc( 'before_update_row_trigger', br.a, br.b, br.c, ar.a,
ar.a, ar.c )\n"
);
goodStatement
(
@@ -818,9 +818,9 @@
"insert into t1_trig( a ) values ( 1 ), ( 2 ), ( 3 )",
new String[][]
{
- { "before_insert_row_trigger: [ 1, -1, null ]" },
- { "before_insert_row_trigger: [ 2, -2, null ]" },
- { "before_insert_row_trigger: [ 3, -3, null ]" },
+ { "before_insert_row_trigger: [ 1, 1, null ]" },
+ { "before_insert_row_trigger: [ 2, 2, null ]" },
+ { "before_insert_row_trigger: [ 3, 3, null ]" },
{ "before_insert_statement_trigger: [ -1, -1, -1 ]" },
{ "after_insert_row_trigger: [ 1, -1, null ]" },
{ "after_insert_row_trigger: [ 2, -2, null ]" },
@@ -834,9 +834,9 @@
"update t1_trig set a = a + 10",
new String[][]
{
- { "before_update_row_trigger: [ 1, -1, null, 11, -11, null ]" },
- { "before_update_row_trigger: [ 2, -2, null, 12, -12, null ]" },
- { "before_update_row_trigger: [ 3, -3, null, 13, -13, null ]" },
+ { "before_update_row_trigger: [ 1, -1, null, 11, 11, null ]" },
+ { "before_update_row_trigger: [ 2, -2, null, 12, 12, null ]" },
+ { "before_update_row_trigger: [ 3, -3, null, 13, 13, null ]" },
{ "before_update_statement_trigger: [ -1, -1, -1 ]" },
{ "after_update_row_trigger: [ 1, -1, null, 11, -11, null ]" },
{ "after_update_row_trigger: [ 2, -2, null, 12, -12, null ]" },
@@ -4330,6 +4330,125 @@
}
+ /**
+ * <p>
+ * Test that the NEW variables of BEFORE triggers do not mention generated columns.
+ * </p>
+ */
+ public void test_024_beforeTriggers()
+ throws Exception
+ {
+ Connection conn = getConnection();
+
+ //
+ // Schema.
+ //
+ goodStatement
+ (
+ conn,
+ "create procedure t_tba_report_proc\n" +
+ "( tag varchar( 40 ), a int, b int, c int )\n" +
+ "language java\n" +
+ "parameter style java\n" +
+ "no sql\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.showValues'\n"
+ );
+ goodStatement
+ (
+ conn,
+ "create procedure t_tba_wide_report_proc\n" +
+ "( tag varchar( 40 ), old_a int, old_b int, old_c int, new_a int, new_b int,
new_c int )\n" +
+ "language java\n" +
+ "parameter style java\n" +
+ "no sql\n" +
+ "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.showValues'\n"
+ );
+ goodStatement
+ (
+ conn,
+ "create table t_tba_1( a int, b int generated always as ( -a ), c int )"
+ );
+
+ // BEFORE INSERT trigger that DOESN'T mention generated columns
+ goodStatement
+ (
+ conn,
+ "create trigger trig_tba_good_before_insert\n" +
+ "no cascade before insert on t_tba_1\n" +
+ "referencing new as ar\n" +
+ "for each row\n" +
+ "call t_tba_report_proc( 'before_insert_row_trigger', ar.a, ar.a, ar.a )\n"
+ );
+
+ // BEFORE INSERT trigger that DOES mention generated columns
+ expectCompilationError
+ (
+ BAD_BEFORE_TRIGGER,
+ "create trigger trig_tba_bad_before_insert\n" +
+ "no cascade before insert on t_tba_1\n" +
+ "referencing new as ar\n" +
+ "for each row\n" +
+ "call t_tba_report_proc( 'before_insert_row_trigger', ar.a, ar.b, ar.c )\n"
+ );
+
+ // AFTER INSERT trigger that DOES mention generated columns
+ goodStatement
+ (
+ conn,
+ "create trigger trig_tba_good_after_insert\n" +
+ "after insert on t_tba_1\n" +
+ "referencing new as ar\n" +
+ "for each row\n" +
+ "call t_tba_report_proc( 'after_insert_row_trigger', ar.a, ar.b, ar.c ) \n"
+ );
+
+ // BEFORE UPDATE trigger that DOESN'T mention generated columns in its
+ // NEW variable
+ goodStatement
+ (
+ conn,
+ "create trigger trig_tba_good_before_update\n" +
+ "no cascade before update on t_tba_1\n" +
+ "referencing old as br new as ar\n" +
+ "for each row\n" +
+ "call t_tba_wide_report_proc( 'before_update_row_trigger', br.a, br.b, br.c,
ar.a, ar.a, ar.a )\n"
+ );
+
+ // BEFORE UPDATE trigger that DOES mention generated columns in its NEW variable
+ expectCompilationError
+ (
+ BAD_BEFORE_TRIGGER,
+ "create trigger trig_tba_bad_before_update\n" +
+ "no cascade before update on t_tba_1\n" +
+ "referencing old as br new as ar\n" +
+ "for each row\n" +
+ "call t_tba_wide_report_proc( 'before_update_row_trigger', br.a, br.b, br.c,
ar.a, ar.b, ar.c )\n"
+ );
+
+ // AFTER UPDATE trigger that DOES mention generated columns in its NEW
+ // variable
+ goodStatement
+ (
+ conn,
+ "create trigger trig_tba_good_after_update\n" +
+ "after update on t_tba_1\n" +
+ "referencing old as br new as ar\n" +
+ "for each row\n" +
+ "call t_tba_wide_report_proc( 'after_update_row_trigger', br.a, br.b, br.c,
ar.a, ar.b, ar.c )\n"
+ );
+
+ // BEFORE DELETE trigger which DOES mention generated columns
+ goodStatement
+ (
+ conn,
+ "create trigger trig_tba_before_delete\n" +
+ "no cascade before delete on t_tba_1\n" +
+ "referencing old as br\n" +
+ "for each row\n" +
+ "call t_tba_report_proc( 'before_delete_row_trigger', br.a, br.b, br.c )\n"
+ );
+
+ }
///////////////////////////////////////////////////////////////////////////////////
//
|