[
https://issues.apache.org/jira/browse/DERBY-3753?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen updated DERBY-3753:
--------------------------------------
Issue & fix info: [High Value Fix, Repro attached] (was: [High Value Fix])
Urgency: Normal
Triaged for 10.5.2. Workaround exists (optimizer override).
> select from table with integer primary key and blob column does not do sort
> avoidance
> -------------------------------------------------------------------------------------
>
> Key: DERBY-3753
> URL: https://issues.apache.org/jira/browse/DERBY-3753
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.3.0, 10.4.1.3, 10.5.1.1
> Reporter: Kathey Marsden
> Attachments: derby.log, Derby3753.java
>
>
> 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[] {Integer.TYPE,
> InputStream.class});
> } catch (NoSuchMethodException e) {
> // ignore method not found as method may not be present for
> // jdk's lower than 1.6.
> println("Skipping lengthless insert because method is not
> available");
> return;
> }
> 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 (;;) {
> int size = rsstream.read(buf);
> if (size == -1)
> break;
> len += size;
> int expectedValue = ((len -1) % 26) + 'a';
> if (size != 0)
> assertEquals(expectedValue,buf[size -1]);
> }
> 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},{"2","0"},
> {"3",null},{"4","3"}});
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
