Truncation error w/ INSERT stmt using blob concatenation...

[Army <ar...@golux.com>]

I've come across the following behavior with the Derby engine.  It seems like a bug to me, but I thought I'd check to 
make sure it's not "working as designed" before filing a JIRA entry.

If I create a table with a large blob column (say 100K), and then I try to insert a CONCATENATED blob value into the 
table, where one of the blobs to be concatenated is larger than 32672 bytes, I get a truncation error, even though the 
blob column is large enough to hold the value without truncation.

Basically, what I'm doing is:

create table bt (b blob(100K));
insert into bt values (cast (x'0101' as blob) || ?);

I'm using a prepared statement to bind the parameter to a blob that is larger than 32672 bytes.  Here's a snippet of the 
code I wrote to reproduce the problem:

----

	// 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 the truncation error
		// seen below.
		pSt.execute();

	} catch (SQLException se) {
		se.printStackTrace();
	}

----

ERROR 22001: A truncation error was encountered trying to shrink VARCHAR () FOR BIT DATA 'XX-RESOLVE-XX' to length 32672.
         at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:333)
         at org.apache.derby.iapi.types.SQLBinary.checkHostVariable(SQLBinary.java:982)
         at org.apache.derby.exe.acdcd58064x0101x81d4x2a4cx00000019b5c03.e0(Unknown Source)
         at org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGeneratedClass.java:138)

----

 From the stack trace, it can be seen that this error is thrown from a method called "checkHostVariable", inside of 
which is the following comment:

	/**
		Host variables are rejected if their length is
		bigger than the declared length, regardless of
		if the trailing bytes are the pad character.
	*/

This is where my uncertainty begins.  It seems to me that, in the above reproduction, "the declared length" would be 
100K and the length of the host variable would be 32700.  In that case, since 32700 < 100K, this should be a valid 
insertion.  But since the variable is rejected, either 1) I'm misinterpreting what the "declared length" is, or 2) the 
declared length is not being calculated correctly (it's being set to 32672 when it _should_ be 100K).

Note that this "checkHostVariable" method is called for Blobs, but is NOT called for Clobs.  Thus, if I try to do the 
exact same thing using clobs instead of blobs (with characters instead of bytes, of course), everything works fine.

Anyone have any input/feedback?  Is this a bug?  My guess is "Yes", but as I could be misunderstanding the comment in 
the code, I'm not sure...

Thanks,
Army

Re: Changes to Derby JDBC Implementation Notes page

["Jean T. Anderson" <jt...@bristowhill.com>]

I updated 
http://incubator.apache.org/derby/papers/JDBCImplementation.html with 
Mamta's info. Please post any problems/corrections.

 -jean

Mamta Satoor wrote:

> Hi,
>
> Thanks to Dan for commiting my changes for updatable resultsets over 
> the weekend.
> I think this is a good time to update the Derby JDBC Implementation 
> Notes page
> for updatable resultsets. I will appreciate if one of the committers 
> can add following
> 2 paragraphs.
>
> _Following should go in the doc right before the java.sql.ResultSet 
> section_
> Java.sql.Connection
>
> CreateStatement, prepareStatement and prepareCall
>
> Behavior Clarification
>
> CONCUR_UPDATABLE concurrency is supported for FORWARD ONLY ResultSet 
> types only.
>  
>
> _Following should go in the doc in the java.sql.ResultSet section 
> before the getAsciiStream() api_
>
> deleteRow()
>
> Behavior Clarification
>
> After deleteRow, ResultSet will be positioned right before the next 
> row for FORWARD ONLY updatable ResultSets.
>  
>  
>
> thanks,
> Mamta
>

Changes to Derby JDBC Implementation Notes page

[Mamta Satoor <ma...@Remulak.Net>]

Hi,

Thanks to Dan for commiting my changes for updatable resultsets over the weekend.
I think this is a good time to update the Derby JDBC Implementation Notes page
for updatable resultsets. I will appreciate if one of the committers can add following
2 paragraphs.

Following should go in the doc right before the java.sql.ResultSet section
Java.sql.Connection

CreateStatement, prepareStatement and prepareCall

Behavior Clarification

CONCUR_UPDATABLE concurrency is supported for FORWARD ONLY ResultSet types only.


Following should go in the doc in the java.sql.ResultSet section before the getAsciiStream() api

deleteRow()

Behavior Clarification

After deleteRow, ResultSet will be positioned right before the next row for FORWARD ONLY updatable ResultSets.



thanks,
Mamta

Re: Truncation error w/ INSERT stmt using blob concatenation...

[Mamta Satoor <ma...@Remulak.Net>]

Daniel John Debrunner wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Army wrote:
> [...]
> > This is where my uncertainty begins.  It seems to me that, in the above
> > reproduction, "the declared length" would be 100K and the length of the
> > host variable would be 32700.  In that case, since 32700 < 100K, this
> > should be a valid insertion.  But since the variable is rejected, either
> > 1) I'm misinterpreting what the "declared length" is, or 2) the declared
> > length is not being calculated correctly (it's being set to 32672 when
> > it _should_ be 100K).
>
> I think the issue/bug is that the contactenated operator is resulting in
> a type of VARCHAR(32762) FOR BIT DATA, not BLOB. Or maybe the type of
> the ? is mapped to VARCHAR(32762) FOR BIT DATA. I wonder what the type
> of the ? parameter should be with your statement?
>
> Dan.

The issue here is that the ? parameter is getting bound to VARCHAR() FOR BIT DATA with
length of 32672bytes. This binding happens in ConcatenationOperatorNode.bindExpression()
at line number # 162 as copied below
   if (leftOperand.getTypeId().isBitTypeId())
   {
    rightType = TypeId.getBuiltInTypeId(Types.VARBINARY);
   }

The truncation exception at pSt.execute() is really caused by pSt.setBytes(1, bData);
which is trying to put 32700bytes into parameter which can only take 32672 bytes. If the
code snippet from Army is changed to cast ? to BLOB, the concatenation would
succeed.
   PreparedStatement pSt = con.prepareStatement(
            "insert into bt values (cast (x'1010' as blob) || cast(? as blob))");


So, as Dan mentioned, what should be the type of the ? parameter? Rather than
getting set to VARCHAR FOR BIT DATA, should it always be set to BLOB, if
the other operand of concatenation operator is bit type?

Thought? Comments?
Mamta

Re: Truncation error w/ INSERT stmt using blob concatenation...

[Mamta Satoor <ma...@Remulak.Net>]

Lynh,

It might be an unrelated problem from a quick look. The Jira entry for bug 121 says it is a
problem under Network Server (only?). What Army has reported is for embedded Derby.
My guess is that Army's concatenation issue would be reproducible under Network Server too.

Mamta

Lynh Nguyen wrote:

> Hi,
>
> I submitted a bug in Jira system and wonder if this problem is related.
>
> http://issues.apache.org/jira/browse/DERBY-121
>
> Regards,
> Lynh Nguyen
>
> Satheesh Bandaram wrote:
>
>> I suspect Derby is promoting Blob || Blob to a VARCHAR FOR BIT DATA. I added the following two lines to
>> SQLBinary.java:
>>
>>     System.out.println("declaredLength = "+declaredLength);
>>     System.out.println("ClassName = "+((Class)this.getClass()).getName());
>>
>> When I run the code provided by Army, I got:
>>     declaredLength = 32672
>>     ClassName = org.apache.derby.iapi.types.SQLVarbit
>>
>> But, if I insert only a ?, then the results are correct:
>>     declaredLength = 102400
>>     ClassName = org.apache.derby.iapi.types.SQLBlob
>>
>> So, I suspect the CONCAT is messing it up...
>>
>> Satheesh
>>
>> Daniel John Debrunner wrote:
>>
>> > Army wrote:
>>
>> >
>>
>> > >I've come across the following behavior with the Derby engine.
>> It seems
>>
>> > >like a bug to me, but I thought I'd check to make sure it's
>> not "working
>>
>> > >as designed" before filing a JIRA entry.
>>
>> >
>>
>> > >If I create a table with a large blob column (say 100K), and
>> then I try
>>
>> > >to insert a CONCATENATED blob value into the table, where one
>> of the
>>
>> > >blobs to be concatenated is larger than 32672 bytes, I get a
>> truncation
>>
>> > >error, even though the blob column is large enough to hold the
>> value
>>
>> > >without truncation.
>>
>> >
>>
>> >
>>
>> > [ code, stack trace omitted]
>>
>> >
>>
>> > >This is where my uncertainty begins. It seems to me that, in
>> the above
>>
>> > >reproduction, "the declared length" would be 100K and the
>> length of the
>>
>> > >host variable would be 32700. In that case, since 32700 <
>> 100K, this
>>
>> > >should be a valid insertion. But since the variable is
>> rejected, either
>>
>> > >1) I'm misinterpreting what the "declared length" is, or 2)
>> the declared
>>
>> > >length is not being calculated correctly (it's being set to
>> 32672 when
>>
>> > >it _should_ be 100K).
>>
>> >
>>
>> >
>>
>> >
>>
>> > I think the issue/bug is that the contactenated operator is
>> resulting in
>>
>> > a type of VARCHAR(32762) FOR BIT DATA, not BLOB. Or maybe the type
>> of
>>
>> > the ? is mapped to VARCHAR(32762) FOR BIT DATA. I wonder what the
>> type
>>
>> > of the ? parameter should be with your statement?
>>
>> >
>>
>> > >Note that this "checkHostVariable" method is called for Blobs,
>> but is
>>
>> > >NOT called for Clobs. Thus, if I try to do the exact same
>> thing using
>>
>> > >clobs instead of blobs (with characters instead of bytes, of
>> course),
>>
>> > >everything works fine.
>>
>> >
>>
>> >
>>
>> > The difference it there to match DB2's JCC driver. Basically
>>
>> > checkHostVariable() is a check in additional to normalization.
>>
>> > Column assignment always goes through normalization to ensure the
>> value
>>
>> > is legitimate. Normalization rules allow truncation of pad
>> characters
>>
>> > (0x20) when setting a value. DB2's JCC driver does not allow any
>>
>> > truncation of binary values from a host variable.
>>
>> >
>>
>> > E.g.
>>
>> >
>>
>> > 'abc ' is valid for VARCHAR(3)
>>
>> > 'abcd ' is not valid for VARCHAR(3)
>>
>> >
>>
>> > X'abcdef2020' is valid for VARCHAR(3) FOR BIT DATA (padded with two
>>
>> > bytes) *except* when it is a host variable (set by a parameter
>> marker).
>>
>> >
>>
>> > Dan.
>>
>

Re: Truncation error w/ INSERT stmt using blob concatenation...

[Lynh Nguyen <ly...@Remulak.Net>]

Hi,

I submitted a bug in Jira system and wonder if this problem is related.

http://issues.apache.org/jira/browse/DERBY-121

Regards,
Lynh Nguyen

Satheesh Bandaram wrote:

