- commits - lists.mariadb.org

[Commits] 7f24e37: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY
by IgorBabaev 22 Jun '21

22 Jun '21

revision-id: 7f24e37fbecd5af08ec473a7a1449f305977c6d7 (mariadb-10.4.11-659-g7f24e37) parent(s): ce868cd89e352f7ce04d8db260f96893df2bf26c author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-22 12:23:13 -0700 message: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY Cherry-picking only test case. --- mysql-test/main/derived_view.result | 21 +++++++++++++++++++++ mysql-test/main/derived_view.test | 17 +++++++++++++++++ 2 files changed, 38 insertions(+) diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 00940d8..f761a9f 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -3480,3 +3480,24 @@ Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,3 AS `d`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`a` = 3 and `test`.`t1`.`pk` <= 2 drop view v1; drop table t1,t2,t3; +# +# MDEV-25679: view / derived table defined as ordered select with LIMIT +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +a +7 +3 +select * from v1; +a +7 +3 +select * from ((select a from t1 limit 2) order by a desc) dt; +a +3 +7 +drop view v1; +drop table t1; +# End of 10.2 tests diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test index 76b15fa..9ab1ddd 100644 --- a/mysql-test/main/derived_view.test +++ b/mysql-test/main/derived_view.test @@ -2273,3 +2273,20 @@ eval explain extended $q; drop view v1; drop table t1,t2,t3; + +--echo # +--echo # MDEV-25679: view / derived table defined as ordered select with LIMIT +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +select * from v1; +select * from ((select a from t1 limit 2) order by a desc) dt; + +drop view v1; +drop table t1; + +--echo # End of 10.2 tests

1 0

[Commits] 883d9a6: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY
by IgorBabaev 22 Jun '21

22 Jun '21

revision-id: 883d9a66ccdef5b32876d24377d327b61e1a12c0 (mariadb-10.4.11-659-g883d9a6) parent(s): ce868cd89e352f7ce04d8db260f96893df2bf26c author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-22 10:58:06 -0700 message: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY This bug affected queries with views / derived_tables / CTEs whose specifications were of the form (SELECT ... LIMIT <n>) ORDER BY ... Units representing such specifications contains one SELECT_LEX structure for (SELECT ... LIMIT <n>) and additionally SELECT_LEX structure for fake_select_lex. This fact should have been taken into account in the function mysql_derived_fill(). This patch has to be applied to 10.2 and 10.3 only. --- mysql-test/main/derived_view.result | 21 +++++++++++++++++++++ mysql-test/main/derived_view.test | 17 +++++++++++++++++ sql/sql_derived.cc | 2 +- 3 files changed, 39 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 00940d8..040f291 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -3480,3 +3480,24 @@ Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,3 AS `d`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`a` = 3 and `test`.`t1`.`pk` <= 2 drop view v1; drop table t1,t2,t3; +# +# MDEV-25679: view / derived table defined as ordered select with LIMIT +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +a +7 +3 +select * from v1; +a +7 +3 +select * from ((select a from t1 limit 2) order by a desc) dt; +a +7 +3 +drop view v1; +drop table t1; +# End of 10.2 tests diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test index 76b15fa..9ab1ddd 100644 --- a/mysql-test/main/derived_view.test +++ b/mysql-test/main/derived_view.test @@ -2273,3 +2273,20 @@ eval explain extended $q; drop view v1; drop table t1,t2,t3; + +--echo # +--echo # MDEV-25679: view / derived table defined as ordered select with LIMIT +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +select * from v1; +select * from ((select a from t1 limit 2) order by a desc) dt; + +drop view v1; +drop table t1; + +--echo # End of 10.2 tests diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index fc01dcd..8e6c8db 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1262,7 +1262,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) res= derived->fill_recursive(thd); } } - else if (unit->is_unit_op()) + else if (unit->is_unit_op() || unit->fake_select_lex) { // execute union without clean up res= unit->exec();

1 0

[Commits] 6e94ef4: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY
by IgorBabaev 22 Jun '21

22 Jun '21

revision-id: 6e94ef41859cc628f92224e33dd661d9a4be215b (mariadb-10.3.26-183-g6e94ef4) parent(s): 9dc50ea2293640f8153bc806612fd72634498010 author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-21 22:25:37 -0700 message: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY This bug affected queries with views / derived_tables / CTEs whose specifications were of the form (SELECT ... LIMIT <n>) ORDER BY ... Units representing such specifications contains one SELECT_LEX structure for (SELECT ... LIMIT <n>) and additionally SELECT_LEX structure for fake_select_lex. This fact should have been taken into account in the function mysql_derived_fill(). This patch has to be applied to 10.2 and 10.3 only. --- mysql-test/main/derived_view.result | 21 +++++++++++++++++++++ mysql-test/main/derived_view.test | 17 +++++++++++++++++ sql/sql_derived.cc | 2 +- 3 files changed, 39 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index dd0cac7..65a1adc 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -3478,3 +3478,24 @@ Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,3 AS `d`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`a` = 3 and `test`.`t1`.`pk` <= 2 drop view v1; drop table t1,t2,t3; +# +# MDEV-25679: view / derived table defined as ordered select with LIMIT +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +a +7 +3 +select * from v1; +a +7 +3 +select * from ((select a from t1 limit 2) order by a desc) dt; +a +7 +3 +drop view v1; +drop table t1; +# End of 10.2 tests diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test index 76b15fa..9ab1ddd 100644 --- a/mysql-test/main/derived_view.test +++ b/mysql-test/main/derived_view.test @@ -2273,3 +2273,20 @@ eval explain extended $q; drop view v1; drop table t1,t2,t3; + +--echo # +--echo # MDEV-25679: view / derived table defined as ordered select with LIMIT +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +select * from v1; +select * from ((select a from t1 limit 2) order by a desc) dt; + +drop view v1; +drop table t1; + +--echo # End of 10.2 tests diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index e2e30f3..8f10013 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1163,7 +1163,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) res= derived->fill_recursive(thd); } } - else if (unit->is_unit_op()) + else if (unit->is_unit_op() || unit->fake_select_lex) { // execute union without clean up res= unit->exec();

1 0

[Commits] d4a7781: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY
by IgorBabaev 22 Jun '21

22 Jun '21

revision-id: d4a7781e43b251a59a5bd2aa13132f8b8abdfd5d (mariadb-10.2.31-997-gd4a7781) parent(s): dfa2d0bc13362b949b1b1699955583f74e7db90a author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-21 16:15:07 -0700 message: MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY This bug affected queries with views / derived_tables / CTEs whose specifications were of the form (SELECT ... LIMIT <n>) ORDER BY ... Units representing such specifications contains one SELECT_LEX structure for (SELECT ... LIMIT <n>) and additionally SELECT_LEX structure for fake_select_lex. This fact should have been taken into account in the function mysql_derived_fill(). This patch has to be applied to 10.2 and 10.3 only. --- mysql-test/r/derived_view.result | 21 +++++++++++++++++++++ mysql-test/t/derived_view.test | 17 +++++++++++++++++ sql/sql_derived.cc | 2 +- 3 files changed, 39 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index fe25feb..d8ee508 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -3436,3 +3436,24 @@ Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,3 AS `d`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`a` = 3 and `test`.`t1`.`pk` <= 2 drop view v1; drop table t1,t2,t3; +# +# MDEV-25679: view / derived table defined as ordered select with LIMIT +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +a +7 +3 +select * from v1; +a +7 +3 +select * from ((select a from t1 limit 2) order by a desc) dt; +a +7 +3 +drop view v1; +drop table t1; +# End of 10.2 tests diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 46d6a78..89ada40 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -2249,3 +2249,20 @@ eval explain extended $q; drop view v1; drop table t1,t2,t3; + +--echo # +--echo # MDEV-25679: view / derived table defined as ordered select with LIMIT +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +create view v1 as (select a from t1 limit 2) order by a desc; +(select a from t1 limit 2) order by a desc; +select * from v1; +select * from ((select a from t1 limit 2) order by a desc) dt; + +drop view v1; +drop table t1; + +--echo # End of 10.2 tests diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 3ab9384..632baf4 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1091,7 +1091,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) res= derived->fill_recursive(thd); } } - else if (unit->is_union()) + else if (unit->is_union() || unit->fake_select_lex) { // execute union without clean up res= unit->exec();

1 0

[Commits] e8888cf9f8d: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP
by Sergei Petrunia 21 Jun '21

21 Jun '21

revision-id: e8888cf9f8de304e1d7e6253cff8a3def2f4d379 (mariadb-10.2.31-962-ge8888cf9f8d) parent(s): f3dd96ad25efe23081981f52a54a57b17a5a890e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-21 01:06:00 +0300 message: MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP Consider a query in form: select ... from (select item2 as COL1) as T where COL1=123 Condition pushdown into derived table will try to push "COL1=123" condition down into table T. The process of pushdown involves "substituting" the item, that is, replacing Item_field("T.COL1") with its "producing item" item2. In order to use item2, one needs to clone it (call Item::build_clone). If the item is not cloneable (e.g. SP function call is not), the pushdown process will fail and nothing at all will be pushed. This patch makes pushdown_cond_for_derived() to first extract the portion of a condition which can be pushed, and then push only that. Included changes: - TABLE_LIST::check_pushable_cond_for_table() can now accept the criteria that item must meet in order to be pushable. It is used in two places: first, extract the condition that only depends on a given table, and then, extract the condition that's pushable into a given SELECT. - TABLE_LIST::build_pushable_cond_for_table() works the same as before but it is no longer a member of TABLE_LIST for the sake of symmetry with check_pushable_cond_for_table. --- mysql-test/r/derived_cond_pushdown.result | 45 +++++++++++++++++++++++++++++ mysql-test/t/derived_cond_pushdown.test | 48 +++++++++++++++++++++++++++++++ sql/item.cc | 24 ++++++++++++++++ sql/item.h | 8 ++++++ sql/item_cmpfunc.h | 3 ++ sql/item_func.h | 1 + sql/sql_derived.cc | 15 ++++++++-- sql/table.cc | 37 +++++++++++++----------- sql/table.h | 7 +++-- 9 files changed, 167 insertions(+), 21 deletions(-) diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 25237aa11a9..bae971ced74 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -10634,4 +10634,49 @@ m 7 drop view v1; drop table t1; +# +# MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP +# +create function f1(a int) returns int DETERMINISTIC return (a+1); +create table t1 ( +pk int primary key, +a int, +b int, +key(a) +); +create table t2(a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t3(a int); +insert into t3 select A.a + B.a* 10 + C.a * 100 from t2 A, t2 B, t2 C; +insert into t1 select a,a,a from t3; +create view v1 as +select +t1.a as col1, +f1(t1.b) as col2 +from +t1; +create view v2 as +select +t1.a as col1, +f1(t1.b) as col2 +from +t1; +create view v3 as +select col2, col1 from v1 +union all +select col2, col1 from v2; +explain select * from v3 where col1=123; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t1 ref a a 5 const 1 +3 UNION t1 ref a a 5 const 1 +# This must use ref accesses for reading table t1, not full scans: +explain select * from v3 where col1=123 and col2=321; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED t1 ref a a 5 const 1 +3 UNION t1 ref a a 5 const 1 +drop function f1; +drop view v1,v2,v3; +drop table t1, t2,t3; # End of 10.2 tests diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test index 31b49047bf1..374b61f846a 100644 --- a/mysql-test/t/derived_cond_pushdown.test +++ b/mysql-test/t/derived_cond_pushdown.test @@ -2212,4 +2212,52 @@ select * from v1 where m > 0; drop view v1; drop table t1; +--echo # +--echo # MDEV-25969: Condition pushdown into derived table doesn't work if select list uses SP +--echo # +create function f1(a int) returns int DETERMINISTIC return (a+1); + +create table t1 ( + pk int primary key, + a int, + b int, + key(a) +); + +create table t2(a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t3(a int); +insert into t3 select A.a + B.a* 10 + C.a * 100 from t2 A, t2 B, t2 C; + +insert into t1 select a,a,a from t3; + +create view v1 as +select + t1.a as col1, + f1(t1.b) as col2 +from + t1; + +create view v2 as +select + t1.a as col1, + f1(t1.b) as col2 +from + t1; +create view v3 as +select col2, col1 from v1 +union all +select col2, col1 from v2; + +explain select * from v3 where col1=123; + +--echo # This must use ref accesses for reading table t1, not full scans: +explain select * from v3 where col1=123 and col2=321; + +drop function f1; +drop view v1,v2,v3; +drop table t1, t2,t3; + + --echo # End of 10.2 tests diff --git a/sql/item.cc b/sql/item.cc index 42272fe0148..d729da23daa 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -7249,6 +7249,30 @@ Item *find_producing_item(Item *item, st_select_lex *sel) return NULL; } + +/* + @brief Check if this item cannot be pushed down into derived table + + @detail + This function checks if derived_field_transformer_for_where() will + fail. It will fail if the "producing item" (column in the derived table) + cannot be cloned. + + @return + false - Ok, can be pushed + true - Cannot be pushed +*/ + +bool Item_field::check_non_pushable_processor(void *arg) +{ + st_select_lex *sel= (st_select_lex *)arg; + Item *producing_item= find_producing_item(this, sel); + if (producing_item) + return producing_item->walk(&Item::check_non_cloneable_processor, 0, 0); + return false; // Ok +} + + Item *Item_field::derived_field_transformer_for_where(THD *thd, uchar *arg) { st_select_lex *sel= (st_select_lex *)arg; diff --git a/sql/item.h b/sql/item.h index c94709c733e..f0753f7ded1 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1829,6 +1829,12 @@ class Item: public Value_source, */ virtual bool check_valid_arguments_processor(void *arg) { return 0; } virtual bool update_vcol_processor(void *arg) { return 0; } + + /* Return true if the item can NOT be pushed down into a derived table */ + virtual bool check_non_pushable_processor(void *arg) { return 0; } + + /* Return true if the item cannot be cloned (get_copy() will return NULL) */ + virtual bool check_non_cloneable_processor(void *arg) { return 0; } /*============== End of Item processor list ======================*/ virtual Item *get_copy(THD *thd, MEM_ROOT *mem_root)=0; @@ -2894,6 +2900,8 @@ class Item_field :public Item_ident, friend class Item_default_value; friend class Item_insert_value; friend class st_select_lex_unit; + + bool check_non_pushable_processor(void *arg) override; }; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 26469d88929..ce27989bb16 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2135,6 +2135,7 @@ class Item_func_regex :public Item_bool_func const char *func_name() const { return "regexp"; } enum precedence precedence() const { return IN_PRECEDENCE; } Item *get_copy(THD *thd, MEM_ROOT *mem_root) { return 0; } + bool check_non_cloneable_processor(void *arg) { return true; } void print(String *str, enum_query_type query_type) { print_op(str, query_type); @@ -2163,6 +2164,7 @@ class Item_func_regexp_instr :public Item_int_func bool fix_length_and_dec(); const char *func_name() const { return "regexp_instr"; } Item *get_copy(THD *thd, MEM_ROOT *mem_root) { return 0; } + bool check_non_cloneable_processor(void *arg) { return true; } }; @@ -2421,6 +2423,7 @@ class Item_equal: public Item_bool_func void set_context_field(Item_field *ctx_field) { context_field= ctx_field; } void set_link_equal_fields(bool flag) { link_equal_fields= flag; } Item* get_copy(THD *thd, MEM_ROOT *mem_root) { return 0; } + bool check_non_cloneable_processor(void *arg) override { return true; } /* This does not comply with the specification of the virtual method, but Item_equal items are processed distinguishly anyway diff --git a/sql/item_func.h b/sql/item_func.h index 496109b0e24..5dfcdb956cf 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -2480,6 +2480,7 @@ class Item_func_sp :public Item_func return TRUE; } Item *get_copy(THD *thd, MEM_ROOT *mem_root) { return 0; } + bool check_non_cloneable_processor(void *arg) override { return true; } bool eval_not_null_tables(void *opt_arg) { not_null_tables_cache= 0; diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 3ab93840d80..468e7c326aa 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1261,8 +1261,12 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) This condition has to be fixed yet. */ Item *extracted_cond; - derived->check_pushable_cond_for_table(cond); - extracted_cond= derived->build_pushable_cond_for_table(thd, cond); + table_map derived_tbl_bit= derived->table->map; + check_pushable_cond_for_table(cond, + [=](Item *cond) { + return cond->excl_dep_on_table(derived_tbl_bit); + }); + extracted_cond= build_pushable_cond_for_table(thd, derived_tbl_bit, cond); if (!extracted_cond) { /* Nothing can be pushed into the derived table */ @@ -1287,6 +1291,13 @@ bool pushdown_cond_for_derived(THD *thd, Item *cond, TABLE_LIST *derived) if (!sl->join->group_list && !sl->with_sum_func) { /* extracted_cond_copy is pushed into where of sl */ + check_pushable_cond_for_table(extracted_cond_copy, + [=](Item *cond) { + return !cond->walk(&Item::check_non_pushable_processor, + false, (void*)sl); + }); + extracted_cond_copy= build_pushable_cond_for_table(thd, derived_tbl_bit, + extracted_cond_copy); extracted_cond_copy= extracted_cond_copy->transform(thd, &Item::derived_field_transformer_for_where, (uchar*) sl); diff --git a/sql/table.cc b/sql/table.cc index 1004f583448..c2206b55184 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -8438,19 +8438,19 @@ double KEY::actual_rec_per_key(uint i) @param cond The condition whose subformulas are to be marked @details - This method recursively traverses the AND-OR condition cond and for each subformula - of the codition it checks whether it can be usable for the extraction of a condition + Recursively traverse the AND-OR condition cond and for each subformula + of the codition check whether it can be usable for the extraction of a condition that can be pushed into this table. The subformulas that are not usable are marked with the flag NO_EXTRACTION_FL. @note - This method is called before any call of TABLE_LIST::build_pushable_cond_for_table. + This function is called before any call of build_pushable_cond_for_table(). The flag NO_EXTRACTION_FL set in a subformula allows to avoid building clone for the subformula when extracting the pushable condition. */ -void TABLE_LIST::check_pushable_cond_for_table(Item *cond) +void check_pushable_cond_for_table(Item *cond, + std::function<bool(Item*)> pushable_filter) { - table_map tab_map= table->map; cond->clear_extraction_flag(); if (cond->type() == Item::COND_ITEM) { @@ -8460,7 +8460,7 @@ void TABLE_LIST::check_pushable_cond_for_table(Item *cond) Item *item; while ((item=li++)) { - check_pushable_cond_for_table(item); + check_pushable_cond_for_table(item, pushable_filter); if (item->get_extraction_flag() != NO_EXTRACTION_FL) count++; else if (!and_cond) @@ -8475,25 +8475,29 @@ void TABLE_LIST::check_pushable_cond_for_table(Item *cond) item->clear_extraction_flag(); } } - else if (!cond->excl_dep_on_table(tab_map)) - cond->set_extraction_flag(NO_EXTRACTION_FL); + else + { + if (!pushable_filter(cond)) + cond->set_extraction_flag(NO_EXTRACTION_FL); + } } /** @brief - Build condition extractable from the given one depended only on this table + Build condition extractable from the given one depended only on the table - @param thd The thread handle - @param cond The condition from which the pushable one is to be extracted - + @param thd The thread handle + @param tab_map Table the condition must depend on. + @param cond The condition from which the pushable one is to be extracted + @details For the given condition cond this method finds out what condition depended only on this table can be extracted from cond. If such condition C exists the method builds the item for it. The method uses the flag NO_EXTRACTION_FL set by the preliminary call of - the method TABLE_LIST::check_pushable_cond_for_table to figure out whether - a subformula depends only on this table or not. + check_pushable_cond_for_table() to figure out whether a subformula depends + only on this table or not. @note The built condition C is always implied by the condition cond (cond => C). The method tries to build the most restictive such @@ -8508,9 +8512,8 @@ void TABLE_LIST::check_pushable_cond_for_table(Item *cond) NULL if there is no such a condition */ -Item* TABLE_LIST::build_pushable_cond_for_table(THD *thd, Item *cond) +Item* build_pushable_cond_for_table(THD *thd, table_map tab_map, Item *cond) { - table_map tab_map= table->map; bool is_multiple_equality= cond->type() == Item::FUNC_ITEM && ((Item_func*) cond)->functype() == Item_func::MULT_EQUAL_FUNC; if (cond->get_extraction_flag() == NO_EXTRACTION_FL) @@ -8539,7 +8542,7 @@ Item* TABLE_LIST::build_pushable_cond_for_table(THD *thd, Item *cond) return 0; continue; } - Item *fix= build_pushable_cond_for_table(thd, item); + Item *fix= build_pushable_cond_for_table(thd, tab_map, item); if (!fix && !cond_and) return 0; if (!fix) diff --git a/sql/table.h b/sql/table.h index 83c72f76831..ff80bda5b6e 100644 --- a/sql/table.h +++ b/sql/table.h @@ -16,6 +16,7 @@ along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */ +#include <functional> #include "my_global.h" /* NO_EMBEDDED_ACCESS_CHECKS */ #include "sql_plist.h" #include "sql_list.h" /* Sql_alloc */ @@ -2534,8 +2535,6 @@ struct TABLE_LIST return false; } void set_lock_type(THD* thd, enum thr_lock_type lock); - void check_pushable_cond_for_table(Item *cond); - Item *build_pushable_cond_for_table(THD *thd, Item *cond); void remove_join_columns() { @@ -2562,6 +2561,10 @@ struct TABLE_LIST ulong m_table_ref_version; }; +Item *build_pushable_cond_for_table(THD *thd, table_map tab_map, Item *cond); +void check_pushable_cond_for_table(Item *cond, + std::function<bool(Item*)> pushable_filter); + class Item; /*

