You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ba...@apache.org on 2006/01/18 21:22:53 UTC
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/
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