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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.15
Fix Version/s: 10.0.23, 10.1.10
Component/s: Optimizer
Labels:
None

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

Attachments

Issue Links

is duplicated by

MDEV-21077 explain SQL return an error while the SQL without explain can be executed correctly

Closed

relates to

MDEV-7220 Materialization strategy is not used for REPLACE ... SELECT

Closed

Activity

Ascending order - Click to sort in descending order

Sergei Petrunia created issue - 2014-11-26 13:34

Sergei Petrunia made changes - 2014-11-26 13:36

Field	Original Value	New Value
Component/s		Optimizer [ 10200 ]
Fix Version/s		10.0 [ 16000 ]
Affects Version/s		10.0.15 [ 17300 ]
Assignee		Sergei Petrunia [ psergey ]
Description		Filing based on [~elenst]'s example in CSC#8345: {noformat} drop table if exists t1, t2; create table t1 (a int); insert into t1 values (1); create table t2 (b int, c int); {noformat} {noformat} 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 {noformat}
Summary	EXPLAI	EXPLAIN REPLACE produces an error: Column count doesn't match value count

Sergei Petrunia added a comment - 2014-11-26 13:44

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.

Sergei Petrunia added a comment - 2014-11-26 13:44 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.

Sergei Petrunia added a comment - 2014-11-26 13:55

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.

Sergei Petrunia added a comment - 2014-11-26 13:55 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.

Sergei Petrunia made changes - 2014-11-26 13:55

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Petrunia added a comment - 2014-11-26 16:22

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

Sergei Petrunia added a comment - 2014-11-26 16:22 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);

Sergei Petrunia made changes - 2014-11-26 17:57

Link

This issue relates to ~~MDEV-7220~~ [ ~~MDEV-7220~~ ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 58735 ]

MariaDB v3 [ 65284 ]

Rasmus Johansson (Inactive) made changes - 2015-10-21 10:45

Sprint

10.0.22 [ 17 ]

Rasmus Johansson (Inactive) made changes - 2015-10-21 10:45

Rank

Ranked lower

Rasmus Johansson (Inactive) made changes - 2015-10-28 11:26

Sprint

10.0.22 [ 17 ]

10.0.22, 10.1.9 [ 17, 18 ]

Rasmus Johansson (Inactive) made changes - 2015-10-28 11:26

Rank

Ranked higher

Rasmus Johansson (Inactive) made changes - 2015-11-04 13:10

Sprint

10.0.22, 10.1.9-1 [ 17, 18 ]

10.0.22, 10.1.9-1, 10.1.9-2 [ 17, 18, 19 ]

Sergei Petrunia added a comment - 2015-11-06 16:03

Still repeatable on current 10.1.

Sergei Petrunia added a comment - 2015-11-06 16:03 Still repeatable on current 10.1.

Rasmus Johansson (Inactive) made changes - 2015-11-11 10:55

Sprint

10.0.22, 10.1.9-1, 10.1.9-2 [ 17, 18, 19 ]

10.0.22, 10.1.9-1, 10.1.9-2, 10.1.9-3 [ 17, 18, 19, 20 ]

Rasmus Johansson (Inactive) made changes - 2015-11-11 10:57

Sprint

10.0.22, 10.1.9-1, 10.1.9-2, 10.1.9-3 [ 17, 18, 19, 20 ]

10.0.22, 10.1.9-1, 10.1.9-2 [ 17, 18, 19 ]

Rasmus Johansson (Inactive) made changes - 2015-11-11 10:57

Rank

Ranked lower

Oleksandr Byelkin made changes - 2015-11-11 13:18

Assignee

Sergei Petrunia [ psergey ]

Oleksandr Byelkin [ sanja ]

Oleksandr Byelkin made changes - 2015-11-11 13:19

Sprint

10.0.22, 10.1.9-1, 10.1.9-2 [ 17, 18, 19 ]

10.0.22, 10.1.9-1, 10.1.9-2, 10.1.9-3 [ 17, 18, 19, 20 ]

Oleksandr Byelkin made changes - 2015-11-11 13:19

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2015-11-12 11:41

Fix Version/s		10.1 [ 16100 ]
Fix Version/s	10.0 [ 16000 ]

Oleksandr Byelkin made changes - 2015-11-12 11:41

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2015-11-12 14:27

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

Oleksandr Byelkin added a comment - 2015-11-12 14:27 Second call ofcheck_insert_fields() is against subquery which is incorrect.

Oleksandr Byelkin added a comment - 2015-11-13 18:49

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)

—

Oleksandr Byelkin added a comment - 2015-11-13 18:49 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) —

Oleksandr Byelkin made changes - 2015-11-13 18:50

Assignee	Oleksandr Byelkin [ sanja ]	Sergei Petrunia [ psergey ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Rasmus Johansson (Inactive) made changes - 2015-11-18 11:20

Rank

Ranked higher

Rasmus Johansson (Inactive) made changes - 2015-12-09 13:13

Sprint

10.0.22, 10.1.9-1, 10.1.9-2, 10.1.9-3 [ 17, 18, 19, 20 ]

10.0.22, 10.1.9-1, 10.1.9-2, 10.1.9-3, 10.0.23-1 [ 17, 18, 19, 20, 23 ]

Rasmus Johansson (Inactive) made changes - 2015-12-09 13:13

Rank

Ranked lower

Sergei Petrunia added a comment - 2015-12-10 00:59

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?

Sergei Petrunia added a comment - 2015-12-10 00:59 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?

Sergei Petrunia made changes - 2015-12-10 00:59

Assignee	Sergei Petrunia [ psergey ]	Oleksandr Byelkin [ sanja ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2015-12-10 12:30

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2015-12-10 14:39

Fix Version/s		10.0.23 [ 20401 ]
Fix Version/s		10.1.10 [ 20402 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Alice Sherepa made changes - 2019-11-19 11:19

Link

This issue is duplicated by ~~MDEV-21077~~ [ ~~MDEV-21077~~ ]

Sergei Golubchik made changes - 2021-12-06 21:40

Workflow

MariaDB v3 [ 65284 ]

MariaDB v4 [ 148551 ]

People

Assignee:: Oleksandr Byelkin

Reporter:: Sergei Petrunia

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2014-11-26 13:34

Updated:: 2019-11-19 11:19

Resolved:: 2015-12-10 14:39

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration