Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-3732

SQL Length function materializes BLOB into memory

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments


    • Type: Improvement
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s:,,
    • Fix Version/s:,,
    • Component/s: SQL
    • Labels:
    • Issue & fix info:
      High Value Fix


      Currently the SQL length function materializes the entire lob into memory. In SQLBinary.getLength() we have
      public final int getLength() throws StandardException
      if (stream != null)

      { if (streamValueLength != -1) return streamValueLength; }

      return (getBytes() == null) ? 0 : getBytes().length;
      Which actually is doubly bad because we call getBytes twice and materialize it twice.
      It would be good to read the length from the stream if available and otherwise stream the value to get the length, rather than materializing it into memory.

      To reproduce, run the attached repro.
      java -Xmx16M LengthLargeLob

      It gives an out of memory exception
      Caused by: java.lang.OutOfMemoryError: Java heap space
      at org.apache.derby.iapi.types.SQLBinary.readFromStream(SQLBinary.java:415)
      at org.apache.derby.iapi.types.SQLBinary.readExternal(SQLBinary.java:318)
      at org.apache.derby.iapi.types.SQLBinary.getValue(SQLBinary.java:220)
      at org.apache.derby.iapi.types.SQLBinary.getBytes(SQLBinary.java:210)
      at org.apache.derby.iapi.types.SQLBinary.getLength(SQLBinary.java:250)
      at org.apache.derby.impl.sql.execute.BaseActivation.getDB2Length(BaseActivation.java:1684)
      at org.apache.derby.exe.acf81e0010x011axa317x5db8x0000003d9dc81.e1(Unknown Source)
      at org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGeneratedClass.java:141)
      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.doProjection(ProjectRestrictResultSet.java:497)
      at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:291)
      at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(BasicNoPutResultSetImpl.java:460)
      at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(EmbedResultSet.java:423)
      ... 2 more


        1. derby-3732_skip3_diff.txt
          14 kB
          Katherine Marsden
        2. derby-3732_skip2_diff.txt
          19 kB
          Katherine Marsden
        3. derby-3732_skip_diff.txt
          17 kB
          Katherine Marsden
        4. derby-3732_diff.txt
          17 kB
          Katherine Marsden
        5. derby-3732_proto_diff.txt
          2 kB
          Katherine Marsden
        6. LengthThruBlob.java
          0.9 kB
          Katherine Marsden
        7. LengthLargeLob.zip
          1 kB
          Katherine Marsden

        Issue Links



            • Assignee:
              kmarsden Katherine Marsden
              kmarsden Katherine Marsden


              • Created:

                Issue deployment