db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject svn commit: r357415 - in /db/derby/code/branches/10.1/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Sun, 18 Dec 2005 02:05:21 GMT
Author: bandaram
Date: Sat Dec 17 18:05:13 2005
New Revision: 357415

URL: http://svn.apache.org/viewcvs?rev=357415&view=rev
Log:
DERBY-772: Improve fix for bug derby-649 to allow IN list optimization.

Now IN lists of the form REF IN <constantList> can be pushed from Unions into underlying
select statements.

Submitted by Satheesh Bandaram (satheesh@sourcery.org)

Modified:
    db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java
    db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
    db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java
    db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/predicatesIntoViews.out
    db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatesIntoViews.sql

Modified: db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java?rev=357415&r1=357414&r2=357415&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java
(original)
+++ db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java
Sat Dec 17 18:05:13 2005
@@ -1392,55 +1392,71 @@
 				}
 			}
 
-			if (state && !copyPredicate)
-			{
-				// keep the counters up to date when removing a predicate
-				if (predicate.isStartKey())
-					numberOfStartPredicates--;
-				if (predicate.isStopKey())
-					numberOfStopPredicates--;
-				if (predicate.isQualifier())
-					numberOfQualifiers--;
-
-				/* Clear all of the scan flags since they may be different
-				 * due to the splitting of the list.
-				 */
-				predicate.clearScanFlags();
-				// Remove this predicate from the list
-				removeElementAt(index);
-			}
+			if (!state)
+				continue;
 
 			if (copyPredicate)
 			{
 				// Copy this predicate and push this instead
 				AndNode andNode = predicate.getAndNode();
-
-				// Make sure we are only pushing simple binary relations for now
-				// It should be benificial to push expressions that can be pushed, so they can be applied
-				// closer to the data.
-				if (! (andNode.getLeftOperand() instanceof BinaryRelationalOperatorNode))
-					continue;
-
-				BinaryRelationalOperatorNode opNode = (BinaryRelationalOperatorNode) andNode.getLeftOperand();
-				if (! (opNode.getLeftOperand() instanceof ColumnReference) ||
-				    ! (opNode.getRightOperand() instanceof ConstantNode ||
-					 opNode.getRightOperand() instanceof ParameterNode))
+				ValueNode leftOperand;
+				ColumnReference crNode;
+				BinaryRelationalOperatorNode opNode=null;
+				InListOperatorNode inNode=null;
+
+				// Make sure we are only pushing binary relations and InList for
+				// copyPredicate case. It should be benificial to push expressions that
+				// can be pushed, so they can be applied closer to the data.
+
+				if (andNode.getLeftOperand() instanceof BinaryRelationalOperatorNode)
+				{
+					opNode = (BinaryRelationalOperatorNode) andNode.getLeftOperand();
+					// Investigate using invariant interface to check rightOperand
+					if (! (opNode.getLeftOperand() instanceof ColumnReference) ||
+					    ! (opNode.getRightOperand() instanceof ConstantNode ||
+							 opNode.getRightOperand() instanceof ParameterNode))
+						continue;
+
+					crNode = (ColumnReference) opNode.getLeftOperand();
+				}
+				else if (andNode.getLeftOperand() instanceof InListOperatorNode)
+				{
+					inNode = (InListOperatorNode) andNode.getLeftOperand();
+					if (! (inNode.getRightOperandList().isConstantExpression()))
+						continue;
+
+					crNode = (ColumnReference) inNode.getLeftOperand();
+				}
+				else
 					continue;
 
-				ColumnReference crNode = (ColumnReference) opNode.getLeftOperand();
 				// Remap this crNode to underlying column reference in the select, if possible.
 				ColumnReference newCRNode = select.findColumnReferenceInResult(crNode.columnName);
 				if (newCRNode == null)
 					continue;
 
-				BinaryRelationalOperatorNode newEquals = (BinaryRelationalOperatorNode)
+				if (andNode.getLeftOperand() instanceof BinaryRelationalOperatorNode)
+				{
+					BinaryRelationalOperatorNode newEquals = (BinaryRelationalOperatorNode)
 							getNodeFactory().getNode(
 										C_NodeTypes.BINARY_EQUALS_OPERATOR_NODE,
 										newCRNode,
 										opNode.getRightOperand(),
 										getContextManager());
-
-				newEquals.bindComparisonOperator();
+					newEquals.bindComparisonOperator();
+					leftOperand = newEquals;
+				}
+				else
+				{
+					InListOperatorNode newInNode = (InListOperatorNode)
+							getNodeFactory().getNode(
+								C_NodeTypes.IN_LIST_OPERATOR_NODE,
+								newCRNode,
+								inNode.getRightOperandList(),
+								getContextManager());
+					newInNode.setType(inNode.getTypeServices());
+					leftOperand = newInNode;
+				}
 
 				ValueNode trueNode = (ValueNode) getNodeFactory().getNode(
 										C_NodeTypes.BOOLEAN_CONSTANT_NODE,
@@ -1448,22 +1464,39 @@
 										getContextManager());
 				AndNode newAnd = (AndNode) getNodeFactory().getNode(
 													C_NodeTypes.AND_NODE,
-													newEquals,
+													leftOperand,
 													trueNode,
 													getContextManager());
 				newAnd.postBindFixup();
 				JBitSet tableMap = new JBitSet(select.referencedTableMap.size());
-				Predicate newPred = (Predicate) getNodeFactory().getNode(
+
+				// Use newly constructed predicate
+				predicate = (Predicate) getNodeFactory().getNode(
 												C_NodeTypes.PREDICATE,
 												newAnd,
 												tableMap,
 												getContextManager());
-				predicate = newPred;
+			}
+			else
+			{
+				// keep the counters up to date when removing a predicate
+				if (predicate.isStartKey())
+					numberOfStartPredicates--;
+				if (predicate.isStopKey())
+					numberOfStopPredicates--;
+				if (predicate.isQualifier())
+					numberOfQualifiers--;
+
+				/* Clear all of the scan flags since they may be different
+				 * due to the splitting of the list.
+				 */
+				predicate.clearScanFlags();
+				// Remove this predicate from the list
+				removeElementAt(index);
 			}
 
 			// Push it into the select
-			if (state)
- 				select.pushExpressionsIntoSelect(predicate);
+ 			select.pushExpressionsIntoSelect(predicate);
 		}		
 	}
 

