Bug #2355 NOT ( Field_With_Nulls LIKE '%foo%' ) does not work intuitively

• Submitted: 12 Jan 2004 8:29
• Modified: 12 Jan 2004 9:30
• Reporter: James Sleeman
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• CPU Architecture: Any

[12 Jan 2004 8:29] James Sleeman

Description:
When selecting using a LIKE condition on a field with NULLS, negating by wrapping in NOT () and indeed using "NOT LIKE" do not work intutively.  For example, in a sample table..

mysql> select count(*) from orders;
+----------+
| count(*) |
+----------+
|       37 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from orders where OrCompanyReg  like '%foo%';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

So there are 37 rows, none of them contain the string foo in OrCompanyReg, so you would imaging that the negation of that SQL condition should produce 37 rows.. but.

mysql> select count(*) from orders where NOT (OrCompanyReg  like '%foo%');
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

which had me scratching my head for quite some time to find that the reason is...

mysql> select count(*) from orders where OrCompanyReg IS NULL;
+----------+
| count(*) |
+----------+
|       33 |
+----------+
1 row in set (0.00 sec)

This is counter intuitive, as the explicit negation of some clause should be the exact opposite, shouldn't it?  Obviously the negation of NULL is NULL.

How to repeat:
create table foo ( id int primary key not null auto_increment, bar varchar(25) );
insert into foo (bar) values ( NULL );
insert into foo (bar) values ('zort');

select count(*) from foo; -- shows 2
select count(*) from foo where bar LIKE 'snarf'; -- shows 0
select count(*) from foo where NOT (bar like 'snarf'); -- expect 2, shows 1 because NOT ( NULL ) is NULL not TRUE

Suggested fix:
Add an option that makes NOT ( NULL ) return true, which would allow the above to work, this would be much more intuitive as then you can write 
"NOT ( field LIKE '%foo%' )" and have it actually mean that.

Perhaps more to the crux is that  "some_null_field LIKE '%foo%'" returns NULL when the field is null, instead of false, when clearly null is not LIKE '%foo%'.

[12 Jan 2004 9:30] MySQL Verification Team

This is not a bug.

When applied like you did it will return improper results.

Try with NOT LIME ...

[12 Jan 2004 12:17] James Sleeman

Fair `nuff, not a bug, but you must agree it's counter intuitive.   
 
Of course, this all stems back however to any operation on NULL returning NULL 
as defined in SQL92, some other servers (*cough* MS *cough*) have a setting 
(ANSI_NULLS OFF) that will instead effectively treat null in comparisons as false 
so that comparisons work more intuitively ( NOT(NULL LIKE 'f') is true ). 
 
It would be nice for MySQL to have a similar query-time option.  This should really 
be a feature request now I think of it some more.