New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
A + Left Join B_To_A + Inner join C_To_B different result then A + (Left Join B_To_A + Inner join C_To_B) [CORE1233] #1657
Comments
Commented by: @asfernandes Joins act on streams, it doesn't matter the field name you're using, AFAIU. When you filter (A LEFT JOIN B) (or without parenthesis), http://B.ID = 2 is retired from the stream, and this new stream is INNER JOINed with C. |
Commented by: @dyemanov The behavior is SQL conformant. |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
Commented by: @livius2 Is this realy sql conformant? When you run this query now this have no difference in result with but i suppose that this two sql statments should return different record set |
Commented by: @dyemanov Yes, it is. You may read the SQL specification, if you want. Or just try any other RDBMS -- the result will be the same as in Firebird. Regarding your query: stream C depends on stream B which can contain artificial NULL values (because B is an inner stream of the outer join), hence all those NULLs will be filtered out by the equality predicate, thus forcing LEFT JOIN to behave similarly to INNER JOIN. |
Commented by: @livius2 Yes you have right - Thanks for explanation - |
Modified by: @pcisarWorkflow: jira [ 11960 ] => Firebird [ 15344 ] |
Submitted by: @livius2
################ Three tables ###########################
CREATE TABLE TAB_A
(
ID INTEGER CONSTRAINT NK_TAB_A__ID NOT NULL,
NAME VARCHAR(10) NOT NULL,
CONSTRAINT PK_TAB_A__ID PRIMARY KEY (ID)
);
CREATE TABLE TAB_B
(
ID INTEGER CONSTRAINT NK_TAB_B__ID NOT NULL,
FK_TAB_A INTEGER,
NRVALUE INTEGER CONSTRAINT NK_TAB_B__NRVALUE NOT NULL,
CONSTRAINT PK_TAB_B__ID PRIMARY KEY (ID)
);
CREATE TABLE TAB_C
(
ID INTEGER CONSTRAINT NK_TAB_C__ID NOT NULL,
FK_TAB_B INTEGER CONSTRAINT NK_TAB_C__FK_TAB_B NOT NULL,
CVALUE INTEGER CONSTRAINT NK_TAB_C__CVALUE NOT NULL,
CONSTRAINT PK_TAB_C__ID PRIMARY KEY (ID)
);
ALTER TABLE TAB_B ADD CONSTRAINT FK_TAB_B__FK_TAB_A FOREIGN KEY (FK_TAB_A) REFERENCES TAB_A (ID) ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE TAB_C ADD CONSTRAINT FK_TAB_C__FK_TAB_B FOREIGN KEY (FK_TAB_B) REFERENCES TAB_B (ID) ON UPDATE NO ACTION ON DELETE NO ACTION;
############### Fill Data ###########################
INSERT INTO TAB_A (ID, NAME) VALUES (1, 'REC1');
INSERT INTO TAB_A (ID, NAME) VALUES (2, 'REC2');
INSERT INTO TAB_B (ID, FK_TAB_A, NRVALUE) VALUES (1, 1, 5); /* linked do First record of A */
INSERT INTO TAB_B (ID, FK_TAB_A, NRVALUE) VALUES (2, 2, 6); /* linked do Second record of A */
INSERT INTO TAB_C (ID, FK_TAB_B, CVALUE) VALUES (1, 1, 14); /* linked do First record of B */
INSERT INTO TAB_C (ID, FK_TAB_B, CVALUE) VALUES (2, 1, 18); /* also linked do First record of B */
/* we have not link from C to second record of B */
############### two sql #############################
1:
select
*
FROM
TAB_A A
LEFT JOIN TAB_B B ON B.FK_TAB_A=http://A.ID
INNER JOIN TAB_C C ON C.FK_TAB_B=http://B.ID
result set
ID NAME ID1 FK_TAB_A NRVALUE ID2 FK_TAB_B CVALUE
1 REC1 1 1 5 1 1 14
1 REC1 1 1 5 2 1 18
2:
select
*
FROM
TAB_A A
LEFT JOIN (TAB_B B INNER JOIN TAB_C C ON C.FK_TAB_B=http://B.ID) ON B.FK_TAB_A=http://A.ID
ID NAME ID1 FK_TAB_A NRVALUE ID2 FK_TAB_B CVALUE
1 REC1 1 1 5 1 1 14
1 REC1 1 1 5 2 1 18
2 REC2 <NULL> <NULL> <NULL> <NULL> <NULL> <NULL>
this two sql should return the same result set becaouse we have left join From B to A
and inner join is only to table B from C
this inner join should not have filter access to table A only to B
But now this join filter B and also A table but should not.
The text was updated successfully, but these errors were encountered: