MySQL unable to set BOOL variable to JSON BOOL value?

Question

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?

Akina · Accepted Answer · 2022-02-14 21:12:44Z

1

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.

answered Feb 14, 2022 at 21:12

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Floobinator Over a year ago

Wow. Nuts. Thanks a ton for this!

Collectives™ on Stack Overflow

MySQL unable to set BOOL variable to JSON BOOL value?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related