- commits - lists.mariadb.org

[Commits] 3e9d70e131b: MDEV-16765: Missing rows with pushdown condition defined with CASE using Item_cond
by Galina 06 Aug '18

06 Aug '18

revision-id: 3e9d70e131b3af88799cbc069e07f46b6f33ea67 (mariadb-10.2.16-66-g3e9d70e131b) parent(s): 4ddcb4eb46c62cf459936554d43351db740ba14d author: Galina Shalygina committer: Galina Shalygina timestamp: 2018-08-06 15:10:27 +0300 message: MDEV-16765: Missing rows with pushdown condition defined with CASE using Item_cond The bug appears because of the wrong pushdown into the WHERE clause of the materialized derived table/view work. For the excl_dep_on_grouping_fields() method that checks if the condition can be pushed into the WHERE clause the case when Item_cond is used is missing. For Item_cond elements this method always returns positive result (that condition can be pushed). So this condition is pushed even if is shouldn't be pushed. To fix it new Item_cond::excl_dep_on_grouping_fields() method is added. --- mysql-test/r/derived_cond_pushdown.result | 203 ++++++++++++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 58 +++++++++ sql/item_cmpfunc.cc | 13 ++ sql/item_cmpfunc.h | 1 + 4 files changed, 275 insertions(+) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 22b81ef48a5..9b64de41856 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -9874,3 +9874,206 @@ SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT a aa DROP FUNCTION f1; +# +# MDEV-16765: pushdown condition with the CASE structure +# defined with Item_cond item +# +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,2), (3,4), (2,3); +SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2)) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +max_a b +1 2 +1 3 +EXPLAIN FORMAT=JSON SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2)) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "case when (tab2.max_a = 1 or tab2.max_a = 2) then 1 else 0 end = 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "case when (max_a = 1 or max_a = 2) then 1 else 0 end = 1", + "filesort": { + "sort_key": "t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } + } + } + } +} +SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4))) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +max_a b +1 2 +1 4 +EXPLAIN FORMAT=JSON SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4))) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "case when (tab2.max_a = 1 or tab2.max_a > 2 and tab2.max_a < 4) then 1 else 0 end = 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "case when (max_a = 1 or max_a > 2 and max_a < 4) then 1 else 0 end = 1", + "filesort": { + "sort_key": "t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } + } + } + } +} +SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2))) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +max_a b +1 3 +1 4 +EXPLAIN FORMAT=JSON SELECT * +FROM +( +SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2))) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "case when (tab2.max_a > 1 and (tab2.max_a = 2 or tab2.max_a > 2)) then 1 else 0 end = 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "case when (max_a > 1 and (max_a = 2 or max_a > 2)) then 1 else 0 end = 1", + "filesort": { + "sort_key": "t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } + } + } + } +} +SELECT * +FROM +( +SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4)) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +max_a b +1 2 +1 4 +EXPLAIN FORMAT=JSON SELECT * +FROM +( +SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4)) +THEN 1 ELSE 0 END AS max_a,b +FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "case when (tab2.b = 2 or tab2.b = 4) then 1 else 0 end = 1", + "materialized": { + "query_block": { + "select_id": 3, + "filesort": { + "sort_key": "t1.b", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "case when (t1.b = 2 or t1.b = 4) then 1 else 0 end = 1" + } + } + } + } + } + } + } +} +DROP TABLE t1; diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index d9d767f53ed..a8211ddd3e1 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -1920,3 +1920,61 @@ END;$$ DELIMITER ;$$ SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT 1)); DROP FUNCTION f1; + +--echo # +--echo # MDEV-16765: pushdown condition with the CASE structure +--echo # defined with Item_cond item +--echo # + +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,2), (3,4), (2,3); + +LET $query= +SELECT * +FROM +( + SELECT CASE WHEN ((tab2.max_a=1) OR (tab2.max_a=2)) + THEN 1 ELSE 0 END AS max_a,b + FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * +FROM +( + SELECT CASE WHEN ((tab2.max_a=1) OR ((tab2.max_a>2) AND (tab2.max_a<4))) + THEN 1 ELSE 0 END AS max_a,b + FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * +FROM +( + SELECT CASE WHEN ((tab2.max_a>1) AND ((tab2.max_a=2) OR (tab2.max_a>2))) + THEN 1 ELSE 0 END AS max_a,b + FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * +FROM +( + SELECT CASE WHEN ((tab2.b=2) OR (tab2.b=4)) + THEN 1 ELSE 0 END AS max_a,b + FROM (SELECT MAX(a) as max_a,b FROM t1 GROUP BY t1.b) AS tab2 +) AS tab1 +WHERE (tab1.max_a=1); +EVAL $query; +EVAL EXPLAIN FORMAT=JSON $query; + +DROP TABLE t1; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index f176a0a8193..6834820c0b5 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4970,6 +4970,19 @@ Item *Item_cond::build_clone(THD *thd, MEM_ROOT *mem_root) } +bool Item_cond::excl_dep_on_grouping_fields(st_select_lex *sel) +{ + List_iterator_fast<Item> li(list); + Item *item; + while ((item= li++)) + { + if (!item->excl_dep_on_grouping_fields(sel)) + return false; + } + return true; +} + + void Item_cond_and::mark_as_condition_AND_part(TABLE_LIST *embedding) { List_iterator<Item> li(list); diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 41a51ee8d12..59ac5f56fe1 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2224,6 +2224,7 @@ class Item_cond :public Item_bool_func Item_transformer transformer, uchar *arg_t); bool eval_not_null_tables(void *opt_arg); Item *build_clone(THD *thd, MEM_ROOT *mem_root); + bool excl_dep_on_grouping_fields(st_select_lex *sel); }; template <template<class> class LI, class T> class Item_equal_iterator;

1 0

[Commits] 4ddcb4e: MDEV-16750 JSON_SET mishandles unicode every second pair of arguments.
by holyfoot＠askmonty.org 06 Aug '18

06 Aug '18

