- commits - lists.mariadb.org

[Commits] 40f6bcb856c: Add missing WSREP(thd) condition and remove unnecessary DBUG_RETURN.
by jan 02 Aug '18

02 Aug '18

revision-id: 40f6bcb856ce05e15efd3070663af2686182147d (mariadb-galera-10.0.35-16-g40f6bcb856c) parent(s): 9b29bda0d67ab15299659c9ad4046ab4f5ca7b6a author: Jan Lindström committer: Jan Lindström timestamp: 2018-08-02 20:35:44 +0300 message: Add missing WSREP(thd) condition and remove unnecessary DBUG_RETURN. --- sql/sql_alter.cc | 5 ++--- 1 file changed, 2 insertions(+), 3 deletions(-) diff --git a/sql/sql_alter.cc b/sql/sql_alter.cc index 1129b209775..0bf2d4be2ad 100644 --- a/sql/sql_alter.cc +++ b/sql/sql_alter.cc @@ -308,7 +308,8 @@ bool Sql_cmd_alter_table::execute(THD *thd) #ifdef WITH_WSREP TABLE *find_temporary_table(THD *thd, const TABLE_LIST *tl); - if ((!thd->is_current_stmt_binlog_format_row() || + if (WSREP(thd) && + (!thd->is_current_stmt_binlog_format_row() || !find_temporary_table(thd, first_table))) { WSREP_TO_ISOLATION_BEGIN_ALTER(((lex->name.str) ? select_lex->db : NULL), @@ -329,8 +330,6 @@ bool Sql_cmd_alter_table::execute(THD *thd) lex->ignore); DBUG_RETURN(result); - - DBUG_RETURN(result); #ifdef WITH_WSREP error: {

1 0

[Commits] 21e5aca3e26: Fix merge error.
by jan 02 Aug '18

02 Aug '18

revision-id: 21e5aca3e26a480a60c5e640a44a47601ba0a289 (mariadb-galera-10.0.35-16-g21e5aca3e26) parent(s): 9b29bda0d67ab15299659c9ad4046ab4f5ca7b6a author: Jan Lindström committer: Jan Lindström timestamp: 2018-08-02 18:35:20 +0300 message: Fix merge error. --- sql/sql_alter.cc | 5 ++--- 1 file changed, 2 insertions(+), 3 deletions(-) diff --git a/sql/sql_alter.cc b/sql/sql_alter.cc index 1129b209775..0bf2d4be2ad 100644 --- a/sql/sql_alter.cc +++ b/sql/sql_alter.cc @@ -308,7 +308,8 @@ bool Sql_cmd_alter_table::execute(THD *thd) #ifdef WITH_WSREP TABLE *find_temporary_table(THD *thd, const TABLE_LIST *tl); - if ((!thd->is_current_stmt_binlog_format_row() || + if (WSREP(thd) && + (!thd->is_current_stmt_binlog_format_row() || !find_temporary_table(thd, first_table))) { WSREP_TO_ISOLATION_BEGIN_ALTER(((lex->name.str) ? select_lex->db : NULL), @@ -329,8 +330,6 @@ bool Sql_cmd_alter_table::execute(THD *thd) lex->ignore); DBUG_RETURN(result); - - DBUG_RETURN(result); #ifdef WITH_WSREP error: {

1 0

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

02 Aug '18

revision-id: 2a3559a84a12a1864e8987e5396fcef86768ce42 (mariadb-10.1.34-36-g2a3559a84a1) parent(s): 2fb68244b4246221e4f605749913bfe582a4d040 author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-02 16:55:39 +0530 message: MDEV-15433: Optimizer does not use group by optimization with distinct The group-by optimization does not work with DISTINCT because we are not doing range analysis on the items in the select list. The solution is to do range analyis on the items in the select list when we have DISTINCT Also we should allow this group-by optimization with DISTINCT only if the quert does not have GROUP BY. --- 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 | 87 +++++++++++++++++++++++--------- mysql-test/t/group_min_max.test | 27 ++++++++++ sql/opt_range.cc | 10 ++++ sql/sql_select.cc | 2 +- 7 files changed, 113 insertions(+), 35 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..fe351cef39d 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,46 @@ 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)); +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; +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 +EXPLAIN SELECT a FROM t1 GROUP BY a; +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 a FROM t1 GROUP BY a; +a +1 +2 +3 +4 +DROP TABLE t1; +# # End of 10.1 tests # diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index b0bc42d7f8c..ebf005d32e1 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)); +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; +OPTIMIZE TABLE t1; +EXPLAIN SELECT DISTINCT a FROM t1; +SELECT DISTINCT a FROM t1; +EXPLAIN SELECT a FROM t1 GROUP BY a; +SELECT a FROM t1 GROUP BY a; +DROP TABLE t1; + --echo # --echo # End of 10.1 tests --echo # diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 0315b91b8be..9421319be99 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -12147,6 +12147,16 @@ get_best_group_min_max(PARAM *param, SEL_TREE *tree, double read_time) if (join->make_sum_func_list(join->all_fields, join->fields_list, 1)) DBUG_RETURN(NULL); + /* + If the query has GROUP BY but due to some case it gets optimized away(an example would + be constants in GROUP BY clause) then JOIN::group_list would be NULL. For such a case + we know there would be only one row in the result and we cannot change DISTINCT to GROUP BY + as this can give wrong results. So group-by optimization is used for DISTINCT only if we + don't have a GROUP BY clause in the query. + */ + if (!join->group_list && join->group && join->select_distinct) + DBUG_RETURN(NULL); + List_iterator<Item> select_items_it(join->fields_list); is_agg_distinct = is_indexed_agg_distinct(join, &agg_distinct_flds); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 64d1565dec7..e5430d730bb 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -5754,7 +5754,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] 9b29bda0d67: Merge remote-tracking branch 'origin/5.5-galera' into 10.0-galera
by jan 02 Aug '18

02 Aug '18

revision-id: 9b29bda0d67ab15299659c9ad4046ab4f5ca7b6a (mariadb-galera-10.0.35-15-g9b29bda0d67) parent(s): c5a8583b3146613429d88c1c044de88a4c4c94b0 e88e26b4242e1c030bb138fca7ea2c916dbe6a76 author: Jan Lindström committer: Jan Lindström timestamp: 2018-08-02 13:13:21 +0300 message: Merge remote-tracking branch 'origin/5.5-galera' into 10.0-galera mysql-test/suite/galera/r/mysql-wsrep#332.result | 111 ++++++++++++ mysql-test/suite/galera/t/mysql-wsrep#332.test | 113 ++++++++++++ sql/sql_alter.cc | 17 +- sql/sql_parse.h | 5 + sql/wsrep_mysqld.cc | 217 +++++++++++++---------- sql/wsrep_mysqld.h | 4 +- 6 files changed, 369 insertions(+), 98 deletions(-) diff --cc sql/sql_alter.cc index a39f07ae35d,e343808cf77..1129b209775 --- a/sql/sql_alter.cc +++ b/sql/sql_alter.cc @@@ -308,12 -102,17 +308,16 @@@ bool Sql_cmd_alter_table::execute(THD * #ifdef WITH_WSREP TABLE *find_temporary_table(THD *thd, const TABLE_LIST *tl); - if (WSREP(thd) && - (!thd->is_current_stmt_binlog_format_row() || + if ((!thd->is_current_stmt_binlog_format_row() || !find_temporary_table(thd, first_table))) { - WSREP_TO_ISOLATION_BEGIN(((lex->name.str) ? select_lex->db : NULL), - ((lex->name.str) ? lex->name.str : NULL), - first_table); + WSREP_TO_ISOLATION_BEGIN_ALTER(((lex->name.str) ? select_lex->db : NULL), + ((lex->name.str) ? lex->name.str : NULL), + first_table, + &alter_info); + + thd->variables.auto_increment_offset = 1; + thd->variables.auto_increment_increment = 1; } #endif /* WITH_WSREP */ result= mysql_alter_table(thd, select_lex->db, lex->name.str, @@@ -322,45 -121,14 +326,48 @@@ &alter_info, select_lex->order_list.elements, select_lex->order_list.first, - lex->ignore, lex->online); + lex->ignore); + + DBUG_RETURN(result); + DBUG_RETURN(result); #ifdef WITH_WSREP error: - WSREP_WARN("ALTER TABLE isolation failure"); - DBUG_RETURN(TRUE); + { + WSREP_WARN("ALTER TABLE isolation failure"); + DBUG_RETURN(TRUE); + } #endif /* WITH_WSREP */ } + +bool Sql_cmd_discard_import_tablespace::execute(THD *thd) +{ + /* first SELECT_LEX (have special meaning for many of non-SELECTcommands) */ + SELECT_LEX *select_lex= &thd->lex->select_lex; + /* first table of first SELECT_LEX */ + TABLE_LIST *table_list= (TABLE_LIST*) select_lex->table_list.first; + + if (check_access(thd, ALTER_ACL, table_list->db, + &table_list->grant.privilege, + &table_list->grant.m_internal, + 0, 0)) + return true; + + if (check_grant(thd, ALTER_ACL, table_list, false, UINT_MAX, false)) + return true; + + thd->enable_slow_log= opt_log_slow_admin_statements; + + /* + Check if we attempt to alter mysql.slow_log or + mysql.general_log table and return an error if + it is the case. + TODO: this design is obsolete and will be removed. + */ + if (check_if_log_table(table_list, TRUE, "ALTER")) + return true; + + return + mysql_discard_or_import_tablespace(thd, table_list, + m_tablespace_op == DISCARD_TABLESPACE); +} diff --cc sql/wsrep_mysqld.cc index 5b6575e7166,a7950754666..91dbe75408a --- a/sql/wsrep_mysqld.cc +++ b/sql/wsrep_mysqld.cc @@@ -894,49 -981,107 +894,107 @@@ static bool wsrep_prepare_key_for_isola wsrep_buf_t* key, size_t* key_len) { - if (*key_len < 2) return false; + if (*key_len < 2) return false; - switch (wsrep_protocol_version) - { - case 0: - *key_len= 0; - break; - case 1: - case 2: - case 3: + switch (wsrep_protocol_version) + { + case 0: + *key_len= 0; + break; + case 1: + case 2: + case 3: + { + *key_len= 0; + if (db) { - *key_len= 0; - if (db) - { - // sql_print_information("%s.%s", db, table); - if (db) - { - key[*key_len].ptr= db; - key[*key_len].len= strlen(db); - ++(*key_len); - if (table) - { - key[*key_len].ptr= table; - key[*key_len].len= strlen(table); - ++(*key_len); - } - } - } - break; + // sql_print_information("%s.%s", db, table); + key[*key_len].ptr= db; + key[*key_len].len= strlen(db); + ++(*key_len); + if (table) + { + key[*key_len].ptr= table; + key[*key_len].len= strlen(table); + ++(*key_len); + } } - default: + break; + } + default: + return false; + } + return true; + } + + + static bool wsrep_prepare_key_for_isolation(const char* db, + const char* table, + wsrep_key_arr_t* ka) + { + wsrep_key_t* tmp; + tmp= (wsrep_key_t*)my_realloc(ka->keys, + (ka->keys_len + 1) * sizeof(wsrep_key_t), + MYF(0)); + if (!tmp) + { + WSREP_ERROR("Can't allocate memory for key_array"); + return false; + } + ka->keys= tmp; + if (!(ka->keys[ka->keys_len].key_parts= (wsrep_buf_t*) + my_malloc(sizeof(wsrep_buf_t)*2, MYF(0)))) + { + WSREP_ERROR("Can't allocate memory for key_parts"); + return false; + } + ka->keys[ka->keys_len].key_parts_num= 2; + ++ka->keys_len; + if (!wsrep_prepare_key_for_isolation(db, table, + (wsrep_buf_t*)ka->keys[ka->keys_len - 1].key_parts, + &ka->keys[ka->keys_len - 1].key_parts_num)) + { + WSREP_ERROR("Preparing keys for isolation failed"); + return false; + } + + return true; + } + + + static bool wsrep_prepare_keys_for_alter_add_fk(char* child_table_db, + Alter_info* alter_info, + wsrep_key_arr_t* ka) + { + Key *key; + List_iterator<Key> key_iterator(alter_info->key_list); + while ((key= key_iterator++)) + { + if (key->type == Key::FOREIGN_KEY) + { + Foreign_key *fk_key= (Foreign_key *)key; - const char *db_name= fk_key->ref_table->db.str; - const char *table_name= fk_key->ref_table->table.str; ++ const char *db_name= fk_key->ref_db.str; ++ const char *table_name= fk_key->ref_table.str; + if (!db_name) + { + db_name= child_table_db; + } + if (!wsrep_prepare_key_for_isolation(db_name, table_name, ka)) + { return false; + } } - - return true; + } + return true; } - /* Prepare key list from db/table and table_list */ - bool wsrep_prepare_keys_for_isolation(THD* thd, - const char* db, - const char* table, - const TABLE_LIST* table_list, - wsrep_key_arr_t* ka) + + static bool wsrep_prepare_keys_for_isolation(THD* thd, + const char* db, + const char* table, + const TABLE_LIST* table_list, + Alter_info* alter_info, + wsrep_key_arr_t* ka) { ka->keys= 0; ka->keys_len= 0; @@@ -968,40 -1094,32 +1007,32 @@@ for (const TABLE_LIST* table= table_list; table; table= table->next_global) { - wsrep_key_t* tmp; - if (ka->keys) - tmp= (wsrep_key_t*)my_realloc(ka->keys, - (ka->keys_len + 1) * sizeof(wsrep_key_t), - MYF(0)); - else - tmp= (wsrep_key_t*)my_malloc((ka->keys_len + 1) * sizeof(wsrep_key_t), MYF(0)); - - if (!tmp) - { - WSREP_ERROR("Can't allocate memory for key_array"); + if (!wsrep_prepare_key_for_isolation(table->db, table->table_name, ka)) goto err; - } - ka->keys= tmp; - if (!(ka->keys[ka->keys_len].key_parts= (wsrep_buf_t*) - my_malloc(sizeof(wsrep_buf_t)*2, MYF(0)))) - { - WSREP_ERROR("Can't allocate memory for key_parts"); - goto err; - } - ka->keys[ka->keys_len].key_parts_num= 2; - ++ka->keys_len; - if (!wsrep_prepare_key_for_isolation(table->db, table->table_name, - (wsrep_buf_t*)ka->keys[ka->keys_len - 1].key_parts, - &ka->keys[ka->keys_len - 1].key_parts_num)) - { - WSREP_ERROR("Preparing keys for isolation failed (2)"); + } + - if (alter_info && (alter_info->flags & ALTER_FOREIGN_KEY)) ++ if (alter_info && (alter_info->flags & (Alter_info::ADD_FOREIGN_KEY))) + { + if (!wsrep_prepare_keys_for_alter_add_fk(table_list->db, alter_info, ka)) goto err; - } } - return 0; + + return false; + err: - wsrep_keys_free(ka); - return 1; + wsrep_keys_free(ka); + return true; + } + + + /* Prepare key list from db/table and table_list */ + bool wsrep_prepare_keys_for_isolation(THD* thd, + const char* db, + const char* table, + const TABLE_LIST* table_list, + wsrep_key_arr_t* ka) + { + return wsrep_prepare_keys_for_isolation(thd, db, table, table_list, NULL, ka); } @@@ -1179,171 -1282,9 +1210,172 @@@ create_view_query(THD *thd, uchar** buf return wsrep_to_buf_helper(thd, buff.ptr(), buff.length(), buf, buf_len); } +/* + Rewrite DROP TABLE for TOI. Temporary tables are eliminated from + the query as they are visible only to client connection. + + TODO: See comments for sql_base.cc:drop_temporary_table() and refine + the function to deal with transactional locked tables. + */ +static int wsrep_drop_table_query(THD* thd, uchar** buf, size_t* buf_len) +{ + + LEX* lex= thd->lex; + SELECT_LEX* select_lex= &lex->select_lex; + TABLE_LIST* first_table= select_lex->table_list.first; + String buff; + + DBUG_ASSERT(!lex->drop_temporary); + + bool found_temp_table= false; + for (TABLE_LIST* table= first_table; table; table= table->next_global) + { + if (find_temporary_table(thd, table->db, table->table_name)) + { + found_temp_table= true; + break; + } + } + + if (found_temp_table) + { + buff.append("DROP TABLE "); + if (lex->check_exists) + buff.append("IF EXISTS "); + + for (TABLE_LIST* table= first_table; table; table= table->next_global) + { + if (!find_temporary_table(thd, table->db, table->table_name)) + { + append_identifier(thd, &buff, table->db, strlen(table->db)); + buff.append("."); + append_identifier(thd, &buff, table->table_name, + strlen(table->table_name)); + buff.append(","); + } + } + + /* Chop the last comma */ + buff.chop(); + buff.append(" /* generated by wsrep */"); + + WSREP_DEBUG("Rewrote '%s' as '%s'", thd->query(), buff.ptr()); + + return wsrep_to_buf_helper(thd, buff.ptr(), buff.length(), buf, buf_len); + } + else + { + return wsrep_to_buf_helper(thd, thd->query(), thd->query_length(), + buf, buf_len); + } +} + +/* + Decide if statement should run in TOI. + + Look if table or table_list contain temporary tables. If the + statement affects only temporary tables, statement should not run + in TOI. If the table list contains mix of regular and temporary tables + (DROP TABLE, OPTIMIZE, ANALYZE), statement should be run in TOI but + should be rewritten at later time for replication to contain only + non-temporary tables. + */ +static bool wsrep_can_run_in_toi(THD *thd, const char *db, const char *table, + const TABLE_LIST *table_list) +{ + DBUG_ASSERT(!table || db); + DBUG_ASSERT(table_list || db); + + LEX* lex= thd->lex; + SELECT_LEX* select_lex= &lex->select_lex; + TABLE_LIST* first_table= select_lex->table_list.first; + + switch (lex->sql_command) + { + case SQLCOM_CREATE_TABLE: + DBUG_ASSERT(!table_list); + if (thd->lex->create_info.options & HA_LEX_CREATE_TMP_TABLE) + { + return false; + } + return true; + + case SQLCOM_CREATE_VIEW: + + DBUG_ASSERT(!table_list); + DBUG_ASSERT(first_table); /* First table is view name */ + /* + If any of the remaining tables refer to temporary table error + is returned to client, so TOI can be skipped + */ + for (TABLE_LIST* it= first_table->next_global; it; it= it->next_global) + { + if (find_temporary_table(thd, it)) + { + return false; + } + } + return true; + + case SQLCOM_CREATE_TRIGGER: + + DBUG_ASSERT(!table_list); + DBUG_ASSERT(first_table); + + if (find_temporary_table(thd, first_table)) + { + return false; + } + return true; + + default: + if (table && !find_temporary_table(thd, db, table)) + { + return true; + } + + if (table_list) + { + for (TABLE_LIST* table= first_table; table; table= table->next_global) + { + if (!find_temporary_table(thd, table->db, table->table_name)) + { + return true; + } + } + } + return !(table || table_list); + } +} + +static const char* wsrep_get_query_or_msg(const THD* thd) +{ + switch(thd->lex->sql_command) + { + case SQLCOM_CREATE_USER: + return "CREATE USER"; + case SQLCOM_GRANT: + return "GRANT"; + case SQLCOM_REVOKE: + return "REVOKE"; + case SQLCOM_SET_OPTION: + if (thd->lex->definer) + return "SET PASSWORD"; + /* fallthrough */ + default: + return thd->query(); + } +} + +/* + returns: + 0: statement was replicated as TOI + 1: TOI replication was skipped + -1: TOI replication failed + */ static int wsrep_TOI_begin(THD *thd, char *db_, char *table_, - const TABLE_LIST* table_list) + const TABLE_LIST* table_list, + Alter_info* alter_info) { wsrep_status_t ret(WSREP_WARNING); uchar* buf(0); @@@ -1393,12 -1320,13 +1425,13 @@@ wsrep_key_arr_t key_arr= {0, 0}; struct wsrep_buf buff = { buf, buf_len }; if (!buf_err && - !wsrep_prepare_keys_for_isolation(thd, db_, table_, table_list, &key_arr) && + !wsrep_prepare_keys_for_isolation(thd, db_, table_, + table_list, alter_info, &key_arr) && key_arr.keys_len > 0 && WSREP_OK == (ret = wsrep->to_execute_start(wsrep, thd->thread_id, - key_arr.keys, key_arr.keys_len, - &buff, 1, - &thd->wsrep_trx_meta))) + key_arr.keys, key_arr.keys_len, + &buff, 1, + &thd->wsrep_trx_meta))) { thd->wsrep_exec_mode= TOTAL_ORDER; wsrep_to_isolation++; @@@ -1589,28 -1494,17 +1622,28 @@@ int wsrep_to_isolation_begin(THD *thd, if (thd->variables.wsrep_on && thd->wsrep_exec_mode==LOCAL_STATE) { - switch (wsrep_OSU_method_options) { + switch (thd->variables.wsrep_OSU_method) { case WSREP_OSU_TOI: - ret = wsrep_TOI_begin(thd, db_, table_, table_list); + ret= wsrep_TOI_begin(thd, db_, table_, table_list, alter_info); break; case WSREP_OSU_RSU: - ret = wsrep_RSU_begin(thd, db_, table_); + ret= wsrep_RSU_begin(thd, db_, table_); break; + default: + WSREP_ERROR("Unsupported OSU method: %lu", + thd->variables.wsrep_OSU_method); + ret= -1; + break; } - if (!ret) - { - thd->wsrep_exec_mode= TOTAL_ORDER; + switch (ret) { + case 0: thd->wsrep_exec_mode= TOTAL_ORDER; break; + case 1: + /* TOI replication skipped, treat as success */ + ret = 0; + break; + case -1: + /* TOI replication failed, treat as error */ + break; } } return ret;

