[MDEV-21916] COM_STMT_BULK_EXECUTE with RETURNING insert wrong values Created: 2020-03-11  Updated: 2021-07-15  Resolved: 2021-07-15

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.5.1
Fix Version/s: 10.5.12

Type: Bug Priority: Critical
Reporter: Diego Dupin Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: CONNECTOR_RELATED

Issue Links:
Blocks
blocks CONJS-125 permit using batch with returning clause Closed

 Description   

COM_STMT_BULK_EXECUTE permits to use batching.
Having the results using the new 10.5 RETURNING clause might be very interesting to retrieve all ids.

Return resulset work well. Problem is the data inserted doesn't correspond to command.

to reproduced : 

CREATE TABLE batch_bulk(id int NOT NULL AUTO_INCREMENT, val varchar(250), PRIMARY KEY (id));

sending a COM_STMT_BULK_EXECUTE

INSERT INTO `batch_bulk`(val) values (?)

with data 'test1', 'test2', 'test3' works well, with 3 new rows inserted.

but same query with returning clause :

INSERT INTO `batch_bulk`(val) values (?)  returning id

with result in having 15 new rows with value 'test1', and no rows for 'test2', 'test3'
The returned resultset show the 15 new ids;

To confirm commands follow protocol:

==> conn:125 BatchBulk(0,58)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 36 00 00 00 16 49 4E 53  45 52 54 20 49 4E 54 4F | 6....INSERT INTO |
 
| 20 60 62 61 74 63 68 5F  62 75 6C 6B 60 28 76 61 |  `batch_bulk`(va |
 
| 6C 29 20 76 61 6C 75 65  73 20 28 3F 29 20 72 65 | l) values (?) re |
 
| 74 75 72 6E 69 6E 67 20  69 64                   | turning id       |
 
+--------------------------------------------------+------------------+
 
 
 
==> conn:125 BatchBulk(0,34)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 1E 00 00 00 FA FF FF FF  FF 80 00 0F 00 00 05 74 | ...............t |
 
| 65 73 74 31 00 05 74 65  73 74 32 00 05 74 65 73 | est1..test2..tes |
 
| 74 33                                            | t3               |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readPrepareResultPacket (0,12)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 0C 00 00 01 00 01 00 00  00 00 00 01 00 00 00 00 | ................ |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.skipParameterPacket (0,23)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 17 00 00 02 03 64 65 66  00 00 00 01 3F 00 0C 3F | .....def....?..? |
 
| 00 00 00 00 00 06 80 00  00 00 00                | ...........      |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResponsePacket (0,1)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 01 00 00 01 01                                   | .....            |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readColumn (0,51)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 33 00 00 02 03 64 65 66  05 74 65 73 74 6E 0A 62 | 3....def.testn.b |
 
| 61 74 63 68 5F 62 75 6C  6B 0A 62 61 74 63 68 5F | atch_bulk.batch_ |
 
| 62 75 6C 6B 02 69 64 02  69 64 0C 3F 00 0B 00 00 | bulk.id.id.?.... |
 
| 00 03 03 42 00 00 00                             | ...B...          |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResultSetRow (0,6)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 06 00 00 03 00 00 01 00  00 00                   | ..........       |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResultSetRow (0,6)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 06 00 00 04 00 00 02 00  00 00                   | ..........       |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResultSetRow (0,6)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 06 00 00 05 00 00 03 00  00 00                   | ..........       |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResultSetRow (0,6)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 06 00 00 06 00 00 04 00  00 00                   | ..........       |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResultSetRow (0,6)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 06 00 00 07 00 00 05 00  00 00                   | ..........       |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResultSetRow (0,6)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 06 00 00 08 00 00 06 00  00 00                   | ..........       |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResultSetRow (0,6)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 06 00 00 09 00 00 07 00  00 00                   | ..........       |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResultSetRow (0,6)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 06 00 00 0A 00 00 08 00  00 00                   | ..........       |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResultSetRow (0,6)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 06 00 00 0B 00 00 09 00  00 00                   | ..........       |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResultSetRow (0,6)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 06 00 00 0C 00 00 0A 00  00 00                   | ..........       |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResultSetRow (0,6)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 06 00 00 0D 00 00 0B 00  00 00                   | ..........       |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResultSetRow (0,6)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 06 00 00 0E 00 00 0C 00  00 00                   | ..........       |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResultSetRow (0,6)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 06 00 00 0F 00 00 0D 00  00 00                   | ..........       |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResultSetRow (0,6)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 06 00 00 10 00 00 0E 00  00 00                   | ..........       |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResultSetRow (0,6)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 06 00 00 11 00 00 0F 00  00 00                   | ..........       |
 
+--------------------------------------------------+------------------+
 
 
 
<== conn:125 BatchBulk.readResultSetRow (0,7)
 
+--------------------------------------------------+
 
|  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
 
+--------------------------------------------------+------------------+
 
| 07 00 00 12 FE 00 00 02  00 00 00                | ...........      |
 
+--------------------------------------------------+------------------+



 Comments   
Comment by Oleksandr Byelkin [ 2020-06-22 ]

DELETE RETURNING shoud be brocken it the same way

(reading and writing the same buffer)

Comment by Oleksandr Byelkin [ 2020-06-22 ]

Yes, there is the same problem:

static void test_bulk_delete_returning()
 
{
 
  int rc;
 
  MYSQL_STMT *stmt;
 
  MYSQL_BIND bind[2], res_bind[2];
 
  MYSQL_ROW  row;
 
  MYSQL_RES *result;
 
  int        i,
 
             id[]= {1, 2, 3, 4},
 
             count= sizeof(id)/sizeof(id[0]);
 
  unsigned long length[1];
 
  my_bool       is_null[1];
 
  my_bool       error[1];
 
  int32_t       res[1];
 
 
 
  rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
 
  myquery(rc);
 
  rc= mysql_query(mysql, "CREATE TABLE t1 (id int not null primary key)");
 
  myquery(rc);
 
  rc= mysql_query(mysql, "insert into t1 values (1), (2), (3), (4)");
 
  myquery(rc);
 
  verify_affected_rows(4);
 
 
 
  stmt= mysql_stmt_init(mysql);
 
  rc= mysql_stmt_prepare(stmt, "DELETE FROM t1 WHERE id=? RETURNING id", -1);
 
  check_execute(stmt, rc);
 
 
 
  memset(bind, 0, sizeof(bind));
 
  bind[0].buffer_type = MYSQL_TYPE_LONG;
 
  bind[0].buffer = (void *)id;
 
  bind[0].buffer_length = 0;
 
 
 
  mysql_stmt_attr_set(stmt, STMT_ATTR_ARRAY_SIZE, (void*)&count);
 
  rc= mysql_stmt_bind_param(stmt, bind);
 
  check_execute(stmt, rc);
 
 
 
  rc= mysql_stmt_execute(stmt);
 
  myquery(rc);
 
 
 
  memset(bind, 0, sizeof(res_bind));
 
  res_bind[0].buffer_type= MYSQL_TYPE_LONG;
 
  res_bind[0].buffer= (char *)&res[0];
 
  res_bind[0].is_null= &is_null[0];
 
  res_bind[0].length= &length[0];
 
  res_bind[0].error= &error[0];
 
  rc= mysql_stmt_bind_result(stmt, res_bind);
 
  myquery(rc);
 
  rc= mysql_stmt_store_result(stmt);
 
  myquery(rc);
 
 
 
  i= 0;
 
  while (!mysql_stmt_fetch(stmt))
 
  {
 
    i++;
 
    DIE_IF(is_null[0]);
 
    DIE_IF(res[0] != i);
 
  }
 
  DIE_IF(i != 4);
 
 
 
  mysql_stmt_close(stmt);
 
 
 
  rc= mysql_query(mysql, "SELECT id FROM t1");
 
  myquery(rc);
 
 
 
  result= mysql_store_result(mysql);
 
  mytest(result);
 
 
 
  i= 0;
 
  while ((row= mysql_fetch_row(result)))
 
  {
 
    i++;
 
  }
 
  DIE_IF(i != 0 );
 
  mysql_free_result(result);
 
 
 
 
 
  rc= mysql_query(mysql, "DROP TABLE t1");
 
  myquery(rc);
 
}

Comment by Oleksandr Byelkin [ 2020-06-23 ]

there is more problem with DELETE ... RETURNING due to protocol.

Comment by Oleksandr Byelkin [ 2020-07-03 ]

commit f854ac4d9e0a2ab534fb5e9bbdb2c3338a1ab57e (HEAD > bb-10.5MDEV-21916, origin/bb-10.5-MDEV-21916)
Author: Oleksandr Byelkin <sanja@mariadb.com>
Date: Mon Jun 22 18:21:21 2020 +0200

MDEV-21916: COM_STMT_BULK_EXECUTE with RETURNING insert wrong values

To allocate new net buffer to avoid changing bufer we are reading.

Comment by Oleksandr Byelkin [ 2020-10-07 ]

commit 27060eb6ba514e00ed30e939556b14bcdb4aa89e (HEAD -> bb-10.5-MDEV-21916, origin/bb-10.5-MDEV-21916)
Author: Oleksandr Byelkin <sanja@mariadb.com>
Date: Mon Jun 22 18:21:21 2020 +0200

MDEV-21916: COM_STMT_BULK_EXECUTE with RETURNING insert wrong values

To allocate new net buffer to avoid changing bufer we are reading.

Comment by Oleksandr Byelkin [ 2021-06-09 ]

commit a6e96ce3c9686a9e7a926c9ca20de66daa3143fb (HEAD > bb-10.5MDEV-21916, origin/bb-10.5-MDEV-21916)
Author: Oleksandr Byelkin <sanja@mariadb.com>
Date: Mon Jun 22 18:21:21 2020 +0200

MDEV-21916: COM_STMT_BULK_EXECUTE with RETURNING insert wrong values

The problem is that array binding uses net buffer to read parameters for each
execution while each execiting with RETURNING write in the same buffer.

Solution is to allocate new net buffer to avoid changing buffer we are reading
from.

Generated at Thu Feb 08 09:10:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.