Better handling of not_equal operator

[julienmru]

I came across a use-case where not_equal does not produce the result I expected because of the following MySQL behavior: https://stackoverflow.com/questions/16186674/mysql-syntax-not-evaluating-not-equal-to-in-presence-of-null

Could you consider changing the way the not_equal operator is translated in MySQL? It should be (but I know this is a personal opinion) NOT column <=> value.

Full disclosure: I'd like to avoid my users create a query column IS NULL OR column != value.

Let me know what you think :) Or if it's unclear.

[timgws]

Thanks for bringing this up!

I have been thinking about this for a little bit, and one thing that I can't really get over is that this will work for not_equal and equal, but it doesn't really work for other operators, like <= or =>.

mysql> select 2=null;
+------------+
|     2=null |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec)

mysql> select 2<=>null;
+--------------+
|     2<=>null |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

It breaks down for <=.

mysql> select not 2<=null;
+-------------+
| not 2<=null |
+-------------+
|        NULL |
+-------------+

Where you would still need to add the IS NULL to the SQL query anyway. It doesn't seem right to me that we would change the query for some forms in the query builder, and not others.

The solution that I think might be best, but still don't feel extremely happy about is to add a function that would allow querying with the spaceship operator for equality, but when a greater than operator is used, that the NULL check is added to the query.

What is the purpose for avoiding your users performing a IS NULL OR query? It seems that the impact that this query would have is fairly minimal when indexes are placed on the columns that you are querying.