1 0

[Commits] c5a8583b314: Merge tag 'mariadb-10.0.36' into 10.0-galera
by jan 02 Aug '18

02 Aug '18

revision-id: c5a8583b3146613429d88c1c044de88a4c4c94b0 (mariadb-galera-10.0.35-14-gc5a8583b314) parent(s): c863159c320008676aff978a7cdde5732678f975 e023f9a4d5a620b54d7f7132567150d80b630692 author: Jan Lindström committer: Jan Lindström timestamp: 2018-08-02 11:44:02 +0300 message: Merge tag 'mariadb-10.0.36' into 10.0-galera .gitignore | 6 + CMakeLists.txt | 6 + client/mysqldump.c | 15 +- client/mysqlslap.c | 4 +- client/mysqltest.cc | 1 + cmake/install_macros.cmake | 8 + cmake/mysql_add_executable.cmake | 3 + cmake/os/Windows.cmake | 2 +- include/m_ctype.h | 28 +- include/sql_common.h | 2 +- .../rpl_tests/rpl_implicit_commit_binlog.test | 708 +++------------------ mysql-test/include/ctype_mdev13118.inc | 15 + mysql-test/lib/My/SafeProcess/safe_process.cc | 14 +- mysql-test/mysql-test-run.pl | 83 +-- mysql-test/r/alter_table.result | 84 +++ mysql-test/r/assign_key_cache.result | 13 + ...e-5405.result => assign_key_cache_debug.result} | 0 mysql-test/r/auto_increment.result | 29 + mysql-test/r/create_or_replace.result | 20 + mysql-test/r/ctype_binary.result | 23 + mysql-test/r/ctype_eucjpms.result | 23 + mysql-test/r/ctype_euckr.result | 29 + mysql-test/r/ctype_gbk.result | 23 + mysql-test/r/ctype_latin1.result | 23 + mysql-test/r/ctype_ucs.result | 23 + mysql-test/r/ctype_ujis.result | 23 + mysql-test/r/ctype_utf16.result | 23 + mysql-test/r/ctype_utf16le.result | 29 + mysql-test/r/ctype_utf32.result | 23 + mysql-test/r/ctype_utf8.result | 23 + mysql-test/r/ctype_utf8mb4.result | 23 + mysql-test/r/derived.result | 33 + mysql-test/r/grant.result | 9 +- mysql-test/r/grant2.result | 3 +- mysql-test/r/grant_not_windows.result | 7 + mysql-test/r/having.result | 17 + mysql-test/r/implicit_commit.result | 4 +- mysql-test/r/information_schema.result | 2 +- mysql-test/r/insert_select.result | 9 + mysql-test/r/join.result | 40 ++ mysql-test/r/join_cache.result | 33 + mysql-test/r/join_outer.result | 50 ++ mysql-test/r/join_outer_jcl6.result | 50 ++ mysql-test/r/limit.result | 16 + mysql-test/r/lock.result | 2 +- mysql-test/r/myisam.result | 8 + mysql-test/r/mysqldump.result | 15 + mysql-test/r/mysqlslap.result | 3 + mysql-test/r/rename.result | 70 ++ mysql-test/r/selectivity.result | 70 +- mysql-test/r/selectivity_innodb.result | 70 +- mysql-test/r/stat_tables.result | 60 ++ mysql-test/r/stat_tables_innodb.result | 60 ++ mysql-test/r/statistics.result | 20 + mysql-test/r/statistics_close.result | 6 + mysql-test/r/subselect.result | 16 + mysql-test/r/subselect4.result | 6 +- mysql-test/r/subselect_mat.result | 93 +++ mysql-test/r/subselect_mat_cost_bugs.result | 4 +- mysql-test/r/subselect_no_exists_to_in.result | 16 + mysql-test/r/subselect_no_mat.result | 16 + mysql-test/r/subselect_no_opts.result | 16 + mysql-test/r/subselect_no_scache.result | 16 + mysql-test/r/subselect_no_semijoin.result | 16 + mysql-test/r/subselect_sj2_mat.result | 198 ++++++ mysql-test/r/subselect_sj_mat.result | 93 +++ mysql-test/r/subselect_sj_nonmerged.result | 6 +- mysql-test/r/union.result | 16 + mysql-test/std_data/frm/t1.frm | Bin 0 -> 8584 bytes .../suite/binlog/include/check_binlog_size.inc | 31 + .../suite/binlog/r/binlog_tmp_table_row.result | 7 + .../suite/binlog/t/binlog_tmp_table_row.test | 30 + mysql-test/suite/federated/have_federatedx.inc | 2 +- .../suite/funcs_1/r/is_engines_archive.result | 2 +- mysql-test/suite/funcs_1/r/is_engines_csv.result | 2 +- .../suite/funcs_1/r/is_engines_federated.result | 2 +- .../suite/funcs_1/r/is_engines_myisam.result | 2 +- mysql-test/suite/handler/handler.inc | 2 + mysql-test/suite/handler/interface.result | 2 +- mysql-test/suite/handler/interface.test | 2 +- mysql-test/suite/handler/ps.result | 9 + mysql-test/suite/handler/ps.test | 11 + mysql-test/suite/heap/heap_auto_increment.result | 29 + mysql-test/suite/heap/heap_auto_increment.test | 25 + .../suite/innodb/r/alter_partitioned_xa.result | 15 + mysql-test/suite/innodb/r/innodb-alter.result | 29 + mysql-test/suite/innodb/r/innodb-wl5522.result | 263 +++++++- mysql-test/suite/innodb/r/innodb_bug54044.result | 6 - mysql-test/suite/innodb/r/rename_table.result | 5 + .../suite/innodb/t/alter_partitioned_xa.test | 31 + mysql-test/suite/innodb/t/innodb-alter.test | 21 + mysql-test/suite/innodb/t/innodb-mdev7046.test | 7 +- mysql-test/suite/innodb/t/innodb-wl5522.test | 258 +++++++- mysql-test/suite/innodb/t/innodb_bug54044.test | 10 - mysql-test/suite/innodb/t/rename_table.test | 11 + .../r/{innodb-fts-basic.result => basic.result} | 34 + mysql-test/suite/innodb_fts/r/sync_ddl.result | 117 ++++ .../t/{innodb-fts-basic.test => basic.test} | 39 +- mysql-test/suite/innodb_fts/t/sync_ddl.test | 177 ++++++ mysql-test/suite/maria/alter.result | 16 + mysql-test/suite/maria/alter.test | 17 + mysql-test/suite/maria/lock.result | 10 + mysql-test/suite/maria/lock.test | 12 + mysql-test/suite/maria/maria.result | 37 ++ mysql-test/suite/maria/maria.test | 37 ++ .../parts/r/alter_data_directory_innodb.result | 65 ++ mysql-test/suite/parts/r/truncate_locked.result | 7 + .../suite/parts/t/alter_data_directory_innodb.test | 46 ++ mysql-test/suite/parts/t/truncate_locked.test | 10 + mysql-test/suite/plugins/r/processlist.result | 6 + mysql-test/suite/plugins/r/server_audit.result | 12 + mysql-test/suite/plugins/t/processlist.test | 20 + mysql-test/suite/plugins/t/server_audit.test | 7 + mysql-test/suite/rpl/r/rename.result | 34 + .../rpl/r/rpl_mixed_implicit_commit_binlog.result | 528 ++++----------- .../rpl/r/rpl_row_implicit_commit_binlog.result | 523 ++++----------- .../rpl/r/rpl_stm_implicit_commit_binlog.result | 515 ++++----------- mysql-test/suite/rpl/t/rename.test | 33 + mysql-test/suite/unit/suite.pm | 7 +- mysql-test/suite/vcol/r/vcol_misc.result | 10 + mysql-test/suite/vcol/t/vcol_misc.test | 18 + mysql-test/t/alter_table.test | 48 ++ mysql-test/t/assign_key_cache.test | 13 + ...cache-5405.test => assign_key_cache_debug.test} | 0 mysql-test/t/auto_increment.test | 25 + mysql-test/t/bootstrap.test | 9 + mysql-test/t/create_or_replace.test | 25 + mysql-test/t/ctype_binary.test | 3 + mysql-test/t/ctype_eucjpms.test | 2 + mysql-test/t/ctype_euckr.test | 11 + mysql-test/t/ctype_gbk.test | 3 + mysql-test/t/ctype_latin1.test | 3 + mysql-test/t/ctype_ucs.test | 4 + mysql-test/t/ctype_ujis.test | 4 + mysql-test/t/ctype_utf16.test | 5 + mysql-test/t/ctype_utf16le.test | 13 + mysql-test/t/ctype_utf32.test | 8 + mysql-test/t/ctype_utf8.test | 7 + mysql-test/t/ctype_utf8mb4.test | 8 + mysql-test/t/derived.test | 21 + mysql-test/t/grant.test | 11 +- mysql-test/t/grant2.test | 3 +- mysql-test/t/grant_not_windows.test | 13 + mysql-test/t/having.test | 17 + mysql-test/t/insert_select.test | 10 + mysql-test/t/join.test | 53 ++ mysql-test/t/join_cache.test | 32 +- mysql-test/t/join_outer.test | 48 ++ mysql-test/t/limit.test | 14 + mysql-test/t/lock.test | 2 +- mysql-test/t/myisam.test | 13 + mysql-test/t/mysql.test | 16 +- mysql-test/t/mysql_cp932.test | 42 +- mysql-test/t/mysqldump.test | 22 + mysql-test/t/mysqlslap.test | 8 + mysql-test/t/rename.test | 53 ++ mysql-test/t/selectivity.test | 18 + mysql-test/t/stat_tables.test | 50 ++ mysql-test/t/statistics.test | 24 + mysql-test/t/statistics_close.test | 18 + mysql-test/t/subselect.test | 15 + mysql-test/t/subselect4.test | 2 +- mysql-test/t/subselect_sj2_mat.test | 178 ++++++ mysql-test/t/subselect_sj_mat.test | 79 +++ mysql-test/t/union.test | 15 + mysql-test/unstable-tests | 161 +++-- mysql-test/valgrind.supp | 13 + mysys/my_rename.c | 5 +- plugin/server_audit/server_audit.c | 29 +- scripts/CMakeLists.txt | 2 + scripts/mysql_install_db.pl.in | 2 +- scripts/mysql_install_db.sh | 7 +- scripts/mysql_system_tables_fix.sql | 50 +- scripts/mysqld_multi.sh | 2 +- sql-common/client.c | 30 +- sql/ha_partition.cc | 3 +- sql/handler.h | 10 +- sql/item.cc | 11 +- sql/item.h | 5 + sql/item_strfunc.cc | 112 ++-- sql/log.cc | 10 +- sql/log_event.cc | 4 - sql/mysql_install_db.cc | 2 +- sql/mysql_upgrade_service.cc | 20 +- sql/mysqld.cc | 15 +- sql/opt_subselect.cc | 72 ++- sql/share/CMakeLists.txt | 12 +- sql/sql_acl.cc | 13 +- sql/sql_admin.cc | 19 +- sql/sql_base.cc | 61 +- sql/sql_base.h | 6 +- sql/sql_class.cc | 97 --- sql/sql_class.h | 2 - sql/sql_delete.cc | 2 +- sql/sql_handler.cc | 5 +- sql/sql_insert.cc | 3 +- sql/sql_parse.cc | 95 ++- sql/sql_parse.h | 1 + sql/sql_rename.cc | 2 +- sql/sql_select.cc | 90 +-- sql/sql_show.cc | 103 ++- sql/sql_statistics.cc | 48 +- sql/sql_statistics.h | 1 + sql/sql_table.cc | 71 ++- sql/sql_truncate.cc | 3 +- sql/table.cc | 32 +- sql/table_cache.cc | 2 + sql/tztime.cc | 2 +- storage/archive/ha_archive.cc | 2 +- storage/connect/filamvct.cpp | 5 - storage/connect/tabjson.cpp | 14 +- storage/csv/ha_tina.cc | 2 +- storage/federated/ha_federated.cc | 2 +- storage/federatedx/ha_federatedx.cc | 2 +- storage/heap/hp_hash.c | 2 +- storage/innobase/buf/buf0buddy.cc | 2 +- storage/innobase/buf/buf0dump.cc | 2 +- storage/innobase/dict/dict0dict.cc | 3 +- storage/innobase/fil/fil0fil.cc | 110 ---- storage/innobase/fts/fts0fts.cc | 76 +-- storage/innobase/fts/fts0opt.cc | 175 +---- storage/innobase/handler/ha_innodb.cc | 37 +- storage/innobase/handler/handler0alter.cc | 124 ++-- storage/innobase/handler/i_s.cc | 47 +- storage/innobase/include/fil0fil.h | 4 - storage/innobase/include/fts0fts.h | 8 +- storage/innobase/include/fts0priv.h | 18 +- storage/innobase/include/univ.i | 2 +- storage/innobase/os/os0file.cc | 31 +- storage/innobase/row/row0import.cc | 95 ++- storage/innobase/row/row0log.cc | 126 +--- storage/innobase/row/row0merge.cc | 3 +- storage/innobase/row/row0mysql.cc | 12 + storage/innobase/trx/trx0trx.cc | 4 +- storage/innobase/trx/trx0undo.cc | 3 +- storage/maria/ha_maria.cc | 141 ++-- storage/maria/ha_maria.h | 1 + storage/maria/ma_blockrec.c | 3 +- storage/maria/ma_close.c | 2 + storage/maria/ma_commit.c | 17 +- storage/maria/ma_extra.c | 4 +- storage/maria/ma_key.c | 2 +- storage/maria/ma_loghandler.c | 219 ++++++- storage/maria/ma_open.c | 4 +- storage/maria/ma_recovery.c | 11 + storage/maria/ma_state.c | 48 +- storage/maria/ma_state.h | 2 +- storage/maria/ma_trnman.h | 65 ++ storage/maria/maria_def.h | 14 +- storage/maria/trnman.c | 1 + storage/maria/trnman.h | 3 +- storage/myisam/ha_myisam.cc | 8 +- storage/myisam/mi_check.c | 6 +- storage/myisam/mi_key.c | 2 +- storage/myisam/mi_locking.c | 6 +- storage/tokudb/CMakeLists.txt | 6 + storage/tokudb/PerconaFT/ft/ft-ops.cc | 8 +- .../tokudb/PerconaFT/ft/serialize/ft-serialize.cc | 16 +- .../PerconaFT/ft/serialize/ft_node-serialize.cc | 116 ++-- storage/tokudb/PerconaFT/portability/memory.h | 2 +- .../tokudb/PerconaFT/portability/toku_debug_sync.h | 3 - .../tokudb/PerconaFT/portability/toku_pthread.h | 12 +- storage/tokudb/PerconaFT/src/CMakeLists.txt | 2 +- .../third_party/xz-4.999.9beta/autogen.sh | 0 .../third_party/xz-4.999.9beta/build-aux/compile | 0 .../xz-4.999.9beta/build-aux/config.guess | 0 .../xz-4.999.9beta/build-aux/config.rpath | 0 .../xz-4.999.9beta/build-aux/config.sub | 0 .../third_party/xz-4.999.9beta/build-aux/depcomp | 0 .../xz-4.999.9beta/build-aux/install-sh | 0 .../third_party/xz-4.999.9beta/build-aux/ltmain.sh | 0 .../third_party/xz-4.999.9beta/build-aux/missing | 0 .../xz-4.999.9beta/extra/7z2lzma/7z2lzma.bash | 0 .../xz-4.999.9beta/tests/test_compress.sh | 0 .../third_party/xz-4.999.9beta/tests/test_files.sh | 0 storage/tokudb/PerconaFT/util/dmt.cc | 4 +- storage/tokudb/PerconaFT/util/omt.h | 8 +- storage/tokudb/tokudb_thread.h | 49 -- storage/xtradb/buf/buf0buddy.cc | 2 +- storage/xtradb/buf/buf0dump.cc | 2 +- storage/xtradb/dict/dict0dict.cc | 3 +- storage/xtradb/fil/fil0fil.cc | 110 ---- storage/xtradb/fts/fts0fts.cc | 76 +-- storage/xtradb/fts/fts0opt.cc | 175 +---- storage/xtradb/handler/ha_innodb.cc | 40 +- storage/xtradb/handler/handler0alter.cc | 175 ++--- storage/xtradb/handler/i_s.cc | 47 +- storage/xtradb/include/fil0fil.h | 4 - storage/xtradb/include/fts0fts.h | 8 +- storage/xtradb/include/fts0priv.h | 18 +- storage/xtradb/os/os0file.cc | 70 +- storage/xtradb/row/row0import.cc | 84 ++- storage/xtradb/row/row0log.cc | 138 +--- storage/xtradb/row/row0merge.cc | 3 +- storage/xtradb/row/row0mysql.cc | 20 +- storage/xtradb/trx/trx0trx.cc | 4 +- storage/xtradb/trx/trx0undo.cc | 3 +- strings/ctype-bin.c | 8 +- strings/ctype-euc_kr.c | 4 +- strings/ctype-mb.c | 100 +-- strings/ctype-simple.c | 22 +- strings/ctype-ucs2.c | 78 +-- strings/ctype-ujis.c | 8 +- strings/ctype-utf8.c | 20 +- support-files/CMakeLists.txt | 4 +- unittest/sql/CMakeLists.txt | 1 - 306 files changed, 6741 insertions(+), 4034 deletions(-) diff --cc sql/sql_class.cc index 59746d466ea,3b293935048..385ced114c8 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@@ -721,277 -717,6 +721,180 @@@ extern "C } - /** - Dumps a text description of a thread, its security context - (user, host) and the current query. - - @param thd thread context - @param buffer pointer to preferred result buffer - @param length length of buffer - @param max_query_len how many chars of query to copy (0 for all) - - @return Pointer to string - */ - - extern "C" - char *thd_get_error_context_description(THD *thd, char *buffer, - unsigned int length, - unsigned int max_query_len) - { - String str(buffer, length, &my_charset_latin1); - const Security_context *sctx= &thd->main_security_ctx; - char header[256]; - int len; - - mysql_mutex_lock(&LOCK_thread_count); - - /* - The pointers thd->query and thd->proc_info might change since they are - being modified concurrently. This is acceptable for proc_info since its - values doesn't have to very accurate and the memory it points to is static, - but we need to attempt a snapshot on the pointer values to avoid using NULL - values. The pointer to thd->query however, doesn't point to static memory - and has to be protected by thd->LOCK_thd_data or risk pointing to - uninitialized memory. - */ - const char *proc_info= thd->proc_info; - - len= my_snprintf(header, sizeof(header), - "MySQL thread id %lu, OS thread handle 0x%lx, query id %lu", - thd->thread_id, (ulong) thd->real_id, (ulong) thd->query_id); - str.length(0); - str.append(header, len); - - if (sctx->host) - { - str.append(' '); - str.append(sctx->host); - } - - if (sctx->ip) - { - str.append(' '); - str.append(sctx->ip); - } - - if (sctx->user) - { - str.append(' '); - str.append(sctx->user); - } - - if (proc_info) - { - str.append(' '); - str.append(proc_info); - } - - /* Don't wait if LOCK_thd_data is used as this could cause a deadlock */ - if (!mysql_mutex_trylock(&thd->LOCK_thd_data)) - { - if (thd->query()) - { - if (max_query_len < 1) - len= thd->query_length(); - else - len= MY_MIN(thd->query_length(), max_query_len); - str.append('\n'); - str.append(thd->query(), len); - } - mysql_mutex_unlock(&thd->LOCK_thd_data); - } - mysql_mutex_unlock(&LOCK_thread_count); - - if (str.c_ptr_safe() == buffer) - return buffer; - - /* - We have to copy the new string to the destination buffer because the string - was reallocated to a larger buffer to be able to fit. - */ - DBUG_ASSERT(buffer != NULL); - length= MY_MIN(str.length(), length-1); - memcpy(buffer, str.c_ptr_quick(), length); - /* Make sure that the new string is null terminated */ - buffer[length]= '\0'; - return buffer; - } - +#ifdef WITH_WSREP +extern int wsrep_on(void *thd) +{ + return (int)(WSREP(((THD*)thd))); +} +extern "C" bool wsrep_thd_is_wsrep_on(THD *thd) +{ + return thd->variables.wsrep_on; +} + +extern "C" bool wsrep_consistency_check(void *thd) +{ + return ((THD*)thd)->wsrep_consistency_check == CONSISTENCY_CHECK_RUNNING; +} + +extern "C" void wsrep_thd_set_exec_mode(THD *thd, enum wsrep_exec_mode mode) +{ + thd->wsrep_exec_mode= mode; +} +extern "C" void wsrep_thd_set_query_state( + THD *thd, enum wsrep_query_state state) +{ + thd->wsrep_query_state= state; +} +extern "C" void wsrep_thd_set_conflict_state( + THD *thd, enum wsrep_conflict_state state) +{ + if (WSREP(thd)) thd->wsrep_conflict_state= state; +} + + +extern "C" enum wsrep_exec_mode wsrep_thd_exec_mode(THD *thd) +{ + return thd->wsrep_exec_mode; +} + +extern "C" const char *wsrep_thd_exec_mode_str(THD *thd) +{ + return + (!thd) ? "void" : + (thd->wsrep_exec_mode == LOCAL_STATE) ? "local" : + (thd->wsrep_exec_mode == REPL_RECV) ? "applier" : + (thd->wsrep_exec_mode == TOTAL_ORDER) ? "total order" : + (thd->wsrep_exec_mode == LOCAL_COMMIT) ? "local commit" : "void"; +} + +extern "C" enum wsrep_query_state wsrep_thd_query_state(THD *thd) +{ + return thd->wsrep_query_state; +} + +extern "C" const char *wsrep_thd_query_state_str(THD *thd) +{ + return + (!thd) ? "void" : + (thd->wsrep_query_state == QUERY_IDLE) ? "idle" : + (thd->wsrep_query_state == QUERY_EXEC) ? "executing" : + (thd->wsrep_query_state == QUERY_COMMITTING) ? "committing" : + (thd->wsrep_query_state == QUERY_EXITING) ? "exiting" : + (thd->wsrep_query_state == QUERY_ROLLINGBACK) ? "rolling back" : "void"; +} + +extern "C" enum wsrep_conflict_state wsrep_thd_conflict_state(THD *thd) +{ + return thd->wsrep_conflict_state; +} +extern "C" const char *wsrep_thd_conflict_state_str(THD *thd) +{ + return + (!thd) ? "void" : + (thd->wsrep_conflict_state == NO_CONFLICT) ? "no conflict" : + (thd->wsrep_conflict_state == MUST_ABORT) ? "must abort" : + (thd->wsrep_conflict_state == ABORTING) ? "aborting" : + (thd->wsrep_conflict_state == MUST_REPLAY) ? "must replay" : + (thd->wsrep_conflict_state == REPLAYING) ? "replaying" : + (thd->wsrep_conflict_state == RETRY_AUTOCOMMIT) ? "retrying" : + (thd->wsrep_conflict_state == CERT_FAILURE) ? "cert failure" : "void"; +} + +extern "C" wsrep_ws_handle_t* wsrep_thd_ws_handle(THD *thd) +{ + return &thd->wsrep_ws_handle; +} + +extern "C" void wsrep_thd_LOCK(THD *thd) +{ + mysql_mutex_lock(&thd->LOCK_wsrep_thd); +} +extern "C" void wsrep_thd_UNLOCK(THD *thd) +{ + mysql_mutex_unlock(&thd->LOCK_wsrep_thd); +} +extern "C" time_t wsrep_thd_query_start(THD *thd) +{ + return thd->query_start(); +} +extern "C" uint32 wsrep_thd_wsrep_rand(THD *thd) +{ + return thd->wsrep_rand; +} +extern "C" my_thread_id wsrep_thd_thread_id(THD *thd) +{ + return thd->thread_id; +} +extern "C" wsrep_seqno_t wsrep_thd_trx_seqno(THD *thd) +{ + return (thd) ? thd->wsrep_trx_meta.gtid.seqno : WSREP_SEQNO_UNDEFINED; +} +extern "C" query_id_t wsrep_thd_query_id(THD *thd) +{ + return thd->query_id; +} +extern "C" char *wsrep_thd_query(THD *thd) +{ + return (thd) ? thd->query() : NULL; +} +extern "C" query_id_t wsrep_thd_wsrep_last_query_id(THD *thd) +{ + return thd->wsrep_last_query_id; +} +extern "C" void wsrep_thd_set_wsrep_last_query_id(THD *thd, query_id_t id) +{ + thd->wsrep_last_query_id= id; +} +extern "C" void wsrep_thd_awake(THD *thd, my_bool signal) +{ + if (signal) + { + mysql_mutex_lock(&thd->LOCK_thd_data); + thd->awake(KILL_QUERY); + mysql_mutex_unlock(&thd->LOCK_thd_data); + } + else + { + mysql_mutex_lock(&LOCK_wsrep_replaying); + mysql_cond_broadcast(&COND_wsrep_replaying); + mysql_mutex_unlock(&LOCK_wsrep_replaying); + } +} +extern "C" int wsrep_thd_retry_counter(THD *thd) +{ + return(thd->wsrep_retry_counter); +} +extern "C" bool wsrep_thd_skip_append_keys(THD *thd) +{ + return thd->wsrep_skip_append_keys; +} + +extern int +wsrep_trx_order_before(void *thd1, void *thd2) +{ + if (wsrep_thd_trx_seqno((THD*)thd1) < wsrep_thd_trx_seqno((THD*)thd2)) { + WSREP_DEBUG("BF conflict, order: %lld %lld\n", + (long long)wsrep_thd_trx_seqno((THD*)thd1), + (long long)wsrep_thd_trx_seqno((THD*)thd2)); + return 1; + } + WSREP_DEBUG("waiting for BF, trx order: %lld %lld\n", + (long long)wsrep_thd_trx_seqno((THD*)thd1), + (long long)wsrep_thd_trx_seqno((THD*)thd2)); + return 0; +} +extern "C" int +wsrep_trx_is_aborting(void *thd_ptr) +{ + if (thd_ptr) { + if ((((THD *)thd_ptr)->wsrep_conflict_state == MUST_ABORT) || + (((THD *)thd_ptr)->wsrep_conflict_state == ABORTING)) { + return 1; + } + } + return 0; +} +#endif - #if MARIA_PLUGIN_INTERFACE_VERSION < 0x0200 /** TODO: This function is for API compatibility, remove it eventually. diff --cc sql/sql_parse.cc index 2b11e492a94,a9849c7248d..7e3590bcc83 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@@ -5839,8 -5337,62 +5838,61 @@@ static bool execute_show_status(THD *th } - static bool check_rename_table(THD *thd, TABLE_LIST *first_table, - TABLE_LIST *all_tables) + /* + Find out if a table is a temporary table + + A table is a temporary table if it's a temporary table or + there has been before a temporary table that has been renamed + to the current name. + + Some examples: + A->B B is a temporary table if and only if A is a temp. + A->B, B->C Second B is temp if A is temp + A->B, A->C Second A can't be temp as if A was temp then B is temp + and Second A can only be a normal table. C is also not temp + */ + + static TABLE *find_temporary_table_for_rename(THD *thd, + TABLE_LIST *first_table, + TABLE_LIST *cur_table) + { + TABLE_LIST *table; + TABLE *res= 0; + bool found= 0; + DBUG_ENTER("find_temporary_table_for_rename"); + + /* Find last instance when cur_table is in TO part */ + for (table= first_table; + table != cur_table; + table= table->next_local->next_local) + { + TABLE_LIST *next= table->next_local; + + if (!strcmp(table->get_db_name(), cur_table->get_db_name()) && + !strcmp(table->get_table_name(), cur_table->get_table_name())) + { + /* Table was moved away, can't be same as 'table' */ + found= 1; + res= 0; // Table can't be a temporary table + } + if (!strcmp(next->get_db_name(), cur_table->get_db_name()) && + !strcmp(next->get_table_name(), cur_table->get_table_name())) + { + /* + Table has matching name with new name of this table. cur_table should + have same temporary type as this table. + */ + found= 1; + res= table->table; + } + } + if (!found) + res= find_temporary_table(thd, table); + DBUG_RETURN(res); + } + - + static bool execute_rename_table(THD *thd, TABLE_LIST *first_table, + TABLE_LIST *all_tables) { DBUG_ASSERT(first_table == all_tables && first_table != 0); TABLE_LIST *table; @@@ -5854,7 -5406,12 +5906,12 @@@ &table->next_local->grant.privilege, &table->next_local->grant.m_internal, 0, 0)) - return 1; + return true; + + /* check if these are refering to temporary tables */ + table->table= find_temporary_table_for_rename(thd, first_table, table); + table->next_local->table= table->table; + TABLE_LIST old_list, new_list; /* we do not need initialize old_list and new_list because we will @@@ -5867,10 -5425,10 +5925,17 @@@ INSERT_ACL | CREATE_ACL) && check_grant(thd, INSERT_ACL | CREATE_ACL, &new_list, FALSE, 1, FALSE))) - return 1; + return true; } - return false; ++ WSREP_TO_ISOLATION_BEGIN(0, 0, first_table) ++ + return mysql_rename_tables(thd, first_table, 0); ++ ++#ifdef WITH_WSREP ++error: ++ return true; ++#endif }

1 0

[Commits] 4f74dead630: MDEV-13170: Database service (MySQL) stops after update with trigger
by Varun 01 Aug '18

01 Aug '18

revision-id: 4f74dead6308f2928227f68b0f973a78a5aeaaa1 (mariadb-10.2.16-52-g4f74dead630) parent(s): fd378fc613851a12be346329d32e1666f10610d7 author: Varun Gupta committer: Varun Gupta timestamp: 2018-08-02 02:56:32 +0530 message: MDEV-13170: Database service (MySQL) stops after update with trigger The issue here is that we are not able to resolve the items in the order by clause of the window function. For name resolution the rule is that we look first in select list and then in the FROM clause. For prepared statements we do some caching as to we remember which table the item was resolved during the prepare state and try to reuse it. --- mysql-test/r/win.result | 13 +++++++++++++ mysql-test/t/win.test | 12 ++++++++++++ sql/sql_select.cc | 1 + 3 files changed, 26 insertions(+) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index b519b2bb223..7ce9cf00e70 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3314,3 +3314,16 @@ COUNT(DISTINCT t2.a2) rank() OVER (ORDER BY t2.b1) 1 2 1 3 DROP TABLE t1,t2; +# +# MDEV-13170: Database service (MySQL) stops after update with trigger +# +CREATE TABLE t1 (i INT, a char); +INSERT INTO t1 VALUES (1, 'a'),(2, 'b'); +create view v1 as select * from t1; +PREPARE stmt FROM "SELECT i, row_number() over (partition by i order by i) FROM v1"; +execute stmt; +i row_number() over (partition by i order by i) +1 1 +2 1 +drop table t1; +drop view v1; diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index b354a55d0d6..8ba1b8a0595 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2082,3 +2082,15 @@ SELECT COUNT(DISTINCT t2.a2), rank() OVER (ORDER BY t2.b1) FROM t2 ,t1 GROUP BY t2.b1 ORDER BY t1.a1; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-13170: Database service (MySQL) stops after update with trigger +--echo # + +CREATE TABLE t1 (i INT, a char); +INSERT INTO t1 VALUES (1, 'a'),(2, 'b'); +create view v1 as select * from t1; +PREPARE stmt FROM "SELECT i, row_number() over (partition by i order by i) FROM v1"; +execute stmt; +drop table t1; +drop view v1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 47ca733468b..72942923fbc 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -22406,6 +22406,7 @@ find_order_in_list(THD *thd, Ref_ptr_array ref_pointer_array, 'shadowed' a table field with the same name, the table field will be chosen over the derived field. */ + ((Item_ident*)order_item)->cached_table= NULL; order->item= &ref_pointer_array[counter]; order->in_field_list=1; return FALSE;

