revision-id: 8328cf09425bae0c10ebfc0eea68b5cbbb0e1870 (mariadb-10.2.18-75-g8328cf09425) parent(s): a9c1420233f3f65aee00a8e7bdd1c8481b0bbc37 author: Varun Gupta committer: Varun Gupta timestamp: 2018-11-14 13:04:25 +0530 message: MDEV-13170: Database service (MySQL) stops after update with trigger For prepare statemtent/stored procedures we rollback the items to original ones after prepare execution in the function reinit_stmt_before_use. This rollback is done for group by, order by clauses but is not done for the window specification containing the order by and partition by clause of the window function. --- mysql-test/r/win.result | 34 ++++++++++++++++++++++++++++++++++ mysql-test/t/win.test | 41 +++++++++++++++++++++++++++++++++++++++++ sql/sql_prepare.cc | 12 ++++++++++++ 3 files changed, 87 insertions(+) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index c539ac4f252..4ffa9f34c1d 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3423,3 +3423,37 @@ GROUP BY LEFT('2018-08-24', 100) having 1=1 limit 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Zero limit drop table t1; +# +# MDEV-13170: Database service (MySQL) stops after update with trigger +# +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 t3 (id1 int, id2 int, d1 int); +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 t3) a +JOIN t2 ON (a.d1=t2.db_time AND a.id1=t2.au_nr)) +SELECT 1; +END;// +update t1 set ml_id=8884 where point_id=1; +ERROR 23000: Duplicate entry '8884-1' for key 't1_ml_u' +update t1 set ml_id=8884 where point_id=1; +ERROR 23000: Duplicate entry '8884-1' for key 't1_ml_u' +drop table t1, t2,t3; +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; +i row_number() over (partition by i order by i) +1 1 +2 1 +deallocate prepare stmt; +drop table t1; +drop view v1; diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 7dda2b6215f..807d394edd3 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2175,3 +2175,44 @@ explain SELECT DISTINCT BIT_OR(100) OVER () FROM t1 GROUP BY LEFT('2018-08-24', 100) having 1=1 limit 0; drop table t1; + +--echo # +--echo # MDEV-13170: Database service (MySQL) stops after update with trigger +--echo # + +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 t3 (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 t3) 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; +--error 1062 +update t1 set ml_id=8884 where point_id=1; +drop table t1, t2,t3; + +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; +deallocate prepare stmt; +drop table t1; +drop view v1; diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 65f7c85b4ec..c3156ce1d5c 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2927,6 +2927,7 @@ void reinit_stmt_before_use(THD *thd, LEX *lex) { SELECT_LEX *sl= lex->all_selects_list; DBUG_ENTER("reinit_stmt_before_use"); + Window_spec *win_spec; /* We have to update "thd" pointer in LEX, all its units and in LEX::result, @@ -2995,6 +2996,17 @@ void reinit_stmt_before_use(THD *thd, LEX *lex) /* Fix ORDER list */ for (order= sl->order_list.first; order; order= order->next) order->item= &order->item_ptr; + /* Fix window functions too */ + List_iterator<Window_spec> it(sl->window_specs); + + while ((win_spec= it++)) + { + for (order= win_spec->partition_list->first; order; order= order->next) + order->item= &order->item_ptr; + for (order= win_spec->order_list->first; order; order= order->next) + order->item= &order->item_ptr; + } + { #ifndef DBUG_OFF bool res=