[MDEV-7215] EXPLAIN REPLACE produces an error: Column count doesn't match value count Created: 2014-11-26  Updated: 2019-11-19  Resolved: 2015-12-10

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.15
Fix Version/s: 10.0.23, 10.1.10

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-21077 explain SQL return an error while the... Closed
Relates
relates to MDEV-7220 Materialization strategy is not used ... Closed
Sprint: 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



 Comments   
Comment by Sergei Petrunia [ 2014-11-26 ]

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.

Comment by Sergei Petrunia [ 2014-11-26 ]

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.

Comment by Sergei Petrunia [ 2014-11-26 ]

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);

Comment by Sergei Petrunia [ 2015-11-06 ]

Still repeatable on current 10.1.

Comment by Oleksandr Byelkin [ 2015-11-12 ]

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

Comment by Oleksandr Byelkin [ 2015-11-13 ]

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)

—

Comment by Sergei Petrunia [ 2015-12-10 ]

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?

Generated at Thu Feb 08 07:17:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.