Skip to content
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

Closed
firebird-automations opened this issue Apr 27, 2007 · 10 comments

Comments

@firebird-automations
Copy link
Collaborator

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.

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

The behavior is SQL conformant.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

Is this realy sql conformant?
Where can i find this info?

When you run this query
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

now this have no difference in result with
select
*
FROM
TAB_A A
INNER 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

but i suppose that this two sql statments should return different record set
(Left join stream B to A and Inner join Stream C to B)

@firebird-automations
Copy link
Collaborator Author

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

Yes you have right -
this is SQL conformant.

Thanks for explanation -
i am wrong that i think that
Inner join in this query work on stream B but it really work on result stream of left join B to A.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 11960 ] => Firebird [ 15344 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant