db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From davi...@apache.org
Subject svn commit: r231366 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Thu, 11 Aug 2005 01:12:40 GMT
Author: davidvc
Date: Wed Aug 10 18:12:31 2005
New Revision: 231366

URL: http://svn.apache.org/viewcvs?rev=231366&view=rev
Log:
Committing changes for DERBY-171 - Need correlation ID in UPDATE/DELETE statements

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CurrentOfNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromTable.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/delete.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/update.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/delete.sql
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/update.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java?rev=231366&r1=231365&r2=231366&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnReference.java Wed Aug 10 18:12:31 2005
@@ -142,9 +142,7 @@
 				"tableNumber: " + tableNumber + "\n" +
 				"columnNumber: " + columnNumber + "\n" +
 				"replacesAggregate: " + replacesAggregate + "\n" +
-				( ( tableName != null) ?
-						tableName.toString() :
-						"tableName: null\n") +
+				"tableName: " + ( ( tableName != null) ? tableName.toString() : "null") + "\n" +
 				"nestingLevel: " + nestingLevel + "\n" +
 				"sourceLevel: " + sourceLevel + "\n" +
 				super.toString();
@@ -387,7 +385,6 @@
 			Vector aggregateVector) 
 				throws StandardException
 	{
-
 		ResultColumn matchingRC;
 
 		if (SanityManager.DEBUG)

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CurrentOfNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CurrentOfNode.java?rev=231366&r1=231365&r2=231366&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CurrentOfNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CurrentOfNode.java Wed Aug 10 18:12:31 2005
@@ -98,9 +98,9 @@
 	//
 	// initializers
 	//
-	public void init(Object cursor, Object tableProperties)
+	public void init( Object correlationName, Object cursor, Object tableProperties)
 	{
-		super.init(null, tableProperties);
+		super.init(correlationName, tableProperties);
 		cursorName = (String) cursor;
 	}
 
@@ -337,7 +337,11 @@
 		 * matches the table we're looking at, see whether the column
 		 * is in this table, and also whether it is in the for update list.
 		*/
-		if (columnsTableName == null || columnsTableName.getFullTableName().equals(baseTableName.getFullTableName()))
+		if (
+			   (columnsTableName == null) ||
+			   (columnsTableName.getFullTableName().equals(baseTableName.getFullTableName())) ||
+			   ((correlationName != null) && correlationName.equals( columnsTableName.getTableName()))
+		   )
 		{
 			boolean notfound = false;
 
@@ -367,6 +371,15 @@
 			}
 		}
 
+		/*
+		 * Patch up the table number for correlated references.
+		 * Part of the fix for bug 171.
+		 */
+		if ( (correlationName != null) && (columnReference.getTableNumber() < 0) )
+		{
+			columnReference.setTableNumber( tableNumber );
+		}
+		
 		return resultColumn;
 	}
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java?rev=231366&r1=231365&r2=231366&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/DeleteNode.java Wed Aug 10 18:12:31 2005
@@ -238,6 +238,7 @@
 			** Start off assuming no columns from the base table
 			** are needed in the rcl.
 			*/
+
 			resultColumnList = new ResultColumnList();
 
 			FromBaseTable fbt = getResultColumnList(resultColumnList);
@@ -246,6 +247,7 @@
 										targetTableDescriptor);
 
 			resultColumnList = fbt.addColsToList(resultColumnList, readColsBitSet);
+
 			/*
 			** If all bits are set, then behave as if we chose all
 			** in the first place
@@ -285,6 +287,9 @@
 			/* Append to the ResultColumnList */
 			resultColumnList.addResultColumn(rowLocationColumn);
 
+			/* Force the added columns to take on the table's correlation name, if any */
+			correlateAddedColumns( resultColumnList, targetTable );
+			
 			/* Set the new result column list in the result set */
 			resultSet.setResultColumns(resultColumnList);
 		}
@@ -968,4 +973,34 @@
 		return	columnMap;
 	}
     
+	/*
+	 * Force column references (particularly those added by the compiler)
+	 * to use the correlation name on the base table, if any.
+	 */
+	private	void	correlateAddedColumns( ResultColumnList rcl, FromTable fromTable )
+		throws StandardException
+	{
+		String		correlationName = fromTable.getCorrelationName();
+
+		if ( correlationName == null ) { return; }
+
+		TableName	correlationNameNode = makeTableName( null, correlationName );
+		int			count = rcl.size();
+
+		for ( int i = 0; i < count; i++ )
+		{
+			ResultColumn	column = (ResultColumn) rcl.elementAt( i );
+
+			ValueNode		expression = column.getExpression();
+
+			if ( (expression != null) && (expression instanceof ColumnReference) )
+			{
+				ColumnReference	reference = (ColumnReference) expression;
+				
+				reference.setTableNameNode( correlationNameNode );
+			}
+		}
+		
+	}
+	
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java?rev=231366&r1=231365&r2=231366&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java Wed Aug 10 18:12:31 2005
@@ -309,7 +309,7 @@
 	 * @exception StandardException		Thrown on error
 	 */
 
-	public void bindExpressions()
+	public void bindExpressions( FromList fromListParam )
 					throws StandardException
 	{
 		FromTable	fromTable;
@@ -318,11 +318,32 @@
 		for (int index = 0; index < size; index++)
 		{
 			fromTable = (FromTable) elementAt(index);
-			fromTable.bindExpressions(this);
+			fromTable.bindExpressions( makeFromList( fromListParam, fromTable ) );
 		}
 	}
 
 	/**
+	 * Construct an appropriate from list for binding an individual
+	 * table element. Normally, this is just this list. However,
+	 * for the special wrapper queries which the parser creates for
+	 * GROUP BY and HAVING clauses, the appropriate list is the
+	 * outer list passed into us--it will contain the appropriate
+	 * tables needed to resolve correlated columns.
+	 */
+	private	FromList	makeFromList( FromList fromListParam, FromTable fromTable )
+	{
+		if ( fromTable instanceof FromSubquery )
+		{
+			FromSubquery	fromSubquery = (FromSubquery) fromTable;
+
+			if ( fromSubquery.generatedForGroupByClause || fromSubquery.generatedForHavingClause )
+			{ return fromListParam; }
+		}
+
+		return this;
+	}
+	
+	/**
 	 * Bind the result columns of the ResultSetNodes in this FromList when there is no
 	 * base table to bind them to.  This is useful for SELECT statements,
 	 * where the result columns get their types from the expressions that
@@ -521,6 +542,7 @@
 			previousLevel = currentLevel;
 
 			resultColumn = fromTable.getMatchingColumn(columnReference);
+
 			if (resultColumn != null)
 			{
 				if (! columnNameMatch)

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java?rev=231366&r1=231365&r2=231366&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java Wed Aug 10 18:12:31 2005
@@ -257,12 +257,20 @@
 									getContextManager());
 		ResultColumnList	derivedRCL = resultColumns;
 		ResultColumnList	subqueryRCL;
+		FromList			nestedFromList;
 
 		/* From subqueries cannot be correlated, so we pass an empty FromList
-		 * to subquery.bindExpressions() and .bindResultColumns().
+		 * to subquery.bindExpressions() and .bindResultColumns(). However,
+		 * the parser rewrites queries which have GROUP BY and HAVING clauses.
+		 * For these rewritten pseudo-subqueries, we need to pass in the outer FromList
+		 * which contains correlated tables.
 		 */
-		subquery.bindExpressions(emptyFromList);
-		subquery.bindResultColumns(emptyFromList);
+		if ( generatedForGroupByClause || generatedForHavingClause )
+		{ nestedFromList = fromListParam; }
+		else { nestedFromList = emptyFromList; }
+		
+		subquery.bindExpressions(nestedFromList);
+		subquery.bindResultColumns(nestedFromList);
 
 		/* Now that we've bound the expressions in the subquery, we 
 		 * can propagate the subquery's RCL up to the FromSubquery.

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromTable.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromTable.java?rev=231366&r1=231365&r2=231366&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromTable.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromTable.java Wed Aug 10 18:12:31 2005
@@ -125,6 +125,11 @@
 		tableNumber = -1;
 	}
 
+	/**
+	 * Get this table's correlation name, if any.
+	 */
+	public	String	getCorrelationName() { return correlationName; }
+
 	/*
 	 *  Optimizable interface
 	 */

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java?rev=231366&r1=231365&r2=231366&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java Wed Aug 10 18:12:31 2005
@@ -267,7 +267,7 @@
 
 	/**
 	 * Clear the table name for the underlying ColumnReference.
-	 * See UpdateNode for full explaination.
+	 * See UpdateNode.scrubResultColumns() for full explaination.
 	 */
 	public void clearTableName()
 	{
@@ -764,6 +764,7 @@
 				*/
 				String realName = tableDescriptor.getName();
 				String refName = reference.getTableName();
+
 				throw StandardException.newException(SQLState.LANG_TABLE_NAME_MISMATCH, 
 					realName, refName);
 			}
@@ -1745,4 +1746,8 @@
     public TableName getTableNameObject() {
         return null;
     }
+
+	/* Get the wrapped reference if any */
+	public	ColumnReference	getReference() { return reference; }
+	
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=231366&r1=231365&r2=231366&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java Wed Aug 10 18:12:31 2005
@@ -469,7 +469,7 @@
 		if (! bindTargetListOnly)
 		{
 			/* Bind the expressions in FromSubquerys, JoinNodes, etc. */
-			fromList.bindExpressions();
+			fromList.bindExpressions( fromListParam );
 		}
 
 		selectSubquerys = (SubqueryList) getNodeFactory().getNode(

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java?rev=231366&r1=231365&r2=231366&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java Wed Aug 10 18:12:31 2005
@@ -225,6 +225,7 @@
 		SelectNode sel;
 		sel = (SelectNode)resultSet;
 		targetTable = (FromTable) sel.fromList.elementAt(0);
+
 		if (targetTable instanceof CurrentOfNode) 
 		{	
 			positionedUpdate = true;
@@ -341,6 +342,7 @@
 			"More than one table in result from list in an update.");
 
 		/* Bind the original result columns by column name */
+		normalizeCorrelatedColumns( resultSet.resultColumns, targetTable );
  		resultSet.bindResultColumns(targetTableDescriptor,
 									targetVTI,
  									resultSet.resultColumns, this,
@@ -496,17 +498,11 @@
 			/* Append to the ResultColumnList */
         resultColumnList.addResultColumn(rowLocationColumn);
 
-		/* The last thing that we do to the generated RCL is to clear
-		 * the table name out from each RC.  The table name is
-		 * unnecessary for an update.  More importantly, though, it
-		 * creates a problem in the degenerate case with a positioned
-		 * update.  The user must specify the base table name for a
-		 * positioned update.  If a correlation name was specified for
-		 * the cursor, then a match for the ColumnReference would not
-		 * be found if we didn't null out the name.  (Aren't you
-		 * glad you asked?)
+		/*
+		 * The last thing that we do to the generated RCL is to clear
+		 * the table name out from each RC. See comment on scrubResultColumns().
 		 */
-		resultColumnList.clearTableNames();
+		scrubResultColumns( resultColumnList );
 
 		/* Set the new result column list in the result set */
 		resultSet.setResultColumns(resultColumnList);
@@ -1000,4 +996,52 @@
 
 		return	columnMap;
 	}
+
+	private	void	scrubResultColumns( ResultColumnList rcl )
+	{
+		/* The table name is
+		 * unnecessary for an update.  More importantly, though, it
+		 * creates a problem in the degenerate case with a positioned
+		 * update.  The user must specify the base table name for a
+		 * positioned update.  If a correlation name was specified for
+		 * the cursor, then a match for the ColumnReference would not
+		 * be found if we didn't null out the name.  (Aren't you
+		 * glad you asked?)
+		 */
+		rcl.clearTableNames();
+	}
+
+	/*
+	 * Force correlated column references in the SET clause to have the
+	 * name of the base table. This dances around the problem alluded to
+	 * in scrubResultColumn().
+	 */
+	private	void	normalizeCorrelatedColumns( ResultColumnList rcl, FromTable fromTable )
+		throws StandardException
+	{
+		String		correlationName = fromTable.getCorrelationName();
+
+		if ( correlationName == null ) { return; }
+
+		TableName	tableNameNode;
+
+		if ( fromTable instanceof CurrentOfNode )
+		{ tableNameNode = ((CurrentOfNode) fromTable).getBaseCursorTargetTableName(); }
+		else { tableNameNode = makeTableName( null, fromTable.getBaseTableName() ); }
+		
+		int			count = rcl.size();
+
+		for ( int i = 0; i < count; i++ )
+		{
+			ResultColumn	column = (ResultColumn) rcl.elementAt( i );
+			ColumnReference	reference = column.getReference();
+
+			if ( (reference != null) && correlationName.equals( reference.getTableName() ) )
+			{
+				reference.setTableNameNode( tableNameNode );
+			}
+		}
+		
+	}
+	
 } // end of UpdateNode

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=231366&r1=231365&r2=231366&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 Aug 10 18:12:31 2005
@@ -2599,6 +2599,7 @@
 deleteBody() throws StandardException :
 {
 	JavaToSQLValueNode	javaToSQLNode = null;
+	String				correlationName = null;
 	TableName  tableName = null;
 	ValueNode  whereClause = null;
 	FromTable  fromTable = null;
@@ -2623,6 +2624,11 @@
 	}
 |
 	<FROM> tableName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH) 
+		[ 
+			LOOKAHEAD
+			( { (getToken(1).kind != EOF) && (getToken(1).kind != WHERE) && !propertyListFollows() } )
+			[ <AS> ] correlationName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true) 
+		]
 	    [targetProperties = propertyList() ] 
 		[ 
 			whereToken = <WHERE>
@@ -2635,7 +2641,7 @@
 					 (getToken(2).kind == OF) 
 				  	}
 				)	
-				fromTable = currentOfClause() |
+				fromTable = currentOfClause( correlationName ) |
 				whereClause = whereClause(whereToken)
 			)
 		]
