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

mysql_stmt_insert_id returns first generated id when using MYSQL_TYPE_NULL

Details

    Description

      When inserting values into a table with auto_increment column via bulk operation, the result of mysql_stmt_insert_id() differs:
      In case buffer_type MYSQL_TYPE_NULL is used, or indicator variable was set to STMT_INDICATOR_IGNORE the values of first inserted row will be returned.

      Output from attached test case:

       Insert id with buffer_type MYSQL_TYPE_LONG: 2
      		 
       Max value for t1.a=2
      		 
       Insert id with buffer_type MYSQL_TYPE_NULL: 1
      		 
       Max value for t1.a=2
      		 
       Insert id with buffer_type STMT_INDICATOR_NULL: 1
      		 
       Max value for t1.a=2

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          georg Georg Richter created issue -
          georg Georg Richter made changes -
          Field Original Value New Value
          Description When inserting values into a table with auto_increment column via bulk operation, the result of {{mysql_stmt_insert_id()}} differs:
           In case buffer_type {{MYSQL_TYPE_NULL}} is used, or indicator variable was set to {{STMT_INDICATOR_IGNORE}} the values of first inserted row will be returned.

          Output from attached test case:
          {{# Insert id with buffer_type MYSQL_TYPE_LONG: 2
          # Max value for t1.a=2
          # Insert id with buffer_type MYSQL_TYPE_NULL: 1
          # Max value for t1.a=2
          # Insert id with buffer_type STMT_INDICATOR_NULL: 1
          # Max value for t1.a=2}}
          		When inserting values into a table with auto_increment column via bulk operation, the result of {{mysql_stmt_insert_id()}} differs:
           In case buffer_type {{MYSQL_TYPE_NULL}} is used, or indicator variable was set to {{STMT_INDICATOR_IGNORE}} the values of first inserted row will be returned.

          Output from attached test case:
          {{ Insert id with buffer_type MYSQL_TYPE_LONG: 2
           Max value for t1.a=2
           Insert id with buffer_type MYSQL_TYPE_NULL: 1
           Max value for t1.a=2
           Insert id with buffer_type STMT_INDICATOR_NULL: 1
           Max value for t1.a=2}}
          georg Georg Richter made changes -
          Description When inserting values into a table with auto_increment column via bulk operation, the result of {{mysql_stmt_insert_id()}} differs:
           In case buffer_type {{MYSQL_TYPE_NULL}} is used, or indicator variable was set to {{STMT_INDICATOR_IGNORE}} the values of first inserted row will be returned.

          Output from attached test case:
          {{ Insert id with buffer_type MYSQL_TYPE_LONG: 2
           Max value for t1.a=2
           Insert id with buffer_type MYSQL_TYPE_NULL: 1
           Max value for t1.a=2
           Insert id with buffer_type STMT_INDICATOR_NULL: 1
           Max value for t1.a=2}}
          		When inserting values into a table with auto_increment column via bulk operation, the result of {{mysql_stmt_insert_id()}} differs:
           In case buffer_type {{MYSQL_TYPE_NULL}} is used, or indicator variable was set to {{STMT_INDICATOR_IGNORE}} the values of first inserted row will be returned.

          Output from attached test case:

          {code:java}
           Insert id with buffer_type MYSQL_TYPE_LONG: 2
           Max value for t1.a=2
           Insert id with buffer_type MYSQL_TYPE_NULL: 1
           Max value for t1.a=2
           Insert id with buffer_type STMT_INDICATOR_NULL: 1
           Max value for t1.a=2
          {code}

          georg Georg Richter made changes -
          Assignee Oleksandr Byelkin [ sanja ]
          sanja Oleksandr Byelkin made changes -
          Fix Version/s 10.2 [ 14601 ]
          Fix Version/s 10.3 [ 22126 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.4 [ 22408 ]
          sanja Oleksandr Byelkin added a comment -

          It is according to our documentation https://mariadb.com/kb/en/library/last_insert_id/
          if statement insert several rows, mysql_stmt_insert_id is assigned with first insert value

          sanja Oleksandr Byelkin added a comment - It is according to our documentation https://mariadb.com/kb/en/library/last_insert_id/ if statement insert several rows, mysql_stmt_insert_id is assigned with first insert value
          sanja Oleksandr Byelkin made changes -
          Fix Version/s 5.5.66 [ 23403 ]
          Fix Version/s 10.2 [ 14601 ]
          Fix Version/s 10.3 [ 22126 ]
          Fix Version/s 10.4 [ 22408 ]
          Resolution Not a Bug [ 6 ]
          Status Open [ 1 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 88081 ] MariaDB v4 [ 154575 ]

          People

            sanja Oleksandr Byelkin
            georg Georg Richter
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.