🚀 go-pugleaf

Why don't you reply?

Hi,
Massimiliano Trezzi

"Massimiliano Trezzi" <support_leavethis_@intesanet.it> ha scritto nel 
messaggio news:4850c5a0@newsgroups.borland.com...
>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
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>