@@ -2654,7 +2660,7 @@
 			fromTable = (FromTable) nodeFactory.getNode(
 										C_NodeTypes.FROM_BASE_TABLE,
 										tableName,
-										null,
+										correlationName,
 										ReuseFactory.getInteger(
 												FromBaseTable.DELETE),
 										null,
@@ -2682,7 +2688,7 @@
  * <A NAME="currentOfClause">currentOfClause</A>
  */
 FromTable
-currentOfClause() throws StandardException :
+currentOfClause( String correlationName ) throws StandardException :
 {
 	String cursorName = null;
 }
@@ -2691,6 +2697,7 @@
 	{ 
 		return (FromTable) nodeFactory.getNode(
 								C_NodeTypes.CURRENT_OF_NODE,
+								correlationName,
 								cursorName,
 								null,
 								getContextManager());
@@ -2845,6 +2852,7 @@
 updateBody() throws StandardException :
 {
 	ResultColumnList	columnList;
+	String				correlationName = null;
 	JavaToSQLValueNode	javaToSQLNode = null;
 	TableName  tableName = null;
 	ValueNode  whereClause = null;
@@ -2870,13 +2878,19 @@
 	}
 |
 		tableName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
-		[targetProperties = propertyList() ] 
+		[
+			LOOKAHEAD( { (getToken(1).kind != SET) && !propertyListFollows() } )
+			[ <AS> ] correlationName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true) 
+		]
+		[
+			targetProperties = propertyList()
+		] 
 		<SET> columnList = setClauseList()
 		[ 
 			whereToken = <WHERE>
 		    (
 				whereClause = whereClause(whereToken) |
-				fromTable = currentOfClause()
+				fromTable = currentOfClause( correlationName )
 			)
 		]
 	{
@@ -2894,7 +2908,7 @@
 			fromTable = (FromTable) nodeFactory.getNode(
 										C_NodeTypes.FROM_BASE_TABLE,
 										tableName,
-										null,
+										correlationName,
 										ReuseFactory.getInteger(
 														FromBaseTable.UPDATE),
 										null,

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/delete.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/delete.out?rev=231366&r1=231365&r2=231366&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/delete.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/delete.out Wed Aug 10 18:12:31 2005
@@ -213,4 +213,77 @@
 autocommit on;
 ij> drop table x;
 0 rows inserted/updated/deleted
+ij> --------------------------------------------
+--
+-- Test delete piece of the fix for bug171.
+--
+--------------------------------------------
+create table bug171_employee( empl_id int, bonus int );
+0 rows inserted/updated/deleted
+ij> create table bug171_bonuses( empl_id int, bonus int );
+0 rows inserted/updated/deleted
+ij> insert into bug171_employee( empl_id, bonus ) values ( 1, 0 ), ( 2, 0 ), ( 3, 0 );
+3 rows inserted/updated/deleted
+ij> insert into bug171_bonuses( empl_id, bonus )
+values
+( 1, 100 ), ( 1, 100 ), ( 1, 100 ),
+( 2, 200 ), ( 2, 200 ), ( 2, 200 ),
+( 3, 300 ), ( 3, 300 ), ( 3, 300 );
+9 rows inserted/updated/deleted
+ij> select * from bug171_employee;
+EMPL_ID    |BONUS      
+-----------------------
+1          |0          
+2          |0          
+3          |0          
+ij> select * from bug171_bonuses;
+EMPL_ID    |BONUS      
+-----------------------
+1          |100        
+1          |100        
+1          |100        
+2          |200        
+2          |200        
+2          |200        
+3          |300        
+3          |300        
+3          |300        
+ij> --
+-- The problem query. could not use correlation names in delete.
+--
+delete from bug171_employee e
+    where e.empl_id > 2 and e.bonus <
+    (
+        select sum( b.bonus ) from bug171_bonuses b
+        where b.empl_id = e.empl_id
+    );
+1 row inserted/updated/deleted
+ij> select * from bug171_employee;
+EMPL_ID    |BONUS      
+-----------------------
+1          |0          
+2          |0          
+ij> -- positioned delete with correlation names
+autocommit off;
+ij> get cursor bug171_c2 as
+'select * from bug171_employee where empl_id = 2 for update';
+ij> next bug171_c2;
+EMPL_ID    |BONUS      
+-----------------------
+2          |0          
+ij> delete from bug171_employee e where current of bug171_c2;
+1 row inserted/updated/deleted
+ij> close bug171_c2;
+ij> select * from bug171_employee;
+EMPL_ID    |BONUS      
+-----------------------
+1          |0          
+ij> autocommit on;
+ij> --
+-- Cleanup
+--
+drop table bug171_employee;
+0 rows inserted/updated/deleted
+ij> drop table bug171_bonuses;
+0 rows inserted/updated/deleted
 ij> 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out?rev=231366&r1=231365&r2=231366&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out Wed Aug 10 18:12:31 2005
@@ -2783,7 +2783,7 @@
                   where e.mgrname = e2.mgrname group by dno having
                   e2.dno in (select dno from db2test.emp  e1
                    where e.mgrname = 'JOHN')))));
-ERROR 42X04: Column 'DB2TEST.EMP.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'DB2TEST.EMP.NAME' is not a column in the target table.
+ERROR 42X04: Column 'E.MGRNAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'E.MGRNAME' is not a column in the target table.
 ij> select * from db2test.emp order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -2941,7 +2941,10 @@
                   where e4.dno = e2.dno group by dno having
                   e2.dno in (select dno from db2test.emp  e1
                    where e.mgrname = 'JOHN'))))) order by 2, 3, 4;
-ERROR 42X04: Column 'E.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'E.NAME' is not a column in the target table.
+C0         |NAME      |MGRNAME   |DNO 
+--------------------------------------
+16         |GUY       |JOHN      |K55 
+6          |HAMID     |JOHN      |K55 
 ij> delete from db2test.emp 
   where dno in (select dno from db2test.emp5 e5
    where e5.dno in (select dno from db2test.emp4 e4 where
@@ -2951,7 +2954,7 @@
                   where e4.dno = e2.dno group by dno having
                   e2.dno in (select dno from db2test.emp  e1
                    where db2test.emp.mgrname = 'JOHN')))));
-ERROR 42X04: Column 'DB2TEST.EMP.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'DB2TEST.EMP.NAME' is not a column in the target table.
+2 rows inserted/updated/deleted
 ij> select * from db2test.emp order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -2967,8 +2970,6 @@
 15         |SAM2      |ROGER     |K52 
 10         |BOBBIE    |HAMID     |K55 
 17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
 8          |LARRY1    |HAMID     |K55 
 9          |LARRY2    |HAMID     |K55 
 19         |LILY1     |GUY       |K55 
@@ -2990,8 +2991,6 @@
 15         |SAM2      |ROGER     |K52 
 10         |BOBBIE    |HAMID     |K55 
 17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
 8          |LARRY1    |HAMID     |K55 
 9          |LARRY2    |HAMID     |K55 
 19         |LILY1     |GUY       |K55 
@@ -3013,8 +3012,6 @@
 15         |SAM2      |ROGER     |K52 
 10         |BOBBIE    |HAMID     |K55 
 17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
 8          |LARRY1    |HAMID     |K55 
 9          |LARRY2    |HAMID     |K55 
 19         |LILY1     |GUY       |K55 
@@ -3036,8 +3033,6 @@
 15         |SAM2      |ROGER     |K52 
 10         |BOBBIE    |HAMID     |K55 
 17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
 8          |LARRY1    |HAMID     |K55 
 9          |LARRY2    |HAMID     |K55 
 19         |LILY1     |GUY       |K55 
@@ -3059,8 +3054,6 @@
 15         |SAM2      |ROGER     |K52 
 10         |BOBBIE    |HAMID     |K55 
 17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
 8          |LARRY1    |HAMID     |K55 
 9          |LARRY2    |HAMID     |K55 
 19         |LILY1     |GUY       |K55 
@@ -3109,7 +3102,7 @@
                   where e4.name = e2.mgrname group by dno having
                   e2.dno in (select dno from db2test.emp  e1
                    where e.mgrname = 'JOHN'))))) order by 2, 3, 4;
-ERROR 42X04: Column 'E.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'E.NAME' is not a column in the target table.
+ERROR 42X04: Column 'E4.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'E4.NAME' is not a column in the target table.
 ij> -- select should get -119;
 delete from db2test.emp 
   where dno in (select dno from db2test.emp5 e5
@@ -3120,7 +3113,7 @@
                   where e4.name = e2.mgrname group by dno having
                   e2.dno in (select dno from db2test.emp  e1
                   where db2test.emp.mgrname = 'JOHN')))));
-ERROR 42X04: Column 'DB2TEST.EMP.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'DB2TEST.EMP.NAME' is not a column in the target table.
+ERROR 42X04: Column 'E4.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'E4.NAME' is not a column in the target table.
 ij> select * from db2test.emp order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -3278,7 +3271,10 @@
                   where e4.dno = e2.dno group by dno having
                   e2.dno in (select dno from db2test.emp  e1
                    where e.mgrname = 'JOHN'))))) order by 2, 3, 4;
-ERROR 42X04: Column 'E.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'E.NAME' is not a column in the target table.
+C0         |NAME      |MGRNAME   |DNO 
+--------------------------------------
+16         |GUY       |JOHN      |K55 
+6          |HAMID     |JOHN      |K55 
 ij> delete from db2test.emp 
   where dno in (select dno from db2test.emp5 e5
    where e5.dno in (select dno from db2test.emp4 e4 where
@@ -3288,7 +3284,7 @@
                   where e4.dno = e2.dno group by dno having
                   e2.dno in (select dno from db2test.emp  e1
                    where e.mgrname = 'JOHN')))));
-ERROR 42X04: Column 'DB2TEST.EMP.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'DB2TEST.EMP.NAME' is not a column in the target table.
+ERROR 42X04: Column 'E.MGRNAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'E.MGRNAME' is not a column in the target table.
 ij> select * from db2test.emp order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -5999,20 +5995,18 @@
 2          |K52|OFC       
 1          |K55|DB        
 ij> delete from db2test.dept d where
