You are viewing a plain text version of this content. The canonical link for it is here.

Posted to users@openjpa.apache.org by Georg Nozicka <ge...@nozicka.at> on 2011/05/17 19:02:07 UTC

Criteria API; AND 1 = 1 in WHERE Clause

Hello, 

we are using OPENJPA 2.0.0 and have defined with the criteria api a query
which produces the following sql code (actually the sql uses more columns, I
tried to remove some of them to make the sql easier to read):

SELECT T0.OID, T0.X, T3.BEZOID, T3.OID, T3.GEMNR 
FROM TABLE0 T0 
INNER JOIN TABLE1 T1 ON T0.OID = T1.UOID 
INNER JOIN TABLE2 T2 ON T0.OID = T2.BEZOID 
LEFT OUTER JOIN TABLE2 T3 ON T0.OID = T3.BEZOID 
WHERE (T1.Y LIKE ? ESCAPE '\' AND T2.GEMNR LIKE ? ESCAPE '\' AND 1 = 1) 

[params=(String) abc%, (String) 801%]

The questions are
- why is at the end “AND 1 = 1” appended?
- is it possible to avoid somehow the "AND 1 = 1" and if, how?

Best Regards,
Georg


--
View this message in context: http://openjpa.208410.n2.nabble.com/Criteria-API-AND-1-1-in-WHERE-Clause-tp6373899p6373899.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Criteria API; AND 1 = 1 in WHERE Clause

Posted by Georg Nozicka <ge...@nozicka.at>.

Hello,

this are good news, that it could be fixed. So I guess I cannot do anything
different in coding the queries so that the "1 == 1" is not added to the
query. 

The point is, that we are evaluating using JPA in general and OpenJpa in
specific as a new way to handle our persistence. Our DB gurus are very picky
about, which SQL code finally is created for specific queries and the “1 ==
1” is for the moment a construct they don’t like because they mean that it
not clear, which side effects such constructs could have and how DB
optimizers handle it in various situations. Therefore I will create a Jira
and hope that it will get fixed.

Georg

--
View this message in context: http://openjpa.208410.n2.nabble.com/Criteria-API-AND-1-1-in-WHERE-Clause-tp6373899p6527779.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Criteria API; AND 1 = 1 in WHERE Clause

Posted by Pinaki Poddar <pp...@apache.org>.

Hi,
It could be fixed -- the query expression tree sees a expression node but
that expression is not used in the query filter and inserts a "1==1"
condition i.e the node is always satisfied.

But how important it is to be fixed in your view?
If you think it is, please create a JIRA.

-----
Pinaki 
--
View this message in context: http://openjpa.208410.n2.nabble.com/Criteria-API-AND-1-1-in-WHERE-Clause-tp6373899p6526360.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Criteria API; AND 1 = 1 in WHERE Clause

Posted by Georg Nozicka <ge...@nozicka.at>.

Hello,

thanks for the Nits ...

I've uploaded the three *_classes. Are they enough or do you also need some
others (which are not referenced in the posted code directly)?

http://openjpa.208410.n2.nabble.com/file/n6525827/Adresse_.java
Adresse_.java 
http://openjpa.208410.n2.nabble.com/file/n6525827/UnameHist_.java
UnameHist_.java 
http://openjpa.208410.n2.nabble.com/file/n6525827/UnternehmenUrv_.java
UnternehmenUrv_.java 

Georg

--
View this message in context: http://openjpa.208410.n2.nabble.com/Criteria-API-AND-1-1-in-WHERE-Clause-tp6373899p6525827.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Criteria API; AND 1 = 1 in WHERE Clause

Posted by Pinaki Poddar <pp...@apache.org>.

Hi,

   Please post UnternehmenUrv_ and other such *_ classes. I know where is
the 1 = 1 clause is introduced but can not figure out why exactly.
  
   Nits:
   1.    Join&lt;UnternehmenUrv, Adresse&gt; adresseJoin = untRoot.join(
                UnternehmenUrv_.adressen.getName(), JoinType.INNER); 

       A simpler (and slightly more effective) form would be to get rid of
.getName(). Actually by calling getName(), the code is loosing type-safety
and OpenJPA is working twice to restore it !

     Join&lt;UnternehmenUrv, Adresse&gt; adresseJoin = untRoot.join(
                UnternehmenUrv_.adressen, JoinType.INNER); 

   2. cq.where(cb.and(predicates.toArray(new Predicate[0]))); 

      A better form would be (would save JVM to instantiate an extra array
unnecessarily)

      cq.where(cb.and(predicates.toArray(new
Predicate[predicates.size()]))); 


-----
Pinaki 
--
View this message in context: http://openjpa.208410.n2.nabble.com/Criteria-API-AND-1-1-in-WHERE-Clause-tp6373899p6525633.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Criteria API; AND 1 = 1 in WHERE Clause

Posted by Georg Nozicka <ge...@nozicka.at>.

Hello,

I tried to reduce the sample as far as possible. First the code:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<UnternehmenUrv> cq = cb.createQuery(UnternehmenUrv.class);
Root<UnternehmenUrv> untRoot = cq.from(UnternehmenUrv.class);
cq.select(untRoot);

Join&lt;UnternehmenUrv, UnameHist&gt; unameHistJoin = untRoot.join(
  UnternehmenUrv_.unameHist.getName(), JoinType.INNER);

Join&lt;UnternehmenUrv, Adresse&gt; adresseJoin = untRoot.join(
  UnternehmenUrv_.adressen.getName(), JoinType.INNER);

List<Predicate> predicates = new ArrayList<Predicate>();

ParameterExpression<String> pe = cb.parameter(String.class,
  UnameHist_.unameL.getName());
predicates.add(cb.like(
  unameHistJoin.get(UnameHist_.unameL).as(String.class), pe));

ParameterExpression<String> peStr = cb.parameter(String.class,
  Adresse_.strL.getName());
predicates.add(cb.like(adresseJoin.get(Adresse_.strL).as(String.class),
  peStr));

cq.where(cb.and(predicates.toArray(new Predicate[0])));

TypedQuery<UnternehmenUrv> tq = em.createQuery(cq);

tq.setParameter(UnameHist_.unameL.getName(), "nozick%");
tq.setParameter(Adresse_.strL.getName(), "fadenw%");

return tq.getResultList();

The toString for the CriteriaQuery instance gives the following output:
SELECT u FROM UnternehmenUrv u INNER JOIN u.unameHist ?  INNER JOIN
u.adressen ?  WHERE (u.unameHist.unameL LIKE :unameL AND u.adressen.strL
LIKE :strL)

The jpa trace for the generated SQL is like this:
SELECT t0.OID, t0.UKOID, t0.UNAME, t0.UNTID FROM udbq.TUUNT_UNTERNEHMEN t0
INNER JOIN udbq.TUUNT_UNAME_HIST t1 ON t0.OID = t1.UOID INNER JOIN
udbq.TUUNT_ADRESSE t2 ON t0.OID = t2.BEZOID WHERE (t1.UNAME_L LIKE ? ESCAPE
'\' AND t2.STR_L LIKE ? ESCAPE '\' AND 1 = 1)  [params=?, ?]

Any ideas?

--
View this message in context: http://openjpa.208410.n2.nabble.com/Criteria-API-AND-1-1-in-WHERE-Clause-tp6373899p6525216.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Criteria API; AND 1 = 1 in WHERE Clause

Posted by Pinaki Poddar <pp...@apache.org>.

Hello,
  1. call toString() on the CriteriaQuery instance you are executing. And
post the string.
  2. If possible, post the code that builds the CriteriaQuery

OpenJPA runtime represents an empty expression node in the query expression
tree as 1 = 1 or 1 <> 1 for conjunction or disjunction. Seeing the query
expression tree will help us to know how an empty expression node is getting
inserted.


-----
Pinaki 
--
View this message in context: http://openjpa.208410.n2.nabble.com/Criteria-API-AND-1-1-in-WHERE-Clause-tp6373899p6386782.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Criteria API; AND 1 = 1 in WHERE Clause

Posted by Kevin Sutter <kw...@gmail.com>.

Hi Georg,
When creating dynamic SQL, it's a common practice to use the "1 = 1" in the
WHERE clause to know that there is always at least one conditional.  This
way, you can automatically use the "AND" with the other conditions since the
"1 = 1" always evaluates to True.  Normally, this "1 = 1" gets optimized out
by the SQL processor on the database side, so it shouldn't be hurting
anything.

Another common usage is to force a result set to be returned.  If the only
condition that evaluates to True is "1 = 1", then an empty result set is
returned instead of a null or an exception (depending on the query).

Are you finding an issue with this "1 = 1" clause, or are you just curious
why it's there?  The above explanations are just in general.  I have not dug
into the specific case you outline below, but my guess it's related to the
first explanation above.

Kevin

On Tue, May 17, 2011 at 12:02 PM, Georg Nozicka <ge...@nozicka.at>wrote:

> Hello,
>
> we are using OPENJPA 2.0.0 and have defined with the criteria api a query
> which produces the following sql code (actually the sql uses more columns,
> I
> tried to remove some of them to make the sql easier to read):
>
> SELECT T0.OID, T0.X, T3.BEZOID, T3.OID, T3.GEMNR
> FROM TABLE0 T0
> INNER JOIN TABLE1 T1 ON T0.OID = T1.UOID
> INNER JOIN TABLE2 T2 ON T0.OID = T2.BEZOID
> LEFT OUTER JOIN TABLE2 T3 ON T0.OID = T3.BEZOID
> WHERE (T1.Y LIKE ? ESCAPE '\' AND T2.GEMNR LIKE ? ESCAPE '\' AND 1 = 1)
>
> [params=(String) abc%, (String) 801%]
>
> The questions are
> - why is at the end “AND 1 = 1” appended?
> - is it possible to avoid somehow the "AND 1 = 1" and if, how?
>
> Best Regards,
> Georg
>
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Criteria-API-AND-1-1-in-WHERE-Clause-tp6373899p6373899.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>