Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21916

COM_STMT_BULK_EXECUTE with RETURNING insert wrong values

    XMLWordPrintable

    Details

      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                | ...........      |
      +--------------------------------------------------+------------------+
      
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              diego dupin Diego Dupin
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration