MySQL Bugs: #97115: Calling add() on a collection in a loop only adds a single document

Bug #97115	Calling add() on a collection in a loop only adds a single document
Submitted:	6 Oct 2019 16:35	Modified:	16 Dec 2019 15:02
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell General / Core Client	Severity:	S3 (Non-critical)
Version:	8.0.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The statements below cause only one record to be inserted in the first loop and all of them in the second loop.
I would expect that calling add() in a loop would either not add anything or all the documents, not just one.

col1 = db.create_collection('col1')
data = [{"t": "foo"}, {"t": "bar"}, {"t": "baz"}]
for x in data:
    col1.add(x)
col1.find().fields('t')

for x in data: col1.add(x).execute()
col1.find().fields('t')

Note also that in the output it says "1 item affected" even when there are 3 documents added to the collection.

How to repeat:
 MySQL  127.0.0.1:18017+ ssl  test  Py > col1 = db.create_collection('col1')
Query OK, 1 item affected (0.0114 sec)
 MySQL  127.0.0.1:18017+ ssl  test  Py > data = [{"t": "foo"}, {"t": "bar"}, {"t": "baz"}]
Query OK, 1 item affected (0.0105 sec)
 MySQL  127.0.0.1:18017+ ssl  test  Py > for x in data: col1.add(x)
Query OK, 1 item affected (0.0118 sec)
 MySQL  127.0.0.1:18017+ ssl  test  Py > col1.find().fields('t')
{
    "t": "baz"
}
1 document in set (0.0011 sec)
 MySQL  127.0.0.1:18017+ ssl  test  Py > for x in data: col1.add(x).execute()
Query OK, 1 item affected (0.0077 sec)
 MySQL  127.0.0.1:18017+ ssl  test  Py > col1.find().fields('t')
{
    "t": "baz"
}
{
    "t": "foo"
}
{
    "t": "bar"
}
{
    "t": "baz"
}
4 documents in set (0.0011 sec)

Hello Daniël,

Thank you for the report.

regards,
Umesh

Posted by developer:
 
Hi Guys

Thanks for submitting the report.

Unfortunately, our documentation is wrong regarding what should be expected from the Collection.add function.

The collection.add(...) function actually returns an instance of the CollectionAdd object:

https://dev.mysql.com/doc/dev/mysqlsh-api-javascript/8.0/classmysqlsh_1_1mysqlx_1_1_collec...

The CollectionAdd object is a handler to insert documents into the collection, but the documents are NOT inserted into the collection when you call the add function, but until the .execute() function in it is called.

In addition to this, when the shell is executed in interactive mode, it will automatically handle the returned values from the executed code, i.e.

- It will automatically print DB results
- It will automatically execute CRUD operations (Like CollectionAdd)

So, on the first example:

    for x in data:
       col1.add(x)

When the for loop is executed, the JavaScript runtime will return the value of the last executed statement in the loop, on this case will return the CollectionAdd object that was created for the last element in the document list, and the automatic handling done in the shell will cause that object to be executed, resulting in the behaviour you describe: only 1 document got inserted, the last one.

OTOH in the second example:

for x in data: col1.add(x).execute()

Since the .execute() function is called for each document, all of them will be inserted. The .execute() function returns a Result object, however, being a loop, the JavaScript runtimr will return the Result object for the last executed document only, and the shell will automatically print it, saying just 1 document was added, when actually 3 were addde in the loop.

To insert all the documents at once the easy way is to simply pass the document array as follows:

    col1.add(data)
    Query OK, 3 items affected (0.0753 sec)

    Records: 3  Duplicates: 0  Warnings: 0

So as you can see, this works as it expected, but the problem is the Collection.add documentation that indicates it adds the documents, when it actually creates the CollectionAdd handler only. We will use this bug report to fix this documentation issue.

Posted by developer:
 
Documentation fix, no changelog entry needed.