I found a bug where SQLBase does not respect an IN-Clause, when two LEFT-OUTER-JOINS are used. I get row-results which I do not expect (as they do not match the WHERE-CLAUSE).
I am using SQLBase 11.7 SP4.
Minimal working example to reproduce the bug:
Code: Select all
// CREATE TEST-DATABASE;
CREATE DATABASE OJBUG;
CONNECT OJBUG SYSADM/SYSADM;
CREATE TABLE SYSADM.BUILDING (BUILDING_KEY INTEGER NOT NULL, COMPANY_KEY INTEGER NOT NULL WITH DEFAULT);
INSERT INTO BUILDING (BUILDING_KEY,COMPANY_KEY) VALUES (1,1);
INSERT INTO BUILDING (BUILDING_KEY,COMPANY_KEY) VALUES (2,2);
CREATE TABLE SYSADM.ROOM (ROOM_KEY INTEGER NOT NULL, BLD_KEY INTEGER);
INSERT INTO ROOM (ROOM_KEY, BLD_KEY) VALUES (1,1);
INSERT INTO ROOM (ROOM_KEY, BLD_KEY) VALUES (2,1);
CREATE TABLE LINKED_ROOMS (MAIN_ROOM INTEGER NOT NULL, SUB_ROOM INTEGER NOT NULL);
INSERT INTO LINKED_ROOMS (MAIN_ROOM, SUB_ROOM) VALUES (1,2);
// UNEXPECTEDLY RETURNS A RESULT;
SELECT MAIN_ROOM, SUB_ROOM, BUILDING_KEY, COMPANY_KEY FROM LINKED_ROOMS
LEFT OUTER JOIN ROOM ON LINKED_ROOMS.MAIN_ROOM=ROOM.ROOM_KEY
LEFT OUTER JOIN BUILDING ON ROOM.BLD_KEY=BUILDING.BUILDING_KEY
WHERE BUILDING.COMPANY_KEY IN (2);
// RETURNS NO RESULT (AS EXPECTED);
SELECT MAIN_ROOM, SUB_ROOM, BUILDING_KEY, COMPANY_KEY FROM LINKED_ROOMS
LEFT OUTER JOIN ROOM ON LINKED_ROOMS.MAIN_ROOM=ROOM.ROOM_KEY
LEFT OUTER JOIN BUILDING ON ROOM.BLD_KEY=BUILDING.BUILDING_KEY
WHERE BUILDING.COMPANY_KEY=2;
// RETURNS NO RESULT (AS EXPECTED);
SELECT MAIN_ROOM, SUB_ROOM, BUILDING_KEY, COMPANY_KEY FROM LINKED_ROOMS
JOIN ROOM ON LINKED_ROOMS.MAIN_ROOM=ROOM.ROOM_KEY
JOIN BUILDING ON ROOM.BLD_KEY=BUILDING.BUILDING_KEY
WHERE BUILDING.COMPANY_KEY IN (2);