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: | |
Category: | MySQL Workbench: Modeling | Severity: | S3 (Non-critical) |
Version: | 8.0.17 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[24 Sep 2019 6:43]
YAN LOONG LIM
[24 Sep 2019 8:16]
MySQL Verification Team
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
[24 Sep 2019 8:47]
YAN LOONG LIM
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)
[24 Sep 2019 10:03]
MySQL Verification Team
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
[24 Sep 2019 10:03]
MySQL Verification Team
Screenshot
Attachment: 96983_WB8.0.17.png (image/png, text), 68.95 KiB.
[25 Sep 2019 5:26]
YAN LOONG LIM
msyql workbench 8.0
Attachment: workbench_version.jpg (image/jpeg, text), 33.58 KiB.
[25 Sep 2019 5:28]
YAN LOONG LIM
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.
[25 Sep 2019 5:29]
YAN LOONG LIM
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.
[25 Sep 2019 5:32]
YAN LOONG LIM
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
[25 Sep 2019 8:54]
MySQL Verification Team
Thank you for the feedback. Issue noticed in the while adding Routines to the Physical Schemata through the Routine Editor. regards, Umesh
[25 Sep 2019 8:55]
MySQL Verification Team
Screenshot
Attachment: 96983_WB8.0.17_.png (image/png, text), 66.75 KiB.