- commits - lists.mariadb.org

[Commits] aec4aa7a04b: MDEV-12059: Assertion `precision > 0' failed with a window function or window aggregate function
by Varun 02 Jul '20

02 Jul '20

revision-id: aec4aa7a04bc84d361124f3c9feab4cdce572310 (mariadb-10.2.31-294-gaec4aa7a04b) parent(s): fbfb5b5f68a428ec819bc09d14b30cebf660b37b author: Varun Gupta committer: Varun Gupta timestamp: 2020-07-02 02:28:30 +0530 message: MDEV-12059: Assertion `precision > 0' failed with a window function or window aggregate function Pass the unsigned flag from the Item_sum to the window function --- mysql-test/r/win.result | 10 ++++++++++ mysql-test/t/win.test | 9 +++++++++ sql/item_windowfunc.cc | 1 + 3 files changed, 20 insertions(+) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 9c85315b7c1..71953871295 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3798,5 +3798,15 @@ SELECT FIRST_VALUE(MAX(a) OVER (PARTITION BY a)) OVER (ORDER BY a) AS x FROM t1 ERROR HY000: Window functions can not be used as arguments to group functions. DROP TABLE t1; # +# MDEV-12059: Assertion `precision > 0' failed with a window function or window aggregate function +# +CREATE TABLE t1 (d DECIMAL(1,0) UNSIGNED); +INSERT INTO t1 VALUES (1),(2); +SELECT MIN(d) OVER () FROM t1; +MIN(d) OVER () +1 +1 +DROP TABLE t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index d19ff2c624d..a768b893432 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2472,6 +2472,15 @@ SELECT NTILE(MAX(a) OVER (PARTITION BY a)) OVER (PARTITION BY a ORDER BY b) FROM SELECT FIRST_VALUE(MAX(a) OVER (PARTITION BY a)) OVER (ORDER BY a) AS x FROM t1 GROUP BY a; DROP TABLE t1; +--echo # +--echo # MDEV-12059: Assertion `precision > 0' failed with a window function or window aggregate function +--echo # + +CREATE TABLE t1 (d DECIMAL(1,0) UNSIGNED); +INSERT INTO t1 VALUES (1),(2); +SELECT MIN(d) OVER () FROM t1; +DROP TABLE t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/item_windowfunc.cc b/sql/item_windowfunc.cc index a3edacd880e..40116647b4d 100644 --- a/sql/item_windowfunc.cc +++ b/sql/item_windowfunc.cc @@ -111,6 +111,7 @@ Item_window_func::fix_fields(THD *thd, Item **ref) max_length= window_func()->max_length; maybe_null= window_func()->maybe_null; + unsigned_flag= window_func()->unsigned_flag; fixed= 1; set_phase_to_initial();

1 0

[Commits] ab88849645d: MDEV-23029: JSON_OBJECTAGG returns NULL when used together with GROUP BY
by Varun 29 Jun '20

29 Jun '20

revision-id: ab88849645dd58a8261a3612262254ff145e48c6 (mariadb-10.5.2-484-gab88849645d) parent(s): ead98fe5d34912578445d42e620c8ed95df4c433 author: Varun Gupta committer: Varun Gupta timestamp: 2020-06-29 14:31:07 +0530 message: MDEV-23029: JSON_OBJECTAGG returns NULL when used together with GROUP BY Quick grouping is not supported for JSON_OBJECTAGG. The same for GROUP_CONCAT too so make sure that Item::quick_group is set to FALSE. We need to make sure that in the case of JSON_OBJECTAGG we don't create an index over grouping fields and update the result after each iteration. Instead we should first sort the result in accordance to the GROUP BY fields and then perform the grouping and write the result to the temp table. --- mysql-test/main/func_json.result | 14 ++++++++++++++ mysql-test/main/func_json.test | 12 ++++++++++++ sql/item_jsonfunc.cc | 1 + sql/item_jsonfunc.h | 1 + 4 files changed, 28 insertions(+) diff --git a/mysql-test/main/func_json.result b/mysql-test/main/func_json.result index b3d7a1d2ed1..aa9c9cdc8b5 100644 --- a/mysql-test/main/func_json.result +++ b/mysql-test/main/func_json.result @@ -1328,5 +1328,19 @@ JSON_ARRAYAGG(col1) [{"color":"red", "size":1},{"color":"blue", "size":2}] drop table t1; # +# MDEV-23029: JSON_OBJECTAGG returns NULL when used together with GROUP BY +# +CREATE TABLE t1 (e INT, a VARCHAR(255), v VARCHAR(255)); +INSERT INTO t1 VALUES (0, 'a1', '1') , (0, 'a2', '2') , (1, 'b1', '3'); +EXPLAIN SELECT B.e, JSON_OBJECTAGG(B.a, B.v) FROM t1 A, t1 B GROUP BY B.e; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A ALL NULL NULL NULL NULL 3 Using temporary; Using filesort +1 SIMPLE B ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +SELECT B.e, JSON_OBJECTAGG(B.a, B.v) FROM t1 A, t1 B GROUP BY B.e; +e JSON_OBJECTAGG(B.a, B.v) +0 {"a1":"1", "a1":"1", "a1":"1", "a2":"2", "a2":"2", "a2":"2"} +1 {"b1":"3", "b1":"3", "b1":"3"} +DROP TABLE t1; +# # End of 10.5 tests # diff --git a/mysql-test/main/func_json.test b/mysql-test/main/func_json.test index 531f6cd758d..75a118a808f 100644 --- a/mysql-test/main/func_json.test +++ b/mysql-test/main/func_json.test @@ -826,6 +826,18 @@ insert into t1 values('{"color":"blue", "size":2}' ); select JSON_ARRAYAGG(col1) from t1; drop table t1; +--echo # +--echo # MDEV-23029: JSON_OBJECTAGG returns NULL when used together with GROUP BY +--echo # + +CREATE TABLE t1 (e INT, a VARCHAR(255), v VARCHAR(255)); +INSERT INTO t1 VALUES (0, 'a1', '1') , (0, 'a2', '2') , (1, 'b1', '3'); + +EXPLAIN SELECT B.e, JSON_OBJECTAGG(B.a, B.v) FROM t1 A, t1 B GROUP BY B.e; +SELECT B.e, JSON_OBJECTAGG(B.a, B.v) FROM t1 A, t1 B GROUP BY B.e; + +DROP TABLE t1; + --echo # --echo # End of 10.5 tests --echo # diff --git a/sql/item_jsonfunc.cc b/sql/item_jsonfunc.cc index b9a84775311..fe3fae5840c 100644 --- a/sql/item_jsonfunc.cc +++ b/sql/item_jsonfunc.cc @@ -3725,6 +3725,7 @@ Item_func_json_objectagg:: Item_func_json_objectagg(THD *thd, Item_func_json_objectagg *item) :Item_sum(thd, item) { + quick_group= FALSE; result.set_charset(collation.collation); result.append("{"); } diff --git a/sql/item_jsonfunc.h b/sql/item_jsonfunc.h index 0408b3ad72f..69625831045 100644 --- a/sql/item_jsonfunc.h +++ b/sql/item_jsonfunc.h @@ -579,6 +579,7 @@ class Item_func_json_objectagg : public Item_sum Item_func_json_objectagg(THD *thd, Item *key, Item *value) : Item_sum(thd, key, value) { + quick_group= FALSE; result.append("{"); }