-  dno in (select dno from db2test.emp e where
- e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where
- e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where
- e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where
- e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where
- e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where
+  d.dno in (select e.dno from db2test.emp e where
+ e.dno = d.dno and e.dno in (select e2.dno from db2test.emp2 e2 where
+ e2.dno = e.dno and e2.dno in (select e3.dno from db2test.emp3 e3 where
+ e3.dno = e2.dno and e3.dno in (select e4.dno from db2test.emp4 e4 where
+ e4.dno = e3.dno and e4.dno in (select e5.dno from db2test.emp5 e5 where
+ e5.dno = e4.dno and e5.dno in (select e6.dno from db2test.emp6 e6 where
  e6.dno = e5.dno and e6.dno in ('K55', 'K52')))))));
-ERROR 42X01: Syntax error: Encountered "d" at line 1, column 26.
+2 rows inserted/updated/deleted
 ij> select * from db2test.dept order by dno, dname;
 C0         |DNO|DNAME     
 --------------------------
 3          |K51|CS        
-2          |K52|OFC       
-1          |K55|DB        
 ij> select * from db2test.emp order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -6021,21 +6015,21 @@
 5          |JOE2      |ASHOK     |K51 
 2          |JOHN      |ASHOK     |K51 
 3          |ROBIN     |ASHOK     |K51 
-13         |DAN       |ROGER     |K52 
-12         |JIM       |ROGER     |K52 
-11         |ROGER     |ROBIN     |K52 
-14         |SAM1      |ROGER     |K52 
-15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+10         |BOBBIE    |HAMID     |NULL
+13         |DAN       |ROGER     |NULL
+17         |DON       |GUY       |NULL
+16         |GUY       |JOHN      |NULL
+6          |HAMID     |JOHN      |NULL
+12         |JIM       |ROGER     |NULL
+8          |LARRY1    |HAMID     |NULL
+9          |LARRY2    |HAMID     |NULL
+19         |LILY1     |GUY       |NULL
+20         |LILY2     |GUY       |NULL
+18         |MONICA    |GUY       |NULL
+11         |ROGER     |ROBIN     |NULL
+14         |SAM1      |ROGER     |NULL
+15         |SAM2      |ROGER     |NULL
+7          |TRUONG    |HAMID     |NULL
 ij> select * from db2test.secondemp order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -6067,21 +6061,21 @@
 5          |JOE2      |ASHOK     |K51 
 2          |JOHN      |ASHOK     |K51 
 3          |ROBIN     |ASHOK     |K51 
-13         |DAN       |ROGER     |K52 
-12         |JIM       |ROGER     |K52 
-11         |ROGER     |ROBIN     |K52 
-14         |SAM1      |ROGER     |K52 
-15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+10         |BOBBIE    |HAMID     |NULL
+13         |DAN       |ROGER     |NULL
+17         |DON       |GUY       |NULL
+16         |GUY       |JOHN      |NULL
+6          |HAMID     |JOHN      |NULL
+12         |JIM       |ROGER     |NULL
+8          |LARRY1    |HAMID     |NULL
+9          |LARRY2    |HAMID     |NULL
+19         |LILY1     |GUY       |NULL
+20         |LILY2     |GUY       |NULL
+18         |MONICA    |GUY       |NULL
+11         |ROGER     |ROBIN     |NULL
+14         |SAM1      |ROGER     |NULL
+15         |SAM2      |ROGER     |NULL
+7          |TRUONG    |HAMID     |NULL
 ij> select * from db2test.emp3 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -6090,21 +6084,21 @@
 5          |JOE2      |ASHOK     |K51 
 2          |JOHN      |ASHOK     |K51 
 3          |ROBIN     |ASHOK     |K51 
-13         |DAN       |ROGER     |K52 
-12         |JIM       |ROGER     |K52 
-11         |ROGER     |ROBIN     |K52 
-14         |SAM1      |ROGER     |K52 
-15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+10         |BOBBIE    |HAMID     |NULL
+13         |DAN       |ROGER     |NULL
+17         |DON       |GUY       |NULL
+16         |GUY       |JOHN      |NULL
+6          |HAMID     |JOHN      |NULL
+12         |JIM       |ROGER     |NULL
+8          |LARRY1    |HAMID     |NULL
+9          |LARRY2    |HAMID     |NULL
+19         |LILY1     |GUY       |NULL
+20         |LILY2     |GUY       |NULL
+18         |MONICA    |GUY       |NULL
+11         |ROGER     |ROBIN     |NULL
+14         |SAM1      |ROGER     |NULL
+15         |SAM2      |ROGER     |NULL
+7          |TRUONG    |HAMID     |NULL
 ij> select * from db2test.emp4 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -6113,21 +6107,21 @@
 5          |JOE2      |ASHOK     |K51 
 2          |JOHN      |ASHOK     |K51 
 3          |ROBIN     |ASHOK     |K51 
-13         |DAN       |ROGER     |K52 
-12         |JIM       |ROGER     |K52 
-11         |ROGER     |ROBIN     |K52 
-14         |SAM1      |ROGER     |K52 
-15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+10         |BOBBIE    |HAMID     |NULL
+13         |DAN       |ROGER     |NULL
+17         |DON       |GUY       |NULL
+16         |GUY       |JOHN      |NULL
+6          |HAMID     |JOHN      |NULL
+12         |JIM       |ROGER     |NULL
+8          |LARRY1    |HAMID     |NULL
+9          |LARRY2    |HAMID     |NULL
+19         |LILY1     |GUY       |NULL
+20         |LILY2     |GUY       |NULL
+18         |MONICA    |GUY       |NULL
+11         |ROGER     |ROBIN     |NULL
+14         |SAM1      |ROGER     |NULL
+15         |SAM2      |ROGER     |NULL
+7          |TRUONG    |HAMID     |NULL
 ij> select * from db2test.emp5 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -6136,21 +6130,21 @@
 5          |JOE2      |ASHOK     |K51 
 2          |JOHN      |ASHOK     |K51 
 3          |ROBIN     |ASHOK     |K51 
-13         |DAN       |ROGER     |K52 
-12         |JIM       |ROGER     |K52 
-11         |ROGER     |ROBIN     |K52 
-14         |SAM1      |ROGER     |K52 
-15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+10         |BOBBIE    |HAMID     |NULL
+13         |DAN       |ROGER     |NULL
+17         |DON       |GUY       |NULL
+16         |GUY       |JOHN      |NULL
+6          |HAMID     |JOHN      |NULL
+12         |JIM       |ROGER     |NULL
+8          |LARRY1    |HAMID     |NULL
+9          |LARRY2    |HAMID     |NULL
+19         |LILY1     |GUY       |NULL
+20         |LILY2     |GUY       |NULL
+18         |MONICA    |GUY       |NULL
+11         |ROGER     |ROBIN     |NULL
+14         |SAM1      |ROGER     |NULL
+15         |SAM2      |ROGER     |NULL
+7          |TRUONG    |HAMID     |NULL
 ij> select * from db2test.emp6 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -6159,21 +6153,21 @@
 5          |JOE2      |ASHOK     |K51 
 2          |JOHN      |ASHOK     |K51 
 3          |ROBIN     |ASHOK     |K51 
-13         |DAN       |ROGER     |K52 
-12         |JIM       |ROGER     |K52 
-11         |ROGER     |ROBIN     |K52 
-14         |SAM1      |ROGER     |K52 
-15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+10         |BOBBIE    |HAMID     |NULL
+13         |DAN       |ROGER     |NULL
+17         |DON       |GUY       |NULL
+16         |GUY       |JOHN      |NULL
+6          |HAMID     |JOHN      |NULL
+12         |JIM       |ROGER     |NULL
+8          |LARRY1    |HAMID     |NULL
+9          |LARRY2    |HAMID     |NULL
+19         |LILY1     |GUY       |NULL
+20         |LILY2     |GUY       |NULL
+18         |MONICA    |GUY       |NULL
+11         |ROGER     |ROBIN     |NULL
+14         |SAM1      |ROGER     |NULL
+15         |SAM2      |ROGER     |NULL
+7          |TRUONG    |HAMID     |NULL
 ij> select * from db2test.emp7 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -6182,21 +6176,21 @@
 5          |JOE2      |ASHOK     |K51 
 2          |JOHN      |ASHOK     |K51 
 3          |ROBIN     |ASHOK     |K51 
-13         |DAN       |ROGER     |K52 
-12         |JIM       |ROGER     |K52 
-11         |ROGER     |ROBIN     |K52 
-14         |SAM1      |ROGER     |K52 
-15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+10         |BOBBIE    |HAMID     |NULL
+13         |DAN       |ROGER     |NULL
+17         |DON       |GUY       |NULL
+16         |GUY       |JOHN      |NULL
+6          |HAMID     |JOHN      |NULL
+12         |JIM       |ROGER     |NULL
+8          |LARRY1    |HAMID     |NULL
+9          |LARRY2    |HAMID     |NULL
+19         |LILY1     |GUY       |NULL
+20         |LILY2     |GUY       |NULL
+18         |MONICA    |GUY       |NULL
+11         |ROGER     |ROBIN     |NULL
+14         |SAM1      |ROGER     |NULL
+15         |SAM2      |ROGER     |NULL
+7          |TRUONG    |HAMID     |NULL
 ij> select * from db2test.emp8 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -6205,44 +6199,44 @@
 5          |JOE2      |ASHOK     |K51 
 2          |JOHN      |ASHOK     |K51 
 3          |ROBIN     |ASHOK     |K51 
-13         |DAN       |ROGER     |K52 
-12         |JIM       |ROGER     |K52 
-11         |ROGER     |ROBIN     |K52 
-14         |SAM1      |ROGER     |K52 
-15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
-ij> select * from db2test.emp9 order by dno, name, mgrname;
-C0         |NAME      |MGRNAME   |DNO 
---------------------------------------
+10         |BOBBIE    |HAMID     |NULL
+13         |DAN       |ROGER     |NULL
+17         |DON       |GUY       |NULL
+16         |GUY       |JOHN      |NULL
+6          |HAMID     |JOHN      |NULL
+12         |JIM       |ROGER     |NULL
+8          |LARRY1    |HAMID     |NULL
+9          |LARRY2    |HAMID     |NULL
+19         |LILY1     |GUY       |NULL
+20         |LILY2     |GUY       |NULL
+18         |MONICA    |GUY       |NULL
+11         |ROGER     |ROBIN     |NULL
+14         |SAM1      |ROGER     |NULL
+15         |SAM2      |ROGER     |NULL
+7          |TRUONG    |HAMID     |NULL
+ij> select * from db2test.emp9 order by dno, name, mgrname;
+C0         |NAME      |MGRNAME   |DNO 
+--------------------------------------
 1          |ASHOK     |NULL      |K51 
 4          |JOE1      |ASHOK     |K51 
 5          |JOE2      |ASHOK     |K51 
 2          |JOHN      |ASHOK     |K51 
 3          |ROBIN     |ASHOK     |K51 
-13         |DAN       |ROGER     |K52 
-12         |JIM       |ROGER     |K52 
-11         |ROGER     |ROBIN     |K52 
-14         |SAM1      |ROGER     |K52 
-15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+10         |BOBBIE    |HAMID     |NULL
+13         |DAN       |ROGER     |NULL
+17         |DON       |GUY       |NULL
+16         |GUY       |JOHN      |NULL
+6          |HAMID     |JOHN      |NULL
+12         |JIM       |ROGER     |NULL
+8          |LARRY1    |HAMID     |NULL
+9          |LARRY2    |HAMID     |NULL
+19         |LILY1     |GUY       |NULL
+20         |LILY2     |GUY       |NULL
+18         |MONICA    |GUY       |NULL
+11         |ROGER     |ROBIN     |NULL
+14         |SAM1      |ROGER     |NULL
+15         |SAM2      |ROGER     |NULL
+7          |TRUONG    |HAMID     |NULL
 ij> select * from db2test.emp10 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -6251,21 +6245,21 @@
 5          |JOE2      |ASHOK     |K51 
 2          |JOHN      |ASHOK     |K51 
 3          |ROBIN     |ASHOK     |K51 
-13         |DAN       |ROGER     |K52 
-12         |JIM       |ROGER     |K52 
-11         |ROGER     |ROBIN     |K52 
-14         |SAM1      |ROGER     |K52 
-15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+10         |BOBBIE    |HAMID     |NULL
+13         |DAN       |ROGER     |NULL
+17         |DON       |GUY       |NULL
+16         |GUY       |JOHN      |NULL
+6          |HAMID     |JOHN      |NULL
+12         |JIM       |ROGER     |NULL
+8          |LARRY1    |HAMID     |NULL
+9          |LARRY2    |HAMID     |NULL
+19         |LILY1     |GUY       |NULL
+20         |LILY2     |GUY       |NULL
+18         |MONICA    |GUY       |NULL
+11         |ROGER     |ROBIN     |NULL
+14         |SAM1      |ROGER     |NULL
+15         |SAM2      |ROGER     |NULL
+7          |TRUONG    |HAMID     |NULL
 ij> select * from db2test.emp11 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -6274,21 +6268,21 @@
 5          |JOE2      |ASHOK     |K51 
 2          |JOHN      |ASHOK     |K51 
 3          |ROBIN     |ASHOK     |K51 
-13         |DAN       |ROGER     |K52 
-12         |JIM       |ROGER     |K52 
-11         |ROGER     |ROBIN     |K52 
-14         |SAM1      |ROGER     |K52 
-15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+10         |BOBBIE    |HAMID     |NULL
+13         |DAN       |ROGER     |NULL
+17         |DON       |GUY       |NULL
+16         |GUY       |JOHN      |NULL
+6          |HAMID     |JOHN      |NULL
+12         |JIM       |ROGER     |NULL
+8          |LARRY1    |HAMID     |NULL
+9          |LARRY2    |HAMID     |NULL
+19         |LILY1     |GUY       |NULL
+20         |LILY2     |GUY       |NULL
+18         |MONICA    |GUY       |NULL
+11         |ROGER     |ROBIN     |NULL
+14         |SAM1      |ROGER     |NULL
+15         |SAM2      |ROGER     |NULL
+7          |TRUONG    |HAMID     |NULL
 ij> select * from db2test.emp12 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -6297,21 +6291,21 @@
 5          |JOE2      |ASHOK     |K51 
 2          |JOHN      |ASHOK     |K51 
 3          |ROBIN     |ASHOK     |K51 
-13         |DAN       |ROGER     |K52 
-12         |JIM       |ROGER     |K52 
-11         |ROGER     |ROBIN     |K52 
-14         |SAM1      |ROGER     |K52 
-15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+10         |BOBBIE    |HAMID     |NULL
+13         |DAN       |ROGER     |NULL
+17         |DON       |GUY       |NULL
+16         |GUY       |JOHN      |NULL
+6          |HAMID     |JOHN      |NULL
+12         |JIM       |ROGER     |NULL
+8          |LARRY1    |HAMID     |NULL
+9          |LARRY2    |HAMID     |NULL
+19         |LILY1     |GUY       |NULL
+20         |LILY2     |GUY       |NULL
+18         |MONICA    |GUY       |NULL
+11         |ROGER     |ROBIN     |NULL
+14         |SAM1      |ROGER     |NULL
+15         |SAM2      |ROGER     |NULL
+7          |TRUONG    |HAMID     |NULL
 ij> select * from db2test.emp13 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -6320,21 +6314,21 @@
 5          |JOE2      |ASHOK     |K51 
 2          |JOHN      |ASHOK     |K51 
 3          |ROBIN     |ASHOK     |K51 
-13         |DAN       |ROGER     |K52 
-12         |JIM       |ROGER     |K52 
-11         |ROGER     |ROBIN     |K52 
-14         |SAM1      |ROGER     |K52 
-15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+10         |BOBBIE    |HAMID     |NULL
+13         |DAN       |ROGER     |NULL
+17         |DON       |GUY       |NULL
+16         |GUY       |JOHN      |NULL
+6          |HAMID     |JOHN      |NULL
+12         |JIM       |ROGER     |NULL
+8          |LARRY1    |HAMID     |NULL
+9          |LARRY2    |HAMID     |NULL
+19         |LILY1     |GUY       |NULL
+20         |LILY2     |GUY       |NULL
+18         |MONICA    |GUY       |NULL
+11         |ROGER     |ROBIN     |NULL
+14         |SAM1      |ROGER     |NULL
+15         |SAM2      |ROGER     |NULL
+7          |TRUONG    |HAMID     |NULL
 ij> select * from db2test.emp14 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -6343,21 +6337,21 @@
 5          |JOE2      |ASHOK     |K51 
 2          |JOHN      |ASHOK     |K51 
 3          |ROBIN     |ASHOK     |K51 
-13         |DAN       |ROGER     |K52 
-12         |JIM       |ROGER     |K52 
-11         |ROGER     |ROBIN     |K52 
-14         |SAM1      |ROGER     |K52 
-15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+10         |BOBBIE    |HAMID     |NULL
+13         |DAN       |ROGER     |NULL
+17         |DON       |GUY       |NULL
+16         |GUY       |JOHN      |NULL
+6          |HAMID     |JOHN      |NULL
+12         |JIM       |ROGER     |NULL
+8          |LARRY1    |HAMID     |NULL
+9          |LARRY2    |HAMID     |NULL
+19         |LILY1     |GUY       |NULL
+20         |LILY2     |GUY       |NULL
+18         |MONICA    |GUY       |NULL
+11         |ROGER     |ROBIN     |NULL
+14         |SAM1      |ROGER     |NULL
+15         |SAM2      |ROGER     |NULL
+7          |TRUONG    |HAMID     |NULL
 ij> select * from db2test.emp15 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -6366,21 +6360,21 @@
 5          |JOE2      |ASHOK     |K51 
 2          |JOHN      |ASHOK     |K51 
 3          |ROBIN     |ASHOK     |K51 
-13         |DAN       |ROGER     |K52 
-12         |JIM       |ROGER     |K52 
-11         |ROGER     |ROBIN     |K52 
-14         |SAM1      |ROGER     |K52 
-15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+10         |BOBBIE    |HAMID     |NULL
+13         |DAN       |ROGER     |NULL
+17         |DON       |GUY       |NULL
+16         |GUY       |JOHN      |NULL
+6          |HAMID     |JOHN      |NULL
+12         |JIM       |ROGER     |NULL
+8          |LARRY1    |HAMID     |NULL
+9          |LARRY2    |HAMID     |NULL
+19         |LILY1     |GUY       |NULL
+20         |LILY2     |GUY       |NULL
+18         |MONICA    |GUY       |NULL
+11         |ROGER     |ROBIN     |NULL
+14         |SAM1      |ROGER     |NULL
+15         |SAM2      |ROGER     |NULL
+7          |TRUONG    |HAMID     |NULL
 ij> -- "END OF TESTUNIT: 11";
 -- *************************************************************************
 -- TESTUNIT         : 12
@@ -6390,7 +6384,7 @@
 -- "START OF TESTUNIT: 12";
 -- reset to original rows
 delete from db2test.dept;
-3 rows inserted/updated/deleted
+1 row inserted/updated/deleted
 ij> delete from db2test.emp;
 20 rows inserted/updated/deleted
 ij> delete from db2test.secondemp;
@@ -7575,17 +7569,26 @@
                   e2.dno in (select dno from db2test.emp e1
                   where e1.name = e.mgrname and e1.mgrname = 'JOHN')))))
    order by 2,3,4;
