0

Is there any reason why the following queries would return different results?

select count(id) 
from mytable 
where 
  status='active' 
  and ((textfield1 ilike '%needle%') or (textfield2 ilike '%needle%'));

vs

select count(id) 
from mytable 
where 
  status='active' 
  and ((textfield1 || textfield2) ilike '%needle%');

When I'm running these queries, the former is returning 26 and the latter 19.

What idiotic mistake am I making?

5
  • 1
    (('aaa' ilike '%ab%') or ('bbb' ilike '%ab%')) == false and (('aaa' || 'bbb') ilike '%ab%') == true. Use something like (textfield1 || CHAR(0) || textfield2).
    – Akina
    Sep 26 '18 at 11:19
  • Are you sure the 1st query returns more results that the 2nd? I would expect the opposite. Sep 26 '18 at 12:52
  • Those two queries are not doing the same thing Sep 26 '18 at 12:54
  • I triple-checked. The first query is returning more results than the second! Sep 26 '18 at 13:00
  • That could happen if there are nulls. If you use SELECT textfield1, textfield2 instead of SELECT count(id), you can see which rows differ between the 2 queries. Sep 26 '18 at 13:04
2

Do you have null values? If yes, the second form will be evaluated as null.

For example:

NULL || 'needle' = NULL

This way the second form can have less values than first one.

Try this:

coalesce(textfield1 ,'') || coalesce(textfield2,'')
1
  • 100% absolutely correct. NULL strikes again! Sep 26 '18 at 13:01
1

Yes, the reason is that the queries are not logically equivalent.

Just put textfield1 = 'I need' and or textfield2 = 'lemons'.

Then (textfield1 ilike '%needle%') or (textfield2 ilike '%needle%') evaluates to false
but textfield1 || textfield2 = 'I needlemons'
so (textfield1 || textfield2) ilike '%needle%' evaluates to true.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.