revision-id: 4ddcb4eb46c62cf459936554d43351db740ba14d (mariadb-10.2.16-65-g4ddcb4e) parent(s): fc324a5f87827e43f51d1a75e60b3d5abcd807e5 committer: Alexey Botchkov timestamp: 2018-08-06 13:37:09 +0400 message: MDEV-16750 JSON_SET mishandles unicode every second pair of arguments. The charset of temporary storage (Item_func_json_insert::tmp_js) was not properly set. --- mysql-test/r/func_json.result | 12 ++++++++++++ mysql-test/t/func_json.test | 8 ++++++++ sql/item_jsonfunc.cc | 6 +++--- 3 files changed, 23 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/func_json.result b/mysql-test/r/func_json.result index 2a2df00..e8b2fac 100644 --- a/mysql-test/r/func_json.result +++ b/mysql-test/r/func_json.result @@ -778,3 +778,15 @@ select REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') = _latin1 X'53EC' fro REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') = _latin1 X'53EC' 1 drop table t1; +# +# MDEV-16750 JSON_SET mishandles unicode every second pair of arguments. +# +SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6); +JSON_SET('{}', '$.a', _utf8 0xC3B6) +{"a": "�"} +SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6, '$.b', _utf8 0xC3B6); +JSON_SET('{}', '$.a', _utf8 0xC3B6, '$.b', _utf8 0xC3B6) +{"a": "�", "b": "�"} +SELECT JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6'); +JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6') +{"a": "�", "x": 1, "b": "�"} diff --git a/mysql-test/t/func_json.test b/mysql-test/t/func_json.test index 41e5204..fd5e3d7 100644 --- a/mysql-test/t/func_json.test +++ b/mysql-test/t/func_json.test @@ -440,3 +440,11 @@ select JSON_VALUE(json_col, '$.key1')= _latin1 X'53EC' from t1; select REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') = _latin1 X'53EC' from t1; drop table t1; +--echo # +--echo # MDEV-16750 JSON_SET mishandles unicode every second pair of arguments. +--echo # + +SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6); +SELECT JSON_SET('{}', '$.a', _utf8 0xC3B6, '$.b', _utf8 0xC3B6); +SELECT JSON_SET('{}', '$.a', _utf8 X'C3B6', '$.x', 1, '$.b', _utf8 X'C3B6'); + diff --git a/sql/item_jsonfunc.cc b/sql/item_jsonfunc.cc index e9d38b7..4a83764 100644 --- a/sql/item_jsonfunc.cc +++ b/sql/item_jsonfunc.cc @@ -2384,8 +2384,9 @@ String *Item_func_json_insert::val_str(String *str) if ((null_value= args[0]->null_value)) return 0; - str->set_charset(js->charset()); - json_string_set_cs(&key_name, js->charset()); + str->set_charset(collation.collation); + tmp_js.set_charset(collation.collation); + json_string_set_cs(&key_name, collation.collation); for (n_arg=1, n_path=0; n_arg < arg_count; n_arg+=2, n_path++) { @@ -2599,7 +2600,6 @@ String *Item_func_json_insert::val_str(String *str) json_scan_start(&je, js->charset(),(const uchar *) js->ptr(), (const uchar *) js->ptr() + js->length()); str->length(0); - str->set_charset(js->charset()); if (json_nice(&je, str, Item_func_json_format::LOOSE)) goto js_error;

1 0

[Commits] fc324a5: MDEV-16869 String functions don't respect character set of JSON_VALUE.
by holyfoot＠askmonty.org 05 Aug '18

05 Aug '18

revision-id: fc324a5f87827e43f51d1a75e60b3d5abcd807e5 (mariadb-10.2.16-64-gfc324a5) parent(s): affdd79c69dfe443cb9fda06ef156243e1967ac3 committer: Alexey Botchkov timestamp: 2018-08-05 18:33:17 +0400 message: MDEV-16869 String functions don't respect character set of JSON_VALUE. Item_func_json_value::val_str() produced string of wrong charset. --- mysql-test/r/func_json.result | 12 ++++++++++++ mysql-test/t/func_json.test | 11 +++++++++++ sql/item_jsonfunc.cc | 2 +- 3 files changed, 24 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/func_json.result b/mysql-test/r/func_json.result index 206f745..2a2df00 100644 --- a/mysql-test/r/func_json.result +++ b/mysql-test/r/func_json.result @@ -766,3 +766,15 @@ DROP TABLE t1; select json_array(1,uuid(),compress(5.140264e+307)); json_array(1,uuid(),compress(5.140264e+307)) NULL +# +# MDEV-16869 String functions don't respect character set of JSON_VALUE. +# +create table t1(json_col TEXT) DEFAULT CHARSET=latin1; +insert into t1 values (_latin1 X'7B226B657931223A2253EC227D'); +select JSON_VALUE(json_col, '$.key1')= _latin1 X'53EC' from t1; +JSON_VALUE(json_col, '$.key1')= _latin1 X'53EC' +1 +select REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') = _latin1 X'53EC' from t1; +REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') = _latin1 X'53EC' +1 +drop table t1; diff --git a/mysql-test/t/func_json.test b/mysql-test/t/func_json.test index c2cc00d..41e5204 100644 --- a/mysql-test/t/func_json.test +++ b/mysql-test/t/func_json.test @@ -429,3 +429,14 @@ DROP TABLE t1; select json_array(1,uuid(),compress(5.140264e+307)); + +--echo # +--echo # MDEV-16869 String functions don't respect character set of JSON_VALUE. +--echo # + +create table t1(json_col TEXT) DEFAULT CHARSET=latin1; +insert into t1 values (_latin1 X'7B226B657931223A2253EC227D'); +select JSON_VALUE(json_col, '$.key1')= _latin1 X'53EC' from t1; +select REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') = _latin1 X'53EC' from t1; +drop table t1; + diff --git a/sql/item_jsonfunc.cc b/sql/item_jsonfunc.cc index ac0d7e9..e9d38b7 100644 --- a/sql/item_jsonfunc.cc +++ b/sql/item_jsonfunc.cc @@ -479,7 +479,7 @@ String *Item_func_json_value::val_str(String *str) (const uchar *) js->ptr() + js->length()); str->length(0); - str->set_charset(&my_charset_utf8mb4_bin); + str->set_charset(collation.collation); path.cur_step= path.p.steps; continue_search:

1 0

[Commits] a208b6ba185: Fix galera test MW-44
by jan 05 Aug '18

05 Aug '18