-ERROR 42X04: Column 'E5.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'E5.NAME' is not a column in the target table.
+C0         |NAME      |MGRNAME   |DNO 
+--------------------------------------
+10         |BOBBIE    |HAMID     |K55 
+17         |DON       |GUY       |K55 
+8          |LARRY1    |HAMID     |K55 
+9          |LARRY2    |HAMID     |K55 
+19         |LILY1     |GUY       |K55 
+20         |LILY2     |GUY       |K55 
+18         |MONICA    |GUY       |K55 
+7          |TRUONG    |HAMID     |K55 
 ij> delete from db2test.emp e
-  where dno in (select dno from db2test.emp5 e5
-   where e5.dno in (select dno from db2test.emp4 e4 where
-   e5.name = e4.mgrname group by dno having dno in
-     (select dno from db2test.emp3 e3 where e4.dno = e3.dno and
-       e3.dno in (select dno from db2test.emp2 e2
-                  where e3.name = e2.mgrname group by dno having
-                  e2.dno in (select dno from db2test.emp  e1
+  where e.dno in (select e5.dno from db2test.emp5 e5
+   where e5.dno in (select e4.dno from db2test.emp4 e4 where
+   e5.name = e4.mgrname group by e4.dno having dno in
+     (select e3.dno from db2test.emp3 e3 where e4.dno = e3.dno and
+       e3.dno in (select e2.dno from db2test.emp2 e2
+                  where e3.name = e2.mgrname group by e2.dno having
+                  e2.dno in (select e1.dno from db2test.emp  e1
                   where e1.name = e.mgrname and e1.mgrname = 'JOHN')))));
-ERROR 42X01: Syntax error: Encountered "e" at line 1, column 25.
+8 rows inserted/updated/deleted
 ij> select * from db2test.emp order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -7599,16 +7602,8 @@
 11         |ROGER     |ROBIN     |K52 
 14         |SAM1      |ROGER     |K52 
 15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
 16         |GUY       |JOHN      |K55 
 6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
 ij> select * from db2test.emp2 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -7713,7 +7708,7 @@
 delete from db2test.dept;
 3 rows inserted/updated/deleted
 ij> delete from db2test.emp;
-20 rows inserted/updated/deleted
+12 rows inserted/updated/deleted
 ij> delete from db2test.emp2;
 20 rows inserted/updated/deleted
 ij> delete from db2test.emp3;
@@ -7743,17 +7738,20 @@
                   where e.name = e2.mgrname group by dno having
                   e2.dno in (select dno from db2test.emp  e1
                    where e.mgrname = 'JOHN'))))) order by 2,3,4;
-ERROR 42X04: Column 'E.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'E.NAME' is not a column in the target table.
+C0         |NAME      |MGRNAME   |DNO 
+--------------------------------------
+16         |GUY       |JOHN      |K55 
+6          |HAMID     |JOHN      |K55 
 ij> delete from db2test.emp e
-  where dno in (select dno from db2test.emp5 e5
-   where e5.dno in (select dno from db2test.emp4 e4 where
-   e.name = e4.mgrname group by dno having dno in
-     (select dno from db2test.emp3 e3 where e.name = e3.mgrname and
-       e3.dno in (select dno from db2test.emp2 e2
-                  where e.name = e2.mgrname group by dno having
-                  e2.dno in (select dno from db2test.emp  e1
+  where e.dno in (select e5.dno from db2test.emp5 e5
+   where e5.dno in (select e4.dno from db2test.emp4 e4 where
+   e.name = e4.mgrname group by e4.dno having e4.dno in
+     (select e3.dno from db2test.emp3 e3 where e.name = e3.mgrname and
+       e3.dno in (select e2.dno from db2test.emp2 e2
+                  where e.name = e2.mgrname group by e2.dno having
+                  e2.dno in (select e1.dno from db2test.emp  e1
                    where e.mgrname = 'JOHN')))));
-ERROR 42X01: Syntax error: Encountered "e" at line 1, column 25.
+2 rows inserted/updated/deleted
 ij> select * from db2test.emp order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -7769,8 +7767,6 @@
 15         |SAM2      |ROGER     |K52 
 10         |BOBBIE    |HAMID     |K55 
 17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
 8          |LARRY1    |HAMID     |K55 
 9          |LARRY2    |HAMID     |K55 
 19         |LILY1     |GUY       |K55 
@@ -7790,16 +7786,16 @@
 11         |ROGER     |ROBIN     |K52 
 14         |SAM1      |ROGER     |K52 
 15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
+10         |BOBBIE    |NULL      |K55 
+17         |DON       |NULL      |K55 
 16         |GUY       |JOHN      |K55 
 6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+8          |LARRY1    |NULL      |K55 
+9          |LARRY2    |NULL      |K55 
+19         |LILY1     |NULL      |K55 
+20         |LILY2     |NULL      |K55 
+18         |MONICA    |NULL      |K55 
+7          |TRUONG    |NULL      |K55 
 ij> select * from db2test.emp3 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -7813,16 +7809,16 @@
 11         |ROGER     |ROBIN     |K52 
 14         |SAM1      |ROGER     |K52 
 15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
+10         |BOBBIE    |NULL      |K55 
+17         |DON       |NULL      |K55 
 16         |GUY       |JOHN      |K55 
 6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+8          |LARRY1    |NULL      |K55 
+9          |LARRY2    |NULL      |K55 
+19         |LILY1     |NULL      |K55 
+20         |LILY2     |NULL      |K55 
+18         |MONICA    |NULL      |K55 
+7          |TRUONG    |NULL      |K55 
 ij> select * from db2test.emp4 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -7836,16 +7832,16 @@
 11         |ROGER     |ROBIN     |K52 
 14         |SAM1      |ROGER     |K52 
 15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
+10         |BOBBIE    |NULL      |K55 
+17         |DON       |NULL      |K55 
 16         |GUY       |JOHN      |K55 
 6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+8          |LARRY1    |NULL      |K55 
+9          |LARRY2    |NULL      |K55 
+19         |LILY1     |NULL      |K55 
+20         |LILY2     |NULL      |K55 
+18         |MONICA    |NULL      |K55 
+7          |TRUONG    |NULL      |K55 
 ij> select * from db2test.emp5 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -7859,16 +7855,16 @@
 11         |ROGER     |ROBIN     |K52 
 14         |SAM1      |ROGER     |K52 
 15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
+10         |BOBBIE    |NULL      |K55 
+17         |DON       |NULL      |K55 
 16         |GUY       |JOHN      |K55 
 6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+8          |LARRY1    |NULL      |K55 
+9          |LARRY2    |NULL      |K55 
+19         |LILY1     |NULL      |K55 
+20         |LILY2     |NULL      |K55 
+18         |MONICA    |NULL      |K55 
+7          |TRUONG    |NULL      |K55 
 ij> -- "END OF TESTUNIT: 06";
 -- *************************************************************************
 -- TESTUNIT         : 07
@@ -7881,7 +7877,7 @@
 delete from db2test.dept;
 3 rows inserted/updated/deleted
 ij> delete from db2test.emp;
-20 rows inserted/updated/deleted
+18 rows inserted/updated/deleted
 ij> delete from db2test.emp2;
 20 rows inserted/updated/deleted
 ij> delete from db2test.emp3;
@@ -7911,7 +7907,10 @@
                   where e4.dno = e2.dno group by dno having
                   e2.dno in (select dno from db2test.emp  e1
                    where e.mgrname = 'JOHN'))))) order by 2,3,4;
-ERROR 42X04: Column 'E.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'E.NAME' is not a column in the target table.
+C0         |NAME      |MGRNAME   |DNO 
+--------------------------------------
+16         |GUY       |JOHN      |K55 
+6          |HAMID     |JOHN      |K55 
 ij> delete from db2test.emp
   where dno in (select dno from db2test.emp5 e5
    where e5.dno in (select dno from db2test.emp4 e4 where
@@ -7921,7 +7920,7 @@
                   where e4.dno = e2.dno group by dno having
                   e2.dno in (select dno from db2test.emp  e1
                    where db2test.emp.mgrname = 'JOHN')))));
