The following query returns wrong results in server mode - embedded
mode is fine (tested with latest stable 1.2.147):
DROP TABLE PMS_SETTING_TEST IF EXISTS;
CREATE TABLE PMS_SETTING_TEST (
PMS_SETTING_ID INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY (PMS_SETTING_ID)
);
DROP TABLE INSTALLATION_TEST IF EXISTS;
CREATE TABLE INSTALLATION_TEST (
INSTALLATION_ID INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY (INSTALLATION_ID)
);
DROP TABLE PMS_SETTING_DISABLED_TEST IF EXISTS;
CREATE TABLE PMS_SETTING_DISABLED_TEST (
PMS_SETTING_DISABLED_ID INTEGER NOT NULL AUTO_INCREMENT,
PMS_SETTING_ID INTEGER,
INSTALLATION_ID INTEGER NOT NULL,
PRIMARY KEY (PMS_SETTING_DISABLED_ID, INSTALLATION_ID)
);
INSERT INTO PMS_SETTING_TEST VALUES (1);
INSERT INTO PMS_SETTING_TEST VALUES (3);
INSERT INTO INSTALLATION_TEST VALUES (85217);
INSERT INTO PMS_SETTING_DISABLED_TEST VALUES (4, 1, 85217);
INSERT INTO PMS_SETTING_DISABLED_TEST VALUES (5, 1, 33445);
INSERT INTO PMS_SETTING_DISABLED_TEST VALUES (6, 3, 33445);
SELECT *
FROM PMS_SETTING_TEST
LEFT JOIN PMS_SETTING_DISABLED_TEST ON
PMS_SETTING_DISABLED_TEST.PMS_SETTING_ID =
PMS_SETTING_TEST.PMS_SETTING_ID
AND PMS_SETTING_DISABLED_TEST.INSTALLATION_ID = 85217
WHERE PMS_SETTING_DISABLED_ID IS NULL
If run on a server mode db, the query returns two records instead of
one as expected. At first glance it looks like the query optimizer
uses "PMS_SETTING_DISABLED_ID IS NULL" as additional JOIN condition,
which is illegal for LEFT JOINs.
Thanks for any feedback
Remo
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
