Dear lazyweb,
I’m possibly being stupid, but can someone explain the differences
between these two queries?
mysql> select count(*) from Table where blobid is null or not blobid like '%-%'; +----------+ | count(*) | +----------+ | 15262487 | +----------+ 1 row in set (25 min 4.18 sec) mysql> select count(*) from Table where blobid is null or blobid not like '%-%'; +----------+ | count(*) | +----------+ | 20044216 | +----------+ 1 row in set (24 min 54.06 sec)
For reference:
mysql> select count(*) from Table where blobid is null; +----------+ | count(*) | +----------+ | 15262127 | +----------+ 1 row in set (24 min 7.15 sec)
Update: It turns out that the former was doing (not blobid) like '%-%' which turns out to not do anything sensible:
mysql> select not blobid from Table limit 10; +------------+ | not blobid | +------------+ | 0 | | NULL | | 1 | | 0 | | 0 | | 0 | | 1 | | NULL | | 1 | | 0 | +------------+ 10 rows in set (0.02 sec) mysql> select blobid from Table limit 10; +-------------------------------------------------------------------+ | blobid | +-------------------------------------------------------------------+ | 5cd1237469cc4b52ca094e215156c582-9ef460ac4134c600a4d2382c4b0acee7 | | NULL | | d20cb4037f8f9ab1de5de264660f005c-2c34209dcfb39251cf7c16bb6754bbd2 | | 845a8d06719d8bad521455a8dd47745c-095d9a0831433c92cd269e14e717b3a9 | | 9580ed23f34dd68d35da82f7b2a293d6-bf39df7509d977a1de767340536ebe80 | | 06c9521472cdac02a2d4b2a18f8bec0f-0a8a28d3b63df54860055f1d1de92969 | | ed3cd0dd9b55f76db7544eeb64f3cfa0-80a6a3eb6d73c0a58f88b7c332866d5c | | NULL | | b339f6545651fbfa49fa500b7845c4ce-6defb5ffc188b8f72f1aa10bbd5c6bec | | 642075963d6f69bb11c35a110dd07c2c-8db54ac2d2accae7fa4a22db1d6caae9 | +-------------------------------------------------------------------+ 10 rows in set (0.00 sec)
The documentation
says Logical NOT. Evaluates to 1 if the operand is 0, to 0 if the
operand is non-zero, and NOT NULL returns NULL. but doesn’t
describe the behaviour of NOT 'string'. It would appear that a
string starting with a number returns 0 and a string starting with a
letter returns 1. Either way, neither has a hyphen in.
on said:
Is this perhaps to do with the associativity of “not”? In your two different examples “not” may well be binding in a different fashion…
IIRC, “not” will be binding to the “like” in the first example and to “blobid” in the second. However, I’m usually a PostgreSQL user so I may have that the wrong way around in MySQL.
Or I may just be plain wrong.
on said:
Hmm, I tried to reproduce this with various databases
(including mysql-5.0.45) and I had to explicitly parenthesize the
“(not blobid)” to get any difference between the not blobid like and
the blobid not like forms. My attempt can be found at http://mayfly.svn.sourceforge.net/viewvc/mayfly/trunk/mayfly/test/net/sourceforge/mayfly/acceptance/expression/WhereTest.java?r1=540&r2=557