Inconsistent SQLSyntaxErrorException (Hash join requires an optimizable equijoin predicate...)

[Charles Coates]

I am using embedded Derby version 10.5.3.0_1.  I am experiencing an occasional 
SQLSytaxErrorException with the following error message:

java.sql.SQLSyntaxErrorException: Hash join requires an optimizable equijoin 
predicate on a column in the selected index or heap.  An optimizable equijoin 
predicate does not exist on any column in table or index ''. Use the 'index' 
optimizer override to specify such an index or the heap on table ''.


This happens when executing the following SQL statement:

SELECT 
        parent_table.id, child_table.value 
FROM 
        MYDB.TABLE_A AS parent_table 
LEFT JOIN 
        MYDB.TABLE_B AS child_table 
ON 
        parent_table.id = child_table.table_a_id 
INNER JOIN ( 
        SELECT 
                id, sub_id 
        FROM ( 
                SELECT 
                        id, sub_id, ROW_NUMBER() OVER () AS r 
                FROM 
                        MYDB.TABLE_A
                WHERE 
                        sub_id = 'foo' AND 
                        id LIKE '%%%' 
        ) AS UniqueIds 
        WHERE 
                r > 0 AND r <= 10
) AS inner_table 
ON 
        parent_table.id = inner_table.id AND 
        parent_table.sub_id = inner_table.sub_id 
ORDER BY 
        parent_table.id


TABLE_A and TABLE_B are created with the following SQL script:

CREATE TABLE MYDB.TABLE_A (
  ID VARCHAR(80) NOT NULL,
  SUB_ID VARCHAR(80) NOT NULL,
  {some additional columns},
  PRIMARY KEY (ID, SUB_ID),
);

CREATE TABLE MYDB.TABLE_B (
  TABLE_A_ID VARCHAR(80),
  SUB_ID VARCHAR(80) NOT NULL,
  VALUE VARCHAR(255),
  {some additional columns},
  CONSTRAINT TABLE_B_TABLE_A_FK
    FOREIGN KEY (TABLE_A_ID, SUB_ID)
    REFERENCES MYDB.TABLE_A(ID, SUB_ID)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
);

CREATE INDEX TABLE_A_ID_INDEX
  ON MYDB.TABLE_A (ID);

CREATE INDEX TABLE_A_SUB_ID_INDEX
  ON MYDB.TABLE_A (SUB_ID);



It appears that the inner_table query WHERE clause is the cause of the problem. 
 I think the error message is indicating that "sub_id = 'foo' AND id LIKE 
'%%%'" is not optimizable.  However, this exact query works most of the time.  
And it always works when using a SQL client tool (like SQirreL SQL Client).  It 
will only fail occasionally when executed via JDBC within a java application.

Does anyone have any ideas what could be causing this?  Any ideas on possible 
modifications to the query that would avoid this error and still accomplish the 
same results?  The query is a bit complex, but it is the simplest single query 
I could come up with that would allow us to essentially create stateless 
pagination of the results.  The inner query takes care of restricting the 
results to a set page (the boundaries of r represent the start and end index of 
a page).  The inner query feeds the outer query with uniqe keys (combination pk 
made up of id and sub_id) that restrict the results to retrieve one page at a 
time.

Any help would be greatly appreciated.

Thank you.

- Chuck