> I suspect Derby is promoting Blob || Blob to a VARCHAR FOR BIT DATA. I 
> added the following two lines to SQLBinary.java:
>
>     System.out.println("declaredLength = "+declaredLength);
>     System.out.println("ClassName = "+((Class)this.getClass()).getName());
>
> When I run the code provided by Army, I got:
>     declaredLength = 32672
>     ClassName = org.apache.derby.iapi.types.SQLVarbit
>
> But, if I insert only a ?, then the results are correct:
>     declaredLength = 102400
>     ClassName = org.apache.derby.iapi.types.SQLBlob
>
> So, I suspect the CONCAT is messing it up...
>
> Satheesh
>
> Daniel John Debrunner wrote:
>
>> Army wrote:
>
>>
>
>> >I've come across the following behavior with the Derby engine.
> It seems
>
>> >like a bug to me, but I thought I'd check to make sure it's
> not "working
>
>> >as designed" before filing a JIRA entry.
>
>>
>
>> >If I create a table with a large blob column (say 100K), and
> then I try
>
>> >to insert a CONCATENATED blob value into the table, where one
> of the
>
>> >blobs to be concatenated is larger than 32672 bytes, I get a
> truncation
>
>> >error, even though the blob column is large enough to hold the
> value
>
>> >without truncation.
>
>>
>
>>
>
>> [ code, stack trace omitted]
>
>>
>
>> >This is where my uncertainty begins. It seems to me that, in
> the above
>
>> >reproduction, "the declared length" would be 100K and the
> length of the
>
>> >host variable would be 32700. In that case, since 32700 <
> 100K, this
>
>> >should be a valid insertion. But since the variable is
> rejected, either
>
>> >1) I'm misinterpreting what the "declared length" is, or 2)
> the declared
>
>> >length is not being calculated correctly (it's being set to
> 32672 when
>
>> >it _should_ be 100K).
>
>>
>
>>
>
>>
>
>> I think the issue/bug is that the contactenated operator is
> resulting in
>
>> a type of VARCHAR(32762) FOR BIT DATA, not BLOB. Or maybe the type
> of
>
>> the ? is mapped to VARCHAR(32762) FOR BIT DATA. I wonder what the
> type
>
>> of the ? parameter should be with your statement?
>
>>
>
>> >Note that this "checkHostVariable" method is called for Blobs,
> but is
>
>> >NOT called for Clobs. Thus, if I try to do the exact same
> thing using
>
>> >clobs instead of blobs (with characters instead of bytes, of
> course),
>
>> >everything works fine.
>
>>
>
>>
>
>> The difference it there to match DB2's JCC driver. Basically
>
>> checkHostVariable() is a check in additional to normalization.
>
>> Column assignment always goes through normalization to ensure the
> value
>
>> is legitimate. Normalization rules allow truncation of pad
> characters
>
>> (0x20) when setting a value. DB2's JCC driver does not allow any
>
>> truncation of binary values from a host variable.
>
>>
>
>> E.g.
>
>>
>
>> 'abc ' is valid for VARCHAR(3)
>
>> 'abcd ' is not valid for VARCHAR(3)
>
>>
>
>> X'abcdef2020' is valid for VARCHAR(3) FOR BIT DATA (padded with two
>
>> bytes) *except* when it is a host variable (set by a parameter
> marker).
>
>>
>
>> Dan.
>

Re: Truncation error w/ INSERT stmt using blob concatenation...

[Daniel John Debrunner <dj...@debrunners.com>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Army wrote:
>
> I've come across the following behavior with the Derby engine.  It seems
> like a bug to me, but I thought I'd check to make sure it's not "working
> as designed" before filing a JIRA entry.
>
> If I create a table with a large blob column (say 100K), and then I try
> to insert a CONCATENATED blob value into the table, where one of the
> blobs to be concatenated is larger than 32672 bytes, I get a truncation
> error, even though the blob column is large enough to hold the value
> without truncation.
>

[ code, stack trace omitted]

> This is where my uncertainty begins.  It seems to me that, in the above
> reproduction, "the declared length" would be 100K and the length of the
> host variable would be 32700.  In that case, since 32700 < 100K, this
> should be a valid insertion.  But since the variable is rejected, either
> 1) I'm misinterpreting what the "declared length" is, or 2) the declared
> length is not being calculated correctly (it's being set to 32672 when
> it _should_ be 100K).


I think the issue/bug is that the contactenated operator is resulting in
a type of VARCHAR(32762) FOR BIT DATA, not BLOB. Or maybe the type of
the ? is mapped to VARCHAR(32762) FOR BIT DATA. I wonder what the type
of the ? parameter should be with your statement?

> Note that this "checkHostVariable" method is called for Blobs, but is
> NOT called for Clobs.  Thus, if I try to do the exact same thing using
> clobs instead of blobs (with characters instead of bytes, of course),
> everything works fine.

The difference it there to match DB2's JCC driver. Basically
checkHostVariable() is a check in additional to normalization.
Column assignment always goes through normalization to ensure the value
is legitimate. Normalization rules allow truncation of pad characters
(0x20) when setting a value. DB2's JCC driver does not allow any
truncation of binary values from a host variable.

E.g.

'abc  ' is valid for VARCHAR(3)
'abcd ' is not valid for VARCHAR(3)

X'abcdef2020' is valid for VARCHAR(3) FOR BIT DATA (padded with two
bytes) *except* when it is a host variable (set by a parameter marker).

Dan.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFB7B3UIv0S4qsbfuQRAkGzAJsEq8JELSjV4Plgp+Pi84xhvKUhwACgwQxc
HuK44Wz/oZZRIaLBYPMfPpI=
=5Gaz
-----END PGP SIGNATURE-----