From derby-commits-return-8867-apmail-db-derby-commits-archive=db.apache.org@db.apache.org Fri Nov 23 00:15:43 2007 Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 65010 invoked from network); 23 Nov 2007 00:15:43 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 23 Nov 2007 00:15:43 -0000 Received: (qmail 83620 invoked by uid 500); 23 Nov 2007 00:15:31 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 83594 invoked by uid 500); 23 Nov 2007 00:15:30 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 83583 invoked by uid 99); 23 Nov 2007 00:15:30 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 22 Nov 2007 16:15:30 -0800 X-ASF-Spam-Status: No, hits=-100.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 23 Nov 2007 00:15:28 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 5502E1A9832; Thu, 22 Nov 2007 16:15:22 -0800 (PST) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r597516 - in /db/derby/code/branches/10.3/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/ Date: Fri, 23 Nov 2007 00:15:21 -0000 To: derby-commits@db.apache.org From: bpendleton@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20071123001522.5502E1A9832@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: bpendleton Date: Thu Nov 22 16:15:20 2007 New Revision: 597516 URL: http://svn.apache.org/viewvc?rev=597516&view=rev Log: DERBY-2352: Assertion failure with order by and group by on SUBSTR expression Merged revision 596824 from the trunk. Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java?rev=597516&r1=597515&r2=597516&view=diff ============================================================================== --- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java (original) +++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java Thu Nov 22 16:15:20 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/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out?rev=597516&r1=597515&r2=597516&view=diff ============================================================================== --- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out (original) +++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out Thu Nov 22 16:15:20 2007 @@ -1761,4 +1761,50 @@ John ij> drop table person; 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/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql?rev=597516&r1=597515&r2=597516&view=diff ============================================================================== --- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql (original) +++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql Thu Nov 22 16:15:20 2007 @@ -684,3 +684,22 @@ +-- 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;