0

Can anybody explain why FIND_IN_SET isn't returning the row with the NULL value? To me it seems obvious, the string "test", isn't part of NULL, so it should return it?

http://sqlfiddle.com/#!9/389043/2

CREATE TABLE test (
  id INT AUTO_INCREMENT,
  result VARCHAR(255),
  PRIMARY KEY (`id`)
);

INSERT INTO test (id, result) VALUES (1, NULL), (2, 'abc'), (3, 'abc,test');

SELECT * FROM test
WHERE
  NOT FIND_IN_SET('test', result);
1
  • 1
    Null comparisons are sometimes not false but undefined. And negating undefined is not true but again undefined. – eckes May 22 '17 at 11:32
2

Well, NULL is not a string, not even an empty one. When you compare to NULL special operators are of need, like column IS NULL or the NULL-safe comparator <=>.

Usually this is solved with a query like

SELECT * FROM test
WHERE
  NOT FIND_IN_SET('test', result)
  OR result IS NULL;

I for one think, that the FIND_IN_SET() function shouldn't exist at all. It makes it seem like having comma separated values in a column is absolutely normal and quite common. But it isn't. You shouldn't store the values this way. You have a lot of disadvantages (see this post for a probably incomplete list) and you should really look into database normalization. Like Bill says in the linked post, comma separated values violate the very first normal form already.

3
  • The result IS NULL is the solution I came up with. My actual use case is selecting a bunch of rows, GROUP_CONCAT the results, and using FIND_IN_SET on that, so I'm not actually storing CSV in the database. I'll have a read through the linked posts now, thanks! – TMH May 22 '17 at 11:38
  • Sounds like you could avoid FIND_IN_SET()? Consider asking in another question, how the query could be rewritten. FIND_IN_SET() can't make use of an index, so this might come in handy once you deal with enough data :) – tombom May 22 '17 at 11:44
  • Well, for now I'm still working some kinks out (read: forced rushed deployment, retro-fixing bugs :p) with the full query, but once I'm satisfied I've got it nailed, I'll post here again for advice on improvements I could make. – TMH May 22 '17 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.