db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r358115 - 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 Tue, 20 Dec 2005 21:51:48 GMT
Author: djd
Date: Tue Dec 20 13:51:42 2005
New Revision: 358115

URL: http://svn.apache.org/viewcvs?rev=358115&view=rev
Log:
DERBY-649 DERBY-772 Revert changes that cause a regression. See DERBY-649 for details.

Merge commands used to revert were:

svn merge -r 357415:357414 https://svn.apache.org/repos/asf/db/derby/code/branches/10.1
svn merge -r 357105:357104 https://svn.apache.org/repos/asf/db/derby/code/branches/10.1
svn merge -r 357055:357054 https://svn.apache.org/repos/asf/db/derby/code/branches/10.1


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/ProjectRestrictNode.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=358115&r1=358114&r2=358115&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
Tue Dec 20 13:51:42 2005
@@ -1347,17 +1347,15 @@
      * every ColumnReference in the predicate is itself a ColumnReference.
 	 *
 	 * This is useful when attempting to push predicates into non-flattenable
-	 * views or derived tables or into unions.
+	 * views or derived tables.
 	 *
-	 * @param select			The underlying SelectNode.
-	 * @param copyPredicate		Whether to make a copy of the predicate
-	 *							before pushing
+	 * @param select	The underlying SelectNode.
 	 *
 	 * @return Nothing.
 	 *
 	 * @exception StandardException		Thrown on error
 	 */
-	void pushExpressionsIntoSelect(SelectNode select, boolean copyPredicate)
+	void pushExpressionsIntoSelect(SelectNode select)
 		throws StandardException
 	{
 		/* Walk list backwards since we can delete while
@@ -1392,92 +1390,7 @@
 				}
 			}
 
-			if (!state)
-				continue;
-
-			if (copyPredicate)
-			{
-				// Copy this predicate and push this instead
-				AndNode andNode = predicate.getAndNode();
-				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;
-
-				// Remap this crNode to underlying column reference in the select, if possible.
-				ColumnReference newCRNode = select.findColumnReferenceInResult(crNode.columnName);
-				if (newCRNode == null)
-					continue;
-
-				if (andNode.getLeftOperand() instanceof BinaryRelationalOperatorNode)
-				{
-					BinaryRelationalOperatorNode newEquals = (BinaryRelationalOperatorNode)
-							getNodeFactory().getNode(
-										C_NodeTypes.BINARY_EQUALS_OPERATOR_NODE,
-										newCRNode,
-										opNode.getRightOperand(),
-										getContextManager());
-					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,
-										Boolean.TRUE,
-										getContextManager());
-				AndNode newAnd = (AndNode) getNodeFactory().getNode(
-													C_NodeTypes.AND_NODE,
-													leftOperand,
-													trueNode,
-													getContextManager());
-				newAnd.postBindFixup();
-				JBitSet tableMap = new JBitSet(select.referencedTableMap.size());
-
-				// Use newly constructed predicate
-				predicate = (Predicate) getNodeFactory().getNode(
-												C_NodeTypes.PREDICATE,
-												newAnd,
-												tableMap,
-												getContextManager());
-			}
-			else
+			if (state)
 			{
 				// keep the counters up to date when removing a predicate
 				if (predicate.isStartKey())
@@ -1493,10 +1406,9 @@
 				predicate.clearScanFlags();
 				// Remove this predicate from the list
 				removeElementAt(index);
+				// Push it into the select
+				select.pushExpressionsIntoSelect(predicate);
 			}
-
-			// Push it into the select
- 			select.pushExpressionsIntoSelect(predicate);
 		}		
 	}
 

Modified: db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java?rev=358115&r1=358114&r2=358115&view=diff
==============================================================================
--- db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
(original)
+++ db/derby/code/branches/10.1/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
Tue Dec 20 13:51:42 2005
@@ -953,19 +953,8 @@
 		 */
 		if (pushPList != null && (childResult instanceof SelectNode))
 		{
-			pushPList.pushExpressionsIntoSelect((SelectNode) childResult, false);
+			pushPList.pushExpressionsIntoSelect((SelectNode) childResult);
 		}
-
-		/* DERBY-649: Push simple predicates into Unions. It would be up to UnionNode
-		 * to decide if these predicates can be pushed further into underlying SelectNodes
-		 * or UnionNodes.  Note, we also keep the predicateList at this
-		 * ProjectRestrictNode in case the predicates are not pushable or only
-		 * partially pushable.
-		 *
-		 * It is possible to expand this optimization in UnionNode later.
-		 */
-		if (pushPList != null && (childResult instanceof UnionNode))
-			((UnionNode)childResult).pushExpressions(pushPList);
 
 		if (restrictionList == null)
 		{

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=358115&r1=358114&r2=358115&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
Tue Dec 20 13:51:42 2005
@@ -294,47 +294,6 @@
 	}
 
 	/**
-	 * 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.
-	 *
-	 * @param	colName		Name of the column
-	 *
-	 * @return	ColumnReference	ColumnReference to the column, if found
-	 */
-	public ColumnReference findColumnReferenceInResult(String colName)
-					throws StandardException
-	{
-		if (fromList.size() != 1)
-			return null;
-
-		// This logic is similar to SubQueryNode.singleFromBaseTable(). Refactor
-		FromTable ft = (FromTable) fromList.elementAt(0);
-		if (! ((ft instanceof ProjectRestrictNode) &&
-		 		((ProjectRestrictNode) ft).getChildResult() instanceof FromBaseTable) &&
-				!(ft instanceof FromBaseTable))
-			return null;
-
-		// Loop through the result columns looking for a match
-		int rclSize = resultColumns.size();
-		for (int index = 0; index < rclSize; index++)
-		{
-			ResultColumn rc = (ResultColumn) resultColumns.elementAt(index);
-			if (! (rc.getExpression() instanceof ColumnReference))
-				return null;
-
-			ColumnReference crNode = (ColumnReference) rc.getExpression();
-
-			if (crNode.columnName.equals(colName))
-				return (ColumnReference) crNode.getClone();
-		}
-
-		return null;
-	}
-
-	/**
 	 * Return the whereClause for this SelectNode.
 	 *
 	 * @return ValueNode	The whereClause for this SelectNode.

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=358115&r1=358114&r2=358115&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
Tue Dec 20 13:51:42 2005
@@ -258,46 +258,6 @@
 	}
 
 	/**
-	 * 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. 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)
-			predicateList.pushExpressionsIntoSelect((SelectNode)leftResultSet, true);
-
-		if (rightResultSet instanceof UnionNode)
-			((UnionNode)rightResultSet).pushExpressions(predicateList);
-		else if (rightResultSet instanceof SelectNode)
-			predicateList.pushExpressionsIntoSelect((SelectNode)rightResultSet, true);
-	}
-
-	/**
 	 * @see Optimizable#modifyAccessPath
 	 *
 	 * @exception StandardException		Thrown on error

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=358115&r1=358114&r2=358115&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
Tue Dec 20 13:51:42 2005
@@ -1963,650 +1963,6 @@
 0 rows inserted/updated/deleted
 ij> drop table t1;
 0 rows inserted/updated/deleted
-ij> -- DERBY-649: Test for making sure predicates are pushed into unions.
-create table test.table1(a integer, b integer, c integer);
-0 rows inserted/updated/deleted
-ij> create index test.table1idx on test.table1(b);
-0 rows inserted/updated/deleted
-ij> create table test.table2(a integer, b integer, c integer);
-0 rows inserted/updated/deleted
-ij> create index test.table2idx on test.table2(b);
-0 rows inserted/updated/deleted
-ij> create view test.view0 as select all a,b from test.table1 union all select a,b from
test.table2;
-0 rows inserted/updated/deleted
-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
-ij> select a from test.table1 where b=25;
-A          
------------
-ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1                                                                                       
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         

                                    
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -----------------------------------
-Statement Name: 
-	null
-Statement Text: 
-	select a from test.table1 where b=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 (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:
-	Index Row to Base Row ResultSet for TABLE1:
-	Number of opens = 1
-	Rows seen = 0
-	Columns accessed from heap = {0, 1}
-		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 = 16
-			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> select a from test.table2 where b=25;
-A          
------------
-ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1                                                                                       
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         

                                    
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -----------------------------------
-Statement Name: 
-	null
-Statement Text: 
-	select a from test.table2 where b=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 (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:
-	Index Row to Base Row ResultSet for TABLE2:
-	Number of opens = 1
-	Rows seen = 0
-	Columns accessed from heap = {0, 1}
-		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 = 16
-			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 from a view based on the tables would use TableScan before DERBY-649
-select a from test.view0 where b=25;
-A          
------------
-ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1                                                                                       
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         

                                    
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -----------------------------------
-Statement Name: 
-	null
-Statement Text: 
-	-- This select from a view based on the tables would use TableScan before DERBY-649
-select a from test.view0 where b=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 (7):
-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 (6):
-	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:
-			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:
-			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> -- 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          
------------
-ij> VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1                                                                                       
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         

                                    
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -----------------------------------
-Statement Name: 
-	null
-Statement Text: 
-	-- This select should use index for first two selects, table scan for the third
-select a from test.view1 where b=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 (8):
-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 (7):
-	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:
-			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
-		Right result set:
-			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:
-				Index Scan ResultSet for TABLE1 using index TABLE1IDX 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={}
-					Number of columns fetched=0
-					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: 
-	None
-					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
 ij> -- Beetle 4983, customer case, complex query involving views, wrong column remapping
 -- after view flattening, NullPointerException, and didn't get predicate pushed down
 -- all the way to base table.

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=358115&r1=358114&r2=358115&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
Tue Dec 20 13:51:42 2005
@@ -87,43 +87,6 @@
 drop view sv1;
 drop table t1;
 
--- DERBY-649: Test for making sure predicates are pushed into unions.
-create table test.table1(a integer, b integer, c integer); 
-create index test.table1idx on test.table1(b); 
-
-create table test.table2(a integer, b integer, c integer); 
-create index test.table2idx on test.table2(b); 
- 
-create view test.view0 as select all a,b from test.table1 union all select a,b from test.table2;

-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; 
-VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 
-select a from test.table2 where b=25; 
-VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 
-
--- This select from a view based on the tables would use TableScan before DERBY-649
-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); 
-
 -- Beetle 4983, customer case, complex query involving views, wrong column remapping
 -- after view flattening, NullPointerException, and didn't get predicate pushed down
 -- all the way to base table.



Mime
View raw message