Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » left join implementation for informix problem
left join implementation for informix problem [message #388105] Thu, 21 May 2009 15:38 Go to next message
Janusz is currently offline JanuszFriend
Messages: 1
Registered: July 2009
Junior Member
Hi,


I'm using Sun Java System Application Server 9.1_02, Java Persistence
implementation is toplink included with the server.
My Infomix version is IDS 11.50

The following Java Persistance query:

SELECT DISTINCT a.key1, a.key2, a.key3, a.key4, c.preference, ......
FROM Product a LEFT JOIN a.refSupHeader b LEFT JOIN b.refSup c
WHERE a.type = ?1
ORDER BY a.key1, a.key2, a.key3, a.key4, c.rowNb

for Informix is translated as:

SELECT DISTINCT t0.KEY1, t0.KEY2, t0.KEY3, t0.KEY4, t1.PREFERENCE,
.... FROM p_product t0, OUTER p_ref_sup_header t2, p_ref_sup t1
WHERE ((t0.TYPE = ?) AND ((t2.ID = t0.p_ref_sup_header_id) AND
(t1.p_ref_sup_header_id = t2.ID)))
ORDER BY t0.KEY1 ASC, t0.KEY2 ASC, t0.KEY3 ASC, t0.KEY4 ASC, t1.row_nb ASC
bind => [MAT]

what is totaly wrong. Infomix supports LEFT JOIN, why Java Persistance is
using old OUTER syntax?

The proper query syntax, generated by JP for other database servers is:

SELECT DISTINCT t0.KEY1, t0.KEY2, t0.KEY3, t0.KEY4, t1.PREFERENCE, ......
FROM p_product t0 LEFT OUTER JOIN p_ref_sup_header t2 ON (t2.ID =
t0.p_ref_sup_header_id)
LEFT OUTER JOIN p_ref_sup t1 ON (t1.p_ref_sup_header_id
= t2.ID)
WHERE (t0.TYPE = ?) ORDER BY t0.KEY1 ASC, t0.KEY2 ASC, t0.KEY3 ASC,
t0.KEY4 ASC, t1.row_nb ASC
bind => [MAT]

The above query works properly in infomix database when executed in
dbaccess tool.
Is there a way to force Java Persistence to generate right query syntax
like above?

Thank you very match for any help.
Janusz
Re: left join implementation for informix problem [message #388109 is a reply to message #388105] Fri, 22 May 2009 13:30 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom EugelinkFriend
Messages: 807
Registered: July 2009
Senior Member
I also use Eclipselink on top of Informix.

But even though the SQL syntax is old, as long as Informix supports it, it is not wrong. Are you actually receiving different results from the two queries?

Tom




Janusz wrote:
> Hi,
>
>
> I'm using Sun Java System Application Server 9.1_02, Java Persistence
> implementation is toplink included with the server.
> My Infomix version is IDS 11.50
>
> The following Java Persistance query:
>
> SELECT DISTINCT a.key1, a.key2, a.key3, a.key4, c.preference, ......
> FROM Product a LEFT JOIN a.refSupHeader b LEFT JOIN b.refSup c
> WHERE a.type = ?1 ORDER BY a.key1, a.key2, a.key3, a.key4, c.rowNb
>
> for Informix is translated as:
>
> SELECT DISTINCT t0.KEY1, t0.KEY2, t0.KEY3, t0.KEY4, t1.PREFERENCE,
> ... FROM p_product t0, OUTER p_ref_sup_header t2, p_ref_sup t1
> WHERE ((t0.TYPE = ?) AND ((t2.ID = t0.p_ref_sup_header_id) AND
> (t1.p_ref_sup_header_id = t2.ID))) ORDER BY t0.KEY1 ASC, t0.KEY2
> ASC, t0.KEY3 ASC, t0.KEY4 ASC, t1.row_nb ASC
> bind => [MAT]
>
> what is totaly wrong. Infomix supports LEFT JOIN, why Java Persistance
> is using old OUTER syntax?
>
> The proper query syntax, generated by JP for other database servers is:
>
> SELECT DISTINCT t0.KEY1, t0.KEY2, t0.KEY3, t0.KEY4, t1.PREFERENCE, ......
> FROM p_product t0 LEFT OUTER JOIN p_ref_sup_header t2 ON (t2.ID =
> t0.p_ref_sup_header_id) LEFT OUTER JOIN p_ref_sup t1
> ON (t1.p_ref_sup_header_id = t2.ID) WHERE (t0.TYPE = ?) ORDER BY t0.KEY1
> ASC, t0.KEY2 ASC, t0.KEY3 ASC, t0.KEY4 ASC, t1.row_nb ASC
> bind => [MAT]
>
> The above query works properly in infomix database when executed in
> dbaccess tool. Is there a way to force Java Persistence to generate
> right query syntax like above?
>
> Thank you very match for any help.
> Janusz
>
Re: left join implementation for informix problem [message #388113 is a reply to message #388109] Mon, 25 May 2009 09:50 Go to previous messageGo to next message
Janusz is currently offline JanuszFriend
Messages: 3
Registered: July 2009
Junior Member
Yes, result of the query is wrong - it doesnt load p_product records where
no record in p_ref_sup exists. Probably (?) because OUTER is missing in
front of "p_ref_sup t1 " in FROM clause of the generated query.

Janusz



tbee wrote:

> I also use Eclipselink on top of Informix.

> But even though the SQL syntax is old, as long as Informix supports it, it
is not wrong. Are you actually receiving different results from the two
queries?

> Tom




> Janusz wrote:
>> Hi,
>>
>>
>> I'm using Sun Java System Application Server 9.1_02, Java Persistence
>> implementation is toplink included with the server.
>> My Infomix version is IDS 11.50
>>
>> The following Java Persistance query:
>>
>> SELECT DISTINCT a.key1, a.key2, a.key3, a.key4, c.preference, ......
>> FROM Product a LEFT JOIN a.refSupHeader b LEFT JOIN b.refSup c
>> WHERE a.type = ?1 ORDER BY a.key1, a.key2, a.key3, a.key4, c.rowNb
>>
>> for Informix is translated as:
>>
>> SELECT DISTINCT t0.KEY1, t0.KEY2, t0.KEY3, t0.KEY4, t1.PREFERENCE,
>> ... FROM p_product t0, OUTER p_ref_sup_header t2, p_ref_sup t1
>> WHERE ((t0.TYPE = ?) AND ((t2.ID = t0.p_ref_sup_header_id) AND
>> (t1.p_ref_sup_header_id = t2.ID))) ORDER BY t0.KEY1 ASC, t0.KEY2
>> ASC, t0.KEY3 ASC, t0.KEY4 ASC, t1.row_nb ASC
>> bind => [MAT]
>>
>> what is totaly wrong. Infomix supports LEFT JOIN, why Java Persistance
>> is using old OUTER syntax?
>>
>> The proper query syntax, generated by JP for other database servers is:
>>
>> SELECT DISTINCT t0.KEY1, t0.KEY2, t0.KEY3, t0.KEY4, t1.PREFERENCE, ......
>> FROM p_product t0 LEFT OUTER JOIN p_ref_sup_header t2 ON (t2.ID =
>> t0.p_ref_sup_header_id) LEFT OUTER JOIN p_ref_sup t1
>> ON (t1.p_ref_sup_header_id = t2.ID) WHERE (t0.TYPE = ?) ORDER BY t0.KEY1
>> ASC, t0.KEY2 ASC, t0.KEY3 ASC, t0.KEY4 ASC, t1.row_nb ASC
>> bind => [MAT]
>>
>> The above query works properly in infomix database when executed in
>> dbaccess tool. Is there a way to force Java Persistence to generate
>> right query syntax like above?
>>
>> Thank you very match for any help.
>> Janusz
>>
Re: left join implementation for informix problem [message #388114 is a reply to message #388113] Mon, 25 May 2009 15:02 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom EugelinkFriend
Messages: 807
Registered: July 2009
Senior Member
I have not run into problems so far. But I figure it should not be that difficult to copy-paste the generation code for, say, Oracle to the Informix class. Do you know since which version IFX supports the ANSI notations?

Tom




Janusz wrote:
> Yes, result of the query is wrong - it doesnt load p_product records
> where no record in p_ref_sup exists. Probably (?) because OUTER is
> missing in front of "p_ref_sup t1 " in FROM clause of the generated query.
>
> Janusz
>
>
>
> tbee wrote:
>
>> I also use Eclipselink on top of Informix.
>
>> But even though the SQL syntax is old, as long as Informix supports
>> it, it
> is not wrong. Are you actually receiving different results from the two
> queries?
>
>> Tom
>
>
>
>
>> Janusz wrote:
>>> Hi,
>>>
>>>
>>> I'm using Sun Java System Application Server 9.1_02, Java Persistence
>>> implementation is toplink included with the server.
>>> My Infomix version is IDS 11.50
>>>
>>> The following Java Persistance query:
>>>
>>> SELECT DISTINCT a.key1, a.key2, a.key3, a.key4, c.preference, ......
>>> FROM Product a LEFT JOIN a.refSupHeader b LEFT JOIN b.refSup c
>>> WHERE a.type = ?1 ORDER BY a.key1, a.key2, a.key3, a.key4,
>>> c.rowNb
>>>
>>> for Informix is translated as:
>>>
>>> SELECT DISTINCT t0.KEY1, t0.KEY2, t0.KEY3, t0.KEY4,
>>> t1.PREFERENCE, ... FROM p_product t0, OUTER p_ref_sup_header t2,
>>> p_ref_sup t1 WHERE ((t0.TYPE = ?) AND ((t2.ID =
>>> t0.p_ref_sup_header_id) AND (t1.p_ref_sup_header_id = t2.ID)))
>>> ORDER BY t0.KEY1 ASC, t0.KEY2 ASC, t0.KEY3 ASC, t0.KEY4 ASC,
>>> t1.row_nb ASC
>>> bind => [MAT]
>>> what is totaly wrong. Infomix supports LEFT JOIN, why Java
>>> Persistance is using old OUTER syntax?
>>>
>>> The proper query syntax, generated by JP for other database servers is:
>>>
>>> SELECT DISTINCT t0.KEY1, t0.KEY2, t0.KEY3, t0.KEY4, t1.PREFERENCE,
>>> ......
>>> FROM p_product t0 LEFT OUTER JOIN p_ref_sup_header t2 ON (t2.ID =
>>> t0.p_ref_sup_header_id) LEFT OUTER JOIN p_ref_sup
>>> t1 ON (t1.p_ref_sup_header_id = t2.ID) WHERE (t0.TYPE = ?) ORDER BY
>>> t0.KEY1 ASC, t0.KEY2 ASC, t0.KEY3 ASC, t0.KEY4 ASC, t1.row_nb ASC
>>> bind => [MAT]
>>> The above query works properly in infomix database when executed
>>> in dbaccess tool. Is there a way to force Java Persistence to
>>> generate right query syntax like above?
>>>
>>> Thank you very match for any help.
>>> Janusz
>>>
>
>
Re: left join implementation for informix problem [message #388121 is a reply to message #388105] Mon, 25 May 2009 17:53 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

The InformixPlatform code for outer-joins is probably old, it use to be
required on Informix as it did not support the standard syntax.

To fix this create your own InformixPlatform subclass and override the
method,
isInformixOuterJoin() to return false.

Please log a bug to have this old syntax removed.

---
James
http://www.nabble.com/EclipseLink---Users-f26658.html


James : Wiki : Book : Blog : Twitter
Re: left join implementation for informix problem [message #388424 is a reply to message #388121] Tue, 26 May 2009 06:32 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom EugelinkFriend
Messages: 807
Registered: July 2009
Senior Member
> To fix this create your own InformixPlatform subclass and override the
> method,
> isInformixOuterJoin() to return false.

Wow. I'm always impressed with how well the code has been setup. I'll switch to the newer syntax as well.

Tom
Re: left join implementation for informix problem [message #388429 is a reply to message #388121] Tue, 26 May 2009 12:24 Go to previous messageGo to next message
Janusz is currently offline JanuszFriend
Messages: 3
Registered: July 2009
Junior Member
Thank you, to works!
Janusz



James wrote:

> The InformixPlatform code for outer-joins is probably old, it use to be
> required on Informix as it did not support the standard syntax.

> To fix this create your own InformixPlatform subclass and override the
> method,
> isInformixOuterJoin() to return false.

> Please log a bug to have this old syntax removed.

> ---
> James
> http://www.nabble.com/EclipseLink---Users-f26658.html
Re: left join implementation for informix problem [message #388437 is a reply to message #388429] Wed, 27 May 2009 11:58 Go to previous message
Janusz is currently offline JanuszFriend
Messages: 3
Registered: July 2009
Junior Member
Thank you, it works!
Janusz


> James wrote:

>> The InformixPlatform code for outer-joins is probably old, it use to be
>> required on Informix as it did not support the standard syntax.

>> To fix this create your own InformixPlatform subclass and override the
>> method,
>> isInformixOuterJoin() to return false.

>> Please log a bug to have this old syntax removed.

>> ---
>> James
>> http://www.nabble.com/EclipseLink---Users-f26658.html
Previous Topic:null in or part of where clause
Next Topic:Zero = invalid primary key
Goto Forum:
  


Current Time: Thu Dec 18 22:38:16 GMT 2014

Powered by FUDForum. Page generated in 0.03702 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software