These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 
  Previous topic | Next topic 
Author Message
pouniok
 Post subject: Hibernate adds filter to my HQL query when converting to SQL
PostPosted: Fri Jun 13, 2014 11:19 am 
Newbie

Joined: Fri Jun 13, 2014 11:06 am
Posts: 4
Hello everyone,


We use hibernate in our web application since 2009, and we decided to update our version from 3.2.x to 4.2.13, and we are confronted to a strange behavior.

We do an HQL query, with only one table, and multiple left joins. And when hibernate do the query, it adds a new table after the left joins, and add a filter in the where clause !

Here is the HQL Query :

Code:
FROM OtProd op 
LEFT JOIN op.prodMvtEntete entete 
LEFT JOIN entete.paramsByTransporteurId trans 
LEFT JOIN entete.paramsByPerso1 perso1 
LEFT JOIN entete.paramDossier dossierMvt 
LEFT JOIN entete.paramProcessus processusMvt 
LEFT JOIN entete.prodPartenaire fournisseur 
LEFT JOIN entete.paramTypemvt typemvt 
LEFT JOIN entete.paramTypestatut statutMvt 

LEFT JOIN op.otOt ot 
LEFT JOIN ot.codetravCodetravail ct 
LEFT JOIN ot.paramDossier dossierOt 
LEFT JOIN ct.paramProcessus processusOt 
LEFT JOIN ot.paramParamsByStatut statutOT 

LEFT JOIN op.prodProduit produit 
LEFT JOIN produit.paramsByCategorie categorie 
LEFT JOIN produit.paramsByFormat format 
LEFT JOIN produit.parParamAppellation appellation 
LEFT JOIN produit.paramsByPerso1 paramsByPerso1 
LEFT JOIN produit.paramsByPerso2 paramsByPerso2 
LEFT JOIN produit.millesime paramMillesime 
LEFT JOIN op.prodLot lot 
LEFT JOIN op.paramsByCondiId condi 
LEFT JOIN op.prodEmplac emplac 

WHERE op.stock = true
AND op.sens != 0 
AND (dossierMvt IS NULL OR dossierMvt.id IN (13))
AND (dossierOt IS NULL OR dossierOt.id IN (13))


