db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mi...@apache.org
Subject 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...
Date Sun, 17 Sep 2006 03:57:06 GMT
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;



Mime
View raw message