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

[Charles Coates <ch...@oracle.com>]

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

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

[Knut Anders Hatlen <kn...@oracle.com>]

Knut Anders Hatlen <kn...@oracle.com> writes:

> Another thing is that the ROW_NUMBER() implementation was more or less
> completely rewritten in Derby 10.6 to address some bugs, so perhaps 10.6
> would handle this query differently.

I managed to reproduce the bug on 10.5.3.0 by adding an optimizer
override that forced the use of a hash join. With 10.6.1.0 it looks like
the optimizer is able to construct a hash join for the query, and I
don't see the error any more. And a little more digging showed that the
query stopped failing when the ROW_NUMBER rewrite went into the codeline
(https://issues.apache.org/jira/browse/DERBY-3634).

So I think you should be able to get around this problem either by
upgrading or by adding the optimizer override suggested in the previous
mail to force a nested loop join.

-- 
Knut Anders

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

[Knut Anders Hatlen <kn...@oracle.com>]

Hi Chuck,

Please see my comments inline.

Charles Coates <ch...@oracle.com> writes:

> 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 error means that Derby's optimizer has picked hash join as the
strategy for a join that does not satisfy the requirements for a hash
join, so it sounds very much like a bug in the optimizer.

It would be good to have this problem logged in the bug tracker:
https://issues.apache.org/jira/browse/DERBY

> This happens when executing the following SQL statement:
>
> SELECT 
> 	parent_table.id, child_table.value 
[...]
>
> 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.

You may force that part of the query to use a nested loop join instead
of a hash join by adding an optimizer override in a comment. Something
like this should do the trick:

...
) AS inner_table
-- DERBY-PROPERTIES joinStrategy=NESTEDLOOP
ON
...

> 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?

If you had been using Derby 10.6, you could have replaced the query in
the inner table with a simpler SELECT statement that used OFFSET/FETCH
NEXT instead of the nested query with ROW_NUMBER(). (Derby 10.5 doesn't
support OFFSET/FETCH NEXT in a sub-query.) I don't know if this would
help avoiding the error, but it would make the query less complicated,
so it might make it easier for the optimizer to do the right thing.

Another thing is that the ROW_NUMBER() implementation was more or less
completely rewritten in Derby 10.6 to address some bugs, so perhaps 10.6
would handle this query differently.

These are of course only guesses as long as we haven't established the
root cause of the problem.

-- 
Knut Anders