db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject svn commit: r358426 - 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 Thu, 22 Dec 2005 00:53:54 GMT
Author: bandaram
Date: Wed Dec 21 16:53:47 2005
New Revision: 358426

URL: http://svn.apache.org/viewcvs?rev=358426&view=rev
Log:
DERBY-772: Optimize IN predicates on derived tables with UNIONs by pushing
them into underlying select statements.

Add some test cases to show index being used. Will also add more tests in another pending
change to improve finding matching column.

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=358426&r1=358425&r2=358426&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
Wed Dec 21 16:53:47 2005
@@ -1392,42 +1392,44 @@
 				}
 			}
 
-			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)
@@ -1435,37 +1437,69 @@
 
 				// Create a copy of the predicate to push down
 				// <column> <relop> <value> AND TRUE
-				BinaryRelationalOperatorNode newRelop = (BinaryRelationalOperatorNode)
+				if (andNode.getLeftOperand() instanceof BinaryRelationalOperatorNode)
+				{
+					BinaryRelationalOperatorNode newRelop = (BinaryRelationalOperatorNode)
 							getNodeFactory().getNode(
 										opNode.getNodeType(),
 										newCRNode,
 										opNode.getRightOperand(),
 										getContextManager());
+					newRelop.bindComparisonOperator();
+					leftOperand = newRelop;
+				}
+				else
+				{
+					InListOperatorNode newInNode = (InListOperatorNode)
+							getNodeFactory().getNode(
+								C_NodeTypes.IN_LIST_OPERATOR_NODE,
+								newCRNode,
+								inNode.getRightOperandList(),
+								getContextManager());
+					newInNode.setType(inNode.getTypeServices());
+					leftOperand = newInNode;
+				}
 
-				newRelop.bindComparisonOperator();
-
+				// Convert the predicate into CNF form
 				ValueNode trueNode = (ValueNode) getNodeFactory().getNode(
 										C_NodeTypes.BOOLEAN_CONSTANT_NODE,
 										Boolean.TRUE,
 										getContextManager());
 				AndNode newAnd = (AndNode) getNodeFactory().getNode(
 													C_NodeTypes.AND_NODE,
-													newRelop,
+													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=358426&r1=358425&r2=358426&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
Wed Dec 21 16:53:47 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=358426&r1=358425&r2=358426&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
Wed Dec 21 16:53:47 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=358426&r1=358425&r2=358426&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
Wed Dec 21 16:53:47 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,388 @@
 	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> -- Inlist tests
+select * from test.view0 where b in (1, 2, 3);
+A          |B          
+-----------------------
+ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                       
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         

                                    
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -----------------------------------
+Statement Name: 
+	null
+Statement Text: 
+	-- Inlist tests
+select * from test.view0 where b in (1, 2, 3)
+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 (8):
+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 (4):
+		Number of opens = 1
+		Rows seen = 0
+		Rows filtered = 0
+		restriction = true
+		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:
+			Index Row to Base Row ResultSet for TABLE1:
+			Number of opens = 1
+			Rows seen = 0
+			Columns accessed from heap = {0}
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+				Index Scan ResultSet for TABLE1 using index TABLE1IDX at serializable isolation level
using share row 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=All
+					Number of columns fetched=2
+					Number of deleted rows visited=0
+					Number of pages visited=1
+					Number of rows qualified=0
+					Number of rows visited=0
+					Scan type=btree
+					Tree height=1
+					start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+					stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+					qualifiers:
+None
+	Right result set:
+		Project-Restrict ResultSet (7):
+		Number of opens = 1
+		Rows seen = 0
+		Rows filtered = 0
+		restriction = true
+		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:
+			Index Row to Base Row ResultSet for TABLE2:
+			Number of opens = 1
+			Rows seen = 0
+			Columns accessed from heap = {0}
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+				Index Scan ResultSet for TABLE2 using index TABLE2IDX at serializable isolation level
using share row 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=All
+					Number of columns fetched=2
+					Number of deleted rows visited=0
+					Number of pages visited=1
+					Number of rows qualified=0
+					Number of rows visited=0
+					Scan type=btree
+					Tree height=1
+					start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+					stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+					qualifiers:
+None
+ij> prepare s as 'select * from test.view0 where b in (?, ?, ?)';
+ij> execute s using 'values (1,2,3)';
+IJ WARNING: Autocommit may close using result set
+A          |B          
+-----------------------
+ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1                                                                                       
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         

                                    
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -----------------------------------
+Statement Name: 
+	null
+Statement Text: 
+	select * from test.view0 where b in (?, ?, ?)
+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 (8):
+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 (4):
+		Number of opens = 1
+		Rows seen = 0
+		Rows filtered = 0
+		restriction = true
+		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:
+			Index Row to Base Row ResultSet for TABLE1:
+			Number of opens = 1
+			Rows seen = 0
+			Columns accessed from heap = {0}
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+				Index Scan ResultSet for TABLE1 using index TABLE1IDX at serializable isolation level
using share row 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=All
+					Number of columns fetched=2
+					Number of deleted rows visited=0
+					Number of pages visited=1
+					Number of rows qualified=0
+					Number of rows visited=0
+					Scan type=btree
+					Tree height=1
+					start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+					stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+					qualifiers:
+None
+	Right result set:
+		Project-Restrict ResultSet (7):
+		Number of opens = 1
+		Rows seen = 0
+		Rows filtered = 0
+		restriction = true
+		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:
+			Index Row to Base Row ResultSet for TABLE2:
+			Number of opens = 1
+			Rows seen = 0
+			Columns accessed from heap = {0}
+				constructor time (milliseconds) = 0
+				open time (milliseconds) = 0
+				next time (milliseconds) = 0
+				close time (milliseconds) = 0
+				Index Scan ResultSet for TABLE2 using index TABLE2IDX at serializable isolation level
using share row 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=All
+					Number of columns fetched=2
+					Number of deleted rows visited=0
+					Number of pages visited=1
+					Number of rows qualified=0
+					Number of rows visited=0
+					Scan type=btree
+					Tree height=1
+					start position: 
+	>= on first 1 column(s).
+	Ordered null semantics on the following columns: 
+					stop position: 
+	> on first 1 column(s).
+	Ordered null semantics on the following columns: 
+					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 +2775,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=358426&r1=358425&r2=358426&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
Wed Dec 21 16:53:47 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,10 +111,26 @@
 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(); 
+
+-- Inlist tests
+select * from test.view0 where b in (1, 2, 3);
+VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 
+
+prepare s as 'select * from test.view0 where b in (?, ?, ?)';
+
+execute s using 'values (1,2,3)';
+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); 
 
 -- Beetle 4983, customer case, complex query involving views, wrong column remapping
@@ -4252,4 +4270,4 @@
 select * from V1 where B  <> x'83452213' ;
 
 DROP VIEW V1;
-DROP TABLE D1;
\ No newline at end of file
+DROP TABLE D1;



Mime
View raw message