revision-id: a208b6ba185971162e0f78eae4932d1187dbfc06 (mariadb-10.1.34-44-ga208b6ba185) parent(s): b9f0112248ee085199b2918f2c9d74ec7099a21e author: Jan Lindström committer: Jan Lindström timestamp: 2018-08-05 12:11:31 +0300 message: Fix galera test MW-44 --- mysql-test/suite/galera/r/MW-44.result | 15 ++------------- mysql-test/suite/galera/t/MW-44.test | 11 ++++++----- 2 files changed, 8 insertions(+), 18 deletions(-) diff --git a/mysql-test/suite/galera/r/MW-44.result b/mysql-test/suite/galera/r/MW-44.result index 459a61030a4..a1e55318422 100644 --- a/mysql-test/suite/galera/r/MW-44.result +++ b/mysql-test/suite/galera/r/MW-44.result @@ -1,21 +1,10 @@ SET GLOBAL general_log='OFF'; TRUNCATE TABLE mysql.general_log; -SELECT COUNT(*) from mysql.general_log; -COUNT(*) -0 -SELECT * FROM mysql.general_log; -event_time user_host thread_id server_id command_type argument SET GLOBAL general_log='OFF'; TRUNCATE TABLE mysql.general_log; -SELECT COUNT(*) from mysql.general_log; -COUNT(*) -0 -SELECT * FROM mysql.general_log; -event_time user_host thread_id server_id command_type argument SET GLOBAL general_log='ON'; -SELECT COUNT(*) from mysql.general_log; -COUNT(*) -1 +SELECT argument from mysql.general_log WHERE argument NOT LIKE 'SELECT%'; +argument SET SESSION wsrep_osu_method=TOI; CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; SET SESSION wsrep_osu_method=RSU; diff --git a/mysql-test/suite/galera/t/MW-44.test b/mysql-test/suite/galera/t/MW-44.test index 09f444fdff4..6b5eb823985 100644 --- a/mysql-test/suite/galera/t/MW-44.test +++ b/mysql-test/suite/galera/t/MW-44.test @@ -8,18 +8,19 @@ --connection node_1 SET GLOBAL general_log='OFF'; TRUNCATE TABLE mysql.general_log; -SELECT COUNT(*) from mysql.general_log; -SELECT * FROM mysql.general_log; +--let $wait_condition = SELECT COUNT(*) = 0 FROM mysql.general_log; +--source include/wait_condition.inc --connection node_2 SET GLOBAL general_log='OFF'; TRUNCATE TABLE mysql.general_log; -SELECT COUNT(*) from mysql.general_log; -SELECT * FROM mysql.general_log; +--let $wait_condition = SELECT COUNT(*) = 0 FROM mysql.general_log; +--source include/wait_condition.inc --connection node_1 SET GLOBAL general_log='ON'; -SELECT COUNT(*) from mysql.general_log; +SELECT argument from mysql.general_log WHERE argument NOT LIKE 'SELECT%'; + SET SESSION wsrep_osu_method=TOI; CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; SET SESSION wsrep_osu_method=RSU;

1 0

[Commits] 5d90717cc9e: Add wsrep.cnf
by jan 04 Aug '18

04 Aug '18

