db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Charles Coates <charles.coa...@oracle.com>
Subject Inconsistent SQLSyntaxErrorException (Hash join requires an optimizable equijoin predicate...)
Date Thu, 08 Jul 2010 22:37:49 GMT
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

Mime
View raw message