0

In trying to test whether a variable has been defined, I discovered that the IF, IFNULL, and COALESCE statements return simply BLOB rather than the value I expected when (a) the variable has not been defined or (b) it has been explicitly set to NULL before being assigned a value in the session. I've verified this in MySQL versions 5.7 and 8.0.

SELECT IF(@p IS NULL, 'is null', 'not null'); # 'is null' 
SELECT IF(@p IS NULL, 'is null', @p); # BLOB
SELECT IFNULL(@p, 'is null'); # BLOB
SELECT COALESCE(@p, 'is null'); # BLOB

The first statement acts as I expected, but the others return BLOB rather than 'is null'. The behavior is the same if preceded by SET @p = NULL.

On the other hand, if you do any of these, the above statements will all return 'is null', that is, they act as expected with a null value.

  1. SET @p = NULL + 1 rather than SET @p = NULL before testing.
  2. SET @p = 5; SET @p = NULL; That is, first set a non-null value, then set to null
  3. SELECT COALESCE(@p+1, 'is null') # use expression @p+1 instead of @p.

Presumably, these three somehow set a type for NULL. In any case, an undefined variable or variable only defined as NULL have a different kind of NULL than those with a history of typing (?).

What is the underlying principle that explains this behavior? Is it simply an untyped NULL acts differently than a typed NULL, and those statements don't work with untyped ones?

Note: I know that that a workaround for this in a procedure, in order to detect undefined variables, can be 

IF @p IS NULL
    SET @p = default_value
END IF;

and it looks as if I can also use COALESCE(@p+1,default_value). But I'm interested in the explanation for why this is needed. Thanks.

Mike Blyth
  • 4,158
  • 4
  • 30
  • 41
  • 1
    You seem to be mixing some things (or I do not understand your question). `blob` is a datatype, `'is null'` is a value. The 1st statement will return a `varchar` with the text `'is null'` (as your input is identifyable as a varchar), statements 2-4 should return a `blob`(type) with the text `'is null'` (as one of the inputs is of unknown type = blob type). Some clients may display blob fields as "blob" instead of showing it's content, so maybe this is what confuses you? – Solarflare Jun 16 '20 at 18:17
  • That may be it. I have been using Workbench which just shows the result as BLOB in a grey box, just like NULL. I understand the concepts of datatypes and values. So you're saying that what is happening is that when these functions, like ISNULL, receive undefined variables and those defined as NULL before having a value they return the correct value but as a blob, right? So the function doesn't, by default, convert 'is null' into a string and 5 into an integer, but returns them as blobs? – Mike Blyth Jun 16 '20 at 19:19
  • 1
    If by "correct value" you mean the input you provided, then yes. The output datatype for "isnull" is the "widest" input datatype. For undeclared types MySQL has to guess, and without more information, it will guess the most general "(long)blob". If you e.g. do `null + 1`, it guesses "double". This belongs to the wide topic of [autocast](https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html). If you want to get an explicit type, you can use [`cast`](https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html). (Rereading your "note"-part, this might have been your actual question). – Solarflare Jun 18 '20 at 11:19

0 Answers0