[ https://issues.apache.org/jira/browse/DERBY-6477?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13897883#comment-13897883 ]
Knut Anders Hatlen edited comment on DERBY-6477 at 2/11/14 3:40 PM: -------------------------------------------------------------------- This is a problem with an optimization in the LIKE handling. The optimization is that predicates on the form x LIKE 'prefix%' are converted into x >= 'prefix\u0000\u0000\u0000...' AND x < 'prefiy\u0000\u0000\u0000...' AND x LIKE 'prefix%' in the hope that one of the two generated predicates will evaluate to false and avoid the need to evaluate the more expensive LIKE predicate. The first predicate (the lower limit) is padded with \u0000 characters up to the maximum length of the column (because the string comparison algorithm otherwise will pad with space characters, so there are values that begin with 'prefix' that don't match the predicate x >= 'prefix'). Similar padding is done with the upper limit. The problem is that the JAVACLASSNAME column in SYS.SYSALIASES is defined with maximum length Integer.MAX_VALUE, and creating such a long string for the predicate fails. This optimization is disabled for CLOBs. I suppose the easiest solution is to disable it for all columns whose maximum length is greater than DB2_LONGVARCHAR_MAXWIDTH too. That would preserve the optimization in all user-defined tables, since users cannot declare CHAR, VARCHAR, LONG VARCHAR columns longer than that. \[edit: corrected what the upper limit would look like after optimization\] was (Author: knutanders): This is a problem with an optimization in the LIKE handling. The optimization is that predicates on the form x LIKE 'prefix%' are converted into x >= 'prefix\u0000\u0000\u0000...' AND x < 'prefix\uffff' AND x LIKE 'prefix%' in the hope that one of the two generated predicates will evaluate to false and avoid the need to evaluate the more expensive LIKE predicate. The first predicate (the lower limit) is padded with \u0000 characters up to the maximum length of the column (because the string comparison algorithm otherwise will pad with space characters, so there are values that begin with 'prefix' that don't match the predicate x >= 'prefix'). The problem is that the JAVACLASSNAME column in SYS.SYSALIASES is defined with maximum length Integer.MAX_VALUE, and creating such a long string for the predicate fails. This optimization is disabled for CLOBs. I suppose the easiest solution is to disable it for all columns whose maximum length is greater than DB2_LONGVARCHAR_MAXWIDTH too. That would preserve the optimization in all user-defined tables, since users cannot declare CHAR, VARCHAR, LONG VARCHAR columns longer than that. > OutOfMemoryError selecting from SYS.SYSALIASES > ---------------------------------------------- > > Key: DERBY-6477 > URL: https://issues.apache.org/jira/browse/DERBY-6477 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.11.0.0 > Reporter: Rick Hillegas > Assignee: Knut Anders Hatlen > Attachments: d6477-1a-disable-optimization.diff > > > The following query raises an OutOfMemoryError: > if> select a.alias, a.aliastype > from sys.sysaliases a > where a.javaclassname like 'org.apache.derby.catalog.SystemProcedures%'; > ERROR XJ001: Java exception: 'Requested array size exceeds VM limit: > java.lang.OutOfMemoryError'. -- This message was sent by Atlassian JIRA (v6.1.5#6160)