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

EXPLAIN REPLACE produces an error: Column count doesn't match value count

Details

    • 10.0.22, 10.1.9-1, 10.1.9-2, 10.1.9-3, 10.0.23

    Description

      Filing based on elenst's example in CSC#8345: 

      drop table if exists t1, t2;
      		 
      create table t1 (a int);
      		 
      insert into t1 values (1);
      		 
      create table t2 (b int, c int);
      		 

      MariaDB [test]> replace into t2 select 100, (select a from t1);
      		 
      Query OK, 1 row affected (0.05 sec)
      		 
      Records: 1  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [test]> explain replace into t2 select 100, (select a from t1);
      		 
      ERROR 1136 (21S01): Column count doesn't match value count at row 1

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21077 explain SQL return an error while the SQL without explain can be executed correctly

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7220 Materialization strategy is not used for REPLACE ... SELECT

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia added a comment -

            The error is generated here:

            (gdb) wher
            		 
              #0  my_error (nr=1136, MyFlags=0) at /home/psergey/dev2/10.0/mysys/my_error.c:113
            		 
              #1  0x00000000006438ad in check_insert_fields (thd=0x31d1050, table_list=0x7fffa00068a0, fields=..., values=..., check_unique=true, fields_and_values_from_different_maps=true, map=0x7fffe0066300) at /home/psergey/dev2/10.0/sql/sql_insert.cc:215
            		 
              #2  0x000000000064b548 in select_insert::prepare (this=0x7fffa0008288, values=..., u=0x7fffa00072a0) at /home/psergey/dev2/10.0/sql/sql_insert.cc:3377
            		 
              #3  0x00000000006d5b2a in JOIN::change_result (this=0x7fffa00755a0, res=0x7fffa0008288) at /home/psergey/dev2/10.0/sql/sql_select.cc:24412
            		 
              #4  0x00000000006a1590 in mysql_select (thd=0x31d1050, rref_pointer_array=0x7fffa00071a0, tables=0x7fffa0007ad8, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7fffa0008288, unit=0x7fffa00072a0, select_lex=0x7fffa0006f28) at /home/psergey/dev2/10.0/sql/sql_select.cc:3248
            		 
              #5  0x00000000006d4528 in mysql_explain_union (thd=0x31d1050, unit=0x7fffa00072a0, result=0x7fffa0008288) at /home/psergey/dev2/10.0/sql/sql_select.cc:23924
            		 
              #6  0x00000000006d4287 in select_describe (join=0x7fffa0075068, need_tmp_table=false, need_order=false, distinct=false, message=0x103585c "No tables used") at /home/psergey/dev2/10.0/sql/sql_select.cc:23881
            		 
              #7  0x000000000069e670 in JOIN::exec_inner (this=0x7fffa0075068) at /home/psergey/dev2/10.0/sql/sql_select.cc:2431
            		 
              #8  0x000000000069e2aa in JOIN::exec (this=0x7fffa0075068) at /home/psergey/dev2/10.0/sql/sql_select.cc:2370
            		 
              #9  0x00000000006a1869 in mysql_select (thd=0x31d1050, rref_pointer_array=0x31d56c0, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=3489925892, result=0x7fffa0008288, unit=0x31d4d60, select_lex=0x31d5448) at /home/psergey/dev2/10.0/sql/sql_select.cc:3308
            		 
              #10 0x0000000000697b7b in handle_select (thd=0x31d1050, lex=0x31d4c98, result=0x7fffa0008288, setup_tables_done_option=1073741824) at /home/psergey/dev2/10.0/sql/sql_select.cc:373
            		 
              #11 0x0000000000665c9f in mysql_execute_command (thd=0x31d1050) at /home/psergey/dev2/10.0/sql/sql_parse.cc:3519
            		 
              #12 0x000000000066dd51 in mysql_parse (thd=0x31d1050, rawbuf=0x7fffa0006788 "explain replace into t2 select 100, (select a from t1)", length=54, parser_state=0x7fffe00674f0) at /home/psergey/dev2/10.0/sql/sql_parse.cc:6407

            select_describe(join, join->select_lex->select_number=1) calls
            mysql_explain_union for the subquery.

            mysql_explain_union calls mysql_select, which makes this call

            	//here is EXPLAIN of subselect or derived table
            		 
            	if (join->change_result(result))
            		 
            	{
            		 
            	  DBUG_RETURN(TRUE);
            		 
            	}

            where the result is select_insert object. The number of columns select_insert
            expects is not (and doesn't have to be) equal to number of columns produced by
            the subquery, and we get an error.

            psergei Sergei Petrunia added a comment - The error is generated here: (gdb) wher #0 my_error (nr=1136, MyFlags=0) at /home/psergey/dev2/10.0/mysys/my_error.c:113 #1 0x00000000006438ad in check_insert_fields (thd=0x31d1050, table_list=0x7fffa00068a0, fields=..., values=..., check_unique=true, fields_and_values_from_different_maps=true, map=0x7fffe0066300) at /home/psergey/dev2/10.0/sql/sql_insert.cc:215 #2 0x000000000064b548 in select_insert::prepare (this=0x7fffa0008288, values=..., u=0x7fffa00072a0) at /home/psergey/dev2/10.0/sql/sql_insert.cc:3377 #3 0x00000000006d5b2a in JOIN::change_result (this=0x7fffa00755a0, res=0x7fffa0008288) at /home/psergey/dev2/10.0/sql/sql_select.cc:24412 #4 0x00000000006a1590 in mysql_select (thd=0x31d1050, rref_pointer_array=0x7fffa00071a0, tables=0x7fffa0007ad8, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7fffa0008288, unit=0x7fffa00072a0, select_lex=0x7fffa0006f28) at /home/psergey/dev2/10.0/sql/sql_select.cc:3248 #5 0x00000000006d4528 in mysql_explain_union (thd=0x31d1050, unit=0x7fffa00072a0, result=0x7fffa0008288) at /home/psergey/dev2/10.0/sql/sql_select.cc:23924 #6 0x00000000006d4287 in select_describe (join=0x7fffa0075068, need_tmp_table=false, need_order=false, distinct=false, message=0x103585c "No tables used") at /home/psergey/dev2/10.0/sql/sql_select.cc:23881 #7 0x000000000069e670 in JOIN::exec_inner (this=0x7fffa0075068) at /home/psergey/dev2/10.0/sql/sql_select.cc:2431 #8 0x000000000069e2aa in JOIN::exec (this=0x7fffa0075068) at /home/psergey/dev2/10.0/sql/sql_select.cc:2370 #9 0x00000000006a1869 in mysql_select (thd=0x31d1050, rref_pointer_array=0x31d56c0, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=3489925892, result=0x7fffa0008288, unit=0x31d4d60, select_lex=0x31d5448) at /home/psergey/dev2/10.0/sql/sql_select.cc:3308 #10 0x0000000000697b7b in handle_select (thd=0x31d1050, lex=0x31d4c98, result=0x7fffa0008288, setup_tables_done_option=1073741824) at /home/psergey/dev2/10.0/sql/sql_select.cc:373 #11 0x0000000000665c9f in mysql_execute_command (thd=0x31d1050) at /home/psergey/dev2/10.0/sql/sql_parse.cc:3519 #12 0x000000000066dd51 in mysql_parse (thd=0x31d1050, rawbuf=0x7fffa0006788 "explain replace into t2 select 100, (select a from t1)", length=54, parser_state=0x7fffe00674f0) at /home/psergey/dev2/10.0/sql/sql_parse.cc:6407 select_describe(join, join->select_lex->select_number=1) calls mysql_explain_union for the subquery. mysql_explain_union calls mysql_select, which makes this call //here is EXPLAIN of subselect or derived table if (join->change_result(result)) { DBUG_RETURN(TRUE); } where the result is select_insert object. The number of columns select_insert expects is not (and doesn't have to be) equal to number of columns produced by the subquery, and we get an error.
            psergei Sergei Petrunia added a comment -

            select_insert::prepare is called twice.

            The first call is from here:

              #0  select_insert::prepare (this=0x7fffa0008288, values=..., u=0x31d4d60) at /home/psergey/dev2/10.0/sql/sql_insert.cc:3364
            		 
              #1  0x0000000000699753 in JOIN::prepare (this=0x7fffa0075068, rref_pointer_array=0x31d56c0, tables_init=0x0, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x31d5448, unit_arg=0x31d4d60) at /home/psergey/dev2/10.0/sql/sql_select.cc:967
            		 
              #2  0x00000000006a17bb in mysql_select (thd=0x31d1050, rref_pointer_array=0x31d56c0, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=3489925892, result=0x7fffa0008288, unit=0x31d4d60, select_lex=0x31d5448) at /home/psergey/dev2/10.0/sql/sql_select.cc:3286
            		 
              #3  0x0000000000697b7b in handle_select (thd=0x31d1050, lex=0x31d4c98, result=0x7fffa0008288, setup_tables_done_option=1073741824) at /home/psergey/dev2/10.0/sql/sql_select.cc:373

            and it succeeds.

            For the second call, see the previous comment. The second call should not have
            been made.

            psergei Sergei Petrunia added a comment - select_insert::prepare is called twice. The first call is from here: #0 select_insert::prepare (this=0x7fffa0008288, values=..., u=0x31d4d60) at /home/psergey/dev2/10.0/sql/sql_insert.cc:3364 #1 0x0000000000699753 in JOIN::prepare (this=0x7fffa0075068, rref_pointer_array=0x31d56c0, tables_init=0x0, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x31d5448, unit_arg=0x31d4d60) at /home/psergey/dev2/10.0/sql/sql_select.cc:967 #2 0x00000000006a17bb in mysql_select (thd=0x31d1050, rref_pointer_array=0x31d56c0, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=3489925892, result=0x7fffa0008288, unit=0x31d4d60, select_lex=0x31d5448) at /home/psergey/dev2/10.0/sql/sql_select.cc:3286 #3 0x0000000000697b7b in handle_select (thd=0x31d1050, lex=0x31d4c98, result=0x7fffa0008288, setup_tables_done_option=1073741824) at /home/psergey/dev2/10.0/sql/sql_select.cc:373 and it succeeds. For the second call, see the previous comment. The second call should not have been made.
            psergei Sergei Petrunia added a comment -

            BTW, EXPLAIN-in-the-slow-query-log feature is not affected by this:

            ...
            		 
            # Query_time: 0.000276  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0
            		 
            SET timestamp=1417011689;
            		 
            set log_slow_verbosity='explain,query_plan';
            		 
            # Time: 141126 17:21:33
            		 
            # User@Host: root[root] @ localhost []
            		 
            # Thread_id: 2  Schema: j10  QC_hit: No
            		 
            # Query_time: 0.069501  Lock_time: 0.001863  Rows_sent: 0  Rows_examined: 0
            		 
            # Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
            		 
            # Filesort: No  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No
            		 
            #
            		 
            # explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    Extra
            		 
            # explain: 1    PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
            		 
            # explain: 2    SUBQUERY        t1      ALL     NULL    NULL    NULL    NULL    1       
            #
            		 
            SET timestamp=1417011693;
            		 
            replace into t2 select 100, (select a from t1);

            psergei Sergei Petrunia added a comment - BTW, EXPLAIN-in-the-slow-query-log feature is not affected by this: ... # Query_time: 0.000276 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 SET timestamp=1417011689; set log_slow_verbosity='explain,query_plan'; # Time: 141126 17:21:33 # User@Host: root[root] @ localhost [] # Thread_id: 2 Schema: j10 QC_hit: No # Query_time: 0.069501 Lock_time: 0.001863 Rows_sent: 0 Rows_examined: 0 # Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No # Filesort: No Filesort_on_disk: No Merge_passes: 0 Priority_queue: No # # explain: id select_type table type possible_keys key key_len ref rows Extra # explain: 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used # explain: 2 SUBQUERY t1 ALL NULL NULL NULL NULL 1 # SET timestamp=1417011693; replace into t2 select 100, (select a from t1);
            psergei Sergei Petrunia added a comment -

            Still repeatable on current 10.1.

            psergei Sergei Petrunia added a comment - Still repeatable on current 10.1.
            sanja Oleksandr Byelkin added a comment -

            Second call ofcheck_insert_fields() is against subquery which is incorrect.

            sanja Oleksandr Byelkin added a comment - Second call ofcheck_insert_fields() is against subquery which is incorrect.
            sanja Oleksandr Byelkin added a comment -

            revision-id: 4e3fa980d47fa511ab2b4b41b7a16b63e27acb2a (mariadb-10.1.8-49-g4e3fa98)
            parent(s): a430df3aba59c57b0756c25b1586d880d19286df
            committer: Oleksandr Byelkin
            timestamp: 2015-11-13 17:47:46 +0100
            message:

            MDEV-7215 EXPLAIN REPLACE produces an error: Column count doesn't match value count

            removed outdated code which was cousing error (no need reassign result for subqueries & Co)

            —

            sanja Oleksandr Byelkin added a comment - revision-id: 4e3fa980d47fa511ab2b4b41b7a16b63e27acb2a (mariadb-10.1.8-49-g4e3fa98) parent(s): a430df3aba59c57b0756c25b1586d880d19286df committer: Oleksandr Byelkin timestamp: 2015-11-13 17:47:46 +0100 message: MDEV-7215 EXPLAIN REPLACE produces an error: Column count doesn't match value count removed outdated code which was cousing error (no need reassign result for subqueries & Co) —
            psergei Sergei Petrunia added a comment -

            The patch looks ok.

            One thing I'm wondering about: AffectsVersion is 10.0, but FixVersion is 10.1 and the patch is against 10.1. So, we don't fix it in 10.0, because it's not critical there?

            psergei Sergei Petrunia added a comment - The patch looks ok. One thing I'm wondering about: AffectsVersion is 10.0, but FixVersion is 10.1 and the patch is against 10.1. So, we don't fix it in 10.0, because it's not critical there?

            People

              sanja Oleksandr Byelkin
              psergei Sergei Petrunia
              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.