MySQL Bugs: #81271: mysqlsh accepts collections with extra fields

Bug #81271	mysqlsh accepts collections with extra fields
Submitted:	2 May 2016 7:24	Modified:	9 Oct 2017 16:14
Reporter:	Giuseppe Maxia (OCA)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Document Store: MySQL Shell	Severity:	S3 (Non-critical)
Version:	5.7.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In the SQL client, create this table and insert something:

    CREATE TABLE strangedoc (doc JSON, \
    _id VARCHAR(32) \
        GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) \
        STORED NOT NULL UNIQUE,
        secret_stash varchar(200),
        more_secret_info mediumtext
    ) CHARSET utf8mb4 ENGINE=InnoDB;

insert into strangedoc (doc,secret_stash,more_secret_info) values ('{"_id": "abc", "name": "Susan"}', 'and now for something completely different', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.');
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (world_x) > select * from strangedoc\G
*************************** 1. row ***************************
             doc: {"_id": "abc", "name": "Susan"}
             _id: abc
    secret_stash: and now for something completely different
more_secret_info: Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.
1 row in set (0.00 sec)

select * from strangedoc\G
*************************** 1. row ***************************
             doc: {"_id": "abc", "name": "Susan"}
             _id: abc
    secret_stash: and now for something completely different
more_secret_info: Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.

Then, retrieve the information from mysqlsh:

    mysql-js> db.collections
    {
        "strangedoc": <Collection:strangedoc>
    }
    mysql-js> db.strangedoc.find()
    [
        {
            "_id": "abc",
            "name": "Susan"
        }
    ]
    1 document in set (0.00 sec)

The collection is recognized as such, but the extra fields are invisible.

How to repeat:
See above

Hello Giuseppe,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh

Posted by developer:
 
MySQL Shell 1.0.10
MySQL Shell 8.0.3
MySQL Server 8.0.3

Could not reproduce in latest environment.

1. Create Table

mysql-sql> CREATE TABLE strangedoc (doc JSON, _id VARCHAR(32) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id')))  STORED NOT NULL UNIQUE, secret_stash varchar(200), more_secret_info mediumtext) CHARSET utf8mb4 ENGINE=InnoDB;
Query OK, 0 rows affected (0.67 sec)

2. Insert data

mysql-sql> insert into strangedoc (doc,secret_stash,more_secret_info) values ('{"_id":"abc", "name": "Susan"}', 'and now for something completely different','Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.');
Query OK, 1 row affected (0.19 sec)

3. SQL Query to check insert.

mysql-sql> select * from strangedoc\G
*************************** 1. row ***************************
             doc: {"_id": "abc", "name": "Susan"}
             _id: abc
    secret_stash: and now for something completely different
more_secret_info: Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.
1 row in set (0.00 sec)

4. This is not longer accepted as a collection, instead is accepted as a table object.

mysql-js> db.getCollections()
[]
mysql-js> db.getTables()
[
    <Table:strangedoc>
]

mysql-js> dir(db.strangedoc)
[
    "name",
    "session",
    "schema",
    "delete",
    "existsInDatabase",
    "getName",
    "getSchema",
    "getSession",
    "help",
    "insert",
    "isView",
    "select",
    "update"
]

5. Using it as a table object you can get with the select() the required info

mysql-js> db.strangedoc.select()
+---------------------------------+-----+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------+
| doc                             | _id | secret_stash                               | more_secret_info                                                                                                             |
+---------------------------------+-----+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------+
| {"_id": "abc", "name": "Susan"} | abc | and now for something completely different | Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. |
+---------------------------------+-----+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql-js> db.strangedoc.select(["secret_stash"])
+--------------------------------------------+
| secret_stash                               |
+--------------------------------------------+
| and now for something completely different |
+--------------------------------------------+
1 row in set (0.00 sec)