summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--debian/libmariadb3.install1
-rw-r--r--mysql-test/main/table_value_constr.result110
-rw-r--r--mysql-test/main/table_value_constr.test57
-rw-r--r--mysql-test/suite/compat/oracle/r/table_value_constr.result4
-rw-r--r--mysys/lf_hash.c30
-rw-r--r--sql/sql_lex.cc23
-rw-r--r--sql/sql_lex.h2
-rw-r--r--sql/sql_tvc.cc67
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;