Hello,
I have the following tables :
CREATE TABLE MYTABLE
(
PK BIGINT NOT NULL PRIMARY KEY,
NAME VARCHAR(255),
INTERNAL_KEY VARCHAR(255) UNIQUE,
REL_PK BIGINT,
)
CREATE TABLE PROP
(
OWNER_PK BIGINT,
VALUE VARCHAR(255),
ID VARCHAR(255)
)
ALTER TABLE MYTABLE ADD CONSTRAINT rel_pk FOREIGN KEY (REL_PK) REFERENCES
MYTABLE(PK)
ALTER TABLE PROP ADD CONSTRAINT owner_pk FOREIGN KEY (OWNER_PK) REFERENCES
MYTABLE(PK)
If I try to execute a query with a left outer join on "PROP", the optimizer
didn't return the expected execution plan :
explain analyze select A.NAME, X.VALUE as XVALUE from MYTABLE A
inner join MYTABLE J on A.REL_PK = J.PK and J.INTERNAL_KEY = 'x'
left outer join PROP X ON X.OWNER_PK = A.PK and X.ID='myid'
SELECT
A.NAME,
X.VALUE AS XVALUE
FROM PUBLIC.MYTABLE A
/* PUBLIC.MYTABLE.tableScan */
/* scanCount: 1 */
INNER JOIN PUBLIC.MYTABLE J
/* PUBLIC.PRIMARY_KEY_8: PK = A.REL_PK
AND PK = A.REL_PK
*/
ON 1=1
/* WHERE (J.INTERNAL_KEY = 'x')
AND (A.REL_PK = J.PK)
*/
LEFT OUTER JOIN PUBLIC.PROP X
/* PUBLIC.OWNER_PK_INDEX_2: OWNER_PK = A.PK */
ON (X.ID = 'myid')
AND (X.OWNER_PK = A.PK)
WHERE (J.INTERNAL_KEY = 'x')
AND (A.REL_PK = J.PK)
In fact, it tries only the permutation [A, X, J] or [J, X, A] and not [J,
A, X] which is in my case the best execution plan ("internal_key" is unique
with a selectivity of 100, starting with J is the best execution plan in
this case)
If I remove the "left outer join", the optimizer returns the best result :
explain analyze select A.NAME from MYTABLE A
inner join MYTABLE J on A.REL_PK = J.PK and J.INTERNAL_KEY = 'x'
SELECT
A.NAME
FROM PUBLIC.MYTABLE J
/* PUBLIC.CONSTRAINT_INDEX_8: INTERNAL_KEY = 'x'
AND INTERNAL_KEY = 'x'
*/
/* WHERE J.INTERNAL_KEY = 'x'
*/
/* scanCount: 1 */
INNER JOIN PUBLIC.MYTABLE A
/* PUBLIC.REL_PK_INDEX_8: REL_PK = J.PK */
ON 1=1
WHERE (J.INTERNAL_KEY = 'x')
AND (A.REL_PK = J.PK)
I can't change the query because they are generated, and this is just a
small extract in order to reproduce this behavior.
Is it the "expected optimization result" with H2, or a (known) bug ?
I am using H2 v1.4.197
Regards,
Jérémie
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
