Alice Sherepa added a comment - 2018-05-24 11:22 variation using CTE   CREATE TABLE t1 ( t1_id int , point_id int , ml_id int , UNIQUE KEY t1_ml_u (ml_id,point_id)) ; INSERT INTO t1 VALUES (1,1,8884),(2,1,8885);   CREATE TABLE t2 ( db_time datetime, au_nr int , col_id int , new_val int ); CREATE TABLE t4 (id1 int , id2 int , d1 int );   delimiter //;   CREATE TRIGGER t1_aurtrg AFTER UPDATE ON t1 FOR EACH ROW begin CREATE OR REPLACE TEMPORARY TABLE trg_u AS WITH l AS ( SELECT a.*, Max (t2.col_id) over (PARTITION BY a.d1), Max (t2.new_val) over (PARTITION BY a.d1) FROM ( SELECT d1 , id1, id2 FROM t4) a JOIN t2 ON (a.d1=t2.db_time AND a.id1=t2.au_nr)) SELECT 1;   END ;//   delimiter ;// --error 1062 update t1 set ml_id=8884 where point_id=1; update t1 set ml_id=8884 where point_id=1; 52df8040264cc6f322 Thread 1 (Thread 0x7f30d427b700 (LWP 24517)): #0 __pthread_kill (threadid=<optimized out>, signo=signo@entry=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:62 #1 0x00005618ff0d9d9a in my_write_core (sig=sig@entry=11) at /home/alice/git/10.2/mysys/stacktrace.c:477 #2 0x00005618fec197b4 in handle_fatal_signal (sig=11) at /home/alice/git/10.2/sql/signal_handler.cc:305 #3 <signal handler called> #4 find_field_in_tables (thd=thd@entry=0x7f30c40009a8, item=item@entry=0x7f30c4092a50, first_table=first_table@entry=0x7f30c408e190, last_table=last_table@entry=0x0, ref=ref@entry=0x7f30d4276688, report_error=report_error@entry=IGNORE_ERRORS, check_privileges=false, register_tree_change=false) at /home/alice/git/10.2/sql/sql_base.cc:5846 #5 0x00005618feac1f36 in find_order_in_list (thd=thd@entry=0x7f30c40009a8, ref_pointer_array=..., tables=tables@entry=0x7f30c408e190, order=order@entry=0x7f30c40b60e8, fields=..., all_fields=..., is_group_field=true, add_to_all_fields=true, from_window_spec=true) at /home/alice/git/10.2/sql/sql_select.cc:22363 #6 0x00005618fead855a in setup_group (thd=thd@entry=0x7f30c40009a8, ref_pointer_array=..., tables=tables@entry=0x7f30c408e190, fields=..., all_fields=..., order=0x7f30c40b60e8, hidden_group_fields=0x7f30d42767cf, from_window_spec=true) at /home/alice/git/10.2/sql/sql_select.cc:22537 #7 0x00005618feba90d1 in setup_windows (thd=thd@entry=0x7f30c40009a8, ref_pointer_array=..., tables=tables@entry=0x7f30c408e190, fields=..., all_fields=..., win_specs=..., win_funcs=...) at /home/alice/git/10.2/sql/sql_window.cc:208 #8 0x00005618feada772 in setup_without_group (reserved=0x7f30c40b370c, hidden_group_fields=0x7f30c402c64f, win_funcs=..., win_specs=..., group=0x0, order=<optimized out>, conds=0x7f30c402c770, all_fields=..., fields=..., leaves=..., tables=0x7f30c408e190, ref_pointer_array=..., thd=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_select.cc:664 #9 JOIN::prepare (this=this@entry=0x7f30c402c368, tables_init=<optimized out>, wild_num=<optimized out>, conds_init=<optimized out>, og_num=<optimized out>, order_init=<optimized out>, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f30c40b3448, unit_arg=0x7f30c40b50d0) at /home/alice/git/10.2/sql/sql_select.cc:819 #10 0x00005618feb250fb in st_select_lex_unit::prepare (this=0x7f30c40b50d0, thd_arg=thd_arg@entry=0x7f30c40009a8, sel_result=sel_result@entry=0x0, additional_options=additional_options@entry=0) at /home/alice/git/10.2/sql/sql_union.cc:585 #11 0x00005618febaf55c in With_element::prepare_unreferenced (this=this@entry=0x7f30c408fbf8, thd=thd@entry=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_cte.cc:970 #12 0x00005618febaf5e3 in With_clause::prepare_unreferenced_elements (this=<optimized out>, thd=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_cte.cc:717 #13 0x00005618feada974 in JOIN::prepare (this=0x7f30c402be10, tables_init=<optimized out>, wild_num=<optimized out>, conds_init=<optimized out>, og_num=<optimized out>, order_init=<optimized out>, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f30c40b4250, unit_arg=0x7f30c40b3b18) at /home/alice/git/10.2/sql/sql_select.cc:893 #14 0x00005618feae7767 in mysql_select (thd=thd@entry=0x7f30c40009a8, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2415921920, result=0x7f30c402bd18, unit=0x7f30c40b3b18, select_lex=0x7f30c40b4250) at /home/alice/git/10.2/sql/sql_select.cc:3758 #15 0x00005618feae78de in handle_select (thd=thd@entry=0x7f30c40009a8, lex=lex@entry=0x7f30c40b3a50, result=result@entry=0x7f30c402bd18, setup_tables_done_option=setup_tables_done_option@entry=0) at /home/alice/git/10.2/sql/sql_select.cc:376 #16 0x00005618fea9ec9a in mysql_execute_command (thd=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_parse.cc:3952 #17 0x00005618fed1a5e4 in sp_instr_stmt::exec_core (this=0x7f30c408fe30, thd=<optimized out>, nextp=0x7f30d4278274) at /home/alice/git/10.2/sql/sp_head.cc:3244 #18 0x00005618fed20303 in sp_lex_keeper::reset_lex_and_exec_core (this=this@entry=0x7f30c408fe70, thd=thd@entry=0x7f30c40009a8, nextp=nextp@entry=0x7f30d4278274, open_tables=open_tables@entry=false, instr=instr@entry=0x7f30c408fe30) at /home/alice/git/10.2/sql/sp_head.cc:3007 #19 0x00005618fed20914 in sp_instr_stmt::execute (this=0x7f30c408fe30, thd=0x7f30c40009a8, nextp=0x7f30d4278274) at /home/alice/git/10.2/sql/sp_head.cc:3160 #20 0x00005618fed1d13b in sp_head::execute (this=this@entry=0x7f30c40b1a20, thd=thd@entry=0x7f30c40009a8, merge_da_on_success=merge_da_on_success@entry=false) at /home/alice/git/10.2/sql/sp_head.cc:1326 #21 0x00005618fed1d650 in sp_head::execute_trigger (this=0x7f30c40b1a20, thd=thd@entry=0x7f30c40009a8, db_name=<optimized out>, table_name=<optimized out>, grant_info=grant_info@entry=0x7f30c40a3188) at /home/alice/git/10.2/sql/sp_head.cc:1655 #22 0x00005618feb1d7dc in Table_triggers_list::process_triggers (this=0x7f30c40a2bb0, thd=thd@entry=0x7f30c40009a8, event=event@entry=TRG_EVENT_UPDATE, time_type=time_type@entry=TRG_ACTION_AFTER, old_row_is_record1=old_row_is_record1@entry=true) at /home/alice/git/10.2/sql/sql_trigger.cc:2189 #23 0x00005618feb29fd1 in mysql_update (thd=thd@entry=0x7f30c40009a8, table_list=0x7f30c400f190, fields=..., values=..., conds=0x7f30c400fb10, order_num=<optimized out>, order=<optimized out>, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=0x7f30d4278ce0, updated_return=0x7f30d4278d90) at /home/alice/git/10.2/sql/sql_update.cc:838 #24 0x00005618fea98c04 in mysql_execute_command (thd=thd@entry=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_parse.cc:4289 #25 0x00005618fea9fddd in mysql_parse (thd=0x7f30c40009a8, rawbuf=<optimized out>, length=41, parser_state=0x7f30d427a240, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /home/alice/git/10.2/sql/sql_parse.cc:7941 #26 0x00005618feaa2976 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f30c40009a8, packet=packet@entry=0x7f30c4006ce9 "update t1 set ml_id=8884 where point_id=1", packet_length=packet_length@entry=41, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /home/alice/git/10.2/sql/sql_parse.cc:1820 #27 0x00005618feaa324f in do_command (thd=0x7f30c40009a8) at /home/alice/git/10.2/sql/sql_parse.cc:1374 #28 0x00005618feb5d734 in do_handle_one_connection (connect=connect@entry=0x5619010593a8) at /home/alice/git/10.2/sql/sql_connect.cc:1335 #29 0x00005618feb5d8a4 in handle_one_connection (arg=arg@entry=0x5619010593a8) at /home/alice/git/10.2/sql/sql_connect.cc:1241 #30 0x00005618fed9bb84 in pfs_spawn_thread (arg=0x56190100ddd8) at /home/alice/git/10.2/storage/perfschema/pfs.cc:1862 #31 0x00007f30dabe76ba in start_thread (arg=0x7f30d427b700) at pthread_create.c:333 #32 0x00007f30da07c41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

Tadas Balaišis added a comment - 2018-05-24 11:42 Thank you alice . This issue reminds me the other issue MDEV-12135 (created by me). If there would be statement triggers functionality, I'd use that complex queries at statement level trigger, which is not so mutation complicated.

Varun Gupta (Inactive) added a comment - 2018-07-27 19:13 A simple test case that crashes with views and prepared statement CREATE TABLE t1 (i INT , a char ); INSERT INTO t1 VALUES (1, 'a' ),(2, 'b' ); create view v1 as select * from t1; PREPARE stmt FROM "SELECT i, row_number() over (partition by i order by i) FROM v1" ; execute stmt;

Varun Gupta (Inactive) added a comment - 2018-11-14 07:41 Patch http://lists.askmonty.org/pipermail/commits/2018-November/013091.html

Oleksandr Byelkin added a comment - 2018-11-14 08:04 OK to push