db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r1570230 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/types/ engine/org/apache/derby/impl/sql/execute/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/functionTes...
Date Thu, 20 Feb 2014 15:37:52 GMT
Author: rhillegas
Date: Thu Feb 20 15:37:51 2014
New Revision: 1570230

URL: http://svn.apache.org/r1570230
Log:
DERBY-3155: Prevent a MERGE statement from altering the same target row twice; commit derby-3155-28-aa-cardinalityViolations.diff.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLRef.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/MergeResultSet.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/MergeStatementTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLRef.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLRef.java?rev=1570230&r1=1570229&r2=1570230&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLRef.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLRef.java Thu Feb 20 15:37:51
2014
@@ -243,4 +243,13 @@ public class SQLRef extends DataType imp
 		else
 			return value.toString();
 	}
+
+    /**
+     * Adding this overload makes it possible to use SQLRefs as keys in HashMaps.
+     */
+    public  int hashCode()
+    {
+        if ( value == null ) { return 0; }
+        else { return value.hashCode(); }
+    }
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/MergeResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/MergeResultSet.java?rev=1570230&r1=1570229&r2=1570230&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/MergeResultSet.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/MergeResultSet.java
Thu Feb 20 15:37:51 2014
@@ -25,15 +25,16 @@ import org.apache.derby.iapi.error.Stand
 import org.apache.derby.iapi.reference.SQLState;
 import org.apache.derby.iapi.services.context.ContextManager;
 import org.apache.derby.iapi.services.loader.GeneratedMethod;
-import org.apache.derby.shared.common.sanity.SanityManager;
 import org.apache.derby.iapi.sql.Activation;
 import org.apache.derby.iapi.sql.execute.ConstantAction;
 import org.apache.derby.iapi.sql.execute.ExecIndexRow;
 import org.apache.derby.iapi.sql.execute.ExecRow;
 import org.apache.derby.iapi.sql.execute.NoPutResultSet;
+import org.apache.derby.iapi.store.access.BackingStoreHashtable;
 import org.apache.derby.iapi.types.DataValueDescriptor;
 import org.apache.derby.iapi.types.RowLocation;
 import org.apache.derby.iapi.types.SQLRef;