1 0

[Commits] 258a0fb47b7: MDEV-22951: rpl.rpl_slave_alias_replica failed in buildbot with wrong result
by sujatha 24 Jun '20

24 Jun '20

revision-id: 258a0fb47b74363d0919d6a18b9b2e67d45d4cf7 (mariadb-10.5.2-490-g258a0fb47b7) parent(s): d1bb7f9143ceafe9cbdc1e53c4ffd4abe232cb97 author: Sujatha committer: Sujatha timestamp: 2020-06-24 15:33:45 +0530 message: MDEV-22951: rpl.rpl_slave_alias_replica failed in buildbot with wrong result Problem: ======== rpl.rpl_slave_alias_replica test fails sporadically on build bot as shown below. rpl.rpl_slave_alias_replica 'innodb,stmt' w4 [ fail ] +++ /usr/local/mariadb-10.5.4-linux-x86_64/mysql-test/suite/rpl/r/ rpl_slave_alias_replica.reject 2020-06-12 09:52:25.191267945 +0000 @@ -22,6 +22,7 @@ SHOW REPLICA HOSTS; Server_id Host Port Master_id 2 127.0.0.1 SLAVE_PORT 1 +2 127.0.0.1 SLAVE_PORT 1 Analysis: ======== The SHOW REPLICA HOSTS command reports an additional row for the same server occasionally. This happens because of zombie dump threads on the master. Error log from master also confirms that "A slave with the same server_uuid/server_id as this slave has connected to the master". Since SHOW REPLICA HOSTS goes through the list of dump threads and reports the corresponding hosts,the same host is reported twice. Fix: === Instead of capturing the SHOW REPLICA HOSTS command in result file use wait_show_condition.inc. This will ensure that SHOW REPLICA HOSTS command fetches slave host details. --- mysql-test/suite/rpl/r/rpl_slave_alias_replica.result | 5 ++--- mysql-test/suite/rpl/t/rpl_slave_alias_replica.test | 10 ++++++++-- 2 files changed, 10 insertions(+), 5 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_slave_alias_replica.result b/mysql-test/suite/rpl/r/rpl_slave_alias_replica.result index 8262cf8212a..2a49e80075f 100644 --- a/mysql-test/suite/rpl/r/rpl_slave_alias_replica.result +++ b/mysql-test/suite/rpl/r/rpl_slave_alias_replica.result @@ -18,10 +18,9 @@ START REPLICA; include/wait_for_slave_io_to_start.inc include/wait_for_slave_sql_to_start.inc connection master; +connection slave; +connection master; "Command: SHOW SLAVE HOSTS --> SHOW REPLICA HOSTS" -SHOW REPLICA HOSTS; -Server_id Host Port Master_id -2 127.0.0.1 SLAVE_PORT 1 DROP USER 'repl_user'; connection slave; "Command: SHOW SLAVE IO/SQL THREAD --> SHOW REPLICA IO/SQL THREAD" diff --git a/mysql-test/suite/rpl/t/rpl_slave_alias_replica.test b/mysql-test/suite/rpl/t/rpl_slave_alias_replica.test index 8f2cd1a4eb3..324821a325f 100644 --- a/mysql-test/suite/rpl/t/rpl_slave_alias_replica.test +++ b/mysql-test/suite/rpl/t/rpl_slave_alias_replica.test @@ -45,10 +45,16 @@ START REPLICA; --source include/wait_for_slave_io_to_start.inc --source include/wait_for_slave_sql_to_start.inc +--connection master +--sync_slave_with_master + --connection master --echo "Command: SHOW SLAVE HOSTS --> SHOW REPLICA HOSTS" ---replace_result $SLAVE_MYPORT SLAVE_PORT -SHOW REPLICA HOSTS; +let $show_statement= SHOW REPLICA HOSTS; +let $field= Server_id; +# Slave's server_id 2 +let $condition= ='2'; +source include/wait_show_condition.inc; DROP USER 'repl_user'; --sync_slave_with_master