revision-id: 5d90717cc9e05f2a3bfe137031eb15fc1e291ad7 (mariadb-galera-10.0.35-23-g5d90717cc9e) parent(s): ea0356e1ad506e55f2c26808c9b0e2d88953c79e author: Jan Lindström committer: Jan Lindström timestamp: 2018-08-04 11:28:25 +0300 message: Add wsrep.cnf --- support-files/CMakeLists.txt | 6 ++++++ 1 file changed, 6 insertions(+) diff --git a/support-files/CMakeLists.txt b/support-files/CMakeLists.txt index 80de8333db8..36340a2fbe7 100644 --- a/support-files/CMakeLists.txt +++ b/support-files/CMakeLists.txt @@ -102,6 +102,12 @@ IF(UNIX) DESTINATION ${inst_location} COMPONENT SupportFiles PERMISSIONS OWNER_READ OWNER_WRITE OWNER_EXECUTE GROUP_READ GROUP_EXECUTE WORLD_READ WORLD_EXECUTE) + CONFIGURE_FILE(${CMAKE_CURRENT_SOURCE_DIR}/wsrep.cnf.sh + ${CMAKE_CURRENT_BINARY_DIR}/wsrep.cnf @ONLY) + INSTALL(FILES ${CMAKE_CURRENT_BINARY_DIR}/wsrep.cnf + DESTINATION ${inst_location} COMPONENT SupportFiles + PERMISSIONS OWNER_READ OWNER_WRITE OWNER_EXECUTE GROUP_READ + GROUP_EXECUTE WORLD_READ WORLD_EXECUTE) IF (INSTALL_SYSCONFDIR) INSTALL(FILES ${CMAKE_CURRENT_BINARY_DIR}/mysql-log-rotate DESTINATION ${INSTALL_SYSCONFDIR}/logrotate.d

1 0

[Commits] 0f9efd5: MDEV-16359 wrong result (extra rows) on the query with UNION and brackets
by IgorBabaev 04 Aug '18

04 Aug '18

revision-id: 0f9efd54ceb0e2b42841f5eb15d30072d210bac2 (mariadb-10.3.6-67-g0f9efd5) parent(s): 980aa3e71dfe0284ae8acb3e22852f29aaebd0bd author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-03 23:27:24 -0700 message: MDEV-16359 wrong result (extra rows) on the query with UNION and brackets Due to an obvious bug in st_select_lex_unit::reset_distinct() a union without duplicates of more than 2 selects could return a result set containing duplicate rows if this union was enclosed in (). --- mysql-test/main/brackets.result | 12 ++++++++++++ mysql-test/main/brackets.test | 8 ++++++++ sql/sql_lex.cc | 1 - 3 files changed, 20 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result index 1948072..479b74b 100644 --- a/mysql-test/main/brackets.result +++ b/mysql-test/main/brackets.result @@ -207,4 +207,16 @@ a b 1 100 3 30 drop table t1; +# +# MDEV-16359: union with 3 selects in brackets +# +select 1 union select 1 union select 1; +1 +1 +(select 1 union select 1 union select 1); +1 +1 +((select 1) union (select 1) union (select 1)); +1 +1 # End of 10.4 tests diff --git a/mysql-test/main/brackets.test b/mysql-test/main/brackets.test index ac22770..699c70a 100644 --- a/mysql-test/main/brackets.test +++ b/mysql-test/main/brackets.test @@ -80,5 +80,13 @@ select a,b from t1 order by 1 limit 3; drop table t1; +--echo # +--echo # MDEV-16359: union with 3 selects in brackets +--echo # + +select 1 union select 1 union select 1; +(select 1 union select 1 union select 1); +((select 1) union (select 1) union (select 1)); + --echo # End of 10.4 tests diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index fa81555..20b1eb4 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -8763,7 +8763,6 @@ void st_select_lex_unit::reset_distinct() if (sl->distinct) { union_distinct= sl; - return; } } }

1 0

[Commits] f58442ec793: MDEV-15433: Optimizer does not use group by optimization with distinct
by Varun 04 Aug '18

04 Aug '18

revision-id: f58442ec7938a48f947c9e91d1d8cb60891ad485 (mariadb-10.1.34-36-gf58442ec793) parent(s): 2fb68244b4246221e4f605749913bfe582a4d040 author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-04 11:49:25 +0530 message: MDEV-15433: Optimizer does not use group by optimization with distinct After the commit b76b69cd5fe634d8ddb9406aa2c82ef2a375b4d8 loose index scan for queries with DISTINCT stopped working. That is why that commit has to be reverted. Additionally this patch fixes the problem of MDEV-10880. --- mysql-test/r/bench_count_distinct.result | 2 +- mysql-test/r/distinct.result | 2 +- mysql-test/r/explain_json.result | 18 +++---- mysql-test/r/group_min_max.result | 63 ++++++++++++++--------- mysql-test/suite/vcol/r/vcol_select_innodb.result | 2 +- mysql-test/t/group_min_max.test | 27 ++++++++++ sql/sql_select.cc | 10 +++- 7 files changed, 88 insertions(+), 36 deletions(-) diff --git a/mysql-test/r/bench_count_distinct.result b/mysql-test/r/bench_count_distinct.result index 79e12afd237..8b67e4be38a 100644 --- a/mysql-test/r/bench_count_distinct.result +++ b/mysql-test/r/bench_count_distinct.result @@ -5,7 +5,7 @@ count(distinct n) 100 explain extended select count(distinct n) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index NULL n 4 NULL 200 100.00 Using index +1 SIMPLE t1 range NULL n 4 NULL 10 100.00 Using index for group-by Warnings: Note 1003 select count(distinct `test`.`t1`.`n`) AS `count(distinct n)` from `test`.`t1` drop table t1; diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index b5e8cefca69..d6e5a69e217 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -212,7 +212,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using index explain SELECT distinct a from t3 order by a desc limit 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index NULL a 5 NULL 2 Using index +1 SIMPLE t3 index NULL a 5 NULL 40 Using index explain SELECT distinct a,b from t3 order by a+1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort diff --git a/mysql-test/r/explain_json.result b/mysql-test/r/explain_json.result index 9e5515f3cfa..0abbda352b7 100644 --- a/mysql-test/r/explain_json.result +++ b/mysql-test/r/explain_json.result @@ -1030,10 +1030,10 @@ Table Op Msg_type Msg_text test.t1 analyze status Table is already up to date explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 65 Using where; Using index for group-by (scanning) explain format=json select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); EXPLAIN { @@ -1041,14 +1041,14 @@ EXPLAIN "select_id": 1, "table": { "table_name": "t1", - "access_type": "index", - "key": "idx_t1_2", + "access_type": "range", + "key": "idx_t1_1", "key_length": "147", "used_key_parts": ["a1", "a2", "b"], - "rows": 128, + "rows": 17, "filtered": 100, "attached_condition": "((t1.b = 'a') and (t1.a2 >= 'b'))", - "using_index": true + "using_index_for_group_by": true } } } @@ -1059,14 +1059,14 @@ EXPLAIN "select_id": 1, "table": { "table_name": "t1", - "access_type": "index", + "access_type": "range", "key": "idx_t1_1", "key_length": "163", "used_key_parts": ["a1", "a2", "b", "c"], - "rows": 128, + "rows": 65, "filtered": 100, "attached_condition": "((t1.b = 'a') and (t1.c = 'i121') and (t1.a2 >= 'b'))", - "using_index": true + "using_index_for_group_by": "scanning" } } } diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index cd7f1014ec0..ec3f4d9bf99 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -1707,13 +1707,13 @@ select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1 a1 a2 b explain select distinct a1,a2,b from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using index +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 100.00 Using where; Using index +1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 50.78 Using where; Using index Warnings: Note 1003 select distinct `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`b` = 'a') and (`test`.`t1`.`c` = 'i121') and (`test`.`t1`.`a2` >= 'b')) explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); @@ -1724,13 +1724,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index explain select distinct a1,a2,b from t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL idx_t2_2 146 NULL # Using index +1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using index for group-by explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL idx_t2_2 146 NULL # Using where; Using index +1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 100.00 Using where; Using index +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 50.61 Using where; Using index Warnings: Note 1003 select distinct `test`.`t2`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`b` = 'a') and (`test`.`t2`.`c` = 'i121') and (`test`.`t2`.`a2` >= 'b')) explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); @@ -1855,7 +1855,7 @@ c e d e explain select distinct a1,a2,b from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using index +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by @@ -1870,7 +1870,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by; Using temporary; Using filesort explain select distinct a1,a2,b from t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL idx_t2_2 146 NULL # Using index +1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using index for group-by explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by @@ -1953,10 +1953,10 @@ b a explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 65 Using where; Using index for group-by (scanning) explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 100.00 Using where; Using index for group-by @@ -2173,7 +2173,7 @@ c d explain select distinct a1 from t1 where a2 = 'b'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 5 Using where; Using index for group-by select distinct a1 from t1 where a2 = 'b'; a1 a @@ -2283,7 +2283,7 @@ INSERT INTO t1 (a) VALUES ('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN'); EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 66 NULL 11 Using index +1 SIMPLE t1 range NULL a 66 NULL 6 Using index for group-by SELECT DISTINCT a,a FROM t1 ORDER BY a; a a @@ -2499,7 +2499,7 @@ INSERT INTO t1 VALUES (4), (2), (1), (2), (2), (4), (1), (4); EXPLAIN SELECT DISTINCT(a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx 5 NULL 16 Using index +1 SIMPLE t1 range NULL idx 5 NULL 9 Using index for group-by SELECT DISTINCT(a) FROM t1; a 1 @@ -2507,7 +2507,7 @@ a 4 EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx 5 NULL 16 Using index +1 SIMPLE t1 range NULL idx 5 NULL 9 Using index for group-by SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; a 1 @@ -2646,7 +2646,7 @@ INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT a,b,c+1,d FROM t1; EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL foo 20 NULL 32 Using where; Using index +1 SIMPLE t1 range NULL foo 10 NULL 9 Using where; Using index for group-by SELECT DISTINCT c FROM t1 WHERE d=4; c 1 @@ -3339,19 +3339,19 @@ INSERT INTO t2 SELECT a, b + 4, c,d,e,f FROM t2; INSERT INTO t2 SELECT a + 1, b, c,d,e,f FROM t2; EXPLAIN SELECT COUNT(DISTINCT a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 10 NULL 16 Using index +1 SIMPLE t1 range NULL a 5 NULL 9 Using index for group-by SELECT COUNT(DISTINCT a) FROM t1; COUNT(DISTINCT a) 2 EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 10 NULL 16 Using index +1 SIMPLE t1 range NULL a 10 NULL 9 Using index for group-by SELECT COUNT(DISTINCT a,b) FROM t1; COUNT(DISTINCT a,b) 16 EXPLAIN SELECT COUNT(DISTINCT b,a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 10 NULL 16 Using index +1 SIMPLE t1 range NULL a 10 NULL 9 Using index for group-by SELECT COUNT(DISTINCT b,a) FROM t1; COUNT(DISTINCT b,a) 16 @@ -3414,7 +3414,7 @@ COUNT(DISTINCT a) 2 EXPLAIN SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 10 NULL 16 Using index +1 SIMPLE t1 range NULL a 5 NULL 9 Using index for group-by SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; 1 1 @@ -3435,19 +3435,19 @@ COUNT(DISTINCT t1_1.a) 1 EXPLAIN SELECT COUNT(DISTINCT a), 12 FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 10 NULL 16 Using index +1 SIMPLE t1 range NULL a 5 NULL 9 Using index for group-by SELECT COUNT(DISTINCT a), 12 FROM t1; COUNT(DISTINCT a) 12 2 12 EXPLAIN SELECT COUNT(DISTINCT a, b, c) FROM t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL a 15 NULL 16 Using index +1 SIMPLE t2 range NULL a 15 NULL 9 Using index for group-by SELECT COUNT(DISTINCT a, b, c) FROM t2; COUNT(DISTINCT a, b, c) 16 EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL a 15 NULL 16 Using index +1 SIMPLE t2 range NULL a 5 NULL 9 Using index for group-by SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; COUNT(DISTINCT a) SUM(DISTINCT a) AVG(DISTINCT a) 2 3 1.5000 @@ -3459,7 +3459,7 @@ COUNT(DISTINCT a) SUM(DISTINCT a) AVG(DISTINCT f) 2 3 1.0000 EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL a 15 NULL 16 Using index +1 SIMPLE t2 range NULL a 10 NULL 9 Using index for group-by SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; COUNT(DISTINCT a, b) COUNT(DISTINCT b, a) 16 16 @@ -3893,5 +3893,22 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index a a 13 NULL 2 Using where; Using index drop table t1; # +# MDEV-15433: Optimizer does not use group by optimization with distinct +# +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)); +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +EXPLAIN SELECT DISTINCT a FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL a 4 NULL 5 Using index for group-by +SELECT DISTINCT a FROM t1; +a +1 +2 +3 +4 +drop table t1; +# # End of 10.1 tests # diff --git a/mysql-test/suite/vcol/r/vcol_select_innodb.result b/mysql-test/suite/vcol/r/vcol_select_innodb.result index 5c29f5f8283..97bfbbe4eaf 100644 --- a/mysql-test/suite/vcol/r/vcol_select_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_select_innodb.result @@ -133,7 +133,7 @@ count(distinct c) 3 explain select count(distinct c) from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL c 5 NULL 5 Using index +1 SIMPLE t1 range NULL c 5 NULL 6 Using index for group-by (scanning) ### ### filesort & range-based utils ### diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index b0bc42d7f8c..adad9073235 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -1608,6 +1608,33 @@ explain select min(a) from t1 where a between "a" and "Cafeeeeeeeeeeeeeeeeeeeeee explain select min(a) from t1 where a between "abbbbbbbbbbbbbbbbbbbb" and "Cafe2"; drop table t1; +--echo # +--echo # MDEV-15433: Optimizer does not use group by optimization with distinct +--echo # + +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)); +--disable_query_log +INSERT INTO t1(a) VALUES (1), (2), (3), (4); +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +--enable_query_log +OPTIMIZE TABLE t1; +EXPLAIN SELECT DISTINCT a FROM t1; +SELECT DISTINCT a FROM t1; +drop table t1; + --echo # --echo # End of 10.1 tests --echo # diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 64d1565dec7..d1b1c17f178 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1388,6 +1388,14 @@ JOIN::optimize_inner() error= 1; DBUG_RETURN(1); } + if (!group_list) + { + /* The output has only one row */ + order=0; + simple_order=1; + group_optimized_away= 1; + select_distinct=0; + } } /* Calculate how to do the join */ @@ -5754,7 +5762,7 @@ add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab) Item_field *cur_item; key_map possible_keys(0); - if (join->group_list || join->simple_group) + if (join->group_list) { /* Collect all query fields referenced in the GROUP clause. */ for (cur_group= join->group_list; cur_group; cur_group= cur_group->next) (*cur_group->item)->walk(&Item::collect_item_field_processor, 0,

1 0

[Commits] 519dfbe775d: MDEV-15433: Optimizer does not use group by optimization with distinct
by Varun 04 Aug '18

04 Aug '18

revision-id: 519dfbe775d8f6fa28b780b02ee1016cdca08f9f (mariadb-10.1.34-36-g519dfbe775d) parent(s): 2fb68244b4246221e4f605749913bfe582a4d040 author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-04 05:57:06 +0530 message: MDEV-15433: Optimizer does not use group by optimization with distinct The issue is that with distinct group by optimization does not work,this happens because the current code ignores GROUP BY optimization when we have a simple group , simple group here means that the GROUP BY has all fields belonging to the first non-const table of the join. This bug was introduced by the commit b76b69cd5fe634d8ddb9406aa2c82ef2a375b4d8. So we need to revert it. Also when we have a constant GROUP BY clause , then we are sure that we would only return 1 row for the table so there is no point of evaluating DISTINCT and ORDER BY clauses of the query. --- mysql-test/r/bench_count_distinct.result | 2 +- mysql-test/r/distinct.result | 2 +- mysql-test/r/explain_json.result | 18 +++---- mysql-test/r/group_min_max.result | 63 ++++++++++++++--------- mysql-test/suite/vcol/r/vcol_select_innodb.result | 2 +- mysql-test/t/group_min_max.test | 27 ++++++++++ sql/sql_select.cc | 16 +++++- 7 files changed, 94 insertions(+), 36 deletions(-) diff --git a/mysql-test/r/bench_count_distinct.result b/mysql-test/r/bench_count_distinct.result index 79e12afd237..8b67e4be38a 100644 --- a/mysql-test/r/bench_count_distinct.result +++ b/mysql-test/r/bench_count_distinct.result @@ -5,7 +5,7 @@ count(distinct n) 100 explain extended select count(distinct n) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index NULL n 4 NULL 200 100.00 Using index +1 SIMPLE t1 range NULL n 4 NULL 10 100.00 Using index for group-by Warnings: Note 1003 select count(distinct `test`.`t1`.`n`) AS `count(distinct n)` from `test`.`t1` drop table t1; diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index b5e8cefca69..d6e5a69e217 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -212,7 +212,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using index explain SELECT distinct a from t3 order by a desc limit 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index NULL a 5 NULL 2 Using index +1 SIMPLE t3 index NULL a 5 NULL 40 Using index explain SELECT distinct a,b from t3 order by a+1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 204 Using temporary; Using filesort diff --git a/mysql-test/r/explain_json.result b/mysql-test/r/explain_json.result index 9e5515f3cfa..0abbda352b7 100644 --- a/mysql-test/r/explain_json.result +++ b/mysql-test/r/explain_json.result @@ -1030,10 +1030,10 @@ Table Op Msg_type Msg_text test.t1 analyze status Table is already up to date explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 65 Using where; Using index for group-by (scanning) explain format=json select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); EXPLAIN { @@ -1041,14 +1041,14 @@ EXPLAIN "select_id": 1, "table": { "table_name": "t1", - "access_type": "index", - "key": "idx_t1_2", + "access_type": "range", + "key": "idx_t1_1", "key_length": "147", "used_key_parts": ["a1", "a2", "b"], - "rows": 128, + "rows": 17, "filtered": 100, "attached_condition": "((t1.b = 'a') and (t1.a2 >= 'b'))", - "using_index": true + "using_index_for_group_by": true } } } @@ -1059,14 +1059,14 @@ EXPLAIN "select_id": 1, "table": { "table_name": "t1", - "access_type": "index", + "access_type": "range", "key": "idx_t1_1", "key_length": "163", "used_key_parts": ["a1", "a2", "b", "c"], - "rows": 128, + "rows": 65, "filtered": 100, "attached_condition": "((t1.b = 'a') and (t1.c = 'i121') and (t1.a2 >= 'b'))", - "using_index": true + "using_index_for_group_by": "scanning" } } } diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index cd7f1014ec0..ec3f4d9bf99 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -1707,13 +1707,13 @@ select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by a1 a1 a2 b explain select distinct a1,a2,b from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using index +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by explain extended select distinct a1,a2,b,c from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 100.00 Using where; Using index +1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 50.78 Using where; Using index Warnings: Note 1003 select distinct `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`b` = 'a') and (`test`.`t1`.`c` = 'i121') and (`test`.`t1`.`a2` >= 'b')) explain select distinct a1,a2,b from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); @@ -1724,13 +1724,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index explain select distinct a1,a2,b from t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL idx_t2_2 146 NULL # Using index +1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using index for group-by explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL idx_t2_2 146 NULL # Using where; Using index +1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by explain extended select distinct a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 100.00 Using where; Using index +1 SIMPLE t2 index NULL idx_t2_1 163 NULL 164 50.61 Using where; Using index Warnings: Note 1003 select distinct `test`.`t2`.`a1` AS `a1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where ((`test`.`t2`.`b` = 'a') and (`test`.`t2`.`c` = 'i121') and (`test`.`t2`.`a2` >= 'b')) explain select distinct a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); @@ -1855,7 +1855,7 @@ c e d e explain select distinct a1,a2,b from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using index +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using index for group-by explain select distinct a1,a2,b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by @@ -1870,7 +1870,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by; Using temporary; Using filesort explain select distinct a1,a2,b from t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL idx_t2_2 146 NULL # Using index +1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using index for group-by explain select distinct a1,a2,b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range NULL idx_t2_1 146 NULL # Using where; Using index for group-by @@ -1953,10 +1953,10 @@ b a explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index +1 SIMPLE t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index +1 SIMPLE t1 range NULL idx_t1_1 163 NULL 65 Using where; Using index for group-by (scanning) explain extended select count(distinct a1,a2,b) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c'); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_1 147 NULL 14 100.00 Using where; Using index for group-by @@ -2173,7 +2173,7 @@ c d explain select distinct a1 from t1 where a2 = 'b'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using where; Using index +1 SIMPLE t1 range NULL idx_t1_1 130 NULL 5 Using where; Using index for group-by select distinct a1 from t1 where a2 = 'b'; a1 a @@ -2283,7 +2283,7 @@ INSERT INTO t1 (a) VALUES ('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN'); EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 66 NULL 11 Using index +1 SIMPLE t1 range NULL a 66 NULL 6 Using index for group-by SELECT DISTINCT a,a FROM t1 ORDER BY a; a a @@ -2499,7 +2499,7 @@ INSERT INTO t1 VALUES (4), (2), (1), (2), (2), (4), (1), (4); EXPLAIN SELECT DISTINCT(a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx 5 NULL 16 Using index +1 SIMPLE t1 range NULL idx 5 NULL 9 Using index for group-by SELECT DISTINCT(a) FROM t1; a 1 @@ -2507,7 +2507,7 @@ a 4 EXPLAIN SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL idx 5 NULL 16 Using index +1 SIMPLE t1 range NULL idx 5 NULL 9 Using index for group-by SELECT SQL_BIG_RESULT DISTINCT(a) FROM t1; a 1 @@ -2646,7 +2646,7 @@ INSERT INTO t1 SELECT * FROM t1; INSERT INTO t1 SELECT a,b,c+1,d FROM t1; EXPLAIN SELECT DISTINCT c FROM t1 WHERE d=4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL foo 20 NULL 32 Using where; Using index +1 SIMPLE t1 range NULL foo 10 NULL 9 Using where; Using index for group-by SELECT DISTINCT c FROM t1 WHERE d=4; c 1 @@ -3339,19 +3339,19 @@ INSERT INTO t2 SELECT a, b + 4, c,d,e,f FROM t2; INSERT INTO t2 SELECT a + 1, b, c,d,e,f FROM t2; EXPLAIN SELECT COUNT(DISTINCT a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 10 NULL 16 Using index +1 SIMPLE t1 range NULL a 5 NULL 9 Using index for group-by SELECT COUNT(DISTINCT a) FROM t1; COUNT(DISTINCT a) 2 EXPLAIN SELECT COUNT(DISTINCT a,b) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 10 NULL 16 Using index +1 SIMPLE t1 range NULL a 10 NULL 9 Using index for group-by SELECT COUNT(DISTINCT a,b) FROM t1; COUNT(DISTINCT a,b) 16 EXPLAIN SELECT COUNT(DISTINCT b,a) FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 10 NULL 16 Using index +1 SIMPLE t1 range NULL a 10 NULL 9 Using index for group-by SELECT COUNT(DISTINCT b,a) FROM t1; COUNT(DISTINCT b,a) 16 @@ -3414,7 +3414,7 @@ COUNT(DISTINCT a) 2 EXPLAIN SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 10 NULL 16 Using index +1 SIMPLE t1 range NULL a 5 NULL 9 Using index for group-by SELECT 1 FROM t1 HAVING COUNT(DISTINCT a) < 10; 1 1 @@ -3435,19 +3435,19 @@ COUNT(DISTINCT t1_1.a) 1 EXPLAIN SELECT COUNT(DISTINCT a), 12 FROM t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 10 NULL 16 Using index +1 SIMPLE t1 range NULL a 5 NULL 9 Using index for group-by SELECT COUNT(DISTINCT a), 12 FROM t1; COUNT(DISTINCT a) 12 2 12 EXPLAIN SELECT COUNT(DISTINCT a, b, c) FROM t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL a 15 NULL 16 Using index +1 SIMPLE t2 range NULL a 15 NULL 9 Using index for group-by SELECT COUNT(DISTINCT a, b, c) FROM t2; COUNT(DISTINCT a, b, c) 16 EXPLAIN SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL a 15 NULL 16 Using index +1 SIMPLE t2 range NULL a 5 NULL 9 Using index for group-by SELECT COUNT(DISTINCT a), SUM(DISTINCT a), AVG(DISTINCT a) FROM t2; COUNT(DISTINCT a) SUM(DISTINCT a) AVG(DISTINCT a) 2 3 1.5000 @@ -3459,7 +3459,7 @@ COUNT(DISTINCT a) SUM(DISTINCT a) AVG(DISTINCT f) 2 3 1.0000 EXPLAIN SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index NULL a 15 NULL 16 Using index +1 SIMPLE t2 range NULL a 10 NULL 9 Using index for group-by SELECT COUNT(DISTINCT a, b), COUNT(DISTINCT b, a) FROM t2; COUNT(DISTINCT a, b) COUNT(DISTINCT b, a) 16 16 @@ -3893,5 +3893,22 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index a a 13 NULL 2 Using where; Using index drop table t1; # +# MDEV-15433: Optimizer does not use group by optimization with distinct +# +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)); +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +EXPLAIN SELECT DISTINCT a FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL a 4 NULL 5 Using index for group-by +SELECT DISTINCT a FROM t1; +a +1 +2 +3 +4 +drop table t1; +# # End of 10.1 tests # diff --git a/mysql-test/suite/vcol/r/vcol_select_innodb.result b/mysql-test/suite/vcol/r/vcol_select_innodb.result index 5c29f5f8283..97bfbbe4eaf 100644 --- a/mysql-test/suite/vcol/r/vcol_select_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_select_innodb.result @@ -133,7 +133,7 @@ count(distinct c) 3 explain select count(distinct c) from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL c 5 NULL 5 Using index +1 SIMPLE t1 range NULL c 5 NULL 6 Using index for group-by (scanning) ### ### filesort & range-based utils ### diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index b0bc42d7f8c..adad9073235 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -1608,6 +1608,33 @@ explain select min(a) from t1 where a between "a" and "Cafeeeeeeeeeeeeeeeeeeeeee explain select min(a) from t1 where a between "abbbbbbbbbbbbbbbbbbbb" and "Cafe2"; drop table t1; +--echo # +--echo # MDEV-15433: Optimizer does not use group by optimization with distinct +--echo # + +CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)); +--disable_query_log +INSERT INTO t1(a) VALUES (1), (2), (3), (4); +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +INSERT INTO t1(a) SELECT a FROM t1; +--enable_query_log +OPTIMIZE TABLE t1; +EXPLAIN SELECT DISTINCT a FROM t1; +SELECT DISTINCT a FROM t1; +drop table t1; + --echo # --echo # End of 10.1 tests --echo # diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 64d1565dec7..0c60e22b2d3 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1389,6 +1389,20 @@ JOIN::optimize_inner() DBUG_RETURN(1); } } + + /* + If we have optimized away GROUP BY somehow (maybe because GROUP BY + only contained constants). For such cases we are sure we would + only return 1 row in the result, so we can avoid doing DISTINCT + and ORDER BY. + */ + if (group && !group_list) + { + order=0; + simple_order=1; + group_optimized_away= 1; + select_distinct=0; + } /* Calculate how to do the join */ THD_STAGE_INFO(thd, stage_statistics); @@ -5754,7 +5768,7 @@ add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab) Item_field *cur_item; key_map possible_keys(0); - if (join->group_list || join->simple_group) + if (join->group_list) { /* Collect all query fields referenced in the GROUP clause. */ for (cur_group= join->group_list; cur_group; cur_group= cur_group->next) (*cur_group->item)->walk(&Item::collect_item_field_processor, 0,

1 0

[Commits] b4ab5a86ca9: MDEV-16315: NOT FOUND condition not triggered in stored aggregate functions
by Varun 03 Aug '18

03 Aug '18

revision-id: b4ab5a86ca92c873b5352d2013072d2f9050afd0 (mariadb-10.3.7-54-gb4ab5a86ca9) parent(s): b534a7b89ef39cd75bfe09524be044bc16ea2f48 author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-04 03:32:26 +0530 message: MDEV-16315: NOT FOUND condition not triggered in stored aggregate functions Aggregate function has: - initialization code - code that is run for every row - code that generates a result For an empty table we don't run the middle part but for custom aggregate we are running the middle part also. Fixed this by only allowing the generation of result. As soon as we encounter the first FETCH GROUP NEXT ROW instruction with empty tables, we should exit and tell the handler that there is nothing to fetch and return the result. --- mysql-test/main/custom_aggregate_functions.result | 44 ++++++++++++++++++++--- mysql-test/main/custom_aggregate_functions.test | 43 ++++++++++++++++++++++ sql/sp_head.cc | 17 +++++---- 3 files changed, 91 insertions(+), 13 deletions(-) diff --git a/mysql-test/main/custom_aggregate_functions.result b/mysql-test/main/custom_aggregate_functions.result index 4060d6665f6..e85145c040c 100644 --- a/mysql-test/main/custom_aggregate_functions.result +++ b/mysql-test/main/custom_aggregate_functions.result @@ -84,8 +84,7 @@ return (select count(*) + f2( i - 1) from t1 where id = i); end if; end| select f2(1)| -f2(1) -3 +ERROR 02000: No data - zero rows fetched, selected, or processed select f2(2)| ERROR HY000: Recursive stored functions and triggers are not allowed select f2(3)| @@ -109,7 +108,7 @@ f1(sal) 6000 select f1(sal) from t1 where 1=0; f1(sal) -NULL +0 drop function f1; create aggregate function f1(x int) returns int begin @@ -464,7 +463,7 @@ f1(sal) set @param= 5; execute test using @param; f1(sal) -NULL +0 deallocate prepare test; drop function f2; prepare test from "select f1(sal) from t1 where id>= ?"; @@ -1153,3 +1152,40 @@ i sum(i) NULL 8 drop function agg_sum; drop table t1; +# +# MDEV-16315: NOT FOUND condition not triggered in stored aggregate functions +# +CREATE AGGREGATE FUNCTION test1(p_value TEXT) +RETURNS BOOL +BEGIN +DECLARE CONTINUE HANDLER +FOR NOT FOUND +BEGIN +RETURN FALSE; +END; +FETCH GROUP NEXT ROW; +RETURN TRUE; +END| +CREATE OR REPLACE TABLE t1 (n TEXT); +SELECT test1(n) FROM t1; +test1(n) +0 +CREATE OR REPLACE AGGREGATE FUNCTION test2(p_value TEXT) +RETURNS BOOL +BEGIN +DECLARE CONTINUE HANDLER +FOR NOT FOUND +BEGIN +RETURN FALSE; +END; +FETCH GROUP NEXT ROW; +FETCH GROUP NEXT ROW; +FETCH GROUP NEXT ROW; +RETURN TRUE; +END| +SELECT test2(n) FROM t1; +test2(n) +0 +drop function test1; +drop function test2; +drop table t1; diff --git a/mysql-test/main/custom_aggregate_functions.test b/mysql-test/main/custom_aggregate_functions.test index ab799b48bdb..2c981ac518d 100644 --- a/mysql-test/main/custom_aggregate_functions.test +++ b/mysql-test/main/custom_aggregate_functions.test @@ -69,6 +69,7 @@ begin return (select count(*) + f2( i - 1) from t1 where id = i); end if; end| +--error 1329 select f2(1)| # Since currently recursive functions are disallowed ER_SP_NO_RECURSION # error will be returned, once we will allow them error about @@ -965,3 +966,45 @@ select i, sum(i) from t1 group by i with rollup; # Cleanup drop function agg_sum; drop table t1; + +--echo # +--echo # MDEV-16315: NOT FOUND condition not triggered in stored aggregate functions +--echo # + +delimiter |; +CREATE AGGREGATE FUNCTION test1(p_value TEXT) + RETURNS BOOL +BEGIN + DECLARE CONTINUE HANDLER + FOR NOT FOUND + BEGIN + RETURN FALSE; + END; + FETCH GROUP NEXT ROW; + RETURN TRUE; +END| + +delimiter ;| +CREATE OR REPLACE TABLE t1 (n TEXT); +SELECT test1(n) FROM t1; + +delimiter |; +CREATE OR REPLACE AGGREGATE FUNCTION test2(p_value TEXT) + RETURNS BOOL + BEGIN + DECLARE CONTINUE HANDLER + FOR NOT FOUND + BEGIN + RETURN FALSE; + END; + FETCH GROUP NEXT ROW; + FETCH GROUP NEXT ROW; + FETCH GROUP NEXT ROW; + RETURN TRUE; + END| + +delimiter ;| +SELECT test2(n) FROM t1; +drop function test1; +drop function test2; +drop table t1; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 3a9e83d9d09..1f70dcf9244 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -4403,6 +4403,13 @@ sp_instr_agg_cfetch::execute(THD *thd, uint *nextp) { DBUG_ENTER("sp_instr_agg_cfetch::execute"); int res= 0; + if (thd->server_status == SERVER_STATUS_LAST_ROW_SENT) + { + my_message(ER_SP_FETCH_NO_DATA, + ER_THD(thd, ER_SP_FETCH_NO_DATA), MYF(0)); + res= -1; + thd->spcont->quit_func= TRUE; + } if (!thd->spcont->instr_ptr) { *nextp= m_ip+1; @@ -4413,15 +4420,7 @@ sp_instr_agg_cfetch::execute(THD *thd, uint *nextp) else { thd->spcont->pause_state= FALSE; - if (thd->server_status == SERVER_STATUS_LAST_ROW_SENT) - { - my_message(ER_SP_FETCH_NO_DATA, - ER_THD(thd, ER_SP_FETCH_NO_DATA), MYF(0)); - res= -1; - thd->spcont->quit_func= TRUE; - } - else - *nextp= m_ip + 1; + *nextp= m_ip + 1; } DBUG_RETURN(res); }

