10:29 a.m.
A createQuery() caused ORA-00904 error
--------------------------------------
Key: HHH-2211
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2211
Project: Hibernate3
Type: Bug
Components: query-hql
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
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
10:30 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2211) A createQuery() caused ORA-00904 error
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2211?page=c...
]
Jitendra Singh commented on HHH-2211:
-------------------------------------
Hi Sodia,
Your query is not generating correct sequel. It is generating :
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
)
)
Look at the last line, it will not work as it is. If we modify the query to :
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,Event ev where p.id=ev.id)
then it generates:
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_,
EVENTS event2_
where
personeven1_.EVENT_ID=event2_.EVENT_ID
)
)
which I think is the right query.
Let me know if this is not what you are trying to do in the query.
Regards,
Jitendra
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
3:39 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-2211) A createQuery() caused ORA-00904 error
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2211?page=c...
]
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
5986
days inactive
6391
days old
hibernate-issues@lists.jboss.org
2 comments
2 participants
participants (2)
-
Jitendra Singh (JIRA) -
sodia (JIRA)