1 0

[Commits] d7a39ef8f12: MDEV-22866: Crash in join optimizer with constant outer join nest
by Sergei Petrunia 23 Jun '20

23 Jun '20

revision-id: d7a39ef8f12acbc15efed9e500f258c5b9e1ca17 (mariadb-10.3.21-157-gd7a39ef8f12) parent(s): 32b34cb95edc1032381225b58780fc92cb449200 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-06-23 15:19:29 +0300 message: MDEV-22866: Crash in join optimizer with constant outer join nest Starting from 10.3, the optimizer is able to detect that entire outer join nests are constants (because of "Impossible ON") and remove them (see mark_join_nest_as_const) However, this was not properly accounted for in NESTED_JOIN structure and the way check_interleaving_with_nj() uses its n_tables member to check if the join prefix order is allowed. (The result was that the optimizer could conclude that no join prefix is allowed and fail an assertion) --- mysql-test/main/join_outer.result | 31 ++++++++++++++++++++++++++++++ mysql-test/main/join_outer.test | 35 ++++++++++++++++++++++++++++++++++ mysql-test/main/join_outer_jcl6.result | 31 ++++++++++++++++++++++++++++++ sql/sql_select.cc | 18 ++++++++++++----- sql/table.h | 6 ++++-- 5 files changed, 114 insertions(+), 7 deletions(-) diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result index 7a230ccd15a..796d01a0996 100644 --- a/mysql-test/main/join_outer.result +++ b/mysql-test/main/join_outer.result @@ -2752,3 +2752,34 @@ WHERE t3.pk IN (2); drop view v4; drop table t1,t2,t3,t4; SET optimizer_switch=@org_optimizer_switch; +# +# MDEV-22866: Crash in join optimizer with constant outer join nest +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3),(4); +CREATE TABLE t3 (c INT, KEY(c)) ENGINE=MyISAM; +CREATE TABLE t4 (d INT, KEY(d)) ENGINE=MyISAM; +INSERT INTO t4 VALUES (5),(6); +CREATE TABLE t5 (e INT) ENGINE=MyISAM; +INSERT INTO t5 VALUES (7),(8); +CREATE TABLE t6 (f INT) ENGINE=MyISAM; +INSERT INTO t6 VALUES (9),(10); +SELECT * +FROM +t1 +LEFT JOIN ( +t2 LEFT JOIN ( +t3 JOIN +t4 ON t3.c = t4.d and t3.c >2 and t3.c<0 +) ON t2.b >= t4.d +) ON t1.a <= t2.b +LEFT JOIN t5 ON t2.b = t5.e +LEFT JOIN t6 ON t3.c = t6.f; +a b c d e f +1 3 NULL NULL NULL NULL +2 3 NULL NULL NULL NULL +1 4 NULL NULL NULL NULL +2 4 NULL NULL NULL NULL +drop table t1,t2,t3,t4,t5,t6; diff --git a/mysql-test/main/join_outer.test b/mysql-test/main/join_outer.test index 2e5fc65ebb6..f835d8af5a8 100644 --- a/mysql-test/main/join_outer.test +++ b/mysql-test/main/join_outer.test @@ -2252,3 +2252,38 @@ drop view v4; drop table t1,t2,t3,t4; SET optimizer_switch=@org_optimizer_switch; + +--echo # +--echo # MDEV-22866: Crash in join optimizer with constant outer join nest +--echo # + +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3),(4); + +CREATE TABLE t3 (c INT, KEY(c)) ENGINE=MyISAM; + +CREATE TABLE t4 (d INT, KEY(d)) ENGINE=MyISAM; +INSERT INTO t4 VALUES (5),(6); + +CREATE TABLE t5 (e INT) ENGINE=MyISAM; +INSERT INTO t5 VALUES (7),(8); + +CREATE TABLE t6 (f INT) ENGINE=MyISAM; +INSERT INTO t6 VALUES (9),(10); + +SELECT * +FROM + t1 + LEFT JOIN ( + t2 LEFT JOIN ( + t3 JOIN + t4 ON t3.c = t4.d and t3.c >2 and t3.c<0 + ) ON t2.b >= t4.d + ) ON t1.a <= t2.b + LEFT JOIN t5 ON t2.b = t5.e + LEFT JOIN t6 ON t3.c = t6.f; + +drop table t1,t2,t3,t4,t5,t6; diff --git a/mysql-test/main/join_outer_jcl6.result b/mysql-test/main/join_outer_jcl6.result index d3276de88ea..4f66b004cfc 100644 --- a/mysql-test/main/join_outer_jcl6.result +++ b/mysql-test/main/join_outer_jcl6.result @@ -2759,3 +2759,34 @@ WHERE t3.pk IN (2); drop view v4; drop table t1,t2,t3,t4; SET optimizer_switch=@org_optimizer_switch; +# +# MDEV-22866: Crash in join optimizer with constant outer join nest +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3),(4); +CREATE TABLE t3 (c INT, KEY(c)) ENGINE=MyISAM; +CREATE TABLE t4 (d INT, KEY(d)) ENGINE=MyISAM; +INSERT INTO t4 VALUES (5),(6); +CREATE TABLE t5 (e INT) ENGINE=MyISAM; +INSERT INTO t5 VALUES (7),(8); +CREATE TABLE t6 (f INT) ENGINE=MyISAM; +INSERT INTO t6 VALUES (9),(10); +SELECT * +FROM +t1 +LEFT JOIN ( +t2 LEFT JOIN ( +t3 JOIN +t4 ON t3.c = t4.d and t3.c >2 and t3.c<0 +) ON t2.b >= t4.d +) ON t1.a <= t2.b +LEFT JOIN t5 ON t2.b = t5.e +LEFT JOIN t6 ON t3.c = t6.f; +a b c d e f +1 3 NULL NULL NULL NULL +2 3 NULL NULL NULL NULL +1 4 NULL NULL NULL NULL +2 4 NULL NULL NULL NULL +drop table t1,t2,t3,t4,t5,t6; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 05da4a1e750..3d618643d7e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -15823,10 +15823,15 @@ static uint build_bitmap_for_nested_joins(List<TABLE_LIST> *join_list, /** - Set NESTED_JOIN::counter=0 in all nested joins in passed list. + Set NESTED_JOIN::counter and n_tables in all nested joins in passed list. - Recursively set NESTED_JOIN::counter=0 for all nested joins contained in - the passed join_list. + For all nested joins contained in the passed join_list (including its + children), set: + - nested_join->counter=0 + - nested_join->n_tables= {number of non-degenerate direct children}. + + Non-degenerate means non-const base table or a join nest that has a + non-degenerate child. @param join_list List of nested joins to process. It may also contain base tables which will be ignored. @@ -15849,8 +15854,11 @@ static uint reset_nj_counters(JOIN *join, List<TABLE_LIST> *join_list) if (!nested_join->n_tables) is_eliminated_nest= TRUE; } - if ((table->nested_join && !is_eliminated_nest) || - (!table->nested_join && (table->table->map & ~join->eliminated_tables))) + const table_map removed_tables= join->eliminated_tables | + join->const_table_map; + + if ((table->nested_join && !is_eliminated_nest) || + (!table->nested_join && (table->table->map & ~removed_tables))) n++; } DBUG_RETURN(n); diff --git a/sql/table.h b/sql/table.h index 2bad6cbf32f..d289b6e0ab2 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2932,9 +2932,11 @@ typedef struct st_nested_join Before each use the counters are zeroed by reset_nj_counters. */ uint counter; + /* - Number of elements in join_list that were not (or contain table(s) that - weren't) removed by table elimination. + Number of elements in join_list that participate in the join plan choice: + - Base tables that were not removed by table elimination + - Join nests that were not removed by mark_join_nest_as_const */ uint n_tables; nested_join_map nj_map; /* Bit used to identify this nested join*/

1 0

[Commits] c5d4bfa31bd: MDEV-22866: Crash in join optimizer with constant outer join nest
by Sergei Petrunia 23 Jun '20

23 Jun '20

revision-id: c5d4bfa31bddef5dcc67c92849bafdd9c0115eab (mariadb-10.3.21-157-gc5d4bfa31bd) parent(s): 32b34cb95edc1032381225b58780fc92cb449200 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-06-23 15:00:52 +0300 message: MDEV-22866: Crash in join optimizer with constant outer join nest Starting from 10.3, the optimizer is able to detect that entire outer join nests are constants (because of "Impossible ON") and remove them (see mark_join_nest_as_const) However, this was not properly accounted for in NESTED_JOIN structure and the way check_interleaving_with_nj() uses its n_tables member to check if the join prefix order is allowed. (The result was that the optimizer could conclude that no join prefix is allowed and fail an assertion) --- mysql-test/main/join_outer.result | 31 +++++++++++++++++++++++++++++++ mysql-test/main/join_outer.test | 35 +++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 18 +++++++++++++----- sql/table.h | 6 ++++-- 4 files changed, 83 insertions(+), 7 deletions(-) diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result index 7a230ccd15a..493e4f1a0a9 100644 --- a/mysql-test/main/join_outer.result +++ b/mysql-test/main/join_outer.result @@ -2752,3 +2752,34 @@ WHERE t3.pk IN (2); drop view v4; drop table t1,t2,t3,t4; SET optimizer_switch=@org_optimizer_switch; +# +# MDEV-22866: Server crashes in ... with not_null_range_scan=on +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3),(4); +CREATE TABLE t3 (c INT, KEY(c)) ENGINE=MyISAM; +CREATE TABLE t4 (d INT, KEY(d)) ENGINE=MyISAM; +INSERT INTO t4 VALUES (5),(6); +CREATE TABLE t5 (e INT) ENGINE=MyISAM; +INSERT INTO t5 VALUES (7),(8); +CREATE TABLE t6 (f INT) ENGINE=MyISAM; +INSERT INTO t6 VALUES (9),(10); +SELECT * +FROM +t1 +LEFT JOIN ( +t2 LEFT JOIN ( +t3 JOIN +t4 ON t3.c = t4.d and t3.c >2 and t3.c<0 +) ON t2.b >= t4.d +) ON t1.a <= t2.b +LEFT JOIN t5 ON t2.b = t5.e +LEFT JOIN t6 ON t3.c = t6.f; +a b c d e f +1 3 NULL NULL NULL NULL +2 3 NULL NULL NULL NULL +1 4 NULL NULL NULL NULL +2 4 NULL NULL NULL NULL +drop table t1,t2,t3,t4,t5,t6; diff --git a/mysql-test/main/join_outer.test b/mysql-test/main/join_outer.test index 2e5fc65ebb6..dfac9caae73 100644 --- a/mysql-test/main/join_outer.test +++ b/mysql-test/main/join_outer.test @@ -2252,3 +2252,38 @@ drop view v4; drop table t1,t2,t3,t4; SET optimizer_switch=@org_optimizer_switch; + +--echo # +--echo # MDEV-22866: Server crashes in ... with not_null_range_scan=on +--echo # + +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3),(4); + +CREATE TABLE t3 (c INT, KEY(c)) ENGINE=MyISAM; + +CREATE TABLE t4 (d INT, KEY(d)) ENGINE=MyISAM; +INSERT INTO t4 VALUES (5),(6); + +CREATE TABLE t5 (e INT) ENGINE=MyISAM; +INSERT INTO t5 VALUES (7),(8); + +CREATE TABLE t6 (f INT) ENGINE=MyISAM; +INSERT INTO t6 VALUES (9),(10); + +SELECT * +FROM + t1 + LEFT JOIN ( + t2 LEFT JOIN ( + t3 JOIN + t4 ON t3.c = t4.d and t3.c >2 and t3.c<0 + ) ON t2.b >= t4.d + ) ON t1.a <= t2.b + LEFT JOIN t5 ON t2.b = t5.e + LEFT JOIN t6 ON t3.c = t6.f; + +drop table t1,t2,t3,t4,t5,t6; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 05da4a1e750..3d618643d7e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -15823,10 +15823,15 @@ static uint build_bitmap_for_nested_joins(List<TABLE_LIST> *join_list, /** - Set NESTED_JOIN::counter=0 in all nested joins in passed list. + Set NESTED_JOIN::counter and n_tables in all nested joins in passed list. - Recursively set NESTED_JOIN::counter=0 for all nested joins contained in - the passed join_list. + For all nested joins contained in the passed join_list (including its + children), set: + - nested_join->counter=0 + - nested_join->n_tables= {number of non-degenerate direct children}. + + Non-degenerate means non-const base table or a join nest that has a + non-degenerate child. @param join_list List of nested joins to process. It may also contain base tables which will be ignored. @@ -15849,8 +15854,11 @@ static uint reset_nj_counters(JOIN *join, List<TABLE_LIST> *join_list) if (!nested_join->n_tables) is_eliminated_nest= TRUE; } - if ((table->nested_join && !is_eliminated_nest) || - (!table->nested_join && (table->table->map & ~join->eliminated_tables))) + const table_map removed_tables= join->eliminated_tables | + join->const_table_map; + + if ((table->nested_join && !is_eliminated_nest) || + (!table->nested_join && (table->table->map & ~removed_tables))) n++; } DBUG_RETURN(n); diff --git a/sql/table.h b/sql/table.h index 2bad6cbf32f..d289b6e0ab2 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2932,9 +2932,11 @@ typedef struct st_nested_join Before each use the counters are zeroed by reset_nj_counters. */ uint counter; + /* - Number of elements in join_list that were not (or contain table(s) that - weren't) removed by table elimination. + Number of elements in join_list that participate in the join plan choice: + - Base tables that were not removed by table elimination + - Join nests that were not removed by mark_join_nest_as_const */ uint n_tables; nested_join_map nj_map; /* Bit used to identify this nested join*/

