[DERBY-124] Result type for CONCAT operator with blobs appears to be incorrect.

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 10.1.1.0
• Component/s: SQL
• Assignee: Mamta A. Satoor
• Reporter: A B
• Created: 18/Jan/05 07:28
• Updated: 05/Jun/05 01:16
• Resolved: 27/Jan/05 07:21

Description

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...