'Why do MySQL 8 IF and IFNULL return a BLOB result when testing a variable with value NULL?

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?

mysqlvariablesnullcoalesceifnull


Solution 1:[1]

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'.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tony from NH

Related Questions