Unable to set MySQL's BOOL variable to a JSON's BOOL value?

Question

MySQL version: 8.0.27

This makes no sense to me. I have the following definition:

DECLARE p_array_only bool DEFAULT IFNULL(JSON_EXTRACT(in_parameters, '$.array_only'),FALSE);

If I pass a correct JSON structure:

{"array_only":true}

I get the error:

The value 'true' for column 'p_array_only' in row 1 is incorrect

Note that if I omit the key completely, it works (because IFNULL returns Null and is set to FALSE).

What happened? All other code assignments from JSON work fine (INT, Text, SMALLINT, etc.) - the format is exactly the same. And I'm sure this code would have worked a few months ago (using 8.0.24).

I even tried:

IFNULL(IF(JSON_EXTRACT(@test1, '$.array_only') IS TRUE,TRUE,FALSE),FALSE)

Same error.

So, how do I simply convert a true/false BOOL JSON value to a BOOL MySQL value in 8.0.27?

Answer 1

BOOLEAN is not a built-in data type in MySQL. It is an alias and implemented as TINYINT(1).

Seehttps://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html

---

Use implicit data type conversion:

DECLARE p_array_only BOOL 
    DEFAULT IFNULL(0 + JSON_EXTRACT(in_parameter, '$.array_only'), FALSE);

Fails if the corresponding value is of type string or null.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9c5fac2c7533d9e365a449ce00c06f1b

PS. The short form DEFAULT IFNULL(0 in_parameter->'$.array_only', FALSE); is also useful.

PPS. Of course, explicit CAST() can also be used.