Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
10.3.3.0, 10.4.1.3, 10.5.1.1
-
None
-
Normal
-
High Value Fix, Repro attached
Description
In working on DERBY-3732 I noticed that select from a table with a primary key and a blob column does not do sort avoidance. If you remove the optimizer directive, this fixture in BlobMemTest will run out of memory sorting if memory.BlobMemTest is run with 16M heap.
private void testBlobLength(boolean lengthless) throws SQLException, IOException, IllegalArgumentException, IllegalAccessException, InvocationTargetException {
getConnection().setAutoCommit(false);
Statement s = createStatement();
s.executeUpdate("CREATE TABLE BLOBTAB (K INT CONSTRAINT PK PRIMARY KEY, B BLOB(" + LONG_BLOB_LENGTH + "))");
PreparedStatement ps = prepareStatement("INSERT INTO BLOBTAB VALUES(?,?)");
// We allocate 16MB for the test so use something bigger than that.
ps.setInt(1,1);
LoopingAlphabetStream stream = new LoopingAlphabetStream(LONG_BLOB_LENGTH);
if (lengthless) {
Method m = null;
try {
Class c = ps.getClass();
m = c.getMethod("setBinaryStream",new Class[]
);
} catch (NoSuchMethodException e)
m.invoke(ps, new Object[]
{new Integer(2),stream});
}
else
ps.setBinaryStream(2, stream,LONG_BLOB_LENGTH);
ps.executeUpdate();
// insert a zero length blob.
ps.setInt(1, 2);
ps.setBytes(2, new byte[] {});
ps.executeUpdate();
// insert a null blob.
ps.setInt(1, 3);
ps.setBytes(2,null);
ps.executeUpdate();
// insert a short blob
ps.setInt(1, 4);
ps.setBytes(2, SHORT_BLOB_BYTES);
ps.executeUpdate();
// Currently need to use optimizer override to force use of the index.
// Derby should use sort avoidance and do it automatically, but there
// appears to be a bug.
ResultSet rs = s.executeQuery("SELECT K, LENGTH(B), B FROM BLOBTAB" +
"-- DERBY-PROPERTIES constraint=pk\n ORDER BY K");
rs.next();
assertEquals(LONG_BLOB_LENGTH_STRING,rs.getString(2));
// make sure we can still access the blob after getting length.
// It should be ok because we reset the stream
InputStream rsstream = rs.getBinaryStream(3);
int len= 0;
byte[] buf = new byte[32672];
for (;
assertEquals(LONG_BLOB_LENGTH,len);
// empty blob
rs.next();
assertEquals("0",rs.getString(2));
byte[] bytes = rs.getBytes(3);
assertEquals(0, bytes.length);
// null blob
rs.next();
assertEquals(null,rs.getString(2));
bytes = rs.getBytes(3);
assertEquals(null,bytes);
// short blob
rs.next();
assertEquals("3",rs.getString(2));
bytes = rs.getBytes(3);
assertTrue(Arrays.equals(SHORT_BLOB_BYTES, bytes));
rs.close();
// Select just length without selecting the blob.
rs = s.executeQuery("SELECT K, LENGTH(B) FROM BLOBTAB " +
"ORDER BY K");
JDBC.assertFullResultSet(rs, new String [][] {{"1",LONG_BLOB_LENGTH_STRING},
,
,{"4","3"}});
}
Attachments
Attachments
Issue Links
- is related to
-
DERBY-5555 Execution Time with a Statement 1 ms and with a PreparedStatement about 4 minutes
- Open