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

BULK insert wrong values when command contain inner select

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 10.4.14, 10.5.5, 10.2(EOL), 10.3(EOL)
    • 10.5
    • None

    Description

      When executing an bulk insert with a SELECT as part of the query, the SELECT part will not be treated for every series of parameters.

      example to reproduce:

      #include <mysql.h>
      #include <string.h>
      #include <stdio.h>
      #include <stdlib.h>
       
      int main(int argc, char **argv)
      {
        printf("MySQL client version: %s\n", mysql_get_client_info());
        MYSQL *con = mysql_init(NULL);
       
        if (con == NULL) {
            fprintf(stderr, "%s\n", mysql_error(con));
            exit(1);
        }
       
        if (mysql_real_connect(con, "localhost", "root", "", NULL, 0, NULL, 0) == NULL) {
            fprintf(stderr, "%s\n", mysql_error(con));
            mysql_close(con);
            exit(1);
        }
       
        if (mysql_query(con, "CREATE DATABASE IF NOT EXISTS testdb")) {
            fprintf(stderr, "%s\n", mysql_error(con));
            mysql_close(con);
            exit(1);
        }
        printf("Database created\n");
       
       
        if (mysql_query(con, "CREATE TEMPORARY TABLE testdb.my_table(id int, val int)")) {
            fprintf(stderr, "%s\n", mysql_error(con));
            mysql_close(con);
            exit(1);
        }
        printf("Table created\n");
       
       
        MYSQL_STMT *stmt;
        stmt = mysql_stmt_init(con);
        if (mysql_stmt_prepare(stmt, "INSERT INTO testdb.my_table(id,val) VALUES( ?, (SELECT ?))", -1) != 0) {
            fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
            return 1;
        }
       
        MYSQL_BIND bind[2];
        int id[3];
        id[0] = 1;
        id[1] = 2;
        id[2] = 3;
       
        int val[3];
        val[0] = 10;
        val[1] = 20;
        val[2] = 30;
       
        char id_ind = STMT_INDICATOR_NONE;
        char val_ind = STMT_INDICATOR_NONE;
       
        memset(bind, 0, sizeof(bind));
        bind[0].u.indicator = &id_ind;
        bind[0].buffer_type = MYSQL_TYPE_LONG;
        bind[0].buffer = &id;
        bind[1].u.indicator = &val_ind;
        bind[1].buffer_type = MYSQL_TYPE_LONG;
        bind[1].buffer = &val;
       
        if (mysql_stmt_bind_param(stmt, bind) != 0) {
            fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
            return 1;
        }
       
        unsigned int numrows = 3;
        mysql_stmt_attr_set(stmt, STMT_ATTR_ARRAY_SIZE, &numrows);
        if (mysql_stmt_execute(stmt) != 0) {
            fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt));
            mysql_close(con);
            exit(1);
        }
        printf("Results:\n");
       
        if (mysql_query(con, "SELECT * FROM testdb.my_table")) {
            fprintf(stderr, "%s\n", mysql_error(con));
            mysql_close(con);
            exit(1);
        }
       
        MYSQL_RES *result = mysql_store_result(con);
        if (result == NULL) {
            printf("No data !?");
            mysql_close(con);
            exit(1);
        }
       
        int num_fields = mysql_num_fields(result);
        MYSQL_ROW row;
        while ((row = mysql_fetch_row(result))) {
            for (int i = 0; i < num_fields; i++) {
                printf("%s ", row[i] ? row[i] : "NULL");
            }
            printf("\n");
        }
        mysql_free_result(result);
       
        mysql_close(con);
        exit(0);
      }
      

      This returns
      Results:
      1 10
      2 10
      NULL 10

      but expected
      1 10
      2 20
      3 30

      (the null value but expected 3 must probably be an example error, but I don't see why)

      Attachments

        Issue Links

          Activity

            sanja Oleksandr Byelkin added a comment - - edited

            Here is the test in our client test:

            static void test_mdev_23864()
            {
              MYSQL_STMT *stmt;
              int rc;
              MYSQL_BIND bind[2];
              int id[3]= {1,2,3};
              int val[3]= {10, 20, 30};
              char id_ind= STMT_INDICATOR_NONE;
              char val_ind= STMT_INDICATOR_NONE;
              unsigned int numrows= 3;
              MYSQL_RES *result;
              MYSQL_ROW row;
             
              myheader("test_mdev_23864");
             
              rc= mysql_query(mysql, "CREATE DATABASE Id testdb");
              myquery(rc);
              rc= mysql_query(mysql, "CREATE TEMPORARY TABLE testdb.my_table"
                                     " (id int, val int)");
              myquery(rc);
              stmt= mysql_stmt_init(mysql);
              check_stmt(stmt);
              rc= mysql_stmt_prepare(stmt, "INSERT INTO testdb.my_table(id,val)"
                                           " VALUES( ?, (SELECT ?))", -1);
              check_execute(stmt, rc);
             
              memset(bind, 0, sizeof(bind));
              bind[0].u.indicator = &id_ind;
              bind[0].buffer_type = MYSQL_TYPE_LONG;
              bind[0].buffer = &id;
              bind[1].u.indicator = &val_ind;
              bind[1].buffer_type = MYSQL_TYPE_LONG;
              bind[1].buffer = &val;
             
              rc= mysql_stmt_bind_param(stmt, bind);
              check_execute(stmt, rc);
             
              mysql_stmt_attr_set(stmt, STMT_ATTR_ARRAY_SIZE, &numrows);
              rc= mysql_stmt_execute(stmt);
              check_execute(stmt, rc);
             
              mysql_query(mysql, "SELECT * FROM testdb.my_table");
              myquery(rc);
             
              result = mysql_store_result(mysql);
              mytest(result);
             
              DIE_UNLESS(2 == mysql_num_fields(result));
              for (int i= 0; i< 3; i++)
              {
                row= mysql_fetch_row(result);
                DIE_UNLESS(row);
                fprintf(stdout, "Raw: %d  id: %s  val: %s\n",
                        i+1,
                        (row[0] ? row[0] : "NULL"),
                        (row[1] ? row[1] : "NULL"));
                DIE_IF(atoi(row[0]) != id[i]);
                DIE_IF(atoi(row[1]) != val[i]);
              }
              row= mysql_fetch_row(result);
              DIE_UNLESS(row != 0);
             
              mysql_free_result(result);
             
              rc= mysql_query(mysql, "DROP DATABASE testdb");
              myquery(rc);
             
            }
            

            sanja Oleksandr Byelkin added a comment - - edited Here is the test in our client test: static void test_mdev_23864() { MYSQL_STMT *stmt; int rc; MYSQL_BIND bind[2]; int id[3]= {1,2,3}; int val[3]= {10, 20, 30}; char id_ind= STMT_INDICATOR_NONE; char val_ind= STMT_INDICATOR_NONE; unsigned int numrows= 3; MYSQL_RES *result; MYSQL_ROW row;   myheader("test_mdev_23864");   rc= mysql_query(mysql, "CREATE DATABASE Id testdb"); myquery(rc); rc= mysql_query(mysql, "CREATE TEMPORARY TABLE testdb.my_table" " (id int, val int)"); myquery(rc); stmt= mysql_stmt_init(mysql); check_stmt(stmt); rc= mysql_stmt_prepare(stmt, "INSERT INTO testdb.my_table(id,val)" " VALUES( ?, (SELECT ?))", -1); check_execute(stmt, rc);   memset(bind, 0, sizeof(bind)); bind[0].u.indicator = &id_ind; bind[0].buffer_type = MYSQL_TYPE_LONG; bind[0].buffer = &id; bind[1].u.indicator = &val_ind; bind[1].buffer_type = MYSQL_TYPE_LONG; bind[1].buffer = &val;   rc= mysql_stmt_bind_param(stmt, bind); check_execute(stmt, rc);   mysql_stmt_attr_set(stmt, STMT_ATTR_ARRAY_SIZE, &numrows); rc= mysql_stmt_execute(stmt); check_execute(stmt, rc);   mysql_query(mysql, "SELECT * FROM testdb.my_table"); myquery(rc);   result = mysql_store_result(mysql); mytest(result);   DIE_UNLESS(2 == mysql_num_fields(result)); for (int i= 0; i< 3; i++) { row= mysql_fetch_row(result); DIE_UNLESS(row); fprintf(stdout, "Raw: %d id: %s val: %s\n", i+1, (row[0] ? row[0] : "NULL"), (row[1] ? row[1] : "NULL")); DIE_IF(atoi(row[0]) != id[i]); DIE_IF(atoi(row[1]) != val[i]); } row= mysql_fetch_row(result); DIE_UNLESS(row != 0);   mysql_free_result(result);   rc= mysql_query(mysql, "DROP DATABASE testdb"); myquery(rc);   }

            Item_cache in Item_singlerow_subselect was not updated

            sanja Oleksandr Byelkin added a comment - Item_cache in Item_singlerow_subselect was not updated
            diego dupin Diego Dupin added a comment -

            It might be nice to check if that correct MDEV-24411 as well

            diego dupin Diego Dupin added a comment - It might be nice to check if that correct MDEV-24411 as well

            The problem is that the subquery treated as a constant, because a parameter is a constant in one run, but not in this case where "one run" include changing it for different parameters.

            sanja Oleksandr Byelkin added a comment - The problem is that the subquery treated as a constant, because a parameter is a constant in one run, but not in this case where "one run" include changing it for different parameters.

            Preventing make_const in INSERT do not help, probably it is better to prohibit array binding optimization in case of INSERT with subqueries...

            sanja Oleksandr Byelkin added a comment - Preventing make_const in INSERT do not help, probably it is better to prohibit array binding optimization in case of INSERT with subqueries...

            People

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

              Dates

                Created:
                Updated:

                Git Integration

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