1 0

[Commits] 6bdde42: MDEV-16801 seg_fault on a query
by IgorBabaev 03 Aug '18

03 Aug '18

revision-id: 6bdde42d9964389894ab14480413f51055e385e0 (mariadb-10.3.7-101-g6bdde42) parent(s): 0c745c743c65ceb6533babdf21556b633e81566c author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-03 14:54:02 -0700 message: MDEV-16801 seg_fault on a query The bug was in the in the code of JOIN::check_for_splittable_materialized() where the structures describing the fields of a materialized derived table that potentially could be used in split optimization were build. As a result of this bug some fields that were not usable for splitting were detected as usable. This could trigger crashes further in st_join_table::choose_best_splitting(). --- mysql-test/main/derived_cond_pushdown.result | 54 ++++++++++++++++++++++++++++ mysql-test/main/derived_cond_pushdown.test | 33 +++++++++++++++++ sql/opt_split.cc | 3 +- 3 files changed, 89 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 0cd8b4c..fea6d61 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -15960,3 +15960,57 @@ f c 9 1 DROP VIEW v1; DROP TABLE t1; +# +# MDEV-16801: splittable materialized derived/views with +# one grouping field from table without keys +# +CREATE TABLE t1 (a int, b int, INDEX idx_a(a), INDEX idx_b(b)) ENGINE=MYISAM; +CREATE TABLE t2 (c int) ENGINE=MYISAM; +CREATE TABLE t3 (d int) ENGINE=MYISAM; +INSERT INTO t1 VALUES +(77,7), (11,1), (33,3), (44,4), (8,88), +(78,7), (98,9), (38,3), (28,2), (79,7), +(58,5), (42,4), (71,7), (27,2), (91,9); +INSERT INTO t1 SELECT a+100, b+10 FROM t1; +INSERT INTO t2 VALUES +(100), (700), (200), (100), (200); +INSERT INTO t3 VALUES +(3), (4), (1), (8), (3); +ANALYZE tables t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +SELECT * +FROM t3, +(SELECT t1.b, t2.c +FROM t1, t2 +GROUP BY t1.b,t2.c) dt +WHERE t3.d = dt.b; +d b c +3 3 700 +3 3 200 +3 3 100 +4 4 700 +4 4 200 +4 4 100 +1 1 700 +1 1 200 +1 1 100 +3 3 700 +3 3 200 +3 3 100 +EXPLAIN EXTENDED SELECT * +FROM t3, +(SELECT t1.b, t2.c +FROM t1, t2 +GROUP BY t1.b,t2.c) dt +WHERE t3.d = dt.b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t3.d 2 100.00 +2 LATERAL DERIVED t1 ref idx_b idx_b 5 test.t3.d 2 100.00 Using index; Using temporary; Using filesort +2 LATERAL DERIVED t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 /* select#1 */ select `test`.`t3`.`d` AS `d`,`dt`.`b` AS `b`,`dt`.`c` AS `c` from `test`.`t3` join (/* select#2 */ select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`b` = `test`.`t3`.`d` group by `test`.`t1`.`b`,`test`.`t2`.`c`) `dt` where `dt`.`b` = `test`.`t3`.`d` +DROP TABLE t1,t2,t3; diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index e51646a..d918e48 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -2906,3 +2906,36 @@ SELECT * FROM ( SELECT STRAIGHT_JOIN f, COUNT(*) as c FROM v1 GROUP BY f ) AS s; DROP VIEW v1; DROP TABLE t1; + +--echo # +--echo # MDEV-16801: splittable materialized derived/views with +--echo # one grouping field from table without keys +--echo # + +CREATE TABLE t1 (a int, b int, INDEX idx_a(a), INDEX idx_b(b)) ENGINE=MYISAM; +CREATE TABLE t2 (c int) ENGINE=MYISAM; +CREATE TABLE t3 (d int) ENGINE=MYISAM; +INSERT INTO t1 VALUES + (77,7), (11,1), (33,3), (44,4), (8,88), + (78,7), (98,9), (38,3), (28,2), (79,7), + (58,5), (42,4), (71,7), (27,2), (91,9); +INSERT INTO t1 SELECT a+100, b+10 FROM t1; +INSERT INTO t2 VALUES + (100), (700), (200), (100), (200); +INSERT INTO t3 VALUES + (3), (4), (1), (8), (3); + +ANALYZE tables t1,t2,t3; + +let $q= +SELECT * + FROM t3, + (SELECT t1.b, t2.c + FROM t1, t2 + GROUP BY t1.b,t2.c) dt +WHERE t3.d = dt.b; + +eval $q; +eval EXPLAIN EXTENDED $q; + +DROP TABLE t1,t2,t3; diff --git a/sql/opt_split.cc b/sql/opt_split.cc index 37853bd..611e703 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -413,7 +413,8 @@ bool JOIN::check_for_splittable_materialized() for (cand= cand_start; cand < cand_end; cand++) { - if (cand->underlying_field->field_index + 1 == fldnr) + if (cand->underlying_field->table == table && + cand->underlying_field->field_index + 1 == fldnr) { cand->is_usable_for_ref_access= true; break;

1 0