Play 1.1 Beta 2 Unexpected token: CROSS in statement

[Cheong Chung Onn]

Hi,

I have realized after upgrading to Play 1.1, all my queries with join
statement changes from an inner join to a cross join. I am able to fix
this in all my queries except classes that uses CRUD. Is there any way
of specifying the type of join in CRUD in order for me to work around
this problem? Below is the error I received. I am using Play 1.1 beta 2,
HSQLDialect

Internal Server Error (500) for request GET /admin/commsaliases

Execution exception (In {module:crud}/app/controllers/CRUD.java around
line 252)
PersistenceException occured :
org.hibernate.exception.SQLGrammarException: could not execute query

play.exceptions.JavaExecutionException:
org.hibernate.exception.SQLGrammarException: could not execute query
at play.mvc.ActionInvoker.invoke(ActionInvoker.java:285)
at Invocation.HTTP Request(Play!)
Caused by: javax.persistence.PersistenceException:
org.hibernate.exception.SQLGrammarException: could not execute query
at
org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1235)
at
org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1168)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:250)
at play.db.jpa.JPAPlugin$JPAModelLoader.fetch(JPAPlugin.java:422)
at controllers.CRUD$ObjectType.findPage(CRUD.java:252)
at controllers.comms.CommsAliases.list(CommsAliases.java:27)
at
play.mvc.ActionInvoker.invokeControllerMethod(ActionInvoker.java:408)
at
play.mvc.ActionInvoker.invokeControllerMethod(ActionInvoker.java:403)
at play.mvc.ActionInvoker.invoke(ActionInvoker.java:176)
... 1 more
Caused by: org.hibernate.exception.SQLGrammarException: could not
execute query
at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2297)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2172)
at org.hibernate.loader.Loader.list(Loader.java:2167)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:448)
at
org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
at
org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1258)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:241)
... 7 more
Caused by: java.sql.SQLException: Unexpected token: CROSS in statement
[select top ? commsalias0_.id as id1_, commsalias0_.name as name1_,
commsalias0_.user_id as user3_1_ from COMMS_Alias commsalias0_ cross
join RBAC_Member user1_ where commsalias0_.user_id=user1_.id and
user1_.name='sgiadmin' order by commsalias0_.name ASC]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at
com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:213)
at
org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
at
org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452)
at
org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1596)
at org.hibernate.loader.Loader.doQuery(Loader.java:717)
at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
at org.hibernate.loader.Loader.doList(Loader.java:2294)
... 15 more

[Guillaume Bort]

I don't know. In Play 1.1 we use Hibernate 3.5. It is probably the new
default in this version of Hibernate...

Which database are you using?

> --
> You received this message because you are subscribed to the Google Groups
> "play-framework" group.
> To post to this group, send email to play-fr...@googlegroups.com.
> To unsubscribe from this group, send email to
> play-framewor...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/play-framework?hl=en.
>
>

--
Guillaume Bort, http://guillaume.bort.fr

For anything work-related, use g...@zenexity.fr; for everything else,
write guillau...@gmail.com

[chungonn]

I am using HyperSonic that is bundle with Play 1.1.

My google search shows that there is a bug reported against Hibernate
3.5.0 for Db2 http://opensource.atlassian.com/projects/hibernate/browse/HHH-5168.

Is there anyway to get CRUD to do an inner join on a specific field?
Will Play 1.1 use a later version of Hibernate e.g. 3.5.5 when
released?

Btw, after migrated my app from 1.0.3 to 1.1, there is a very
noticeable degradation in performance. The environment, both hardware
and software being the same. All my colleagues had the same
observation. The most significant performance degradation is when the
application is first invoked via "play test" and subsequent
incremental re-compilation during code changes.

> Guillaume Bort,http://guillaume.bort.fr

>
> For anything work-related, use g...@zenexity.fr; for everything else,

> write guillaume.b...@gmail.com

[chungonn]

I upgrade HSQLDB from 1.8.1.2 to 2.0.0 and it seems ok though.

On Sep 14, 10:20 pm, chungonn <chung...@gmail.com> wrote:
> I am using HyperSonic that is bundle with Play 1.1.
>
> My google search shows that there is a bug reported against Hibernate

> 3.5.0 for Db2http://opensource.atlassian.com/projects/hibernate/browse/HHH-5168.

[Guillaume Bort]

Ok thank you. I'll update both hibernate and hsqldb.

[chungonn]

THANK YOU!

Cheers!

On Sep 15, 12:22 am, Guillaume Bort <guillaume.b...@gmail.com> wrote:
> Ok thank you. I'll update both hibernate and hsqldb.
>

[Guillaume Bort]

Well I've tried to update hsqldb to 2.0.0, but some test cases of the
framework test suite that don't pass anymore.

This version don't seem very stable yet (even if it is marked as
stable by the hsqldb team I've noticed several strange things).

Btw I'm not sure to have understood exactly what your issue is?

--
Guillaume Bort, http://guillaume.bort.fr

For anything work-related, use g...@zenexity.fr; for everything else,

write guillau...@gmail.com

[chungonn]

Hi Guillaume,

Here is my issue - I migrate my app from Play 1.0.3 to 1.1 Beta2, and
all my HQL query starts to break. I found the reason is because
Hibernate (Play 1.1 Beta2) will automatically add the token "CROSS" to
all the implicit joins. I solved this by changing all my HQL from an
implicit join to an explicit join. The only problem left is I need to
get CRUD to make an explicit join. So I was asking you previously, is
there a way i can get CRUD to specify explicit join.

I think that can be fixed in Hibernate's HSQLDialect but I didn't have
the time to investigate. Btw, for all these and other reasons, I am
now staying with Play 1.0.3 now.

Please let me know if you need any further clarification.

On Sep 16, 9:37 pm, Guillaume Bort <guillaume.b...@gmail.com> wrote:
> Well I've tried to update hsqldb to 2.0.0, but some test cases of the
> framework test suite that don't pass anymore.
>
> This version don't seem very stable yet (even if it is marked as
> stable by the hsqldb team I've noticed several strange things).
>
> Btw I'm not sure to have understood exactly what your issue is?
>

> > For more options, visit this group athttp://groups.google.com/group/play-framework?hl=en.

[Guillaume Bort]

> Here is my issue - I migrate my app from Play 1.0.3 to 1.1 Beta2, and
> all my HQL query starts to break. I found the reason is because
> Hibernate (Play 1.1 Beta2) will automatically add the token "CROSS" to
> all the implicit joins.

Ok but is it a problem?

All the framework test cases pass and they all use HSQL.

--
Guillaume Bort, http://guillaume.bort.fr

For anything work-related, use g...@zenexity.fr; for everything else,

write guillau...@gmail.com

[chungonn]

Hmm.. I checked it is because HSQL 1.8.x does not support CROSS token.
I will check my setup again.

Thanks.

On Sep 17, 4:24 pm, Guillaume Bort <guillaume.b...@gmail.com> wrote:
> > Here is my issue - I migrate my app from Play 1.0.3 to 1.1 Beta2, and
> > all my HQL query starts to break. I found the reason is because
> > Hibernate (Play 1.1 Beta2) will automatically add the token "CROSS" to
> > all the implicit joins.
>
> Ok but is it a problem?
>
> All the framework test cases pass and they all use HSQL.
>

[chungonn]

Sorry for getting back so late, I found the source of the problem. It
was because I was using an older HSQL version, specifically 1.8.0.1,
when I upgraded to 1.8.0.10 the problem disappears.

Apologize for the raising a false alarm.

On Sep 17, 4:24 pm, Guillaume Bort <guillaume.b...@gmail.com> wrote:
> > Here is my issue - I migrate my app from Play 1.0.3 to 1.1 Beta2, and
> > all my HQL query starts to break. I found the reason is because
> > Hibernate (Play 1.1 Beta2) will automatically add the token "CROSS" to
> > all the implicit joins.
>
> Ok but is it a problem?
>
> All the framework test cases pass and they all use HSQL.
>

> On Fri, Sep 17, 2010 at 2:42 AM,chungonn<chung...@gmail.com> wrote:
> > Hi Guillaume,
>
> > Here is my issue - I migrate my app from Play 1.0.3 to 1.1 Beta2, and
> > all my HQL query starts to break. I found the reason is because
> > Hibernate (Play 1.1 Beta2) will automatically add the token "CROSS" to
> > all the implicit joins. I solved this by changing all my HQL from an
> > implicit join to an explicit join. The only problem left is I need to
> > get CRUD to make an explicit join. So I was asking you previously, is
> > there a way i can get CRUD to specify explicit join.
>
> > I think that can be fixed in Hibernate's HSQLDialect but I didn't have
> > the time to investigate. Btw, for all these  and other reasons, I am
> > now staying with Play 1.0.3 now.
>
> > Please let me know if you need any further clarification.
>
> > On Sep 16, 9:37 pm, Guillaume Bort <guillaume.b...@gmail.com> wrote:
> >> Well I've tried to update hsqldb to 2.0.0, but some test cases of the
> >> framework test suite that don't pass anymore.
>
> >> This version don't seem very stable yet (even if it is marked as
> >> stable by the hsqldb team I've noticed several strange things).
>
> >> Btw I'm not sure to have understood exactly what your issue is?
>

> >> On Wed, Sep 15, 2010 at 6:19 AM,chungonn<chung...@gmail.com> wrote:
> >> > THANK YOU!
>
> >> > Cheers!
>
> >> > On Sep 15, 12:22 am, Guillaume Bort <guillaume.b...@gmail.com> wrote:
> >> >> Ok thank you. I'll update both hibernate and hsqldb.
>

> >> >> Le 14 sept. 2010 à 18:16,chungonn<chung...@gmail.com> a écrit :
>
> >> >> > I upgrade HSQLDB from 1.8.1.2 to 2.0.0 and it seems ok though.
>

> ...
>
> read more »