revision-id: e60cbbbda31d7a10e87c16313171b2036a4519c4 (mariadb-10.2.18-52-ge60cbbbda31) parent(s): f8268f3cce4577c28ab62e53293556d05a74fb1a author: Varun Gupta committer: Varun Gupta timestamp: 2018-11-01 14:27:24 +0530 message: MDEV-12575: Server crash in AGGR_OP::put_record or in JOIN_CACHE::free or Invalid write in JOIN::make_aggr_tables_info During the optimize state of a query, we come know that the result set would atmost contain one row, then for such a query we don't need to compute GROUP BY, ORDER BY and DISTINCT. --- mysql-test/r/distinct.result | 20 ++++++++++++++ mysql-test/r/group_by.result | 10 +++++++ mysql-test/r/win.result | 62 ++++++++++++++++++++++++++++++++++++++++++++ mysql-test/t/distinct.test | 18 +++++++++++++ mysql-test/t/group_by.test | 12 +++++++++ mysql-test/t/win.test | 56 +++++++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 12 +++++++++ 7 files changed, 190 insertions(+) diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index e1bbf5adb79..850ce9a1965 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -1049,4 +1049,24 @@ b1+'0' b2+'0' b3+'0' b4+'0' b5+'0' b6 +'0' 1 0 0 1 0 1 0 1 0 0 1 0 DROP TABLE t1; +# +# MDEV-12575: Server crash in AGGR_OP::put_record or in JOIN_CACHE::free +# or Invalid write in JOIN::make_aggr_tables_info +# +CREATE TABLE t1 (pk INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2); +explain +( SELECT DISTINCT 1 FROM t1 ORDER BY BENCHMARK(1, MIN(pk)) ) +UNION +( SELECT DISTINCT 1 FROM t1 ORDER BY BENCHMARK(1, MIN(pk)) ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL PRIMARY 4 NULL 2 Using index; Using temporary +2 UNION t1 index NULL PRIMARY 4 NULL 2 Using index; Using temporary +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +( SELECT DISTINCT 1 FROM t1 ORDER BY BENCHMARK(1, MIN(pk)) ) +UNION +( SELECT DISTINCT 1 FROM t1 ORDER BY BENCHMARK(1, MIN(pk)) ); +1 +1 +drop table t1; End of 5.5 tests diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 9fee03877c5..9da6fa5ae8f 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -2832,3 +2832,13 @@ select distinct 1 from t1 group by a,b with rollup limit 1; 1 1 drop table t1; +CREATE TABLE t1 ( pk int, i1 int, v1 varchar(1)); +explain +SELECT 1 FROM t1 +GROUP BY REPEAT((BINARY pk), v1), AES_DECRYPT((@A := i1), 20852) WITH ROLLUP HAVING LOAD_FILE('a') ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +SELECT 1 FROM t1 +GROUP BY REPEAT((BINARY pk), v1), AES_DECRYPT((@A := i1), 20852) WITH ROLLUP HAVING LOAD_FILE('a') ; +1 +drop table t1; diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index b73fe346303..dc4ff0aaa1e 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3363,3 +3363,65 @@ Kaolin Tuning 88 68.7500 Tatiana SQL 87 65.2500 Tatiana Tuning 83 68.7500 drop table t1; +# +# MDEV-12575: Server crash in AGGR_OP::put_record or in JOIN_CACHE::free +# or Invalid write in JOIN::make_aggr_tables_info +# +SELECT DISTINCT BIT_OR(100) OVER () FROM dual +GROUP BY LEFT('2018-08-24', 100) order by 1+2; +BIT_OR(100) OVER () +100 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1),(2); +SELECT * FROM ( +SELECT +ROW_NUMBER() OVER(), i, sum(i) +FROM t1 +WHERE 1=0 +limit 0 +) AS sq; +ROW_NUMBER() OVER() i sum(i) +SELECT * FROM ( +SELECT +ROW_NUMBER() OVER(), i, sum(i) +FROM t1 +WHERE 1=0 +GROUP BY i +) AS sq; +ROW_NUMBER() OVER() i sum(i) +drop table t1; +create table t1 (a int); +explain +select distinct 1, row_number() over (order by 1) from t1 where a=0 group by a with rollup; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +select distinct 1, row_number() over (order by 1) from t1 where a=0 group by a with rollup; +1 row_number() over (order by 1) +drop table t1; +explain +SELECT DISTINCT BIT_OR(100) OVER () FROM dual +GROUP BY LEFT('2018-08-24', 100) WITH ROLLUP +HAVING @A := 'qwerty'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +SELECT DISTINCT BIT_OR(100) OVER () FROM dual +GROUP BY LEFT('2018-08-24', 100) WITH ROLLUP +HAVING @A := 'qwerty'; +BIT_OR(100) OVER () +explain +SELECT DISTINCT BIT_OR(100) OVER () FROM dual +GROUP BY LEFT('2018-08-24', 100) +HAVING @A := 'qwerty'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +SELECT DISTINCT BIT_OR(100) OVER () FROM dual +GROUP BY LEFT('2018-08-24', 100) +HAVING @A := 'qwerty'; +BIT_OR(100) OVER () +create table t1 (a int); +explain +SELECT DISTINCT BIT_OR(100) OVER () FROM t1 +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; diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index c11f8b501bc..d41340c29fd 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -798,4 +798,22 @@ CREATE TABLE t1 (b1 BIT, b2 BIT, b3 BIT, b4 BIT , b5 BIT, b6 BIT); INSERT INTO t1 VALUES (1,0,0,1,0,1),(0,1,0,0,1,0); SELECT DISTINCT b1+'0', b2+'0', b3+'0', b4+'0', b5+'0', b6 +'0' FROM t1; DROP TABLE t1; + +--echo # +--echo # MDEV-12575: Server crash in AGGR_OP::put_record or in JOIN_CACHE::free +--echo # or Invalid write in JOIN::make_aggr_tables_info +--echo # + +CREATE TABLE t1 (pk INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2); +explain +( SELECT DISTINCT 1 FROM t1 ORDER BY BENCHMARK(1, MIN(pk)) ) +UNION +( SELECT DISTINCT 1 FROM t1 ORDER BY BENCHMARK(1, MIN(pk)) ); + +( SELECT DISTINCT 1 FROM t1 ORDER BY BENCHMARK(1, MIN(pk)) ) +UNION +( SELECT DISTINCT 1 FROM t1 ORDER BY BENCHMARK(1, MIN(pk)) ); +drop table t1; + --echo End of 5.5 tests diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 275939df5c5..61be676bb11 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1948,3 +1948,15 @@ insert into t1 values(-126,7),(1,1),(0,0),(-1,1),(351,65534); select distinct 1 from t1 group by a,b with rollup limit 1; drop table t1; +# +# MDEV-12575: Server crash in AGGR_OP::put_record or in JOIN_CACHE::free +# or Invalid write in JOIN::make_aggr_tables_info +# + +CREATE TABLE t1 ( pk int, i1 int, v1 varchar(1)); +explain +SELECT 1 FROM t1 +GROUP BY REPEAT((BINARY pk), v1), AES_DECRYPT((@A := i1), 20852) WITH ROLLUP HAVING LOAD_FILE('a') ; +SELECT 1 FROM t1 +GROUP BY REPEAT((BINARY pk), v1), AES_DECRYPT((@A := i1), 20852) WITH ROLLUP HAVING LOAD_FILE('a') ; +drop table t1; diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index e24c6533dcd..f70403ca6a9 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2117,3 +2117,59 @@ SELECT name, test, score, AVG(score) OVER (PARTITION BY test) AS average_by_test FROM t1; drop table t1; + +--echo # +--echo # MDEV-12575: Server crash in AGGR_OP::put_record or in JOIN_CACHE::free +--echo # or Invalid write in JOIN::make_aggr_tables_info +--echo # + +SELECT DISTINCT BIT_OR(100) OVER () FROM dual +GROUP BY LEFT('2018-08-24', 100) order by 1+2; + +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1),(2); + +SELECT * FROM ( + SELECT + ROW_NUMBER() OVER(), i, sum(i) + FROM t1 + WHERE 1=0 + limit 0 +) AS sq; + +SELECT * FROM ( + SELECT + ROW_NUMBER() OVER(), i, sum(i) + FROM t1 + WHERE 1=0 + GROUP BY i +) AS sq; +drop table t1; + +create table t1 (a int); +explain +select distinct 1, row_number() over (order by 1) from t1 where a=0 group by a with rollup; +select distinct 1, row_number() over (order by 1) from t1 where a=0 group by a with rollup; +drop table t1; + +explain +SELECT DISTINCT BIT_OR(100) OVER () FROM dual +GROUP BY LEFT('2018-08-24', 100) WITH ROLLUP +HAVING @A := 'qwerty'; +SELECT DISTINCT BIT_OR(100) OVER () FROM dual +GROUP BY LEFT('2018-08-24', 100) WITH ROLLUP +HAVING @A := 'qwerty'; + +explain +SELECT DISTINCT BIT_OR(100) OVER () FROM dual +GROUP BY LEFT('2018-08-24', 100) +HAVING @A := 'qwerty'; +SELECT DISTINCT BIT_OR(100) OVER () FROM dual +GROUP BY LEFT('2018-08-24', 100) +HAVING @A := 'qwerty'; + +create table t1 (a int); +explain +SELECT DISTINCT BIT_OR(100) OVER () FROM t1 +GROUP BY LEFT('2018-08-24', 100) having 1=1 limit 0; +drop table t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1fa80da85a6..a721aed6ce3 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2245,6 +2245,18 @@ JOIN::optimize_inner() if (!tables_list || !table_count) { choose_tableless_subquery_plan(); + + /* The output has atmost one row */ + if (group_list) + { + group_list= 0; + group_optimized_away= 1; + rollup.state= ROLLUP::STATE_NONE; + } + order=0; + simple_order=1; + select_distinct=0; + if (select_lex->have_window_funcs()) { if (!(join_tab= (JOIN_TAB*) thd->alloc(sizeof(JOIN_TAB))))