1 0

[Commits] a73ad0ced51: MDEV-22187: SIGSEGV in ha_innobase::cmp_ref on DELETE
by Varun 22 Jun '20

22 Jun '20

revision-id: a73ad0ced51f8261be90fb60efba818965ef605e (mariadb-10.5.2-481-ga73ad0ced51) parent(s): 9160e4aa95cd3c40ea0733d632692526049ed12b author: Varun Gupta committer: Varun Gupta timestamp: 2020-06-22 19:48:46 +0530 message: MDEV-22187: SIGSEGV in ha_innobase::cmp_ref on DELETE Added a new test file for tests with delete using INNODB. --- mysql-test/main/delete_innodb.result | 22 ++++++++++++++++++++++ mysql-test/main/delete_innodb.test | 21 +++++++++++++++++++++ 2 files changed, 43 insertions(+) diff --git a/mysql-test/main/delete_innodb.result b/mysql-test/main/delete_innodb.result new file mode 100644 index 00000000000..e354ad6f787 --- /dev/null +++ b/mysql-test/main/delete_innodb.result @@ -0,0 +1,22 @@ +# Tests for delete with INNODB +# +# MDEV-22187: SIGSEGV in ha_innobase::cmp_ref on DELETE +# +SET @save_sort_buffer_size= @@sort_buffer_size; +SET sort_buffer_size=1024; +CREATE TABLE t1(c1 CHAR(255) PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (0), ('a'), ('b'); +SELECT * FROM t1; +c1 +0 +a +b +EXPLAIN DELETE b FROM t1 AS a JOIN t1 AS b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE a index NULL PRIMARY 255 NULL 3 Using index +1 SIMPLE b ALL NULL NULL NULL NULL 3 +DELETE b FROM t1 AS a JOIN t1 AS b; +SELECT * FROM t1; +c1 +SET sort_buffer_size=@save_sort_buffer_size; +DROP TABLE t1; diff --git a/mysql-test/main/delete_innodb.test b/mysql-test/main/delete_innodb.test new file mode 100644 index 00000000000..243be1c1776 --- /dev/null +++ b/mysql-test/main/delete_innodb.test @@ -0,0 +1,21 @@ +--source include/have_innodb.inc +--source include/have_sequence.inc + +--echo # Tests for delete with INNODB + +--echo # +--echo # MDEV-22187: SIGSEGV in ha_innobase::cmp_ref on DELETE +--echo # + +SET @save_sort_buffer_size= @@sort_buffer_size; +SET sort_buffer_size=1024; +CREATE TABLE t1(c1 CHAR(255) PRIMARY KEY) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (0), ('a'), ('b'); +SELECT * FROM t1; +EXPLAIN DELETE b FROM t1 AS a JOIN t1 AS b; +DELETE b FROM t1 AS a JOIN t1 AS b; +SELECT * FROM t1; + +SET sort_buffer_size=@save_sort_buffer_size; +DROP TABLE t1;

1 0

[Commits] 16a2b009ec7: MDEV-15313: Error with aggregate function inside of last_value() window function
by Varun 22 Jun '20

22 Jun '20

revision-id: 16a2b009ec7554dd1f76eee95e3e8124bd886607 (mariadb-10.2.31-281-g16a2b009ec7) parent(s): 667bb528500a68cff26b442c125ba0e4d109c9b3 author: Varun Gupta committer: Varun Gupta timestamp: 2020-06-22 18:24:08 +0530 message: MDEV-15313: Error with aggregate function inside of last_value() window function The issue here is for window functions like LAG/LAST_VALUE/FIRST_VALUE did not allow aggregate functions like SUM, COUNT as their arguments. The fix ensures that aggregate functions will be allowed as arguments to all window functions. --- mysql-test/r/win.result | 13 +++++++++++++ mysql-test/t/win.test | 10 ++++++++++ sql/sql_yacc.yy | 3 +++ 3 files changed, 26 insertions(+) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index c73f9f8ce6b..b4bd29496ed 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3788,5 +3788,18 @@ row_number() OVER() 3 DROP TABLE t1; # +# MDEV-15313: Error with aggregate function inside of last_value() window function +# +CREATE TABLE t1(a int,b int); +INSERT INTO t1 VALUES (1,1), (1,5),(1,4), (2,2),(2,5), (3,3),(4,4); +INSERT INTO t1 VALUES (1,1), (1,5),(1,4), (2,2),(2,5), (3,3),(4,4); +SELECT a, last_value(count(a)) OVER (partition BY a) FROM t1 GROUP BY a; +a last_value(count(a)) OVER (partition BY a) +1 6 +2 4 +3 2 +4 2 +DROP TABLE t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 37c107633d9..5b5eb3c0cf8 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2459,6 +2459,16 @@ ANALYZE FORMAT=JSON SELECT row_number() OVER() FROM t1; SELECT row_number() OVER() FROM t1; DROP TABLE t1; +--echo # +--echo # MDEV-15313: Error with aggregate function inside of last_value() window function +--echo # + +CREATE TABLE t1(a int,b int); +INSERT INTO t1 VALUES (1,1), (1,5),(1,4), (2,2),(2,5), (3,3),(4,4); +INSERT INTO t1 VALUES (1,1), (1,5),(1,4), (2,2),(2,5), (3,3),(4,4); +SELECT a, last_value(count(a)) OVER (partition BY a) FROM t1 GROUP BY a; +DROP TABLE t1; + --echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 386c86cb3e2..71ff052fabc 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -10495,6 +10495,9 @@ window_func_expr: window_func: simple_window_func + { + ((Item_sum *) $1)->mark_as_window_func_sum_expr(); + } | sum_expr {

1 0

[Commits] e82858fc6b7: MDEV-22910: SIGSEGV in Opt_trace_context::is_started & SIGSEGV in Json_writer::add_table_name (on optimized builds)
by Varun 17 Jun '20

17 Jun '20