-ERROR 42X04: Column 'DB2TEST.EMP.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'DB2TEST.EMP.NAME' is not a column in the target table.
+2 rows inserted/updated/deleted
 ij> select * from db2test.emp order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -7937,8 +7936,6 @@
 15         |SAM2      |ROGER     |K52 
 10         |BOBBIE    |HAMID     |K55 
 17         |DON       |GUY       |K55 
-16         |GUY       |JOHN      |K55 
-6          |HAMID     |JOHN      |K55 
 8          |LARRY1    |HAMID     |K55 
 9          |LARRY2    |HAMID     |K55 
 19         |LILY1     |GUY       |K55 
@@ -7958,16 +7955,16 @@
 11         |ROGER     |ROBIN     |K52 
 14         |SAM1      |ROGER     |K52 
 15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
+10         |BOBBIE    |NULL      |K55 
+17         |DON       |NULL      |K55 
 16         |GUY       |JOHN      |K55 
 6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+8          |LARRY1    |NULL      |K55 
+9          |LARRY2    |NULL      |K55 
+19         |LILY1     |NULL      |K55 
+20         |LILY2     |NULL      |K55 
+18         |MONICA    |NULL      |K55 
+7          |TRUONG    |NULL      |K55 
 ij> select * from db2test.emp3 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -7981,16 +7978,16 @@
 11         |ROGER     |ROBIN     |K52 
 14         |SAM1      |ROGER     |K52 
 15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
