Note: I have moved the essence of this question to here having learned some of what is happening but still wanting to understand. (In essence, ISNULL and COALESCE behave unusually when a variable is either undefined or has been set to NULL before being defined.)
I must be missing something very simple but I don't see any answers to a similar, previous question. I want to use a default value if a variable is not set, but can't see how to do it.
SET @x = NULL;
SELECT IFNULL(NULL, 5), IFNULL(@x,5), IF(@x IS NULL, 5, @x); -- gives 5, BLOB, BLOB
I read that COALESCE is the right function to use here, but COALESCE(@x,5) also gives BLOB.
What am I missing? Is it not possible to, in effect, say x = x OR 5?
This is bizarre. Using MySQL Workbench, it will start to work if you run all 3 blocks:
SET @x = NULL;
SELECT IFNULL(NULL, 5), IFNULL(@x,5), IF(@x IS NULL, 5, @x); -- 5, blob, blob
SET @x = 'TEST';
SELECT IFNULL(NULL, 5), IFNULL(@x,5), IF(@x IS NULL, 5, @x); -- 5, TEST, TEST
SET @x = NULL;
SELECT IFNULL(NULL, 5), IFNULL(@x,5), IF(@x IS NULL, 5, @x); -- 5, 5, 5
AFAIK, you can't define a type for a user-defined variable, but MySQL doesn't seem to know what to do with this variable until you first set it to something that isn't null. If I run your statement within sqlDeveloper, it will output 77686174 instead of 'BLOB'.