You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2006/11/15 17:10:38 UTC
[jira] Commented: (DERBY-1132) Truncation Error with Concat
[ http://issues.apache.org/jira/browse/DERBY-1132?page=comments#action_12450082 ]
Knut Anders Hatlen commented on DERBY-1132:
-------------------------------------------
Here's another repro. It doesn't reproduce the the exception, but it could perhaps shed some more light on the magic number 15.
ij> values length(char('abc'));
1
-----------
15
1 row selected
ij> values length(char('abcdefghijklmnopqrstuvwxyz'));
1
-----------
15
1 row selected
ij> values length(varchar('abc'));
1
-----------
3
1 row selected
ij> values length(varchar('abcdefghijklmnopqrstuvwxyz'));
1
-----------
15
1 row selected
When a string is cast to char with no length specified, the length is always 15. When a string is cast to varchar with no length specified, the length is always min(length(string), 15).
> 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: 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