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 Mamta Satoor <ma...@Remulak.Net> on 2005/01/24 23:34:16 UTC
[PATCH] (DERBY-124) Result type for CONCAT operator with blobs appears
to be incorrect.
"A B (JIRA)" wrote:
> Result type for CONCAT operator with blobs appears to be incorrect.
> -------------------------------------------------------------------
>
> Key: DERBY-124
> URL: http://issues.apache.org/jira/browse/DERBY-124
> Project: Derby
> Type: Bug
> Components: SQL
> Reporter: A B
>
> PROBLEM:
>
> If one creates a table with a large blob column (say 100K), and then tries to insert a CONCATENATED blob value into the table, where one of the blobs to be concatenated is larger than 32672 bytes, a truncation error occurs even though the blob column is large enough to hold the value without truncation.
>
> REPRODUCTION:
>
> Code snippet used to reproduce is as follows:
>
> ----
>
> // Create an array of bytes to be used as the input parameter.
> // NOTE: Size of the parameter is greater than 32672.
> byte [] bData = new byte[32700];
> for (int i = 0; i < bData.length; i++)
> bData[i] = (byte)(i % 10);
>
> Statement st = conn1.createStatement();
> try {
>
> // Create table with a BLOB column.
> st.execute("CREATE TABLE bt (b blob(100K))");
>
> // Now, prepare a statement to execute an INSERT command that uses
> // blob concatenation.
> PreparedStatement pSt = conn1.prepareStatement(
> "insert into bt values (cast (x'1010' as blob) || ?)");
> pSt.setBytes(1, bData);
>
> // And now try to execute. This will throw a truncation error.
> pSt.execute();
>
> } catch (SQLException se) {
> se.printStackTrace();
> }
>
> ----
>
> NOTES:
>
> It turns out that the truncation error is for the parameter itself, not for the blob column.
>
> As mentioned on the derby-dev list by Dan and Satheesh, it looks like the concatenation operator is promoting the parameter to VARCHAR FOR BIT DATA instead of BLOB. The cause for that particular choice of type could be one of two things: 1) the type of the "?" parameter is mapped to VARCHAR(32762) FOR BIT DATA, and thus the concatentation operator uses that type to infer the result type; or 2) the parameter type is mapped to BLOB, but the concat operator is incorrectly inferring that the result is VARCHAR FOR BIT DATA.
>
> In the case of #1, the question becomes that of what type _should_ the parameter be mapped to? Is VARCHAR FOR BIT DATA correct since we're using "setBytes", or should it in fact be BLOB? Of course, whether or not the parameter mapping needs to change, it would appear that the concatenation operator is going to need modification so that it can determine the correct result type for blobs...
>
> --
> 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
> -
> If you want more information on JIRA, or have a bug to report see:
> http://www.atlassian.com/software/jira
Hi,
I have a patch for this bug. The problem was that the parameter was getting mapped to
VARCHAR FOR BIT DATA rather than BLOB.
My change is localized into ConcatenationOperatorNode.java and the proposed change
is to map the ? to BLOB/CLOB if the other operand to CONCAT operation is BLOB/CLOB.
I have also added couple tests to lang\concateTests.java.
Thanks,
Mamta
Re: [PATCH] (DERBY-124) Result type for CONCAT operator with
blobsappears to be incorrect.
Posted by Mamta Satoor <ma...@Remulak.Net>.
Yes, I ran all the Derby tests and there were no failures.
thanks,
Mamta
Satheesh Bandaram wrote:
> I am trying to submit this patch... Thanks for taking the time to add test cases. It is wonderful to see additions to
> existing tests.
>
> Would also appreciate information on whether all Derby tests have been run and pass/fail info...
>
> Satheesh
>
> Mamta Satoor wrote:
>
>> "A B (JIRA)" wrote:
>>
>>
>> > Result type for CONCAT operator with blobs appears to be incorrect.
>> > -------------------------------------------------------------------
>> >
>> > Key: DERBY-124
>> > URL: http://issues.apache.org/jira/browse/DERBY-124
>> > Project: Derby
>> > Type: Bug
>> > Components: SQL
>> > Reporter: A B
>> >
>> > PROBLEM:
>> >
>> > If one creates a table with a large blob column (say 100K), and then tries to insert a CONCATENATED blob value into
>> > the table, where one of the blobs to be concatenated is larger than 32672 bytes, a truncation error occurs even
>> > though the blob column is large enough to hold the value without truncation.
>> >
>> > REPRODUCTION:
>> >
>> > Code snippet used to reproduce is as follows:
>> >
>> > ----
>> >
>> > // Create an array of bytes to be used as the input parameter.
>> > // NOTE: Size of the parameter is greater than 32672.
>> > byte [] bData = new byte[32700];
>> > for (int i = 0; i < bData.length; i++)
>> > bData[i] = (byte)(i % 10);
>> >
>> > Statement st = conn1.createStatement();
>> > try {
>> >
>> > // Create table with a BLOB column.
>> > st.execute("CREATE TABLE bt (b blob(100K))");
>> >
>> > // Now, prepare a statement to execute an INSERT command that uses
>> > // blob concatenation.
>> > PreparedStatement pSt = conn1.prepareStatement(
>> > "insert into bt values (cast (x'1010' as blob) || ?)");
>> > pSt.setBytes(1, bData);
>> >
>> > // And now try to execute. This will throw a truncation error.
>> > pSt.execute();
>> >
>> > } catch (SQLException se) {
>> > se.printStackTrace();
>> > }
>> >
>> > ----
>> >
>> > NOTES:
>> >
>> > It turns out that the truncation error is for the parameter itself, not for the blob column.
>> >
>> > As mentioned on the derby-dev list by Dan and Satheesh, it looks like the concatenation operator is promoting the
>> > parameter to VARCHAR FOR BIT DATA instead of BLOB. The cause for that particular choice of type could be one of
>> > two things: 1) the type of the "?" parameter is mapped to VARCHAR(32762) FOR BIT DATA, and thus the concatentation
>> > operator uses that type to infer the result type; or 2) the parameter type is mapped to BLOB, but the concat
>> > operator is incorrectly inferring that the result is VARCHAR FOR BIT DATA.
>> >
>> > In the case of #1, the question becomes that of what type _should_ the parameter be mapped to? Is VARCHAR FOR BIT
>> > DATA correct since we're using "setBytes", or should it in fact be BLOB? Of course, whether or not the parameter
>> > mapping needs to change, it would appear that the concatenation operator is going to need modification so that it
>> > can determine the correct result type for blobs...
>> >
>> > --
>> > 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
>> > -
>> > If you want more information on JIRA, or have a bug to report see:
>> > http://www.atlassian.com/software/jira
>> >
>> Hi,
>>
>> I have a patch for this bug. The problem was that the parameter was getting mapped to
>> VARCHAR FOR BIT DATA rather than BLOB.
>>
>> My change is localized into ConcatenationOperatorNode.java and the proposed change
>> is to map the ? to BLOB/CLOB if the other operand to CONCAT operation is BLOB/CLOB.
>> I have also added couple tests to lang\concateTests.java.
>>
>> Thanks,
>> Mamta
>>
>> ---------------------------------------------------------------------------------------------------------
>>
>> Index: java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java
>> ===================================================================
>> --- java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java (revision 125811)
>> +++ java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java (working copy)
>> @@ -98,19 +98,21 @@
>> {
>> if (rightOperand.isParameterNode())
>> {
>> - throw StandardException.newException(SQLState.LANG_BINARY_OPERANDS_BOTH_PARMS,
>> + throw StandardException.newException(SQLState.LANG_BINARY_OPERANDS_BOTH_PARMS,
>>
>> operator);
>> }
>>
>> TypeId leftType;
>>
>> /*
>> - ** A ? on the left gets its type from the right. There are six
>> + ** A ? on the left gets its type from the right. There are eight
>> ** legal types for the concatenation operator: CHAR, VARCHAR,
>> - ** LONG VARCHAR, BIT, BIT VARYING, and LONG BIT VARYING. If the
>> - ** right type is one of the bit types, set the parameter type to
>> + ** LONG VARCHAR, CLOB, BIT, BIT VARYING, LONG BIT VARYING, and BLOB.
>> + ** If the right type is BLOB, set the parameter type to BLOB with max length.
>> + ** If the right type is one of the other bit types, set the parameter type to
>> ** BIT VARYING with maximum length.
>> **
>> + ** If the right type is CLOB, set parameter type to CLOB with max length.
>> ** If the right type is anything else, set it to VARCHAR with
>> ** maximum length. We count on the resolveConcatOperation method to
>> ** catch an illegal type.
>> @@ -118,19 +120,23 @@
>> ** NOTE: When I added the long types, I could have changed the
>> ** resulting parameter types to LONG VARCHAR and LONG BIT VARYING,
>> ** but they were already VARCHAR and BIT VARYING, and it wasn't
>> - ** clear to me what effect it would have to change it.
>> - **
>> - ** - Jeff
>> + ** clear to me what effect it would have to change it. - Jeff
>> */
>> if (rightOperand.getTypeId().isBitTypeId())
>> {
>> - leftType = TypeId.getBuiltInTypeId(Types.VARBINARY);
>> + if (rightOperand.getTypeId().isBlobTypeId())
>> + leftType = TypeId.getBuiltInTypeId(Types.BLOB);
>> + else
>> + leftType = TypeId.getBuiltInTypeId(Types.VARBINARY);
>> }
>> else
>> {
>> - leftType = TypeId.getBuiltInTypeId(Types.VARCHAR);
>> + if (rightOperand.getTypeId().isClobTypeId())
>> + leftType = TypeId.getBuiltInTypeId(Types.CLOB);
>> + else
>> + leftType = TypeId.getBuiltInTypeId(Types.VARCHAR);
>> }
>> -
>> +
>> ((ParameterNode) leftOperand).setDescriptor(new DataTypeDescriptor(leftType, true));
>> }
>>
>> @@ -142,28 +148,36 @@
>> TypeId rightType;
>>
>> /*
>> - ** A ? on the right gets its type from the left. There are six
>> + ** A ? on the right gets its type from the left. There are eight
>> ** legal types for the concatenation operator: CHAR, VARCHAR,
>> - ** LONG VARCHAR, BIT, BIT VARYING, and LONG BIT VARYING. If the
>> - ** left type is one of the bit types, set the parameter type to
>> - ** BIT VARYING with maximum length. If the left type is anything
>> - ** else, set it to VARCHAR with maximum length. We count on the
>> - ** resolveConcatOperation method to catch an illegal type.
>> + ** LONG VARCHAR, CLOB, BIT, BIT VARYING, LONG BIT VARYING, and BLOB.
>> + ** If the left type is BLOB, set the parameter type to BLOB with max length.
>> + ** If the left type is one of the other bit types, set the parameter type to
>> + ** BIT VARYING with maximum length.
>> **
>> + ** If the left type is CLOB, set parameter type to CLOB with max length.
>> + ** If the left type is anything else, set it to VARCHAR with
>> + ** maximum length. We count on the resolveConcatOperation method to
>> + ** catch an illegal type.
>> + **
>> ** NOTE: When I added the long types, I could have changed the
>> ** resulting parameter types to LONG VARCHAR and LONG BIT VARYING,
>> ** but they were already VARCHAR and BIT VARYING, and it wasn't
>> - ** clear to me what effect it would have to change it.
>> - **
>> - ** - Jeff
>> + ** clear to me what effect it would have to change it. - Jeff
>> */
>> if (leftOperand.getTypeId().isBitTypeId())
>> {
>> - rightType = TypeId.getBuiltInTypeId(Types.VARBINARY);
>> + if (leftOperand.getTypeId().isBlobTypeId())
>> + rightType = TypeId.getBuiltInTypeId(Types.BLOB);
>> + else
>> + rightType = TypeId.getBuiltInTypeId(Types.VARBINARY);
>> }
>> else
>> {
>> - rightType = TypeId.getBuiltInTypeId(Types.VARCHAR);
>> + if (leftOperand.getTypeId().isClobTypeId())
>> + rightType = TypeId.getBuiltInTypeId(Types.CLOB);
>> + else
>> + rightType = TypeId.getBuiltInTypeId(Types.VARCHAR);
>> }
>>
>> ((ParameterNode) rightOperand).setDescriptor(
>> Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/concateTests.java
>> ===================================================================
>> --- java/testing/org/apache/derbyTesting/functionTests/tests/lang/concateTests.java (revision 125811)
>> +++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/concateTests.java (working copy)
>> @@ -398,8 +398,33 @@
>> dumpSomeMetaDataInfo(s.executeQuery("values('' || '')"), concatenatedSQLTypesNames[0]);
>> verifyStringData(s.executeQuery("values('' || '')"), "");
>>
>> + System.out.println("Test13 - Prepared statement with CLOB(A) and ? concatenations will give
>> result type of CLOB(A+length of ?)");
>> + System.out.println(" Prior to Derby-124 fix, ? parameter was getting bound to VARCHAR of
>> length 32672 rather than CLOB.");
>> + try {
>> + s.executeUpdate("drop table ct");
>> + } catch(Exception ex) {}
>> + s.executeUpdate("create table ct (c CLOB(100K))");
>> + String cData = Formatters.repeatChar("c",32700);
>> + String cData1 = "aa";
>> + String cConcatenatedData = cData1 + cData;
>> + //Prior to fix for Derby-124, the ? was getting bound to VARCHAR with max length of 32670
>> + //As a fix for this, if one of the operands of concatenation is CLOB, then the ? parameter
>> would be bound to CLOB as well
>> + System.out.println(" preapre statement with clob||?");
>> + ps = conn.prepareStatement("insert into ct values (cast ('aa' as CLOB) || ?)");
>> + ps.setString(1, cData);
>> + ps.execute();
>> + verifyStringData(s.executeQuery("select c from ct"), cConcatenatedData);
>> + s.executeUpdate("delete from ct");
>> + System.out.println(" Test - preapre statement with clob||cast(? to cLOB)");
>> + ps = conn.prepareStatement("insert into ct values (cast ('aa' as CLOB) || cast(? as
>> CLOB))");
>> + ps.setString(1, cData);
>> + ps.execute();
>> + verifyStringData(s.executeQuery("select c from ct"), cConcatenatedData);
>> + s.executeUpdate("delete from ct");
>> +
>> s.executeUpdate("drop table testCLOB_MAIN");
>> s.executeUpdate("drop table t1");
>> + s.executeUpdate("drop table ct");
>> System.out.println("Test1 finished - CHAR, VARCHAR, LONGVARCHAR and CLOB concatenation
>> tests");
>> } catch (SQLException sqle) {
>> org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
>> @@ -844,8 +869,41 @@
>> System.out.println("Test22 - try 2 empty char for bit data concatenation and verify that
>> length comes back as 0 for the result");
>> dumpSomeMetaDataInfo(s.executeQuery("values(X'' || X'')"), concatenatedSQLTypesNames[4]);
>>
>> + System.out.println("Test23 - Derby-124 try concatenation in a prepared statement with one
>> operand casted to BLOB and other as ? parameter");
>> + System.out.println(" Prior to Derby-124 fix, ? parameter was getting bound to VARCHAR TO
>> BIT DATA of length 32672 rather than BLOB.");
>> + System.out.println(" That caused truncation exception when ? parameter was set to > 32672
>> bytes");
>> + try {
>> + s.executeUpdate("drop table bt");
>> + } catch(Exception ex) {}
>> + s.executeUpdate("create table bt (b BLOB(100K))");
>> + byte [] bData = new byte[32700];
>> + for (int i = 0; i < bData.length; i++)
>> + bData[i] = (byte)(i % 10);
>> + byte [] bData1 = new byte[2];
>> + bData1[0] = (byte) 0x10;
>> + bData1[1] = (byte) 0x10;
>> + byte [] bConcatenatedData = new byte[32702];
>> + System.arraycopy(bData1, 0, bConcatenatedData, 0, bData1.length);
>> + System.arraycopy(bData, 0, bConcatenatedData, bData1.length, bData.length);
>> + //Prior to fix for Derby-124, the ? was getting bound to VARCHAR FOR BIT DATA with max
>> length of 32670
>> + //And when ps.setBytes() set 32700 bytes into the ? parameter, at ps.execute() time, you got
>> following exception
>> + //ERROR 22001: A truncation error was encountered trying to shrink VARCHAR () FOR BIT DATA
>> 'XX-RESOLVE-XX' to length 32672.
>> + //As a fix for this, if one of the operands of concatenation is BLOB, then the ? parameter
>> would be bound to BLOB as well
>> + System.out.println(" preapre statement with blob||?");
>> + ps = conn.prepareStatement("insert into bt values (cast (x'1010' as BLOB) || ?)");
>> + ps.setBytes(1, bData);
>> + ps.execute();
>> + verifyByteData(s.executeQuery("select b from bt"), bConcatenatedData);
>> + s.executeUpdate("delete from bt");
>> + System.out.println(" Test - preapre statement with blob||cast(? to BLOB)");
>> + ps = conn.prepareStatement("insert into bt values (cast (x'1010' as BLOB) || cast(? as
>> BLOB))");
>> + ps.setBytes(1, bData);
>> + ps.execute();
>> + verifyByteData(s.executeQuery("select b from bt"), bConcatenatedData);
>> +
>> s.executeUpdate("drop table testBLOB_MAIN");
>> s.executeUpdate("drop table t2");
>> + s.executeUpdate("drop table bt");
>> System.out.println("Test2 finished - CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONGVARCHAR
>> FOR BIT DATA and BLOB concatenation tests");
>> } catch (SQLException sqle) {
>> org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
>> Index: java/testing/org/apache/derbyTesting/functionTests/master/concateTests.out
>> ===================================================================
>> --- java/testing/org/apache/derbyTesting/functionTests/master/concateTests.out (revision 125811)
>> +++ java/testing/org/apache/derbyTesting/functionTests/master/concateTests.out (working copy)
>> @@ -149,6 +149,12 @@
>> datatype of concatenated string is : CHAR
>> precision of concatenated string is : 0
>> Successful CHAR read of 0 characters
>> +Test13 - Prepared statement with CLOB(A) and ? concatenations will give result type of CLOB(A+length of ?)
>> + Prior to Derby-124 fix, ? parameter was getting bound to VARCHAR of length 32672 rather than CLOB.
>> + preapre statement with clob||?
>> +Successful CLOB read of 32702 characters
>> + Test - preapre statement with clob||cast(? to cLOB)
>> +Successful CLOB read of 32702 characters
>> Test1 finished - CHAR, VARCHAR, LONGVARCHAR and CLOB concatenation tests
>> Test2 - CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONGVARCHAR FOR BIT DATA and BLOB concatenation tests
>> Test2a - CHAR FOR BIT DATA concatenations will give result type of CHAR FOR BIT DATA when concatenated string < 255
>> @@ -306,4 +312,11 @@
>> Test22 - try 2 empty char for bit data concatenation and verify that length comes back as 0 for the result
>> datatype of concatenated string is : CHAR () FOR BIT DATA
>> precision of concatenated string is : 0
>> +Test23 - Derby-124 try concatenation in a prepared statement with one operand casted to BLOB and other as ?
>> parameter
>> + Prior to Derby-124 fix, ? parameter was getting bound to VARCHAR TO BIT DATA of length 32672 rather than BLOB.
>> + That caused truncation exception when ? parameter was set to > 32672 bytes
>> + preapre statement with blob||?
>> +Successful BLOB read of 32702 bytes
>> + Test - preapre statement with blob||cast(? to BLOB)
>> +Successful BLOB read of 32702 bytes
>> Test2 finished - CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONGVARCHAR FOR BIT DATA and BLOB concatenation tests
>>