MYSQL "not equal to" on enum

Question

I created a table with an enum column as follow

create table test_1(
    id BIGINT NOT NULL AUTO_INCREMENT,
    order_billing_status ENUM ("BILLING_IN_PROGRESS") DEFAULT NULL
);

I insert two values as follow

+-----+----------------------+
| id  | order_billing_status |
+-----+----------------------+
| 100 | NULL                 |
| 200 | BILLING_IN_PROGRESS  |
+-----+----------------------+

Now when I try to query like select * from test_1 where order_billing_status <> "BILLING_IN_PROGRESS";, it is returning empty result, instead of returning the following.

+-----+----------------------+
| id  | order_billing_status |
+-----+----------------------+
| 100 | NULL                 |
+-----+----------------------+

Is this a bug in mysql or am I doing something wrong? If it's a bug, is there a work around or should I just use varchar instead of enum?

Answer 1

For the NULL value check we need to use the IS NULL or IS NOT NULL

= or <> ignores the NULL

select * from  test_1 
where order_billing_status <> "BILLING_IN_PROGRESS"  OR order_billing_status IS NULL

Answer 2

Try this instead:

select * from  test_1 
where order_billing_status<>'BILLING_IN_PROGRESS'
or order_billing_status is null;