db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject svn commit: r370247 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Wed, 18 Jan 2006 20:22:53 GMT
Author: bandaram
Date: Wed Jan 18 12:22:30 2006
New Revision: 370247

URL: http://svn.apache.org/viewcvs?rev=370247&view=rev
Log:
DERBY-649: Push single table predicates that can be pushed into underlying SELECTs of a union.

Merging from 10.1 branch. It is possible to further improve the patch to handle more cases
for this optimization.

Submitted by Satheesh Bandaram (satheesh@sourcery.org)

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

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java?rev=370247&r1=370246&r2=370247&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java Wed
Jan 18 12:22:30 2006
@@ -1349,15 +1349,17 @@
      * every ColumnReference in the predicate is itself a ColumnReference.
 	 *
 	 * This is useful when attempting to push predicates into non-flattenable
-	 * views or derived tables.
+	 * views or derived tables or into unions.
 	 *
-	 * @param select	The underlying SelectNode.
+	 * @param select			The underlying SelectNode.
+	 * @param copyPredicate		Whether to make a copy of the predicate
+	 *							before pushing
 	 *
 	 * @return Nothing.
 	 *
 	 * @exception StandardException		Thrown on error
 	 */
-	void pushExpressionsIntoSelect(SelectNode select)
+	void pushExpressionsIntoSelect(SelectNode select, boolean copyPredicate)
 		throws StandardException
 	{
 		/* Walk list backwards since we can delete while
@@ -1392,7 +1394,7 @@
 				}
 			}
 
-			if (state)
+			if (state && !copyPredicate)
 			{
 				// keep the counters up to date when removing a predicate
 				if (predicate.isStartKey())
@@ -1408,9 +1410,64 @@
 				predicate.clearScanFlags();
 				// Remove this predicate from the list
 				removeElementAt(index);
-				// Push it into the select
-				select.pushExpressionsIntoSelect(predicate);
 			}
+
+			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))
+					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;
+
+				// Create a copy of the predicate to push down
+				// <column> <relop> <value> AND TRUE
+				BinaryRelationalOperatorNode newRelop = (BinaryRelationalOperatorNode)
+							getNodeFactory().getNode(
+										opNode.getNodeType(),
+										newCRNode,
+										opNode.getRightOperand(),
+										getContextManager());
+
+				newRelop.bindComparisonOperator();
+
+				ValueNode trueNode = (ValueNode) getNodeFactory().getNode(
+										C_NodeTypes.BOOLEAN_CONSTANT_NODE,
+										Boolean.TRUE,
+										getContextManager());
+				AndNode newAnd = (AndNode) getNodeFactory().getNode(
+													C_NodeTypes.AND_NODE,
+													newRelop,
+													trueNode,
+													getContextManager());
+				newAnd.postBindFixup();
+				JBitSet tableMap = new JBitSet(select.referencedTableMap.size());
+				Predicate newPred = (Predicate) getNodeFactory().getNode(
+												C_NodeTypes.PREDICATE,
+												newAnd,
+												tableMap,
+												getContextManager());
+				predicate = newPred;
+			}
+
+			// Push it into the select
+			if (state)
+ 				select.pushExpressionsIntoSelect(predicate);
 		}		
 	}
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java?rev=370247&r1=370246&r2=370247&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java
Wed Jan 18 12:22:30 2006
@@ -953,8 +953,19 @@
 		 */
 		if (pushPList != null && (childResult instanceof SelectNode))
 		{
-			pushPList.pushExpressionsIntoSelect((SelectNode) childResult);
+			pushPList.pushExpressionsIntoSelect((SelectNode) childResult, false);
 		}
+
+		/* 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/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=370247&r1=370246&r2=370247&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java Wed
Jan 18 12:22:30 2006
@@ -293,6 +293,47 @@
 		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.
+	 *
+	 * 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.
+	 *
+	 * @return ColumnReference	If colName could be remapped to a table reference
+	 */
+	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.
 	 *

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java?rev=370247&r1=370246&r2=370247&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UnionNode.java Wed Jan
18 12:22:30 2006
@@ -257,6 +257,34 @@
 		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.
+	 *
+	 * Note that the predicates are not removed after pushing. This is to ensure if
+	 * pushing is not possible or only partially feasible.
+	 */
+	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.
+		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
 	 *

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatesIntoViews.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatesIntoViews.out?rev=370247&r1=370246&r2=370247&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatesIntoViews.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/predicatesIntoViews.out
Wed Jan 18 12:22:30 2006
@@ -1963,6 +1963,437 @@
 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> -- 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> -- 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> 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.
@@ -6436,4 +6867,67 @@
 Unknown return value: false
 Negate comparison result: false
 ij> rollback;
+ij> autocommit on;
+ij> CREATE TABLE D1 (A INT, B VARCHAR(4) FOR BIT DATA);
+0 rows inserted/updated/deleted
+ij> INSERT INTO D1 VALUES (1, x'600Eaaef') ;
+1 row inserted/updated/deleted
+ij> INSERT INTO D1 VALUES (2, x'83452213') ;
+1 row inserted/updated/deleted
+ij> select * from D1 where B IN (x'600Eaaef',x'83452213') ;
+A          |B       
+--------------------
+1          |600eaaef
+2          |83452213
+ij> select * from D1 where B IN (x'83452213') ;
+A          |B       
+--------------------
+2          |83452213
+ij> select * from D1 where B  IN (x'600Eaaef') ;
+A          |B       
+--------------------
+1          |600eaaef
+ij> CREATE VIEW V1 AS SELECT A,B FROM D1 UNION SELECT A,B FROM D1;
+0 rows inserted/updated/deleted
+ij> SELECT * FROM V1;
+A          |B       
+--------------------
+1          |600eaaef
+2          |83452213
+ij> select * from V1 where B IN (x'83452213') ;
+A          |B       
+--------------------
+2          |83452213
+ij> select * from V1 where B  IN (x'600Eaaef') ;
+A          |B       
+--------------------
+1          |600eaaef
+ij> select * from V1 where B  = x'600Eaaef' ;
+A          |B       
+--------------------
+1          |600eaaef
+ij> -- these all failed with the initial patch to DERBY-649.
+select * from V1 where B IN (x'600Eaaef',x'83452213') ;
+A          |B       
+--------------------
+1          |600eaaef
+2          |83452213
+ij> select * from V1 where B  >= x'600Eaaef' ;
+A          |B       
+--------------------
+1          |600eaaef
+2          |83452213
+ij> select * from V1 where B  <= x'83452213' ;
+A          |B       
+--------------------
+1          |600eaaef
+2          |83452213
+ij> select * from V1 where B  <> x'83452213' ;
+A          |B       
+--------------------
+1          |600eaaef
+ij> DROP VIEW V1;
+0 rows inserted/updated/deleted
+ij> DROP TABLE D1;
+0 rows inserted/updated/deleted
 ij> 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatesIntoViews.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatesIntoViews.sql?rev=370247&r1=370246&r2=370247&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatesIntoViews.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/predicatesIntoViews.sql
Wed Jan 18 12:22:30 2006
@@ -87,6 +87,34 @@
 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;
+ 
+-- 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(); 
+
+-- 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(); 
+
+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.
@@ -4196,3 +4224,32 @@
 values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
 
 rollback;
+
+autocommit on;
+CREATE TABLE D1 (A INT, B VARCHAR(4) FOR BIT DATA);
+
+INSERT INTO D1 VALUES (1, x'600Eaaef') ; 
+INSERT INTO D1 VALUES (2, x'83452213') ; 
+
+select * from D1 where B IN (x'600Eaaef',x'83452213') ;  
+select * from D1 where B IN (x'83452213') ; 
+select * from D1 where B  IN (x'600Eaaef') ; 
+
+CREATE VIEW V1 AS SELECT A,B FROM D1 UNION SELECT A,B FROM D1;
+
+SELECT * FROM V1;
+
+
+select * from V1 where B IN (x'83452213') ; 
+select * from V1 where B  IN (x'600Eaaef') ; 
+
+select * from V1 where B  = x'600Eaaef' ;
+
+-- these all failed with the initial patch to DERBY-649.
+select * from V1 where B IN (x'600Eaaef',x'83452213') ;  
+select * from V1 where B  >= x'600Eaaef' ;
+select * from V1 where B  <= x'83452213' ;
+select * from V1 where B  <> x'83452213' ;
+
+DROP VIEW V1;
+DROP TABLE D1;
\ No newline at end of file



Mime
View raw message