Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 12 posts ] 
Author Message
 Post subject: Hibernate generates wrong SQL query for Oracle
PostPosted: Thu Jan 18, 2007 5:30 pm 
Newbie

Joined: Sun Dec 17, 2006 10:15 am
Posts: 6
Location: Prague
I have the following three tables:
Code:
CREATE TABLE "ECS_IMPLEMENTATIONS" (
  "IMPLEMENTATION_ID" NUMBER(10,0),
  ...
);

CREATE TABLE "ECS_REFLIST_VALUES" (
  "LIST_ITEM_ID" NUMBER(10,0),
  "LIST_NAME" VARCHAR2(30 BYTE),
  "LIST_VALUE" VARCHAR2(30 BYTE)
);

CREATE TABLE "ECS_IMPL_REFLIST_VALUES" (
  "IMPLEMENTATION_ID" NUMBER(10,0),
  "LIST_ITEM_ID" NUMBER(10,0),
  ...
);

As you can see the ECS_IMPL_REFLIST_VALUES is many-to-many association table between both ECS_IMPLEMENTATIONS and ECS_REFLIST_VALUES.

Then I have defined these two entities:
Code:
@Entity
@Table(name = "ECS_IMPLEMENTATIONS")
public class ECSImplementation {
  ...
  @ManyToMany(cascade = { CascadeType.PERSIST, CascadeType.MERGE }, fetch = FetchType.LAZY)
  @JoinTable(name = "ECS_IMPL_REFLIST_VALUES",
    joinColumns = @JoinColumn(name = "IMPLEMENTATION_ID"),
    inverseJoinColumns = @JoinColumn(name = "LIST_ITEM_ID")
  )
  @Where(clause = "LIST_NAME = 'ORDER_PAYMENT_TYPES")
  @MapKey(name = "listValue")
  @Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
  private Map<String, ECSReflistValue> deliveryTypes;
  ...
}

@Entity
@Table (name = "ECS_REFLIST_VALUES")
public class ECSReflistValue {

  @Id
  @Column (name = "LIST_ITEM_ID")
  private Long id;
 
  @Column (name = "LIST_NAME")
  private String listName;

  @Column (name = "LIST_VALUE")
  private String listValue;
  ...
}


Now when I try to acess ECSImplementation.deliveryTypes, Hibernate generates the query
Code:
select paymenttyp0_.IMPLEMENTATION_ID as IMPLEMEN1_1_, paymenttyp0_.LIST_ITEM_ID as LIST2_1_, (select a5.LIST_VALUE from ECS_REFLIST_VALUES as a5 where a5.LIST_ITEM_ID=paymenttyp0_.LIST_ITEM_ID) as formula2_1_, ecsreflist1_.LIST_ITEM_ID as LIST1_35_0_, ecsreflist1_.TEXT_ID as TEXT2_35_0_, ecsreflist1_.LIST_NAME as LIST3_35_0_, ecsreflist1_.LIST_VALUE as LIST4_35_0_ from ECS_IMPL_REFLIST_VALUES paymenttyp0_ left outer join ECS_REFLIST_VALUES ecsreflist1_ on paymenttyp0_.LIST_ITEM_ID=ecsreflist1_.LIST_ITEM_ID where  ( ecsreflist1_.LIST_NAME = 'ORDER_PAYMENT_TYPES' ) and paymenttyp0_.IMPLEMENTATION_ID=?

which fails with the exception java.sql.SQLException: ORA-00907: missing right parenthesis.

Can anyone sense where is a problem and why does Hibernate generate wrong SQL? Thank you for any info.

--
Hibernate settings:
Code:
<prop key="hibernate.dialect">org.hibernate.dialect.Oracle9Dialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.connection.autocommit">false</prop>
<prop key="hibernate.cache.use_second_level_cache">true</prop>
<prop key="hibernate.cache.use_query_cache">true</prop>
<prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>

Hibernate version: 3.2.0.ga
Database: Oracle Database 10.2 XE
JDBC driver: Oracle Thin JDBC Driver 10.1.0.5.0

_________________
Tomáš Klíma, Aiteq Ltd.
tomas.klima@aiteq.com | skype: tomas.klima | icq: 117-871-950 | www.aiteq.cz


Last edited by Darbic on Tue Feb 27, 2007 7:47 pm, edited 1 time in total.

Top
 Profile  
 
 Post subject: I'm having the same problem
PostPosted: Mon Feb 05, 2007 6:38 am 
Newbie

Joined: Mon Feb 05, 2007 6:28 am
Posts: 4
I'm having the same problem as Darbic

In addition, I have tried using the old OracleDialect and Hibernate version 3.2.1ga with no luck.