And here is the SQL query (in red the problems) :
Code:
select [...]
from p2w.ot_prod otprod0_
[color=#40BF00]left outer join p2w.prod_mvt_entete prodmvtent1_ on otprod0_.mvt_entete_id=prodmvtent1_.id [/color]
left outer join p2w.params params2_ on prodmvtent1_.transporteur_id=params2_.id
left outer join p2w.params params3_ on prodmvtent1_.perso1=params3_.id
left outer join p2w.param_dossier paramdossi4_ on prodmvtent1_.dossier_id=paramdossi4_.id
left outer join p2w.param_processus paramproce5_ on prodmvtent1_.type_processus_id=paramproce5_.id
left outer join p2w.prod_partenaire prodparten6_ on prodmvtent1_.partenaire_id=prodparten6_.id
left outer join p2w.param_typemvt paramtypem7_ on prodmvtent1_.type=paramtypem7_.id
left outer join p2w.param_typestatut paramtypes8_ on prodmvtent1_.statut_id=paramtypes8_.id
left outer join p2w.ot_ot otot9_ on otprod0_.ot_id=otot9_.id
left outer join p2w.codetrav_codetravail codetravco10_ on otot9_.code_travail_id=codetravco10_.id
left outer join p2w.param_processus paramproce12_ on codetravco10_.type_processus_id=paramproce12_.id
left outer join p2w.param_dossier paramdossi11_ on otot9_.dossier_id=paramdossi11_.id
left outer join p2w.param_params paramparam13_ on otot9_.statut=paramparam13_.id
left outer join p2w.prod_produit prodprodui14_ on otprod0_.produit_id=prodprodui14_.id
left outer join p2w.params params15_ on prodprodui14_.categorie=params15_.id
left outer join p2w.params params16_ on prodprodui14_.format=params16_.id
left outer join p2w.par_param_appellation parparamap17_ on prodprodui14_.appellation_id=parparamap17_.id
left outer join p2w.params params18_ on prodprodui14_.perso1=params18_.id
left outer join p2w.params params19_ on prodprodui14_.perso2=params19_.id
left outer join p2w.param_millesime parammille20_ on prodprodui14_.millesime_id=parammille20_.id
left outer join p2w.prod_lot prodlot21_ on otprod0_.prod_lot_id=prodlot21_.id
left outer join p2w.params params22_ on otprod0_.condi_id=params22_.id
left outer join p2w.prod_emplac prodemplac23_ on otprod0_.prod_emplac_id=prodemplac23_.id[color=#FF0000],
p2w.prod_mvt_entete prodmvtent24_ [/color]

where [color=#FF0000]otprod0_.mvt_entete_id=prodmvtent24_.id[/color]
and otprod0_.stock=true
and otprod0_.sens<>0
and (paramdossi4_.id is null or paramdossi4_.id in (13)) and (paramdossi11_.id is null or paramdossi11_.id in (13)


I highlighted in green the table that is added, to prove its already in my query, and I don't know why this add it to the end of the query.

For information, I do the same query to have the total row count, and it gives me the right number. Could it be linked to the field I select in the query ?

Did I miss something during the migration ?


Thank's for any help,

Pou.
Top
 Profile  
 
pouniok
 Post subject: Re: Hibernate adds filter to my HQL query when converting to SQL
PostPosted: Mon Jun 16, 2014 4:21 am 
Newbie

Joined: Fri Jun 13, 2014 11:06 am
Posts: 4
This is really blocking us, and as we now use PostGIS, we need Hibernate 4+ to have the Hibernate-spatial extension working...

I can give more precise code if needed, I'll be glad of any help ;)
Top
 Profile  
 
DavideD
 Post subject: Re: Hibernate adds filter to my HQL query when converting to SQL
PostPosted: Mon Jun 16, 2014 5:38 am 
Hibernate Team
Hibernate Team

Joined: Fri Sep 09, 2011 3:18 am
Posts: 295
Hi,
that's odd.

Could you add some details about the entities mapping? Mainly the one that are causing the problem.

If you could create a test to replicate the error that would be awesome.

Thanks,
Davide
Top
 Profile  
 
pouniok
 Post subject: Re: Hibernate adds filter to my HQL query when converting to SQL
PostPosted: Mon Jun 16, 2014 9:17 am 
Newbie

Joined: Fri Jun 13, 2014 11:06 am
Posts: 4
Hi Davide,


Thanks for the reply.


I'm not really sure it's linked to the entities mapping, after some work on my query, it looks like it's happing only when I have "CASE WHEN .. END" in the SELECT part.

Here is the simplified HQL query :

Code:
SELECT product.nom as "Product",
           CASE WHEN op.prodMvtEntete IS NULL
                      THEN 'No movement'
                      ELSE typemvt.nom
           END as "Movement",
           op.id as "ID"
FROM OtProd op
LEFT JOIN op.prodMvtEntete entete
LEFT JOIN entete.paramTypemvt typemvt
LEFT JOIN op.prodProduit product          


Here is the generated SQL query NOT WORKING :

Code:
SELECT product.nom AS "Product",
       CASE
         WHEN otprod.mvt_entete_id IS NULL THEN 'No movement'
         ELSE type_movement.nom
       END              AS "Movement",
       otprod.id      AS "ID"
FROM   ot_prod otprod
       LEFT OUTER JOIN prod_mvt_entete movement ON otprod.mvt_entete_id = movement.id
       LEFT OUTER JOIN param_typemvt type_movement ON movement.type = type_movement.id
       LEFT OUTER JOIN prod_produit product ON otprod.produit_id = product.id,
       prod_mvt_entete prodmvtent4_
WHERE  otprod.mvt_entete_id = prodmvtent4_.id



Here is the generated SQL query WORKING when CASE is removed from SELECT clause :

Code:
SELECT product.nom AS "Product",
           otprod.id      AS "ID"
FROM   ot_prod otprod
       LEFT OUTER JOIN prod_mvt_entete movement ON otprod.mvt_entete_id = movement.id
       LEFT OUTER JOIN param_typemvt type_movement ON movement.type = type_movement.id
       LEFT OUTER JOIN prod_produit product ON otprod.produit_id = product.id
Top
 Profile  
 
pouniok
 Post subject: Re: Hibernate adds filter to my HQL query when converting to SQL
PostPosted: Mon Jun 16, 2014 9:31 am 
Newbie

Joined: Fri Jun 13, 2014 11:06 am
Posts: 4
Just found the solution :

Now with Hibernate 4.x.x, I have to add the column name before testing object relation is null.

So, I replaced :

Code:
CASE WHEN op.prodMvtEntete IS NULL
     THEN 'No movement'
     ELSE typemvt.nom
END


By :

Code:
CASE WHEN op.prodMvtEntete.id IS NULL
     THEN 'No movement'
     ELSE typemvt.nom
END



I have to check my entire code to fix every possible occurence and update the HQL.

Is that a normal behavior by the way ?
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.