Hi Chuck, Please see my comments inline.
Charles Coates <[email protected]> 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
