SQLB-2520: Unexpected ROW-Results on Query with two OUTER JOINs

Report SqlBase bugs and possible workarounds.
CasablancaTech

SQLB-2520: Unexpected ROW-Results on Query with two OUTER JOINs

Post by CasablancaTech » 21 Sep 2016, 10:40

Hello!
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);
The OUTER-JOIN would not be necessary, but I can not remove it easily, because it is generated by NHibernate.

Mike Vandine

Re: SQLB-2520: Unexpected ROW-Results on Query with two OUTER JOINs

Post by Mike Vandine » 03 Oct 2016, 03:31

Hi,

I can reproduce this with 11.7 SP4 and also with 12.0.1.

Return to “Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 0 guests