db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <ma...@Remulak.Net>
Subject Re: [PATCH] (DERBY-124) Result type for CONCAT operator with blobsappears to be incorrect.
Date Tue, 25 Jan 2005 04:32:49 GMT
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
>>

Mime
View raw message