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:
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)
... 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.