MySQL Bugs: #96983: can not CAST( anything AS JSON) in stored routines , triggers

Bug #96983	can not CAST( anything AS JSON) in stored routines , triggers
Submitted:	24 Sep 2019 6:43	Modified:	25 Sep 2019 8:54
Reporter:	YAN LOONG LIM	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	8.0.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
-- adding json object to be another json object child
SET @aaa = '{"300006":{"a":"b"}}';
SET @bbb = '{"300007":{"c":"d"}}';

SELECT (SELECT JSON_EXTRACT(@aaa,'$."300006"') ) INTO @a;
SET @b = (SELECT JSON_EXTRACT(@bbb,'$."300007"') );
SET @c = (SELECT JSON_SET(@a,'$."300007"',CAST(@b AS JSON)));
SELECT @c; -- output (no slashes) : {"a": "b", "300007": {"c": "d"}}'
SET @ccc = JSON_OBJECT("300006",CAST(@c AS JSON));
SELECT @ccc; -- output (no slashes) : {"300006": {"a": "b", "300007": {"c": "d"}}}

How to repeat:
This a way to create a membership tree in json without any irritating slashes.

It work fine in mysql workbench query tab. but it does not work in stored routine, triggers maybe transaction.

In stored routine , we can cast(anything AS NCHAR) BUT NOT AS JSON , NEVER JSON , AND CAN NOT EVEN `DECLARE abc JSON;` in stored routine.

In conclude JSON dataType just does not work in stored routine, triggers, and maybe transaction.

Hello YAN LOONG LIM,

Thank you for the report.
I'm sorry but not seeing any issues when executed as individual statements or through stored routine. Could you please explain what exactly is the issue here? Thank you.

-
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> -- adding json object to be another json object child
mysql> SET @aaa = '{"300006":{"a":"b"}}';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @bbb = '{"300007":{"c":"d"}}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT (SELECT JSON_EXTRACT(@aaa,'$."300006"') ) INTO @a;
Query OK, 1 row affected (0.01 sec)

mysql> SET @b = (SELECT JSON_EXTRACT(@bbb,'$."300007"') );
Query OK, 0 rows affected (0.00 sec)

mysql> SET @c = (SELECT JSON_SET(@a,'$."300007"',CAST(@b AS JSON)));
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @c; -- output (no slashes) : {"a": "b", "300007": {"c": "d"}}'
+----------------------------------+
| @c                               |
+----------------------------------+
| {"a": "b", "300007": {"c": "d"}} |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SET @ccc = JSON_OBJECT("300006",CAST(@c AS JSON));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @ccc; -- output (no slashes) : {"300006": {"a": "b", "300007": {"c": "d"}}}
+----------------------------------------------+
| @ccc                                         |
+----------------------------------------------+
| {"300006": {"a": "b", "300007": {"c": "d"}}} |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE Bug96983()
    -> BEGIN
    -> SET @aaa = '{"300006":{"a":"b"}}';
    -> SET @bbb = '{"300007":{"c":"d"}}';
    ->
    -> SELECT (SELECT JSON_EXTRACT(@aaa,'$."300006"') ) INTO @a;
    -> SET @b = (SELECT JSON_EXTRACT(@bbb,'$."300007"') );
    -> SET @c = (SELECT JSON_SET(@a,'$."300007"',CAST(@b AS JSON)));
    -> SELECT @c; -- output (no slashes) : {"a": "b", "300007": {"c": "d"}}'
    -> SET @ccc = JSON_OBJECT("300006",CAST(@c AS JSON));
    -> SELECT @ccc; -- output (no slashes) : {"300006": {"a": "b", "300007": {"c": "d"}}}
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER
ERROR:
DELIMITER must be followed by a 'delimiter' character or string
mysql> DELIMITER ;
mysql>
mysql> call Bug96983();
+----------------------------------+
| @c                               |
+----------------------------------+
| {"a": "b", "300007": {"c": "d"}} |
+----------------------------------+
1 row in set (0.00 sec)

+----------------------------------------------+
| @ccc                                         |
+----------------------------------------------+
| {"300006": {"a": "b", "300007": {"c": "d"}}} |
+----------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

regards,
Umesh

JSON does not work in `mysql workbench` (versino 8.0.17 built 14960816 64bit) windows 10

the following will generate error and crash the program and can not forward engineering :
1) DECLARE abc JSON; -->(error dataType)
2) CAST(something AS JSON); -->(error)

Thank you for the feedback, could you please provide exact screenshot of the issue that you are seeing? Are you able to create use in CLI? I tried at my end using WB 8.0.17 on Win10 but not seeing any issues, joining the screenshot shortly. If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.  

Thank you for your interest in MySQL.

regards,
Umesh

Screenshot

Attachment: 96983_WB8.0.17.png (image/png, text), 68.95 KiB.

msyql workbench 8.0

Attachment: workbench_version.jpg (image/jpeg, text), 33.58 KiB.

workbench .mwb can not cast as json but can cast as nchar

Attachment: workbench_can_not_cast_as_json_datatype.jpg (image/jpeg, text), 103.48 KiB.

workbench .mwb can not declare as json datatype but can declare as others

Attachment: workbench_can_not_declare_json_datatype.jpg (image/jpeg, text), 105.70 KiB.

i can do it by cli and workbench query tab. this is now i am doing it.
i can not 'declare json datatype' and 'cast as json datatype'.
Attached 3 screenshot.

i have to do it by cli or query tab after i did forward engineering to server.

thank you

Thank you for the feedback.
Issue noticed in the while adding Routines to the Physical Schemata through the Routine Editor.

regards,
Umesh

Screenshot

Attachment: 96983_WB8.0.17_.png (image/png, text), 66.75 KiB.