1 0

[Commits] 3a550aaf3b4: fix tanle name logging in WRESP_DEBUG
by Oleksandr Byelkin 01 Aug '18

01 Aug '18

revision-id: 3a550aaf3b42a9676b059548f0ed0ff8a817ce83 (mariadb-10.2.16-56-g3a550aaf3b4) parent(s): c6f2c490605791be4bda08d6efc69d88aedd2c98 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-08-01 15:54:15 +0200 message: fix tanle name logging in WRESP_DEBUG --- storage/innobase/dict/dict0stats_bg.cc | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/storage/innobase/dict/dict0stats_bg.cc b/storage/innobase/dict/dict0stats_bg.cc index 920252a07b2..b203c60bd3c 100644 --- a/storage/innobase/dict/dict0stats_bg.cc +++ b/storage/innobase/dict/dict0stats_bg.cc @@ -179,7 +179,7 @@ void dict_stats_update_if_needed_func(dict_table_t* table) if (thd && wsrep_on(thd) && wsrep_thd_is_BF(thd, 0)) { WSREP_DEBUG("Avoiding background statistics" " calculation for table %s", - table->name); + table->name.m_name); return; } #endif /* WITH_WSREP */

1 0

[Commits] 861c6c32fe0: Merge 10.1 into 10.2
by Oleksandr Byelkin 01 Aug '18

01 Aug '18

