Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.4(EOL), 10.5, 10.6, 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL)
Description
It is possible that the problem is limited to degenerate use cases like the one below. If the analysis confirms it, it can be demoted from a blocker.
CREATE TABLE t (f INT); |
INSERT INTO t VALUES (1),(2); # Optional, fails either way |
CREATE VIEW v1 AS SELECT 1 AS a; |
CREATE VIEW v2 AS SELECT a FROM v1; |
|
|
PREPARE stmt FROM "SELECT * FROM v2 HAVING 1 IN (SELECT f FROM t)"; |
EXECUTE stmt; |
EXECUTE stmt; |
|
|
# Cleanup
|
DROP VIEW v1; |
DROP VIEW v2; |
DROP TABLE t; |
|
10.4 c6ef9b1c debug |
mysqld: /data/src/10.4/sql/table.cc:6470: void TABLE_LIST::set_check_materialized(): Assertion `!derived->is_excluded()' failed.
|
230504 17:12:19 [ERROR] mysqld got signal 6 ;
|
|
|
#9 0x00007fc497253df2 in __GI___assert_fail (assertion=0x55b58203ea60 "!derived->is_excluded()", file=0x55b582039240 "/data/src/10.4/sql/table.cc", line=6470, function=0x55b58203ea00 "void TABLE_LIST::set_check_materialized()") at ./assert/assert.c:101
|
#10 0x000055b58023deb4 in TABLE_LIST::set_check_materialized (this=0x62b00008e4f8) at /data/src/10.4/sql/table.cc:6470
|
#11 0x000055b57fdaa1ff in TABLE_LIST::set_materialized_derived (this=0x62b00008e4f8) at /data/src/10.4/sql/table.h:2883
|
#12 0x000055b580253187 in TABLE_LIST::init_derived (this=0x62b00008e4f8, thd=0x62b00005b208, init_view=true) at /data/src/10.4/sql/table.cc:9241
|
#13 0x000055b57fe3821e in mysql_derived_init (thd=0x62b00005b208, lex=0x62b00008c238, derived=0x62b00008e4f8) at /data/src/10.4/sql/sql_derived.cc:564
|
#14 0x000055b57fe35aec in mysql_handle_derived (lex=0x62b00008c238, phases=1) at /data/src/10.4/sql/sql_derived.cc:123
|
#15 0x000055b57fd8e8de in open_and_lock_tables (thd=0x62b00005b208, options=..., tables=0x62b00008e4f8, derived=true, flags=0, prelocking_strategy=0x7fc48f4daa00) at /data/src/10.4/sql/sql_base.cc:5353
|
#16 0x000055b57fce8616 in open_and_lock_tables (thd=0x62b00005b208, tables=0x62b00008e4f8, derived=true, flags=0) at /data/src/10.4/sql/sql_base.h:503
|
#17 0x000055b57ff216a3 in execute_sqlcom_select (thd=0x62b00005b208, all_tables=0x62b00008e4f8) at /data/src/10.4/sql/sql_parse.cc:6384
|
#18 0x000055b57ff0f638 in mysql_execute_command (thd=0x62b00005b208) at /data/src/10.4/sql/sql_parse.cc:3966
|
#19 0x000055b57ff7ee7f in Prepared_statement::execute (this=0x619000084388, expanded_query=0x7fc48f4dcb30, open_cursor=false) at /data/src/10.4/sql/sql_prepare.cc:5024
|
#20 0x000055b57ff7a4bf in Prepared_statement::execute_loop (this=0x619000084388, expanded_query=0x7fc48f4dcb30, open_cursor=false, packet=0x0, packet_end=0x0) at /data/src/10.4/sql/sql_prepare.cc:4493
|
#21 0x000055b57ff74335 in mysql_sql_stmt_execute (thd=0x62b00005b208) at /data/src/10.4/sql/sql_prepare.cc:3577
|
#22 0x000055b57ff0f67d in mysql_execute_command (thd=0x62b00005b208) at /data/src/10.4/sql/sql_parse.cc:3982
|
#23 0x000055b57ff2b2fb in mysql_parse (thd=0x62b00005b208, rawbuf=0x62b000062228 "EXECUTE stmt", length=12, parser_state=0x7fc48f4de860, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7998
|
#24 0x000055b57ff017a7 in dispatch_command (command=COM_QUERY, thd=0x62b00005b208, packet=0x62900023f209 "EXECUTE stmt", packet_length=12, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1857
|
#25 0x000055b57fefe320 in do_command (thd=0x62b00005b208) at /data/src/10.4/sql/sql_parse.cc:1378
|
#26 0x000055b5802fc31c in do_handle_one_connection (connect=0x6080000009a8) at /data/src/10.4/sql/sql_connect.cc:1420
|
#27 0x000055b5802fbc33 in handle_one_connection (arg=0x6080000009a8) at /data/src/10.4/sql/sql_connect.cc:1324
|
#28 0x000055b580f6674c in pfs_spawn_thread (arg=0x615000003508) at /data/src/10.4/storage/perfschema/pfs.cc:1869
|
#29 0x00007fc4972a7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
|
#30 0x00007fc4973285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
|
|
10.4 c6ef9b1c non-debug |
#2 <signal handler called>
|
#3 0x000055871ef47ec6 in st_select_lex::mark_as_dependent (this=this@entry=0x7fb77407e478, thd=thd@entry=0x7fb774000c58, last=last@entry=0x7fb77407e478, dependency=0x7fb77407e950) at /data/src/10.4/sql/sql_lex.cc:2831
|
#4 0x000055871f18ce00 in mark_as_dependent (thd=thd@entry=0x7fb774000c58, last=0x7fb77407e478, current=0x7fb77407e478, resolved_item=resolved_item@entry=0x7fb77407e950, mark_item=<optimized out>, suppress_warning_output=suppress_warning_output@entry=false) at /data/src/10.4/sql/item.cc:5119
|
#5 0x000055871f19d88d in Item_field::fix_outer_field (this=this@entry=0x7fb77407e950, thd=thd@entry=0x7fb774000c58, from_field=from_field@entry=0x7fb784c8f9d0, reference=reference@entry=0x7fb7741283e8) at /data/src/10.4/sql/item.cc:5810
|
#6 0x000055871f19e347 in Item_field::fix_fields (this=0x7fb77407e950, thd=0x7fb774000c58, reference=0x7fb7741283e8) at /data/src/10.4/sql/item.cc:6013
|
#7 0x000055871f19f06c in Item::fix_fields_if_needed (ref=0x7fb7741283e8, thd=0x7fb774000c58, this=0x7fb77407e950) at /data/src/10.4/sql/item.h:964
|
#8 Item::fix_fields_if_needed (ref=0x7fb7741283e8, thd=0x7fb774000c58, this=0x7fb77407e950) at /data/src/10.4/sql/item.h:964
|
#9 Item_direct_view_ref::fix_fields (this=0x7fb7740883b0, thd=0x7fb774000c58, reference=0x7fb77407bb50) at /data/src/10.4/sql/item.cc:9071
|
#10 0x000055871ef0eac9 in Item::fix_fields_if_needed (ref=0x7fb77407bb50, thd=0x7fb774000c58, this=0x7fb7740883b0) at /data/src/10.4/sql/item.h:966
|
#11 Item::fix_fields_if_needed (ref=0x7fb77407bb50, thd=0x7fb774000c58, this=0x7fb7740883b0) at /data/src/10.4/sql/item.h:964
|
#12 Item::fix_fields_if_needed_for_scalar (ref=0x7fb77407bb50, thd=0x7fb774000c58, this=0x7fb7740883b0) at /data/src/10.4/sql/item.h:970
|
#13 setup_fields (thd=0x7fb774000c58, ref_pointer_array=..., fields=..., column_usage=column_usage@entry=MARK_COLUMNS_READ, sum_func_list=sum_func_list@entry=0x7fb774010618, pre_fix=<optimized out>, allow_sum_func=<optimized out>) at /data/src/10.4/sql/sql_base.cc:7738
|
#14 0x000055871efc7a61 in JOIN::prepare (this=this@entry=0x7fb774010300, tables_init=tables_init@entry=0x7fb77407bb90, wild_num=wild_num@entry=0, conds_init=conds_init@entry=0x0, og_num=og_num@entry=0, order_init=order_init@entry=0x0, skip_order_by=<optimized out>, group_init=<optimized out>, having_init=<optimized out>, proc_param_init=<optimized out>, select_lex_arg=<optimized out>, unit_arg=<optimized out>) at /data/src/10.4/sql/sql_select.cc:1308
|
#15 0x000055871efdb589 in mysql_select (thd=thd@entry=0x7fb774000c58, tables=<optimized out>, wild_num=<optimized out>, fields=..., conds=<optimized out>, og_num=<optimized out>, order=<optimized out>, group=<optimized out>, having=<optimized out>, proc_param=<optimized out>, select_options=<optimized out>, result=<optimized out>, unit=<optimized out>, select_lex=<optimized out>) at /data/src/10.4/sql/sql_select.cc:4774
|
#16 0x000055871efdb904 in handle_select (thd=thd@entry=0x7fb774000c58, lex=lex@entry=0x7fb774079928, result=result@entry=0x7fb77407e348, setup_tables_done_option=setup_tables_done_option@entry=0) at /data/src/10.4/sql/sql_select.cc:442
|
#17 0x000055871ef637a0 in execute_sqlcom_select (thd=thd@entry=0x7fb774000c58, all_tables=0x7fb77407bb90) at /data/src/10.4/sql/sql_parse.cc:6463
|
#18 0x000055871ef720bb in mysql_execute_command (thd=0x7fb774000c58) at /data/src/10.4/sql/sql_parse.cc:3966
|
#19 0x000055871ef89c66 in Prepared_statement::execute (this=this@entry=0x7fb774115338, expanded_query=expanded_query@entry=0x7fb784c91350, open_cursor=open_cursor@entry=false) at /data/src/10.4/sql/sql_prepare.cc:5024
|
#20 0x000055871ef89de5 in Prepared_statement::execute_loop (packet=<optimized out>, packet_end=<optimized out>, open_cursor=<optimized out>, expanded_query=0x7fb784c91350, this=0x7fb774115338) at /data/src/10.4/sql/sql_prepare.cc:4493
|
#21 Prepared_statement::execute_loop (this=0x7fb774115338, expanded_query=0x7fb784c91350, open_cursor=<optimized out>, packet=<optimized out>, packet_end=<optimized out>) at /data/src/10.4/sql/sql_prepare.cc:4442
|
#22 0x000055871ef8a11b in mysql_sql_stmt_execute (thd=thd@entry=0x7fb774000c58) at /data/src/10.4/sql/sql_prepare.cc:3577
|
#23 0x000055871ef6fb85 in mysql_execute_command (thd=thd@entry=0x7fb774000c58) at /data/src/10.4/sql/sql_parse.cc:3982
|
#24 0x000055871ef743e1 in mysql_parse (thd=0x7fb774000c58, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /data/src/10.4/sql/sql_parse.cc:7998
|
#25 0x000055871ef76915 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7fb774000c58, packet=packet@entry=0x7fb774007d59 "EXECUTE stmt", packet_length=packet_length@entry=12, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /data/src/10.4/sql/sql_parse.cc:1958
|
#26 0x000055871ef78bbf in do_command (thd=0x7fb774000c58) at /data/src/10.4/sql/sql_parse.cc:1378
|
#27 0x000055871f071c7e in do_handle_one_connection (connect=connect@entry=0x5587222445a8) at /data/src/10.4/sql/sql_connect.cc:1420
|
#28 0x000055871f071d9d in handle_one_connection (arg=arg@entry=0x5587222445a8) at /data/src/10.4/sql/sql_connect.cc:1324
|
#29 0x000055871f402fab in pfs_spawn_thread (arg=0x55872219d818) at /data/src/10.4/storage/perfschema/pfs.cc:1869
|
#30 0x00007fb78aca7fd4 in start_thread (arg=<optimized out>) at ./nptl/pthread_create.c:442
|
#31 0x00007fb78ad285bc in clone3 () at ../sysdeps/unix/sysv/linux/x86_64/clone3.S:81
|
Reproducible on debug- and non-debug build as shown above, with at least MyISAM, InnoDB, Aria.
The failure started happening on 10.4 branch after this commit:
commit ccec9b1de95a66b7597bc30e0a60bd61866f225d
|
Author: Igor Babaev
|
Date: Wed Mar 1 22:49:27 2023 -0800
|
|
|
MDEV-30706 Different results of selects from view and CTE with same definition
|
MDEV-30668 Set function aggregated in outer select used in view definition
|
Attachments
Activity
diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result
|
index 8c31545eb84..97d19aa2690 100644
|
--- a/mysql-test/main/view.result
|
+++ b/mysql-test/main/view.result
|
@@ -6956,4 +6956,22 @@ create algorithm=merge view v as
|
select * from t1 left join t2 on t1.a=t2.b and t1.a in (select d from t3);
|
ERROR 42S22: Unknown column 'd' in 'field list'
|
drop table t1,t2,t3;
|
+#
|
+# MDEV-31189: Server crash or assertion failure in upon 2nd
|
+# execution of PS with views and HAVING
|
+#
|
+CREATE TABLE t (f INT);
|
+INSERT INTO t VALUES (1),(2);
|
+CREATE VIEW v1 AS SELECT 1 AS a;
|
+CREATE VIEW v2 AS SELECT a FROM v1;
|
+PREPARE stmt FROM "SELECT * FROM v2 HAVING 1 IN (SELECT f FROM t)";
|
+EXECUTE stmt;
|
+a
|
+1
|
+EXECUTE stmt;
|
+a
|
+1
|
+DROP VIEW v1;
|
+DROP VIEW v2;
|
+DROP TABLE t;
|
# End of 10.4 tests
|
diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test
|
index 0e2dce1fb70..385ca523436 100644
|
--- a/mysql-test/main/view.test
|
+++ b/mysql-test/main/view.test
|
@@ -6696,4 +6696,23 @@ create algorithm=merge view v as
|
|
drop table t1,t2,t3;
|
|
+--echo #
|
+--echo # MDEV-31189: Server crash or assertion failure in upon 2nd
|
+--echo # execution of PS with views and HAVING
|
+--echo #
|
+
|
+CREATE TABLE t (f INT);
|
+INSERT INTO t VALUES (1),(2); # Optional, fails either way
|
+CREATE VIEW v1 AS SELECT 1 AS a;
|
+CREATE VIEW v2 AS SELECT a FROM v1;
|
+
|
+PREPARE stmt FROM "SELECT * FROM v2 HAVING 1 IN (SELECT f FROM t)";
|
+EXECUTE stmt;
|
+EXECUTE stmt;
|
+
|
+# Cleanup
|
+DROP VIEW v1;
|
+DROP VIEW v2;
|
+DROP TABLE t;
|
+
|
--echo # End of 10.4 tests
|
diff --git a/sql/table.cc b/sql/table.cc
|
index 15a92818b81..0f296a85e58 100644
|
--- a/sql/table.cc
|
+++ b/sql/table.cc
|
@@ -9163,8 +9163,13 @@ void TABLE_LIST::wrap_into_nested_join(List<TABLE_LIST> &join_list)
|
|
static inline bool derived_table_optimization_done(TABLE_LIST *table)
|
{
|
- return table->derived &&
|
- (table->derived->is_excluded() ||
|
+ SELECT_LEX_UNIT *derived= (table->derived ?
|
+ table->derived :
|
+ (table->view ?
|
+ &table->view->unit:
|
+ NULL));
|
+ return derived &&
|
+ (derived->is_excluded() ||
|
table->is_materialized_derived());
|
}
|
|
@@ -9226,8 +9231,7 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view)
|
set_derived();
|
}
|
|
- if (is_view() ||
|
- !derived_table_optimization_done(this))
|
+ if (!derived_table_optimization_done(this))
|
{
|
/* A subquery might be forced to be materialized due to a side-effect. */
|
if (!is_materialized_derived() && unit->can_be_merged() &&
|
It is assert that derived is not excluded here:
#7 0x000055c03dc0c8b9 in TABLE_LIST::set_check_materialized (this=0x7f5ffc0d7a08) at /home/sanja/maria/git/10.4/sql/table.cc:6470#8 0x000055c03da1a07b in TABLE_LIST::set_materialized_derived (this=0x7f5ffc0d7a08) at /home/sanja/maria/git/10.4/sql/table.h:2883#9 0x000055c03dc14e40 in TABLE_LIST::init_derived (this=0x7f5ffc0d7a08,thd=0x7f5ffc000da0, init_view=true)at /home/sanja/maria/git/10.4/sql/table.cc:9241#10 0x000055c03da59446 in mysql_derived_init (thd=0x7f5ffc000da0,lex=0x7f5ffc0d57a0, derived=0x7f5ffc0d7a08)at /home/sanja/maria/git/10.4/sql/sql_derived.cc:564#11 0x000055c03da5836c in mysql_handle_derived (lex=0x7f5ffc0d57a0, phases=1)at /home/sanja/maria/git/10.4/sql/sql_derived.cc:123#12 0x000055c03da0da8f in open_and_lock_tables (thd=0x7f5ffc000da0,options=..., tables=0x7f5ffc0d7a08, derived=true, flags=0,prelocking_strategy=0x7f600d7929d0)at /home/sanja/maria/git/10.4/sql/sql_base.cc:5353#13 0x000055c03d9c080d in open_and_lock_tables (thd=0x7f5ffc000da0,tables=0x7f5ffc0d7a08, derived=true, flags=0)at /home/sanja/maria/git/10.4/sql/sql_base.h:503#14 0x000055c03dac2c77 in execute_sqlcom_select (thd=0x7f5ffc000da0,all_tables=0x7f5ffc0d7a08)the derived is in deed excluded, and it had happened here:
#0 0x000055c03da80084 in st_select_lex_unit::exclude_level (this=0x7f5ffc09dce8) at /home/sanja/maria/git/10.4/sql/sql_lex.cc:2746#1 0x000055c03da58b06 in mysql_derived_merge (thd=0x7f5ffc000da0,lex=0x7f5ffc0d57a0, derived=0x7f5ffc0d7a08)at /home/sanja/maria/git/10.4/sql/sql_derived.cc:417#2 0x000055c03da5869f in mysql_handle_single_derived (lex=0x7f5ffc0d57a0,derived=0x7f5ffc0d7a08, phases=8)at /home/sanja/maria/git/10.4/sql/sql_derived.cc:200#3 0x000055c03dc1497b in TABLE_LIST::handle_derived (this=0x7f5ffc0d7a08,lex=0x7f5ffc0d57a0, phases=8)at /home/sanja/maria/git/10.4/sql/table.cc:9090#4 0x000055c03da75d75 in LEX::handle_list_of_derived (this=0x7f5ffc0d57a0,table_list=0x7f5ffc0d7a08, phases=8)at /home/sanja/maria/git/10.4/sql/sql_lex.h:4388#5 0x000055c03da830e6 in st_select_lex::handle_derived (this=0x7f5ffc0d73f8,lex=0x7f5ffc0d57a0, phases=8)at /home/sanja/maria/git/10.4/sql/sql_lex.cc:4309#6 0x000055c03db09a1f in JOIN::optimize_inner (this=0x7f5ffc014a68)at /home/sanja/maria/git/10.4/sql/sql_select.cc:1910#7 0x000055c03db08e56 in JOIN::optimize (this=0x7f5ffc014a68)at /home/sanja/maria/git/10.4/sql/sql_select.cc:1690#8 0x000055c03db14d72 in mysql_select (thd=0x7f5ffc000da0,tables=0x7f5ffc0d7a08, wild_num=0, fields=..., conds=0x0, og_num=0,order=0x0, group=0x0, having=0x7f5ffc1912a8, proc_param=0x0,select_options=2416184064, result=0x7f5ffc0da1c0, unit=0x7f5ffc0d5860,select_lex=0x7f5ffc0d73f8)at /home/sanja/maria/git/10.4/sql/sql_select.cc:4797#9 0x000055c03db033eb in handle_select (thd=0x7f5ffc000da0,lex=0x7f5ffc0d57a0, result=0x7f5ffc0da1c0, setup_tables_done_option=0)at /home/sanja/maria/git/10.4/sql/sql_select.cc:442#10 0x000055c03dac3198 in execute_sqlcom_select (thd=0x7f5ffc000da0,all_tables=0x7f5ffc0d7a08)at /home/sanja/maria/git/10.4/sql/sql_parse.cc:6463#11 0x000055c03dab9656 in mysql_execute_command (thd=0x7f5ffc000da0)at /home/sanja/maria/git/10.4/sql/sql_parse.cc:3966#12 0x000055c03daeb6df in Prepared_statement::execute (this=0x7f5ffc197a60,expanded_query=0x7f600d793940, open_cursor=false)