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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.