Any insight / solution / workaround would be very welcome.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Feb 05, 2007 8:41 am 
Newbie

Joined: Tue Oct 03, 2006 9:22 am
Posts: 11
This problems looks suspicisly like the problem I have in http://forum.hibernate.org/viewtopic.php?t=970557

Any insight will be welcomed.

/Steen


Top
 Profile  
 
 Post subject:
PostPosted: Mon Feb 05, 2007 9:00 am 
Newbie

Joined: Sun Dec 17, 2006 10:15 am
Posts: 6
Location: Prague
Hi, I'm not an SQL guru but there is no problem with missing parentehsis in the statement. Actualy the 'as' keyword in the inner select after table name is surplus. It appears as a bug in OracleDialect implementation...

_________________
Tomáš Klíma, Aiteq Ltd.
tomas.klima@aiteq.com | skype: tomas.klima | icq: 117-871-950 | www.aiteq.cz


Top
 Profile  
 
 Post subject: bug in OracleDialect implementation
PostPosted: Tue Feb 06, 2007 7:40 pm 
Newbie

Joined: Fri Feb 02, 2007 4:01 pm
Posts: 2
Location: Wisconsin
Darbic wrote:
Actually the 'as' keyword in the inner select after table name is surplus. It appears as a bug in OracleDialect implementation...


Checking the http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76989/ch4l2.htm#16368 Oracle doc, it seems you are correct. There is an optional AS keyword before c_alias (column), but that is not true for t_alias. See the query_table_expression_clause


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 07, 2007 5:46 am 
Newbie

Joined: Tue Oct 03, 2006 9:22 am
Posts: 11
So we agree that this is a problem we should report to the developers by making a bug report ?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 07, 2007 10:29 am 
Newbie

Joined: Mon Feb 05, 2007 6:28 am
Posts: 4
Has anyone tested with hibernate 3.2.2?

I'll test it myself and if the error persists, I'll post a bug report.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 07, 2007 11:50 am 
Newbie

Joined: Mon Feb 05, 2007 6:28 am
Posts: 4
Posted as JIRA ref "HHH-2409" - see http://opensource.atlassian.com/project ... e/HHH-2409

After a bit of digging, it looks like this is not defined in the Dialect classes - it's hard coded in the core engine - so I'm reluctant to try and fix it myself.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Feb 09, 2007 12:30 pm 
Newbie

Joined: Fri Feb 02, 2007 4:01 pm
Posts: 2
Location: Wisconsin
[quote="gregtill"]After a bit of digging, it looks like this is not defined in the Dialect classes - it's hard coded in the core engine.[/quote]

Hard coded? What class is it? I'm interested, considering it would also seem to me this would most definitely not be part of the core classes, but then I'm new to Hibernate (not new to the concept as I've worked with EJBs, Enhydra DODS, Castor)


Top
 Profile  
 
 Post subject:
PostPosted: Fri Feb 09, 2007 1:37 pm 
Newbie

Joined: Mon Feb 05, 2007 6:28 am
Posts: 4
TBH I'm not absolutely certain which line of code is responsible and work pressures mean I don't have a huge amount of time to spend at the moment.

Having said that, there is no String constant with "as" in it in the Dialect classes (well, there is one, but it's to do with casting, not aliasing).

There are several core classes with code like:

Code:
projection.toString() + " as " + alias;


which looks like a contender to me.

See -

org.hibernate.sql.*Fragment (there's a few in here).

In particular -

org.hibernate.sql.SelectFragment
org.hibernate.sql.SimpleSelect

org.hibernate.criterion.AliasedProjection

It's a shame if it's not customisable via a Dialect, but the hibernate programmers have to assume a set of common SQL...


Top
 Profile  
 
 Post subject:
PostPosted: Mon Feb 19, 2007 10:47 am 
Newbie

Joined: Mon Feb 19, 2007 10:31 am
Posts: 1
Hi,

the code that creates the malicious "as" is located in
org.hibernate.cfg.annotations.MapBinder, method "createFormulatedValue" (hibernate-annotations-3.2.1.GA):

Code:
StringBuilder fromAndWhereSb = new StringBuilder( " from " )
.append( associatedClass.getTable().getName() )
.append(" as ").append(alias).append(" where ");


Top
 Profile  
 
 Post subject: Excellent!
PostPosted: Wed Feb 21, 2007 4:17 pm 
Newbie

Joined: Tue Oct 03, 2006 9:22 am
Posts: 11
Great work!

All my problems disappeared after I recompiled the annotations package after having removed the " as " u spoted.

Just wanted to say "great work!" :)

/Steen


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 12 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.