Modified: db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=357415&r1=357414&r2=357415&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
(original)
+++ db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
Sat Dec 17 18:05:13 2005
@@ -293,16 +293,16 @@
 		return groupByList;
 	}
 
-	/*
-	 * DERBY-649: Find colName in the result columns and return underlying
-	 * columnReference.  This is useful for pushing union predicates into underlying
-	 * select statements.
+	/**
+	 * Find colName in the result columns and return underlying columnReference.
+	 * Note that this function returns null if there are more than one FromTable
+	 * for this SelectNode and the columnReference needs to be directly under
+	 * the resultColumn. So having an expression under the resultSet would cause
+	 * returning null.
 	 *
-	 * Handle the case of single table selects for now. Also if there is an
-	 * expression under the result column, it is not possible yet to push the
-	 * predicates for now.
+	 * @param	colName		Name of the column
 	 *
-	 * @return ColumnReference	If colName could be remapped to a table reference
+	 * @return	ColumnReference	ColumnReference to the column, if found
 	 */
 	public ColumnReference findColumnReferenceInResult(String colName)
 					throws StandardException

Modified: db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java?rev=357415&r1=357414&r2=357415&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java
(original)
+++ db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java
Sat Dec 17 18:05:13 2005
@@ -257,23 +257,35 @@
 		return costEstimate;
 	}
 
