Open julienmru opened 2 years ago
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.
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-nullCould 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.