[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2211) A createQuery() caused ORA-00904 error

[sodia (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2211?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_29157 ] 

sodia commented on HHH-2211:
----------------------------

Thank you for your kind correction,which solves the grammar error,but the result differs from what I am expecting:

The 4th event,'BUY A XBOX',is not generated by a real person,it might be generated by a machine.It hasn't be recorded in the PERSONEVENT table,so I do not want to move it to the history table.

When I run with your modified hql query,it inserts 3 records into the history table:
EVENT_ID TITLE
1        BUY A CAR
3        BUY A PS3
4        BUY A XBOX
When I run under sqlplus,it inserts 2 records into the history table:
EVENT_ID TITLE
1        BUY A CAR
3        BUY A PS3

I think there is something different between oracle's sqlplus grammar and hiberate's grammar.Sqlplus recognizes the outer EVENT and inner EVENT as one table.Hibernate regards the outer EVENT and inner EVENT as two seperated tables.

> A createQuery() caused ORA-00904 error
> --------------------------------------
>
>                 Key: HHH-2211
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2211
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: query-hql
>    Affects Versions: 3.2.0.ga
>         Environment: JDK1.5.0+Eclipse3.2.1+Hibernate3.2.0.ga+Oracle9.2.0+WinXp(Prof)Sp2
>            Reporter: sodia
>            Priority: Trivial
>         Attachments: hiber-grammar-sql-and-errlog.zip, hiber-grammar-test.zip
>
>   Original Estimate: 2 weeks
>  Remaining Estimate: 2 weeks
>
> I want to try the following sql under hibernate,it failed.
> (The project is build on the basis of hibernate3.2's tutorial)
>         String hqlAlias = 
>         	"insert into EventHistory ( id, date, title )" +
>         	"  select id, date, title from Event e" +
>         	"    where e.title like 'BUY%'" +
>         	"      and exists" +
>         	"        ( select 1 from PersonEvent p" +
>         	"          where p.id=e.id)";
>                 int resultAlias = session.createQuery(hqlAlias).executeUpdate();
> But it works nice under sqlplus.
> INSERT INTO EVENTS_HISTORY
>   SELECT * FROM EVENTS
>     WHERE TITLE LIKE 'BUY%'
>       AND EXISTS
>         ( SELECT 1 FROM PERSON_EVENT
>           WHERE EVENT_ID=EVENTS.EVENT_ID);
> The 4 lines of error log is like this:
> 15:33:29,906 DEBUG SQL:393 - insert into EVENTS_HISTORY ( EVENT_ID, EVENT_DATE, title ) select event0_.EVENT_ID as col_0_0_, event0_.EVENT_DATE as col_1_0_, event0_.title as col_2_0_ from EVENTS event0_ where (event0_.title like 'BUY%') and (exists (select 1 from PERSON_EVENT personeven1_ where personeven1_.EVENT_ID=EVENTS.EVENT_ID))
> Hibernate: insert into EVENTS_HISTORY ( EVENT_ID, EVENT_DATE, title ) select event0_.EVENT_ID as col_0_0_, event0_.EVENT_DATE as col_1_0_, event0_.title as col_2_0_ from EVENTS event0_ where (event0_.title like 'BUY%') and (exists (select 1 from PERSON_EVENT personeven1_ where personeven1_.EVENT_ID=EVENTS.EVENT_ID))
> 15:33:29,984  WARN JDBCExceptionReporter:71 - SQL Error: 904, SQLState: 42000
> 15:33:29,984 ERROR JDBCExceptionReporter:72 - ORA-00904: "EVENTS"."EVENT_ID": 无效的？？符
> Is there any wrong with the hql,or it's alias?
> Thanks to anyone who pay attetion to this trivial question which I search the forum and google without solutions.
> Or if I missed them,I would be grateful that you might tell me where can I find the right answer to fix this hql grammar error.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira