🚀 go-pugleaf

I see you are the only Codegear employee, so I quote here some of my 
comments from other messages of the thread.

I never wrote manually a query like this, but some _good_ tools do it!

I think this is a bug beacuse:
    SQL-92 should accept this syntax: it's bad but it's correct!
    all other good databases work with this syntax,
    you can have the same problem making a good query with implicit joins
made over some view with explicit outer joins (have I to look inside the
view before building the query?)
    In Interbase 5.6 this worked

This is not as designed because:
    it happens when fetching records not after parsing the query,
    the error is not systematic, but it comes only with outer joins and only
in some circustances
    the message is clearly related to some internal code (from sql we do not
manage fetching)

If it was designed as this,  I'd expect a message like this "You cannot mix
explicit joins with implicit joins" and it should be systematic.

The problem we have, is that we made a lot of projects with Oracle Hyperion
business untelligence suite (the old BrioQuery), and we developed them on
Interbase 5.6; now we migrated a lot of our customers to Interbase 2007 and
we discovered that these projects don't work because Hyperion generate the
queries with implicit joins for inner join and explicit joins for outer
ones.
I don't think I'm the only one with this problem and I don't think you like
have this defect and remember that this syntax is valid in SQL-92.
Maybe Oracle could change something in its product, but their database works
fine in this context and I think they don't mind of Interbase, because, I
repeat, this syntax is standard in SQL-92.

Hi,
Massimiliano.


"Quinn Wildman (CodeGear Developer Support)" <qwildman@nospam.codegear.com> 
ha scritto nel messaggio news:483d8dff$1@newsgroups.borland.com...
> 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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>