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 "Mitesh Meswani (JIRA)" <de...@db.apache.org> on 2006/03/20 19:28:58 UTC
[jira] Created: (DERBY-1132) Truncation Error with Concat
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
[jira] Assigned: (DERBY-1132) Truncation Error with Concat
Posted by "Saurabh Vyas (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1132?page=all ]
Saurabh Vyas reassigned DERBY-1132:
-----------------------------------
Assignee: Saurabh Vyas
> 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
[jira] Updated: (DERBY-1132) Truncation Error with Concat
Posted by "Bryan Pendleton (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1132?page=all ]
Bryan Pendleton updated DERBY-1132:
-----------------------------------
Attachment: repro.java
Attached file 'repro.java' is the repro program I put together following the poster's description.
> 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
> 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
[jira] Updated: (DERBY-1132) Truncation Error with Concat
Posted by "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
[jira] Updated: (DERBY-1132) Truncation Error with Concat
Posted by "Saurabh Vyas (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1132?page=all ]
Saurabh Vyas updated DERBY-1132:
--------------------------------
Derby Info: [Patch Available]
> 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
[jira] Resolved: (DERBY-1132) Truncation Error with Concat
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1132?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen resolved DERBY-1132.
---------------------------------------
Resolution: Fixed
Fix Version/s: 10.2.2.1
Committed to 10.2 with revision 517332.
> Truncation Error with Concat
> ----------------------------
>
> Key: DERBY-1132
> URL: https://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
> Fix For: 10.2.2.1, 10.3.0.0
>
> 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.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-1132) Truncation Error with Concat
Posted by "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
[jira] Commented: (DERBY-1132) Truncation Error with Concat
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ 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
[jira] Updated: (DERBY-1132) Truncation Error with Concat
Posted by "Saurabh Vyas (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1132?page=all ]
Saurabh Vyas updated DERBY-1132:
--------------------------------
Fix Version/s: 10.3.0.0
> 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
> Fix For: 10.3.0.0
>
> 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
[jira] Reopened: (DERBY-1132) Truncation Error with Concat
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-1132?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen reopened DERBY-1132:
---------------------------------------
Reopening to back-port the fix to 10.2.
> Truncation Error with Concat
> ----------------------------
>
> Key: DERBY-1132
> URL: https://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
> Fix For: 10.3.0.0
>
> 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.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-1132) Truncation Error with Concat
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-1132?page=all ]
Knut Anders Hatlen resolved DERBY-1132.
---------------------------------------
Resolution: Fixed
Derby Info: (was: [Patch Available])
The fix and the test changes look correct. I have verified that the repro runs cleanly with the patch. Derbyall and the JUnit tests also run cleanly. Committed revision 477168. Thanks for fixing the bug, Saurabh!
> 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
> Fix For: 10.3.0.0
>
> 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