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 mi...@apache.org on 2006/09/17 05:57:06 UTC
svn commit: r447011 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/master/DerbyNet/
testing/org/apache/derbyTesting/functionT...
Author: mikem
Date: Sat Sep 16 20:57:05 2006
New Revision: 447011
URL: http://svn.apache.org/viewvc?view=rev&rev=447011
Log:
DERBY-1707
contributed by Mamta A. Satoor
Basically, FromBaseTable.verifyProperties method had following if condition
if (constraintSpecified)
{
ConglomerateDescriptor cd =
dDictionary.getConglomerateDescriptor(
consDesc.getConglomerateId());
String indexName = cd.getConglomerateName();
tableProperties.remove("constraint");
tableProperties.put("index", indexName);
}
The if condition above did not do null check for consDesc. In Sunitha's case,
her sql statement looked as follows
SELECT * FROM t1 --DERBY-PROPERTIES constraint=null;
When null value is supplied for constraint property, consDesc will be null and the code inside the if condition above should not be executed. I changed the if condition above as follows
if (constraintSpecified && consDesc != null)
and now constraint=nulll does not result in null pointer exception. I added a
test case for this in lang/optimizerOverrides.sql and noticed that
constraint=null resulted in an index scan. There is already a test which
checks for index=null and that property results in a table scan. This behavior
seems to be in line with the documentation in Tuning Derby Guide for 10.2
(section name Overriding the default optimizer behavior) which says that if
null is specified for property index, a table scan will be performed on the
table. It does not say anything about a table scan for constraint=null
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/optimizerOverrides.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/optimizerOverrides.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/optimizerOverrides.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?view=diff&rev=447011&r1=447010&r2=447011
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Sat Sep 16 20:57:05 2006
@@ -725,7 +725,6 @@
}
constraintSpecified = true;
- /* Validate constraint name - NULL means table scan */
if (! StringUtil.SQLToUpperCase(value).equals("NULL"))
{
consDesc =
@@ -816,9 +815,9 @@
}
}
- /* If user specified a constraint name, then replace it in the
- * properties list with the underlying index name to simplify
- * the code in the optimizer.
+ /* If user specified a non-null constraint name(DERBY-1707), then
+ * replace it in the properties list with the underlying index name to
+ * simplify the code in the optimizer.
* NOTE: The code to get from the constraint name, for a constraint
* with a backing index, to the index name is convoluted. Given
* the constraint name, we can get the conglomerate id from the
@@ -826,7 +825,7 @@
* the ConglomerateDescriptor from the DataDictionary and, finally,
* we get the index name (conglomerate name) from the ConglomerateDescriptor.
*/
- if (constraintSpecified)
+ if (constraintSpecified && consDesc != null)
{
ConglomerateDescriptor cd =
dDictionary.getConglomerateDescriptor(
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/optimizerOverrides.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/optimizerOverrides.out?view=diff&rev=447011&r1=447010&r2=447011
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/optimizerOverrides.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/optimizerOverrides.out Sat Sep 16 20:57:05 2006
@@ -355,6 +355,58 @@
None
qualifiers:
None
+ij> select * from t1 --derby-properties constraint = null
+;
+C1 |C2 |C3
+-----
+1 |1 |1
+2 |2 |2
+3 |3 |3
+4 |4 |4
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+-----
+Statement Name:
+ SQL_CURSH200C1
+Statement Text:
+ select * from t1 --derby-properties constraint = null
+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:
+User supplied optimizer overrides on T1 are { constraint=NULL }
+Index Scan ResultSet for T1 using index T1_C1C2C3 at read committed isolation level using instantaneous share row locking chosen by the optimizer
+Number of opens = 1
+Rows seen = 4
+Rows filtered = 0
+Fetch Size = 16
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+scan information:
+ Bit set of columns fetched={0, 1, 2}
+ Number of columns fetched=3
+ Number of deleted rows visited=0
+ Number of pages visited=1
+ Number of rows qualified=4
+ Number of rows visited=4
+ Scan type=btree
+ Tree height=1
+ start position:
+ None
+ stop position:
+ None
+ qualifiers:
+None
ij> -- constraint which includes columns in for update of list
select * from t1 --derby-properties constraint = cons1
for update;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/optimizerOverrides.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/optimizerOverrides.out?view=diff&rev=447011&r1=447010&r2=447011
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/optimizerOverrides.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/optimizerOverrides.out Sat Sep 16 20:57:05 2006
@@ -356,6 +356,58 @@
None
qualifiers:
None
+ij> select * from t1 --derby-properties constraint = null
+;
+C1 |C2 |C3
+-----
+1 |1 |1
+2 |2 |2
+3 |3 |3
+4 |4 |4
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+-----
+Statement Name:
+ SQL_CURLH000C1
+Statement Text:
+ select * from t1 --derby-properties constraint = null
+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:
+User supplied optimizer overrides on T1 are { constraint=NULL }
+Index Scan ResultSet for T1 using index T1_C1C2C3 at read committed isolation level using instantaneous share row locking chosen by the optimizer
+Number of opens = 1
+Rows seen = 4
+Rows filtered = 0
+Fetch Size = 16
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+scan information:
+ Bit set of columns fetched={0, 1, 2}
+ Number of columns fetched=3
+ Number of deleted rows visited=0
+ Number of pages visited=1
+ Number of rows qualified=4
+ Number of rows visited=4
+ Scan type=btree
+ Tree height=1
+ start position:
+ None
+ stop position:
+ None
+ qualifiers:
+None
ij> -- constraint which includes columns in for update of list
select * from t1 --derby-properties constraint = cons1
for update;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/optimizerOverrides.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/optimizerOverrides.out?view=diff&rev=447011&r1=447010&r2=447011
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/optimizerOverrides.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/optimizerOverrides.out Sat Sep 16 20:57:05 2006
@@ -356,6 +356,58 @@
None
qualifiers:
None
+ij> select * from t1 --derby-properties constraint = null
+;
+C1 |C2 |C3
+-----------------------------------
+1 |1 |1
+2 |2 |2
+3 |3 |3
+4 |4 |4
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------
+Statement Name:
+ null
+Statement Text:
+ select * from t1 --derby-properties constraint = null
+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:
+User supplied optimizer overrides on T1 are { constraint=NULL }
+Index Scan ResultSet for T1 using index T1_C1C2C3 at read committed isolation level using instantaneous share row locking chosen by the optimizer
+Number of opens = 1
+Rows seen = 4
+Rows filtered = 0
+Fetch Size = 16
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ next time in milliseconds/row = 0
+scan information:
+ Bit set of columns fetched={0, 1, 2}
+ Number of columns fetched=3
+ Number of deleted rows visited=0
+ Number of pages visited=1
+ Number of rows qualified=4
+ Number of rows visited=4
+ Scan type=btree
+ Tree height=1
+ start position:
+ None
+ stop position:
+ None
+ qualifiers:
+None
ij> -- constraint which includes columns in for update of list
select * from t1 --derby-properties constraint = cons1
for update;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql?view=diff&rev=447011&r1=447010&r2=447011
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql Sat Sep 16 20:57:05 2006
@@ -107,6 +107,10 @@
for update of c2, c1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+select * from t1 --derby-properties constraint = null
+;
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
-- constraint which includes columns in for update of list
select * from t1 --derby-properties constraint = cons1
for update;