Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » FetchGroup and column alias
FetchGroup and column alias [message #651396] Sun, 30 January 2011 06:40 Go to next message
attila Mising name is currently offline attila Mising name
Messages: 45
Registered: April 2010
Member
Hi ,

When using fetchgroup in a query the generated sql statement does not add an alias to the columns ( Eclipselink 2.1.2 )

Scenario more like a pseudo code to highlight the concept:

@Entity  Country{ id, name , version, ... } 
@Entity  Region { id, name , version, country:Country , ....}
@Entity  City { id, name , version, region: Region, country:Country , .... }


Now i'd like to query the City with the following fetch group

FetchGroup f = new FetchGroup();
f.addAttribute("name");
f.addAttribute("id");
f.addAttribute("region.id");
f.addAttribute("region.name");
f.addAttribute("country.id");
f.addAttribute("country.name");



It fails with
ORA-00918: column ambiguously defined


as the sql has :


SELECT .....  t0.ID,   t0.NAME,    t1.ID,   t1.NAME   .....


The same bahaviour with nested FetchGroup

      FetchGroup country = new FetchGroup();
        country.addAttribute("id");
        country.addAttribute("code");
        f.addAttribute("country", country);


It happens when using left join fetch in jql , works fine without it

Am i missing something ?

[Updated on: Sun, 30 January 2011 09:39]

Report message to a moderator

Re: FetchGroup and column alias [message #651511 is a reply to message #651396] Mon, 31 January 2011 08:42 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1016
Registered: July 2009
Senior Member
Hello,

I'm not sure I see the problem with the SQL you posted, it works fine for me. Can you post the entire SQL query for the working and not working case?

Best Regards,
Chris
Re: FetchGroup and column alias [message #651540 is a reply to message #651511] Mon, 31 January 2011 10:31 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Are you using pagination? i.e. firstResult/maxResults

James : Wiki : Book : Blog : Twitter
Re: FetchGroup and column alias [message #651666 is a reply to message #651540] Tue, 01 February 2011 03:14 Go to previous messageGo to next message
attila Mising name is currently offline attila Mising name
Messages: 45
Registered: April 2010
Member
Hi,


Yes i use pagination:

List<E> list = q.setFirstResult(ctx.getResultStart())
                .setMaxResults(ctx.getResultSize())
.getResultList();



Here is the eql:

select e from City e  left join fetch e.country left join fetch e.region where e.clientId = :clientId and e.country.id = :countryId and e.region.id = :regionId order by  e.name 



Here is the SQL:


SELECT *
  FROM (SELECT /*+ FIRST_ROWS */
         a.*, ROWNUM rnum
          FROM (SELECT t1.ID AS a1,
                       t1.ACTIVE AS a2,
                       t1.CLIENTID AS a3,
                       t1.CODE AS a4,
                       t1.CREATEDAT AS a5,
                       t1.CREATEDBY AS a6,
                       t1.MODIFIEDAT AS a7,
                       t1.MODIFIEDBY AS a8,
                       t1.NAME AS a9,
                       t1.NOTES AS a10,
                       t1.VERSION AS a11,
                       t1.COUNTRY_ID AS a12,
                       t1.REGION_ID AS a13,
                       t0.ID,
                       t0.CODE,
                       t0.VERSION,
                       t2.ID,
                       t2.CODE,
                       t2.VERSION
                  FROM BD_COUNTRY t0, BD_REGION t2, BD_CITY t1
                 WHERE ((((t1.CLIENTID = ?) AND (t1.COUNTRY_ID = ?)) AND
                       (t1.REGION_ID = ?)) AND
                       ((t0.ID = t1.COUNTRY_ID) AND (t2.ID = t1.REGION_ID)))
                 ORDER BY t1.NAME ASC) a
         WHERE ROWNUM <= ?)
 WHERE rnum > ?



As you can see the last six columns are without alias.

If i remove the left join fetch from the query it works as expected :

select e from City e  where ....etc ..








[Updated on: Tue, 01 February 2011 03:16]

Report message to a moderator

Re: FetchGroup and column alias [message #652325 is a reply to message #651666] Thu, 03 February 2011 10:20 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Seems to be a bug with pagination and fetch groups or join fetching.

Please log the bug and vote for it.



James : Wiki : Book : Blog : Twitter
Re: FetchGroup and column alias [message #654173 is a reply to message #652325] Mon, 14 February 2011 08:36 Go to previous message
attila Mising name is currently offline attila Mising name
Messages: 45
Registered: April 2010
Member

Thank you James.

I filed a bug https://bugs.eclipse.org/bugs/show_bug.cgi?id=337115
Previous Topic:RCP and concurrency
Next Topic:ReadOnly object with NativeQuery
Goto Forum:
  


Current Time: Fri Aug 01 12:13:36 EDT 2014

Powered by FUDForum. Page generated in 0.01738 seconds