db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r596824 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java testing/org/apache/derbyTesting/functionTests/master/orderby.out testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
Date Tue, 20 Nov 2007 20:54:02 GMT
Author: bpendleton
Date: Tue Nov 20 12:54:01 2007
New Revision: 596824

URL: http://svn.apache.org/viewvc?rev=596824&view=rev
Log:
DERBY-2352: Assertion failure with order by and group by on SUBSTR expression

For a query plan which involves a sort, the compilation system generates a
method called a "row allocator" which allocates a template row of the
correct shape (correct number of columns of the correct data types) to hold
the row that is being provided to the sorter. The row allocator determines
the data type of the column in the row allocator by examining the 
ResultColumn's type, which in turn is computed by the compilation system
during bind processing.

For the SUBSTR expression, TernaryOperatorNode.substrBind() is called, and
the logic in that method was determining that the data type of the SUBSTR
result depended on the data type of the operand on which SUBSTR was called;
i.e., a SUBSTR of a CHAR became a CHAR, while a SUBSTR of a VARCHAR became
a VARCHAR. But this is incorrect; SUBSTR always returns a VARCHAR type,
unless it is called on a BLOB/CLOB object.

This patch modifies the substrBind() processing to perform result data type
analysis in the same way as is done for the trim family of methods in trimBind.




Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java?rev=596824&r1=596823&r2=596824&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java
Tue Nov 20 12:54:01 2007
@@ -28,22 +28,17 @@
 import org.apache.derby.iapi.sql.compile.C_NodeTypes;
 import org.apache.derby.iapi.sql.compile.Visitable;
 import org.apache.derby.iapi.sql.compile.Visitor;
-import org.apache.derby.iapi.sql.dictionary.DataDictionary;
-import org.apache.derby.iapi.store.access.Qualifier;
 import org.apache.derby.iapi.error.StandardException;
 
 import org.apache.derby.iapi.sql.compile.TypeCompiler;
-import org.apache.derby.iapi.types.NumberDataValue;
 import org.apache.derby.iapi.types.StringDataValue;
 import org.apache.derby.iapi.types.TypeId;
 import org.apache.derby.iapi.types.DataTypeDescriptor;
 
-import org.apache.derby.iapi.store.access.Qualifier;
 import org.apache.derby.iapi.reference.SQLState;
 import org.apache.derby.iapi.reference.ClassName;
 import org.apache.derby.iapi.services.classfile.VMOpcode;
 
-import org.apache.derby.impl.sql.compile.ExpressionClassBuilder;
 import org.apache.derby.iapi.util.JBitSet;
 import org.apache.derby.iapi.util.ReuseFactory;
 
@@ -765,7 +760,7 @@
 			throws StandardException
 	{
 		TypeId	receiverType;
-		TypeId	resultType;
+		TypeId	resultType = TypeId.getBuiltInTypeId(Types.VARCHAR);
 
 		// handle parameters here
 
@@ -813,7 +808,7 @@
 		** Check the type of the receiver - this function is allowed only on
 		** string value types.  
 		*/
-		resultType = receiverType = receiver.getTypeId();
+		receiverType = receiver.getTypeId();
 		switch (receiverType.getJDBCTypeId())
 		{
 			case Types.CHAR:
@@ -825,6 +820,15 @@
 			{
 				throwBadType("SUBSTR", receiverType.getSQLTypeName());
 			}
+		}
+		if ((receiverType.getTypeFormatId() == StoredFormatIds.CLOB_TYPE_ID) ||
+		   (receiverType.getTypeFormatId() == StoredFormatIds.NCLOB_TYPE_ID)) {
+		// special case for CLOBs: if we start with a CLOB, we have to get
+		// a CLOB as a result (as opposed to a VARCHAR), because we can have a 
+		// CLOB that is beyond the max length of VARCHAR (ex. "clob(100k)").
+		// This is okay because CLOBs, like VARCHARs, allow variable-length
+		// values (which is a must for the substr to actually work).
+			resultType = receiverType;
 		}
 
 		// Determine the maximum length of the result

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out?rev=596824&r1=596823&r2=596824&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
Tue Nov 20 12:54:01 2007
@@ -1857,4 +1857,50 @@
 1          |1991-01-01
 ij> drop table d2887_types;
 0 rows inserted/updated/deleted
-ij> 
\ No newline at end of file
+ij> -- DERBY-2352 involved a mismatch between the return type of the SUBSTR
+-- method and the expected type of the result column. During compilation,
+-- bind processing was computing that the SUBSTR would return a CHAR, but
+-- at execution time it actually returned a VARCHAR, resulting in a type
+-- mismatch detected by the sorter. Since the TRIM functions are very
+-- closely related to the SUBSTR function, we include a few tests of
+-- those functions in the test case.
+
+create table d2352 (c int);
+0 rows inserted/updated/deleted
+ij> insert into d2352 values (1), (2), (3);
+3 rows inserted/updated/deleted
+ij> select substr('abc', 1) from d2352 order by substr('abc', 1);
+1   
+----
+abc 
+abc 
+abc 
+ij> select substr('abc', 1) from d2352 group by substr('abc', 1);
+1   
+----
+abc 
+ij> select ltrim('abc') from d2352 order by ltrim('abc');
+1   
+----
+abc 
+abc 
+abc 
+ij> select ltrim('abc') from d2352 group by ltrim('abc');
+1   
+----
+abc 
+ij> select trim(trailing ' ' from 'abc') from d2352
+       order by trim(trailing ' ' from 'abc');
+1   
+----
+abc 
+abc 
+abc 
+ij> select trim(trailing ' ' from 'abc') from d2352
+       group by trim(trailing ' ' from 'abc');
+1   
+----
+abc 
+ij> drop table d2352;
+0 rows inserted/updated/deleted
+ij> 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql?rev=596824&r1=596823&r2=596824&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
Tue Nov 20 12:54:01 2007
@@ -733,4 +733,23 @@
 
 drop table d2887_types;
 
+-- DERBY-2352 involved a mismatch between the return type of the SUBSTR
+-- method and the expected type of the result column. During compilation,
+-- bind processing was computing that the SUBSTR would return a CHAR, but
+-- at execution time it actually returned a VARCHAR, resulting in a type
+-- mismatch detected by the sorter. Since the TRIM functions are very
+-- closely related to the SUBSTR function, we include a few tests of
+-- those functions in the test case.
+
+create table d2352 (c int);
+insert into d2352 values (1), (2), (3);
+select substr('abc', 1) from d2352 order by substr('abc', 1);
+select substr('abc', 1) from d2352 group by substr('abc', 1);
+select ltrim('abc') from d2352 order by ltrim('abc');
+select ltrim('abc') from d2352 group by ltrim('abc');
+select trim(trailing ' ' from 'abc') from d2352
+       order by trim(trailing ' ' from 'abc');
+select trim(trailing ' ' from 'abc') from d2352
+       group by trim(trailing ' ' from 'abc');
+drop table d2352;
 



Mime
View raw message