diff options
-rw-r--r-- | debian/libmariadb3.install | 1 | ||||
-rw-r--r-- | mysql-test/main/table_value_constr.result | 110 | ||||
-rw-r--r-- | mysql-test/main/table_value_constr.test | 57 | ||||
-rw-r--r-- | mysql-test/suite/compat/oracle/r/table_value_constr.result | 4 | ||||
-rw-r--r-- | mysys/lf_hash.c | 30 | ||||
-rw-r--r-- | sql/sql_lex.cc | 23 | ||||
-rw-r--r-- | sql/sql_lex.h | 2 | ||||
-rw-r--r-- | sql/sql_tvc.cc | 67 |
8 files changed, 247 insertions, 47 deletions
diff --git a/debian/libmariadb3.install b/debian/libmariadb3.install index 8636166a493..82c4b2a3f1d 100644 --- a/debian/libmariadb3.install +++ b/debian/libmariadb3.install @@ -3,3 +3,4 @@ usr/lib/mysql/plugin/client_ed25519.so usr/lib/mysql/plugin/dialog.so usr/lib/mysql/plugin/mysql_clear_password.so usr/lib/mysql/plugin/sha256_password.so +usr/lib/mysql/plugin/caching_sha2_password.so diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index f965fab0e7a..88780179a36 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -748,7 +748,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1 explain extended select * from t1 where a in (select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -983,7 +983,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1 explain extended select * from t1 where a = any (select * from (values (1),(2)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -2776,6 +2776,112 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where drop table t1; # +# MDEV-24910: TVC containing subquery used as a subselect +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (7), (1); +create table t2 (b int) engine=myisam; +insert into t2 values (1), (2); +select (values ((select 2))) from t2; +(values ((select 2))) +2 +2 +explain select (values ((select 2))) from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1249 Select 3 was reduced during optimization +prepare stmt from "select (values ((select 2))) from t2"; +execute stmt; +(values ((select 2))) +2 +2 +execute stmt; +(values ((select 2))) +2 +2 +deallocate prepare stmt; +select (values ((select * from t1 where a > 10))) from t2; +(values ((select * from t1 where a > 10))) +NULL +NULL +explain select (values ((select * from t1 where a > 10))) from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 +4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +prepare stmt from "select (values ((select * from t1 where a > 10))) from t2"; +execute stmt; +(values ((select * from t1 where a > 10))) +NULL +NULL +execute stmt; +(values ((select * from t1 where a > 10))) +NULL +NULL +deallocate prepare stmt; +create table t3 (a int); +insert into t3 values +(3), (7), (7), (1), (3), (9), (7), (9), (8), (7), (8); +create view v1 as select count(a) as c from t3 group by a; +select +(values ((select * from t3 where a in (select * from v1)))); +(values ((select * from t3 where a in (select * from v1)))) +1 +explain select +(values ((select * from t3 where a in (select * from v1)))); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +6 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t3 ALL NULL NULL NULL NULL 11 +3 SUBQUERY <subquery4> eq_ref distinct_key distinct_key 8 func 1 Using where +4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 11 +5 DERIVED t3 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort +prepare stmt from "select +(values ((select * from t3 where a in (select * from v1))))"; +execute stmt; +(values ((select * from t3 where a in (select * from v1)))) +1 +execute stmt; +(values ((select * from t3 where a in (select * from v1)))) +1 +deallocate prepare stmt; +select +(values ((select * from t3 +where a > 10 and a in (select * from v1)))); +(values ((select * from t3 +where a > 10 and a in (select * from v1)))) +NULL +explain select +(values ((select * from t3 +where a > 10 and a in (select * from v1)))); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +6 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t3 ALL NULL NULL NULL NULL 11 Using where +3 SUBQUERY <subquery4> eq_ref distinct_key distinct_key 8 func 1 Using where +4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 11 +5 DERIVED t3 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort +prepare stmt from "select +(values ((select * from t3 +where a > 10 and a in (select * from v1))))"; +execute stmt; +(values ((select * from t3 +where a > 10 and a in (select * from v1)))) +NULL +execute stmt; +(values ((select * from t3 +where a > 10 and a in (select * from v1)))) +NULL +deallocate prepare stmt; +drop view v1; +drop table t1,t2,t3; +# # End of 10.3 tests # # diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 4014a8b4d62..dd090f97f38 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1460,6 +1460,63 @@ eval explain $q3; drop table t1; --echo # +--echo # MDEV-24910: TVC containing subquery used as a subselect +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (3), (7), (1); +create table t2 (b int) engine=myisam; +insert into t2 values (1), (2); + +let $q1= +select (values ((select 2))) from t2; +eval $q1; +eval explain $q1; +eval prepare stmt from "$q1"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +let $q2= +select (values ((select * from t1 where a > 10))) from t2; +eval $q2; +eval explain $q2; +eval prepare stmt from "$q2"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +create table t3 (a int); +insert into t3 values + (3), (7), (7), (1), (3), (9), (7), (9), (8), (7), (8); + +create view v1 as select count(a) as c from t3 group by a; + +let $q3= +select +(values ((select * from t3 where a in (select * from v1)))); +eval $q3; +eval explain $q3; +eval prepare stmt from "$q3"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +let $q4= +select +(values ((select * from t3 + where a > 10 and a in (select * from v1)))); +eval $q4; +eval explain $q4; +eval prepare stmt from "$q4"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop view v1; +drop table t1,t2,t3; + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/suite/compat/oracle/r/table_value_constr.result b/mysql-test/suite/compat/oracle/r/table_value_constr.result index 4383845cd87..cb198764538 100644 --- a/mysql-test/suite/compat/oracle/r/table_value_constr.result +++ b/mysql-test/suite/compat/oracle/r/table_value_constr.result @@ -746,7 +746,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where 1 +Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where 1 explain extended select * from t1 where a in (select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -981,7 +981,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where 1 +Note 1003 /* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where 1 explain extended select * from t1 where a = any (select * from (values (1),(2)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra diff --git a/mysys/lf_hash.c b/mysys/lf_hash.c index e626e88b6fd..668c73fbbfe 100644 --- a/mysys/lf_hash.c +++ b/mysys/lf_hash.c @@ -32,10 +32,10 @@ /* An element of the list */ typedef struct { - intptr volatile link; /* a pointer to the next element in a list and a flag */ - uint32 hashnr; /* reversed hash number, for sorting */ + intptr link; /* a pointer to the next element in a list and a flag */ const uchar *key; size_t keylen; + uint32 hashnr; /* reversed hash number, for sorting */ /* data is stored here, directly after the keylen. thus the pointer to data is (void*)(slist_element_ptr+1) @@ -49,7 +49,7 @@ const int LF_HASH_OVERHEAD= sizeof(LF_SLIST); in a list) from l_find to l_insert/l_delete */ typedef struct { - intptr volatile *prev; + intptr *prev; LF_SLIST *curr, *next; } CURSOR; @@ -86,8 +86,8 @@ typedef struct { 0 - ok 1 - error (callbck returned 1) */ -static int l_find(LF_SLIST * volatile *head, CHARSET_INFO *cs, uint32 hashnr, - const uchar *key, size_t keylen, CURSOR *cursor, LF_PINS *pins, +static int l_find(LF_SLIST **head, CHARSET_INFO *cs, uint32 hashnr, + const uchar *key, uint keylen, CURSOR *cursor, LF_PINS *pins, my_hash_walk_action callback) { uint32 cur_hashnr; @@ -169,7 +169,7 @@ retry: it uses pins[0..2], on return all pins are removed. if there're nodes with the same key value, a new node is added before them. */ -static LF_SLIST *l_insert(LF_SLIST * volatile *head, CHARSET_INFO *cs, +static LF_SLIST *l_insert(LF_SLIST **head, CHARSET_INFO *cs, LF_SLIST *node, LF_PINS *pins, uint flags) { CURSOR cursor; @@ -221,7 +221,7 @@ static LF_SLIST *l_insert(LF_SLIST * volatile *head, CHARSET_INFO *cs, NOTE it uses pins[0..2], on return all pins are removed. */ -static int l_delete(LF_SLIST * volatile *head, CHARSET_INFO *cs, uint32 hashnr, +static int l_delete(LF_SLIST **head, CHARSET_INFO *cs, uint32 hashnr, const uchar *key, uint keylen, LF_PINS *pins) { CURSOR cursor; @@ -279,7 +279,7 @@ static int l_delete(LF_SLIST * volatile *head, CHARSET_INFO *cs, uint32 hashnr, it uses pins[0..2], on return the pin[2] keeps the node found all other pins are removed. */ -static LF_SLIST *l_search(LF_SLIST * volatile *head, CHARSET_INFO *cs, +static LF_SLIST *l_search(LF_SLIST **head, CHARSET_INFO *cs, uint32 hashnr, const uchar *key, uint keylen, LF_PINS *pins) { @@ -320,7 +320,7 @@ static inline my_hash_value_type calc_hash(CHARSET_INFO *cs, #define MAX_LOAD 1.0 /* average number of elements in a bucket */ -static int initialize_bucket(LF_HASH *, LF_SLIST * volatile*, uint, LF_PINS *); +static int initialize_bucket(LF_HASH *, LF_SLIST **, uint, LF_PINS *); static void default_initializer(LF_HASH *hash, void *dst, const void *src) { @@ -399,7 +399,7 @@ void lf_hash_destroy(LF_HASH *hash) int lf_hash_insert(LF_HASH *hash, LF_PINS *pins, const void *data) { int csize, bucket, hashnr; - LF_SLIST *node, * volatile *el; + LF_SLIST *node, **el; node= (LF_SLIST *)lf_alloc_new(pins); if (unlikely(!node)) @@ -438,7 +438,7 @@ int lf_hash_insert(LF_HASH *hash, LF_PINS *pins, const void *data) */ int lf_hash_delete(LF_HASH *hash, LF_PINS *pins, const void *key, uint keylen) { - LF_SLIST * volatile *el; + LF_SLIST **el; uint bucket, hashnr; hashnr= hash->hash_function(hash->charset, (uchar *)key, keylen) & INT_MAX32; @@ -474,7 +474,7 @@ void *lf_hash_search_using_hash_value(LF_HASH *hash, LF_PINS *pins, my_hash_value_type hashnr, const void *key, uint keylen) { - LF_SLIST * volatile *el, *found; + LF_SLIST **el, *found; uint bucket; /* hide OOM errors - if we cannot initialize a bucket, try the previous one */ @@ -508,7 +508,7 @@ int lf_hash_iterate(LF_HASH *hash, LF_PINS *pins, CURSOR cursor; uint bucket= 0; int res; - LF_SLIST * volatile *el; + LF_SLIST **el; el= lf_dynarray_lvalue(&hash->array, bucket); if (unlikely(!el)) @@ -540,13 +540,13 @@ static const uchar *dummy_key= (uchar*)""; 0 - ok -1 - out of memory */ -static int initialize_bucket(LF_HASH *hash, LF_SLIST * volatile *node, +static int initialize_bucket(LF_HASH *hash, LF_SLIST **node, uint bucket, LF_PINS *pins) { uint parent= my_clear_highest_bit(bucket); LF_SLIST *dummy= (LF_SLIST *)my_malloc(sizeof(LF_SLIST), MYF(MY_WME)); LF_SLIST **tmp= 0, *cur; - LF_SLIST * volatile *el= lf_dynarray_lvalue(&hash->array, parent); + LF_SLIST **el= lf_dynarray_lvalue(&hash->array, parent); if (unlikely(!el || !dummy)) return -1; if (*el == NULL && bucket && diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 6aec301bedc..73cbcf569f5 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2530,6 +2530,8 @@ void st_select_lex_node::add_slave(st_select_lex_node *slave_arg) { slave= slave_arg; slave_arg->master= this; + slave->prev= &master->slave; + slave->next= 0; } } @@ -2552,6 +2554,27 @@ void st_select_lex_node::link_chain_down(st_select_lex_node *first) } /* + @brief + Substitute this node in select tree for a newly creates node + + @param subst the node to substitute for + + @details + The function substitute this node in the select tree for a newly + created node subst. This node is just removed from the tree but all + its link fields and the attached sub-tree remain untouched. +*/ + +void st_select_lex_node::substitute_in_tree(st_select_lex_node *subst) +{ + if ((subst->next= next)) + next->prev= &subst->next; + subst->prev= prev; + (*prev)= subst; + subst->master= master; +} + +/* include on level down (but do not link) SYNOPSYS diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 7dce0bf6016..d3b316d6a8d 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -764,7 +764,7 @@ public: link_next= NULL; link_prev= NULL; } - + void substitute_in_tree(st_select_lex_node *subst); void set_slave(st_select_lex_node *slave_arg) { slave= slave_arg; } void move_node(st_select_lex_node *where_to_move) diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 4b10ef4ecb4..7f26256df09 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -653,44 +653,61 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl, Query_arena backup; Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup); + + Item *item; + SELECT_LEX *wrapper_sl; + SELECT_LEX_UNIT *derived_unit; + /* - Create SELECT_LEX of the select used in the result of transformation + Create SELECT_LEX wrapper_sl of the select used in the result + of the transformation */ - lex->current_select= tvc_sl; - if (mysql_new_select(lex, 0, NULL)) + if (!(wrapper_sl= new (thd->mem_root) SELECT_LEX())) goto err; - mysql_init_select(lex); - /* Create item list as '*' for the subquery SQ */ - Item *item; - SELECT_LEX *wrapper_sl; - wrapper_sl= lex->current_select; + wrapper_sl->select_number= ++thd->lex->stmt_lex->current_select_number; + wrapper_sl->parent_lex= lex; /* Used in init_query. */ + wrapper_sl->init_query(); + wrapper_sl->init_select(); + + wrapper_sl->nest_level= tvc_sl->nest_level; + wrapper_sl->parsing_place= tvc_sl->parsing_place; wrapper_sl->set_linkage(tvc_sl->get_linkage()); - wrapper_sl->parsing_place= SELECT_LIST; + + lex->current_select= wrapper_sl; item= new (thd->mem_root) Item_field(thd, &wrapper_sl->context, NULL, NULL, &star_clex_str); if (item == NULL || add_item_to_list(thd, item)) goto err; (wrapper_sl->with_wild)++; - - /* Exclude SELECT with TVC */ - tvc_sl->exclude(); + + /* Include the newly created select into the global list of selects */ + wrapper_sl->include_global((st_select_lex_node**)&lex->all_selects_list); + + /* Substitute select node used of TVC for the newly created select */ + tvc_sl->substitute_in_tree(wrapper_sl); + /* - Create derived table DT that will wrap TVC in the result of transformation + Create a unit for the substituted select used for TVC and attach it + to the the wrapper select wrapper_sl as the only unit. The created + unit is the unit for the derived table tvc_x of the transformation. */ - SELECT_LEX *tvc_select; // select for tvc - SELECT_LEX_UNIT *derived_unit; // unit for tvc_select - if (mysql_new_select(lex, 1, tvc_sl)) + if (!(derived_unit= new (thd->mem_root) SELECT_LEX_UNIT())) goto err; - tvc_select= lex->current_select; - derived_unit= tvc_select->master_unit(); - tvc_select->set_linkage(DERIVED_TABLE_TYPE); + derived_unit->init_query(); + derived_unit->thd= thd; + derived_unit->include_down(wrapper_sl); - lex->current_select= wrapper_sl; + /* + Attach the select used of TVC as the only slave to the unit for + the derived table tvc_x of the transformation + */ + derived_unit->add_slave(tvc_sl); + tvc_sl->set_linkage(DERIVED_TABLE_TYPE); /* - Create the name of the wrapping derived table and - add it to the FROM list of the wrapper - */ + Generate the name of the derived table created for TVC and + add it to the FROM list of the wrapping select + */ Table_ident *ti; LEX_CSTRING alias; TABLE_LIST *derived_tab; @@ -709,10 +726,6 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl, wrapper_sl->table_list.first->derived_type= DTYPE_TABLE | DTYPE_MATERIALIZE; lex->derived_tables|= DERIVED_SUBQUERY; - wrapper_sl->where= 0; - wrapper_sl->set_braces(false); - derived_unit->set_with_clause(0); - if (arena) thd->restore_active_arena(arena, &backup); thd->lex->result= save_result; |