+10         |BOBBIE    |NULL      |K55 
+17         |DON       |NULL      |K55 
 16         |GUY       |JOHN      |K55 
 6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+8          |LARRY1    |NULL      |K55 
+9          |LARRY2    |NULL      |K55 
+19         |LILY1     |NULL      |K55 
+20         |LILY2     |NULL      |K55 
+18         |MONICA    |NULL      |K55 
+7          |TRUONG    |NULL      |K55 
 ij> select * from db2test.emp4 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -8004,16 +8001,16 @@
 11         |ROGER     |ROBIN     |K52 
 14         |SAM1      |ROGER     |K52 
 15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
+10         |BOBBIE    |NULL      |K55 
+17         |DON       |NULL      |K55 
 16         |GUY       |JOHN      |K55 
 6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+8          |LARRY1    |NULL      |K55 
+9          |LARRY2    |NULL      |K55 
+19         |LILY1     |NULL      |K55 
+20         |LILY2     |NULL      |K55 
+18         |MONICA    |NULL      |K55 
+7          |TRUONG    |NULL      |K55 
 ij> select * from db2test.emp5 order by dno, name, mgrname;
 C0         |NAME      |MGRNAME   |DNO 
 --------------------------------------
@@ -8027,16 +8024,16 @@
 11         |ROGER     |ROBIN     |K52 
 14         |SAM1      |ROGER     |K52 
 15         |SAM2      |ROGER     |K52 
-10         |BOBBIE    |HAMID     |K55 
-17         |DON       |GUY       |K55 
+10         |BOBBIE    |NULL      |K55 
+17         |DON       |NULL      |K55 
 16         |GUY       |JOHN      |K55 
 6          |HAMID     |JOHN      |K55 
-8          |LARRY1    |HAMID     |K55 
-9          |LARRY2    |HAMID     |K55 
-19         |LILY1     |GUY       |K55 
-20         |LILY2     |GUY       |K55 
-18         |MONICA    |GUY       |K55 
-7          |TRUONG    |HAMID     |K55 
+8          |LARRY1    |NULL      |K55 
+9          |LARRY2    |NULL      |K55 
+19         |LILY1     |NULL      |K55 
+20         |LILY2     |NULL      |K55 
+18         |MONICA    |NULL      |K55 
+7          |TRUONG    |NULL      |K55 
 ij> -- "END OF TESTUNIT: 07";
 -- "cleanup";
 drop table db2test.emp6;

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/update.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/update.out?rev=231366&r1=231365&r2=231366&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/update.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/update.out Wed Aug 10 18:12:31 2005
@@ -436,4 +436,86 @@
 ij> autocommit on;
 ij> drop table tab1;
 0 rows inserted/updated/deleted
+ij> --------------------------------------------
+--
+-- Test upgrade piece of the fix for bug171.
+--
+--------------------------------------------
+create table bug171_employee( empl_id int, bonus int );
+0 rows inserted/updated/deleted
+ij> create table bug171_bonuses( empl_id int, bonus int );
+0 rows inserted/updated/deleted
+ij> insert into bug171_employee( empl_id, bonus ) values ( 1, 0 ), ( 2, 0 ), ( 3, 0 );
+3 rows inserted/updated/deleted
+ij> insert into bug171_bonuses( empl_id, bonus )
+values
+( 1, 100 ), ( 1, 100 ), ( 1, 100 ),
+( 2, 200 ), ( 2, 200 ), ( 2, 200 ),
+( 3, 300 ), ( 3, 300 ), ( 3, 300 );
+9 rows inserted/updated/deleted
+ij> select * from bug171_employee;
+EMPL_ID    |BONUS      
+-----------------------
+1          |0          
+2          |0          
+3          |0          
+ij> select * from bug171_bonuses;
+EMPL_ID    |BONUS      
+-----------------------
+1          |100        
+1          |100        
+1          |100        
+2          |200        
+2          |200        
+2          |200        
+3          |300        
+3          |300        
+3          |300        
+ij> --
+-- The problem query. could not use correlation names in update.
+--
+update bug171_employee e
+    set e.bonus =
+    (
+        select sum( b.bonus ) from bug171_bonuses b
+        where b.empl_id = e.empl_id
+    );
+3 rows inserted/updated/deleted
+ij> select * from bug171_employee;
+EMPL_ID    |BONUS      
+-----------------------
+1          |300        
+2          |600        
+3          |900        
+ij> -- positioned update with correlation names
+autocommit off;
+ij> get cursor bug171_c1 as
+'select * from bug171_employee where empl_id = 1 for update';
+ij> next bug171_c1;
+EMPL_ID    |BONUS      
+-----------------------
+1          |300        
+ij> update bug171_employee e
+    set e.bonus =
+    (
+        select 2 * sum( b.bonus ) from bug171_bonuses b
+        where b.empl_id = e.empl_id
+    )
+where current of bug171_c1;
+1 row inserted/updated/deleted
+ij> close bug171_c1;
+ij> select * from bug171_employee;
+EMPL_ID    |BONUS      
+-----------------------
+1          |600        
+2          |600        
+3          |900        
+ij> autocommit on;
+ij> --
+-- Cleanup
+--
+drop table bug171_employee;
+0 rows inserted/updated/deleted
+ij> drop table bug171_bonuses;
+0 rows inserted/updated/deleted
 ij> 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/delete.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/delete.sql?rev=231366&r1=231365&r2=231366&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/delete.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/delete.sql Wed Aug 10 18:12:31 2005
