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

["Saurabh Vyas (JIRA)" <ji...@apache.org>]

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

Saurabh Vyas updated DERBY-1132:
--------------------------------

    Attachment: derby_1132_v1.diff
                derby_1132_v1.stat

The Char & Varchar functions were not handled separately to assign to length for the target type (in the bindExpression() method of CastNode.java) and thus a default value of 15 was getting assigned to it (from getColumnDisplaySize() method of DataTypeUtilities class)

Now I added a check for the string types (i.e. both Char & Varchar) in the source type, and using the operand's maximum length or the maximum length for that data type (whichever is minimum) and assigning it to be the length for the target type. Thus avoiding the unwanted truncation error caused by the default value of 15.

Corrospondingly I had to modify following 3 output files for the test cases. :
 -  java/testing/org/apache/derbyTesting/functionTests/master/cast.out
 -  java/testing/org/apache/derbyTesting/functionTests/master/ejbql.ou
Note : The changes are made to have the new display in the result in ij as earlier the expected result was based on default 15 characters and now it is based on the actual max width of the target type.
 -  java/testing/org/apache/derbyTesting/functionTests/master/implicitConversions.out
Note : In earlier implementation, the expected result was an exception. As the char function use to truncate 26 characters of a timestamp value to default 15 and thus the insertion of truncated value to timestamp type field use to fail. But now as this case is handled, the proper value can be inserted and thus no errors.

> Truncation Error with Concat
> ----------------------------
>
>                 Key: DERBY-1132
>                 URL: http://issues.apache.org/jira/browse/DERBY-1132
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC
>    Affects Versions: 10.1.1.0
>         Environment: Solaris, Windwos, JDK 1.5
>            Reporter: Mitesh Meswani
>         Assigned To: Saurabh Vyas
>         Attachments: derby_1132_v1.diff, derby_1132_v1.stat, repro.java
>
>
> 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