+import org.apache.derby.shared.common.sanity.SanityManager;
 
 /**
  * INSERT/UPDATE/DELETE a target table based on how it outer joins
@@ -61,7 +62,9 @@ class MergeResultSet extends NoRowsResul
     private long                        _rowCount;
     private TemporaryRowHolderImpl[]    _thenRows;
 
-	private int						numOpens;
+	private BackingStoreHashtable		_subjectRowIDs;
+    
+	private int						_numOpens;
     
     ///////////////////////////////////////////////////////////////////////////////////
     //
@@ -98,7 +101,7 @@ class MergeResultSet extends NoRowsResul
     {
         setup();
 
-		if (numOpens++ == 0)
+		if (_numOpens++ == 0)
 		{
 			_drivingLeftJoin.openCore();
 		}
@@ -162,7 +165,14 @@ class MergeResultSet extends NoRowsResul
         }
 
         if ( _drivingLeftJoin != null ) { _drivingLeftJoin.close(); }
-		numOpens = 0;
+
+        if ( _subjectRowIDs != null )
+        {
+            _subjectRowIDs.close();
+            _subjectRowIDs = null;
+        }
+        
+		_numOpens = 0;
     }
 
 
@@ -180,7 +190,6 @@ class MergeResultSet extends NoRowsResul
     boolean  collectAffectedRows() throws StandardException
     {
         DataValueDescriptor     rlColumn;
-        RowLocation             baseRowLocation;
         boolean rowsFound = false;
 
         while ( true )
@@ -196,7 +205,7 @@ class MergeResultSet extends NoRowsResul
             rowsFound = true;
 
             rlColumn = _row.getColumn( _row.nColumns() );
-            baseRowLocation = null;
+            SQLRef             baseRowLocation = null;
 
             boolean matched = false;
             if ( rlColumn != null )
@@ -204,11 +213,11 @@ class MergeResultSet extends NoRowsResul
                 if ( !rlColumn.isNull() )
                 {
                     matched = true;
-                    baseRowLocation = (RowLocation) rlColumn.getObject();
                     
                     // change the HeapRowLocation into a SQLRef, something which the
                     // temporary table can (de)serialize correctly
-                    _row.setColumn( _row.nColumns(), new SQLRef( baseRowLocation ) );
+                    baseRowLocation = new SQLRef( (RowLocation) rlColumn.getObject() );
+                    _row.setColumn( _row.nColumns(), baseRowLocation );
                 }
             }
 
@@ -243,6 +252,8 @@ class MergeResultSet extends NoRowsResul
 
             if ( matchingClause != null )
             {
+                if ( baseRowLocation != null ) { addSubjectRow( baseRowLocation ); }
+                
                 _thenRows[ clauseIdx ] = matchingClause.bufferThenRow( activation, _thenRows[
clauseIdx ], _row );
                 _rowCount++;
             }
@@ -251,4 +262,61 @@ class MergeResultSet extends NoRowsResul
         return rowsFound;
     }
 
+    /**
+     * <p>
+     * Add another subject row id to the evolving hashtable of affected target rows.
+     * The concept of a subject row is defined by the 2011 SQL Standard, part 2,
+     * section 14.12 (merge statement), general rule 6. A row in the target table
+     * is a subject row if it joins to the source table on the main search condition
+     * and if the joined row satisfies the matching refinement condition for
+     * some WHEN MATCHED clause. A row in the target table may only be a
+     * subject row once. That is, a given target row may only qualify for UPDATE
+     * or DELETE processing once. If it qualifies for more than one UPDATE or DELETE
+     * action, then the Standard requires us to raise a cardinality violation.
+     * </p>
+     *
+     * @param   subjectRowID    The location of the subject row.
+     *
+	 * @exception StandardException A cardinality exception is thrown if we've already added
this subject row.
+     */
+    private void    addSubjectRow( SQLRef subjectRowID ) throws StandardException
+    {
+        if ( _subjectRowIDs == null ) { createSubjectRowIDhashtable(); }
+
+        if ( _subjectRowIDs.get( subjectRowID ) != null )
+        {
+            throw StandardException.newException( SQLState.LANG_REDUNDANT_SUBJECT_ROW );
+        }
+        else
+        {
+            DataValueDescriptor[] row = new DataValueDescriptor[] { subjectRowID };
+
+            _subjectRowIDs.putRow( true, row, null );
+        }
+    }
+
+    /**
+     * <p>
+     * Create a BackingStoreHashtable to hold the ids of subject rows.
+     * </p>
+     */
+    private void    createSubjectRowIDhashtable()   throws StandardException
+    {
+		final int[] keyCols = new int[] { 0 };
+
+		_subjectRowIDs = new BackingStoreHashtable
+            (
+             getActivation().getLanguageConnectionContext().getTransactionExecute(),
+             null,          // no row source. we'll fill the hashtable as we go along
+             keyCols,
+             false,         // duplicate handling doesn't matter. we probe for duplicates
and error out if we find one
+             -1,            // who knows what the row count will be
+             HashScanResultSet.DEFAULT_MAX_CAPACITY,
+             HashScanResultSet.DEFAULT_INITIAL_CAPACITY,
+             HashScanResultSet.DEFAULT_MAX_CAPACITY,
+             false,         // null keys aren't relevant. the row id is always non-null
+             false          // discard after commit
+             );
+    }
+
 }

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=1570230&r1=1570229&r2=1570230&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 Thu Feb 20 15:37:51
2014
@@ -607,6 +607,11 @@ Guide.
                 <text>Scalar subquery is only allowed to return a single row.</text>
             </msg>
 
+            <msg>
+                <name>21000.S.1</name>
+                <text>A row in the target table qualifies for more than one DELETE
or UPDATE action.</text>
+            </msg>
+
         </family>
 
 

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=1570230&r1=1570229&r2=1570230&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
Thu Feb 20 15:37:51 2014
@@ -694,6 +694,7 @@ public interface SQLState {
 	
 	String LANG_MISSING_PARMS                                          = "07000";
 	String LANG_SCALAR_SUBQUERY_CARDINALITY_VIOLATION                  = "21000";
+	String LANG_REDUNDANT_SUBJECT_ROW                         = "21000.S.1";
 	String LANG_STRING_TRUNCATION                                      = "22001";
 	String LANG_CONCAT_STRING_OVERFLOW                                      = "54006";
 	String LANG_OUTSIDE_RANGE_FOR_DATATYPE                             = "22003";

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java?rev=1570230&r1=1570229&r2=1570230&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/MergeStatementTest.java
Thu Feb 20 15:37:51 2014
@@ -75,6 +75,7 @@ public class MergeStatementTest extends 
     private static  final   String      NO_SYNONYMS_IN_MERGE = "42XAP";
     private static  final   String      NO_DCL_IN_MERGE = "42XAQ";
     private static  final   String      PARAMETER_NOT_SET = "07000";
+    private static  final   String      CARDINALITY_VIOLATION = "21000";
 
     private static  final   String[]    TRIGGER_HISTORY_COLUMNS = new String[] { "ACTION",
"ACTION_VALUE" };
 
@@ -4737,9 +4738,9 @@ public class MergeStatementTest extends 
             (
              dboConnection,
              "merge into t1_030\n" +
-             "using t2_030 on true\n" +
-             "when matched then update set y = y || 'x'\n",
-             6
+             "using t2_030 on t1_030.x = t2_030.x\n" +
+             "when matched and y is not null then update set y = y || 'x'\n",
+             1
              );
         assertResults
             (
@@ -5508,6 +5509,128 @@ public class MergeStatementTest extends 
         goodStatement( dboConnection, "drop table t1_040" );
     }
     
+   /**
+     * <p>
+     * Verify the same target row can't be touched twice by a MERGE statement.
+     * </p>
+     */
+    public  void    test_041_cardinalityViolations()
+        throws Exception
+    {
+        Connection  dboConnection = openUserConnection( TEST_DBO );
+
+        //
+        // create schema
+        //
+        goodStatement
+            (
+             dboConnection,
+             "create view sr_041( i ) as values ( 1 ), ( 3 )"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create table t1_041( x int, y int, z int )"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create unique index idx on t1_041( x, y )"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create table t2_041( x int, y int, z int )"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create table t3_041( x int, y int, z int )"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create unique index t2_idx_041 on t2_041( x, y )"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "create unique index t3_idx_041 on t3_041( x, y )"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "insert into t1_041 values\n" +
+             "( 1, 100, 1000 ), ( 1, 101, 1000 ), ( 1, 102, 1000 ), ( 1, 103, 1000 ), ( 2,
200, 2000 )\n"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "insert into t2_041 values\n" +
+             "( 1, 100, 1000 ), ( 1, 101, 1000 ), ( 2, 200, 2000 )\n"
+             );
+        goodStatement
+            (
+             dboConnection,
+             "insert into t3_041 values\n" +
+             "( 1, 100, 1000 ), ( 1, -101, 1000 ), ( 3, 300, 3000 )\n"
+             );
+
+        //
+        // Attempt to delete the same row twice.
+        //
+        expectExecutionError
+            ( dboConnection, CARDINALITY_VIOLATION,
+              "merge into t1_041\n" +
+              "using sr_041 on ( x = 1 )\n" +
+              "when matched and y = 101 then delete\n" +
+              "when matched and y = 102 then update set z = -1000\n" +
+              "when not matched and i > 1 then insert values ( -1, i, 0 )\n"
+              );
+        expectExecutionError
+            ( dboConnection, CARDINALITY_VIOLATION,
+              "merge into t2_041\n" +
+              "using t3_041 on t2_041.x = t3_041.x\n" +
+              "when matched and t2_041.y = 101 then delete\n"
+              );
+
+        //
+        // attempt to update the same row twice
+        //
+        expectExecutionError
+            ( dboConnection, CARDINALITY_VIOLATION,
+              "merge into t2_041\n" +
+              "using t3_041 on t2_041.x = t3_041.x\n" +
+              "when matched and t2_041.y = 101 then update set z = t3_041.z\n" 
+              );
+
+        //
+        // attempt to delete and update the same row
+        //
+        expectExecutionError
+            ( dboConnection, CARDINALITY_VIOLATION,
+              "merge into t2_041\n" +
+              "using t3_041 on t2_041.x = t3_041.x\n" +
+              "when matched and t2_041.y = t3_041.y then delete\n" +
+              "when matched and t2_041.y = 100 and -101 = t3_041.y then update set z = 2
* t3_041.z\n"
+              );
+        expectExecutionError
+            ( dboConnection, CARDINALITY_VIOLATION,
+              "merge into t2_041\n" +
+              "using t3_041 on t2_041.x = t3_041.x\n" +
+              "when matched and t2_041.y = 100 and -101 = t3_041.y then update set z = 2
* t3_041.z\n" +
+              "when matched and t2_041.y = t3_041.y then delete\n"
+              );
+
+        //
+        // drop schema
+        //
+        goodStatement( dboConnection, "drop view sr_041" );
+        goodStatement( dboConnection, "drop table t1_041" );
+        goodStatement( dboConnection, "drop table t2_041" );
+        goodStatement( dboConnection, "drop table t3_041" );
+    }
+    
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // ROUTINES



Mime
View raw message