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

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


select count(id) 
from mytable 
  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?

  • 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, 2018 at 11:19
  • Are you sure the 1st query returns more results that the 2nd? I would expect the opposite. Sep 26, 2018 at 12:52
  • Those two queries are not doing the same thing
    – user1822
    Sep 26, 2018 at 12:54
  • I triple-checked. The first query is returning more results than the second! Sep 26, 2018 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, 2018 at 13:04

2 Answers 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,'')
  • 100% absolutely correct. NULL strikes again! Sep 26, 2018 at 13:01

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 and acknowledge you have read our privacy policy.

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