I should have mentioned that my previous
blog posting was using MySQL
4.0(4.0.23_Debian-3ubuntu2-log). It seems that in 5.0.2 they changed the
precedence of the NOT operator to be lower than LIKE.
From the manual:
The precedence shown for NOT is as of MySQL 5.0.2. For
earlier versions, or from 5.0.2 on if the HIGH_NOT_PRECEDENCE SQL mode
is enabled, the precedence of NOT is the same as that of the !
operator.
Using 5.0 (5.0.22-Debian_0ubuntu6.06.8-log), and a slightly smaller
dataset, I get:
mysql> select count(*) from Table where blobid is null or not blobid like '%-%';
+----------+
| count(*) |
+----------+
| 199057 |
+----------+
1 row in set (3.26 sec)
mysql> select count(*) from Table where blobid is null or blobid not like '%-%';
+----------+
| count(*) |
+----------+
| 199057 |
+----------+
1 row in set (0.96 sec)
Jim Kingdon
experimented with other databases and was unable to reproduce this
problem. My test with PostgreSQL 8.3:
quux=> create table foo (blobid varchar(255));
CREATE TABLE
quux=> insert into foo (blobid) values
('5cd1237469cc4b52ca094e215156c582-9ef460ac4134c600a4d2382c4b0acee7'),
(NULL),
('d20cb4037f8f9ab1de5de264660f005c-2c34209dcfb39251cf7c16bb6754bbd2'),
('845a8d06719d8bad521455a8dd47745c-095d9a0831433c92cd269e14e717b3a9'),
('9580ed23f34dd68d35da82f7b2a293d6-bf39df7509d977a1de767340536ebe80'),
('06c9521472cdac02a2d4b2a18f8bec0f-0a8a28d3b63df54860055f1d1de92969'),
('ed3cd0dd9b55f76db7544eeb64f3cfa0-80a6a3eb6d73c0a58f88b7c332866d5c'),
(NULL),
('b339f6545651fbfa49fa500b7845c4ce-6defb5ffc188b8f72f1aa10bbd5c6bec'),
('642075963d6f69bb11c35a110dd07c2c8db54ac2d2accae7fa4a22db1d6caae9');
INSERT 0 10
quux=> select count(*) from foo
where blobid is null or blobid not like '%-%';
count
-------
3
(1 row)
quux=> select count(*) from foo
where blobid is null or not blobid like '%-%';
count
-------
3
(1 row)
quux=> select not blobid from foo limit 10;
ERROR: argument of NOT must be type boolean, not type character varying
This appears to have been the case since at least 7.4
Problems like this is going to make the transition from MySQL 4.0 to 5.x all the more fun when we get around to doing it.