revision-id: 861c6c32fe0df073d365f4525f68ca6e57140759 (mariadb-10.2.16-54-g861c6c32fe0) parent(s): f9e4ae816bb1bb84e8f456900cc7378b14981c72 865e807125d1d3fb2d784ea33f1154ba999466ec author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-08-01 14:38:07 +0200 message: Merge 10.1 into 10.2 CMakeLists.txt | 6 + cmake/install_macros.cmake | 9 + cmake/mysql_add_executable.cmake | 3 + include/m_ctype.h | 28 ++- include/sql_common.h | 4 +- mysql-test/include/ctype_mdev13118.inc | 15 ++ mysql-test/r/ctype_binary.result | 23 ++ mysql-test/r/ctype_eucjpms.result | 23 ++ mysql-test/r/ctype_euckr.result | 29 +++ mysql-test/r/ctype_gbk.result | 23 ++ mysql-test/r/ctype_latin1.result | 23 ++ mysql-test/r/ctype_ucs.result | 23 ++ mysql-test/r/ctype_ujis.result | 23 ++ mysql-test/r/ctype_utf16.result | 23 ++ mysql-test/r/ctype_utf16le.result | 29 +++ mysql-test/r/ctype_utf32.result | 23 ++ mysql-test/r/ctype_utf8.result | 23 ++ mysql-test/r/ctype_utf8mb4.result | 23 ++ mysql-test/r/grant.result | 4 +- mysql-test/r/join_outer.result | 50 ++++ mysql-test/r/join_outer_jcl6.result | 50 ++++ mysql-test/r/rename.result | 4 + mysql-test/r/sp_notembedded.result | 2 - mysql-test/r/subselect_mat.result | 93 ++++++++ mysql-test/r/subselect_mat_cost_bugs.result | 2 +- mysql-test/r/subselect_sj_mat.result | 93 ++++++++ mysql-test/r/union.result | 16 ++ .../galera/r/galera_sst_mysqldump_with_key.result | 90 ++++++- .../galera/t/galera_sst_mysqldump_with_key.test | 7 +- .../r/default_row_format_compatibility.result | 2 +- mysql-test/suite/innodb/r/innodb-wl5522.result | 264 ++++++++++++++++++++- .../suite/innodb/r/row_format_redundant.result | 2 +- mysql-test/suite/innodb/t/innodb-wl5522.test | 254 +++++++++++++++++++- mysql-test/suite/parts/r/truncate_locked.result | 7 + mysql-test/suite/parts/t/truncate_locked.test | 10 + .../r/sysvars_innodb,32bit,xtradb.rdiff-disabled | 2 +- .../r/sysvars_innodb,xtradb.rdiff-disabled | 6 +- mysql-test/t/bootstrap.test | 9 + mysql-test/t/ctype_binary.test | 3 + mysql-test/t/ctype_eucjpms.test | 2 + mysql-test/t/ctype_euckr.test | 10 + mysql-test/t/ctype_gbk.test | 3 + mysql-test/t/ctype_latin1.test | 3 + mysql-test/t/ctype_ucs.test | 4 + mysql-test/t/ctype_ujis.test | 4 + mysql-test/t/ctype_utf16.test | 5 + mysql-test/t/ctype_utf16le.test | 13 + mysql-test/t/ctype_utf32.test | 8 + mysql-test/t/ctype_utf8.test | 7 + mysql-test/t/ctype_utf8mb4.test | 8 + mysql-test/t/grant.test | 3 + mysql-test/t/join_outer.test | 48 ++++ mysql-test/t/rename.test | 7 + mysql-test/t/subselect_sj_mat.test | 79 ++++++ mysql-test/t/union.test | 15 ++ mysys/my_rename.c | 5 +- scripts/CMakeLists.txt | 2 + scripts/mysql_install_db.pl.in | 2 +- scripts/mysql_install_db.sh | 2 +- scripts/mysql_system_tables_fix.sql | 50 ++-- scripts/mysqld_multi.sh | 2 +- sql-common/client.c | 4 + sql/item.cc | 3 +- sql/item_strfunc.cc | 112 ++++----- sql/opt_subselect.cc | 66 ++++++ sql/share/CMakeLists.txt | 12 +- sql/sql_acl.cc | 13 +- sql/sql_select.cc | 13 +- sql/sql_table.cc | 2 + sql/sql_truncate.cc | 3 +- sql/tztime.cc | 2 +- storage/connect/filamvct.cpp | 5 - storage/innobase/buf/buf0buddy.cc | 2 +- storage/innobase/buf/buf0dump.cc | 2 +- storage/innobase/fts/fts0fts.cc | 57 ++--- storage/innobase/handler/ha_innodb.cc | 37 ++- storage/innobase/handler/i_s.cc | 8 +- storage/innobase/include/dict0stats.h | 2 +- storage/innobase/row/row0import.cc | 88 +++++-- storage/myisam/ha_myisam.cc | 6 +- storage/myisam/mi_check.c | 6 +- storage/myisam/mi_locking.c | 6 +- storage/xtradb/buf/buf0buddy.cc | 2 +- storage/xtradb/buf/buf0dump.cc | 2 +- storage/xtradb/fts/fts0fts.cc | 57 ++--- storage/xtradb/handler/ha_innodb.cc | 37 ++- storage/xtradb/handler/i_s.cc | 19 +- storage/xtradb/row/row0import.cc | 78 ++++-- storage/xtradb/trx/trx0trx.cc | 4 +- storage/xtradb/trx/trx0undo.cc | 3 +- strings/ctype-bin.c | 8 +- strings/ctype-euc_kr.c | 4 +- strings/ctype-mb.c | 100 ++------ strings/ctype-simple.c | 22 +- strings/ctype-ucs2.c | 78 +++--- strings/ctype-ujis.c | 8 +- strings/ctype-utf8.c | 20 +- support-files/CMakeLists.txt | 2 + 98 files changed, 2065 insertions(+), 433 deletions(-) diff --cc CMakeLists.txt index 48f573b772e,0b48ca20485..990cc5f7097 --- a/CMakeLists.txt +++ b/CMakeLists.txt @@@ -175,7 -177,14 +175,13 @@@ IF(UNIX OPTION(WITH_VALGRIND "Valgrind instrumentation" OFF) ENDIF() OPTION (WITH_UNIT_TESTS "Compile MySQL with unit tests" ON) -MARK_AS_ADVANCED(CYBOZU) + IF (WITHOUT_SERVER) + SET (SKIP_COMPONENTS "Server|IniFiles|SuportFiles|Readme") + ELSE() + SET (SKIP_COMPONENTS "N-O-N-E") + ENDIF() + OPTION(NOT_FOR_DISTRIBUTION "Allow linking with GPLv2-incompatible system libraries. Only set it you never plan to distribute the resulting binaries" OFF) INCLUDE(check_compiler_flag) diff --cc mysql-test/r/ctype_binary.result index 976c3df6e0e,627ba8a35a5..228949904ec --- a/mysql-test/r/ctype_binary.result +++ b/mysql-test/r/ctype_binary.result @@@ -3048,6 -3047,29 +3048,29 @@@ DROP TABLE t1 SELECT _binary 0x7E, _binary X'7E', _binary B'01111110'; _binary 0x7E _binary X'7E' _binary B'01111110' ~ ~ ~ + SET NAMES utf8, character_set_connection=binary; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varbinary(10) NOT NULL ++ `t` varbinary(10) DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + abcdefghi-abcdefghi + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_eucjpms.result index bc7b4b9ef9d,160c4758122..507d0021a1f --- a/mysql-test/r/ctype_eucjpms.result +++ b/mysql-test/r/ctype_eucjpms.result @@@ -33844,6 -33868,29 +33844,29 @@@ HEX(a) CHAR_LENGTH(a DROP TABLE t1; SELECT _eucjpms 0x8EA0; ERROR HY000: Invalid eucjpms character string: '8EA0' + SET NAMES eucjpms; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET eucjpms NOT NULL ++ `t` varchar(10) CHARACTER SET eucjpms DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_euckr.result index 3fb6f98b2eb,a23ced76df9..cc50ddd20be --- a/mysql-test/r/ctype_euckr.result +++ b/mysql-test/r/ctype_euckr.result @@@ -25405,566 -25429,31 +25405,595 @@@ A1A1A1A1A1A1202020202020202020202020202 # End of 5.6 tests # # + # Start of 10.0 tests + # + SET NAMES utf8, character_set_connection=euckr; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET euckr NOT NULL ++ `t` varchar(10) CHARACTER SET euckr DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; + # + # End of 10.0 tests + # ++# +# Start of 10.2 tests +# +# +# MDEV-9711 NO PAD Collatons +# +SET character_set_connection=euckr; +SET STORAGE_ENGINE=MyISAM; +# +# Start of ctype_pad.inc +# +# +# Unique indexes +# +CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'euckr_korean_nopad_ci'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) COLLATE euckr_korean_nopad_ci NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=euckr COLLATE=euckr_korean_nopad_ci +INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM t1 ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; +HEX(a) a +6162632020 abc +616263 abc +61202020 a +206120 a +2061 a +# +# UNION +# +CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'euckr_korean_nopad_ci'; +INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); +SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; +HEX(a) a +2061 a +206120 a +6120 a +61202020 a +616263 abc +61626320 abc +6162632020 abc +DROP TABLE t1; +DROP TABLE t2; +# +# DISTINCT, COUNT, MAX +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'euckr_korean_nopad_ci'; +INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; +HEX(a) a +2061 a +206120 a +61 a +612020 a +61202020 a +SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; +COUNT(DISTINCT a) +5 +SELECT HEX(MAX(a)), MAX(a) FROM t1; +HEX(MAX(a)) MAX(a) +61202020 a +# +# GROUP BY +# +CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'euckr_korean_nopad_ci'; +INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); +SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; +HEX(a) cnt +6161 1 +61612020 1 +6162 2 +DROP TABLE t2; +# +# Weights +# +SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; +HEX(WEIGHT_STRING(a AS CHAR(10))) +41000000000000000000 +41202000000000000000 +20410000000000000000 +20412000000000000000 +41202020000000000000 +DROP TABLE t1; +# +# IF, CASE, LEAST +# +SELECT IF('abc' COLLATE 'euckr_korean_nopad_ci' = 'abc ', 'pad', 'nopad'); +IF('abc' COLLATE 'euckr_korean_nopad_ci' = 'abc ', 'pad', 'nopad') +nopad +SELECT CASE 'abc' COLLATE 'euckr_korean_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE 'abc' COLLATE 'euckr_korean_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT CASE WHEN 'abc' COLLATE 'euckr_korean_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE WHEN 'abc' COLLATE 'euckr_korean_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT HEX(LEAST('abc ' COLLATE 'euckr_korean_nopad_ci', 'abc ')); +HEX(LEAST('abc ' COLLATE 'euckr_korean_nopad_ci', 'abc ')) +61626320 +SELECT HEX(GREATEST('abc ' COLLATE 'euckr_korean_nopad_ci', 'abc ')); +HEX(GREATEST('abc ' COLLATE 'euckr_korean_nopad_ci', 'abc ')) +6162632020 +# +# Collation mix +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'euckr_korean_ci'; +INSERT INTO t1 VALUES ('a'),('a '); +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_korean_ci'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_korean_nopad_ci'; +COUNT(*) +1 +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'euckr_korean_nopad_ci'; +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +1 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_korean_ci'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_korean_nopad_ci'; +COUNT(*) +1 +DROP TABLE t1; +# +# End of ctype_pad.inc +# +SET STORAGE_ENGINE=HEAP; +# +# Start of ctype_pad.inc +# +# +# Unique indexes +# +CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'euckr_korean_nopad_ci'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) COLLATE euckr_korean_nopad_ci NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MEMORY DEFAULT CHARSET=euckr COLLATE=euckr_korean_nopad_ci +INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM t1 ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; +HEX(a) a +6162632020 abc +616263 abc +61202020 a +206120 a +2061 a +# +# UNION +# +CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'euckr_korean_nopad_ci'; +INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); +SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; +HEX(a) a +2061 a +206120 a +6120 a +61202020 a +616263 abc +61626320 abc +6162632020 abc +DROP TABLE t1; +DROP TABLE t2; +# +# DISTINCT, COUNT, MAX +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'euckr_korean_nopad_ci'; +INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; +HEX(a) a +2061 a +206120 a +61 a +612020 a +61202020 a +SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; +COUNT(DISTINCT a) +5 +SELECT HEX(MAX(a)), MAX(a) FROM t1; +HEX(MAX(a)) MAX(a) +61202020 a +# +# GROUP BY +# +CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'euckr_korean_nopad_ci'; +INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); +SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; +HEX(a) cnt +6161 1 +61612020 1 +6162 2 +DROP TABLE t2; +# +# Weights +# +SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; +HEX(WEIGHT_STRING(a AS CHAR(10))) +41000000000000000000 +41202000000000000000 +20410000000000000000 +20412000000000000000 +41202020000000000000 +DROP TABLE t1; +# +# IF, CASE, LEAST +# +SELECT IF('abc' COLLATE 'euckr_korean_nopad_ci' = 'abc ', 'pad', 'nopad'); +IF('abc' COLLATE 'euckr_korean_nopad_ci' = 'abc ', 'pad', 'nopad') +nopad +SELECT CASE 'abc' COLLATE 'euckr_korean_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE 'abc' COLLATE 'euckr_korean_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT CASE WHEN 'abc' COLLATE 'euckr_korean_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE WHEN 'abc' COLLATE 'euckr_korean_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT HEX(LEAST('abc ' COLLATE 'euckr_korean_nopad_ci', 'abc ')); +HEX(LEAST('abc ' COLLATE 'euckr_korean_nopad_ci', 'abc ')) +61626320 +SELECT HEX(GREATEST('abc ' COLLATE 'euckr_korean_nopad_ci', 'abc ')); +HEX(GREATEST('abc ' COLLATE 'euckr_korean_nopad_ci', 'abc ')) +6162632020 +# +# Collation mix +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'euckr_korean_ci'; +INSERT INTO t1 VALUES ('a'),('a '); +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_korean_ci'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_korean_nopad_ci'; +COUNT(*) +1 +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'euckr_korean_nopad_ci'; +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +1 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_korean_ci'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_korean_nopad_ci'; +COUNT(*) +1 +DROP TABLE t1; +# +# End of ctype_pad.inc +# +SET STORAGE_ENGINE=Default; +SET STORAGE_ENGINE=MyISAM; +# +# Start of ctype_pad.inc +# +# +# Unique indexes +# +CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'euckr_nopad_bin'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) COLLATE euckr_nopad_bin NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=euckr COLLATE=euckr_nopad_bin +INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM t1 ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; +HEX(a) a +6162632020 abc +616263 abc +61202020 a +206120 a +2061 a +# +# UNION +# +CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'euckr_nopad_bin'; +INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); +SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; +HEX(a) a +2061 a +206120 a +6120 a +61202020 a +616263 abc +61626320 abc +6162632020 abc +DROP TABLE t1; +DROP TABLE t2; +# +# DISTINCT, COUNT, MAX +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'euckr_nopad_bin'; +INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; +HEX(a) a +2061 a +206120 a +61 a +612020 a +61202020 a +SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; +COUNT(DISTINCT a) +5 +SELECT HEX(MAX(a)), MAX(a) FROM t1; +HEX(MAX(a)) MAX(a) +61202020 a +# +# GROUP BY +# +CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'euckr_nopad_bin'; +INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); +SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; +HEX(a) cnt +6161 1 +61612020 1 +6162 2 +DROP TABLE t2; +# +# Weights +# +SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; +HEX(WEIGHT_STRING(a AS CHAR(10))) +61000000000000000000 +61202000000000000000 +20610000000000000000 +20612000000000000000 +61202020000000000000 +DROP TABLE t1; +# +# IF, CASE, LEAST +# +SELECT IF('abc' COLLATE 'euckr_nopad_bin' = 'abc ', 'pad', 'nopad'); +IF('abc' COLLATE 'euckr_nopad_bin' = 'abc ', 'pad', 'nopad') +nopad +SELECT CASE 'abc' COLLATE 'euckr_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE 'abc' COLLATE 'euckr_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT CASE WHEN 'abc' COLLATE 'euckr_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE WHEN 'abc' COLLATE 'euckr_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT HEX(LEAST('abc ' COLLATE 'euckr_nopad_bin', 'abc ')); +HEX(LEAST('abc ' COLLATE 'euckr_nopad_bin', 'abc ')) +61626320 +SELECT HEX(GREATEST('abc ' COLLATE 'euckr_nopad_bin', 'abc ')); +HEX(GREATEST('abc ' COLLATE 'euckr_nopad_bin', 'abc ')) +6162632020 +# +# Collation mix +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'euckr_bin'; +INSERT INTO t1 VALUES ('a'),('a '); +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_bin'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_nopad_bin'; +COUNT(*) +1 +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'euckr_nopad_bin'; +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +1 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_bin'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_nopad_bin'; +COUNT(*) +1 +DROP TABLE t1; +# +# End of ctype_pad.inc +# +SET STORAGE_ENGINE=HEAP; +# +# Start of ctype_pad.inc +# +# +# Unique indexes +# +CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'euckr_nopad_bin'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) COLLATE euckr_nopad_bin NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MEMORY DEFAULT CHARSET=euckr COLLATE=euckr_nopad_bin +INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM t1 ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; +HEX(a) a +6162632020 abc +616263 abc +61202020 a +206120 a +2061 a +# +# UNION +# +CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'euckr_nopad_bin'; +INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); +SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; +HEX(a) a +2061 a +206120 a +6120 a +61202020 a +616263 abc +61626320 abc +6162632020 abc +DROP TABLE t1; +DROP TABLE t2; +# +# DISTINCT, COUNT, MAX +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'euckr_nopad_bin'; +INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; +HEX(a) a +2061 a +206120 a +61 a +612020 a +61202020 a +SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; +COUNT(DISTINCT a) +5 +SELECT HEX(MAX(a)), MAX(a) FROM t1; +HEX(MAX(a)) MAX(a) +61202020 a +# +# GROUP BY +# +CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'euckr_nopad_bin'; +INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); +SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; +HEX(a) cnt +6161 1 +61612020 1 +6162 2 +DROP TABLE t2; +# +# Weights +# +SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; +HEX(WEIGHT_STRING(a AS CHAR(10))) +61000000000000000000 +61202000000000000000 +20610000000000000000 +20612000000000000000 +61202020000000000000 +DROP TABLE t1; +# +# IF, CASE, LEAST +# +SELECT IF('abc' COLLATE 'euckr_nopad_bin' = 'abc ', 'pad', 'nopad'); +IF('abc' COLLATE 'euckr_nopad_bin' = 'abc ', 'pad', 'nopad') +nopad +SELECT CASE 'abc' COLLATE 'euckr_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE 'abc' COLLATE 'euckr_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT CASE WHEN 'abc' COLLATE 'euckr_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE WHEN 'abc' COLLATE 'euckr_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT HEX(LEAST('abc ' COLLATE 'euckr_nopad_bin', 'abc ')); +HEX(LEAST('abc ' COLLATE 'euckr_nopad_bin', 'abc ')) +61626320 +SELECT HEX(GREATEST('abc ' COLLATE 'euckr_nopad_bin', 'abc ')); +HEX(GREATEST('abc ' COLLATE 'euckr_nopad_bin', 'abc ')) +6162632020 +# +# Collation mix +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'euckr_bin'; +INSERT INTO t1 VALUES ('a'),('a '); +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_bin'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_nopad_bin'; +COUNT(*) +1 +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'euckr_nopad_bin'; +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +1 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_bin'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_nopad_bin'; +COUNT(*) +1 +DROP TABLE t1; +# +# End of ctype_pad.inc +# +SET STORAGE_ENGINE=Default; +# +# End of 10.2 tests +# diff --cc mysql-test/r/ctype_gbk.result index 5244d6eb333,ac8de1e229c..4e5c9695415 --- a/mysql-test/r/ctype_gbk.result +++ b/mysql-test/r/ctype_gbk.result @@@ -5077,6 -5099,29 +5077,29 @@@ E05C5 E05B DROP TABLE t1; # Start of ctype_E05C.inc + SET NAMES utf8, character_set_connection=gbk; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET gbk NOT NULL ++ `t` varchar(10) CHARACTER SET gbk DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant # diff --cc mysql-test/r/ctype_latin1.result index cbf5f145d22,3b9b2633480..9899342bdcd --- a/mysql-test/r/ctype_latin1.result +++ b/mysql-test/r/ctype_latin1.result @@@ -8022,6 -7989,29 +8022,29 @@@ 0 DROP VIEW v1; DROP TABLE t1; + SET NAMES latin1; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) NOT NULL ++ `t` varchar(10) DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_ucs.result index f6d3d86e2cd,9c598780edf..3caac471a7c --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@@ -5685,6 -5705,29 +5685,29 @@@ c YWJjZGVmZ2hp-YWJjZGVmZ2hp DROP TABLE t1; SET optimizer_switch=@save_optimizer_switch; + SET NAMES utf8, character_set_connection=ucs2; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET ucs2 NOT NULL ++ `t` varchar(10) CHARACTER SET ucs2 DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_ujis.result index cb9de83fedc,9146356b5f9..0b1cf606f0b --- a/mysql-test/r/ctype_ujis.result +++ b/mysql-test/r/ctype_ujis.result @@@ -26149,6 -26173,29 +26149,29 @@@ HEX(a) CHAR_LENGTH(a DROP TABLE t1; SELECT _ujis 0x8EA0; ERROR HY000: Invalid ujis character string: '8EA0' + SET NAMES ujis; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET ujis NOT NULL ++ `t` varchar(10) CHARACTER SET ujis DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_utf16.result index 8de21a14bd9,ba72a859351..a105017b726 --- a/mysql-test/r/ctype_utf16.result +++ b/mysql-test/r/ctype_utf16.result @@@ -2142,6 -2138,29 +2142,29 @@@ EXECUTE stmt USING @arg00 CONCAT(_utf16'a' COLLATE utf16_unicode_ci, ?) aÿ DEALLOCATE PREPARE stmt; + SET NAMES utf8, character_set_connection=utf16; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET utf16 NOT NULL ++ `t` varchar(10) CHARACTER SET utf16 DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_utf16le.result index 0fedff7d3bf,c7c8c210014..6d8d7ddca2a --- a/mysql-test/r/ctype_utf16le.result +++ b/mysql-test/r/ctype_utf16le.result @@@ -2326,6 -2324,35 +2326,35 @@@ DFFFFFDFFFFF9CFFFF9DFFFF9EFFF # End of 5.6 tests # # + # Start of 10.0 tests + # + SET NAMES utf8, character_set_connection=utf16le; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET utf16le NOT NULL ++ `t` varchar(10) CHARACTER SET utf16le DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; + # + # Start of 10.0 tests + # + # # Start of 10.1 tests # # diff --cc mysql-test/r/ctype_utf32.result index 59ce4c2f445,024f8aa7875..28b9caf6ffe --- a/mysql-test/r/ctype_utf32.result +++ b/mysql-test/r/ctype_utf32.result @@@ -2241,6 -2237,29 +2241,29 @@@ EXECUTE stmt USING @arg00 CONCAT(_utf32'a' COLLATE utf32_unicode_ci, ?) aÿ DEALLOCATE PREPARE stmt; + SET NAMEs utf8, character_set_connection=utf32; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET utf32 NOT NULL ++ `t` varchar(10) CHARACTER SET utf32 DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_utf8.result index 46b7386d993,7b486f0f443..de524055d51 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@@ -10286,6 -10278,29 +10286,29 @@@ SELECT * FROM v1 c ß DROP VIEW v1; + SET NAMES utf8; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET utf8 NOT NULL ++ `t` varchar(10) CHARACTER SET utf8 DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_utf8mb4.result index 7e5e99354ea,de9ba321ad9..27ffd5552fa --- a/mysql-test/r/ctype_utf8mb4.result +++ b/mysql-test/r/ctype_utf8mb4.result @@@ -3478,6 -3469,29 +3478,29 @@@ t1 CREATE TABLE `t1` ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; SET NAMES default; + SET NAMES utf8mb4; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET utf8mb4 NOT NULL ++ `t` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/grant.result index 81b41a083a3,7abaf834975..44eb59fbf9e --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@@ -1750,9 -1683,9 +1750,10 @@@ revoke insert on mysqltest.t4 f revoke create, insert on mysqltest.t5 from mysqltest@localhost; revoke create, insert on mysqltest.t6 from mysqltest@localhost; drop user mysqltest@localhost; +disconnect user1; drop database mysqltest; use test; + call mtr.add_suppression("Can't open and lock privilege tables"); FLUSH PRIVILEGES without procs_priv table. RENAME TABLE mysql.procs_priv TO mysql.procs_gone; FLUSH PRIVILEGES; @@@ -1846,9 -1774,6 +1847,7 @@@ BEGI SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; END ;|| - Warnings: - Warning 1404 Failed to grant EXECUTE and ALTER ROUTINE privileges +connection default; SHOW GRANTS FOR 'user1'@'localhost'; Grants for user1@localhost GRANT USAGE ON *.* TO 'user1'@'localhost' @@@ -1858,8 -1783,7 +1857,9 @@@ SHOW GRANTS FOR 'user2' Grants for user2@% GRANT USAGE ON *.* TO 'user2'@'%' GRANT CREATE, CREATE ROUTINE ON `db1`.* TO 'user2'@'%' + GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `db1`.`proc2` TO 'user2'@'%' +disconnect con1; +disconnect con2; DROP PROCEDURE db1.proc1; DROP PROCEDURE db1.proc2; REVOKE ALL ON db1.* FROM 'user1'@'localhost'; diff --cc mysql-test/r/sp_notembedded.result index 190f8f78d23,712f299014c..8edb9e79b70 --- a/mysql-test/r/sp_notembedded.result +++ b/mysql-test/r/sp_notembedded.result @@@ -225,13 -203,7 +225,11 @@@ VALUES('%', 'mysqltest_1', password('') Warnings: Warning 1364 Field 'authentication_string' doesn't have a default value FLUSH PRIVILEGES; +connect con1, localhost, mysqltest_1,,; +connection con1; CREATE PROCEDURE p1(i INT) BEGIN END; - Warnings: - Warning 1404 Failed to grant EXECUTE and ALTER ROUTINE privileges +disconnect con1; +connection default; DROP PROCEDURE p1; DELETE FROM mysql.user WHERE User='mysqltest_1'; FLUSH PRIVILEGES; diff --cc mysql-test/suite/galera/r/galera_sst_mysqldump_with_key.result index 93ab4a3f3d4,bdf3844f2c5..0329e105a63 --- a/mysql-test/suite/galera/r/galera_sst_mysqldump_with_key.result +++ b/mysql-test/suite/galera/r/galera_sst_mysqldump_with_key.result @@@ -59,8 -49,95 +59,96 @@@ INSERT INTO t1 VALUES ('node1_to_be_rol INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +connection node_2; - Loading wsrep provider ... + Starting server ... + SET AUTOCOMMIT=OFF; + START TRANSACTION; + INSERT INTO t1 VALUES ('node2_committed_after'); + INSERT INTO t1 VALUES ('node2_committed_after'); + INSERT INTO t1 VALUES ('node2_committed_after'); + INSERT INTO t1 VALUES ('node2_committed_after'); + INSERT INTO t1 VALUES ('node2_committed_after'); + COMMIT; + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + COMMIT; + SET AUTOCOMMIT=OFF; + START TRANSACTION; + INSERT INTO t1 VALUES ('node1_committed_after'); + INSERT INTO t1 VALUES ('node1_committed_after'); + INSERT INTO t1 VALUES ('node1_committed_after'); + INSERT INTO t1 VALUES ('node1_committed_after'); + INSERT INTO t1 VALUES ('node1_committed_after'); + COMMIT; + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + ROLLBACK; + SELECT COUNT(*) = 35 FROM t1; + COUNT(*) = 35 + 1 + SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; + COUNT(*) = 0 + 1 + COMMIT; + SET AUTOCOMMIT=ON; + SELECT COUNT(*) = 35 FROM t1; + COUNT(*) = 35 + 1 + SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; + COUNT(*) = 0 + 1 + DROP TABLE t1; + COMMIT; + SET AUTOCOMMIT=ON; + Performing State Transfer on a server that has been killed and restarted + CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; + SET AUTOCOMMIT=OFF; + START TRANSACTION; + INSERT INTO t1 VALUES ('node1_committed_before'); + INSERT INTO t1 VALUES ('node1_committed_before'); + INSERT INTO t1 VALUES ('node1_committed_before'); + INSERT INTO t1 VALUES ('node1_committed_before'); + INSERT INTO t1 VALUES ('node1_committed_before'); + COMMIT; + SET AUTOCOMMIT=OFF; + START TRANSACTION; + INSERT INTO t1 VALUES ('node2_committed_before'); + INSERT INTO t1 VALUES ('node2_committed_before'); + INSERT INTO t1 VALUES ('node2_committed_before'); + INSERT INTO t1 VALUES ('node2_committed_before'); + INSERT INTO t1 VALUES ('node2_committed_before'); + COMMIT; + Killing server ... + SET AUTOCOMMIT=OFF; + START TRANSACTION; + INSERT INTO t1 VALUES ('node1_committed_during'); + INSERT INTO t1 VALUES ('node1_committed_during'); + INSERT INTO t1 VALUES ('node1_committed_during'); + INSERT INTO t1 VALUES ('node1_committed_during'); + INSERT INTO t1 VALUES ('node1_committed_during'); + COMMIT; + START TRANSACTION; + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + SET AUTOCOMMIT=OFF; + START TRANSACTION; + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + Performing --wsrep-recover ... + Starting server ... + Using --wsrep-start-position when starting mysqld ... SET AUTOCOMMIT=OFF; START TRANSACTION; INSERT INTO t1 VALUES ('node2_committed_after'); diff --cc mysql-test/suite/innodb/r/innodb-wl5522.result index 4ef92cdfed9,fb80580f797..4ef8a2ee131 --- a/mysql-test/suite/innodb/r/innodb-wl5522.result +++ b/mysql-test/suite/innodb/r/innodb-wl5522.result @@@ -104,15 -99,6 +104,15 @@@ a b 822 Devotion asdfuihknaskdf 821 Cavalry ..asdasdfaeraf DROP TABLE t1; +ALTER TABLE t2 ROW_FORMAT=DYNAMIC; +ALTER TABLE t2 DISCARD TABLESPACE; +# List after t2 DISCARD +t2.frm +ALTER TABLE t2 IMPORT TABLESPACE; - ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x1) ++ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x1; .cfg file uses ROW_FORMAT=COMPACT) +ALTER TABLE t2 IMPORT TABLESPACE; +ERROR HY000: Schema mismatch (Expected FSP_SPACE_FLAGS=0x*, .ibd file contains 0x*.) +DROP TABLE t2; SET GLOBAL innodb_file_per_table = 1; SELECT @@innodb_file_per_table; @@innodb_file_per_table @@@ -586,10 -576,10 +586,10 @@@ c1 INT NOT NULL AUTO_INCREMENT PRIMARY c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' +ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; - ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x1 and the meta-data file has 0x0) + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x1 and the meta-data file has 0x0; .cfg file uses ROW_FORMAT=REDUNDANT) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; @@@ -598,10 -588,22 +598,22 @@@ c1 INT NOT NULL AUTO_INCREMENT PRIMARY c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' +ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; - ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x0) + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x0; .cfg file uses ROW_FORMAT=REDUNDANT) + unlink: t1.ibd + unlink: t1.cfg + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x29 and the meta-data file has 0x0; .cfg file uses ROW_FORMAT=REDUNDANT) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; @@@ -772,10 -774,10 +784,10 @@@ c1 INT NOT NULL AUTO_INCREMENT PRIMARY c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' +ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; - ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x0 and the meta-data file has 0x1) + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x0 and the meta-data file has 0x1; .cfg file uses ROW_FORMAT=COMPACT) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; @@@ -784,10 -786,22 +796,22 @@@ c1 INT NOT NULL AUTO_INCREMENT PRIMARY c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' +ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; - ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x1) + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x1; .cfg file uses ROW_FORMAT=COMPACT) + unlink: t1.ibd + unlink: t1.cfg + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x29 and the meta-data file has 0x1; .cfg file uses ROW_FORMAT=COMPACT) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; @@@ -961,10 -975,10 +985,10 @@@ c1 INT NOT NULL AUTO_INCREMENT PRIMARY c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' +ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; - ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x1 and the meta-data file has 0x21) + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x1 and the meta-data file has 0x21; .cfg file uses ROW_FORMAT=DYNAMIC) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; @@@ -973,10 -987,22 +997,22 @@@ c1 INT NOT NULL AUTO_INCREMENT PRIMARY c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' +ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; - ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x0 and the meta-data file has 0x21) + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x0 and the meta-data file has 0x21; .cfg file uses ROW_FORMAT=DYNAMIC) + unlink: t1.ibd + unlink: t1.cfg + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x29 and the meta-data file has 0x21; .cfg file uses ROW_FORMAT=DYNAMIC) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; @@@ -1035,6 -1061,220 +1071,220 @@@ c1 c 42 1 43 1 DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; + INSERT INTO t1(c2) VALUES(1); + INSERT INTO t1(c2) SELECT c2 FROM t1; + INSERT INTO t1(c2) SELECT c2 FROM t1; + INSERT INTO t1(c2) SELECT c2 FROM t1; + INSERT INTO t1(c2) SELECT c2 FROM t1; + INSERT INTO t1(c2) SELECT c2 FROM t1; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + `c2` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`), + KEY `idx` (`c2`) + ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED + SELECT * FROM t1; + c1 c2 + 1 1 + 2 1 + 3 1 + 4 1 + 6 1 + 7 1 + 8 1 + 9 1 + 13 1 + 14 1 + 15 1 + 16 1 + 17 1 + 18 1 + 19 1 + 20 1 + 28 1 + 29 1 + 30 1 + 31 1 + 32 1 + 33 1 + 34 1 + 35 1 + 36 1 + 37 1 + 38 1 + 39 1 + 40 1 + 41 1 + 42 1 + 43 1 + FLUSH TABLES t1 FOR EXPORT; + backup: t1 + UNLOCK TABLES; + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + CHECK TABLE t1; + Table Op Msg_type Msg_text + test.t1 check status OK + unlink: t1.cfg + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + `c2` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`), + KEY `idx` (`c2`) -) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED ++) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED + SELECT * FROM t1; + c1 c2 + 1 1 + 2 1 + 3 1 + 4 1 + 6 1 + 7 1 + 8 1 + 9 1 + 13 1 + 14 1 + 15 1 + 16 1 + 17 1 + 18 1 + 19 1 + 20 1 + 28 1 + 29 1 + 30 1 + 31 1 + 32 1 + 33 1 + 34 1 + 35 1 + 36 1 + 37 1 + 38 1 + 39 1 + 40 1 + 41 1 + 42 1 + 43 1 + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x0 and the meta-data file has 0x29; .cfg file uses ROW_FORMAT=COMPRESSED) + unlink: t1.ibd + unlink: t1.cfg + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x1 and the meta-data file has 0x29; .cfg file uses ROW_FORMAT=COMPRESSED) + unlink: t1.ibd + unlink: t1.cfg + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x29; .cfg file uses ROW_FORMAT=COMPRESSED) + unlink: t1.ibd + unlink: t1.cfg + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB + ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x23 and the meta-data file has 0x29; .cfg file uses ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8) + unlink: t1.ibd + unlink: t1.cfg + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + CHECK TABLE t1; + Table Op Msg_type Msg_text + test.t1 check status OK + unlink: t1.cfg + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + `c2` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`), + KEY `idx` (`c2`) -) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED ++) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED + SELECT * FROM t1; + c1 c2 + 1 1 + 2 1 + 3 1 + 4 1 + 6 1 + 7 1 + 8 1 + 9 1 + 13 1 + 14 1 + 15 1 + 16 1 + 17 1 + 18 1 + 19 1 + 20 1 + 28 1 + 29 1 + 30 1 + 31 1 + 32 1 + 33 1 + 34 1 + 35 1 + 36 1 + 37 1 + 38 1 + 39 1 + 40 1 + 41 1 + 42 1 + 43 1 + DROP TABLE t1; call mtr.add_suppression("Got error -1 when reading table '.*'"); call mtr.add_suppression("InnoDB: Error: tablespace id and flags in file '.*'.*"); call mtr.add_suppression("InnoDB: The table .* doesn't have a corresponding tablespace, it was discarded"); diff --cc mysql-test/suite/innodb/r/row_format_redundant.result index a2d5bbef8df,db31c32559f..07b7a29675e --- a/mysql-test/suite/innodb/r/row_format_redundant.result +++ b/mysql-test/suite/innodb/r/row_format_redundant.result @@@ -51,29 -45,4 +51,29 @@@ ERROR HY000: Table 't3' is read onl TRUNCATE TABLE t1; TRUNCATE TABLE t2; TRUNCATE TABLE t3; -DROP TABLE t1,t2,t3; +corrupted SYS_TABLES.MIX_LEN for test/t1 +corrupted SYS_TABLES.MIX_LEN for test/t2 +corrupted SYS_TABLES.MIX_LEN for test/t3 +TRUNCATE TABLE t1; +ERROR 42S02: Table 'test.t1' doesn't exist in engine +TRUNCATE TABLE t2; +TRUNCATE TABLE t3; +SELECT COUNT(*) FROM t1; +ERROR 42S02: Table 'test.t1' doesn't exist in engine +SELECT COUNT(*) FROM t2; +COUNT(*) +0 +SELECT COUNT(*) FROM t3; +COUNT(*) +0 +RENAME TABLE t1 TO tee_one; +ERROR HY000: Error on rename of './test/t1' to './test/tee_one' (errno: 155 "The table does not exist in the storage engine") +DROP TABLE t1; +Warnings: +Warning 1932 Table 'test.t1' doesn't exist in engine +DROP TABLE t2,t3; - FOUND 49 /\[ERROR\] InnoDB: Table `test`\.`t1` in InnoDB data dictionary contains invalid flags\. SYS_TABLES\.TYPE=1 SYS_TABLES\.MIX_LEN=255\b/ in mysqld.1.err ++FOUND 50 /\[ERROR\] InnoDB: Table `test`\.`t1` in InnoDB data dictionary contains invalid flags\. SYS_TABLES\.TYPE=1 SYS_TABLES\.MIX_LEN=255\b/ in mysqld.1.err +ib_buffer_pool +ib_logfile0 +ib_logfile1 +ibdata1 diff --cc mysql-test/t/ctype_euckr.test index 950c60c47e5,71660314279..46955360217 --- a/mysql-test/t/ctype_euckr.test +++ b/mysql-test/t/ctype_euckr.test @@@ -197,23 -197,14 +197,33 @@@ set collation_connection=euckr_bin --echo # End of 5.6 tests --echo # - + --echo # + --echo # Start of 10.0 tests + --echo # + + SET NAMES utf8, character_set_connection=euckr; + --source include/ctype_mdev13118.inc + + --echo # + --echo # End of 10.0 tests + --echo # + +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo # +--echo # MDEV-9711 NO PAD Collatons +--echo # +SET character_set_connection=euckr; +let $coll='euckr_korean_nopad_ci'; +let $coll_pad='euckr_korean_ci'; +--source include/ctype_pad_all_engines.inc + +let $coll='euckr_nopad_bin'; +let $coll_pad='euckr_bin'; +--source include/ctype_pad_all_engines.inc + +--echo # +--echo # End of 10.2 tests +--echo # diff --cc scripts/mysql_install_db.sh index 1d640abef7c,40fdf798d71..9e0c66cc55b --- a/scripts/mysql_install_db.sh +++ b/scripts/mysql_install_db.sh @@@ -346,10 -343,9 +346,10 @@@ els basedir="@prefix@" bindir="@bindir@" resolveip="$bindir/resolveip" - mysqld="@libexecdir@/mysqld" + mysqld="@sbindir@/mysqld" srcpkgdatadir="@pkgdatadir@" buildpkgdatadir="@pkgdatadir@" + plugindir="@pkgplugindir@" fi # Set up paths to SQL scripts required for bootstrap diff --cc scripts/mysql_system_tables_fix.sql index 2a826bb2df0,a982fc87ec9..e724c3e2d23 --- a/scripts/mysql_system_tables_fix.sql +++ b/scripts/mysql_system_tables_fix.sql @@@ -162,14 -171,8 +171,14 @@@ alter table func comment='User defined # and reset all char columns to correct width ALTER TABLE user MODIFY Host char(60) NOT NULL default '', - MODIFY User char(80) NOT NULL default '', + MODIFY User char(80) binary NOT NULL default '', ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; + +# In MySQL 5.7.6 the Password column is removed. Recreate it to preserve the number +# of columns MariaDB expects in the user table. +ALTER TABLE user + ADD Password char(41) character set latin1 collate latin1_bin NOT NULL default '' AFTER User; + ALTER TABLE user MODIFY Password char(41) character set latin1 collate latin1_bin NOT NULL default '', MODIFY Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, diff --cc sql/item.cc index a501ff6fdc6,f1b57c64410..58d2b7dbfc0 --- a/sql/item.cc +++ b/sql/item.cc @@@ -10406,8 -9579,9 +10406,9 @@@ bool Item_type_holder::join_types(THD * geometry_type= Field_geom::geometry_type_merge(geometry_type, item->get_geometry_type()); - if (Field::result_merge_type(fld_type) == DECIMAL_RESULT) + if (Field::result_merge_type(real_field_type()) == DECIMAL_RESULT) { + collation.set_numeric(); decimals= MY_MIN(MY_MAX(decimals, item->decimals), DECIMAL_MAX_SCALE); int item_int_part= item->decimal_int_part(); int item_prec = MY_MAX(prev_decimal_int_part, item_int_part) + decimals; diff --cc storage/connect/filamvct.cpp index a660461e9ee,dd827d084fa..71174c3530f mode 100644,100755..100644 --- a/storage/connect/filamvct.cpp +++ b/storage/connect/filamvct.cpp diff --cc storage/innobase/handler/ha_innodb.cc index a8594fd37b3,5667efea5df..d8e805862f7 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@@ -13649,7 -13000,7 +13680,7 @@@ ha_innobase::rename_table ++trx->will_lock; trx_set_dict_operation(trx, TRX_DICT_OP_INDEX); - dberr_t error = innobase_rename_table(trx, from, to); - error = innobase_rename_table(thd, trx, from, to); ++ dberr_t error = innobase_rename_table(thd, trx, from, to); DEBUG_SYNC(thd, "after_innobase_rename_table"); diff --cc storage/innobase/include/dict0stats.h index 6687ff79795,72501bf9429..7f25c26f1e2 --- a/storage/innobase/include/dict0stats.h +++ b/storage/innobase/include/dict0stats.h @@@ -110,22 -129,6 +110,22 @@@ dict_stats_deinit dict_table_t* table) /*!< in/out: table */ MY_ATTRIBUTE((nonnull)); +#ifdef WITH_WSREP +/** Update the table modification counter and if necessary, +schedule new estimates for table and index statistics to be calculated. +@param[in,out] table persistent or temporary table +@param[in] thd current session */ +void dict_stats_update_if_needed(dict_table_t* table, THD* thd) + MY_ATTRIBUTE((nonnull(1))); +#else +/** Update the table modification counter and if necessary, +schedule new estimates for table and index statistics to be calculated. +@param[in,out] table persistent or temporary table */ +void dict_stats_update_if_needed_func(dict_table_t* table) + MY_ATTRIBUTE((nonnull)); - # define dict_stats_update_if_needed(t, thd) dict_stats_update_if_needed(t) ++# define dict_stats_update_if_needed(t, thd) dict_stats_update_if_needed_func(t) +#endif + /*********************************************************************//** Calculates new estimates for table and index statistics. The statistics are used in query optimization. diff --cc storage/innobase/row/row0import.cc index 3689c21588d,02e1c3d33a3..de3fa36c7ae --- a/storage/innobase/row/row0import.cc +++ b/storage/innobase/row/row0import.cc @@@ -113,19 -111,19 +113,19 @@@ struct row_index_t struct row_import { row_import() UNIV_NOTHROW : - m_table(), - m_version(), - m_hostname(), - m_table_name(), - m_autoinc(), + m_table(NULL), + m_version(0), + m_hostname(NULL), + m_table_name(NULL), + m_autoinc(0), - m_page_size(0), + m_page_size(0, 0, false), - m_flags(), - m_n_cols(), - m_cols(), - m_col_names(), - m_n_indexes(), - m_indexes(), - m_missing(true) {} + m_flags(0), + m_n_cols(0), + m_cols(NULL), + m_col_names(NULL), + m_n_indexes(0), + m_indexes(NULL), + m_missing(true) { } ~row_import() UNIV_NOTHROW; @@@ -1229,11 -1298,57 +1229,56 @@@ row_import::match_schema { /* Do some simple checks. */ - if ((m_table->flags ^ m_flags) & ~DICT_TF_MASK_DATA_DIR) { - ib_errf(thd, IB_LOG_LEVEL_ERROR, ER_TABLE_SCHEMA_MISMATCH, - "Table flags don't match, server table has 0x%x" - " and the meta-data file has 0x" ULINTPFx, - m_table->flags, m_flags); + if (ulint mismatch = (m_table->flags ^ m_flags) + & ~DICT_TF_MASK_DATA_DIR) { + const char* msg; + if (mismatch & DICT_TF_MASK_ZIP_SSIZE) { + if ((m_table->flags & DICT_TF_MASK_ZIP_SSIZE) + && (m_flags & DICT_TF_MASK_ZIP_SSIZE)) { + switch (m_flags & DICT_TF_MASK_ZIP_SSIZE) { + case 0U << DICT_TF_POS_ZIP_SSIZE: + goto uncompressed; + case 1U << DICT_TF_POS_ZIP_SSIZE: + msg = "ROW_FORMAT=COMPRESSED" + " KEY_BLOCK_SIZE=1"; + break; + case 2U << DICT_TF_POS_ZIP_SSIZE: + msg = "ROW_FORMAT=COMPRESSED" + " KEY_BLOCK_SIZE=2"; + break; + case 3U << DICT_TF_POS_ZIP_SSIZE: + msg = "ROW_FORMAT=COMPRESSED" + " KEY_BLOCK_SIZE=4"; + break; + case 4U << DICT_TF_POS_ZIP_SSIZE: + msg = "ROW_FORMAT=COMPRESSED" + " KEY_BLOCK_SIZE=8"; + break; + case 5U << DICT_TF_POS_ZIP_SSIZE: + msg = "ROW_FORMAT=COMPRESSED" + " KEY_BLOCK_SIZE=16"; + break; + default: + msg = "strange KEY_BLOCK_SIZE"; + } + } else if (m_flags & DICT_TF_MASK_ZIP_SSIZE) { + msg = "ROW_FORMAT=COMPRESSED"; + } else { + goto uncompressed; + } + } else { + uncompressed: + msg = (m_flags & DICT_TF_MASK_ATOMIC_BLOBS) + ? "ROW_FORMAT=DYNAMIC" + : (m_flags & DICT_TF_MASK_COMPACT) + ? "ROW_FORMAT=COMPACT" + : "ROW_FORMAT=REDUNDANT"; + } - - ib_errf(thd, IB_LOG_LEVEL_ERROR, ER_TABLE_SCHEMA_MISMATCH, - "Table flags don't match, server table has 0x%x" - " and the meta-data file has 0x%lx;" - " .cfg file uses %s", - m_table->flags, ulong(m_flags), msg); ++ ib_errf(thd, IB_LOG_LEVEL_ERROR, ER_TABLE_SCHEMA_MISMATCH, ++ "Table flags don't match, server table has 0x%x" ++ " and the meta-data file has 0x" ULINTPFx ";" ++ " .cfg file uses %s", ++ m_table->flags, m_flags, msg); return(DB_ERROR); } else if (m_table->n_cols != m_n_cols) { diff --cc strings/ctype-ucs2.c index 80bb6745b19,0791b0b742c..687796bdcbb --- a/strings/ctype-ucs2.c +++ b/strings/ctype-ucs2.c @@@ -1406,19 -1378,9 +1407,18 @@@ my_hash_sort_utf16_nopad(CHARSET_INFO * } +static void +my_hash_sort_utf16(CHARSET_INFO *cs, const uchar *s, size_t slen, + ulong *nr1, ulong *nr2) +{ + size_t lengthsp= cs->cset->lengthsp(cs, (const char *) s, slen); + my_hash_sort_utf16_nopad(cs, s, lengthsp, nr1, nr2); +} + + static size_t - my_casedn_utf16(CHARSET_INFO *cs, char *src, size_t srclen, - char *dst __attribute__((unused)), - size_t dstlen __attribute__((unused))) + my_casedn_utf16(CHARSET_INFO *cs, const char *src, size_t srclen, + char *dst, size_t dstlen) { my_wc_t wc; my_charset_conv_mb_wc mb_wc= cs->cset->mb_wc; @@@ -2218,22 -1993,9 +2220,21 @@@ my_tosort_utf32(MY_UNICASE_INFO *uni_pl } +static size_t +my_lengthsp_utf32(CHARSET_INFO *cs __attribute__((unused)), + const char *ptr, size_t length) +{ + const char *end= ptr + length; + DBUG_ASSERT((length % 4) == 0); + while (end > ptr + 3 && end[-1] == ' ' && !end[-2] && !end[-3] && !end[-4]) + end-= 4; + return (size_t) (end - ptr); +} + + static size_t - my_caseup_utf32(CHARSET_INFO *cs, char *src, size_t srclen, - char *dst __attribute__((unused)), - size_t dstlen __attribute__((unused))) + my_caseup_utf32(CHARSET_INFO *cs, const char *src, size_t srclen, + char *dst, size_t dstlen) { my_wc_t wc; int res; @@@ -2277,19 -2045,9 +2280,18 @@@ my_hash_sort_utf32_nopad(CHARSET_INFO * } +static void +my_hash_sort_utf32(CHARSET_INFO *cs, const uchar *s, size_t slen, + ulong *nr1, ulong *nr2) +{ + size_t lengthsp= my_lengthsp_utf32(cs, (const char *) s, slen); + my_hash_sort_utf32_nopad(cs, s, lengthsp, nr1, nr2); +} + + static size_t - my_casedn_utf32(CHARSET_INFO *cs, char *src, size_t srclen, - char *dst __attribute__((unused)), - size_t dstlen __attribute__((unused))) + my_casedn_utf32(CHARSET_INFO *cs, const char *src, size_t srclen, + char *dst, size_t dstlen) { my_wc_t wc; int res; @@@ -3124,16 -2831,8 +3129,15 @@@ my_hash_sort_ucs2_nopad(CHARSET_INFO *c } +static void my_hash_sort_ucs2(CHARSET_INFO *cs, const uchar *s, size_t slen, + ulong *nr1, ulong *nr2) +{ + size_t lengthsp= my_lengthsp_mb2(cs, (const char *) s, slen); + my_hash_sort_ucs2_nopad(cs, s, lengthsp, nr1, nr2); +} + - static size_t my_casedn_ucs2(CHARSET_INFO *cs, char *src, size_t srclen, - char *dst __attribute__((unused)), - size_t dstlen __attribute__((unused))) + static size_t my_casedn_ucs2(CHARSET_INFO *cs, const char *src, size_t srclen, + char *dst, size_t dstlen) { my_wc_t wc; int res; diff --cc strings/ctype-utf8.c index a73de37bbd7,8fab93890f4..c095cc7d74d --- a/strings/ctype-utf8.c +++ b/strings/ctype-utf8.c @@@ -5140,7 -5063,15 +5140,7 @@@ my_toupper_utf8mb3(MY_UNICASE_INFO *uni } - static size_t my_caseup_utf8(CHARSET_INFO *cs, char *src, size_t srclen, -static inline void -my_tosort_utf8mb3(MY_UNICASE_INFO *uni_plane, my_wc_t *wc) -{ - MY_UNICASE_CHARACTER *page; - if ((page= uni_plane->page[(*wc >> 8) & 0xFF])) - *wc= page[*wc & 0xFF].sort; -} - + static size_t my_caseup_utf8(CHARSET_INFO *cs, const char *src, size_t srclen, char *dst, size_t dstlen) { my_wc_t wc;

1 0

[Commits] af46c25760b: MDEV-16727: Server crashes in Item_equal_iterator<List_iterator_fast, Item>::get_curr_field()
by Galina 01 Aug '18

01 Aug '18

revision-id: af46c25760bdc698cf219258f6c42316ed01b924 (mariadb-10.3.6-61-gaf46c25760b) parent(s): b1ae4e7e154b9b1ffb87918a8646173e1f8063fe author: Galina Shalygina committer: Galina Shalygina timestamp: 2018-08-01 14:42:47 +0300 message: MDEV-16727: Server crashes in Item_equal_iterator<List_iterator_fast, Item>::get_curr_field() The bug appeares because of the lamely saved list of multiple equalities. To fix it and_new_conditions_to_optimized_cond() was changed. --- mysql-test/main/in_subq_cond_pushdown.result | 24 +++++++++++++++++++++++ mysql-test/main/in_subq_cond_pushdown.test | 29 ++++++++++++++++++++++++++++ sql/opt_subselect.cc | 2 +- 3 files changed, 54 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/in_subq_cond_pushdown.result b/mysql-test/main/in_subq_cond_pushdown.result index 4ad4180ff52..c9319a5fb86 100644 --- a/mysql-test/main/in_subq_cond_pushdown.result +++ b/mysql-test/main/in_subq_cond_pushdown.result @@ -3831,3 +3831,27 @@ WHERE (t1.a!=1) a 2 DROP TABLE t1; +# +# MDEV-16727: failure assertion caused by the lamely saved list +# of multiple equalities +# +CREATE TABLE t1 (a varchar(1)); +INSERT INTO `t1` VALUES ('x'), ('y'), ('z'); +CREATE TABLE t2 (b varchar(1)); +INSERT INTO t2 VALUES ('x'); +CREATE TABLE t3 (c varchar(1)); +INSERT INTO t3 VALUES ('y'); +CREATE TABLE t4 (d varchar(1)); +INSERT INTO t4 VALUES ('x'), ('z'); +SELECT * FROM t1 +JOIN t2 ON (t1.a=t2.b) +LEFT JOIN t3 ON (t1.a=t3.c) +WHERE (t1.a) IN +( +SELECT t4.d +FROM t4 +ORDER BY t4.d +); +a b c +x x NULL +DROP TABLE t1,t2,t3,t4; diff --git a/mysql-test/main/in_subq_cond_pushdown.test b/mysql-test/main/in_subq_cond_pushdown.test index 8f911ea2f19..3c673fb128b 100644 --- a/mysql-test/main/in_subq_cond_pushdown.test +++ b/mysql-test/main/in_subq_cond_pushdown.test @@ -792,3 +792,32 @@ WHERE tbl.a IN ); DROP TABLE t1; + +--echo # +--echo # MDEV-16727: failure assertion caused by the lamely saved list +--echo # of multiple equalities +--echo # + +CREATE TABLE t1 (a varchar(1)); +INSERT INTO `t1` VALUES ('x'), ('y'), ('z'); + +CREATE TABLE t2 (b varchar(1)); +INSERT INTO t2 VALUES ('x'); + +CREATE TABLE t3 (c varchar(1)); +INSERT INTO t3 VALUES ('y'); + +CREATE TABLE t4 (d varchar(1)); +INSERT INTO t4 VALUES ('x'), ('z'); + +SELECT * FROM t1 +JOIN t2 ON (t1.a=t2.b) +LEFT JOIN t3 ON (t1.a=t3.c) +WHERE (t1.a) IN +( + SELECT t4.d + FROM t4 + ORDER BY t4.d +); + +DROP TABLE t1,t2,t3,t4; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 1b3e2973133..06358b3cc20 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5624,7 +5624,7 @@ Item *and_new_conditions_to_optimized_cond(THD *thd, Item *cond, if (equality->fix_fields(thd, NULL)) return NULL; } - *cond_eq= &new_cond_equal; + (*cond_eq)->copy(new_cond_equal); } new_conds_list.append((List<Item> *)&new_cond_equal.current_level); }

1 0

[Commits] 6b2a7653d2b: MDEV-16765: Missing rows with pushdown condition defined with CASE using Item_cond
by Galina 31 Jul '18

31 Jul '18

revision-id: 6b2a7653d2b9a580ddc27f00461e7e6fb351b1bc (mariadb-10.2.16-53-g6b2a7653d2b) parent(s): f4eac2deeb046a68e4a26ed1a08f8df51bbf817b author: Galina Shalygina committer: Galina Shalygina timestamp: 2018-07-31 20:23:49 +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