MySQL Bugs: #106570: cannot add JSON field to JSON document table

Bug #106570	cannot add JSON field to JSON document table
Submitted:	25 Feb 2022 7:54	Modified:	3 Mar 2022 13:23
Reporter:	Peter Huszar	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	8.0.15	OS:	Windows (10)
Assigned to:		CPU Architecture:	x86
Tags:	insert, json

Description:
My table has two fields 
- doc is type JSON; 
- _id is type VARBINARY(32) with PK, NN and G attributes 

In the grid I can see the existing JSON values; 
I can edit the values in the JSON editor and the generated SQL UPDATE runs without problem in the WHERE clause setting _id=0x1234

When I want to create a new record in the grid (typing and then checking in JSON editor, the document is correct JSON), the workbench generates during apply this INSERT:

INSERT INTO <mytable> (`doc`,`_id`) VALUES ('<mydocument>',?);

producing:

ERROR 3105: 3105: The value specified for generated column '_id' in table '<mytable>' is not allowed. 

I took out _id:

INSERT INTO <mytable> (`doc`) VALUES ('<mydocument>');

getting

ERROR 1048: 1048: Column '_id' cannot be null

one of the two forms above MUST work

How to repeat:
- go to Workbench, 
- create the JSON table (collection) with the fields given above
- try adding a new record in the grid
 

Suggested fix:
1. Generate an INSERT that is acceptable for the server 
2. Modify the server to accept any of the above INSERTs

Hello Peter Huszar,

Thank you for the bug report.
Could you please provide test case(create table and sample data, make it as private if you prefer) to reproduce this issue at our end? Thanks.

Regards,
Ashwini Patil

To be very specific, this is the table:

CREATE TABLE docs (
    doc JSON,
    _id VARBINARY(32)
)

And this is the by Workbench generated record

INSERT INTO docs (`doc`,`_id`) VALUES ('[{"a":{"b":"1","c":"2"},"d":"3"},{"a":{"b":"31","c":"23"},"d":"45"}]',?)

Hello Peter Huszar,

Thank you for the requested details.
I tried to reproduce your issue on Windows 10 with MySQL Workbench 8.0.28 using details provided but I am not seeing any issues at my end. 
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,
Ashwini Patil