title: MySql select on fields containing null values
body: <p>In our company we moved our web application (LAMP) from one server (Ubuntu 10.04) to a new server (Ubuntu 12.04.2). Now we encountered a strange behavior I haven't seen before and I really do not know where to begin. Maybe someone can give me hint.</p>
  
  <p>We have got the following simple table:</p>
  
  <pre><code>id      data1       data2       data3
  (int)   (varchar)   (int)       (int)
  -------------------------------------
  1       (empty)     123         456
  2       (null)      321         654
  3       abc         555         666
  </code></pre>
  
  <p>(empty) means the field contains a empty string. (null) means that the field is null. Now we use the following very very simple query:</p>
  
  <pre><code>SELECT * FROM `table` WHERE `data1` != 'abc';
  </code></pre>
  
  <p>On our old server the query returned the lines with the ids 1 and 2 which, I guess, is absolutely correct since <code>!='abc'</code> matches those two recordsets.</p>
  
  <p>On our new server the query only returns the recordset with the id 1. Recordsets containing null in the select fields are suddenly ignored by the query somehow.</p>
  
  <p>Just to make it more clear: I know that <code>IS NULL</code> could be used, but that would result in checking all queries and tables in the application matching this situation.</p>
  
  <p>Now the questions are:</p>
  
  <p>Did we had luck on our old server that the query behaved as expected by returning lines 1 and 2 or does the new server behave correct by returning only line 1?</p>
  
  <p>Generally: Should <code>!='abc'</code> match the recordsets 1 and 2 or should it only match id 1?</p>
  
  <p>Is it possible that there is a setting in the mysql configuration that controlls that behaviour? I am a little stuck with that. Every help is appreciated!</p>
  
  <p>Thanks in advance...</p>