db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-3753) select from table with integer primary key and blob column does not do sort avoidance
Date Wed, 02 Jul 2008 23:42:45 GMT
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.4.1.3, 10.3.3.0, 10.5.0.0
            Reporter: Kathey Marsden


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