🚀 go-pugleaf

R&D has determined SQL with implicit and explicit joins is bad SQL and 
therefore is not supported.

Massimiliano Trezzi wrote:
> Hi,
> 
> I have a problem with Interbase 2007.
> 
> If I make a query with some explicit left outer join (all in from clause) 
> and some implicit inner join (tables in from clause, conditions in where 
> clause) I have some chance to get an error: no current record for fetch 
> operation.
> 
> The problem can arise using a view with left outer join in a query with only 
> implicit joins too.
> 
> I agree that is not the best way to make a query, but some tools do so and 
> this make this trouble a big trouble!
> 
> Here are the steps to reproduce the problem:
> 
> CREATE TABLE A (
>     ID INTEGER NOT NULL PRIMARY KEY,
>     DES VARCHAR(128)
> );
> 
> COMMIT;
> 
> CREATE TABLE B (
>     ID INTEGER NOT NULL PRIMARY KEY,
>     A_ID INTEGER,
>     DES VARCHAR(128)
> );
> 
> COMMIT;
> 
> ALTER TABLE B ADD CONSTRAINT FK_B_A FOREIGN KEY (A_ID) REFERENCES A(ID);
> 
> COMMIT;
> 
> CREATE TABLE C (
>     ID INTEGER NOT NULL PRIMARY KEY,
>     B_ID INTEGER,
>     DES VARCHAR(128)
> );
> 
> COMMIT;
> 
> ALTER TABLE C ADD CONSTRAINT FK_C_B FOREIGN KEY (B_ID) REFERENCES B(ID);
> 
> COMMIT;
> 
> COMMIT;
> 
> INSERT INTO A (ID, DES) VALUES (1,'A-ONE');
> INSERT INTO A (ID, DES) VALUES (2,'A-TWO');
> INSERT INTO A (ID, DES) VALUES (3,'A-THREE');
> INSERT INTO A (ID, DES) VALUES (4,'A-FOUR');
> INSERT INTO A (ID, DES) VALUES (5,'A-FIVE');
> 
> INSERT INTO B (ID, DES, A_ID) VALUES (1,'B-ONE',1);
> INSERT INTO B (ID, DES, A_ID) VALUES (2,'B-TWO',2);
> INSERT INTO B (ID, DES, A_ID) VALUES (3,'B-THREE',3);
> INSERT INTO B (ID, DES, A_ID) VALUES (4,'B-FOUR',NULL);
> 
> INSERT INTO C (ID, DES, B_ID) VALUES (1,'C-ONE',1);
> INSERT INTO C (ID, DES, B_ID) VALUES (2,'C-TWO',2);
> INSERT INTO C (ID, DES, B_ID) VALUES (3,'C-THREE',3);
> INSERT INTO C (ID, DES, B_ID) VALUES (4,'C-FOUR',4);
> 
> COMMIT;
> 
> 
> /******************* TEST QUERIES *******************/
> 
> /* OK! */
> SELECT * FROM C INNER JOIN AB ON C.B_ID = AB.B_ID;
> 
> /* NO CURRENT RECORD FOR FETCH OPERATION */
> SELECT * FROM A, C LEFT OUTER JOIN B ON B.A_ID = A.ID WHERE C.B_ID = B.ID
> 
> /* OK */
> SELECT * FROM A LEFT OUTER JOIN B ON B.A_ID = A.ID, C WHERE C.B_ID = B.ID
> 
> Thank you in advance.
> Massimiliano Trezzi
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
>