Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
From: Knut Anders Hatlen <knut.hatlen@oracle.com>
To: "Derby Discussion" <derby-user@db.apache.org>
Subject: Re: Inconsistent SQLSyntaxErrorException (Hash join requires an
 optimizable equijoin predicate...)
References: <2e954a49-d25e-4928-8f09-5c76154cf6b9@default>
Date: Sun, 11 Jul 2010 01:08:19 +0200
In-Reply-To: <2e954a49-d25e-4928-8f09-5c76154cf6b9@default> (Charles Coates's
	message of "Thu, 8 Jul 2010 15:37:49 -0700 (PDT)")
Message-ID: <x639vrkk9o.fsf@oracle.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (usg-unix-v)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hi Chuck,

Please see my comments inline.

Charles Coates <charles.coates@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