db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-3753) select from table with integer primary key and blob column does not do sort avoidance
Date Fri, 03 Jul 2009 14:32:47 GMT

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


Mime
View raw message