[ http://issues.apache.org/jira/browse/DERBY-124?page=all ] Mamta A. Satoor closed DERBY-124: ---------------------------------
Fixed in 10.1.0.0 > 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 > Assignee: Mamta A. Satoor > Fix For: 10.1.0.0 > > 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 - For more information on JIRA, see: http://www.atlassian.com/software/jira
