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

EXPLAIN INSERT .. VALUES works as INSERT (adds rows to the table)

Details

    Description

      create table t1 (i int);
      explain insert into t1 values (1),(2);
      select * from t1;

      MariaDB [test]> select * from t1;
      +------+
      | i    |
      +------+
      |    1 |
      |    2 |
      +------+
      2 rows in set (0.00 sec)

      bzr version-info
      revision-id: psergey@askmonty.org-20130926104732-aq7wcma425u5skrw
      revno: 3700
      branch-nick: 10.0-base-explain-slowquerylog

      It may not return an execution plan if we don't want it to, but it certainly shouldn't insert any records.

      Attachments

        Issue Links

          Activity

            Not repeatable with the latest tree.

            psergei Sergei Petrunia added a comment - Not repeatable with the latest tree.
            danblack Daniel Black added a comment - - edited

            MariaDB [(none)]> set sql_log_bin=0;
             
            MariaDB [openquery]> select @@version;
            +---------------------------+
            | @@version                 |
            +---------------------------+
            | 10.0.21-MariaDB-wsrep-log |
            +---------------------------+
            1 row in set (0.00 sec)
             
            MariaDB [openquery]> CREATE TEMPORARY TABLE tt_temp    (       id BIGINT   AUTO_INCREMENT PRIMARY KEY ,         value VARCHAR(2)    )  AUTO_INCREMENT = 1;
            Query OK, 0 rows affected (0.04 sec)
             
            MariaDB [openquery]>  SELECT * from tt_temp;
            Empty set (0.00 sec)
             
            MariaDB [openquery]> EXPLAIN INSERT INTO tt_temp (value) select ('a')  union all select  ('b')  union all select  ('c')  union all select  ('c')  union all select  ('c')  union all select  ('c')  union all select  ('c')  union all select  ('c')  union all select  ('c');
            +------+--------------+--------------------------+------+---------------+------+---------+------+------+----------------+
            | id   | select_type  | table                    | type | possible_keys | key  | key_len | ref  | rows | Extra          |
            +------+--------------+--------------------------+------+---------------+------+---------+------+------+----------------+
            |    1 | PRIMARY      | NULL                     | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
            |    2 | UNION        | NULL                     | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
            |    3 | UNION        | NULL                     | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
            |    4 | UNION        | NULL                     | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
            |    5 | UNION        | NULL                     | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
            |    6 | UNION        | NULL                     | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
            |    7 | UNION        | NULL                     | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
            |    8 | UNION        | NULL                     | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
            |    9 | UNION        | NULL                     | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
            | NULL | UNION RESULT | <union1,2,3,4,5,6,7,8,9> | ALL  | NULL          | NULL | NULL    | NULL | NULL |                |
            +------+--------------+--------------------------+------+---------------+------+---------+------+------+----------------+
            10 rows in set (0.01 sec)
             
            MariaDB [openquery]> SELECT * from tt_temp;
            +----+-------+
            | id | value |
            +----+-------+
            |  1 | b     |
            |  2 | c     |
            |  3 | c     |
            |  4 | c     |
            |  5 | c     |
            |  6 | c     |
            |  7 | c     |
            |  8 | c     |
            +----+-------+
            8 rows in set (0.00 sec)

            note 'a' missing from table. Thanks to jkavalik for the test case on irc.

            danblack Daniel Black added a comment - - edited MariaDB [(none)]> set sql_log_bin=0;   MariaDB [openquery]> select @@version; +---------------------------+ | @@version | +---------------------------+ | 10.0.21-MariaDB-wsrep-log | +---------------------------+ 1 row in set (0.00 sec)   MariaDB [openquery]> CREATE TEMPORARY TABLE tt_temp ( id BIGINT AUTO_INCREMENT PRIMARY KEY , value VARCHAR(2) ) AUTO_INCREMENT = 1; Query OK, 0 rows affected (0.04 sec)   MariaDB [openquery]> SELECT * from tt_temp; Empty set (0.00 sec)   MariaDB [openquery]> EXPLAIN INSERT INTO tt_temp (value) select ('a') union all select ('b') union all select ('c') union all select ('c') union all select ('c') union all select ('c') union all select ('c') union all select ('c') union all select ('c'); +------+--------------+--------------------------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+--------------------------+------+---------------+------+---------+------+------+----------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 2 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 3 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 4 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 5 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 6 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 7 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 8 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 9 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | NULL | UNION RESULT | <union1,2,3,4,5,6,7,8,9> | ALL | NULL | NULL | NULL | NULL | NULL | | +------+--------------+--------------------------+------+---------------+------+---------+------+------+----------------+ 10 rows in set (0.01 sec)   MariaDB [openquery]> SELECT * from tt_temp; +----+-------+ | id | value | +----+-------+ | 1 | b | | 2 | c | | 3 | c | | 4 | c | | 5 | c | | 6 | c | | 7 | c | | 8 | c | +----+-------+ 8 rows in set (0.00 sec) note 'a' missing from table. Thanks to jkavalik for the test case on irc.
            danblack Daniel Black added a comment - - edited

            added test case above. config as per MDEV-8746 wsrep_provider=none

            danblack Daniel Black added a comment - - edited added test case above. config as per MDEV-8746 wsrep_provider=none
            jkavalik Jiri Kavalik added a comment - - edited

            I am able to reproduce it on 10.0.21-MariaDB-1~precise-log mariadb.org binary distribution - Ubuntu 12.04.5 64bit (kernel 3.2.0-84-generic)

            I am not sure if it is some symptom or irrelevant coincidence but show create table tt_temp; shows (note the auto_increment value

            CREATE TEMPORARY TABLE `tt_temp` (
              `id` bigint(20) NOT NULL AUTO_INCREMENT,
              `value` varchar(2) DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

            and subsequent insert really adds rows from id 16 up, skipping some auto_increment values - I know gaps in auto_increment are not wrong by themselves but I mention it in case it could hint to something.

            jkavalik Jiri Kavalik added a comment - - edited I am able to reproduce it on 10.0.21-MariaDB-1~precise-log mariadb.org binary distribution - Ubuntu 12.04.5 64bit (kernel 3.2.0-84-generic) I am not sure if it is some symptom or irrelevant coincidence but show create table tt_temp; shows (note the auto_increment value CREATE TEMPORARY TABLE `tt_temp` ( `id` bigint (20) NOT NULL AUTO_INCREMENT, `value` varchar (2) DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 and subsequent insert really adds rows from id 16 up, skipping some auto_increment values - I know gaps in auto_increment are not wrong by themselves but I mention it in case it could hint to something.
            elenst Elena Stepanova added a comment - It was fixed in scope of MDEV-6223 / MDEV-8321 : https://github.com/MariaDB/server/commit/1289794799e7ad5ae4f6a26a545dff7b77ee8637

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              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.