[jira] Commented: (DERBY-1132) Truncation Error with Concat

["Bryan Pendleton (JIRA)" <de...@db.apache.org>]

    [ http://issues.apache.org/jira/browse/DERBY-1132?page=comments#action_12374452 ] 

Bryan Pendleton commented on DERBY-1132:
----------------------------------------

This is certainly a most curious bug! Here is the stack trace when the exception occurs:

Exception in thread "main" ERROR 22001: A truncation error was encountered trying to shrink VARCHAR 'Alan E.Frechette' to length 15.
        at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:355)
        at org.apache.derby.iapi.types.SQLChar.hasNonBlankChars(SQLChar.java:1316)
        at org.apache.derby.iapi.types.SQLChar.setWidth(SQLChar.java:1386)
        at org.apache.derby.exe.aca666c073x010ax964bx9307x000000169a300.e1(Unknown Source)
        at org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGeneratedClass.java:140)
        at org.apache.derby.impl.sql.execute.GenericQualifier.getOrderable(GenericQualifier.java:96)
        at org.apache.derby.impl.sql.execute.NoPutResultSetImpl.clearOrderableCache(NoPutResultSetImpl.java:312)
        at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.openScanController(BulkTableScanResultSet.java:176)
        at org.apache.derby.impl.sql.execute.TableScanResultSet.openCore(TableScanResultSet.java:390)
        at org.apache.derby.impl.sql.execute.BulkTableScanResultSet.openCore(BulkTableScanResultSet.java:224)
        at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(BasicNoPutResultSetImpl.java:259)
        at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:361)
        at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1173)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1425)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeQuery(EmbedPreparedStatement.java:247)
        at repro.main(repro.java:35)

I set a breakpoint in the debugger and I can see that variable "start" in SQLChar.hasNonBlankChars() is passed as "15", and going up one level in the stack, that is because variable "desiredWidth" in SQLChar.setWidth() is passed as "15".

However, the next level up in the stack is generated Java byte code, and my skill with Derby does not reach to the point of knowing how to decrypt the generated Java byte code.

Hopefully this information is helpful to the next person who looks at this problem.


> Truncation Error with Concat
> ----------------------------
>
>          Key: DERBY-1132
>          URL: http://issues.apache.org/jira/browse/DERBY-1132
>      Project: Derby
>         Type: Bug

>   Components: JDBC
>     Versions: 10.1.1.0
>  Environment: Solaris, Windwos, JDK 1.5
>     Reporter: Mitesh Meswani

>
> Consider the table
> CREATE TABLE CUSTOMER_TABLE ( ID VARCHAR(255)   PRIMARY KEY NOT NULL, NAMEZ VARCHAR(255) , COUNTRY VARCHAR(255) )
> Sql:
> PreparedStatement ps = conn.prepateStatement ("SELECT * FROM CUSTOMER_TABLE WHERE NAMEZ  = VARCHAR ( CAST (? AS VARCHAR(32672) ) || CAST (? AS VARCHAR(32672) ) )" );
> ps.setString(1, "Alan E. ");
> ps.setString(2, "Frechette");
> ps.executeQuery()
> Error:
> "A truncation error was encountered trying to shrink VARCHAR 'Alan E. Frechette' to length 15."
> getErrorCode()-1
> getSQLState()22001 
> Please note that
> -The query executes ok against DB2 database
> -The query executes ok if the total length of both the parameters bound is less than 15. That is as follows
>    //Total length of parameters bound = len("Alan E. Fre") = 11
>    ps.setString(1, "Alan E. ");
>    ps.setString(2, "Fre");
> -The error occurs both with embedded and network mode of derby
> -Omitting the casts as follows also gives the same error
>    query: SELECT * FROM CUSTOMER_TABLE WHERE NAMEZ  = VARCHAR( 'Frechette' || ? )
>    error: SQLState: 22001 "A truncation error was encountered trying to shrink VARCHAR 'FrechetteAlan E. ' to length 15"
> -Using parameter markers for both the variables without cast like as follows results in error
>    query: SELECT * FROM CUSTOMER_TABLE WHERE NAMEZ  = ? || ?
>    error: SQLState: 42X35 "It is not allowed for both operands of '||' to be ? parameters."
> -Using parameter markers for only one variables without cast like as follows results in error
>    query: SELECT * FROM CUSTOMER_TABLE WHERE NAMEZ  = 'Frechette' || ?
>    error:SQLState: 42818 "Comparisons between 'VARCHAR' and 'LONG VARCHAR' are not supported." 
> -It works to cast to VARCHAR(2000), but not VARCHAR(2001) or larger.  Regardless of length, if it fails, the magic number is always 15. 

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira