Unable to load entities with composite key using HSQLDB

batmat · **Posted:** Fri Dec 02, 2011 10:15 am

Hi,

I used Hibernate Search in a simple case. This works fine running inside JBoss/Oracle, but it fails using Arquillian/HSQLDB.

After analysis, I see the lucene part of the process works fine: the keys are found in the index, it only fails when trying to send SQL to the HSQLDB DB.
Actually, the entities being loaded have a composite id (for which I wrote FieldBridge).

HSQLDB isn't happy with the in part containing composite keys:

Code:

Caused by: java.sql.SQLException: Unexpected token: , in statement [select this_.COD_ACTE as COD1_14_5_, this_.DT_MODIF as DT2_14_5_, this_.DT_ARRETE as DT3_14_5_, this_.DT_CREATIO as DT4_14_5_, this_.DT_EFFET as DT5_14_5_, this_.DT_FIN as DT6_14_5_, this_.DT_JO as DT7_14_5_, this_.ENTENTE as ENTENTE14_5_, this_.NOM_COURT as NOM9_14_5_, this_.NOM_LONG as NOM10_14_5_, this_.MFIC_PLACE as MFIC11_14_5_, this_.SEXE as SEXE14_5_, this_.TYPE_COD as TYPE13_14_5_, this_.REMBOU_COD as REMBOU14_14_5_, this_.MENU_COD as MENU15_14_5_, this_.FRAIDP_COD as FRAIDP16_14_5_, typeactecc6_.COD_TYPE as COD1_16_0_, typeactecc6_.LIBELLE as LIBELLE16_0_, activitecc1_.ACTE_COD as ACTE6_7_, activitecc1_.DT_MODIF as DT2_7_, activitecc1_.COD_AA as COD1_7_, activitecc1_.COD_AA as COD1_0_1_, activitecc1_.DT_MODIF as DT2_0_1_, activitecc1_.CATMED_COD as CATMED3_0_1_, activitecc1_.ACTIV_COD as ACTIV4_0_1_, activitecc1_.REGROU_COD as REGROU5_0_1_, phaseccam2_.AA_COD as AA9_8_, phaseccam2_.DT_MODIF as DT2_8_, phaseccam2_.COD_AAP as COD1_8_, phaseccam2_.COD_AAP as COD1_2_2_, phaseccam2_.DT_MODIF as DT2_2_2_, phaseccam2_.AGE_MAX as AGE3_2_2_, phaseccam2_.AGE_MIN as AGE4_2_2_, phaseccam2_.CLAS as CLAS2_2_, phaseccam2_.ICR as ICR2_2_, phaseccam2_.PU_BASE as PU7_2_2_, phaseccam2_.PHASE_COD as PHASE8_2_2_, regroupeme3_.COD_REGROU as COD1_3_3_, regroupeme3_.LIBELLE as LIBELLE3_3_, arborescen4_.COD_MENU as COD1_13_4_, arborescen4_.COD_PERE as COD2_13_4_, arborescen4_.LIBELLE as LIBELLE13_4_, arborescen4_.RANG as RANG13_4_ from R_ACTE this_ inner join R_TYPE typeactecc6_ on this_.TYPE_COD=typeactecc6_.COD_TYPE left outer join R_ACTE_IVITE activitecc1_ on this_.COD_ACTE=activitecc1_.ACTE_COD and this_.DT_MODIF=activitecc1_.DT_MODIF left outer join R_ACTE_IVITE_PHASE phaseccam2_ on activitecc1_.COD_AA=phaseccam2_.AA_COD and activitecc1_.DT_MODIF=phaseccam2_.DT_MODIF left outer join R_REGROUPEMENT regroupeme3_ on activitecc1_.REGROU_COD=regroupeme3_.COD_REGROU left outer join R_MENU arborescen4_ on this_.MENU_COD=arborescen4_.COD_MENU where ((this_.COD_ACTE, this_.DT_MODIF) in ((?, ?)))]
   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 org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:250)
   at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:250)
   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:1577)
   at org.hibernate.loader.Loader.doQuery(Loader.java:696)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
   at org.hibernate.loader.Loader.doList(Loader.java:2232)
   ... 74 more

I thought I could try and patch the dialect or so, but didn't find the right way yet.
Do you have any idea/advice of where I should look into?

(Please note : as I'm running my code inside JBoss EAP 5.x, I wrote my POC using Hibernate Search 3.1.1.GA.)

Thanks a lot.

hardy.ferentschik · **Posted:** Tue Dec 06, 2011 4:58 am

Hi,

you really should provide the code for your annotated entity and the custom bridge and the search you are executing.
My biggest recommendation, however, would be to update Hibernate Core + Search. So much has changed since 3.1.1.GA that it
will be hard to give any version specific advice.

--Hardy

batmat · **Posted:** Tue Dec 06, 2011 5:30 am

Hi,

Yup, you're right, I'm gonna provide really soon a sample project to show the problem. Should have done that first, sorry.
I would also have submitted this to JIRA, but I wanted to make kind of sure I wasn't misusing HSearch.

About your point upgrading hsearch: as I said, my code must work and stays supported by RedHat/JBoss for the JBoss EAP 5.1.x platform. And on this one, http://www.jboss.com/products/platforms ... s/#JEAP5-0, the version is 3.1.1.GA_CP04. That's why I didn't use Hsearch 4.0 for this POC.

What do you think?

Cheers

batmat · **Posted:** Wed Dec 21, 2011 7:22 am

For the record, I've created the JIRA issue + the testcase project (hosted in a dedicated github repository).

https://hibernate.onjira.com/browse/HSEARCH-1011

hardy.ferentschik · **Posted:** Wed Dec 21, 2011 7:30 am

Thanks . We will look into it

hardy.ferentschik · **Posted:** Wed Dec 21, 2011 7:37 am

That said, I just had another idea. If I understand correctly your problem is just in the test environment. For production you use Oracle and for testing you want a fast in memory db. If HSQLDB is causing you grief why do you not use H2 instead. Hibernate Core and Search switched for quite some time to H2 as the default in memory database for testing.

If this works you don't have to wait for us investigating the problem.

--Hardy

batmat · **Posted:** Wed Jan 11, 2012 10:57 am

For the record, and as explained in the reported bug, upgrading to hsqldb 2.2.6 actually fixes the issue.
Migrating from 1.8 directly to 2.2.6 didn't cause any code to be modified anywhere here.

So that's great, we don't any problem anymore.

Thanks a lot.