ILIKE and NOT ILIKE in aws redshift different from total

Question

I ran three following queries in amazon redshift:

select count(*)  
from t1  

The count was 1554.

select count(*)  
from t1  
where  
    item_name ilike "blue"  

The count was 62.

select count(*)  
from t1  
where  
    item_name not ilike "blue"  

The count was 85.

The last two (62 + 85) should equal 1554. What am I missing?

Answer 1

Double-quotes are for identifiers: "myColumn"
Single quotes are for values: 'value'.

Your examples contradict those basic syntax rules.

Also, you did not consider NULL values, which neither qualify with:

item_name ilike 'blue'

nor with:

item_name not ilike 'blue'

What do you get for:

SELECT count(*)                             AS all_rows
     , count(item_name  ~~* 'blue' OR NULL) AS item_name_blue
     , count(item_name !~~* 'blue' OR NULL) AS item_name_not_blue
     , count(item_name)                     AS item_name_not_null
     , count(item_name IS NULL OR NULL)     AS item_name_null
FROM   t1;

~~* .. internal Postgres operator for ILIKE
!~~* .. internal Postgres operator for NOT ILIKE
(Careful: slightly different operator precedence.)