MySQL unable to set BOOL variable to JSON BOOL value?-CodePudding

MySQL Version: 8.0.27

This isn't making any 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 proper JSON structure of:

{"array_only":true}

I get the error:

Incorrect integer value: 'true' for column 'p_array_only' at row 1

Note if I omit the key altogether, it's fine (because IFNULL comes back as Null and sets FALSE).

What on earth is going on here? All other code assignments from JSON work perfectly fine (INT, Text, SMALLINT, and others) - in the exact same format. And I could have sworn this code worked a few months ago (back with 8.0.24).

I even tried:

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

Same error.

So how on earth can I simply cast a true/false BOOL JSON value to a BOOL MySQL value with 8.0.27?

CodePudding user response：

BOOLEAN is not built-in datatype in MySQL. It is an alias, and it is implemented as TINYINT(1).

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

Use implicit datatype convertion:

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

Will fail if according value is string-type or null.

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

PS. Short form DEFAULT IFNULL(0 in_parameter->'$.array_only', FALSE); is useful too.

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