3

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?

Mike Blyth
  • 4,158
  • 4
  • 30
  • 41
  • MySQL 5.1 gives 5,5,5. – user207421 Jun 16 '20 at 03:26
  • 1
    Your investigation makes no sense. Server uses a datatype which may store any (initially unpredictable) output value and avoid any theoretically possible data truncate. If you need some definite datatype then use explicit CAST(). – Akina Jun 16 '20 at 04:50
  • @Akina Don't understand your comment. The OP is asking, if `@x` is NULL, and if `IFNULL(NULL, 5)` gives 5, why doesn't `IFNULL(@x, 5)` also give 5? – Willis Blackburn Jun 16 '20 at 16:36
  • @WillisBlackburn *why doesn't IFNULL(@x, 5) also give 5?* ????? OP do NOT look at the result at all !!! He looks at the result's datatype as it is shown by some (unspecified) client... Take a look and make sure: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a68dfa5d19c3f9955ab011aa28eb6f01 – Akina Jun 16 '20 at 16:40
  • Interesting. When I tried it in SQL Fiddle the last two columns showed "NQ==" which is "5" encoded in base 64. It looks like in all cases the result of the two expressions using `@x` is a blob and the different clients just decode it differently. But the question is, why does `IFNULL(NULL, 5)` give 5 (as an integer) but `IFNULL(@x, NULL)` give 5 as a blob? I'm guessing it has something to do with the fact that `IFNULL(NULL, 5)` contains only constants and so MySQL can figure out at the time it parses the query that it's going to be just 5. – Willis Blackburn Jun 16 '20 at 17:07
  • For what it's worth, a literal is not the same as a variable or a column. – Álvaro González Jun 16 '20 at 17:08

1 Answers1

0

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

Tony from NH
  • 1