[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2845) "Another ORA-00907: missing right parenthesis" bug with Oracle

"Another ORA-00907: missing right parenthesis" bug with Oracle -------------------------------------------------------------- Key: HHH-2845 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2845 Project: Hibernate3 Issue Type: Bug Affects Versions: 3.2.5 Environment: hibernate-entitymanager.jar (3.3.1ga) hibernate-annotations.jar (v 3.3.0ga) hibernate-commons-annotations.jar (within Hibernate Annotations.rar\lib) ejb3-persistence.jar (within Hibernate Annotations.rar\lib) hibernate3.jar (v 3.2.5.ga) Oracle 10g Reporter: Dean Pullen Original Estimate: 1 hour Remaining Estimate: 1 hour I'm using JBoss 4.2.1GA, with the Hibernate environment described above, using Oracle 10g, and attempting to perform a query. I've been receiving a "ORA-00907: missing right parenthesis" error when attempting to perform the query. Now a similar bug had this down to Hibernate using the 'as' keyword in the query, aliasing the table name - something that has been fixed in Hibernate 3.3.0ga. (Aliasing a table in the SQL spec is optional, Oracle doesn't support it) But I still get the same error. The query still contains the 'as' keyword (but not aliasing a table) Here is an example: I have two tables. CSIUsers and CSIUserSurveys. There is a one-to-many mapping between CSIUsers to CSIUserSurveys. Query query = entityManager.createQuery("FROM " + CSIUser.class.getSimpleName() + " cu LEFT JOIN cu.csiUserSurveys us" + " WHERE NOT EXISTS (SELECT us FROM cu.csiUserSurveys us WHERE us.csiUserId = cu.csiUserId)"); Result: [[14 Sep 2007 15:43:52] DEBUG org.hibernate.util.JDBCExceptionReporter - could n ot execute query [select csiuser0_.CSI_USER_ID as CSI1_15_0_, csiusersur1_.CSI_U SER_ID as CSI1_16_1_, csiusersur1_.SURVEY_TYPE_CODE as SURVEY2_16_1_, csiuser0_. COMPANY_CODE as COMPANY2_15_0_, csiuser0_.EMAIL_ADDRESS as EMAIL3_15_0_, csiuser 0_.FAILED_LOGINS as FAILED4_15_0_, csiuser0_.LOCALE as LOCALE15_0_, csiuser0_.PA SSWORD as PASSWORD15_0_, csiuser0_.PURCHASE_DATE as PURCHASE7_15_0_, csiuser0_.S TATUS as STATUS15_0_, csiuser0_.USER_NAME as USER9_15_0_, csiuser0_.USER_PRIVILE GE as USER10_15_0_, csiusersur1_.SURVEY_INVITE_DATE as SURVEY3_16_1_ from CSI_US ERS csiuser0_ left outer join CSI_USER_SURVEYS csiusersur1_ on csiuser0_.CSI_USE R_ID=csiusersur1_.CSI_USER_ID where not (exists (select (csiusersur2_.CSI_USER_ ID, csiusersur2_.SURVEY_TYPE_CODE) from CSI_USER_SURVEYS csiusersur2_ where csiu ser0_.CSI_USER_ID=csiusersur2_.CSI_USER_ID and csiusersur2_.CSI_USER_ID=csiuser0 _.CSI_USER_ID))] java.sql.SQLException: ORA-00907: missing right parenthesis