revision-id: e82858fc6b73336778992a371d24a11e96e28f83 (mariadb-10.4.11-230-ge82858fc6b7) parent(s): 6404645980db51fdc1e5dae2ac94eca57804284b author: Varun Gupta committer: Varun Gupta timestamp: 2020-06-17 20:54:09 +0530 message: MDEV-22910: SIGSEGV in Opt_trace_context::is_started & SIGSEGV in Json_writer::add_table_name (on optimized builds) Make sure to initialize members of TABLE::reginfo when TABLE::init is called. In this case the problem was that table->reginfo.join_tab was set for the SELECT query and then was reused by the UPDATE query. This case occurred only when the SELECT query had a degenerate join. --- mysql-test/main/opt_trace.result | 31 +++++++++++++++++++++++++++++++ mysql-test/main/opt_trace.test | 11 +++++++++++ sql/opt_range.cc | 6 ++---- sql/table.cc | 2 ++ 4 files changed, 46 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index be358e69c47..7e6532083da 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -8465,5 +8465,36 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) ] ] DROP TABLE t1,t2; +# +# MDEV-22910:SIGSEGV in Opt_trace_context::is_started & SIGSEGV in Json_writer::add_table_name +# (on optimized builds) +# +CREATE TABLE t1( a INT, b INT, PRIMARY KEY( a ) ); +SELECT sum(b), row_number() OVER (order by b) FROM t1 WHERE a = 101; +sum(b) row_number() OVER (order by b) +NULL 1 +UPDATE t1 SET b=10 WHERE a=1; +SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) +[ + + [ + + { + "index": "PRIMARY", + "ranges": + [ + "(1) <= (a) <= (1)" + ], + "rowid_ordered": true, + "using_mrr": false, + "index_only": false, + "rows": 0, + "cost": 1.125, + "chosen": true + } + ] +] +DROP TABLE t1; # End of 10.4 tests set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index d1a8fedc635..a10de9b0e3b 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -594,6 +594,17 @@ EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a ORDER BY t2.b; select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; DROP TABLE t1,t2; +--echo # +--echo # MDEV-22910:SIGSEGV in Opt_trace_context::is_started & SIGSEGV in Json_writer::add_table_name +--echo # (on optimized builds) +--echo # + +CREATE TABLE t1( a INT, b INT, PRIMARY KEY( a ) ); +SELECT sum(b), row_number() OVER (order by b) FROM t1 WHERE a = 101; +UPDATE t1 SET b=10 WHERE a=1; +SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +DROP TABLE t1; + --echo # End of 10.4 tests set optimizer_trace='enabled=off'; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 66c870dd2ac..1b7f8d1bb9b 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -2683,10 +2683,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, DBUG_PRINT("info",("Time to scan table: %g", read_time)); Json_writer_object table_records(thd); - if (head->reginfo.join_tab) - table_records.add_table_name(head->reginfo.join_tab); - else - table_records.add_table_name(head); + table_records.add_table_name(head); + Json_writer_object trace_range(thd, "range_analysis"); { Json_writer_object table_rec(thd, "table_scan"); diff --git a/sql/table.cc b/sql/table.cc index f58f2428129..aacb4eee228 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -5160,6 +5160,8 @@ void TABLE::init(THD *thd, TABLE_LIST *tl) fulltext_searched= 0; file->ft_handler= 0; reginfo.impossible_range= 0; + reginfo.join_tab= NULL; + reginfo.not_exists_optimize= FALSE; created= TRUE; cond_selectivity= 1.0; cond_selectivity_sampling_explain= NULL;

1 0

[Commits] a2e9f5a3eb1: MDEV-22910: SIGSEGV in Opt_trace_context::is_started & SIGSEGV in Json_writer::add_table_name (on optimized builds)
by Varun 17 Jun '20

17 Jun '20

revision-id: a2e9f5a3eb1b7c573b6705aaed7aef703e34aa34 (mariadb-10.4.11-230-ga2e9f5a3eb1) parent(s): 6404645980db51fdc1e5dae2ac94eca57804284b author: Varun Gupta committer: Varun Gupta timestamp: 2020-06-17 19:59:06 +0530 message: MDEV-22910: SIGSEGV in Opt_trace_context::is_started & SIGSEGV in Json_writer::add_table_name (on optimized builds) Make sure to initialize members of TABLE::reginfo when TABLE::init is called. In this case the problem was that table->reginfo.join_tab was set for the SELECT query and then was reused by the UPDATE query. This case occurred only when the SELECT query had a degenerate join. --- mysql-test/main/opt_trace.result | 31 +++++++++++++++++++++++++++++++ mysql-test/main/opt_trace.test | 11 +++++++++++ sql/opt_range.cc | 6 ++---- sql/table.cc | 2 ++ 4 files changed, 46 insertions(+), 4 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index be358e69c47..464a3564e4f 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -8465,5 +8465,36 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) ] ] DROP TABLE t1,t2; +# +# MDEV-22910:SIGSEGV in Opt_trace_context::is_started & SIGSEGV in Json_writer::add_table_name +# (on optimized builds) +# +CREATE TABLE t1( a INT, b INT, KEY( a ) )ENGINE=MEMORY; +SELECT MAX(a), SUM(MAX(a)) OVER () FROM t1 WHERE a > 10; +MAX(a) SUM(MAX(a)) OVER () +NULL NULL +UPDATE t1 SET b=10 WHERE a=1; +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) +[ + + [ + + { + "index": "a", + "ranges": + [ + "(1) <= (a) <= (1)" + ], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 0, + "cost": 3.125, + "chosen": true + } + ] +] +DROP TABLE t1; # End of 10.4 tests set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index d1a8fedc635..6b2c1c491c0 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -594,6 +594,17 @@ EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a ORDER BY t2.b; select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; DROP TABLE t1,t2; +--echo # +--echo # MDEV-22910:SIGSEGV in Opt_trace_context::is_started & SIGSEGV in Json_writer::add_table_name +--echo # (on optimized builds) +--echo # + +CREATE TABLE t1( a INT, b INT, KEY( a ) )ENGINE=MEMORY; +SELECT MAX(a), SUM(MAX(a)) OVER () FROM t1 WHERE a > 10; +UPDATE t1 SET b=10 WHERE a=1; +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +DROP TABLE t1; + --echo # End of 10.4 tests set optimizer_trace='enabled=off'; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 66c870dd2ac..1b7f8d1bb9b 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -2683,10 +2683,8 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, DBUG_PRINT("info",("Time to scan table: %g", read_time)); Json_writer_object table_records(thd); - if (head->reginfo.join_tab) - table_records.add_table_name(head->reginfo.join_tab); - else - table_records.add_table_name(head); + table_records.add_table_name(head); + Json_writer_object trace_range(thd, "range_analysis"); { Json_writer_object table_rec(thd, "table_scan"); diff --git a/sql/table.cc b/sql/table.cc index f58f2428129..aacb4eee228 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -5160,6 +5160,8 @@ void TABLE::init(THD *thd, TABLE_LIST *tl) fulltext_searched= 0; file->ft_handler= 0; reginfo.impossible_range= 0; + reginfo.join_tab= NULL; + reginfo.not_exists_optimize= FALSE; created= TRUE; cond_selectivity= 1.0; cond_selectivity_sampling_explain= NULL;

