Different results for logically same, but textually different queries

Question

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?

Answer 1

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,'')

Answer 2

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.