-	/*
-	 * DERBY-649: Handle pushing predicates into UnionNodes. For now, we only push simple
-	 * single column predicates that are binaryOperations. It should be possible to expand
-	 * this logic to cover more cases. Even pushing expressions (like a+b = 10) into SELECTs
-	 * would improve performance, even if they don't make Qualifiers. It would mean
-	 * evaluating expressions closer to data and hence could avoid sorting or other
-	 * overheads that UNION may require.
+	/**
+	 * DERBY-649: Handle pushing predicates into UnionNodes. It is possible to push
+	 * single table predicates that are binaryOperations or inListOperations. 
+	 *
+	 * Predicates of the form <columnReference> <RELOP> <constant> or <columnReference>
+	 * IN <constantList> are currently handled. Since these predicates would allow
+	 * optimizer to pick available indices, pushing them provides maximum benifit.
+	 *
+	 * It should be possible to expand this logic to cover more cases. Even pushing
+	 * expressions (like a+b = 10) into SELECTs would improve performance, even if
+	 * they don't allow use of index. It would mean evaluating expressions closer to
+	 * data and hence could avoid sorting or other overheads that UNION may require.
 	 *
 	 * Note that the predicates are not removed after pushing. This is to ensure if
 	 * pushing is not possible or only partially feasible.
+	 *
+	 * @param 	predicateList		List of single table predicates to push
+	 *
+	 * @return	Nothing
+	 *
+	 * @exception	StandardException		Thrown on error
 	 */
 	public void pushExpressions(PredicateList predicateList)
 					throws StandardException
 	{
 		// If left or right side is a UnionNode, further push the predicate list
 		// Note, it is OK not to push these predicates since they are also evaluated
-		// in the ProjectRestrictNode.
+		// in the ProjectRestrictNode. There are other types of operations possible
+		// here in addition to UnionNode or SelectNode, like RowResultSetNode.
 		if (leftResultSet instanceof UnionNode)
 			((UnionNode)leftResultSet).pushExpressions(predicateList);
 		else if (leftResultSet instanceof SelectNode)

Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/predicatesIntoViews.out
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/predicatesIntoViews.out?rev=357415&r1=357414&r2=357415&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/predicatesIntoViews.out
(original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/master/predicatesIntoViews.out
Sat Dec 17 18:05:13 2005
@@ -1977,6 +1977,8 @@
 ij> create view test.view1(a,b) as select all a,b from test.table1 union all select a,b
from test.table2
 			union all select 1,1 from test.table1;
 0 rows inserted/updated/deleted
+ij> create view test.view2(c,d) as select all a+1,b+1 from test.table1 union all select
a,b from test.table2;
+0 rows inserted/updated/deleted
 ij> -- Following Selects using the tables directly would use index
 CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
 0 rows inserted/updated/deleted
@@ -2244,6 +2246,106 @@
 	Ordered null semantics on the following columns: 
 					qualifiers:
 None
+ij> -- Can't use index for the following
+select a from test.view0 where b=25+a;
+A          
+-----------
+ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                       
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         

                                    
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -----------------------------------
+Statement Name: 
+	null
+Statement Text: 
+	-- Can't use index for the following
+select a from test.view0 where b=25+a
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text: 
+Project-Restrict ResultSet (5):
+Number of opens = 1
+Rows seen = 0
+Rows filtered = 0
+restriction = false
+projection = true
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
+	restriction time (milliseconds) = 0
+	projection time (milliseconds) = 0
+Source result set:
+	Project-Restrict ResultSet (4):
+	Number of opens = 1
+	Rows seen = 0
+	Rows filtered = 0
+	restriction = true
+	projection = false
+		constructor time (milliseconds) = 0
+		open time (milliseconds) = 0
+		next time (milliseconds) = 0
+		close time (milliseconds) = 0
+		restriction time (milliseconds) = 0
+		projection time (milliseconds) = 0
+	Source result set:
+		Union ResultSet:
+		Number of opens = 1
+		Rows seen from the left = 0
+		Rows seen from the right = 0
+		Rows returned = 0
+			constructor time (milliseconds) = 0
+			open time (milliseconds) = 0
+			next time (milliseconds) = 0
+			close time (milliseconds) = 0
+		Left result set:
+			Table Scan ResultSet for TABLE1 at serializable isolation level using share table locking
chosen by the optimizer
+			Number of opens = 1
+			Rows seen = 0
+			Rows filtered = 0
+			Fetch Size = 1
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+			scan information: 
+				Bit set of columns fetched={0, 1}
+				Number of columns fetched=2
+				Number of pages visited=1
+				Number of rows qualified=0
+				Number of rows visited=0
+				Scan type=heap
+				start position: 
+null				stop position: 
+null				qualifiers:
+None
+		Right result set:
+			Table Scan ResultSet for TABLE2 at serializable isolation level using share table locking
chosen by the optimizer
+			Number of opens = 1
+			Rows seen = 0
+			Rows filtered = 0
+			Fetch Size = 1
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+			scan information: 
+				Bit set of columns fetched={0, 1}
+				Number of columns fetched=2
+				Number of pages visited=1
+				Number of rows qualified=0
+				Number of rows visited=0
+				Scan type=heap
+				start position: 
+null				stop position: 
+null				qualifiers:
+None
 ij> -- This select should use index for first two selects, table scan for the third
 select a from test.view1 where b=25;
 A          
@@ -2391,6 +2493,117 @@
 					stop position: 
 	None
 					qualifiers:
+None
+ij> select d from test.view2 where d=25;
+D          
+-----------
+ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                       
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         

                                    
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -----------------------------------
+Statement Name: 
+	null
+Statement Text: 
+	select d from test.view2 where d=25
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text: 
+Project-Restrict ResultSet (6):
+Number of opens = 1
+Rows seen = 0
+Rows filtered = 0
+restriction = false
+projection = true
+	constructor time (milliseconds) = 0
+	open time (milliseconds) = 0
+	next time (milliseconds) = 0
+	close time (milliseconds) = 0
+	restriction time (milliseconds) = 0
+	projection time (milliseconds) = 0
+Source result set:
+	Project-Restrict ResultSet (5):
+	Number of opens = 1
+	Rows seen = 0
+	Rows filtered = 0
+	restriction = true
+	projection = false
+		constructor time (milliseconds) = 0
+		open time (milliseconds) = 0
+		next time (milliseconds) = 0
+		close time (milliseconds) = 0
+		restriction time (milliseconds) = 0
+		projection time (milliseconds) = 0
+	Source result set:
+		Union ResultSet:
+		Number of opens = 1
+		Rows seen from the left = 0
+		Rows seen from the right = 0
+		Rows returned = 0
+			constructor time (milliseconds) = 0
+			open time (milliseconds) = 0
+			next time (milliseconds) = 0
+			close time (milliseconds) = 0
+		Left result set:
+			Project-Restrict ResultSet (3):
+			Number of opens = 1
+			Rows seen = 0
+			Rows filtered = 0
+			restriction = false
+			projection = true
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+				restriction time (milliseconds) = 0
+				projection time (milliseconds) = 0
+			Source result set:
+				Table Scan ResultSet for TABLE1 at serializable isolation level using share table locking
chosen by the optimizer
+				Number of opens = 1
+				Rows seen = 0
+				Rows filtered = 0
+				Fetch Size = 1
+					constructor time (milliseconds) = 0
+					open time (milliseconds) = 0
+					next time (milliseconds) = 0
+					close time (milliseconds) = 0
+				scan information: 
+					Bit set of columns fetched={0, 1}
+					Number of columns fetched=2
+					Number of pages visited=1
+					Number of rows qualified=0
+					Number of rows visited=0
+					Scan type=heap
+					start position: 
+null					stop position: 
+null					qualifiers:
+None
+		Right result set:
+			Table Scan ResultSet for TABLE2 at serializable isolation level using share table locking
chosen by the optimizer
+			Number of opens = 1
+			Rows seen = 0
+			Rows filtered = 0
+			Fetch Size = 1
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+			scan information: 
+				Bit set of columns fetched={0, 1}
+				Number of columns fetched=2
+				Number of pages visited=1
+				Number of rows qualified=0
+				Number of rows visited=0
+				Scan type=heap
+				start position: 
+null				stop position: 
+null				qualifiers:
 None
 ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0);
 0 rows inserted/updated/deleted

Modified: db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatesIntoViews.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatesIntoViews.sql?rev=357415&r1=357414&r2=357415&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatesIntoViews.sql
(original)
+++ db/derby/code/branches/10.1/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatesIntoViews.sql
Sat Dec 17 18:05:13 2005
@@ -98,6 +98,8 @@
 create view test.view1(a,b) as select all a,b from test.table1 union all select a,b from
test.table2
 			union all select 1,1 from test.table1;
  
+create view test.view2(c,d) as select all a+1,b+1 from test.table1 union all select a,b from
test.table2; 
+
 -- Following Selects using the tables directly would use index
 CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 
 select a from test.table1 where b=25; 
@@ -109,8 +111,15 @@
 select a from test.view0 where b=25; 
 VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 
 
+-- Can't use index for the following
+select a from test.view0 where b=25+a;
+VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 
+
 -- This select should use index for first two selects, table scan for the third
 select a from test.view1 where b=25; 
+VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 
+
+select d from test.view2 where d=25; 
 VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 
 
 CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0); 



Mime
View raw message