@@ -161,3 +161,56 @@
 -- clean up
 autocommit on;
 drop table x;
+
+--------------------------------------------
+--
+-- Test delete piece of the fix for bug171.
+--
+--------------------------------------------
+
+create table bug171_employee( empl_id int, bonus int );
+create table bug171_bonuses( empl_id int, bonus int );
+
+insert into bug171_employee( empl_id, bonus ) values ( 1, 0 ), ( 2, 0 ), ( 3, 0 );
+insert into bug171_bonuses( empl_id, bonus )
+values
+( 1, 100 ), ( 1, 100 ), ( 1, 100 ),
+( 2, 200 ), ( 2, 200 ), ( 2, 200 ),
+( 3, 300 ), ( 3, 300 ), ( 3, 300 );
+
+select * from bug171_employee;
+select * from bug171_bonuses;
+
+--
+-- The problem query. could not use correlation names in delete.
+--
+
+delete from bug171_employee e
+    where e.empl_id > 2 and e.bonus <
+    (
+        select sum( b.bonus ) from bug171_bonuses b
+        where b.empl_id = e.empl_id
+    );
+select * from bug171_employee;
+
+-- positioned delete with correlation names
+
+autocommit off;
+
+get cursor bug171_c2 as
+'select * from bug171_employee where empl_id = 2 for update';
+next bug171_c2;
+
+delete from bug171_employee e where current of bug171_c2;
+
+close bug171_c2;
+select * from bug171_employee;
+
+autocommit on;
+
+--
+-- Cleanup
+--
+
+drop table bug171_employee;
+drop table bug171_bonuses;

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql?rev=231366&r1=231365&r2=231366&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql Wed Aug 10 18:12:31 2005
@@ -2307,12 +2307,12 @@
  e6.dno = e5.dno and e6.dno in ('K55', 'K52')))))))
  order by 2, 3;
 delete from db2test.dept d where
-  dno in (select dno from db2test.emp e where
- e.dno = d.dno and e.dno in (select dno from db2test.emp2 e2 where
- e2.dno = e.dno and e2.dno in (select dno from db2test.emp3 e3 where
- e3.dno = e2.dno and e3.dno in (select dno from db2test.emp4 e4 where
- e4.dno = e3.dno and e4.dno in (select dno from db2test.emp5 e5 where
- e5.dno = e4.dno and e5.dno in (select dno from db2test.emp6 e6 where
+  d.dno in (select e.dno from db2test.emp e where
+ e.dno = d.dno and e.dno in (select e2.dno from db2test.emp2 e2 where
+ e2.dno = e.dno and e2.dno in (select e3.dno from db2test.emp3 e3 where
+ e3.dno = e2.dno and e3.dno in (select e4.dno from db2test.emp4 e4 where
+ e4.dno = e3.dno and e4.dno in (select e5.dno from db2test.emp5 e5 where
+ e5.dno = e4.dno and e5.dno in (select e6.dno from db2test.emp6 e6 where
  e6.dno = e5.dno and e6.dno in ('K55', 'K52')))))));
 select * from db2test.dept order by dno, dname;
 select * from db2test.emp order by dno, name, mgrname;
@@ -2712,13 +2712,13 @@
                   where e1.name = e.mgrname and e1.mgrname = 'JOHN')))))
    order by 2,3,4;
 delete from db2test.emp e
-  where dno in (select dno from db2test.emp5 e5
-   where e5.dno in (select dno from db2test.emp4 e4 where
-   e5.name = e4.mgrname group by dno having dno in
-     (select dno from db2test.emp3 e3 where e4.dno = e3.dno and
-       e3.dno in (select dno from db2test.emp2 e2
-                  where e3.name = e2.mgrname group by dno having
-                  e2.dno in (select dno from db2test.emp  e1
+  where e.dno in (select e5.dno from db2test.emp5 e5
+   where e5.dno in (select e4.dno from db2test.emp4 e4 where
+   e5.name = e4.mgrname group by e4.dno having dno in
+     (select e3.dno from db2test.emp3 e3 where e4.dno = e3.dno and
+       e3.dno in (select e2.dno from db2test.emp2 e2
+                  where e3.name = e2.mgrname group by e2.dno having
+                  e2.dno in (select e1.dno from db2test.emp  e1
                   where e1.name = e.mgrname and e1.mgrname = 'JOHN')))));
 select * from db2test.emp order by dno, name, mgrname;
 select * from db2test.emp2 order by dno, name, mgrname;
@@ -2759,13 +2759,13 @@
                   e2.dno in (select dno from db2test.emp  e1
                    where e.mgrname = 'JOHN'))))) order by 2,3,4;
 delete from db2test.emp e
-  where dno in (select dno from db2test.emp5 e5
-   where e5.dno in (select dno from db2test.emp4 e4 where
-   e.name = e4.mgrname group by dno having dno in
-     (select dno from db2test.emp3 e3 where e.name = e3.mgrname and
-       e3.dno in (select dno from db2test.emp2 e2
-                  where e.name = e2.mgrname group by dno having
-                  e2.dno in (select dno from db2test.emp  e1
+  where e.dno in (select e5.dno from db2test.emp5 e5
+   where e5.dno in (select e4.dno from db2test.emp4 e4 where
+   e.name = e4.mgrname group by e4.dno having e4.dno in
+     (select e3.dno from db2test.emp3 e3 where e.name = e3.mgrname and
+       e3.dno in (select e2.dno from db2test.emp2 e2
+                  where e.name = e2.mgrname group by e2.dno having
+                  e2.dno in (select e1.dno from db2test.emp  e1
                    where e.mgrname = 'JOHN')))));
 select * from db2test.emp order by dno, name, mgrname;
 select * from db2test.emp2 order by dno, name, mgrname;

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/update.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/update.sql?rev=231366&r1=231365&r2=231366&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/update.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/update.sql Wed Aug 10 18:12:31 2005
@@ -251,3 +251,62 @@
 
 autocommit on;
 drop table tab1;
+
+--------------------------------------------
+--
+-- Test upgrade piece of the fix for bug171.
+--
+--------------------------------------------
+
+create table bug171_employee( empl_id int, bonus int );
+create table bug171_bonuses( empl_id int, bonus int );
+
+insert into bug171_employee( empl_id, bonus ) values ( 1, 0 ), ( 2, 0 ), ( 3, 0 );
+insert into bug171_bonuses( empl_id, bonus )
+values
+( 1, 100 ), ( 1, 100 ), ( 1, 100 ),
+( 2, 200 ), ( 2, 200 ), ( 2, 200 ),
+( 3, 300 ), ( 3, 300 ), ( 3, 300 );
+
+select * from bug171_employee;
+select * from bug171_bonuses;
+
+--
+-- The problem query. could not use correlation names in update.
+--
+
+update bug171_employee e
+    set e.bonus =
+    (
+        select sum( b.bonus ) from bug171_bonuses b
+        where b.empl_id = e.empl_id
+    );
+select * from bug171_employee;
+
+-- positioned update with correlation names
+
+autocommit off;
+get cursor bug171_c1 as
+'select * from bug171_employee where empl_id = 1 for update';
+
+next bug171_c1;
+
+update bug171_employee e
+    set e.bonus =
+    (
+        select 2 * sum( b.bonus ) from bug171_bonuses b
+        where b.empl_id = e.empl_id
+    )
+where current of bug171_c1;
+
+close bug171_c1;
+select * from bug171_employee;
+
+autocommit on;
+
+--
+-- Cleanup
+--
+
+drop table bug171_employee;
+drop table bug171_bonuses;



Mime
View raw message