CODEWITHSUNDEEP
mysqlsqlselectnullmariadb
ruipacheco
ruipacheco

Reputation: 16442

Nullable column not behaving as such

I have a table actor with a nullable column first_name. I set a specific row to null:

update actor set first_name=null where actor_id=201;
Query OK, 1 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

I then try to query that table using the null value in my where clause and get nothing back:

select * from actor where first_name is null;
Empty set (0.00 sec)

But if I select checking if that column is an empty string, I get the right value back:

select * from actor where first_name = '';
[...]
1 row in set (0.00 sec)

Storage engine is MyISAM. Why is a nulled column being set to empty string?

Update

I see the 1 warning but mysql CLI does not show any warnings. How can I make it do so?

Also:

desc actor;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                 | Null | Key | Default           | Extra                       |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| actor_id    | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| first_name  | varchar(45)          | NO   |     | NULL              |                             |
| last_name   | varchar(45)          | NO   | MUL | NULL              |                             |
| last_update | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+

Upvotes: 0

Views: 44

Answers (1)

SQL.RK
SQL.RK

Reputation: 157

From the Table description, looks like all columns including first_name are not null. Are you sure it is nullable column?

Upvotes: 2

Related Questions