1 0

[Commits] 30d09dd3e00: MDEV-22852: SIGSEGV in sortlength (optimized builds)
by Varun 17 Jun '20

17 Jun '20

revision-id: 30d09dd3e0030ba6cc2efd179218100bf188cda2 (mariadb-10.2.31-279-g30d09dd3e00) parent(s): b46b7144d1999d4950a812486f36f2f0d6ab645d author: Varun Gupta committer: Varun Gupta timestamp: 2020-06-17 16:22:11 +0530 message: MDEV-22852: SIGSEGV in sortlength (optimized builds) The issue here is for a DEPENDENT subquery that has an aggregate function in the ORDER BY clause, is wrapped inside an Item_aggregate_ref. For computation of ORDER BY we need to refer to the temp table field corresponding to this item. But in the function make_sortorder, we were explicitly casting Item_aggrgate_ref to Item_sum, which leads to us not getting the temp table field corresponding to the item. --- mysql-test/r/subselect4.result | 12 ++++++++++++ mysql-test/t/subselect4.test | 12 ++++++++++++ sql/filesort.cc | 9 ++++++++- 3 files changed, 32 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 51f199bd1e9..a718895dc34 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2566,3 +2566,15 @@ SELECT sum(a), t2.a, t2.b FROM t2 HAVING t2.a IN (SELECT t2.b FROM t1); sum(a) a b 6 1 1 DROP TABLE t1,t2; +# +# MDEV-22852: SIGSEGV in sortlength (optimized builds) +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='subquery_cache=off'; +CREATE TABLE t1 (a INT,b INT); +INSERT INTO t1 VALUES (0,0),(0,0); +SELECT (SELECT DISTINCT t1i.b FROM t1 t1i GROUP BY t1i.a ORDER BY MAX(t1o.b)) FROM t1 AS t1o; +(SELECT DISTINCT t1i.b FROM t1 t1i GROUP BY t1i.a ORDER BY MAX(t1o.b)) +0 +SET @@optimizer_switch= @save_optimizer_switch; +DROP TABLE t1; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 2b3f610d06f..057a4502684 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -2101,3 +2101,15 @@ SET @@sql_select_limit= @save_sql_select_limit; eval EXPLAIN EXTENDED $query; eval $query; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-22852: SIGSEGV in sortlength (optimized builds) +--echo # + +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='subquery_cache=off'; +CREATE TABLE t1 (a INT,b INT); +INSERT INTO t1 VALUES (0,0),(0,0); +SELECT (SELECT DISTINCT t1i.b FROM t1 t1i GROUP BY t1i.a ORDER BY MAX(t1o.b)) FROM t1 AS t1o; +SET @@optimizer_switch= @save_optimizer_switch; +DROP TABLE t1; diff --git a/sql/filesort.cc b/sql/filesort.cc index 1df2f93676d..f8952f6d711 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -483,7 +483,14 @@ uint Filesort::make_sortorder(THD *thd, JOIN *join, table_map first_table_bit) if (item->type() == Item::FIELD_ITEM) pos->field= ((Item_field*) item)->field; else if (item->type() == Item::SUM_FUNC_ITEM && !item->const_item()) - pos->field= ((Item_sum*) item)->get_tmp_table_field(); + { + // Aggregate, or Item_aggregate_ref + DBUG_ASSERT(first->type() == Item::SUM_FUNC_ITEM || + (first->type() == Item::REF_ITEM && + static_cast<Item_ref*>(first)->ref_type() == + Item_ref::AGGREGATE_REF)); + pos->field= first->get_tmp_table_field(); + } else if (item->type() == Item::COPY_STR_ITEM) { // Blob patch pos->item= ((Item_copy*) item)->get_item();

1 0