title: Informix SQL - Simple select query is returning unexpected results
body: <p>Follow this, it's really obvious and simple.  For some reason, the results differ between queries and approach angles on data subsets.  Keep in mind that the field, <code>correct_addr</code> is a <code>char(1)</code> field with allowable nulls.</p>
  
  <pre><code>select distinct correct_addr, count(*) from id_rec group by correct_addr;
  
  correct_addr       (count(*))
                           2477
  N                          80
  Y                       84013
  
  3 row(s) retrieved.
  </code></pre>
  
  <p>Ok, so <code>correct_addr</code> contains 3 distinct values: "N","Y", and either "" or " " or <code>NULL</code></p>
  
  <p>So now, I try this:</p>
  
  <pre><code>select count(*) from id_rec where correct_addr &lt;&gt; 'N';
  
        (count(*))
             84013
  </code></pre>
  
  <p>What happened to the 2477 records that have that blank value?</p>
  
  <p>Another try from a different angle:</p>
  
  <pre><code>select count(*) from id_rec where correct_addr in (null,'',' ','Y');
  
        (count(*))
             84013
  </code></pre>
  
  <p>Same thing happens....</p>
  
  <p>So what is going on here?  Why doesn't the sql engine (?) recognize the blank value in the last 2 sql statements?  It discovers it fine in the first query with the grouping, but nowhere else.</p>
  
  <p>Does anyone have any ideas as to why this type of thing happens?</p>