1 0

[Commits] 45dd56e287f: Probably fix
by psergey 17 Jun '21

17 Jun '21

revision-id: 45dd56e287ff2ab7397ca00da53ccd5768bee43f (mariadb-10.5.2-1038-g45dd56e287f) parent(s): e801d6fd0075915e58999d7d48251b3f91674c8e author: Oleksandr Byelkin committer: Sergei Petrunia timestamp: 2021-06-18 00:43:45 +0300 message: Probably fix --- mysql-test/main/alias_in_where.result | 59 +++++++++++++++++++++++++++++++++-- mysql-test/main/alias_in_where.test | 32 +++++++++++++++++++ sql/item.cc | 6 ++-- sql/item.h | 1 + sql/sql_select.cc | 13 ++++++-- 5 files changed, 104 insertions(+), 7 deletions(-) diff --git a/mysql-test/main/alias_in_where.result b/mysql-test/main/alias_in_where.result index b66b3ed169a..1fb7d33e14f 100644 --- a/mysql-test/main/alias_in_where.result +++ b/mysql-test/main/alias_in_where.result @@ -383,7 +383,7 @@ set sql_mode="ALIASES_IN_WHERE"; create view v1 as select a, (select t1.a from t1 limit 1) as cc from t1; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,(select `t__1__0__`.`a` from `t1` `t__1__0__` limit 1) AS `cc` from `t1` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `v1`.`a` AS `a`,(select `t__1__0__`.`a` from `t1` `t__1__0__` limit 1) AS `cc` from `t1` latin1 latin1_swedish_ci select * from v1; a cc 1 1 @@ -404,7 +404,7 @@ insert into t1 values (1,1), (2,2), (10, 10); create view v1 as select a+1 as d, (select b from t1 where d=a) as cc from t1; show create view v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` + 1 AS `d`,(select `t__1__0__`.`b` from `t1` `t__1__0__` where `t1`.`a` + 1 = `t__1__0__`.`a`) AS `cc` from `t1` latin1 latin1_swedish_ci +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `a` + 1 AS `d`,(select `t__1__0__`.`b` from `t1` `t__1__0__` where `a` + 1 = `t__1__0__`.`a`) AS `cc` from `t1` latin1 latin1_swedish_ci select * from v1; d cc 2 2 @@ -424,4 +424,59 @@ d cc set sql_mode="ALIASES_IN_WHERE"; drop view v1; drop table t1; +# +# outer reference as part of GROUP BY +# +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t +where t1.a < 2) group by c; +c d +1 1 +# shoud be the same as above +select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t where c < 2) group by c; +c d +1 1 +prepare stmt from "select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t where c < 2) group by c"; +create view v1 as select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t where c < 2) group by c; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `c`,min(`t1`.`b`) AS `d` from `t1` where 5 < (select max(`t`.`b`) from `t1` `t` where `t1`.`a` < 2) group by `t1`.`a` latin1 latin1_swedish_ci +create procedure p1() +select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t where c < 2) group by c; +select * from v1; +c d +1 1 +execute stmt; +c d +1 1 +execute stmt; +c d +1 1 +call p1; +c d +1 1 +call p1; +c d +1 1 +set sql_mode=""; +select * from v1; +c d +1 1 +execute stmt; +c d +1 1 +execute stmt; +c d +1 1 +call p1; +c d +1 1 +call p1; +c d +1 1 +drop procedure p1; +drop view v1; +drop table t1; set @@sql_mode= @save_sql_mode; diff --git a/mysql-test/main/alias_in_where.test b/mysql-test/main/alias_in_where.test index 7638e96ecc3..4eb626f60e1 100644 --- a/mysql-test/main/alias_in_where.test +++ b/mysql-test/main/alias_in_where.test @@ -295,4 +295,36 @@ set sql_mode="ALIASES_IN_WHERE"; drop view v1; drop table t1; + +--echo # +--echo # outer reference as part of GROUP BY +--echo # +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t +where t1.a < 2) group by c; +--echo # shoud be the same as above +select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t where c < 2) group by c; + +prepare stmt from "select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t where c < 2) group by c"; +create view v1 as select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t where c < 2) group by c; +show create view v1; +create procedure p1() +select a as c, min(b) as d from t1 where 5 < (select max(t.b) from t1 as t where c < 2) group by c; +select * from v1; +execute stmt; +execute stmt; +call p1; +call p1; +set sql_mode=""; +select * from v1; +execute stmt; +execute stmt; +call p1; +call p1; + +drop procedure p1; +drop view v1; +drop table t1; set @@sql_mode= @save_sql_mode; diff --git a/sql/item.cc b/sql/item.cc index eb3cc17378e..1314ab5cce5 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -8253,11 +8253,11 @@ bool Item_ref_alias::fix_fields(THD *thd, Item **reference) Item_outer_ref *rf= new(thd->mem_root) Item_outer_ref(thd, context, ref, {0, 0}, name, true, true); - rf->base_flags&= ~item_base_t::FIXED; if (!rf) return TRUE; + rf->base_flags&= ~item_base_t::FIXED; thd->change_item_tree(reference, rf); - context->select_lex->inner_refs_list.push_back(rf, thd->mem_root); + outer_finding->select_lex->inner_refs_list.push_back(rf, thd->mem_root); rf->in_sum_func= thd->lex->in_sum_func; mark_as_dependent(thd, outer_finding->select_lex, context->select_lex, @@ -9266,7 +9266,7 @@ bool Item_outer_ref::fix_fields(THD *thd, Item **reference) err= Item_direct_ref::fix_fields(thd, reference); if (!outer_ref) outer_ref= *ref; - if ((*ref)->type() == Item::FIELD_ITEM) + if ((*ref)->type() == Item::FIELD_ITEM && !outer_alias) table_name= ((Item_field*)outer_ref)->table_name; return err; } diff --git a/sql/item.h b/sql/item.h index 04de60460db..3411c88295b 100644 --- a/sql/item.h +++ b/sql/item.h @@ -6273,6 +6273,7 @@ class Item_outer_ref :public Item_direct_ref else Item_direct_ref::print(str, query_type); } + bool is_outer_alias() { return outer_alias; } }; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0f7fa6a81e9..ed0144d40b2 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -649,8 +649,17 @@ fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select, for (ORDER *group= select->join->group_list; group; group= group->next) { (*group->item)->walk(&Item::check_inner_refs_processor, TRUE, &ref_it); - } - + while ((ref= ref_it++)) + { + if ((*group->item) == *(ref->ref)) + { + //DBUG_ASSERT(ref->is_outer_alias() || ref->found_in_group_by); + ref->found_in_group_by= TRUE; + } + } + ref_it.rewind(); + } + while ((ref= ref_it++)) { bool direct_ref= false;

1 0

[Commits] e801d6fd007: MENT-1062 : add outer reference in VIEWs.
by psergey 17 Jun '21

17 Jun '21

revision-id: e801d6fd0075915e58999d7d48251b3f91674c8e (mariadb-10.5.2-1037-ge801d6fd007) parent(s): 7172ff780bd6c1d131d6134c4f92ae71604a0b1c author: Oleksandr Byelkin committer: Sergei Petrunia timestamp: 2021-06-18 00:43:34 +0300 message: MENT-1062 : add outer reference in VIEWs. CONTEXT_ANALYSIS_ONLY_VIEW devided on two: really view and others (mostly PS) Potential tables aliases found and fixed outer references printed as an expression --- mysql-test/main/alias_in_where.result | 50 +++++++++++++++++++++++++++++++ mysql-test/main/alias_in_where.test | 30 +++++++++++++++++++ sql/item.cc | 6 ++-- sql/item.h | 30 ++++++++++++++++++- sql/item_func.cc | 2 +- sql/mysqld.h | 3 +- sql/sp_rcontext.cc | 4 +-- sql/sql_base.cc | 43 ++++++++++++++++++++++++++- sql/sql_base.h | 3 +- sql/sql_lex.cc | 55 +++++++++++++++++++++++++++++++++++ sql/sql_lex.h | 11 +++++-- sql/sql_prepare.cc | 2 +- sql/sql_priv.h | 12 ++++---- sql/sql_select.cc | 2 +- sql/sql_show.cc | 4 +-- sql/sql_tvc.cc | 2 +- sql/sql_union.cc | 10 +++---- sql/sql_view.cc | 2 +- sql/table.cc | 6 ++-- 19 files changed, 247 insertions(+), 30 deletions(-) diff --git a/mysql-test/main/alias_in_where.result b/mysql-test/main/alias_in_where.result index 05094d1d545..b66b3ed169a 100644 --- a/mysql-test/main/alias_in_where.result +++ b/mysql-test/main/alias_in_where.result @@ -374,4 +374,54 @@ a a NULL 5 NULL 10 drop tables t1,t3; +# +# test of possible table alias problem +# +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +create view v1 as select a, (select t1.a from t1 limit 1) as cc from t1; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,(select `t__1__0__`.`a` from `t1` `t__1__0__` limit 1) AS `cc` from `t1` latin1 latin1_swedish_ci +select * from v1; +a cc +1 1 +5 1 +10 1 +select a, (select t1.a from t1 limit 1) as cc from t1; +a cc +1 1 +5 1 +10 1 +drop view v1; +drop table t1; +# +# test of view with outer reference and changing sql_mode +# +create table t1 (a int, b int); +insert into t1 values (1,1), (2,2), (10, 10); +create view v1 as select a+1 as d, (select b from t1 where d=a) as cc from t1; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` + 1 AS `d`,(select `t__1__0__`.`b` from `t1` `t__1__0__` where `t1`.`a` + 1 = `t__1__0__`.`a`) AS `cc` from `t1` latin1 latin1_swedish_ci +select * from v1; +d cc +2 2 +3 NULL +11 NULL +select a+1 as d, (select b from t1 where d=a) as cc from t1; +d cc +2 2 +3 NULL +11 NULL +set sql_mode=""; +select * from v1; +d cc +2 2 +3 NULL +11 NULL +set sql_mode="ALIASES_IN_WHERE"; +drop view v1; +drop table t1; set @@sql_mode= @save_sql_mode; diff --git a/mysql-test/main/alias_in_where.test b/mysql-test/main/alias_in_where.test index 5073446e435..7638e96ecc3 100644 --- a/mysql-test/main/alias_in_where.test +++ b/mysql-test/main/alias_in_where.test @@ -265,4 +265,34 @@ execute stmt; drop tables t1,t3; + +--echo # +--echo # test of possible table alias problem +--echo # + +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +create view v1 as select a, (select t1.a from t1 limit 1) as cc from t1; +show create view v1; +select * from v1; +select a, (select t1.a from t1 limit 1) as cc from t1; +drop view v1; +drop table t1; + +--echo # +--echo # test of view with outer reference and changing sql_mode +--echo # +create table t1 (a int, b int); +insert into t1 values (1,1), (2,2), (10, 10); +create view v1 as select a+1 as d, (select b from t1 where d=a) as cc from t1; +show create view v1; +select * from v1; +select a+1 as d, (select b from t1 where d=a) as cc from t1; +set sql_mode=""; +select * from v1; +set sql_mode="ALIASES_IN_WHERE"; +drop view v1; +drop table t1; + set @@sql_mode= @save_sql_mode; diff --git a/sql/item.cc b/sql/item.cc index 10107d137dd..eb3cc17378e 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -8251,7 +8251,8 @@ bool Item_ref_alias::fix_fields(THD *thd, Item **reference) { // It is outer resolved field Item_outer_ref *rf= new(thd->mem_root) Item_outer_ref(thd, context, ref, - {0, 0}, name, true); + {0, 0}, name, + true, true); rf->base_flags&= ~item_base_t::FIXED; if (!rf) return TRUE; @@ -8412,7 +8413,8 @@ void Item_ref::print(String *str, enum_query_type query_type) if ((*ref)->type() != Item::CACHE_ITEM && (*ref)->type() != Item::WINDOW_FUNC_ITEM && ref_type() != VIEW_REF && - !table_name.str && name.str && alias_name_used) + !table_name.str && name.str && alias_name_used && + !(query_type & QT_FORCE_REF_EXPRESSION)) { THD *thd= current_thd; append_identifier(thd, str, &(*ref)->real_item()->name); diff --git a/sql/item.h b/sql/item.h index 6e0e9f707c3..04de60460db 100644 --- a/sql/item.h +++ b/sql/item.h @@ -33,6 +33,12 @@ C_MODE_START #include <ma_dyncol.h> +struct st_replace_table_processor_param +{ + LEX_CSTRING new_alias; + LEX_CSTRING old_alias; +}; + /* A prototype for a C-compatible structure to store a value of any data type. Currently it has to stay in /sql, as it depends on String and my_decimal. @@ -2158,6 +2164,7 @@ class Item :public Value_source, is_expensive_cache= (int8)(-1); return 0; } + virtual bool replace_table_processor(void *arg) { return 0; } /** Check db/table_name if they defined in item and match arg values @@ -3495,6 +3502,16 @@ class Item_ident :public Item_result_field const char *db_name, const char *table_name, List_iterator<Item> *it, bool any_privileges); + virtual bool replace_table_processor(void *arg) override + { + st_replace_table_processor_param *param= + ((st_replace_table_processor_param *)arg); + DBUG_ASSERT(param->old_alias.length > 0); + if (table_name.length == param->old_alias.length && + strncmp(table_name.str, param->old_alias.str, table_name.length) == 0) + orig_table_name= table_name= param->new_alias; + return 0; + } }; @@ -6202,6 +6219,7 @@ class Item_direct_view_ref :public Item_direct_ref class Item_sum; class Item_outer_ref :public Item_direct_ref { + bool outer_alias; public: Item *outer_ref; /* The aggregate function under which this outer ref is used, if any. */ @@ -6216,6 +6234,7 @@ class Item_outer_ref :public Item_direct_ref Item_field *outer_field_arg): Item_direct_ref(thd, context_arg, 0, outer_field_arg->table_name, outer_field_arg->field_name), + outer_alias(false), outer_ref(outer_field_arg), in_sum_func(0), found_in_select_list(0), found_in_group_by(0) { @@ -6226,9 +6245,10 @@ class Item_outer_ref :public Item_direct_ref } Item_outer_ref(THD *thd, Name_resolution_context *context_arg, Item **item, const LEX_CSTRING &table_name_arg, LEX_CSTRING &field_name_arg, - bool alias_name_used_arg): + bool alias_name_used_arg, bool alias= false): Item_direct_ref(thd, context_arg, item, table_name_arg, field_name_arg, alias_name_used_arg), + outer_alias(alias), outer_ref(0), in_sum_func(0), found_in_select_list(1), found_in_group_by(0) {} void save_in_result_field(bool no_conversions) override @@ -6245,6 +6265,14 @@ class Item_outer_ref :public Item_direct_ref table_map not_null_tables() const override { return 0; } Ref_Type ref_type() override { return OUTER_REF; } bool check_inner_refs_processor(void * arg) override; + void print(String *str, enum_query_type query_type) override + { + if (outer_alias && (query_type & QT_VIEW_INTERNAL)) + (*ref)->print(str, enum_query_type(((uint)query_type) | + QT_FORCE_REF_EXPRESSION)); + else + Item_direct_ref::print(str, query_type); + } }; diff --git a/sql/item_func.cc b/sql/item_func.cc index 965809ff460..129f6cf0eb2 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -6702,7 +6702,7 @@ Item_func_sp::fix_fields(THD *thd, Item **ref) Checking privileges to execute the function while creating view and executing the function of select. */ - if (!(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW) || + if (!(thd->lex->is_view_context_analysis()) || (thd->lex->sql_command == SQLCOM_CREATE_VIEW)) { Security_context *save_security_ctx= thd->security_ctx; diff --git a/sql/mysqld.h b/sql/mysqld.h index 9cb7423f455..ae05eab3c71 100644 --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -870,7 +870,6 @@ enum enum_query_type QT_ITEM_ORIGINAL_FUNC_NULLIF= (1 << 7), /// good for parsing QT_PARSABLE= (1 << 8), - /// This value means focus on readability, not on ability to parse back, etc. QT_EXPLAIN= QT_TO_SYSTEM_CHARSET | QT_ITEM_IDENT_SKIP_DB_NAMES | @@ -895,6 +894,8 @@ enum enum_query_type // it evaluates to. Should be used for error messages, so that they // don't reveal values. QT_NO_DATA_EXPANSION= (1 << 9), + // Force expression instead of aliases + QT_FORCE_REF_EXPRESSION= (1 << 12) }; diff --git a/sql/sp_rcontext.cc b/sql/sp_rcontext.cc index c4c19dd39f6..c2cedad1cf9 100644 --- a/sql/sp_rcontext.cc +++ b/sql/sp_rcontext.cc @@ -238,7 +238,7 @@ bool Qualified_column_ident::resolve_type_ref(THD *thd, Column_definition *def) sp_lex_local lex(thd, thd->lex); thd->lex= &lex; - lex.context_analysis_only= CONTEXT_ANALYSIS_ONLY_VIEW; + lex.context_analysis_only= CONTEXT_ANALYSIS_ONLY_PS; // Make %TYPE variables see temporary tables that shadow permanent tables thd->temporary_tables= open_tables_state_backup.temporary_tables; @@ -297,7 +297,7 @@ bool Table_ident::resolve_table_rowtype_ref(THD *thd, sp_lex_local lex(thd, thd->lex); thd->lex= &lex; - lex.context_analysis_only= CONTEXT_ANALYSIS_ONLY_VIEW; + lex.context_analysis_only= CONTEXT_ANALYSIS_ONLY_PS; // Make %ROWTYPE variables see temporary tables that shadow permanent tables thd->temporary_tables= open_tables_state_backup.temporary_tables; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 62c7af44256..b72606fbc6c 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -7656,13 +7656,54 @@ static enum check_dep_loops_res check_dep_loops(THD* thd, return LOOP_OK; } +static bool check_and_fix_table_aliases(THD *thd, + Name_resolution_context *context) +{ + for (TABLE_LIST *t= context->first_name_resolution_table; + t; + t= t->next_local) + { + /* + checked by add_table_to_list + if (context->select_lex->find_table_alias_in_the_from_clause(t)) + { + my_error(ER_NONUNIQ_TABLE, MYF(0), t->alias.str); + return true; + } + */ + Name_resolution_context *c= context->outer_context; + for(; c; c= c->outer_context) + { + if (c->select_lex->find_table_alias_in_the_from_clause(t)) + { + // possible alias conflicts + if (context->select_lex->change_to_unique_table_alias(t)) + return true; + break; // it will be unique, there is no sens to continue this check + } + } + } + return false; +} + bool pre_setup_aliases(THD *thd, List<Item> &fields, Item **conds, - Item ***current_select_list_fix) + Name_resolution_context *context, + Item ***current_select_list_fix) { List_iterator<Item> it(fields); uint num= fields.elements; DBUG_ENTER("pre_setup_aliases"); + /* + If it is CREATE VIEW we have to check and fix conflicting table aliases + on this level (before fix_field on this level) and on this level only + (compatibility for other levels is already checked before) + */ + if ((thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW_REAL) && + check_and_fix_table_aliases(thd, context)) + DBUG_RETURN(1); + + // Process SELECT-list Cyclic_dep_check *ctrl= (Cyclic_dep_check *) diff --git a/sql/sql_base.h b/sql/sql_base.h index 31a64a63d2d..05b9807cad1 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -186,7 +186,8 @@ bool setup_fields(THD *thd, Ref_ptr_array ref_pointer_array, List<Item> &item, enum_column_usage column_usage, List<Item> *sum_func_list, List<Item> *pre_fix, bool allow_sum_func, Item ***progress_pointer= NULL); -bool pre_setup_aliases(THD*, List<Item> &, Item **, Item ***); +bool pre_setup_aliases(THD*, List<Item> &, Item**, + Name_resolution_context *, Item ***); void unfix_fields(List<Item> &items); bool fill_record(THD * thd, TABLE *table_arg, List<Item> &fields, List<Item> &values, bool ignore_errors, bool update); diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 53bf876eb75..821b597b3de 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -36,6 +36,7 @@ #include "sql_partition.h" #include "sql_partition_admin.h" // Sql_cmd_alter_table_*_part #include "event_parse_data.h" +#include <sql_string.h> // stringcmp void LEX::parse_error(uint err_number) { @@ -10416,6 +10417,60 @@ bool SELECT_LEX::make_unique_derived_name(THD *thd, LEX_CSTRING *alias) } +bool SELECT_LEX::find_table_alias_in_the_from_clause(TABLE_LIST *table) +{ + for(TABLE_LIST *t=get_table_list(); t; t= t->next_local) + { + if (table->alias.length == t->alias.length && + table != t && + strncmp(table->alias.str, t->alias.str, table->alias.length) == 0) + return TRUE; + } + return FALSE; +} + + +bool SELECT_LEX::change_to_unique_table_alias(TABLE_LIST *table) +{ + char buffer[1+2*3+2*11+1]; + + uint cnt= 0; + for (TABLE_LIST *t=get_table_list(); t && t != table; t= t->next_local) + cnt++; + int len= my_snprintf(buffer, sizeof(buffer) - 1, + "t__%d__%u__", nest_level, cnt); + DBUG_ASSERT(len >= 1+2*3+2); + + st_replace_table_processor_param param; + param.old_alias= table->alias; + table->alias.length= (size_t) len; + if (!(table->alias.str= strmake_root(parent_lex->thd->stmt_arena->mem_root, + buffer, len))) + return true; + table->is_alias= TRUE; + if (table->table) + { + table->table->alias_name_used= TRUE; + for (Field **field_ptr= table->table->field; *field_ptr; field_ptr++) + { + field_ptr[0]->table_name= &table->alias.str; + } + } + param.new_alias= table->alias; + if (where) + where->walk(&Item::replace_table_processor, FALSE, (void*)(&param)); + if (table->on_expr) + table->on_expr->walk(&Item::replace_table_processor, FALSE, + (void*)(&param)); + List_iterator_fast<Item> it(item_list); + Item *item; + while((item= it++)) + { + item->walk(&Item::replace_table_processor, FALSE, (void*)(&param)); + } + return false; +} + /* Make a new sp_instr_stmt and set its m_query to a concatenation of two strings. diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 8b4bdc3a19e..4996db30c30 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1654,6 +1654,9 @@ class st_select_lex: public st_select_lex_node void lex_start(LEX *plex); bool is_unit_nest() { return (nest_flags & UNIT_NEST_FL); } void mark_as_unit_nest() { nest_flags= UNIT_NEST_FL; } + + bool find_table_alias_in_the_from_clause(TABLE_LIST *table); + bool change_to_unique_table_alias(TABLE_LIST *table); }; typedef class st_select_lex SELECT_LEX; @@ -3445,6 +3448,7 @@ struct LEX: public Query_tables_list holds number of tables from which we will delete records. */ uint table_count; + uint context_analysis_only; uint8 describe; /* @@ -3453,7 +3457,6 @@ struct LEX: public Query_tables_list DERIVED_SUBQUERY and DERIVED_VIEW). */ uint8 derived_tables; - uint8 context_analysis_only; uint8 lex_options; // see OPTION_LEX_* enum_nmresolve nm_resolve_mode; @@ -3611,12 +3614,14 @@ struct LEX: public Query_tables_list return (context_analysis_only & (CONTEXT_ANALYSIS_ONLY_PREPARE | CONTEXT_ANALYSIS_ONLY_VCOL_EXPR | - CONTEXT_ANALYSIS_ONLY_VIEW)); + CONTEXT_ANALYSIS_ONLY_PS | + CONTEXT_ANALYSIS_ONLY_VIEW_REAL)); } inline bool is_view_context_analysis() { - return (context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW); + return (context_analysis_only & (CONTEXT_ANALYSIS_ONLY_PS | + CONTEXT_ANALYSIS_ONLY_VIEW_REAL)); } inline void uncacheable(uint8 cause) diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 59006638294..a064df6264d 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2073,7 +2073,7 @@ static bool mysql_test_create_view(Prepared_statement *stmt) if (thd->open_temporary_tables(tables)) goto err; - lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_VIEW; + lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_VIEW_REAL; if (open_normal_and_derived_tables(thd, tables, MYSQL_OPEN_FORCE_SHARED_MDL, DT_INIT | DT_PREPARE)) goto err; diff --git a/sql/sql_priv.h b/sql/sql_priv.h index 07f07a7150f..126f02206b8 100644 --- a/sql/sql_priv.h +++ b/sql/sql_priv.h @@ -297,18 +297,20 @@ any optimizations: afterwards definition of the view will be reconstructed by means of ::print() methods and written to to an .frm file. We need this definition to stay untouched. -*/ -#define CONTEXT_ANALYSIS_ONLY_VIEW 2 +*/ +#define CONTEXT_ANALYSIS_ONLY_VIEW_REAL 2 +// The same as above but mostly for PS +#define CONTEXT_ANALYSIS_ONLY_PS 4 /* Don't evaluate this subquery during derived table prepare even if it's a constant one. */ -#define CONTEXT_ANALYSIS_ONLY_DERIVED 4 +#define CONTEXT_ANALYSIS_ONLY_DERIVED 8 /* Don't evaluate constant sub-expressions of virtual column expressions when opening tables -*/ -#define CONTEXT_ANALYSIS_ONLY_VCOL_EXPR 8 +*/ +#define CONTEXT_ANALYSIS_ONLY_VCOL_EXPR 16 /* diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 9d40e238cbf..0f7fa6a81e9 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1395,7 +1395,7 @@ JOIN::prepare(TABLE_LIST *tables_init, COND *conds_init, uint og_num, if ((thd->lex->nm_resolve_mode == NMRESOLVE_ALIAS || (thd->lex->nm_resolve_mode == NMRESOLVE_NONE && thd->variables.sql_mode & MODE_ALIASES_IN_WHERE)) && - pre_setup_aliases(thd, fields_list, &conds, + pre_setup_aliases(thd, fields_list, &conds, &select_lex->context, &select_lex->current_select_list_fix)) DBUG_RETURN(-1); diff --git a/sql/sql_show.cc b/sql/sql_show.cc index a2cd4e232f5..a980e071795 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -1185,7 +1185,7 @@ mysqld_show_create_get_fields(THD *thd, TABLE_LIST *table_list, /* Access is granted. Execute the command. */ /* We want to preserve the tree for views. */ - lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_VIEW; + lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_PS; { /* @@ -4582,7 +4582,7 @@ fill_schema_table_by_open(THD *thd, MEM_ROOT *mem_root, lex->sql_command= old_lex->sql_command; /* Disable constant subquery evaluation as we won't be locking tables. */ - lex->context_analysis_only= CONTEXT_ANALYSIS_ONLY_VIEW; + lex->context_analysis_only= CONTEXT_ANALYSIS_ONLY_PS; /* Some of process_table() functions rely on wildcard being passed from diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 49f319b3856..b28c58e6d7d 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -303,7 +303,7 @@ bool table_value_constr::prepare(THD *thd, SELECT_LEX *sl, /* setup_order() for a TVC is not called when the following is true - (thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW) + (thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_PS) */ thd->where="order clause"; diff --git a/sql/sql_union.cc b/sql/sql_union.cc index f75391c4503..5074b12e81d 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1501,7 +1501,7 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, !sl->tvc->to_be_wrapped_as_with_tail()) { SELECT_LEX_UNIT *unit= sl->master_unit(); - if (thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW) + if (thd->lex->is_view_context_analysis()) { unit->fake_select_lex= 0; unit->saved_fake_select_lex= 0; @@ -1538,7 +1538,7 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, if (sl->tvc) { if (sl->tvc->to_be_wrapped_as_with_tail() && - !(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW)) + !(thd->lex->is_view_context_analysis())) { st_select_lex *wrapper_sl= wrap_tvc_with_tail(thd, sl); @@ -1802,7 +1802,7 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, if (fake_select_lex != NULL && (thd->stmt_arena->is_stmt_prepare() || - (thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW))) + (thd->lex->is_view_context_analysis()))) { /* Validate the global parameters of this union */ @@ -1841,7 +1841,7 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, } if (fake_select_lex != NULL && (thd->stmt_arena->is_stmt_prepare() || - (thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW))) + (thd->lex->is_view_context_analysis()))) { if (!fake_select_lex->join && !(fake_select_lex->join= @@ -1904,7 +1904,7 @@ void st_select_lex_unit::optimize_bag_operation(bool is_outer_distinct) recursive */ if ((thd->variables.sql_mode & MODE_ORACLE) || - (thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW) || + (thd->lex->is_view_context_analysis()) || (fake_select_lex != NULL && thd->stmt_arena->is_stmt_prepare()) || (with_element && with_element->is_recursive )) return; diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 3fc55552f6b..14fe87a5779 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -547,7 +547,7 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views, } /* prepare select to resolve all fields */ - lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_VIEW; + lex->context_analysis_only|= CONTEXT_ANALYSIS_ONLY_VIEW_REAL; if (unit->prepare(unit->derived, 0, 0)) { /* diff --git a/sql/table.cc b/sql/table.cc index c9420892160..f0ecd78e7cf 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -3993,7 +3993,9 @@ enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share, DBUG_PRINT("enter",("name: '%s.%s' form: %p", share->db.str, share->table_name.str, outparam)); - thd->lex->context_analysis_only&= ~CONTEXT_ANALYSIS_ONLY_VIEW; // not a view + // not a view + thd->lex->context_analysis_only&= ~(CONTEXT_ANALYSIS_ONLY_PS | + CONTEXT_ANALYSIS_ONLY_VIEW_REAL); error= OPEN_FRM_ERROR_ALREADY_ISSUED; // for OOM errors below bzero((char*) outparam, sizeof(*outparam)); @@ -9369,7 +9371,7 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view) { if (is_view() || (unit->prepared && - !(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_VIEW))) + !(thd->lex->is_view_context_analysis()))) create_field_translation(thd); }

1 0

[Commits] 7172ff780bd: MENT-1062 Feature Sponsorship - Support of Column Alias for Sybase compatibility
by psergey 17 Jun '21

17 Jun '21

revision-id: 7172ff780bd6c1d131d6134c4f92ae71604a0b1c (mariadb-10.5.2-1036-g7172ff780bd) parent(s): 03b8b21749294e8455879ae82f91e7f45dacda97 author: Oleksandr Byelkin committer: Sergei Petrunia timestamp: 2021-06-18 00:43:25 +0300 message: MENT-1062 Feature Sponsorship - Support of Column Alias for Sybase compatibility Add RESOLVE_IN_WHERE sql_mode switch (which makes alias resolved in SELECT LIST (backward)/WHERE/ON) Use alias resolving branch created for ORDER BY to resolve aliases as references. To make VIEW independent of the switch, print the resolved alias in view text as expression it refer to. --- mysql-test/main/alias_in_where.result | 377 +++++++++++++++++++++ mysql-test/main/alias_in_where.test | 268 +++++++++++++++ mysql-test/main/information_schema.result | 2 +- mysql-test/main/mysqld--help.result | 2 +- mysql-test/main/sql_mode.result | 4 +- mysql-test/main/sql_mode.test | 2 +- mysql-test/main/system_mysql_db.result | 4 +- mysql-test/main/system_mysql_db_fix40123.result | 4 +- mysql-test/main/system_mysql_db_fix50030.result | 4 +- mysql-test/main/system_mysql_db_fix50117.result | 4 +- mysql-test/suite/funcs_1/r/is_columns_mysql.result | 8 +- mysql-test/suite/sys_vars/r/sql_mode_basic.result | 8 +- .../sys_vars/r/sysvars_server_embedded.result | 2 +- .../sys_vars/r/sysvars_server_notembedded.result | 2 +- mysql-test/suite/sys_vars/t/sql_mode_basic.test | 4 +- scripts/mysql_system_tables.sql | 4 +- scripts/mysql_system_tables_fix.sql | 6 +- sql/item.cc | 202 +++++++++++ sql/item.h | 48 +++ sql/item_cmpfunc.cc | 21 ++ sql/item_cmpfunc.h | 2 + sql/sp.cc | 2 +- sql/sql_base.cc | 82 ++++- sql/sql_base.h | 3 +- sql/sql_class.h | 1 + sql/sql_lex.cc | 3 + sql/sql_lex.h | 10 + sql/sql_prepare.cc | 5 +- sql/sql_select.cc | 10 +- sql/sys_vars.cc | 2 +- 30 files changed, 1060 insertions(+), 36 deletions(-) diff --git a/mysql-test/main/alias_in_where.result b/mysql-test/main/alias_in_where.result new file mode 100644 index 00000000000..05094d1d545 --- /dev/null +++ b/mysql-test/main/alias_in_where.result @@ -0,0 +1,377 @@ +set @save_sql_mode= @@sql_mode; +# +# basic tests of resolving +# +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +select a+b as c from t1 where c > 5; +ERROR 42S22: Unknown column 'c' in 'where clause' +set sql_mode="ALIASES_IN_WHERE"; +# field +select a as c from t1 where c > 5; +c +10 +# expression +select a+b as c from t1 where c > 10; +c +20 +select a+b as c, c*2 as d from t1 where c <= 10; +c d +2 4 +10 20 +select a+b as c, c*2 as d from t1 where c <= 10 and d < 20; +c d +2 4 +# stored procedures +prepare stmt1 from +"select a+b as c, c*2 as d from t1 where c <= 10 and d < 20"; +prepare stmt2 from +"select a as c from t1 where c > 5"; +execute stmt1; +c d +2 4 +execute stmt1; +c d +2 4 +execute stmt2; +c +10 +execute stmt2; +c +10 +# prepared statements keep the mode +set sql_mode=""; +execute stmt1; +c d +2 4 +execute stmt2; +c +10 +set sql_mode="ALIASES_IN_WHERE"; +execute stmt1; +c d +2 4 +execute stmt1; +c d +2 4 +execute stmt2; +c +10 +execute stmt2; +c +10 +# priority have alias +select a+a as b from t1 where b > 10; +b +20 +explain extended +select a+a as b from t1 where b > 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` + `test`.`t1`.`a` AS `b` from `test`.`t1` where `b` > 10 +create view v1 as select a+a as b from t1 where b > 10; +select * from v1; +b +20 +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` + `t1`.`a` AS `b` from `t1` where `t1`.`a` + `t1`.`a` > 10 latin1 latin1_swedish_ci +drop view v1; +drop table t1; +set @@sql_mode= @save_sql_mode; +# +# resolving in ON +# +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +create table t2 (d int, e int); +insert into t2 values (1,2), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +# +# ON is not supported +# +select a+b as c, a,b,c,d from t1 join t2 on c=t2.e; +ERROR 42S22: Unknown column 'c' in 'on clause' +drop table t1, t2; +set @@sql_mode= @save_sql_mode; +# +# VIEW test +# +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +# expression +create view v1 as select a+b as c from t1 where c > 10; +# field +create view v2 as select a as c from t1 where c > 5; +select * from v1; +c +20 +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` + `t1`.`b` AS `c` from `t1` where `t1`.`a` + `t1`.`b` > 10 latin1 latin1_swedish_ci +select * from v2; +c +10 +show create view v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `c` from `t1` where `t1`.`a` > 5 latin1 latin1_swedish_ci +set sql_mode=""; +select * from v1; +c +20 +select * from v2; +c +10 +set sql_mode="ALIASES_IN_WHERE"; +prepare stmt1 from +"select * from v1"; +prepare stmt2 from +"select * from v2"; +execute stmt1; +c +20 +execute stmt1; +c +20 +execute stmt2; +c +10 +execute stmt2; +c +10 +# views are not dependent on the mode after creation +set sql_mode=""; +execute stmt1; +c +20 +execute stmt1; +c +20 +execute stmt2; +c +10 +execute stmt2; +c +10 +set sql_mode="ALIASES_IN_WHERE"; +execute stmt1; +c +20 +execute stmt1; +c +20 +drop view v1, v2; +drop table t1; +# +# PROCEDURES test +# +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +# expression +create procedure p1() select a+b as c from t1 where c > 10; +call p1(); +c +20 +call p1(); +c +20 +set sql_mode=""; +call p1(); +c +20 +call p1(); +c +20 +set sql_mode="ALIASES_IN_WHERE"; +call p1(); +c +20 +call p1(); +c +20 +drop procedure p1; +drop table t1; +# +# aggregate functions test +# +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +select sum(a) as c from t1 where c > 10; +ERROR 42S22: Reference 'c' not supported (reference to group function) +drop table t1; +# +# +# FORWARD resolving tests +# +# +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +create table t3 (d int); +insert into t3 values (1), (2), (6); +set sql_mode="ALIASES_IN_WHERE"; +select a, t1.a + 1 as a, t1.a + 1 as a from t1; +ERROR 23000: Column 'a' in SELECT-list is ambiguous +select a as b, b as a from t1; +ERROR 42S22: Reference 'b' not supported (cyclic reference) +# +# forward reference (first a+1) and +# don't resolve inside alias (second a+1) +# +select a+1, a + 1 as a, t1.a from t1; +a+1 a a +3 2 1 +7 6 5 +12 11 10 +prepare stmt from "select a+1, a + 1 as a, t1.a from t1"; +execute stmt; +a+1 a a +3 2 1 +7 6 5 +12 11 10 +execute stmt; +a+1 a a +3 2 1 +7 6 5 +12 11 10 +# +# WHERE reference expression +# +select a + 1 as a, t1.a from t1 where a = 6; +a a +6 5 +prepare stmt from "select a + 1 as a, t1.a from t1 where a = 6"; +execute stmt; +a a +6 5 +execute stmt; +a a +6 5 +# +# WHERE reference no expression +# +select a - 1 as a, t1.a from t1 where a; +a a +4 5 +9 10 +prepare stmt from "select a - 1 as a, t1.a from t1 where a"; +execute stmt; +a a +4 5 +9 10 +execute stmt; +a a +4 5 +9 10 +# +# inside aggregate and forward reference +# +select max(a), a + 1 as a, t1.a from t1; +max(a) a a +11 2 1 +prepare stmt from "select max(a), a + 1 as a, t1.a from t1"; +execute stmt; +max(a) a a +11 2 1 +execute stmt; +max(a) a a +11 2 1 +# +# reference on aggregate from SELECT LIST +# +select a, max(a + 1) as a, t1.a from t1; +ERROR 42S22: Reference 'a' not supported (reference to group function) +# +# reference on aggregate from SELECT LIST +# +select max(a + 1) as a, t1.a from t1 where a > 2; +ERROR 42S22: Reference 'a' not supported (reference to group function) +# +# normal outer field resolution still works +# +select a from t1 where (select 1 from t3 where a=d); +a +1 +# +# outer alias reference (forward) +# +explain extended +select (select 1 from t3 where a=d), a+1 as a from t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1276 Field or reference 'a' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select <expr_cache><`a`>((/* select#2 */ select 1 from `test`.`t3` where `a` = `test`.`t3`.`d`)) AS `(select 1 from t3 where a=d)`,`test`.`t1`.`a` + 1 AS `a` from `test`.`t1` +select (select 1 from t3 where a=d), a+1 as a from t1; +(select 1 from t3 where a=d) a +1 2 +1 6 +NULL 11 +prepare stmt from "select (select 1 from t3 where a=d), a+1 as a from t1"; +execute stmt; +(select 1 from t3 where a=d) a +1 2 +1 6 +NULL 11 +execute stmt; +(select 1 from t3 where a=d) a +1 2 +1 6 +NULL 11 +# +# outer reference from WHERE +# +explain extended +select a+1 as a from t1 where (select 1 from t3 where a=d); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1276 Field or reference 'a' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` + 1 AS `a` from `test`.`t1` where <expr_cache><`a`>((/* select#2 */ select 1 from `test`.`t3` where `a` = `test`.`t3`.`d`)) +select a+1 as a from t1 where (select 1 from t3 where a=d); +a +2 +6 +prepare stmt from "select a+1 as a from t1 where (select 1 from t3 where a=d)"; +execute stmt; +a +2 +6 +execute stmt; +a +2 +6 +# +# do not resolve outer alias in the same expression +# +explain extended +select (select 1 from t3 where a=d) as a, t1.a from t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select <expr_cache><`test`.`t1`.`a`>((/* select#2 */ select 1 from `test`.`t3` where `test`.`t1`.`a` = `test`.`t3`.`d`)) AS `a`,`test`.`t1`.`a` AS `a` from `test`.`t1` +select (select 1 from t3 where a=d) as a, t1.a from t1; +a a +1 1 +NULL 5 +NULL 10 +prepare stmt from "select (select 1 from t3 where a=d) as a, t1.a from t1"; +execute stmt; +a a +1 1 +NULL 5 +NULL 10 +execute stmt; +a a +1 1 +NULL 5 +NULL 10 +drop tables t1,t3; +set @@sql_mode= @save_sql_mode; diff --git a/mysql-test/main/alias_in_where.test b/mysql-test/main/alias_in_where.test new file mode 100644 index 00000000000..5073446e435 --- /dev/null +++ b/mysql-test/main/alias_in_where.test @@ -0,0 +1,268 @@ + +set @save_sql_mode= @@sql_mode; + +--echo # +--echo # basic tests of resolving +--echo # +create table t1 (a int, b int); + +insert into t1 values (1,1), (5,5), (10, 10); + +--error ER_BAD_FIELD_ERROR +select a+b as c from t1 where c > 5; + +set sql_mode="ALIASES_IN_WHERE"; + +--echo # field +select a as c from t1 where c > 5; + +--echo # expression +select a+b as c from t1 where c > 10; + +select a+b as c, c*2 as d from t1 where c <= 10; + +select a+b as c, c*2 as d from t1 where c <= 10 and d < 20; + +--echo # stored procedures +prepare stmt1 from + "select a+b as c, c*2 as d from t1 where c <= 10 and d < 20"; +prepare stmt2 from + "select a as c from t1 where c > 5"; + +execute stmt1; +execute stmt1; + +execute stmt2; +execute stmt2; + + +--echo # prepared statements keep the mode +set sql_mode=""; +execute stmt1; +execute stmt2; + +set sql_mode="ALIASES_IN_WHERE"; +execute stmt1; +execute stmt1; + +execute stmt2; +execute stmt2; + + +--echo # priority have alias +select a+a as b from t1 where b > 10; +explain extended +select a+a as b from t1 where b > 10; +create view v1 as select a+a as b from t1 where b > 10; +select * from v1; +show create view v1; + +drop view v1; +drop table t1; +set @@sql_mode= @save_sql_mode; + +--echo # +--echo # resolving in ON +--echo # + +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); + +create table t2 (d int, e int); +insert into t2 values (1,2), (5,5), (10, 10); + +set sql_mode="ALIASES_IN_WHERE"; + +--echo # +--echo # ON is not supported +--echo # +--error ER_BAD_FIELD_ERROR +select a+b as c, a,b,c,d from t1 join t2 on c=t2.e; + +drop table t1, t2; +set @@sql_mode= @save_sql_mode; + +--echo # +--echo # VIEW test +--echo # +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +--echo # expression +create view v1 as select a+b as c from t1 where c > 10; +--echo # field +create view v2 as select a as c from t1 where c > 5; + +select * from v1; +show create view v1; +select * from v2; +show create view v2; + +set sql_mode=""; +select * from v1; +select * from v2; + +set sql_mode="ALIASES_IN_WHERE"; +prepare stmt1 from + "select * from v1"; +prepare stmt2 from + "select * from v2"; + + +execute stmt1; +execute stmt1; + +execute stmt2; +execute stmt2; + + +--echo # views are not dependent on the mode after creation +set sql_mode=""; +execute stmt1; +execute stmt1; +execute stmt2; +execute stmt2; + +set sql_mode="ALIASES_IN_WHERE"; +execute stmt1; +execute stmt1; + +drop view v1, v2; +drop table t1; + + +--echo # +--echo # PROCEDURES test +--echo # + +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; +--echo # expression +create procedure p1() select a+b as c from t1 where c > 10; +call p1(); +call p1(); +set sql_mode=""; +call p1(); +call p1(); +set sql_mode="ALIASES_IN_WHERE"; +call p1(); +call p1(); + + +drop procedure p1; +drop table t1; + +--echo # +--echo # aggregate functions test +--echo # +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +set sql_mode="ALIASES_IN_WHERE"; + +--error ER_ILLEGAL_REFERENCE +select sum(a) as c from t1 where c > 10; + +drop table t1; + +--echo # +--echo # +--echo # FORWARD resolving tests +--echo # +--echo # + +create table t1 (a int, b int); +insert into t1 values (1,1), (5,5), (10, 10); +create table t3 (d int); +insert into t3 values (1), (2), (6); + +set sql_mode="ALIASES_IN_WHERE"; + +--error ER_NON_UNIQ_ERROR +select a, t1.a + 1 as a, t1.a + 1 as a from t1; +--error ER_ILLEGAL_REFERENCE +select a as b, b as a from t1; + +--echo # +--echo # forward reference (first a+1) and +--echo # don't resolve inside alias (second a+1) +--echo # +select a+1, a + 1 as a, t1.a from t1; +prepare stmt from "select a+1, a + 1 as a, t1.a from t1"; +execute stmt; +execute stmt; + +--echo # +--echo # WHERE reference expression +--echo # +select a + 1 as a, t1.a from t1 where a = 6; +prepare stmt from "select a + 1 as a, t1.a from t1 where a = 6"; +execute stmt; +execute stmt; + +--echo # +--echo # WHERE reference no expression +--echo # +select a - 1 as a, t1.a from t1 where a; +prepare stmt from "select a - 1 as a, t1.a from t1 where a"; +execute stmt; +execute stmt; + +--echo # +--echo # inside aggregate and forward reference +--echo # +select max(a), a + 1 as a, t1.a from t1; +prepare stmt from "select max(a), a + 1 as a, t1.a from t1"; +execute stmt; +execute stmt; + +--echo # +--echo # reference on aggregate from SELECT LIST +--echo # +--error ER_ILLEGAL_REFERENCE +select a, max(a + 1) as a, t1.a from t1; + +--echo # +--echo # reference on aggregate from SELECT LIST +--echo # +--error ER_ILLEGAL_REFERENCE +select max(a + 1) as a, t1.a from t1 where a > 2; + +--echo # +--echo # normal outer field resolution still works +--echo # +select a from t1 where (select 1 from t3 where a=d); + +--echo # +--echo # outer alias reference (forward) +--echo # +explain extended +select (select 1 from t3 where a=d), a+1 as a from t1; +select (select 1 from t3 where a=d), a+1 as a from t1; +prepare stmt from "select (select 1 from t3 where a=d), a+1 as a from t1"; +execute stmt; +execute stmt; + +--echo # +--echo # outer reference from WHERE +--echo # +explain extended +select a+1 as a from t1 where (select 1 from t3 where a=d); +select a+1 as a from t1 where (select 1 from t3 where a=d); +prepare stmt from "select a+1 as a from t1 where (select 1 from t3 where a=d)"; +execute stmt; +execute stmt; + +--echo # +--echo # do not resolve outer alias in the same expression +--echo # +explain extended +select (select 1 from t3 where a=d) as a, t1.a from t1; +select (select 1 from t3 where a=d) as a, t1.a from t1; +prepare stmt from "select (select 1 from t3 where a=d) as a, t1.a from t1"; +execute stmt; +execute stmt; + +drop tables t1,t3; + +set @@sql_mode= @save_sql_mode; diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result index 8d0425049ea..01cc680f7d1 100644 --- a/mysql-test/main/information_schema.result +++ b/mysql-test/main/information_schema.result @@ -676,7 +676,7 @@ proc body longblob proc definer varchar(384) proc created timestamp proc modified timestamp -proc sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') +proc sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') proc comment text proc character_set_client char(32) proc collation_connection char(32) diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index 7646dc99bce..31640d5e67a 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -1311,7 +1311,7 @@ The following specify which files/extra groups are read (specified before remain NO_AUTO_CREATE_USER, HIGH_NOT_PRECEDENCE, NO_ENGINE_SUBSTITUTION, PAD_CHAR_TO_FULL_LENGTH, EMPTY_STRING_IS_NULL, SIMULTANEOUS_ASSIGNMENT, - TIME_ROUND_FRACTIONAL + TIME_ROUND_FRACTIONAL, ALIASES_IN_WHERE --sql-safe-updates If set to 1, UPDATEs and DELETEs need either a key in the WHERE clause, or a LIMIT clause, or else they will aborted. Prevents the common mistake of accidentally diff --git a/mysql-test/main/sql_mode.result b/mysql-test/main/sql_mode.result index 25a90703bf5..2c1604e2234 100644 --- a/mysql-test/main/sql_mode.result +++ b/mysql-test/main/sql_mode.result @@ -479,8 +479,8 @@ set sql_mode=2147483648*2*2*2; select @@sql_mode; @@sql_mode TIME_ROUND_FRACTIONAL -set sql_mode=2147483648*2*2*2*2; -ERROR 42000: Variable 'sql_mode' can't be set to the value of '34359738368' +set sql_mode=2147483648*2*2*2*2*2; +ERROR 42000: Variable 'sql_mode' can't be set to the value of '68719476736' select @@sql_mode; @@sql_mode TIME_ROUND_FRACTIONAL diff --git a/mysql-test/main/sql_mode.test b/mysql-test/main/sql_mode.test index 97f5cf42791..b9a8ae255fc 100644 --- a/mysql-test/main/sql_mode.test +++ b/mysql-test/main/sql_mode.test @@ -266,7 +266,7 @@ select @@sql_mode; set sql_mode=2147483648*2*2*2; select @@sql_mode; --error 1231 -set sql_mode=2147483648*2*2*2*2; # that mode does not exist +set sql_mode=2147483648*2*2*2*2*2; # that mode does not exist select @@sql_mode; # diff --git a/mysql-test/main/system_mysql_db.result b/mysql-test/main/system_mysql_db.result index c9e337617a1..7929a25eeb2 100644 --- a/mysql-test/main/system_mysql_db.result +++ b/mysql-test/main/system_mysql_db.result @@ -153,7 +153,7 @@ proc CREATE TABLE `proc` ( `definer` varchar(384) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '', `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') NOT NULL DEFAULT '', `comment` text CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `character_set_client` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, `collation_connection` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, @@ -179,7 +179,7 @@ event CREATE TABLE `event` ( `ends` datetime DEFAULT NULL, `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED', `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') NOT NULL DEFAULT '', `comment` char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '', `originator` int(10) unsigned NOT NULL, `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', diff --git a/mysql-test/main/system_mysql_db_fix40123.result b/mysql-test/main/system_mysql_db_fix40123.result index 32609117fd2..068f9d66231 100644 --- a/mysql-test/main/system_mysql_db_fix40123.result +++ b/mysql-test/main/system_mysql_db_fix40123.result @@ -191,7 +191,7 @@ proc CREATE TABLE `proc` ( `definer` varchar(384) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '', `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') NOT NULL DEFAULT '', `comment` text CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `character_set_client` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, `collation_connection` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, @@ -217,7 +217,7 @@ event CREATE TABLE `event` ( `ends` datetime DEFAULT NULL, `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED', `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') NOT NULL DEFAULT '', `comment` char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '', `originator` int(10) unsigned NOT NULL, `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', diff --git a/mysql-test/main/system_mysql_db_fix50030.result b/mysql-test/main/system_mysql_db_fix50030.result index 461da43af88..5fe077b6d1f 100644 --- a/mysql-test/main/system_mysql_db_fix50030.result +++ b/mysql-test/main/system_mysql_db_fix50030.result @@ -195,7 +195,7 @@ proc CREATE TABLE `proc` ( `definer` varchar(384) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '', `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') NOT NULL DEFAULT '', `comment` text CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `character_set_client` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, `collation_connection` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, @@ -221,7 +221,7 @@ event CREATE TABLE `event` ( `ends` datetime DEFAULT NULL, `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED', `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') NOT NULL DEFAULT '', `comment` char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '', `originator` int(10) unsigned NOT NULL, `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', diff --git a/mysql-test/main/system_mysql_db_fix50117.result b/mysql-test/main/system_mysql_db_fix50117.result index 4198cda4590..2a96348498f 100644 --- a/mysql-test/main/system_mysql_db_fix50117.result +++ b/mysql-test/main/system_mysql_db_fix50117.result @@ -175,7 +175,7 @@ proc CREATE TABLE `proc` ( `definer` varchar(384) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '', `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') NOT NULL DEFAULT '', `comment` text CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `character_set_client` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, `collation_connection` char(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, @@ -201,7 +201,7 @@ event CREATE TABLE `event` ( `ends` datetime DEFAULT NULL, `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED', `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP', - `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') NOT NULL DEFAULT '', + `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') NOT NULL DEFAULT '', `comment` char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '', `originator` int(10) unsigned NOT NULL, `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql.result b/mysql-test/suite/funcs_1/r/is_columns_mysql.result index c20547f7a89..bdb0311a70a 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql.result @@ -61,7 +61,7 @@ def mysql event modified 9 '0000-00-00 00:00:00' NO timestamp NULL NULL NULL NUL def mysql event name 2 '' NO char 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci char(64) PRI select,insert,update,references NEVER NULL def mysql event on_completion 14 'DROP' NO enum 8 24 NULL NULL NULL utf8mb3 utf8mb3_general_ci enum('DROP','PRESERVE') select,insert,update,references NEVER NULL def mysql event originator 17 NULL NO int NULL NULL 10 0 NULL NULL NULL int(10) unsigned select,insert,update,references NEVER NULL -def mysql event sql_mode 15 '' NO set 561 1683 NULL NULL NULL utf8mb3 utf8mb3_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') select,insert,update,references NEVER NULL +def mysql event sql_mode 15 '' NO set 578 1734 NULL NULL NULL utf8mb3 utf8mb3_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') select,insert,update,references NEVER NULL def mysql event starts 11 NULL YES datetime NULL NULL NULL NULL 0 NULL NULL datetime select,insert,update,references NEVER NULL def mysql event status 13 'ENABLED' NO enum 18 54 NULL NULL NULL utf8mb3 utf8mb3_general_ci enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') select,insert,update,references NEVER NULL def mysql event time_zone 18 'SYSTEM' NO char 64 64 NULL NULL NULL latin1 latin1_swedish_ci char(64) select,insert,update,references NEVER NULL @@ -136,7 +136,7 @@ def mysql proc returns 10 NULL NO longblob 4294967295 4294967295 NULL NULL NULL def mysql proc security_type 8 'DEFINER' NO enum 7 21 NULL NULL NULL utf8mb3 utf8mb3_general_ci enum('INVOKER','DEFINER') select,insert,update,references NEVER NULL def mysql proc specific_name 4 '' NO char 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci char(64) select,insert,update,references NEVER NULL def mysql proc sql_data_access 6 'CONTAINS_SQL' NO enum 17 51 NULL NULL NULL utf8mb3 utf8mb3_general_ci enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') select,insert,update,references NEVER NULL -def mysql proc sql_mode 15 '' NO set 561 1683 NULL NULL NULL utf8mb3 utf8mb3_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') select,insert,update,references NEVER NULL +def mysql proc sql_mode 15 '' NO set 578 1734 NULL NULL NULL utf8mb3 utf8mb3_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') select,insert,update,references NEVER NULL def mysql proc type 3 NULL NO enum 12 36 NULL NULL NULL utf8mb3 utf8mb3_general_ci enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') PRI select,insert,update,references NEVER NULL def mysql procs_priv Db 2 '' NO char 64 192 NULL NULL NULL utf8mb3 utf8mb3_bin char(64) PRI select,insert,update,references NEVER NULL def mysql procs_priv Grantor 6 '' NO varchar 384 1152 NULL NULL NULL utf8mb3 utf8mb3_bin varchar(384) MUL select,insert,update,references NEVER NULL @@ -389,7 +389,7 @@ NULL mysql event starts datetime NULL NULL NULL NULL datetime NULL mysql event ends datetime NULL NULL NULL NULL datetime 3.0000 mysql event status enum 18 54 utf8mb3 utf8mb3_general_ci enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') 3.0000 mysql event on_completion enum 8 24 utf8mb3 utf8mb3_general_ci enum('DROP','PRESERVE') -3.0000 mysql event sql_mode set 561 1683 utf8mb3 utf8mb3_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') +3.0000 mysql event sql_mode set 578 1734 utf8mb3 utf8mb3_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') 3.0000 mysql event comment char 64 192 utf8mb3 utf8mb3_bin char(64) NULL mysql event originator int NULL NULL NULL NULL int(10) unsigned 1.0000 mysql event time_zone char 64 64 latin1 latin1_swedish_ci char(64) @@ -463,7 +463,7 @@ NULL mysql innodb_table_stats sum_of_other_index_sizes bigint NULL NULL NULL NUL 3.0000 mysql proc definer varchar 384 1152 utf8mb3 utf8mb3_bin varchar(384) NULL mysql proc created timestamp NULL NULL NULL NULL timestamp NULL mysql proc modified timestamp NULL NULL NULL NULL timestamp -3.0000 mysql proc sql_mode set 561 1683 utf8mb3 utf8mb3_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') +3.0000 mysql proc sql_mode set 578 1734 utf8mb3 utf8mb3_general_ci set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') 1.0000 mysql proc comment text 65535 65535 utf8mb3 utf8mb3_bin text 3.0000 mysql proc character_set_client char 32 96 utf8mb3 utf8mb3_bin char(32) 3.0000 mysql proc collation_connection char 32 96 utf8mb3 utf8mb3_bin char(32) diff --git a/mysql-test/suite/sys_vars/r/sql_mode_basic.result b/mysql-test/suite/sys_vars/r/sql_mode_basic.result index d911e80b780..6efa239297e 100644 --- a/mysql-test/suite/sys_vars/r/sql_mode_basic.result +++ b/mysql-test/suite/sys_vars/r/sql_mode_basic.result @@ -370,12 +370,12 @@ SET @@global.sql_mode = 17179869184; SELECT @@global.sql_mode; @@global.sql_mode TIME_ROUND_FRACTIONAL -SET @@global.sql_mode = 34359738367; +SET @@global.sql_mode = 34359738368*2-1; SELECT @@global.sql_mode; @@global.sql_mode -REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT,TIME_ROUND_FRACTIONAL -SET @@global.sql_mode = 34359738368; -ERROR 42000: Variable 'sql_mode' can't be set to the value of '34359738368' +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT,TIME_ROUND_FRACTIONAL,ALIASES_IN_WHERE +SET @@global.sql_mode = 34359738368*2; +ERROR 42000: Variable 'sql_mode' can't be set to the value of '68719476736' SET @@global.sql_mode = 0.4; ERROR 42000: Incorrect argument type to variable 'sql_mode' '#---------------------FN_DYNVARS_152_08----------------------#' diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 0c818a5b0f9..47cb074b622 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -3269,7 +3269,7 @@ VARIABLE_COMMENT Sets the sql mode NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT,TIME_ROUND_FRACTIONAL +ENUM_VALUE_LIST REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT,TIME_ROUND_FRACTIONAL,ALIASES_IN_WHERE READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SQL_NOTES diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 598aee71abd..851a6beeef4 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -3929,7 +3929,7 @@ VARIABLE_COMMENT Sets the sql mode NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT,TIME_ROUND_FRACTIONAL +ENUM_VALUE_LIST REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,IGNORE_BAD_TABLE_OPTIONS,ONLY_FULL_GROUP_BY,NO_UNSIGNED_SUBTRACTION,NO_DIR_IN_CREATE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,MYSQL323,MYSQL40,ANSI,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT,TIME_ROUND_FRACTIONAL,ALIASES_IN_WHERE READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME SQL_NOTES diff --git a/mysql-test/suite/sys_vars/t/sql_mode_basic.test b/mysql-test/suite/sys_vars/t/sql_mode_basic.test index 68be8ba969b..2220f34fa4c 100644 --- a/mysql-test/suite/sys_vars/t/sql_mode_basic.test +++ b/mysql-test/suite/sys_vars/t/sql_mode_basic.test @@ -313,11 +313,11 @@ SELECT @@global.sql_mode; SET @@global.sql_mode = 17179869184; SELECT @@global.sql_mode; -SET @@global.sql_mode = 34359738367; +SET @@global.sql_mode = 34359738368*2-1; SELECT @@global.sql_mode; --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.sql_mode = 34359738368; +SET @@global.sql_mode = 34359738368*2; # use of decimal values diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql index 3bb24eb4c8f..b9e145464c6 100644 --- a/scripts/mysql_system_tables.sql +++ b/scripts/mysql_system_tables.sql @@ -149,7 +149,7 @@ CREATE TABLE IF NOT EXISTS time_zone_transition_type ( Time_zone_id int unsign CREATE TABLE IF NOT EXISTS time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY /*TranTime*/ (Transition_time) ) engine=Aria transactional=1 CHARACTER SET utf8 comment='Leap seconds information for time zones'; -CREATE TABLE IF NOT EXISTS proc (db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum( 'CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA') DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns longblob NOT NULL, body longblob NOT NULL, definer varchar(384) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'IGNORE_BAD_TABLE_OPTIONS', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE ', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE', 'NO_ENGINE_SUBSTITUTION', 'PAD_CHAR_TO_FULL_LENGTH', 'EMPTY_STRING_IS_NULL', 'SIMULTANEOUS_ASSIGNMENT', 'TIME_ROUND_FRACTIONAL') DEFAULT '' NOT NULL, comment text collate utf8_bin NOT NULL, character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, aggregate enum('NONE', 'GROUP') DEFAULT 'NONE' NOT NULL, PRIMARY KEY (db,name,type)) engine=Aria transactional=1 character set utf8 comment='Stored Procedures'; +CREATE TABLE IF NOT EXISTS proc (db char(64) collate utf8_bin DEFAULT '' NOT NULL, name char(64) DEFAULT '' NOT NULL, type enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') NOT NULL, specific_name char(64) DEFAULT '' NOT NULL, language enum('SQL') DEFAULT 'SQL' NOT NULL, sql_data_access enum( 'CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA') DEFAULT 'CONTAINS_SQL' NOT NULL, is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL, security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL, param_list blob NOT NULL, returns longblob NOT NULL, body longblob NOT NULL, definer varchar(384) collate utf8_bin DEFAULT '' NOT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', sql_mode set( 'REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'IGNORE_BAD_TABLE_OPTIONS', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE ', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE', 'NO_ENGINE_SUBSTITUTION', 'PAD_CHAR_TO_FULL_LENGTH', 'EMPTY_STRING_IS_NULL', 'SIMULTANEOUS_ASSIGNMENT', 'TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') DEFAULT '' NOT NULL, comment text collate utf8_bin NOT NULL, character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 longblob, aggregate enum('NONE', 'GROUP') DEFAULT 'NONE' NOT NULL, PRIMARY KEY (db,name,type)) engine=Aria transactional=1 character set utf8 comment='Stored Procedures'; CREATE TABLE IF NOT EXISTS procs_priv ( Host char(255) binary DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User char(128) binary DEFAULT '' NOT NULL, Routine_name char(64) COLLATE utf8_general_ci DEFAULT '' NOT NULL, Routine_type enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') NOT NULL, Grantor varchar(384) DEFAULT '' NOT NULL, Proc_priv set('Execute','Alter Routine','Grant') COLLATE utf8_general_ci DEFAULT '' NOT NULL, Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor) ) engine=Aria transactional=1 CHARACTER SET utf8 COLLATE utf8_bin comment='Procedure privileges'; @@ -170,7 +170,7 @@ PREPARE stmt FROM @str; EXECUTE stmt; DROP PREPARE stmt; -CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer varchar(384) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', last_executed DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status ENUM('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL default 'ENABLED', on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL') DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', originator INTEGER UNSIGNED NOT NULL, time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_bin, body_utf8 lon gblob, PRIMARY KEY (db, name) ) engine=Aria transactional=1 DEFAULT CHARSET=utf8 COMMENT 'Events'; +CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', name char(64) CHARACTER SET utf8 NOT NULL default '', body longblob NOT NULL, definer varchar(384) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', execute_at DATETIME default NULL, interval_value int(11) default NULL, interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') default NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, modified TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', last_executed DATETIME default NULL, starts DATETIME default NULL, ends DATETIME default NULL, status ENUM('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL default 'ENABLED', on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP', sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT','TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE') DEFAULT '' NOT NULL, comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '', originator INTEGER UNSIGNED NOT NULL, time_zone char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM', character_set_client char(32) collate utf8_bin, collation_connection char(32) collate utf8_bin, db_collation char(32) collate utf8_ bin, body_utf8 longblob, PRIMARY KEY (db, name) ) engine=Aria transactional=1 DEFAULT CHARSET=utf8 COMMENT 'Events'; SET @create_innodb_table_stats="CREATE TABLE IF NOT EXISTS innodb_table_stats ( database_name VARCHAR(64) NOT NULL, diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql index ed89cc1fd6c..8077324e279 100644 --- a/scripts/mysql_system_tables_fix.sql +++ b/scripts/mysql_system_tables_fix.sql @@ -460,7 +460,8 @@ ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL, 'PAD_CHAR_TO_FULL_LENGTH', 'EMPTY_STRING_IS_NULL', 'SIMULTANEOUS_ASSIGNMENT', - 'TIME_ROUND_FRACTIONAL' + 'TIME_ROUND_FRACTIONAL', + 'ALIASES_IN_WHERE' ) DEFAULT '' NOT NULL, DEFAULT CHARACTER SET utf8; @@ -597,7 +598,8 @@ ALTER TABLE event MODIFY sql_mode 'PAD_CHAR_TO_FULL_LENGTH', 'EMPTY_STRING_IS_NULL', 'SIMULTANEOUS_ASSIGNMENT', - 'TIME_ROUND_FRACTIONAL' + 'TIME_ROUND_FRACTIONAL', + 'ALIASES_IN_WHERE' ) DEFAULT '' NOT NULL AFTER on_completion; ALTER TABLE event MODIFY name char(64) CHARACTER SET utf8 NOT NULL default ''; diff --git a/sql/item.cc b/sql/item.cc index b98270022cc..10107d137dd 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -41,6 +41,7 @@ // find_item_in_list, // RESOLVED_AGAINST_ALIAS, ... #include "sql_expression_cache.h" +#include "sql_parse.h" // check_stack_overrun const String my_null_string("NULL", 4, default_charset_info); const String my_default_string("DEFAULT", 7, default_charset_info); @@ -740,6 +741,142 @@ bool Item_ident::collect_outer_ref_processor(void *param) } +/** + Create Item_ref_alias based on this Item and put it to the reference + + @param thd thread handle + @param found_ref reference of the item + @param outer_context for local resolving - NULL otherwise outer context + @param reference pointer where to put the new item + + @retval TRUE Error + @retval FALSE OK + +*/ + +bool +Item_ident::create_ref_alias(THD *thd, Item **found_ref, + Name_resolution_context *outer_context, + Item **reference) +{ + Item_ref_alias *rf= + new(thd->mem_root) Item_ref_alias(thd, found_ref, context, outer_context); + if (!rf) + return TRUE; + rf->name= name; + if (is_explicit_name()) + rf->base_flags|= item_base_t::IS_EXPLICIT_NAME; + thd->change_item_tree(reference, rf); + return FALSE; +} + + +/** + Resolve aliases with loop check data prepare if needed + + @param thd thread handle + @param reference resolving Item refrence + @param curr current resolving expression descriptor + @param all_dep array of all descriptors or NULL if it is not + SELECT-list + + @retval TRUE Error + @retval FALSE OK +*/ + +bool Item_ident::pre_fix_aliases(THD *thd, Item **reference, + Cyclic_dep_check* curr, + Cyclic_dep_check* all_dep) +{ + DBUG_ENTER("Item_ident::pre_fix_aliases"); + if (!field_name.length || // alas has field name + ((db_name.str && db_name.str[0]) || + (table_name.str && table_name.str[0]))) + DBUG_RETURN(0); // alias can not have database or table + + List_iterator<Item> it(context->select_lex->item_list); + Item *item, **found_ref= NULL; + uint found_index= 0; + + for(uint i= 0; (item= it++); i++) + { + if (!item->is_explicit_name() || // no explicit name == no alias + (all_dep && curr == (all_dep + i))) // do not resolve self-references + continue; + if (item->name.length == field_name.length && + strncmp(item->name.str, field_name.str, field_name.length) == 0) + { + if (found_ref) + { + // duplicates check + my_error(ER_NON_UNIQ_ERROR, MYF(0), + field_name.str, "SELECT-list"); + DBUG_RETURN (1); + } + found_ref= it.ref(); + found_index= i; + } + } + if (found_ref) + { + if (create_ref_alias(thd, found_ref, NULL, reference)) + DBUG_RETURN(1); + if (all_dep) // infor for loop detection + curr->push_back(all_dep + found_index); + DBUG_RETURN(0); + } + + // check outer references + Name_resolution_context *cnt= context; + for(;;) + { + + Field *fld= find_field_in_tables(thd, this, + cnt->first_name_resolution_table, + cnt->last_name_resolution_table, + cnt->ignored_tables, + NULL, IGNORE_ERRORS, + FALSE, FALSE); + if (fld != not_found_field) + break; // the field found or an error happend (fix_field will handle it) + + cnt= cnt->outer_context; + + if (!cnt) + break; // top of name resolution scope is reached + + // check outer context + Item **self_ref= cnt->select_lex->current_select_list_fix; + List_iterator<Item> it(cnt->select_lex->item_list); + while ((item= it++)) + { + if (!item->is_explicit_name() || // no explicit name == no alias + it.ref() == self_ref) // skip self reference + continue; + if (item->name.length == field_name.length && + strncmp(item->name.str, field_name.str, field_name.length) == 0) + { + if (found_ref) + { + // duplicates check + my_error(ER_NON_UNIQ_ERROR, MYF(0), + field_name.str, "SELECT-list"); + DBUG_RETURN (1); + } + found_ref= it.ref(); + } + } + if (found_ref) + { + if (create_ref_alias(thd, found_ref, cnt, reference)) + DBUG_RETURN(1); + DBUG_RETURN(0); + } + } + + DBUG_RETURN(0); +} + /** Store the pointer to this item field into a list if not already there. @@ -2675,6 +2812,29 @@ Item* Item_func_or_sum::build_clone(THD *thd) return copy; } +bool Item_func_or_sum::pre_fix_aliases(THD *thd, Item **, + Cyclic_dep_check* curr, + Cyclic_dep_check* all_dep) +{ + Item **arg,**arg_end; + uchar buff[sizeof(char*)]; // Max local vars in function + DBUG_ENTER("Item_func::pre_fix_aliases"); + + if (check_stack_overrun(thd, STACK_MIN_SIZE, buff)) + DBUG_RETURN(TRUE); // Fatal error flag is set! + + if (arg_count) + { + for (arg= args, arg_end= args + arg_count; arg != arg_end ; arg++) + { + if ((*arg)->pre_fix_aliases(thd, arg, curr, all_dep)) + DBUG_RETURN(TRUE); + } + } + DBUG_RETURN(FALSE); +} + + Item_sp::Item_sp(THD *thd, Name_resolution_context *context_arg, sp_name *name_arg) : context(context_arg), m_name(name_arg), m_sp(NULL), func_ctx(NULL), @@ -8085,6 +8245,48 @@ bool Item_ref::fix_fields(THD *thd, Item **reference) } +bool Item_ref_alias::fix_fields(THD *thd, Item **reference) +{ + if (outer_finding) + { + // It is outer resolved field + Item_outer_ref *rf= new(thd->mem_root) Item_outer_ref(thd, context, ref, + {0, 0}, name, true); + rf->base_flags&= ~item_base_t::FIXED; + if (!rf) + return TRUE; + thd->change_item_tree(reference, rf); + context->select_lex->inner_refs_list.push_back(rf, thd->mem_root); + rf->in_sum_func= thd->lex->in_sum_func; + + mark_as_dependent(thd, outer_finding->select_lex, context->select_lex, + this, rf, FALSE); + + (*reference)= rf; + + if (rf->fix_fields(thd, reference)) + return TRUE; + + return FALSE; + } + + // it is probably forard reference, but loops was checked + if ((*ref)->fix_fields_if_needed_for_scalar(thd, ref)) + return TRUE; + + if ((*ref)->with_sum_func()) + { + my_error(ER_ILLEGAL_REFERENCE, MYF(0), + name.str,"reference to group function"); + + context->process_error(thd); + return TRUE; + } + set_properties(); + return FALSE; +} + + void Item_ref::set_properties() { Type_std_attributes::set(*ref); diff --git a/sql/item.h b/sql/item.h index 004a56e7371..6e0e9f707c3 100644 --- a/sql/item.h +++ b/sql/item.h @@ -286,6 +286,8 @@ struct Name_resolution_context: Sql_alloc } }; +typedef List<void> Cyclic_dep_check; + /* Store and restore the current state of a name resolution context. @@ -1153,6 +1155,14 @@ class Item :public Value_source, DBUG_ASSERT(0); } + virtual bool pre_fix_aliases(THD *, Item **, + Cyclic_dep_check*, Cyclic_dep_check*) + { + DBUG_ENTER("Item::pre_fix_aliases"); + // most Items do nothing for preliminry aliases resolvimg + DBUG_RETURN(0); + } + /* Fix after some tables has been pulled out. Basically re-calculate all attributes that are dependent on the tables. @@ -1511,6 +1521,7 @@ class Item :public Value_source, - Item_ref - Item_cache_wrapper - Item_direct_ref + - Item_ref_alias - Item_direct_view_ref - Item_ref_null_helper - Item_name_const @@ -3474,6 +3485,12 @@ class Item_ident :public Item_result_field Collect outer references */ bool collect_outer_ref_processor(void *arg) override; + bool pre_fix_aliases(THD *, Item **, + Cyclic_dep_check*, Cyclic_dep_check*) override; + + bool create_ref_alias(THD *thd, Item **found_ref, + Name_resolution_context *outer_context, + Item **reference); friend bool insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, const char *table_name, List_iterator<Item> *it, @@ -5432,6 +5449,8 @@ class Item_func_or_sum: public Item_result_field, { return Item_args::value_depends_on_sql_mode_bit_or().soft_to_hard(); } + bool pre_fix_aliases(THD *, Item**, Cyclic_dep_check*, + Cyclic_dep_check*) override; }; class sp_head; @@ -5807,6 +5826,35 @@ class Item_direct_ref_to_ident :public Item_direct_ref }; +/** + This class is the same as Item_direct_ref but created to point to + an expression with alias in SELECT list for preliminary alias resolving +*/ + +class Item_ref_alias :public Item_direct_ref +{ + Name_resolution_context *outer_finding; +public: + Item_ref_alias(THD *thd, Item **item, Name_resolution_context *context, + Name_resolution_context *outer): + Item_direct_ref(thd, context, item, {NULL, 0}, + item[0]->name, TRUE), outer_finding(outer) + { + ref= item; + base_flags&= ~item_base_t::FIXED; + } + + bool fix_fields(THD *thd, Item **it) override; + + void print(String *str, enum_query_type query_type) override + { + if (query_type & QT_VIEW_INTERNAL) + (*ref)->print(str, query_type); + else + Item_direct_ref::print(str, query_type); + } +}; + class Item_cache; class Expression_cache; class Expression_cache_tracker; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 2a6f8ae57db..cbceff7a798 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4940,6 +4940,27 @@ Item_cond::fix_fields(THD *thd, Item **ref) return FALSE; } +bool +Item_cond::pre_fix_aliases(THD *thd, Item **, Cyclic_dep_check* curr, + Cyclic_dep_check* all_dep) + +{ + List_iterator<Item> li(list); + Item *item; + uchar buff[sizeof(char*)]; // Max local vars in function + DBUG_ENTER("Item_cond::pre_fix_aliases"); + + if (check_stack_overrun(thd, STACK_MIN_SIZE, buff)) + DBUG_RETURN(TRUE); // Fatal error flag is set! + + while ((item= li++)) + { + if (item->pre_fix_aliases(thd, li.ref(), curr, all_dep)) + DBUG_RETURN(TRUE); + } + DBUG_RETURN(FALSE); +} + bool Item_cond::eval_not_null_tables(void *opt_arg) diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index bc7441c2530..f59e66e1f88 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -3162,6 +3162,8 @@ class Item_cond :public Item_bool_func bool fix_fields(THD *, Item **ref) override; void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge) override; + bool pre_fix_aliases(THD *, Item**, Cyclic_dep_check*, + Cyclic_dep_check*) override; enum Type type() const override { return COND_ITEM; } List<Item>* argument_list() { return &list; } diff --git a/sql/sp.cc b/sql/sp.cc index 73c9c960cf7..902d68d7735 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -202,7 +202,7 @@ TABLE_FIELD_TYPE proc_table_fields[MYSQL_PROC_FIELD_COUNT] = "'ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER'," "'HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'," "'EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT'," - "'TIME_ROUND_FRACTIONAL')") }, + "'TIME_ROUND_FRACTIONAL','ALIASES_IN_WHERE')") }, { NULL, 0 } }, { diff --git a/sql/sql_base.cc b/sql/sql_base.cc index e10abd77f71..62c7af44256 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -6465,7 +6465,7 @@ find_field_in_tables(THD *thd, Item_ident *item, If the field was an outer referencee, mark all selects using this sub query as dependent on the outer query */ - if (!all_merged && current_sel != last_select) + if (!all_merged && current_sel != last_select && ref) { mark_select_range_as_dependent(thd, last_select, current_sel, found, *ref, item, true); @@ -7632,6 +7632,79 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields, DBUG_RETURN(0); } +enum check_dep_loops_res {LOOP_OK, LOOP_DETECTED, LOOP_ERROR}; + +static enum check_dep_loops_res check_dep_loops(THD* thd, + Cyclic_dep_check *base, + Cyclic_dep_check *check) +{ + if (check->elements == 0) + return LOOP_OK; + void *i; + if (check_stack_overrun(thd, STACK_MIN_SIZE, (unsigned char*)&i)) + return LOOP_ERROR; // Fatal error flag is set! + List_iterator_fast<void> it(*check); + while((i= it++)) + { + if (base == i) + return LOOP_DETECTED; + enum check_dep_loops_res res= check_dep_loops(thd, base, + (Cyclic_dep_check *)i); + if (res != LOOP_OK) + return res; + } + return LOOP_OK; +} + +bool pre_setup_aliases(THD *thd, List<Item> &fields, Item **conds, + Item ***current_select_list_fix) +{ + List_iterator<Item> it(fields); + uint num= fields.elements; + DBUG_ENTER("pre_setup_aliases"); + + // Process SELECT-list + + Cyclic_dep_check *ctrl= (Cyclic_dep_check *) + alloc_root(thd->mem_root, num * sizeof(Cyclic_dep_check)); + if (!ctrl) + DBUG_RETURN(1); + for (uint i= 0; i < num; i++) + { + ctrl[i].empty(); + } + for (uint i= 0; i < num; i++) + { + Item *item= it++; + (*current_select_list_fix)= it.ref(); + if (item->pre_fix_aliases(thd, it.ref(), ctrl+i, ctrl)) + DBUG_RETURN(1); + } + (*current_select_list_fix)= NULL; + + // check loops + it.rewind(); + for (uint i= 0; i < num; i++) + { + enum check_dep_loops_res res= check_dep_loops(thd, ctrl + i, ctrl + i ); + Item *item= it++; + if (res == LOOP_DETECTED) + my_error(ER_ILLEGAL_REFERENCE, MYF(0), + item->name.str, "cyclic reference"); + if (res != LOOP_OK) + DBUG_RETURN(1); + } + + // Process conditions + if (conds[0]) + { + if (conds[0]->pre_fix_aliases(thd, conds, NULL, NULL)) + DBUG_RETURN(0); + } + + DBUG_RETURN(0); +} + /**************************************************************************** ** Check that all given fields exists and fill struct with current data ****************************************************************************/ @@ -7639,9 +7712,10 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields, bool setup_fields(THD *thd, Ref_ptr_array ref_pointer_array, List<Item> &fields, enum_column_usage column_usage, List<Item> *sum_func_list, List<Item> *pre_fix, - bool allow_sum_func) + bool allow_sum_func, Item ***current_select_list_fix) { Item *item; + Item **unused= NULL; enum_column_usage saved_column_usage= thd->column_usage; nesting_map save_allow_sum_func= thd->lex->allow_sum_func; List_iterator<Item> it(fields); @@ -7649,6 +7723,8 @@ bool setup_fields(THD *thd, Ref_ptr_array ref_pointer_array, bool make_pre_fix= (pre_fix && (pre_fix->elements == 0)); DBUG_ENTER("setup_fields"); DBUG_PRINT("enter", ("ref_pointer_array: %p", ref_pointer_array.array())); + if (!current_select_list_fix) + current_select_list_fix= &unused; thd->column_usage= column_usage; DBUG_PRINT("info", ("thd->column_usage: %d", thd->column_usage)); @@ -7695,6 +7771,7 @@ bool setup_fields(THD *thd, Ref_ptr_array ref_pointer_array, thd->lex->current_select->cur_pos_in_select_list= 0; while ((item= it++)) { + (*current_select_list_fix) = it.ref(); if (make_pre_fix) pre_fix->push_back(item, thd->stmt_arena->mem_root); @@ -7727,6 +7804,7 @@ bool setup_fields(THD *thd, Ref_ptr_array ref_pointer_array, thd->lex->used_tables|= item->used_tables(); thd->lex->current_select->cur_pos_in_select_list++; } + (*current_select_list_fix) = NULL; thd->lex->current_select->is_item_list_lookup= save_is_item_list_lookup; thd->lex->current_select->cur_pos_in_select_list= UNDEF_POS; diff --git a/sql/sql_base.h b/sql/sql_base.h index cafb5967480..31a64a63d2d 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -185,7 +185,8 @@ int setup_returning_fields(THD* thd, TABLE_LIST* table_list); bool setup_fields(THD *thd, Ref_ptr_array ref_pointer_array, List<Item> &item, enum_column_usage column_usage, List<Item> *sum_func_list, List<Item> *pre_fix, - bool allow_sum_func); + bool allow_sum_func, Item ***progress_pointer= NULL); +bool pre_setup_aliases(THD*, List<Item> &, Item **, Item ***); void unfix_fields(List<Item> &items); bool fill_record(THD * thd, TABLE *table_arg, List<Item> &fields, List<Item> &values, bool ignore_errors, bool update); diff --git a/sql/sql_class.h b/sql/sql_class.h index 1f42dd6dec4..30777fd27ea 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -182,6 +182,7 @@ enum enum_binlog_row_image { #define MODE_EMPTY_STRING_IS_NULL (1ULL << 32) #define MODE_SIMULTANEOUS_ASSIGNMENT (1ULL << 33) #define MODE_TIME_ROUND_FRACTIONAL (1ULL << 34) +#define MODE_ALIASES_IN_WHERE (1ULL << 35) /* The following modes are specific to MySQL */ #define MODE_MYSQL80_TIME_TRUNCATE_FRACTIONAL (1ULL << 32) diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 05574daf42b..53bf876eb75 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1318,6 +1318,8 @@ void LEX::start(THD *thd_arg) wild= 0; exchange= 0; + nm_resolve_mode= NMRESOLVE_NONE; + DBUG_VOID_RETURN; } @@ -3009,6 +3011,7 @@ void st_select_lex::init_select() type= 0; db= null_clex_str; having= 0; + current_select_list_fix= 0; table_join_options= 0; select_lock= select_lock_type::NONE; in_sum_expr= with_wild= 0; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 27895af80d8..8b4bdc3a19e 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -253,6 +253,12 @@ enum enum_view_suid VIEW_SUID_DEFAULT= 2 }; +enum enum_nmresolve +{ + NMRESOLVE_NONE= 0, + NMRESOLVE_NORMAL= 1, + NMRESOLVE_ALIAS= 2 +}; enum plsql_cursor_attr_t { @@ -1119,6 +1125,8 @@ class st_select_lex: public st_select_lex_node Item *prep_having;/* saved HAVING clause for prepared statement processing */ Item *cond_pushed_into_where; /* condition pushed into WHERE */ Item *cond_pushed_into_having; /* condition pushed into HAVING */ + Item **current_select_list_fix; /* during prepare reference on current + select list element */ /* nest_levels are local to the query or VIEW, @@ -3448,6 +3456,8 @@ struct LEX: public Query_tables_list uint8 context_analysis_only; uint8 lex_options; // see OPTION_LEX_* + enum_nmresolve nm_resolve_mode; + Alter_info alter_info; Lex_prepared_stmt prepared_stmt; /* diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 0ed21fc12b1..59006638294 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -4206,7 +4206,6 @@ bool Prepared_statement::prepare(const char *packet, uint packet_len) if (! (lex= new (mem_root) st_lex_local)) DBUG_RETURN(TRUE); lex->stmt_lex= lex; - if (set_db(&thd->db)) DBUG_RETURN(TRUE); @@ -4263,6 +4262,10 @@ bool Prepared_statement::prepare(const char *packet, uint packet_len) thd->is_error() || init_param_array(this)); + lex->nm_resolve_mode= (thd->variables.sql_mode & MODE_ALIASES_IN_WHERE ? + NMRESOLVE_ALIAS : + NMRESOLVE_NORMAL); + if (thd->security_ctx->password_expired && lex->sql_command != SQLCOM_SET_OPTION) { diff --git a/sql/sql_select.cc b/sql/sql_select.cc index a7ba0c9ffff..9d40e238cbf 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1392,8 +1392,16 @@ JOIN::prepare(TABLE_LIST *tables_init, COND *conds_init, uint og_num, } } + if ((thd->lex->nm_resolve_mode == NMRESOLVE_ALIAS || + (thd->lex->nm_resolve_mode == NMRESOLVE_NONE && + thd->variables.sql_mode & MODE_ALIASES_IN_WHERE)) && + pre_setup_aliases(thd, fields_list, &conds, + &select_lex->current_select_list_fix)) + DBUG_RETURN(-1); + if (setup_fields(thd, ref_ptrs, fields_list, MARK_COLUMNS_READ, - &all_fields, &select_lex->pre_fix, 1)) + &all_fields, &select_lex->pre_fix, 1, + &select_lex->current_select_list_fix)) DBUG_RETURN(-1); thd->lex->current_select->context_analysis_place= save_place; rand_table_in_field_list= select_lex->select_list_tables & RAND_TABLE_BIT; diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index d50eddb6652..2da40aa976d 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -3691,7 +3691,7 @@ static const char *sql_mode_names[]= "ALLOW_INVALID_DATES", "ERROR_FOR_DIVISION_BY_ZERO", "TRADITIONAL", "NO_AUTO_CREATE_USER", "HIGH_NOT_PRECEDENCE", "NO_ENGINE_SUBSTITUTION", "PAD_CHAR_TO_FULL_LENGTH", "EMPTY_STRING_IS_NULL", "SIMULTANEOUS_ASSIGNMENT", - "TIME_ROUND_FRACTIONAL", + "TIME_ROUND_FRACTIONAL", "ALIASES_IN_WHERE", 0 };

1 0

[Commits] 13ec63ff341: Update RocksDB: TEST PATCH: Range Locking Lazy Lock Release
by psergey 14 Jun '21

14 Jun '21

revision-id: 13ec63ff3410ab15066889cb571815926aee8da4 (percona-202102-60-g13ec63ff341) parent(s): d9c44fd063f7f537f67d9830b6d84891e3a8dc1e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-14 22:29:18 +0300 message: Update RocksDB: TEST PATCH: Range Locking Lazy Lock Release --- rocksdb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/rocksdb b/rocksdb index 56846855b35..50e44623e44 160000 --- a/rocksdb +++ b/rocksdb @@ -1 +1 @@ -Subproject commit 56846855b351a199d707e1b44c2d5f70e77d5535 +Subproject commit 50e44623e441cba43dbe4d20b25959ee37af5c5d

1 0

[Commits] 50e44623e: TEST PATCH: Range Locking Lazy Lock Release
by psergey 14 Jun '21

14 Jun '21

revision-id: 50e44623e441cba43dbe4d20b25959ee37af5c5d (v5.8-3396-g50e44623e) parent(s): 56846855b351a199d707e1b44c2d5f70e77d5535 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-06-14 22:28:25 +0300 message: TEST PATCH: Range Locking Lazy Lock Release STO, lock escalations, and overlapping locks do not work, yet. --- utilities/transactions/lock/lock_manager.h | 6 +- utilities/transactions/lock/lock_tracker.h | 4 + .../transactions/lock/point/point_lock_manager.cc | 5 +- .../transactions/lock/point/point_lock_manager.h | 4 +- .../transactions/lock/range/range_lock_manager.h | 5 +- .../range_tree/lib/locktree/concurrent_tree.cc | 9 +- .../range_tree/lib/locktree/concurrent_tree.h | 2 +- .../range/range_tree/lib/locktree/lock_request.cc | 10 +- .../range/range_tree/lib/locktree/lock_request.h | 1 + .../lock/range/range_tree/lib/locktree/locktree.cc | 93 ++++++++++---- .../lock/range/range_tree/lib/locktree/locktree.h | 12 +- .../lock/range/range_tree/lib/locktree/manager.cc | 4 +- .../range/range_tree/lib/locktree/range_buffer.cc | 21 +-- .../range/range_tree/lib/locktree/range_buffer.h | 11 +- .../lock/range/range_tree/lib/locktree/treenode.cc | 143 +++++++++++++++++++-- .../lock/range/range_tree/lib/locktree/treenode.h | 9 +- .../range/range_tree/range_tree_lock_manager.cc | 24 +++- .../range/range_tree/range_tree_lock_manager.h | 2 +- .../range/range_tree/range_tree_lock_tracker.cc | 8 +- .../range/range_tree/range_tree_lock_tracker.h | 2 +- utilities/transactions/pessimistic_transaction.cc | 11 +- .../transactions/pessimistic_transaction_db.cc | 10 +- .../transactions/pessimistic_transaction_db.h | 5 +- utilities/transactions/transaction_base.cc | 3 +- utilities/transactions/transaction_base.h | 2 +- 25 files changed, 314 insertions(+), 92 deletions(-) diff --git a/utilities/transactions/lock/lock_manager.h b/utilities/transactions/lock/lock_manager.h index a5ce1948c..e236faec2 100644 --- a/utilities/transactions/lock/lock_manager.h +++ b/utilities/transactions/lock/lock_manager.h @@ -42,11 +42,13 @@ class LockManager { // is responsible for calling UnLock() on this key. virtual Status TryLock(PessimisticTransaction* txn, ColumnFamilyId column_family_id, - const std::string& key, Env* env, bool exclusive) = 0; + const std::string& key, Env* env, bool exclusive, + void **lock_data=nullptr) = 0; // The range [start, end] are inclusive at both sides. virtual Status TryLock(PessimisticTransaction* txn, ColumnFamilyId column_family_id, const Endpoint& start, - const Endpoint& end, Env* env, bool exclusive) = 0; + const Endpoint& end, Env* env, bool exclusive, + void **lock_data=nullptr) = 0; // Unlock a key or a range locked by TryLock(). txn must be the same // Transaction that locked this key. diff --git a/utilities/transactions/lock/lock_tracker.h b/utilities/transactions/lock/lock_tracker.h index 5fa228a82..4952fe471 100644 --- a/utilities/transactions/lock/lock_tracker.h +++ b/utilities/transactions/lock/lock_tracker.h @@ -27,6 +27,8 @@ struct PointLockRequest { bool read_only = false; // Whether the lock is in exclusive mode. bool exclusive = true; + + void *lock_data = nullptr; }; // Request for locking a range of keys. @@ -37,6 +39,8 @@ struct RangeLockRequest { // The range to be locked Endpoint start_endp; Endpoint end_endp; + + void *lock_data = nullptr; }; struct PointLockStatus { diff --git a/utilities/transactions/lock/point/point_lock_manager.cc b/utilities/transactions/lock/point/point_lock_manager.cc index 79954d8f0..950fa9ea3 100644 --- a/utilities/transactions/lock/point/point_lock_manager.cc +++ b/utilities/transactions/lock/point/point_lock_manager.cc @@ -227,7 +227,7 @@ bool PointLockManager::IsLockExpired(TransactionID txn_id, Status PointLockManager::TryLock(PessimisticTransaction* txn, ColumnFamilyId column_family_id, const std::string& key, Env* env, - bool exclusive) { + bool exclusive, void**) { // Lookup lock map for this column family id std::shared_ptr<LockMap> lock_map_ptr = GetLockMap(column_family_id); LockMap* lock_map = lock_map_ptr.get(); @@ -704,7 +704,8 @@ Status PointLockManager::TryLock(PessimisticTransaction* /* txn */, ColumnFamilyId /* cf_id */, const Endpoint& /* start */, const Endpoint& /* end */, Env* /* env */, - bool /* exclusive */) { + bool /* exclusive */, + void **) { return Status::NotSupported( "PointLockManager does not support range locking"); } diff --git a/utilities/transactions/lock/point/point_lock_manager.h b/utilities/transactions/lock/point/point_lock_manager.h index 3c541eb3a..d701cc644 100644 --- a/utilities/transactions/lock/point/point_lock_manager.h +++ b/utilities/transactions/lock/point/point_lock_manager.h @@ -133,10 +133,10 @@ class PointLockManager : public LockManager { void RemoveColumnFamily(const ColumnFamilyHandle* cf) override; Status TryLock(PessimisticTransaction* txn, ColumnFamilyId column_family_id, - const std::string& key, Env* env, bool exclusive) override; + const std::string& key, Env* env, bool exclusive, void **lock_data) override; Status TryLock(PessimisticTransaction* txn, ColumnFamilyId column_family_id, const Endpoint& start, const Endpoint& end, Env* env, - bool exclusive) override; + bool exclusive, void **lock_data) override; void UnLock(PessimisticTransaction* txn, const LockTracker& tracker, Env* env) override; diff --git a/utilities/transactions/lock/range/range_lock_manager.h b/utilities/transactions/lock/range/range_lock_manager.h index 91619934b..70827b36a 100644 --- a/utilities/transactions/lock/range/range_lock_manager.h +++ b/utilities/transactions/lock/range/range_lock_manager.h @@ -20,9 +20,10 @@ class RangeLockManagerBase : public LockManager { // range using LockManager::TryLock; Status TryLock(PessimisticTransaction* txn, ColumnFamilyId column_family_id, - const std::string& key, Env* env, bool exclusive) override { + const std::string& key, Env* env, bool exclusive, + void **lock_data) override { Endpoint endp(key.data(), key.size(), false); - return TryLock(txn, column_family_id, endp, endp, env, exclusive); + return TryLock(txn, column_family_id, endp, endp, env, exclusive, lock_data); } }; diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/concurrent_tree.cc b/utilities/transactions/lock/range/range_tree/lib/locktree/concurrent_tree.cc index 5110cd482..a794dc43f 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/concurrent_tree.cc +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/concurrent_tree.cc @@ -101,7 +101,7 @@ void concurrent_tree::locked_keyrange::acquire(const keyrange &range) { bool concurrent_tree::locked_keyrange::add_shared_owner(const keyrange &range, TXNID new_owner) { - return m_subtree->insert(range, new_owner, /*is_shared*/ true); + return m_subtree->insert(range, new_owner, /*is_shared*/ true, NULL /*lock_data*/); } void concurrent_tree::locked_keyrange::release(void) { @@ -109,12 +109,15 @@ void concurrent_tree::locked_keyrange::release(void) { } void concurrent_tree::locked_keyrange::insert(const keyrange &range, - TXNID txnid, bool is_shared) { + TXNID txnid, bool is_shared, + void **lock_data) { // empty means no children, and only the root should ever be empty if (m_subtree->is_empty()) { m_subtree->set_range_and_txnid(range, txnid, is_shared); + if (lock_data) + *lock_data= NULL; //psergey-todo: inserted root is here } else { - m_subtree->insert(range, txnid, is_shared); + m_subtree->insert(range, txnid, is_shared, lock_data); } } diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/concurrent_tree.h b/utilities/transactions/lock/range/range_tree/lib/locktree/concurrent_tree.h index e1bfb86c5..5e2d4705e 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/concurrent_tree.h +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/concurrent_tree.h @@ -120,7 +120,7 @@ class concurrent_tree { // inserts the given range into the tree, with an associated txnid. // requires: range does not overlap with anything in this locked_keyrange // rationale: caller is responsible for only inserting unique ranges - void insert(const keyrange &range, TXNID txnid, bool is_shared); + void insert(const keyrange &range, TXNID txnid, bool is_shared, void **lock_data); // effect: removes the given range from the tree. // - txnid=TXNID_ANY means remove the range no matter what its diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/lock_request.cc b/utilities/transactions/lock/range/range_tree/lib/locktree/lock_request.cc index ec7bd04dc..fba48ad84 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/lock_request.cc +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/lock_request.cc @@ -196,13 +196,14 @@ int lock_request::start(void) { txnid_set conflicts; conflicts.create(); + acquired_lock_node= nullptr; // psergey if (m_type == type::WRITE) { r = m_lt->acquire_write_lock(m_txnid, m_left_key, m_right_key, &conflicts, - m_big_txn); + m_big_txn, &acquired_lock_node); } else { invariant(m_type == type::READ); r = m_lt->acquire_read_lock(m_txnid, m_left_key, m_right_key, &conflicts, - m_big_txn); + m_big_txn, &acquired_lock_node); } // if the lock is not granted, save it to the set of lock requests @@ -329,12 +330,13 @@ int lock_request::retry(lock_wait_infos *conflicts_collector) { txnid_set conflicts; conflicts.create(); + acquired_lock_node= nullptr; // psergey if (m_type == type::WRITE) { r = m_lt->acquire_write_lock(m_txnid, m_left_key, m_right_key, &conflicts, - m_big_txn); + m_big_txn, &acquired_lock_node); } else { r = m_lt->acquire_read_lock(m_txnid, m_left_key, m_right_key, &conflicts, - m_big_txn); + m_big_txn, &acquired_lock_node); } // if the acquisition succeeded then remove ourselves from the diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/lock_request.h b/utilities/transactions/lock/range/range_tree/lib/locktree/lock_request.h index 3544f102f..2cc2c189e 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/lock_request.h +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/lock_request.h @@ -152,6 +152,7 @@ class lock_request { void kill_waiter(void); static void kill_waiter(locktree *lt, void *extra); + void *acquired_lock_node; private: enum state { UNINITIALIZED, diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/locktree.cc b/utilities/transactions/lock/range/range_tree/lib/locktree/locktree.cc index c238b0204..3ba04b79b 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/locktree.cc +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/locktree.cc @@ -224,9 +224,10 @@ static void remove_row_lock_from_tree(concurrent_tree::locked_keyrange *lkr, // the memory tracker of this newly acquired lock. static void insert_row_lock_into_tree(concurrent_tree::locked_keyrange *lkr, const row_lock &lock, - locktree_manager *mgr) { + locktree_manager *mgr, + void **lock_data) { uint64_t mem_used = row_lock_size_in_tree(lock); - lkr->insert(lock.range, lock.txnid, lock.is_shared); + lkr->insert(lock.range, lock.txnid, lock.is_shared, lock_data); if (mgr != nullptr) { mgr->note_mem_used(mem_used); } @@ -297,7 +298,7 @@ void locktree::sto_migrate_buffer_ranges_to_tree(void *prepared_lkr) { sto_lkr.prepare(&sto_rangetree); int r = acquire_lock_consolidated(&sto_lkr, m_sto_txnid, rec.get_left_key(), rec.get_right_key(), - rec.get_exclusive_flag(), nullptr); + rec.get_exclusive_flag(), nullptr, nullptr); invariant_zero(r); sto_lkr.release(); iter.next(); @@ -311,7 +312,9 @@ void locktree::sto_migrate_buffer_ranges_to_tree(void *prepared_lkr) { TxnidVector *owners) { // There can't be multiple owners in STO mode invariant_zero(owners); - dst_lkr->insert(range, txnid, is_shared); + // psergey-todo: here is the migration from STO to non-STO, without + // psergey-todo:remembering the ranges... + dst_lkr->insert(range, txnid, is_shared, NULL); return true; } } migrate_fn; @@ -329,7 +332,9 @@ bool locktree::sto_try_acquire(void *prepared_lkr, TXNID txnid, const DBT *left_key, const DBT *right_key, bool is_write_request) { if (m_rangetree->is_empty() && m_sto_buffer.is_empty() && - toku_unsafe_fetch(m_sto_score) >= STO_SCORE_THRESHOLD) { + toku_unsafe_fetch(m_sto_score) >= STO_SCORE_THRESHOLD && + false // psergey: disable STO optimization + ) { // We can do the optimization because the rangetree is empty, and // we know its worth trying because the sto score is big enough. sto_begin(txnid); @@ -412,7 +417,8 @@ int locktree::acquire_lock_consolidated(void *prepared_lkr, TXNID txnid, const DBT *left_key, const DBT *right_key, bool is_write_request, - txnid_set *conflicts) { + txnid_set *conflicts, + void **lock_data) { int r = 0; concurrent_tree::locked_keyrange *lkr; @@ -471,19 +477,35 @@ int locktree::acquire_lock_consolidated(void *prepared_lkr, TXNID txnid, // requested range into one dominating range. then we insert the dominating // range. bool all_shared = !is_write_request; + bool need_insert = true; for (size_t i = 0; i < num_overlapping_row_locks; i++) { row_lock overlapping_lock = overlapping_row_locks.fetch_unchecked(i); invariant(overlapping_lock.txnid == txnid); - requested_range.extend(m_cmp, overlapping_lock.range); - remove_row_lock_from_tree(lkr, overlapping_lock, TXNID_ANY, m_mgr); - all_shared = all_shared && overlapping_lock.is_shared; + + // psergey: dont "remove and add back" if we're getting the same lock + // as we already have + keyrange::comparison c = requested_range.compare(m_cmp, overlapping_lock.range); + if (num_overlapping_row_locks == 1 && c == keyrange::EQUALS && + !overlapping_lock.is_shared == is_write_request) { + // This is the same lock as we already have + need_insert = false; + *lock_data=(void*)0x1; // Reused + break; + } else { + need_insert = true; + requested_range.extend(m_cmp, overlapping_lock.range); + remove_row_lock_from_tree(lkr, overlapping_lock, TXNID_ANY, m_mgr); + all_shared = all_shared && overlapping_lock.is_shared; + } + } + + if (need_insert) { + row_lock new_lock = {.range = requested_range, + .txnid = txnid, + .is_shared = all_shared, + .owners = nullptr}; + insert_row_lock_into_tree(lkr, new_lock, m_mgr, lock_data); } - - row_lock new_lock = {.range = requested_range, - .txnid = txnid, - .is_shared = all_shared, - .owners = nullptr}; - insert_row_lock_into_tree(lkr, new_lock, m_mgr); } else { r = DB_LOCK_NOTGRANTED; } @@ -498,7 +520,8 @@ int locktree::acquire_lock_consolidated(void *prepared_lkr, TXNID txnid, // transactions that conflict with this request. int locktree::acquire_lock(bool is_write_request, TXNID txnid, const DBT *left_key, const DBT *right_key, - txnid_set *conflicts) { + txnid_set *conflicts, + void **lock_data) { int r = 0; // we are only supporting write locks for simplicity @@ -514,7 +537,7 @@ int locktree::acquire_lock(bool is_write_request, TXNID txnid, sto_try_acquire(&lkr, txnid, left_key, right_key, is_write_request); if (!acquired) { r = acquire_lock_consolidated(&lkr, txnid, left_key, right_key, - is_write_request, conflicts); + is_write_request, conflicts, lock_data); } lkr.release(); @@ -523,13 +546,14 @@ int locktree::acquire_lock(bool is_write_request, TXNID txnid, int locktree::try_acquire_lock(bool is_write_request, TXNID txnid, const DBT *left_key, const DBT *right_key, - txnid_set *conflicts, bool big_txn) { + txnid_set *conflicts, bool big_txn, + void **lock_data) { // All ranges in the locktree must have left endpoints <= right endpoints. // Range comparisons rely on this fact, so we make a paranoid invariant here. paranoid_invariant(m_cmp(left_key, right_key) <= 0); int r = m_mgr == nullptr ? 0 : m_mgr->check_current_lock_constraints(big_txn); if (r == 0) { - r = acquire_lock(is_write_request, txnid, left_key, right_key, conflicts); + r = acquire_lock(is_write_request, txnid, left_key, right_key, conflicts, lock_data); } return r; } @@ -537,15 +561,15 @@ int locktree::try_acquire_lock(bool is_write_request, TXNID txnid, // the locktree silently upgrades read locks to write locks for simplicity int locktree::acquire_read_lock(TXNID txnid, const DBT *left_key, const DBT *right_key, txnid_set *conflicts, - bool big_txn) { + bool big_txn, void **lock_data) { return try_acquire_lock(false, txnid, left_key, right_key, conflicts, - big_txn); + big_txn, lock_data); } int locktree::acquire_write_lock(TXNID txnid, const DBT *left_key, const DBT *right_key, txnid_set *conflicts, - bool big_txn) { - return try_acquire_lock(true, txnid, left_key, right_key, conflicts, big_txn); + bool big_txn, void **lock_data) { + return try_acquire_lock(true, txnid, left_key, right_key, conflicts, big_txn, lock_data); } // typedef void (*dump_callback)(void *cdata, const DBT *left, const DBT *right, @@ -736,7 +760,26 @@ void locktree::release_locks(TXNID txnid, const range_buffer *ranges, // Range comparisons rely on this fact, so we make a paranoid invariant // here. paranoid_invariant(m_cmp(left_key, right_key) <= 0); - remove_overlapping_locks_for_txnid(txnid, left_key, right_key); + + + //psergey-todo: here, check rec._header.lock_data... + void *lock_data= rec.get_lock_data(); + if (lock_data == (void*)0x1) { + // Do nothing. Double-added nodea + } else if (lock_data) { + // psergey-todo: new removal procedure. + // The caller will call retry_all_lock_requests. + // We just set m_is_deleted... + treenode *node= (treenode*)lock_data; + node->m_is_deleted.store(1); + } else { + // psergey-todo: this should be the exception. It should locate the + // node and set m_is_deleted=1... + fprintf(stderr, "AAAA wrong removal!\n"); + assert(0); + remove_overlapping_locks_for_txnid(txnid, left_key, right_key); + } + iter.next(); } // Increase the sto score slightly. Eventually it will hit @@ -955,7 +998,7 @@ void locktree::escalate(lt_escalate_cb after_escalate_callback, .txnid = current_txnid, .is_shared = !rec.get_exclusive_flag(), .owners = nullptr}; - insert_row_lock_into_tree(&lkr, lock, m_mgr); + insert_row_lock_into_tree(&lkr, lock, m_mgr, NULL); iter.next(); } diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/locktree.h b/utilities/transactions/lock/range/range_tree/lib/locktree/locktree.h index 3e438f502..4d7d5e243 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/locktree.h +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/locktree.h @@ -309,7 +309,7 @@ class locktree { // note: Read locks cannot be shared between txnids, as one would expect. // This is for simplicity since read locks are rare in MySQL. int acquire_read_lock(TXNID txnid, const DBT *left_key, const DBT *right_key, - txnid_set *conflicts, bool big_txn); + txnid_set *conflicts, bool big_txn, void **lock_data); // effect: Attempts to grant a write lock for the range of keys between // [left_key, right_key]. returns: If the lock cannot be granted, return @@ -318,7 +318,7 @@ class locktree { // the range. If the locktree cannot create more locks, return // TOKUDB_OUT_OF_LOCKS. int acquire_write_lock(TXNID txnid, const DBT *left_key, const DBT *right_key, - txnid_set *conflicts, bool big_txn); + txnid_set *conflicts, bool big_txn, void **lock_data); // effect: populate the conflicts set with the txnids that would preventing // the given txnid from getting a lock on [left_key, right_key] @@ -540,14 +540,16 @@ class locktree { int acquire_lock_consolidated(void *prepared_lkr, TXNID txnid, const DBT *left_key, const DBT *right_key, - bool is_write_request, txnid_set *conflicts); + bool is_write_request, txnid_set *conflicts, + void **lock_data); int acquire_lock(bool is_write_request, TXNID txnid, const DBT *left_key, - const DBT *right_key, txnid_set *conflicts); + const DBT *right_key, txnid_set *conflicts, + void **lock_data); int try_acquire_lock(bool is_write_request, TXNID txnid, const DBT *left_key, const DBT *right_key, txnid_set *conflicts, - bool big_txn); + bool big_txn, void **lock_data); friend class locktree_unit_test; friend class manager_unit_test; diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/manager.cc b/utilities/transactions/lock/range/range_tree/lib/locktree/manager.cc index 4186182be..ed2aae3d2 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/manager.cc +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/manager.cc @@ -303,11 +303,11 @@ void locktree_manager::note_mem_released(uint64_t mem_released) { } bool locktree_manager::out_of_locks(void) const { - return m_current_lock_memory >= m_max_lock_memory; + return false;// m_current_lock_memory >= m_max_lock_memory; } bool locktree_manager::over_big_threshold(void) { - return m_current_lock_memory >= m_max_lock_memory / 2; + return false; //m_current_lock_memory >= m_max_lock_memory / 2; } int locktree_manager::iterate_pending_lock_requests( diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/range_buffer.cc b/utilities/transactions/lock/range/range_tree/lib/locktree/range_buffer.cc index 1e1d23ef8..e34728659 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/range_buffer.cc +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/range_buffer.cc @@ -72,8 +72,10 @@ bool range_buffer::record_header::right_is_infinite(void) const { void range_buffer::record_header::init(const DBT *left_key, const DBT *right_key, - bool is_exclusive) { + bool is_exclusive, + void *lock_data_arg) { is_exclusive_lock = is_exclusive; + lock_data = lock_data_arg; left_neg_inf = left_key == toku_dbt_negative_infinity(); left_pos_inf = left_key == toku_dbt_positive_infinity(); left_key_size = toku_dbt_is_infinite(left_key) ? 0 : left_key->size; @@ -88,6 +90,9 @@ void range_buffer::record_header::init(const DBT *left_key, } } +void* range_buffer::iterator::record::get_lock_data() const { + return _header.lock_data; +} const DBT *range_buffer::iterator::record::get_left_key(void) const { if (_header.left_neg_inf) { return toku_dbt_negative_infinity(); @@ -195,15 +200,15 @@ void range_buffer::create(void) { } void range_buffer::append(const DBT *left_key, const DBT *right_key, - bool is_write_request) { + bool is_write_request, void *lock_data) { // if the keys are equal, then only one copy is stored. if (toku_dbt_equals(left_key, right_key)) { invariant(left_key->size <= MAX_KEY_SIZE); - append_point(left_key, is_write_request); + append_point(left_key, is_write_request, lock_data); } else { invariant(left_key->size <= MAX_KEY_SIZE); invariant(right_key->size <= MAX_KEY_SIZE); - append_range(left_key, right_key, is_write_request); + append_range(left_key, right_key, is_write_request, lock_data); } _num_ranges++; } @@ -219,13 +224,13 @@ int range_buffer::get_num_ranges(void) const { return _num_ranges; } void range_buffer::destroy(void) { _arena.destroy(); } void range_buffer::append_range(const DBT *left_key, const DBT *right_key, - bool is_exclusive) { + bool is_exclusive, void *lock_data) { size_t record_length = sizeof(record_header) + left_key->size + right_key->size; char *buf = reinterpret_cast<char *>(_arena.malloc_from_arena(record_length)); record_header h; - h.init(left_key, right_key, is_exclusive); + h.init(left_key, right_key, is_exclusive, lock_data); // serialize the header memcpy(buf, &h, sizeof(record_header)); @@ -243,12 +248,12 @@ void range_buffer::append_range(const DBT *left_key, const DBT *right_key, } } -void range_buffer::append_point(const DBT *key, bool is_exclusive) { +void range_buffer::append_point(const DBT *key, bool is_exclusive, void *lock_data) { size_t record_length = sizeof(record_header) + key->size; char *buf = reinterpret_cast<char *>(_arena.malloc_from_arena(record_length)); record_header h; - h.init(key, nullptr, is_exclusive); + h.init(key, nullptr, is_exclusive, lock_data); // serialize the header memcpy(buf, &h, sizeof(record_header)); diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/range_buffer.h b/utilities/transactions/lock/range/range_tree/lib/locktree/range_buffer.h index 76e28d747..318cbef69 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/range_buffer.h +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/range_buffer.h @@ -79,12 +79,13 @@ class range_buffer { uint16_t left_key_size; uint16_t right_key_size; bool is_exclusive_lock; + void *lock_data; bool left_is_infinite(void) const; bool right_is_infinite(void) const; - void init(const DBT *left_key, const DBT *right_key, bool is_exclusive); + void init(const DBT *left_key, const DBT *right_key, bool is_exclusive, void *lock_data_arg); }; // PORT static_assert(sizeof(record_header) == 8, "record header format is // off"); @@ -108,6 +109,8 @@ class range_buffer { // get a read-only pointer to the right key of this record's range const DBT *get_right_key(void) const; + void *get_lock_data() const; + // how big is this record? this tells us where the next record is size_t size(void) const; @@ -150,7 +153,7 @@ class range_buffer { // append a left/right key range to the buffer. // if the keys are equal, then only one copy is stored. void append(const DBT *left_key, const DBT *right_key, - bool is_write_request = false); + bool is_write_request = false, void *lock_data=nullptr); // is this range buffer empty? bool is_empty(void) const; @@ -168,11 +171,11 @@ class range_buffer { int _num_ranges; void append_range(const DBT *left_key, const DBT *right_key, - bool is_write_request); + bool is_write_request, void *lock_data=nullptr); // append a point to the buffer. this is the space/time saving // optimization for key ranges where left == right. - void append_point(const DBT *key, bool is_write_request); + void append_point(const DBT *key, bool is_write_request, void *lock_data=nullptr); }; } /* namespace toku */ diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.cc b/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.cc index bcdaa672f..bf82e1d37 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.cc +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.cc @@ -1,5 +1,5 @@ /* -*- mode: C++; c-basic-offset: 4; indent-tabs-mode: nil -*- */ -// vim: ft=cpp:expandtab:ts=8:sw=4:softtabstop=4: +// vim: ft=cpp:expandtab:ts=8:sw=2:softtabstop=2: #ifndef ROCKSDB_LITE #ifndef OS_WIN #ident "$Id$" @@ -84,6 +84,8 @@ void treenode::init(const comparator *cmp) { m_is_shared = false; m_owners = nullptr; + m_is_deleted= false; + // use an adaptive mutex at each node since we expect the time the // lock is held to be relatively short compared to a context switch. // indeed, this improves performance at high thread counts considerably. @@ -135,6 +137,7 @@ treenode *treenode::alloc(const comparator *cmp, const keyrange &range, } void treenode::swap_in_place(treenode *node1, treenode *node2) { + assert(0); keyrange tmp_range = node1->m_range; TXNID tmp_txnid = node1->m_txnid; node1->m_range = node2->m_range; @@ -230,18 +233,22 @@ treenode *treenode::find_node_with_overlapping_child( } } -bool treenode::insert(const keyrange &range, TXNID txnid, bool is_shared) { +bool treenode::insert(const keyrange &range, TXNID txnid, bool is_shared, void **lock_data) { int rc = true; // choose a child to check. if that child is null, then insert the new node // there. otherwise recur down that child's subtree keyrange::comparison c = range.compare(*m_cmp, m_range); + if (lock_data) + *lock_data= NULL; if (c == keyrange::comparison::LESS_THAN) { treenode *left_child = lock_and_rebalance_left(); if (left_child == nullptr) { left_child = treenode::alloc(m_cmp, range, txnid, is_shared); + if (lock_data) + *lock_data= left_child; m_left_child.set(left_child); } else { - left_child->insert(range, txnid, is_shared); + left_child->insert(range, txnid, is_shared, lock_data); left_child->mutex_unlock(); } } else if (c == keyrange::comparison::GREATER_THAN) { @@ -249,9 +256,11 @@ bool treenode::insert(const keyrange &range, TXNID txnid, bool is_shared) { treenode *right_child = lock_and_rebalance_right(); if (right_child == nullptr) { right_child = treenode::alloc(m_cmp, range, txnid, is_shared); + if (lock_data) + *lock_data= right_child; m_right_child.set(right_child); } else { - right_child->insert(range, txnid, is_shared); + right_child->insert(range, txnid, is_shared, lock_data); right_child->mutex_unlock(); } } else if (c == keyrange::comparison::EQUALS) { @@ -286,6 +295,13 @@ treenode *treenode::find_rightmost_child(treenode **parent) { return find_child_at_extreme(1, parent); } +/* + psergey: remove this subtree. + Entry: this node is locked (if it wasn't, it wouldn't be possible to return its value) + Return: + the node that should be put in place of this node. + (at the moment, return value is either nullptr or "this") +*/ treenode *treenode::remove_root_of_subtree() { // if this node has no children, just free it and return null if (m_left_child.ptr == nullptr && m_right_child.ptr == nullptr) { @@ -335,6 +351,64 @@ treenode *treenode::remove_root_of_subtree() { return this; } +// psergey: this should remove this node but without use of swap_in_place +// operations. +// +// @return +// The node that should be put instead of this node. can be NULL. + +treenode *treenode::remove_root_of_subtree2() { + // if this node has no children, just free it and return null + if (m_left_child.ptr == nullptr && m_right_child.ptr == nullptr) { + // treenode::free requires that non-root nodes are unlocked + if (!is_root()) { + mutex_unlock(); + } + treenode::free(this); + return nullptr; + } + + // we have a child, so get either the in-order successor or + // predecessor of this node to be our replacement. + // replacement_parent is updated by the find functions as + // they recur down the tree, so initialize it to this. + treenode *child, *replacement; + treenode *replacement_parent = this; + if (m_left_child.ptr != nullptr) { + child = m_left_child.get_locked(); + replacement = child->find_rightmost_child(&replacement_parent); + invariant(replacement == child || replacement_parent != this); + + // detach the replacement from its parent + if (replacement_parent == this) { + m_left_child = replacement->m_left_child; + } else { + replacement_parent->m_right_child = replacement->m_left_child; + } + } else { + child = m_right_child.get_locked(); + replacement = child->find_leftmost_child(&replacement_parent); + invariant(replacement == child || replacement_parent != this); + + // detach the replacement from its parent + if (replacement_parent == this) { + m_right_child = replacement->m_right_child; + } else { + replacement_parent->m_left_child = replacement->m_right_child; + } + } + child->mutex_unlock(); + + + //DONT: + // swap in place with the detached replacement, then destroy it + //treenode::swap_in_place(replacement, this); + //treenode::free(replacement); + + return replacement; +} + + void treenode::recursive_remove(void) { treenode *left = m_left_child.ptr; if (left) { @@ -365,7 +439,7 @@ void treenode::remove_shared_owner(TXNID txnid) { m_owners = nullptr; } } - +// psergey: return the node that should be put in place of this node... treenode *treenode::remove(const keyrange &range, TXNID txnid) { treenode *child; // if the range is equal to this node's range, then just remove @@ -492,26 +566,73 @@ treenode *treenode::maybe_rebalance(void) { return new_root; } +treenode *maybe_delete(treenode *node); + treenode *treenode::lock_and_rebalance_left(void) { treenode *child = m_left_child.get_locked(); + if (child) { - treenode *new_root = child->maybe_rebalance(); - m_left_child.set(new_root); - child = new_root; + child = maybe_delete(child); + + if (child) { + treenode *new_root = child->maybe_rebalance(); + if (new_root != child) + child= maybe_delete(new_root); + } } + + m_left_child.set(child); return child; } treenode *treenode::lock_and_rebalance_right(void) { treenode *child = m_right_child.get_locked(); + if (child) { - treenode *new_root = child->maybe_rebalance(); - m_right_child.set(new_root); - child = new_root; + child = maybe_delete(child); + + if (child) { + treenode *new_root = child->maybe_rebalance(); + if (new_root != child) + child= maybe_delete(new_root); + } } + + m_right_child.set(child); return child; } +/* + Lazily remove the node. + + @param + node. The node is locked. Its parent is also locked so we are allowed + to modify it. + Nobody's waiting on the node's mutex (because we've locked the parent, + they are waiting on its parent) + + @return + The same node that passed. + + The node to put instead of the removed node. Can return NULL. + The returned node must be locked. +*/ + +treenode *maybe_delete(treenode *node) { + + while (node) { + + if (!node->m_is_deleted.load()) + return node; // Not deleted. Do nothing + + node = node->remove_root_of_subtree2(); + // Remove this node and return a tree made of its children... + if (node) + node->mutex_lock(); + } + return node; +} + void treenode::child_ptr::set(treenode *node) { ptr = node; depth_est = ptr ? ptr->get_depth_estimate() : 0; diff --git a/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.h b/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.h index ec25a8c58..457e0d2af 100644 --- a/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.h +++ b/utilities/transactions/lock/range/range_tree/lib/locktree/treenode.h @@ -54,6 +54,7 @@ Copyright (c) 2006, 2015, Percona and/or its affiliates. All rights reserved. #pragma once #include <string.h> +#include <atomic> #include "../ft/comparator.h" #include "../portability/memory.h" @@ -161,7 +162,7 @@ class treenode { // effect: inserts the given range and txnid into a subtree, recursively // requires: range does not overlap with any node below the subtree - bool insert(const keyrange &range, TXNID txnid, bool is_shared); + bool insert(const keyrange &range, TXNID txnid, bool is_shared, void **lock_data); // effect: removes the given range from the subtree // requires: range exists in the subtree @@ -172,6 +173,8 @@ class treenode { // requires: every node at and below this node is unlocked void recursive_remove(void); + std::atomic<bool> m_is_deleted; + private: // the child_ptr is a light abstraction for the locking of // a child and the maintenence of its depth estimate. @@ -259,6 +262,8 @@ class treenode { // returns: the new root of the subtree treenode *remove_root_of_subtree(void); + treenode *remove_root_of_subtree2(void); + // requires: subtree is non-empty, direction is not 0 // returns: the child of the subtree at either the left or rightmost extreme treenode *find_child_at_extreme(int direction, treenode **parent); @@ -297,6 +302,8 @@ class treenode { static void swap_in_place(treenode *node1, treenode *node2); friend class concurrent_tree_unit_test; + + friend treenode *maybe_delete(treenode *node); }; } /* namespace toku */ diff --git a/utilities/transactions/lock/range/range_tree/range_tree_lock_manager.cc b/utilities/transactions/lock/range/range_tree/range_tree_lock_manager.cc index 1c07f0992..9cfff4968 100644 --- a/utilities/transactions/lock/range/range_tree/range_tree_lock_manager.cc +++ b/utilities/transactions/lock/range/range_tree/range_tree_lock_manager.cc @@ -67,7 +67,8 @@ Status RangeTreeLockManager::TryLock(PessimisticTransaction* txn, uint32_t column_family_id, const Endpoint& start_endp, const Endpoint& end_endp, Env*, - bool exclusive) { + bool exclusive, + void **lock_data) { toku::lock_request request; request.create(mutex_factory_); DBT start_key_dbt, end_key_dbt; @@ -134,6 +135,9 @@ Status RangeTreeLockManager::TryLock(PessimisticTransaction* txn, request.destroy(); switch (r) { case 0: + // + if (lock_data) + *lock_data= request.acquired_lock_node; break; // fall through case DB_LOCK_NOTGRANTED: return Status::TimedOut(Status::SubCode::kLockTimeout); @@ -363,6 +367,24 @@ void RangeTreeLockManager::AddColumnFamily(const ColumnFamilyHandle* cfh) { cmp.destroy(); ltree_map_.insert({column_family_id, MakeLockTreePtr(ltree)}); + + //if (column_family_id) + { + const char *b="Fake-Root"; + std::string key; + serialize_endpoint(Endpoint(Slice(b, strlen(b)), false), &key); + DBT key_dbt; + toku_fill_dbt(&key_dbt, key.data(), key.size()); + TXNID dummy_txn_id(123); + void *dummy; + txnid_set conflicts; + conflicts.create(); + + ltree->acquire_write_lock(dummy_txn_id, &key_dbt, &key_dbt, &conflicts, + false/*big_txn*/, &dummy); + + conflicts.destroy(); + } } } diff --git a/utilities/transactions/lock/range/range_tree/range_tree_lock_manager.h b/utilities/transactions/lock/range/range_tree/range_tree_lock_manager.h index 5d55ded02..5df3de0e2 100644 --- a/utilities/transactions/lock/range/range_tree/range_tree_lock_manager.h +++ b/utilities/transactions/lock/range/range_tree/range_tree_lock_manager.h @@ -44,7 +44,7 @@ class RangeTreeLockManager : public RangeLockManagerBase, using LockManager::TryLock; Status TryLock(PessimisticTransaction* txn, ColumnFamilyId column_family_id, const Endpoint& start_endp, const Endpoint& end_endp, Env* env, - bool exclusive) override; + bool exclusive, void **lock_data) override; void UnLock(PessimisticTransaction* txn, const LockTracker& tracker, Env* env) override; diff --git a/utilities/transactions/lock/range/range_tree/range_tree_lock_tracker.cc b/utilities/transactions/lock/range/range_tree/range_tree_lock_tracker.cc index d138ed91f..0ac19e926 100644 --- a/utilities/transactions/lock/range/range_tree/range_tree_lock_tracker.cc +++ b/utilities/transactions/lock/range/range_tree/range_tree_lock_tracker.cc @@ -26,7 +26,7 @@ void RangeTreeLockTracker::Track(const PointLockRequest &lock_req) { serialize_endpoint(Endpoint(lock_req.key, false), &key); toku_fill_dbt(&key_dbt, key.data(), key.size()); RangeLockList *rl = getOrCreateList(); - rl->Append(lock_req.column_family_id, &key_dbt, &key_dbt); + rl->Append(lock_req.column_family_id, &key_dbt, &key_dbt, lock_req.lock_data); } void RangeTreeLockTracker::Track(const RangeLockRequest &lock_req) { @@ -40,7 +40,7 @@ void RangeTreeLockTracker::Track(const RangeLockRequest &lock_req) { toku_fill_dbt(&end_dbt, end_key.data(), end_key.size()); RangeLockList *rl = getOrCreateList(); - rl->Append(lock_req.column_family_id, &start_dbt, &end_dbt); + rl->Append(lock_req.column_family_id, &start_dbt, &end_dbt, lock_req.lock_data); } PointLockStatus RangeTreeLockTracker::GetPointLockStatus( @@ -58,7 +58,7 @@ PointLockStatus RangeTreeLockTracker::GetPointLockStatus( void RangeTreeLockTracker::Clear() { range_list_.reset(); } void RangeLockList::Append(ColumnFamilyId cf_id, const DBT *left_key, - const DBT *right_key) { + const DBT *right_key, void *lock_data) { MutexLock l(&mutex_); // Only the transaction owner thread calls this function. // The same thread does the lock release, so we can be certain nobody is @@ -70,7 +70,7 @@ void RangeLockList::Append(ColumnFamilyId cf_id, const DBT *left_key, it = buffers_.emplace(cf_id, std::make_shared<toku::range_buffer>()).first; it->second->create(); } - it->second->append(left_key, right_key); + it->second->append(left_key, right_key, true /*is_write_request*/, lock_data); } void RangeLockList::ReleaseLocks(RangeTreeLockManager *mgr, diff --git a/utilities/transactions/lock/range/range_tree/range_tree_lock_tracker.h b/utilities/transactions/lock/range/range_tree/range_tree_lock_tracker.h index 4ef48d252..08bd1d17f 100644 --- a/utilities/transactions/lock/range/range_tree/range_tree_lock_tracker.h +++ b/utilities/transactions/lock/range/range_tree/range_tree_lock_tracker.h @@ -40,7 +40,7 @@ class RangeLockList { RangeLockList() : releasing_locks_(false) {} - void Append(ColumnFamilyId cf_id, const DBT* left_key, const DBT* right_key); + void Append(ColumnFamilyId cf_id, const DBT* left_key, const DBT* right_key, void *lock_data); void ReleaseLocks(RangeTreeLockManager* mgr, PessimisticTransaction* txn, bool all_trx_locks); void ReplaceLocks(const toku::locktree* lt, const toku::range_buffer& buffer); diff --git a/utilities/transactions/pessimistic_transaction.cc b/utilities/transactions/pessimistic_transaction.cc index 7d4276972..166ea56f7 100644 --- a/utilities/transactions/pessimistic_transaction.cc +++ b/utilities/transactions/pessimistic_transaction.cc @@ -581,8 +581,9 @@ Status PessimisticTransaction::TryLock(ColumnFamilyHandle* column_family, // Lock this key if this transactions hasn't already locked it or we require // an upgrade. + void *lock_data= NULL; if (!previously_locked || lock_upgrade) { - s = txn_db_impl_->TryLock(this, cfh_id, key_str, exclusive); + s = txn_db_impl_->TryLock(this, cfh_id, key_str, exclusive, &lock_data); } SetSnapshotIfNeeded(); @@ -651,7 +652,7 @@ Status PessimisticTransaction::TryLock(ColumnFamilyHandle* column_family, // setting, and at a lower sequence number, so skipping here should be // safe. if (!assume_tracked) { - TrackKey(cfh_id, key_str, tracked_at_seq, read_only, exclusive); + TrackKey(cfh_id, key_str, tracked_at_seq, read_only, exclusive, lock_data); } else { #ifndef NDEBUG if (tracked_locks_->IsPointLockSupported()) { @@ -674,11 +675,11 @@ Status PessimisticTransaction::GetRangeLock(ColumnFamilyHandle* column_family, ColumnFamilyHandle* cfh = column_family ? column_family : db_impl_->DefaultColumnFamily(); uint32_t cfh_id = GetColumnFamilyID(cfh); - - Status s = txn_db_impl_->TryRangeLock(this, cfh_id, start_endp, end_endp); + void *lock_data; + Status s = txn_db_impl_->TryRangeLock(this, cfh_id, start_endp, end_endp, &lock_data); if (s.ok()) { - RangeLockRequest req{cfh_id, start_endp, end_endp}; + RangeLockRequest req{cfh_id, start_endp, end_endp, lock_data}; tracked_locks_->Track(req); } return s; diff --git a/utilities/transactions/pessimistic_transaction_db.cc b/utilities/transactions/pessimistic_transaction_db.cc index 2c69f7359..7813374c3 100644 --- a/utilities/transactions/pessimistic_transaction_db.cc +++ b/utilities/transactions/pessimistic_transaction_db.cc @@ -387,16 +387,18 @@ Status PessimisticTransactionDB::DropColumnFamily( Status PessimisticTransactionDB::TryLock(PessimisticTransaction* txn, uint32_t cfh_id, const std::string& key, - bool exclusive) { - return lock_manager_->TryLock(txn, cfh_id, key, GetEnv(), exclusive); + bool exclusive, + void **lock_data) { + return lock_manager_->TryLock(txn, cfh_id, key, GetEnv(), exclusive, lock_data); } Status PessimisticTransactionDB::TryRangeLock(PessimisticTransaction* txn, uint32_t cfh_id, const Endpoint& start_endp, - const Endpoint& end_endp) { + const Endpoint& end_endp, + void **lock_data) { return lock_manager_->TryLock(txn, cfh_id, start_endp, end_endp, GetEnv(), - /*exclusive=*/true); + /*exclusive=*/true, lock_data); } void PessimisticTransactionDB::UnLock(PessimisticTransaction* txn, diff --git a/utilities/transactions/pessimistic_transaction_db.h b/utilities/transactions/pessimistic_transaction_db.h index eb0dd2f05..24db46cac 100644 --- a/utilities/transactions/pessimistic_transaction_db.h +++ b/utilities/transactions/pessimistic_transaction_db.h @@ -98,9 +98,10 @@ class PessimisticTransactionDB : public TransactionDB { virtual Status DropColumnFamily(ColumnFamilyHandle* column_family) override; Status TryLock(PessimisticTransaction* txn, uint32_t cfh_id, - const std::string& key, bool exclusive); + const std::string& key, bool exclusive, void **lock_data=nullptr); Status TryRangeLock(PessimisticTransaction* txn, uint32_t cfh_id, - const Endpoint& start_endp, const Endpoint& end_endp); + const Endpoint& start_endp, const Endpoint& end_endp, + void **lock_data=nullptr); void UnLock(PessimisticTransaction* txn, const LockTracker& keys); void UnLock(PessimisticTransaction* txn, uint32_t cfh_id, diff --git a/utilities/transactions/transaction_base.cc b/utilities/transactions/transaction_base.cc index 49fa99d7d..055ea3723 100644 --- a/utilities/transactions/transaction_base.cc +++ b/utilities/transactions/transaction_base.cc @@ -555,13 +555,14 @@ uint64_t TransactionBaseImpl::GetNumKeys() const { void TransactionBaseImpl::TrackKey(uint32_t cfh_id, const std::string& key, SequenceNumber seq, bool read_only, - bool exclusive) { + bool exclusive, void *lock_data) { PointLockRequest r; r.column_family_id = cfh_id; r.key = key; r.seq = seq; r.read_only = read_only; r.exclusive = exclusive; + r.lock_data = lock_data; // Update map of all tracked keys for this transaction tracked_locks_->Track(r); diff --git a/utilities/transactions/transaction_base.h b/utilities/transactions/transaction_base.h index 2c5770d8a..76d24ca8f 100644 --- a/utilities/transactions/transaction_base.h +++ b/utilities/transactions/transaction_base.h @@ -258,7 +258,7 @@ class TransactionBaseImpl : public Transaction { // seqno is the earliest seqno this key was involved with this transaction. // readonly should be set to true if no data was written for this key void TrackKey(uint32_t cfh_id, const std::string& key, SequenceNumber seqno, - bool readonly, bool exclusive); + bool readonly, bool exclusive, void *lock_data=nullptr); // Called when UndoGetForUpdate determines that this key can be unlocked. virtual void UnlockGetForUpdate(ColumnFamilyHandle* column_family,

1 0