|EJBQL translaltion problem [message #383018]
||Wed, 12 November 2008 06:10
Registered: July 2009
I ran into pretty interesting problem using Toplink previosly, so it turned
out that Eclipselink does the same thing.
I have EJBQL query like this (taken from Java code):
"SELECT distinct i FROM NumberTestA i WHERE ( " +
"( ( ?1 = true ) OR i.acol <> ?2 ) AND " +
" ( ( ?3 is null ) OR i.str = ?4 ) " +
") ORDER BY i.acol";
parameters 1 and 3 are set as boolean, true in case that parameters 2 and 4
are null (checked before that in java code).
Anyway, this kind of query kind of confuses our persistence provider. It
gets translated into this:
SELECT DISTINCT t1.ID, t1.DTYPE, t1.NUM, t1.STR, t1.ACOL,
FROM NUMBER_TEST t0, NUMBER_TEST t1
WHERE (((((? = ?) OR (t0.ACOL <> ?))
AND ((? IS NULL) OR (t0.STR = ?)))
AND (t0.DTYPE = ?))
AND (t1.DTYPE = ?))
ORDER BY t0.ACOL ASC
bind => [false, true, X, true, z, NumberTestA, NumberTestA]
So i get cartesian on my table, without any join condition. In case of table
with 2000 rows... You get the picture.
It is easy for me to make workaround on this, I simply split query string
into separate conditions and put them, as well as setting parameters with
bunch of if conditions, checking whether passed param is null, bu this looks
like an issue, something that confuses both Toplink and Eclipselink, so I
thought I should point to this.