HEX function returns empty string on null input, normal?
Is this by design?
.null NULL_VALUE select null; ┌────────────┐ │ null │ ├────────────┤ │ NULL_VALUE │ └────────────┘ select hex(null); ┌───────────┐ │ hex(null) │ ├───────────┤ │ │ └───────────┘ select typeof(hex(null)); ┌───────────────────┐ │ typeof(hex(null)) │ ├───────────────────┤ │ text │ └───────────────────┘ select hex('')=hex(null); ┌───────────────────┐ │ hex('')=hex(null) │ ├───────────────────┤ │ 1 │ └───────────────────┘
I would expect null output on null input. Instead it returns an empty string.
(2) By Gunter Hick (gunter_hick) on 2020-09-24 07:09:12 in reply to 1 [link] [source]
Based on the documentation I am tempted to say yes.
"The hex() function interprets its argument as a BLOB and returns a string which is the upper-case hexadecimal rendering of the content of that blob."
NULL, however, can never be interpreted as some value, blob or otherwise. NULL is the absence of any value, not a zero-length content of some type.
(4) By Gunter Hick (gunter_hick) on 2020-09-24 09:54:43 in reply to 3 [link] [source]
There was once a gentleman strolling through the Jewisch district who came upon a shop where the sole item one display in the window was a golden pocket watch. This piqued his interest, so he entered to inquire about the price, but was told the watch was not for sale and that he was at the community mohel's office, whose duty is to perform circumcisions. "Is it not a little misleading to advertise this by displaying a pocket watch?", he inquired. "Tell me, what should I display instead?" So what should a string valued function like hex() return instead?
By the same logic
abs() should return some number instead of NULL.
(6.1) By Gunter Hick (gunter_hick) on 2020-09-24 10:22:12 edited from 6.0 in reply to 5 [link] [source]
Nope. From the same page: "Abs(X) returns NULL if X is NULL." The hex() function is the only function that has the phrase "interprets its argument" in it's documentation. All the other functions on that page either explicitly mention a return value for NULL arguments or require their arguments to be strings without mentioning NULL as an argument. EDIT: and return NULL when called with a NULL argument instead. It may not be overtly logical, but it is documented.
I believe the essence here is that hex() is a string function and should/will work always returning a string.
This may be useful if for instance the column you insert it into has NOT NULL restriction.
That said, it's very easy to deal with a NULL value should one really need to absolutely have a string. It's probably more useful to leave that choice to the user of the function, since as it stands now, hex() cannot ever distinguish.
I think this warrants a reconsideration from the devs - unless there is a very good reason I'm missing.
Anyway, to the OP, if they don't change it (perhaps backward compatibility scares them), you can get around it rather convolutedly by something like:
SELECT ..., CASE WHEN x IS NULL THEN NULL ELSE hex(x) END, ... or if you have the new fancy iif() available ion your version: SELECT ..., iif(x IS NULL,NULL,hex(x)), ... etc. Note that this next expression won't work because of CASE rules: SELECT ..., CASE x WHEN NULL THEN NULL ELSE hex(x) END, ...
cuz: I believe the essence here is that hex() is a string function and should/will work always returning a string.
.null NULL select substr(null,1,1); ┌──────────────────┐ │ substr(null,1,1) │ ├──────────────────┤ │ NULL │ └──────────────────┘
Isn't substr() also a string function?
gunter_hick: Nope. From the same page: "Abs(X) returns NULL if X is NULL."
Just like with the
substr() documentation does not mention what happens with NULL, so this alone should not be taken to imply that NULL is 'consumed' differently by those functions.
(9) By Gunter Hick (gunter_hick) on 2020-09-24 15:58:50 in reply to 8 [link] [source]
Like I said before: hex() documentation says "interprets its argument as a BLOB" substr() documentation says "... input string X ..." and goes on to define the difference between passing a string (utf codepoints are counted) and a BLOB (bytes are counted) none of the other string functions says anything about interpreting any argument as anything. This may be different to the mySQl HEX() function, but the documentation refers to the CONV() function which explicitly states a NULL return for a NULL input. Workarounds have been suggested, and I reckon a change is unlikely, as it might break backwards compatibility, so any further discussion is moot.