[Commits] 9892a3b4fda: JSON_TABLE: undo the changes that are no longer relevant
by psergey 12 Mar '21
by psergey 12 Mar '21
12 Mar '21
revision-id: 9892a3b4fda4c34d35643f5c7e037fbaee775ae6 (mariadb-10.5.2-429-g9892a3b4fda)
parent(s): bbc62d491f523ed84efcfcc73436005d39f77a90
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-03-12 21:58:51 +0300
message:
JSON_TABLE: undo the changes that are no longer relevant
---
sql/table.cc | 8 +++-----
1 file changed, 3 insertions(+), 5 deletions(-)
diff --git a/sql/table.cc b/sql/table.cc
index 9c205fc4be6..6f4fd1af3f8 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -6722,8 +6722,6 @@ Item *create_view_field(THD *thd, TABLE_LIST *view, Item **field_ref,
LEX_CSTRING *name)
{
bool save_wrapper= thd->lex->first_select_lex()->no_wrap_view_item;
- bool *wrapper_to_set= thd->lex->current_select ?
- &thd->lex->current_select->no_wrap_view_item : &save_wrapper;
Item *field= *field_ref;
DBUG_ENTER("create_view_field");
@@ -6739,17 +6737,17 @@ Item *create_view_field(THD *thd, TABLE_LIST *view, Item **field_ref,
}
DBUG_ASSERT(field);
- *wrapper_to_set= TRUE;
+ thd->lex->current_select->no_wrap_view_item= TRUE;
if (!field->is_fixed())
{
if (field->fix_fields(thd, field_ref))
{
- *wrapper_to_set= save_wrapper;
+ thd->lex->current_select->no_wrap_view_item= save_wrapper;
DBUG_RETURN(0);
}
field= *field_ref;
}
- *wrapper_to_set= save_wrapper;
+ thd->lex->current_select->no_wrap_view_item= save_wrapper;
if (save_wrapper)
{
DBUG_RETURN(field);
1
0
[Commits] 7ea4872: MDEV-21104 Wrong result (extra rows and wrong values) with incremental BNLH
by IgorBabaev 11 Mar '21
by IgorBabaev 11 Mar '21
11 Mar '21
revision-id: 7ea487207e4c8d582161c7c4dc413f29f9e61552 (mariadb-10.2.31-769-g7ea4872)
parent(s): 259e5243faa88370bbb890342326a324fb648f7d
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-03-10 17:26:43 -0800
message:
MDEV-21104 Wrong result (extra rows and wrong values) with incremental BNLH
This bug could affect multi-way join queries with embedded outer joins that
contained a conjunctive IS NULL predicate over a non-nullable column from
inner table of an outer join. The predicate could occur in WHERE condition
or in ON condition. Due to this bug a wrong result set could be returned by
the query. The bug manifested itself only when join buffers were employed
for join operations.
The problem appeared because
- a bug in the function JOIN_CACHE::get_match_flag_by_pos that not always
returned proper match flags for embedding outer joins stored together
with table rows put a join buffer.
- bug in the function JOIN_CACHE::join_matching_records that not always
correctly determined that a row from the buffer could be skipped due
to applied 'not_exists' optimization.
Example:
SELECT * FROM t1 LEFT JOIN ((t2 LEFT JOIN t3 ON c = d) JOIN t4) ON b = e
WHERE e IS NULL;
The patch introduces a new function that finds the match flag for a record
from join buffer specifying the buffer where this flag has to be found.
The function is called JOIN_CACHE::get_match_flag_by_pos_from_join_buffer().
Now this function rather than JOIN_CACHE::get_match_flag_by_pos() is used
in JOIN_CACHE::skip_if_matched() to check whether a record from the join
buffer must be ignored when extending the record by null complements.
Also the code of the function JOIN_CACHE::skip_if_not_needed_match() has
been changed. The function checks whether a record from the join buffer
still may produce some useful extensions.
Also some clarifying comments has been added.
Approved by monty(a)mariadb.com.
---
mysql-test/r/join_cache.result | 53 +++++++++++++++++++++++++
mysql-test/t/join_cache.test | 36 +++++++++++++++++
sql/sql_join_cache.cc | 88 +++++++++++++++++++++++++++++++++++++-----
sql/sql_join_cache.h | 11 +++++-
4 files changed, 177 insertions(+), 11 deletions(-)
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index e41c79a..87c4079 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -6054,4 +6054,57 @@ select f2 from t2,t1 where f2 = 0;
f2
drop table t1, t2;
set join_buffer_size=@save_join_buffer_size;
+#
+# MDEV-21104: BNLH used for multi-join query with embedded outer join
+# and possible 'not exists' optimization
+#
+set join_cache_level=4;
+CREATE TABLE t1 (a int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b int, c int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,2),(2,4);
+CREATE TABLE t3 (d int, KEY(d)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1),(2);
+CREATE TABLE t4 (e int primary key) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (1),(2);
+ANALYZE TABLE t1,t2,t3,t4;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+test.t2 analyze status OK
+test.t3 analyze status OK
+test.t4 analyze status OK
+SELECT * FROM t2 LEFT JOIN t3 ON c = d;
+b c d
+1 2 2
+2 4 NULL
+SELECT * FROM (t2 LEFT JOIN t3 ON c = d ) JOIN t4;
+b c d e
+1 2 2 1
+2 4 NULL 1
+1 2 2 2
+2 4 NULL 2
+EXPLAIN SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t3 hash_index d #hash#d:d 5:5 test.t2.c 2 Using where; Using index; Using join buffer (incremental, BNLH join)
+1 SIMPLE t4 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t2.b 2 Using index; Using join buffer (incremental, BNLH join)
+SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e;
+a b c d e
+1 1 2 2 1
+2 1 2 2 1
+1 2 4 NULL 2
+2 2 4 NULL 2
+EXPLAIN SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e
+WHERE e IS NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t3 hash_index d #hash#d:d 5:5 test.t2.c 2 Using where; Using index; Using join buffer (incremental, BNLH join)
+1 SIMPLE t4 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t2.b 2 Using where; Using index; Not exists; Using join buffer (incremental, BNLH join)
+SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e
+WHERE e IS NULL;
+a b c d e
+DROP TABLE t1,t2,t3,t4;
+set join_cache_level=@save_join_cache_level;
set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 9576d59..15cd1e9 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -4014,5 +4014,41 @@ select f2 from t2,t1 where f2 = 0;
drop table t1, t2;
set join_buffer_size=@save_join_buffer_size;
+
+--echo #
+--echo # MDEV-21104: BNLH used for multi-join query with embedded outer join
+--echo # and possible 'not exists' optimization
+--echo #
+
+set join_cache_level=4;
+
+CREATE TABLE t1 (a int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b int, c int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,2),(2,4);
+CREATE TABLE t3 (d int, KEY(d)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1),(2);
+CREATE TABLE t4 (e int primary key) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (1),(2);
+ANALYZE TABLE t1,t2,t3,t4;
+
+SELECT * FROM t2 LEFT JOIN t3 ON c = d;
+SELECT * FROM (t2 LEFT JOIN t3 ON c = d ) JOIN t4;
+
+let $q1=
+SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e;
+eval EXPLAIN $q1;
+eval $q1;
+
+let $q2=
+SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e
+ WHERE e IS NULL;
+eval EXPLAIN $q2;
+eval $q2;
+
+DROP TABLE t1,t2,t3,t4;
+
+set join_cache_level=@save_join_cache_level;
+
# The following command must be the last one in the file
set @@optimizer_switch=@save_optimizer_switch;
diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc
index 1dfc938..594094a 100644
--- a/sql/sql_join_cache.cc
+++ b/sql/sql_join_cache.cc
@@ -1649,7 +1649,7 @@ void JOIN_CACHE::get_record_by_pos(uchar *rec_ptr)
}
-/*
+/*
Get the match flag from the referenced record: the default implementation
SYNOPSIS
@@ -1661,6 +1661,7 @@ void JOIN_CACHE::get_record_by_pos(uchar *rec_ptr)
get the match flag for the record pointed by the reference at the position
rec_ptr. If the match flag is placed in one of the previous buffers the
function first reaches the linked record fields in this buffer.
+ The function returns the value of the first encountered match flag.
RETURN VALUE
match flag for the record at the position rec_ptr
@@ -1685,6 +1686,39 @@ enum JOIN_CACHE::Match_flag JOIN_CACHE::get_match_flag_by_pos(uchar *rec_ptr)
/*
+ Get the match flag for the referenced record from specified join buffer
+
+ SYNOPSIS
+ get_match_flag_by_pos_from_join_buffer()
+ rec_ptr position of the first field of the record in the join buffer
+ tab join table with join buffer where to look for the match flag
+
+ DESCRIPTION
+ This default implementation of the get_match_flag_by_pos_from_join_buffer
+ method gets the match flag for the record pointed by the reference at the
+ position rec_ptr from the join buffer attached to the join table tab.
+
+ RETURN VALUE
+ match flag for the record at the position rec_ptr from the join
+ buffer attached to the table tab.
+*/
+
+enum JOIN_CACHE::Match_flag
+JOIN_CACHE::get_match_flag_by_pos_from_join_buffer(uchar *rec_ptr,
+ JOIN_TAB *tab)
+{
+ DBUG_ASSERT(tab->cache && tab->cache->with_match_flag);
+ for (JOIN_CACHE *cache= this; ; )
+ {
+ if (cache->join_tab == tab)
+ return (enum Match_flag) rec_ptr[0];
+ cache= cache->prev_cache;
+ rec_ptr= cache->get_rec_ref(rec_ptr);
+ }
+}
+
+
+/*
Calculate the increment of the auxiliary buffer for a record write
SYNOPSIS
@@ -1954,6 +1988,10 @@ bool JOIN_CACHE::read_referenced_field(CACHE_FIELD *copy,
If the record is skipped the value of 'pos' is set to point to the position
right after the record.
+ NOTE
+ Currently this function is called only when generating null complemented
+ records for outer joins (=> only when join_tab->first_unmatched != NULL).
+
RETURN VALUE
TRUE the match flag is set to MATCH_FOUND and the record has been skipped
FALSE otherwise
@@ -1966,7 +2004,9 @@ bool JOIN_CACHE::skip_if_matched()
if (prev_cache)
offset+= prev_cache->get_size_of_rec_offset();
/* Check whether the match flag is MATCH_FOUND */
- if (get_match_flag_by_pos(pos+offset) == MATCH_FOUND)
+ if (get_match_flag_by_pos_from_join_buffer(pos+offset,
+ join_tab->first_unmatched) ==
+ MATCH_FOUND)
{
pos+= size_of_rec_len + get_rec_length(pos);
return TRUE;
@@ -1983,13 +2023,23 @@ bool JOIN_CACHE::skip_if_matched()
DESCRIPTION
This default implementation of the virtual function skip_if_not_needed_match
- skips the next record from the join buffer if its match flag is not
- MATCH_NOT_FOUND, and, either its value is MATCH_FOUND and join_tab is the
- first inner table of an inner join, or, its value is MATCH_IMPOSSIBLE
- and join_tab is the first inner table of an outer join.
+ skips the next record from the join when generating join extensions
+ for the records in the join buffer depending on the value of the match flag.
+ - In the case of a semi-nest the match flag may be in two states
+ {MATCH_NOT_FOUND, MATCH_FOUND}. The record is skipped if the flag is set
+ to MATCH_FOUND.
+ - In the case of a outer join nest when not_exists optimization is applied
+ the match may be in three states {MATCH_NOT_FOUND, MATCH_IMPOSSIBLE,
+ MATCH_FOUND. The record is skipped if the flag is set to MATCH_FOUND or
+ to MATCH_IMPOSSIBLE.
+
If the record is skipped the value of 'pos' is set to point to the position
right after the record.
+ NOTE
+ Currently the function is called only when generating non-null complemented
+ extensions for records in the join buffer.
+
RETURN VALUE
TRUE the record has to be skipped
FALSE otherwise
@@ -2000,11 +2050,19 @@ bool JOIN_CACHE::skip_if_not_needed_match()
DBUG_ASSERT(with_length);
enum Match_flag match_fl;
uint offset= size_of_rec_len;
+ bool skip= FALSE;
if (prev_cache)
offset+= prev_cache->get_size_of_rec_offset();
- if ((match_fl= get_match_flag_by_pos(pos+offset)) != MATCH_NOT_FOUND &&
- (join_tab->check_only_first_match() == (match_fl == MATCH_FOUND)) )
+ if (!join_tab->check_only_first_match())
+ return FALSE;
+
+ match_fl= get_match_flag_by_pos(pos+offset);
+ skip= join_tab->first_sj_inner_tab ?
+ match_fl == MATCH_FOUND : // the case of semi-join
+ match_fl != MATCH_NOT_FOUND; // the case of outer-join
+
+ if (skip)
{
pos+= size_of_rec_len + get_rec_length(pos);
return TRUE;
@@ -2104,7 +2162,14 @@ enum_nested_loop_state JOIN_CACHE::join_records(bool skip_last)
goto finish;
}
join_tab->not_null_compl= FALSE;
- /* Prepare for generation of null complementing extensions */
+ /*
+ Prepare for generation of null complementing extensions.
+ For all inner tables of the outer join operation for which
+ regular matches have been just found the field 'first_unmatched'
+ is set to point the the first inner table. After all null
+ complement rows are generated for this outer join this field
+ is set back to NULL.
+ */
for (tab= join_tab->first_inner; tab <= join_tab->last_inner; tab++)
tab->first_unmatched= join_tab->first_inner;
}
@@ -2221,7 +2286,10 @@ enum_nested_loop_state JOIN_CACHE::join_matching_records(bool skip_last)
int error;
enum_nested_loop_state rc= NESTED_LOOP_OK;
join_tab->table->null_row= 0;
- bool check_only_first_match= join_tab->check_only_first_match();
+ bool check_only_first_match=
+ join_tab->check_only_first_match() &&
+ (!join_tab->first_inner || // semi-join case
+ join_tab->first_inner == join_tab->first_unmatched); // outer join case
bool outer_join_first_inner= join_tab->is_first_inner_for_outer_join();
DBUG_ENTER("JOIN_CACHE::join_matching_records");
diff --git a/sql/sql_join_cache.h b/sql/sql_join_cache.h
index 1cbc6ac..4569910 100644
--- a/sql/sql_join_cache.h
+++ b/sql/sql_join_cache.h
@@ -206,7 +206,9 @@ class JOIN_CACHE :public Sql_alloc
/*
This flag indicates that records written into the join buffer contain
- a match flag field. The flag must be set by the init method.
+ a match flag field. The flag must be set by the init method.
+ Currently any implementation of the virtial init method calls
+ the function JOIN_CACHE::calc_record_fields() to set this flag.
*/
bool with_match_flag;
/*
@@ -646,6 +648,13 @@ class JOIN_CACHE :public Sql_alloc
/* Shall return the value of the match flag for the positioned record */
virtual enum Match_flag get_match_flag_by_pos(uchar *rec_ptr);
+ /*
+ Shall return the value of the match flag for the positioned record
+ from the join buffer attached to the specified table
+ */
+ virtual enum Match_flag
+ get_match_flag_by_pos_from_join_buffer(uchar *rec_ptr, JOIN_TAB *tab);
+
/* Shall return the position of the current record */
virtual uchar *get_curr_rec() { return curr_rec_pos; }
1
0
09 Mar '21
revision-id: df098d08601aa4f40caea1d91364bc030f56188f (mariadb-10.2.31-781-gdf098d0)
parent(s): dc6667805dd4567693fcc01666da7d1277050097
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-03-09 15:11:44 -0800
message:
MDEV-24597 Explicit column name error in CTE of UNION
This bug manifested itself when executing queries with multiple reference
to a CTE specified by a query expression with union and having its
column names explicitly declared. In this case the server returned a bogus
error message about unknown column name. It happened because while for the
first reference to the CTE the names of the columns returned by the CTE
specification were properly changed to match the CTE definition for the
other references it was not done. This was a consequence of not quite
complete code of the function With_element::clone_parsed_spec() that forgot
to set the reference to the CTE definition for unit structures representing
non-first CTE references.
Approved by dmitry.shulga(a)mariadb.com
---
mysql-test/r/cte_nonrecursive.result | 42 ++++++++++++++++++++++++++++++++++--
mysql-test/t/cte_nonrecursive.test | 31 ++++++++++++++++++++++++++
sql/sql_cte.cc | 1 +
3 files changed, 72 insertions(+), 2 deletions(-)
diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
index a47fdcd..da954c1 100644
--- a/mysql-test/r/cte_nonrecursive.result
+++ b/mysql-test/r/cte_nonrecursive.result
@@ -618,7 +618,7 @@ with t(c) as (select a from t1 where b >= 'c')
select * from t r1, t r2 where r1.c=r2.c and r2.c=4;
show create view v4;
View Create View character_set_client collation_connection
-v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS with t as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c`,`r2`.`c` AS `d` from (`t` `r1` join (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c') `r2`) where `r1`.`c` = `r2`.`c` and `r2`.`c` = 4 latin1 latin1_swedish_ci
+v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS with t as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c`,`r2`.`c` AS `d` from (`t` `r1` join `t` `r2`) where `r1`.`c` = `r2`.`c` and `r2`.`c` = 4 latin1 latin1_swedish_ci
select * from v4;
c d
4 4
@@ -1126,7 +1126,7 @@ NULL UNION RESULT <union2,5> ALL NULL NULL NULL NULL NULL NULL
NULL UNION RESULT <union9,12> ALL NULL NULL NULL NULL NULL NULL
NULL UNION RESULT <union1,6> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 with cte_e as (with cte_o as (with cte_i as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1)select `cte_e1`.`a` AS `a` from `cte_e` `cte_e1` where `cte_e1`.`a` > 1 union select `cte_e2`.`a` AS `a` from (with cte_o as (with cte_i as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t
1`.`a` > 1 and `test`.`t1`.`a` < 7) `cte_e2`
+Note 1003 with cte_e as (with cte_o as (with cte_i as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1)select `cte_e1`.`a` AS `a` from `cte_e` `cte_e1` where `cte_e1`.`a` > 1 union select `cte_e2`.`a` AS `a` from `cte_e` `cte_e2`
drop table t1;
#
# MDEV-13753: embedded CTE in a VIEW created in prepared statement
@@ -1725,4 +1725,42 @@ drop table db1.t1;
drop database db1;
create database test;
use test;
+#
+# MDEV-24597: CTE with union used multiple times in query
+#
+with cte(a) as
+(select 1 as d union select 2 as d)
+select a from cte as r1
+union
+select a from cte as r2;
+a
+1
+2
+create table t1 (a int, b int) engine=myisam;
+insert into t1 values
+(3,30), (7,70), (1,10), (7,71), (2,20), (7,72), (3,33), (4,44),
+(5,50), (4,40), (3,33), (4,42), (4,43), (5,51);
+with cte(c) as
+(select a from t1 where b < 30 union select a from t1 where b > 40)
+select * from cte as r1, cte as r2 where r1.c = r2.c;
+c c
+1 1
+2 2
+7 7
+4 4
+5 5
+with cte(a,c) as
+(
+select a, count(*) from t1 group by a having count(*) = 1
+union
+select a, count(*) from t1 group by a having count(*) = 3
+)
+select a, c from cte as r1 where a < 3
+union
+select a, c from cte as r2 where a > 4;
+a c
+1 1
+2 1
+7 3
+drop table t1;
# End of 10.2 tests
diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test
index 0174ddb..c2ebc92 100644
--- a/mysql-test/t/cte_nonrecursive.test
+++ b/mysql-test/t/cte_nonrecursive.test
@@ -1230,4 +1230,35 @@ drop database db1;
create database test;
use test;
+--echo #
+--echo # MDEV-24597: CTE with union used multiple times in query
+--echo #
+
+with cte(a) as
+(select 1 as d union select 2 as d)
+select a from cte as r1
+union
+select a from cte as r2;
+
+create table t1 (a int, b int) engine=myisam;
+insert into t1 values
+(3,30), (7,70), (1,10), (7,71), (2,20), (7,72), (3,33), (4,44),
+(5,50), (4,40), (3,33), (4,42), (4,43), (5,51);
+
+with cte(c) as
+(select a from t1 where b < 30 union select a from t1 where b > 40)
+select * from cte as r1, cte as r2 where r1.c = r2.c;
+
+with cte(a,c) as
+(
+ select a, count(*) from t1 group by a having count(*) = 1
+ union
+ select a, count(*) from t1 group by a having count(*) = 3
+)
+select a, c from cte as r1 where a < 3
+union
+select a, c from cte as r2 where a > 4;
+
+drop table t1;
+
--echo # End of 10.2 tests
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index e07a525..3a2301a 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -879,6 +879,7 @@ st_select_lex_unit *With_element::clone_parsed_spec(THD *thd,
with_table->next_global= spec_tables;
}
res= &lex->unit;
+ res->with_element= this;
lex->unit.include_down(with_table->select_lex);
lex->unit.set_slave(with_select);
1
0
09 Mar '21
revision-id: 79ac9485f8fb86243c31d6e7895dfbe3c2f369ca (mariadb-10.2.31-781-g79ac948)
parent(s): dc6667805dd4567693fcc01666da7d1277050097
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-03-09 14:51:52 -0800
message:
MDEV-24597 Explicit column name error in CTE of UNION
This bug manifested itself when executing queries with multiple reference
to a CTE specified by a query expression with union and having its
column names explicitly declared. In this case the server returned a bogus
error message about unknown column name. It happened because while for the
first reference to the CTE the names of the columns returned by the CTE
specification were properly changed to match the CTE definition for the
other references it was not done. This was a consequence of not quite
complete code of the function TABLE_LIST::set_as_with_table that forgot to
set the reference to the CTE definition for unit structures representing
non-first CTE references.
Approved by dmitry.shulga(a)mariadb.com
---
mysql-test/r/cte_nonrecursive.result | 42 ++++++++++++++++++++++++++++++++++--
mysql-test/t/cte_nonrecursive.test | 31 ++++++++++++++++++++++++++
sql/sql_cte.cc | 1 +
3 files changed, 72 insertions(+), 2 deletions(-)
diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
index a47fdcd..da954c1 100644
--- a/mysql-test/r/cte_nonrecursive.result
+++ b/mysql-test/r/cte_nonrecursive.result
@@ -618,7 +618,7 @@ with t(c) as (select a from t1 where b >= 'c')
select * from t r1, t r2 where r1.c=r2.c and r2.c=4;
show create view v4;
View Create View character_set_client collation_connection
-v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS with t as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c`,`r2`.`c` AS `d` from (`t` `r1` join (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c') `r2`) where `r1`.`c` = `r2`.`c` and `r2`.`c` = 4 latin1 latin1_swedish_ci
+v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS with t as (select `test`.`t1`.`a` AS `c` from `test`.`t1` where `test`.`t1`.`b` >= 'c')select `r1`.`c` AS `c`,`r2`.`c` AS `d` from (`t` `r1` join `t` `r2`) where `r1`.`c` = `r2`.`c` and `r2`.`c` = 4 latin1 latin1_swedish_ci
select * from v4;
c d
4 4
@@ -1126,7 +1126,7 @@ NULL UNION RESULT <union2,5> ALL NULL NULL NULL NULL NULL NULL
NULL UNION RESULT <union9,12> ALL NULL NULL NULL NULL NULL NULL
NULL UNION RESULT <union1,6> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 with cte_e as (with cte_o as (with cte_i as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1)select `cte_e1`.`a` AS `a` from `cte_e` `cte_e1` where `cte_e1`.`a` > 1 union select `cte_e2`.`a` AS `a` from (with cte_o as (with cte_i as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t
1`.`a` > 1 and `test`.`t1`.`a` < 7) `cte_e2`
+Note 1003 with cte_e as (with cte_o as (with cte_i as (select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1 union select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1)select `cte_e1`.`a` AS `a` from `cte_e` `cte_e1` where `cte_e1`.`a` > 1 union select `cte_e2`.`a` AS `a` from `cte_e` `cte_e2`
drop table t1;
#
# MDEV-13753: embedded CTE in a VIEW created in prepared statement
@@ -1725,4 +1725,42 @@ drop table db1.t1;
drop database db1;
create database test;
use test;
+#
+# MDEV-24597: CTE with union used multiple times in query
+#
+with cte(a) as
+(select 1 as d union select 2 as d)
+select a from cte as r1
+union
+select a from cte as r2;
+a
+1
+2
+create table t1 (a int, b int) engine=myisam;
+insert into t1 values
+(3,30), (7,70), (1,10), (7,71), (2,20), (7,72), (3,33), (4,44),
+(5,50), (4,40), (3,33), (4,42), (4,43), (5,51);
+with cte(c) as
+(select a from t1 where b < 30 union select a from t1 where b > 40)
+select * from cte as r1, cte as r2 where r1.c = r2.c;
+c c
+1 1
+2 2
+7 7
+4 4
+5 5
+with cte(a,c) as
+(
+select a, count(*) from t1 group by a having count(*) = 1
+union
+select a, count(*) from t1 group by a having count(*) = 3
+)
+select a, c from cte as r1 where a < 3
+union
+select a, c from cte as r2 where a > 4;
+a c
+1 1
+2 1
+7 3
+drop table t1;
# End of 10.2 tests
diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test
index 0174ddb..c2ebc92 100644
--- a/mysql-test/t/cte_nonrecursive.test
+++ b/mysql-test/t/cte_nonrecursive.test
@@ -1230,4 +1230,35 @@ drop database db1;
create database test;
use test;
+--echo #
+--echo # MDEV-24597: CTE with union used multiple times in query
+--echo #
+
+with cte(a) as
+(select 1 as d union select 2 as d)
+select a from cte as r1
+union
+select a from cte as r2;
+
+create table t1 (a int, b int) engine=myisam;
+insert into t1 values
+(3,30), (7,70), (1,10), (7,71), (2,20), (7,72), (3,33), (4,44),
+(5,50), (4,40), (3,33), (4,42), (4,43), (5,51);
+
+with cte(c) as
+(select a from t1 where b < 30 union select a from t1 where b > 40)
+select * from cte as r1, cte as r2 where r1.c = r2.c;
+
+with cte(a,c) as
+(
+ select a, count(*) from t1 group by a having count(*) = 1
+ union
+ select a, count(*) from t1 group by a having count(*) = 3
+)
+select a, c from cte as r1 where a < 3
+union
+select a, c from cte as r2 where a > 4;
+
+drop table t1;
+
--echo # End of 10.2 tests
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index e07a525..4929c2e 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -1124,6 +1124,7 @@ bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem)
{
if(!(derived= with_elem->clone_parsed_spec(thd, this)))
return true;
+ derived->with_element= with_elem;
}
derived->first_select()->linkage= DERIVED_TABLE_TYPE;
with_elem->inc_references();
1
0
06 Mar '21
revision-id: c24bcd5948bd69f336af61214c0f31165d19dc14 (mariadb-10.4.11-556-gc24bcd5)
parent(s): fcc9f8b10cd2f497ff410b592808eedb3ee5f212
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-03-05 20:18:28 -0800
message:
MDEV-22786 Crashes with nested table value constructors
The bug caused crashes of the server when processing queries with nested
table value constructors (TVC) . It happened because the grammar rules to
parse TVC used the same global lists for both nested TVC and nesting TVC.
As a result invalid select trees were constructed for queries with nested
TVC and this led to crashes at the prepare stage.
This patch provides its own lists structures for each TVC nest level.
Besides the patch fixes a bug in the function wrap_tvc() that missed
inheritance of the SELECT_LEX::exclude_from_table_unique_test for
selects that wrapped TVCs. This inheritance is critical for specifications
of derived tables that employ nested TVCs.
Approved by dmitry.shulga(a)mariadb.com
---
mysql-test/main/table_value_constr.result | 175 ++++++++++++++++++++++++++++++
mysql-test/main/table_value_constr.test | 106 ++++++++++++++++++
sql/sql_lex.cc | 21 +++-
sql/sql_lex.h | 11 +-
sql/sql_tvc.cc | 2 +
5 files changed, 305 insertions(+), 10 deletions(-)
diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index 4525a50..0914645 100644
--- a/mysql-test/main/table_value_constr.result
+++ b/mysql-test/main/table_value_constr.result
@@ -2887,6 +2887,181 @@ drop table t1,t2,t3;
select sum((values(1)));
sum((values(1)))
1
+#
+# MDEV-22786: Nested table values constructors
+#
+values ((values (2)));
+(values (2))
+2
+values ((values (2)), (5), (select 4));
+(values (2)) 5 (select 4)
+2 5 4
+values ((7), (values (2)), (5), (select 4));
+7 (values (2)) 5 (select 4)
+7 2 5 4
+values ((values (2))) union values ((values (3)));
+(values (2))
+2
+3
+values ((values (2))), ((values (3)));
+(values (2))
+2
+3
+values ((values (2))), ((select 4)), ((values (3)));
+(values (2))
+2
+4
+3
+values ((values (4)), (values (5))), ((values (1)), (values (7)));
+(values (4)) (values (5))
+4 5
+1 7
+values ((values (4)), (select 5)), ((select 1), (values (7)));
+(values (4)) (select 5)
+4 5
+1 7
+values ((select 2)) union values ((values (3)));
+(select 2)
+2
+3
+values ((values (2))) union values((select 3));
+(values (2))
+2
+3
+values ((values (2))) union all values ((values (2)));
+(values (2))
+2
+2
+values ((values (4)), (values (5))), ((values (1)), (values (7)))
+union
+values ((values (4)), (select 5)), ((select 2), (values (8)));
+(values (4)) (values (5))
+4 5
+1 7
+2 8
+values ((values (4)), (values (5))), ((values (1)), (values (7)))
+union all
+values ((values (4)), (select 5)), ((select 2), (values (8)));
+(values (4)) (values (5))
+4 5
+1 7
+4 5
+2 8
+values ((values (1) union values (1)));
+(values (1) union values (1))
+1
+values ((values (1) union values (1) union values (1)));
+(values (1) union values (1) union values (1))
+1
+values ((values ((values (4)))));
+(values ((values (4))))
+4
+values ((values ((select 5))));
+(values ((select 5)))
+5
+values ((select (values (4))), (values ((values(5)))));
+(select (values (4))) (values ((values(5))))
+4 5
+values ((select (values (4))), (values ((select 5))));
+(select (values (4))) (values ((select 5)))
+4 5
+values ((select (values (4))), (values ((values(5)))))
+union
+values ((select (values (4))), (values ((select 7))));
+(select (values (4))) (values ((values(5))))
+4 5
+4 7
+values ((values (2))), ((values ((values (4)))));
+(values (2))
+2
+4
+values ((values (2))), ((values ((select 4))));
+(values (2))
+2
+4
+values ((values (2))), ((values ((values (4)))))
+union
+values ((values (8))), ((values ((select 4))));
+(values (2))
+2
+4
+8
+values ((values (2))), ((values ((values (4)))))
+union all
+values ((values (8))), ((values ((select 4))));
+(values (2))
+2
+4
+8
+4
+select * from (values ((values (2)))) dt;
+(values (2))
+2
+select * from (values ((values (2)), (5), (select 4))) dt;
+(values (2)) 5 (select 4)
+2 5 4
+select * from (values ((values (2))) union values ((values (3)))) dt;
+(values (2))
+2
+3
+select * from (values ((values (2))), ((values (3)))) dt;
+(values (2))
+2
+3
+select * from (values ((values (2))), ((values (3)))) dt;
+(values (2))
+2
+3
+select * from (values ((values (2))), ((select 4)), ((values (3)))) dt;
+(values (2))
+2
+4
+3
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+values ((values ((select a from t1 where a=7))));
+(values ((select a from t1 where a=7)))
+7
+values ((values ((select (values(2)) from t1 where a=8))));
+(values ((select (values(2)) from t1 where a=8)))
+NULL
+values ((values ((select a from t1 where a=7))))
+union
+values ((values ((select (values(2)) from t1 where a=8))));
+(values ((select a from t1 where a=7)))
+7
+NULL
+values ((values ((select a from t1 where a in ((values (7)))))));
+(values ((select a from t1 where a in ((values (7))))))
+7
+values ((values ((select a from t1 where a in ((values (7), (8)))))));
+(values ((select a from t1 where a in ((values (7), (8))))))
+7
+values ((values
+((select a from t1 where a in (values (7) union values (8))))));
+(values
+((select a from t1 where a in (values (7) union values (8)))))
+7
+values ((values ((select (values(2)) from t1 where a=8))));
+(values ((select (values(2)) from t1 where a=8)))
+NULL
+values ((select (values(2)) from t1 where a<7));
+ERROR 21000: Subquery returns more than 1 row
+select * from (values ((values ((select a from t1 where a=7))))) dt;
+(values ((select a from t1 where a=7)))
+7
+select * from (values ((values ((select (values(2)) from t1 where a=8))))) dt;
+(values ((select (values(2)) from t1 where a=8)))
+NULL
+insert into t1(a) values ((values (2))), ((values (3)));
+select * from t1;
+a
+3
+7
+1
+2
+3
+drop table t1;
End of 10.3 tests
#
# MDEV-22610 Crash in INSERT INTO t1 (VALUES (DEFAULT) UNION VALUES (DEFAULT))
diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test
index 55cf2fc..49e1c7c 100644
--- a/mysql-test/main/table_value_constr.test
+++ b/mysql-test/main/table_value_constr.test
@@ -1522,6 +1522,112 @@ drop table t1,t2,t3;
select sum((values(1)));
+--echo #
+--echo # MDEV-22786: Nested table values constructors
+--echo #
+
+values ((values (2)));
+
+values ((values (2)), (5), (select 4));
+
+values ((7), (values (2)), (5), (select 4));
+
+values ((values (2))) union values ((values (3)));
+
+values ((values (2))), ((values (3)));
+
+values ((values (2))), ((select 4)), ((values (3)));
+
+values ((values (4)), (values (5))), ((values (1)), (values (7)));
+
+values ((values (4)), (select 5)), ((select 1), (values (7)));
+
+values ((select 2)) union values ((values (3)));
+
+values ((values (2))) union values((select 3));
+
+values ((values (2))) union all values ((values (2)));
+
+values ((values (4)), (values (5))), ((values (1)), (values (7)))
+union
+values ((values (4)), (select 5)), ((select 2), (values (8)));
+
+values ((values (4)), (values (5))), ((values (1)), (values (7)))
+union all
+values ((values (4)), (select 5)), ((select 2), (values (8)));
+
+values ((values (1) union values (1)));
+
+values ((values (1) union values (1) union values (1)));
+
+values ((values ((values (4)))));
+
+values ((values ((select 5))));
+
+values ((select (values (4))), (values ((values(5)))));
+
+values ((select (values (4))), (values ((select 5))));
+
+values ((select (values (4))), (values ((values(5)))))
+union
+values ((select (values (4))), (values ((select 7))));
+
+values ((values (2))), ((values ((values (4)))));
+
+values ((values (2))), ((values ((select 4))));
+
+values ((values (2))), ((values ((values (4)))))
+union
+values ((values (8))), ((values ((select 4))));
+
+values ((values (2))), ((values ((values (4)))))
+union all
+values ((values (8))), ((values ((select 4))));
+
+select * from (values ((values (2)))) dt;
+
+select * from (values ((values (2)), (5), (select 4))) dt;
+
+select * from (values ((values (2))) union values ((values (3)))) dt;
+
+select * from (values ((values (2))), ((values (3)))) dt;
+
+select * from (values ((values (2))), ((values (3)))) dt;
+
+select * from (values ((values (2))), ((select 4)), ((values (3)))) dt;
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+
+values ((values ((select a from t1 where a=7))));
+
+values ((values ((select (values(2)) from t1 where a=8))));
+
+values ((values ((select a from t1 where a=7))))
+union
+values ((values ((select (values(2)) from t1 where a=8))));
+
+values ((values ((select a from t1 where a in ((values (7)))))));
+
+values ((values ((select a from t1 where a in ((values (7), (8)))))));
+
+values ((values
+ ((select a from t1 where a in (values (7) union values (8))))));
+
+values ((values ((select (values(2)) from t1 where a=8))));
+
+--error ER_SUBQUERY_NO_1_ROW
+values ((select (values(2)) from t1 where a<7));
+
+select * from (values ((values ((select a from t1 where a=7))))) dt;
+
+select * from (values ((values ((select (values(2)) from t1 where a=8))))) dt;
+
+insert into t1(a) values ((values (2))), ((values (3)));
+select * from t1;
+
+drop table t1;
+
--echo End of 10.3 tests
--echo #
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 73cbcf5..17186e0 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2487,6 +2487,8 @@ void st_select_lex::init_select()
with_dep= 0;
join= 0;
lock_type= TL_READ_DEFAULT;
+ save_many_values.empty();
+ save_insert_list= 0;
tvc= 0;
in_funcs.empty();
curr_tvc_name= 0;
@@ -8897,7 +8899,6 @@ bool LEX::last_field_generated_always_as_row_end()
VERS_SYS_END_FLAG);
}
-
void st_select_lex_unit::reset_distinct()
{
union_distinct= NULL;
@@ -8913,6 +8914,20 @@ void st_select_lex_unit::reset_distinct()
}
+void LEX::save_values_list_state()
+{
+ current_select->save_many_values= many_values;
+ current_select->save_insert_list= insert_list;
+}
+
+
+void LEX::restore_values_list_state()
+{
+ many_values= current_select->save_many_values;
+ insert_list= current_select->save_insert_list;
+}
+
+
void st_select_lex_unit::fix_distinct()
{
if (union_distinct && this != union_distinct->master_unit())
@@ -9409,6 +9424,7 @@ bool LEX::parsed_insert_select(SELECT_LEX *first_select)
bool LEX::parsed_TVC_start()
{
SELECT_LEX *sel;
+ save_values_list_state();
many_values.empty();
insert_list= 0;
if (!(sel= alloc_select(TRUE)) ||
@@ -9422,14 +9438,13 @@ bool LEX::parsed_TVC_start()
SELECT_LEX *LEX::parsed_TVC_end()
{
-
SELECT_LEX *res= pop_select(); // above TVC select
if (!(res->tvc=
new (thd->mem_root) table_value_constr(many_values,
res,
res->options)))
return NULL;
- many_values.empty();
+ restore_values_list_state();
return res;
}
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index d3b316d..6ef36a2 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1287,6 +1287,8 @@ class st_select_lex: public st_select_lex_node
/* it is for correct printing SELECT options */
thr_lock_type lock_type;
+ List<List_item> save_many_values;
+ List<Item> *save_insert_list;
table_value_constr *tvc;
bool in_tvc;
@@ -4409,13 +4411,6 @@ struct LEX: public Query_tables_list
return false;
}
- void tvc_start()
- {
- field_list.empty();
- many_values.empty();
- insert_list= 0;
- }
-
SELECT_LEX_UNIT *alloc_unit();
SELECT_LEX *alloc_select(bool is_select);
SELECT_LEX_UNIT *create_unit(SELECT_LEX*);
@@ -4470,6 +4465,8 @@ struct LEX: public Query_tables_list
bool distinct);
SELECT_LEX *parsed_subselect(SELECT_LEX_UNIT *unit);
bool parsed_insert_select(SELECT_LEX *firs_select);
+ void save_values_list_state();
+ void restore_values_list_state();
bool parsed_TVC_start();
SELECT_LEX *parsed_TVC_end();
TABLE_LIST *parsed_derived_table(SELECT_LEX_UNIT *unit,
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
index 0957de2..63e9e76 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -672,6 +672,8 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl,
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->exclude_from_table_unique_test=
+ tvc_sl->exclude_from_table_unique_test;
lex->current_select= wrapper_sl;
item= new (thd->mem_root) Item_field(thd, &wrapper_sl->context,
1
0
[Commits] 0ce31b9: MDEV-25002 ON expressions cannot contain outer references
by IgorBabaev 05 Mar '21
by IgorBabaev 05 Mar '21
05 Mar '21
revision-id: 0ce31b93c4b85d17e467c922c8359949c18da7cb (mariadb-10.2.31-777-g0ce31b9)
parent(s): 7759991a06d54630214f19eaa0ec39bd21bf09df
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-03-04 23:02:47 -0800
message:
MDEV-25002 ON expressions cannot contain outer references
A bogus error message was issued for any outer references occurred in
ON expressions used in subqueries. This prevented execution of queries
containing subqueries as soon as they used outer references in their ON
clauses. This happened because the Name_resolution_context structure
created for any ON expression erroneously had the field outer_context set
to NULL. The fields select_lex of this structure was not set correctly
either.
The idea of the fix was taken from mysql code of the function
push_new_name_resolution_context().
Approved by dmitry.shulga(a)mariadb.com
---
mysql-test/r/subselect.result | 38 +++++++++++++++++++++++++++
mysql-test/r/subselect_no_exists_to_in.result | 38 +++++++++++++++++++++++++++
mysql-test/r/subselect_no_mat.result | 38 +++++++++++++++++++++++++++
mysql-test/r/subselect_no_opts.result | 38 +++++++++++++++++++++++++++
mysql-test/r/subselect_no_scache.result | 38 +++++++++++++++++++++++++++
mysql-test/r/subselect_no_semijoin.result | 38 +++++++++++++++++++++++++++
mysql-test/t/subselect.test | 35 ++++++++++++++++++++++++
sql/sql_parse.cc | 2 ++
8 files changed, 265 insertions(+)
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index d4d61eb..a03a2cf 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -7331,4 +7331,42 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo
pk i c pk i c
1 10 foo 1 10 foo
DROP TABLE t;
+#
+# MDEV-25002: Outer reference in ON clause of subselect
+#
+create table t1 (
+pk int primary key,
+a int
+) engine=myisam;
+insert into t1 values (1,1), (2,2);
+create table t2 (
+pk int primary key,
+b int
+) engine=myisam;
+insert into t2 values (1,1), (2,3);
+create table t3 (a int);
+insert into t3 values (1),(2);
+select a,
+(select count(*) from t1, t2
+where t2.pk=t3.a and t1.pk=1) as sq
+from t3;
+a sq
+1 1
+2 1
+select a,
+(select count(*) from t1 join t2 on t2.pk=t3.a
+where t1.pk=1) as sq
+from t3;
+a sq
+1 1
+2 1
+select a from t3
+where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1);
+a
+1
+select a from t3
+where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1);
+a
+1
+drop table t1,t2,t3;
# End of 10.2 tests
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index fa9ab8b..f7da3fd 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -7331,6 +7331,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo
pk i c pk i c
1 10 foo 1 10 foo
DROP TABLE t;
+#
+# MDEV-25002: Outer reference in ON clause of subselect
+#
+create table t1 (
+pk int primary key,
+a int
+) engine=myisam;
+insert into t1 values (1,1), (2,2);
+create table t2 (
+pk int primary key,
+b int
+) engine=myisam;
+insert into t2 values (1,1), (2,3);
+create table t3 (a int);
+insert into t3 values (1),(2);
+select a,
+(select count(*) from t1, t2
+where t2.pk=t3.a and t1.pk=1) as sq
+from t3;
+a sq
+1 1
+2 1
+select a,
+(select count(*) from t1 join t2 on t2.pk=t3.a
+where t1.pk=1) as sq
+from t3;
+a sq
+1 1
+2 1
+select a from t3
+where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1);
+a
+1
+select a from t3
+where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1);
+a
+1
+drop table t1,t2,t3;
# End of 10.2 tests
set optimizer_switch=default;
select @@optimizer_switch like '%exists_to_in=off%';
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index c4d5b28..6ab304c 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -7324,6 +7324,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo
pk i c pk i c
1 10 foo 1 10 foo
DROP TABLE t;
+#
+# MDEV-25002: Outer reference in ON clause of subselect
+#
+create table t1 (
+pk int primary key,
+a int
+) engine=myisam;
+insert into t1 values (1,1), (2,2);
+create table t2 (
+pk int primary key,
+b int
+) engine=myisam;
+insert into t2 values (1,1), (2,3);
+create table t3 (a int);
+insert into t3 values (1),(2);
+select a,
+(select count(*) from t1, t2
+where t2.pk=t3.a and t1.pk=1) as sq
+from t3;
+a sq
+1 1
+2 1
+select a,
+(select count(*) from t1 join t2 on t2.pk=t3.a
+where t1.pk=1) as sq
+from t3;
+a sq
+1 1
+2 1
+select a from t3
+where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1);
+a
+1
+select a from t3
+where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1);
+a
+1
+drop table t1,t2,t3;
# End of 10.2 tests
set optimizer_switch=default;
select @@optimizer_switch like '%materialization=on%';
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index fd1c527..338ddd5 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -7322,5 +7322,43 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo
pk i c pk i c
1 10 foo 1 10 foo
DROP TABLE t;
+#
+# MDEV-25002: Outer reference in ON clause of subselect
+#
+create table t1 (
+pk int primary key,
+a int
+) engine=myisam;
+insert into t1 values (1,1), (2,2);
+create table t2 (
+pk int primary key,
+b int
+) engine=myisam;
+insert into t2 values (1,1), (2,3);
+create table t3 (a int);
+insert into t3 values (1),(2);
+select a,
+(select count(*) from t1, t2
+where t2.pk=t3.a and t1.pk=1) as sq
+from t3;
+a sq
+1 1
+2 1
+select a,
+(select count(*) from t1 join t2 on t2.pk=t3.a
+where t1.pk=1) as sq
+from t3;
+a sq
+1 1
+2 1
+select a from t3
+where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1);
+a
+1
+select a from t3
+where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1);
+a
+1
+drop table t1,t2,t3;
# End of 10.2 tests
set @optimizer_switch_for_subselect_test=null;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 48d5bc9..741b070 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -7337,6 +7337,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo
pk i c pk i c
1 10 foo 1 10 foo
DROP TABLE t;
+#
+# MDEV-25002: Outer reference in ON clause of subselect
+#
+create table t1 (
+pk int primary key,
+a int
+) engine=myisam;
+insert into t1 values (1,1), (2,2);
+create table t2 (
+pk int primary key,
+b int
+) engine=myisam;
+insert into t2 values (1,1), (2,3);
+create table t3 (a int);
+insert into t3 values (1),(2);
+select a,
+(select count(*) from t1, t2
+where t2.pk=t3.a and t1.pk=1) as sq
+from t3;
+a sq
+1 1
+2 1
+select a,
+(select count(*) from t1 join t2 on t2.pk=t3.a
+where t1.pk=1) as sq
+from t3;
+a sq
+1 1
+2 1
+select a from t3
+where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1);
+a
+1
+select a from t3
+where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1);
+a
+1
+drop table t1,t2,t3;
# End of 10.2 tests
set optimizer_switch=default;
select @@optimizer_switch like '%subquery_cache=on%';
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 2df8073..ebabafb 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -7322,6 +7322,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo
pk i c pk i c
1 10 foo 1 10 foo
DROP TABLE t;
+#
+# MDEV-25002: Outer reference in ON clause of subselect
+#
+create table t1 (
+pk int primary key,
+a int
+) engine=myisam;
+insert into t1 values (1,1), (2,2);
+create table t2 (
+pk int primary key,
+b int
+) engine=myisam;
+insert into t2 values (1,1), (2,3);
+create table t3 (a int);
+insert into t3 values (1),(2);
+select a,
+(select count(*) from t1, t2
+where t2.pk=t3.a and t1.pk=1) as sq
+from t3;
+a sq
+1 1
+2 1
+select a,
+(select count(*) from t1 join t2 on t2.pk=t3.a
+where t1.pk=1) as sq
+from t3;
+a sq
+1 1
+2 1
+select a from t3
+where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1);
+a
+1
+select a from t3
+where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1);
+a
+1
+drop table t1,t2,t3;
# End of 10.2 tests
#
# MDEV-19714: JOIN::pseudo_bits_cond is not visible in EXPLAIN FORMAT=JSON
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 7e16321..57ac431 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -6188,4 +6188,39 @@ SELECT * FROM t t1 RIGHT JOIN t t2 ON (t2.pk = t1.pk)
DROP TABLE t;
+--echo #
+--echo # MDEV-25002: Outer reference in ON clause of subselect
+--echo #
+
+create table t1 (
+ pk int primary key,
+ a int
+) engine=myisam;
+insert into t1 values (1,1), (2,2);
+
+create table t2 (
+ pk int primary key,
+ b int
+) engine=myisam;
+insert into t2 values (1,1), (2,3);
+
+create table t3 (a int);
+insert into t3 values (1),(2);
+
+select a,
+ (select count(*) from t1, t2
+ where t2.pk=t3.a and t1.pk=1) as sq
+from t3;
+select a,
+ (select count(*) from t1 join t2 on t2.pk=t3.a
+ where t1.pk=1) as sq
+from t3;
+
+select a from t3
+ where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1);
+select a from t3
+ where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1);
+
+drop table t1,t2,t3;
+
--echo # End of 10.2 tests
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 7f75624..e8ab9a2 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -8774,6 +8774,8 @@ push_new_name_resolution_context(THD *thd,
left_op->first_leaf_for_name_resolution();
on_context->last_name_resolution_table=
right_op->last_leaf_for_name_resolution();
+ on_context->select_lex = thd->lex->current_select;
+ on_context->outer_context = thd->lex->current_context()->outer_context;
return thd->lex->push_context(on_context, thd->mem_root);
}
1
0
revision-id: 9554d6f4a3419a080c4cbf393bbcde25119b13da (mariadb-10.5.2-426-g9554d6f4a34)
parent(s): 079179eacbd74f5c73c93d8947aaf4838556393e
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-03-04 12:58:05 +0300
message:
MDEV-17399: JSON_TABLE, code cleanup
In class Json_table_nested_path:
- Make most members private.
- Provide the interfaces:
= "Builder" interface for use from the parser.
= Scanning/reading the value interface for use from the executor.
In class Table_function_json_table:
- Make most members private.
- Provide the interfaces:
= "Builder" for use from the parser. There are still a few public
member vars that are used as parser context.
= Functions to get/set various properties from the optimizer.
- Remove unused members: m_depth, m_cur_depth.
In class ha_json_table:
- Mark override functions with "override".
- Remove unnecessary function implementations.
- Factor out common code from rnd_next() and rnd_pos() into
fill_column_values().
In class Create_json_table:
- Remove force_not_null_cols argument support.
---
sql/sql_yacc.yy | 9 +-
sql/table_function.cc | 337 ++++++++++++++++++++++++++------------------------
sql/table_function.h | 149 +++++++++++++---------
3 files changed, 271 insertions(+), 224 deletions(-)
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index eb36e7f3b78..166abfc55b9 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -11535,7 +11535,7 @@ json_table_column:
lex->json_table->m_cur_json_table_column=
new (thd->mem_root) Json_table_column(f,
- lex->json_table->m_sql_nest);
+ lex->json_table->get_cur_nested_path());
if (unlikely(!f ||
!lex->json_table->m_cur_json_table_column))
@@ -11556,14 +11556,14 @@ json_table_column:
{
LEX *lex=Lex;
Json_table_nested_path *np= new (thd->mem_root)
- Json_table_nested_path(lex->json_table->m_sql_nest);
+ Json_table_nested_path();
np->set_path(thd, $3);
- lex->json_table->add_nested(np);
+ lex->json_table->start_nested_path(np);
}
json_table_columns_clause
{
LEX *lex=Lex;
- lex->json_table->leave_nested();
+ lex->json_table->end_nested_path();
}
;
@@ -11659,7 +11659,6 @@ table_function:
if (unlikely(!jt))
MYSQL_YYABORT;
Lex->json_table= jt;
- jt->m_sql_nest= &jt->m_nested_path;
}
json_text_literal json_table_columns_clause ')' opt_as ident_table_alias
{
diff --git a/sql/table_function.cc b/sql/table_function.cc
index a11e12101d3..8c92df94895 100644
--- a/sql/table_function.cc
+++ b/sql/table_function.cc
@@ -46,10 +46,13 @@ static table_function_handlerton table_function_hton;
class ha_json_table: public handler
{
-protected:
Table_function_json_table *m_jt;
- String m_tmps;
- String *m_js;
+
+ String *m_js; // The JSON document we're reading
+ String m_tmps; // Buffer for the above
+
+ int fill_column_values(uchar * buf, uchar *pos);
+
public:
ha_json_table(TABLE_SHARE *share_arg, Table_function_json_table *jt):
handler(&table_function_hton.m_hton, share_arg), m_jt(jt)
@@ -62,44 +65,42 @@ class ha_json_table: public handler
that call makes no sence for ha_json_table.
*/
mark_trx_read_write_done= 1;
+
+ /* See ha_json_table::position for format definition */
ref_length= m_jt->m_columns.elements * 4;
}
~ha_json_table() {}
- handler *clone(const char *name, MEM_ROOT *mem_root) { return NULL; }
- const char *index_type(uint inx) { return "NONE"; }
+ handler *clone(const char *name, MEM_ROOT *mem_root) override { return NULL; }
/* Rows also use a fixed-size format */
- enum row_type get_row_type() const { return ROW_TYPE_FIXED; }
- ulonglong table_flags() const
+ enum row_type get_row_type() const override { return ROW_TYPE_FIXED; }
+ ulonglong table_flags() const override
{
return (HA_FAST_KEY_READ | /*HA_NO_BLOBS |*/ HA_NULL_IN_KEY |
HA_CAN_SQL_HANDLER |
HA_REC_NOT_IN_SEQ | HA_NO_TRANSACTIONS |
- HA_HAS_RECORDS | HA_CAN_HASH_KEYS);
+ HA_HAS_RECORDS);
}
- ulong index_flags(uint inx, uint part, bool all_parts) const
+ ulong index_flags(uint inx, uint part, bool all_parts) const override
{
+ DBUG_ASSERT(0);
return HA_ONLY_WHOLE_INDEX | HA_KEY_SCAN_NOT_ROR;
}
- ha_rows records() { return HA_POS_ERROR; }
- uint max_supported_keys() const { return 1; }
- uint max_supported_key_part_length() const { return MAX_KEY_LENGTH; }
-
- int open(const char *name, int mode, uint test_if_locked);
- int close(void) { return 0; }
- int rnd_init(bool scan);
- int rnd_next(uchar *buf);
- int rnd_pos(uchar * buf, uchar *pos);
- void position(const uchar *record);
- int can_continue_handler_scan() { return 1; }
- int info(uint);
- int extra(enum ha_extra_function operation);
+ ha_rows records() override { return HA_POS_ERROR; }
+
+ int open(const char *name, int mode, uint test_if_locked) override
+ { return 0; }
+ int close(void) override { return 0; }
+ int rnd_init(bool scan) override;
+ int rnd_next(uchar *buf) override;
+ int rnd_pos(uchar * buf, uchar *pos) override;
+ void position(const uchar *record) override;
+ int info(uint) override;
+ int extra(enum ha_extra_function operation) override { return 0; }
THR_LOCK_DATA **store_lock(THD *thd, THR_LOCK_DATA **to,
- enum thr_lock_type lock_type)
+ enum thr_lock_type lock_type) override
{ return NULL; }
int create(const char *name, TABLE *form, HA_CREATE_INFO *create_info)
- { return 1; }
-private:
- void update_key_stats();
+ override { return 1; }
};
@@ -123,7 +124,7 @@ class Create_json_table: public Data_type_statistics
m_null_count(0)
{ }
- void add_field(TABLE *table, Field *field, uint fieldnr, bool force_not_null_cols);
+ void add_field(TABLE *table, Field *field, uint fieldnr);
TABLE *start(THD *thd,
TMP_TABLE_PARAM *param,
@@ -143,14 +144,16 @@ class Create_json_table: public Data_type_statistics
@detail
Note: non-root nested paths are set to scan one JSON node (that is, a
- "subdocument")
+ "subdocument").
+ The [str ... end] typically includes content beyond the end of the
+ subdocoument.
*/
void Json_table_nested_path::scan_start(CHARSET_INFO *i_cs,
const uchar *str, const uchar *end)
{
json_get_path_start(&m_engine, i_cs, str, end, &m_cur_path);
- m_cur_nested= 0;
+ m_cur_nested= NULL;
m_null= false;
m_ordinality_counter= 0;
}
@@ -202,18 +205,6 @@ int Json_table_nested_path::scan_next()
}
-int ha_json_table::open(const char *name, int mode, uint test_if_locked)
-{
- return 0;
-}
-
-
-int ha_json_table::extra(enum ha_extra_function operation)
-{
- return 0;
-}
-
-
int ha_json_table::rnd_init(bool scan)
{
Json_table_nested_path &p= m_jt->m_nested_path;
@@ -229,6 +220,12 @@ int ha_json_table::rnd_init(bool scan)
}
+/*
+ @brief
+ Store JSON value in an SQL field, doing necessary special conversions
+ for JSON's null, true, and false.
+*/
+
static int store_json_in_field(Field *f, const json_engine_t *je)
{
switch (je->value_type)
@@ -253,22 +250,58 @@ static int store_json_in_field(Field *f, const json_engine_t *je)
}
+/*
+ Add the first child to this nested path, connecting it with all necessary
+ links.
+*/
+void Json_table_nested_path::add_first_child(Json_table_nested_path *np)
+{
+ np->m_parent= this;
+ DBUG_ASSERT(!m_nested);
+ m_nested= np;
+}
+
+
+/*
+ Add the next sibling to this nested path, connecting it with all necessary
+ links.
+*/
+
+void Json_table_nested_path::add_sibling(Json_table_nested_path *np)
+{
+ np->m_parent= m_parent;
+ DBUG_ASSERT(!m_next_nested);
+ m_next_nested= np;
+}
+
+
+bool Json_table_nested_path::check_error(const char *str)
+{
+ if (m_engine.s.error)
+ {
+ report_json_error_ex(str, &m_engine, "JSON_TABLE", 0,
+ Sql_condition::WARN_LEVEL_ERROR);
+ return true; // Error
+ }
+ return false; // Ok
+}
+
+
int ha_json_table::rnd_next(uchar *buf)
{
- Field **f= table->field;
- Json_table_column *jc;
enum_check_fields cf_orig;
if (!m_js)
return HA_ERR_END_OF_FILE;
+ /*
+ Step 1: Move the root nested path to the next record (this implies moving
+ its child nested paths accordingly)
+ */
if (m_jt->m_nested_path.scan_next())
{
- if (m_jt->m_nested_path.m_engine.s.error)
+ if (m_jt->m_nested_path.check_error(m_js->ptr()))
{
- report_json_error_ex(m_js->ptr(), &m_jt->m_nested_path.m_engine,
- "JSON_TABLE", 0, Sql_condition::WARN_LEVEL_ERROR);
-
/*
We already reported an error, so returning an
error code that just doesn't produce extra
@@ -278,93 +311,33 @@ int ha_json_table::rnd_next(uchar *buf)
}
return HA_ERR_END_OF_FILE;
}
-
- cf_orig= table->in_use->count_cuted_fields;
- table->in_use->count_cuted_fields= CHECK_FIELD_EXPRESSION;
+
/*
- Get the values for each field of the table
+ Step 2: Read values for all columns (the columns refer to nested paths
+ they are in).
*/
- List_iterator_fast<Json_table_column> jc_i(m_jt->m_columns);
- my_ptrdiff_t ptrdiff= buf - table->record[0];
- while ((jc= jc_i++))
- {
- if (!bitmap_is_set(table->read_set, (*f)->field_index))
- goto cont_loop;
-
- if (ptrdiff)
- (*f)->move_field_offset(ptrdiff);
- if (jc->m_nest->m_null)
- {
- (*f)->set_null();
- }
- else
- {
- (*f)->set_notnull();
- switch (jc->m_column_type)
- {
- case Json_table_column::FOR_ORDINALITY:
- (*f)->store(jc->m_nest->m_ordinality_counter, TRUE);
- break;
- case Json_table_column::PATH:
- case Json_table_column::EXISTS_PATH:
- {
- json_engine_t je;
- json_engine_t &nest_je= jc->m_nest->m_engine;
- json_path_step_t *cur_step;
- uint array_counters[JSON_DEPTH_LIMIT];
- int not_found;
-
- json_scan_start(&je, nest_je.s.cs,
- nest_je.value_begin, nest_je.s.str_end);
-
- cur_step= jc->m_path.steps;
- not_found= json_find_path(&je, &jc->m_path, &cur_step, array_counters) ||
- json_read_value(&je);
+ cf_orig= table->in_use->count_cuted_fields;
+ table->in_use->count_cuted_fields= CHECK_FIELD_EXPRESSION;
- if (jc->m_column_type == Json_table_column::EXISTS_PATH)
- {
- (*f)->store(!not_found);
- }
- else /*PATH*/
- {
- if (not_found)
- jc->m_on_empty.respond(jc, *f);
- else
- {
- if (!json_value_scalar(&je) ||
- store_json_in_field(*f, &je))
- jc->m_on_error.respond(jc, *f);
- else
- {
- /*
- If the path contains wildcards, check if there are
- more matches for it in json and report an error if so.
- */
- if (jc->m_path.types_used &
- (JSON_PATH_WILD | JSON_PATH_DOUBLE_WILD) &&
- (json_scan_next(&je) ||
- !json_find_path(&je, &jc->m_path, &cur_step,
- array_counters)))
- jc->m_on_error.respond(jc, *f);
- }
+ fill_column_values(buf, NULL);
- }
- }
- break;
- }
- };
- }
- if (ptrdiff)
- (*f)->move_field_offset(-ptrdiff);
-cont_loop:
- f++;
- }
table->in_use->count_cuted_fields= cf_orig;
return 0;
}
-int ha_json_table::rnd_pos(uchar * buf, uchar *pos)
+/*
+ @brief
+ Fill values of table columns, taking data either from Json_nested_path
+ objects, or from the rowid value
+
+ @param pos NULL means the data should be read from Json_nested_path
+ objects.
+ Non-null value is a pointer to previously saved rowid (see
+ ha_json_table::position() for description)
+*/
+
+int ha_json_table::fill_column_values(uchar * buf, uchar *pos)
{
Field **f= table->field;
Json_table_column *jc;
@@ -373,7 +346,8 @@ int ha_json_table::rnd_pos(uchar * buf, uchar *pos)
while ((jc= jc_i++))
{
- uint f_pos;
+ bool is_null_value;
+ uint int_pos;
if (!bitmap_is_set(table->read_set, (*f)->field_index))
goto cont_loop;
@@ -381,9 +355,17 @@ int ha_json_table::rnd_pos(uchar * buf, uchar *pos)
if (ptrdiff)
(*f)->move_field_offset(ptrdiff);
- f_pos= uint4korr(pos);
+ /*
+ Read the NULL flag:
+ - if we are reading from a rowid value, 0 means SQL NULL.
+ - if scanning json document, read it from the nested path
+ */
+ if (pos)
+ is_null_value= !(int_pos= uint4korr(pos));
+ else
+ is_null_value= jc->m_nest->m_null;
- if (f_pos == 0)
+ if (is_null_value)
{
(*f)->set_null();
}
@@ -393,8 +375,16 @@ int ha_json_table::rnd_pos(uchar * buf, uchar *pos)
switch (jc->m_column_type)
{
case Json_table_column::FOR_ORDINALITY:
- (*f)->store(f_pos, TRUE);
+ {
+ /*
+ Read the cardinality counter:
+ - read it from nested path when scanning the json document
+ - or, read it from rowid when in rnd_pos() call
+ */
+ longlong counter= pos? int_pos: jc->m_nest->m_ordinality_counter;
+ (*f)->store(counter, TRUE);
break;
+ }
case Json_table_column::PATH:
case Json_table_column::EXISTS_PATH:
{
@@ -402,11 +392,27 @@ int ha_json_table::rnd_pos(uchar * buf, uchar *pos)
json_path_step_t *cur_step;
uint array_counters[JSON_DEPTH_LIMIT];
int not_found;
+ const uchar* node_start;
+ const uchar* node_end;
+
+ /*
+ Get the JSON context node that we will need to evaluate PATH or
+ EXISTS against:
+ - when scanning the json document, read it from nested path
+ - when in rnd_pos call, the rowid has the start offset.
+ */
+ if (pos)
+ {
+ node_start= (const uchar *) (m_js->ptr() + (int_pos-1));
+ node_end= (const uchar *) m_js->end();
+ }
+ else
+ {
+ node_start= jc->m_nest->get_value();
+ node_end= jc->m_nest->get_value_end();
+ }
- json_scan_start(&je, m_js->charset(),
- (const uchar *) (m_js->ptr() + (f_pos-1)),
- (const uchar *) m_js->end());
-
+ json_scan_start(&je, m_js->charset(), node_start, node_end);
cur_step= jc->m_path.steps;
not_found= json_find_path(&je, &jc->m_path, &cur_step, array_counters) ||
json_read_value(&je);
@@ -447,12 +453,19 @@ int ha_json_table::rnd_pos(uchar * buf, uchar *pos)
(*f)->move_field_offset(-ptrdiff);
cont_loop:
f++;
- pos+= 4;
+ if (pos)
+ pos+= 4;
}
return 0;
}
+int ha_json_table::rnd_pos(uchar * buf, uchar *pos)
+{
+ return fill_column_values(buf, pos);
+}
+
+
/*
The reference has 4 bytes for every column of the JSON_TABLE.
There it keeps 0 for the NULL values, ordinality index for
@@ -481,7 +494,7 @@ void ha_json_table::position(const uchar *record)
case Json_table_column::PATH:
case Json_table_column::EXISTS_PATH:
{
- size_t pos= jc->m_nest->m_engine.value_begin -
+ size_t pos= jc->m_nest->get_value() -
(const uchar *) m_js->ptr() + 1;
int4store(c_ref, pos);
break;
@@ -506,18 +519,11 @@ int ha_json_table::info(uint)
}
-void Create_json_table::add_field(TABLE *table, Field *field,
- uint fieldnr, bool force_not_null_cols)
+void Create_json_table::add_field(TABLE *table, Field *field, uint fieldnr)
{
DBUG_ASSERT(!field->field_name.str ||
strlen(field->field_name.str) == field->field_name.length);
- if (force_not_null_cols)
- {
- field->flags|= NOT_NULL_FLAG;
- field->null_ptr= NULL;
- }
-
if (!(field->flags & NOT_NULL_FLAG))
m_null_count++;
@@ -858,7 +864,7 @@ bool Create_json_table::add_json_table_fields(THD *thd, TABLE *table,
if (!f)
goto err_exit;
f->init(table);
- add_field(table, f, fieldnr++, FALSE);
+ add_field(table, f, fieldnr++);
}
share->fields= fieldnr;
@@ -1090,23 +1096,31 @@ int Json_table_column::On_response::print(const char *name, String *str) const
}
-void Table_function_json_table::add_nested(Json_table_nested_path *np)
-{
- *m_sql_nest->m_nested_hook= np;
- m_sql_nest->m_nested_hook= &np->m_next_nested;
- m_sql_nest= np;
- if (++m_cur_depth > m_depth)
- m_depth= m_cur_depth;
+void Table_function_json_table::start_nested_path(Json_table_nested_path *np)
+{
+ if (cur_last_sibling)
+ {
+ cur_last_sibling->add_sibling(np);
+ cur_last_sibling= np;
+ }
+ else
+ {
+ cur_parent->add_first_child(np);
+ cur_last_sibling= np;
+ }
+
+ // Make the newly added path the parent
+ cur_parent= cur_last_sibling;
+ cur_last_sibling= NULL;
}
-void Table_function_json_table::leave_nested()
-{
- m_sql_nest= m_sql_nest->m_parent;
- --m_cur_depth;
+void Table_function_json_table::end_nested_path()
+{
+ cur_last_sibling= cur_parent;
+ cur_parent= cur_parent->get_parent();
}
-
/*
@brief
Perform name-resolution phase tasks
@@ -1174,8 +1188,10 @@ int Table_function_json_table::setup(THD *thd, TABLE_LIST *sql_table,
return FALSE;
}
+
void Table_function_json_table::get_estimates(ha_rows *out_rows,
- double *scan_time, double *startup_cost)
+ double *scan_time,
+ double *startup_cost)
{
*out_rows= 40;
*scan_time= 0.0;
@@ -1282,13 +1298,12 @@ int Table_function_json_table::print(THD *thd, TABLE_LIST *sql_table,
void Table_function_json_table::fix_after_pullout(TABLE_LIST *sql_table,
st_select_lex *new_parent, bool merge)
{
- if (m_dep_tables)
- sql_table->dep_tables&= ~m_dep_tables;
+ sql_table->dep_tables&= ~m_dep_tables;
+
m_json->fix_after_pullout(new_parent, &m_json, merge);
m_dep_tables= m_json->used_tables();
- if (m_dep_tables)
- sql_table->dep_tables|= m_dep_tables;
+ sql_table->dep_tables|= m_dep_tables;
}
diff --git a/sql/table_function.h b/sql/table_function.h
index 822af71c83d..14cae72c8e2 100644
--- a/sql/table_function.h
+++ b/sql/table_function.h
@@ -36,32 +36,54 @@ class Json_table_column;
COLUMNS( a INT PATH '$.a' ,
NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$',
NESTED PATH '$.c[*]' COLUMNS(x INT PATH '$')),
- NESTED PATH '$.n[*]' COLUMNS (z INT PAHT '$'))
+ NESTED PATH '$.n[*]' COLUMNS (z INT PATH '$'))
results in 4 'nested_path' created:
root nested_b nested_c nested_n
m_path '$[*]' '$.b[*]' '$.c[*]' '$.n[*]
m_nested &nested_b &nested_c NULL NULL
n_next_nested NULL &nested_n NULL NULL
-and 4 columns created:
+ and 4 columns created:
a b x z
m_nest &root &nested_b &nested_c &nested_n
*/
-
class Json_table_nested_path : public Sql_alloc
{
public:
- bool m_null; // TRUE <=> produce SQL NULL.
+ json_path_t m_path; /* The JSON Path to get the rows from */
- json_path_t m_path;
- json_engine_t m_engine;
- json_path_t m_cur_path;
+ /*** Construction interface ***/
+ Json_table_nested_path():
+ m_null(TRUE), m_parent(NULL), m_nested(NULL), m_next_nested(NULL)
+ {}
+
+ int set_path(THD *thd, const LEX_CSTRING &path);
+
+ void add_first_child(Json_table_nested_path *np);
+ void add_sibling(Json_table_nested_path *np);
+ Json_table_nested_path *get_parent() { return m_parent; }
+
+ /*** Methods for performing a scan ***/
+ void scan_start(CHARSET_INFO *i_cs, const uchar *str, const uchar *end);
+ int scan_next();
+ bool check_error(const char *str);
- /* Counts the rows produced. Value is set to the FOR ORDINALITY coluns */
+ /*** Members for getting the values we've scanned to ***/
+ const uchar *get_value() { return m_engine.value_begin; }
+ const uchar *get_value_end() { return m_engine.s.str_end; }
+
+ bool m_null; // TRUE <=> producing a NULL-complemented row.
+
+ /* Counts the rows produced. Used by FOR ORDINALITY columns */
longlong m_ordinality_counter;
- /* the Json_table_nested_path that nests this. */
+ int print(THD *thd, Field ***f, String *str,
+ List_iterator_fast<Json_table_column> &it,
+ Json_table_column **last_column);
+private:
+ /*** Members describing NESTED PATH structure ***/
+ /* Parent nested path. The "root" path has this NULL */
Json_table_nested_path *m_parent;
/* The head of the list of nested NESTED PATH statements. */
@@ -70,30 +92,28 @@ class Json_table_nested_path : public Sql_alloc
/* in the above list items are linked with the */
Json_table_nested_path *m_next_nested;
- /*
- The pointer to the 'm_next_nested' member of the
- last item of the above list. So we can add new item to
- the list doing *m_next_nexted_hook= new_item_ptr
- */
- Json_table_nested_path **m_nested_hook;
+ /*** Members describing current JSON Path scan state ***/
+ /* The JSON Parser and JSON Path evaluator */
+ json_engine_t m_engine;
- /*
- The NESTED PATH that is currently scanned in rnd_next.
- */
+ /* The path the parser is currently pointing to */
+ json_path_t m_cur_path;
+
+ /* The child NESTED PATH we're currently scanning */
Json_table_nested_path *m_cur_nested;
- Json_table_nested_path(Json_table_nested_path *parent_nest):
- m_null(TRUE), m_parent(parent_nest), m_nested(0), m_next_nested(0),
- m_nested_hook(&m_nested) {}
- int set_path(THD *thd, const LEX_CSTRING &path);
- void scan_start(CHARSET_INFO *i_cs, const uchar *str, const uchar *end);
- int scan_next();
- int print(THD *thd, Field ***f, String *str,
- List_iterator_fast<Json_table_column> &it,
- Json_table_column **last_column);
};
+/*
+ @brief
+ Describes the column definition in JSON_TABLE(...) syntax.
+
+ @detail
+ Has methods for printing/handling errors but otherwise it's a static
+ object.
+*/
+
class Json_table_column : public Sql_alloc
{
public:
@@ -166,57 +186,70 @@ class Json_table_column : public Sql_alloc
Then the ha_json_table instance is created based on it in
the create_table_for_function().
*/
+
class Table_function_json_table : public Sql_alloc
{
public:
+ /*** Basic properties of the original JSON_TABLE(...) ***/
+
Item *m_json; /* The JSON value to be parsed. */
/* The COLUMNS(...) part representation. */
Json_table_nested_path m_nested_path;
+
/* The list of table column definitions. */
List<Json_table_column> m_columns;
+ /*** Name resolution functions ***/
+ int setup(THD *thd, TABLE_LIST *sql_table, SELECT_LEX *s_lex);
+
+ /*** Functions for interaction with the Query Optimizer ***/
+ void fix_after_pullout(TABLE_LIST *sql_table,
+ st_select_lex *new_parent, bool merge);
+ void update_used_tables() { m_json->update_used_tables(); }
+
+ table_map used_tables() const { return m_dep_tables; }
+ bool join_cache_allowed() const { return !m_dep_tables; }
+ void get_estimates(ha_rows *out_rows,
+ double *scan_time, double *startup_cost);
+
+ int print(THD *thd, TABLE_LIST *sql_table,
+ String *str, enum_query_type query_type);
+
+ /*** Construction interface to be used from the parser ***/
+ Table_function_json_table(Item *json):
+ m_json(json)
+ {
+ cur_parent= &m_nested_path;
+ cur_last_sibling= NULL;
+ }
+
+ void start_nested_path(Json_table_nested_path *np);
+ void end_nested_path();
+ Json_table_nested_path *get_cur_nested_path() { return cur_parent; }
+
+ /* SQL Parser: current column in JSON_TABLE (...) syntax */
+ Json_table_column *m_cur_json_table_column;
+
+ /* SQL Parser: charset of the current text literal */
+ CHARSET_INFO *m_text_literal_cs;
+
+private:
/*
the JSON argument can be taken from other tables.
We have to mark these tables as dependent so the
mask of these dependent tables is calculated in ::setup().
*/
table_map m_dep_tables;
-
- /*
- The 'depth' of NESTED PATH statements nesting.
- Needed to calculate the reference length.
- m_cur_depth is used in parser.
- */
- uint m_depth, m_cur_depth;
-
- /* used in parser. */
- Json_table_column *m_cur_json_table_column;
- CHARSET_INFO *m_text_literal_cs;
-
- Table_function_json_table(Item *json):
- m_json(json), m_nested_path(0), m_depth(0), m_cur_depth(0) {}
+ /* Current NESTED PATH level being parsed */
+ Json_table_nested_path *cur_parent;
/*
- Used in sql_yacc.yy.
- Represents the current NESTED PATH level being parsed.
+ The last sibling in the current level, if there is any. We need this
+ to call add_sibling() for it
*/
- Json_table_nested_path *m_sql_nest;
- void add_nested(Json_table_nested_path *np);
- void leave_nested();
+ Json_table_nested_path *cur_last_sibling;
- int setup(THD *thd, TABLE_LIST *sql_table, SELECT_LEX *s_lex);
- /* if the table is ready to be used in Item_field::fix_fieds */
- bool join_cache_allowed() const { return !m_dep_tables; }
- table_map used_tables() const { return m_dep_tables; }
- void get_estimates(ha_rows *out_rows,
- double *scan_time, double *startup_cost);
- int print(THD *thd, TABLE_LIST *sql_table,
- String *str, enum_query_type query_type);
-
- void fix_after_pullout(TABLE_LIST *sql_table,
- st_select_lex *new_parent, bool merge);
- void update_used_tables() { m_json->update_used_tables(); }
};
1
0
04 Mar '21
revision-id: 027dcbe0b48eca2e59e2ef137f8f031083546f96 (mariadb-10.3.26-100-g027dcbe)
parent(s): 0f81ca6a0bb21fbba4bca93a7555f7c8e6357b47
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-03-03 22:48:39 -0800
message:
MDEV-22786 Crashes with nested table value constructors
The bug caused crashes of the server when processing queries with nested
table value constructors (TVC) . It happened because the grammar rules to
parse TVC used the same global lists for both nested TVC and nesting TVC.
As a result invalid select trees were constructed for queries with nested
TVC and this led to crashes at the prepare stage.
This patch provides its own lists structures for each TVC nest level.
Besides the patch fixes a bug in the function wrap_tvc() that missed
inheritance of the SELECT_LEX::exclude_from_table_unique_test for
selects that wrapped TVCs. This inheritance is critical for specifications
of derived tables that employ nested TVCs.
Approved by dmitry.shulga(a)mariadb.com
---
mysql-test/main/table_value_constr.result | 175 ++++++++++++++++++++++++++++++
mysql-test/main/table_value_constr.test | 106 ++++++++++++++++++
sql/sql_lex.cc | 45 +++++++-
sql/sql_lex.h | 12 +-
sql/sql_tvc.cc | 2 +
sql/sql_yacc.yy | 3 +-
sql/sql_yacc_ora.yy | 3 +-
7 files changed, 333 insertions(+), 13 deletions(-)
diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index d2965ab..ff6d19a 100644
--- a/mysql-test/main/table_value_constr.result
+++ b/mysql-test/main/table_value_constr.result
@@ -2887,4 +2887,179 @@ drop table t1,t2,t3;
select sum((values(1)));
sum((values(1)))
1
+#
+# MDEV-22786: Nested table values constructors
+#
+values ((values (2)));
+(values (2))
+2
+values ((values (2)), (5), (select 4));
+(values (2)) 5 (select 4)
+2 5 4
+values ((7), (values (2)), (5), (select 4));
+7 (values (2)) 5 (select 4)
+7 2 5 4
+values ((values (2))) union values ((values (3)));
+(values (2))
+2
+3
+values ((values (2))), ((values (3)));
+(values (2))
+2
+3
+values ((values (2))), ((select 4)), ((values (3)));
+(values (2))
+2
+4
+3
+values ((values (4)), (values (5))), ((values (1)), (values (7)));
+(values (4)) (values (5))
+4 5
+1 7
+values ((values (4)), (select 5)), ((select 1), (values (7)));
+(values (4)) (select 5)
+4 5
+1 7
+values ((select 2)) union values ((values (3)));
+(select 2)
+2
+3
+values ((values (2))) union values((select 3));
+(values (2))
+2
+3
+values ((values (2))) union all values ((values (2)));
+(values (2))
+2
+2
+values ((values (4)), (values (5))), ((values (1)), (values (7)))
+union
+values ((values (4)), (select 5)), ((select 2), (values (8)));
+(values (4)) (values (5))
+4 5
+1 7
+2 8
+values ((values (4)), (values (5))), ((values (1)), (values (7)))
+union all
+values ((values (4)), (select 5)), ((select 2), (values (8)));
+(values (4)) (values (5))
+4 5
+1 7
+4 5
+2 8
+values ((values (1) union values (1)));
+(values (1) union values (1))
+1
+values ((values (1) union values (1) union values (1)));
+(values (1) union values (1) union values (1))
+1
+values ((values ((values (4)))));
+(values ((values (4))))
+4
+values ((values ((select 5))));
+(values ((select 5)))
+5
+values ((select (values (4))), (values ((values(5)))));
+(select (values (4))) (values ((values(5))))
+4 5
+values ((select (values (4))), (values ((select 5))));
+(select (values (4))) (values ((select 5)))
+4 5
+values ((select (values (4))), (values ((values(5)))))
+union
+values ((select (values (4))), (values ((select 7))));
+(select (values (4))) (values ((values(5))))
+4 5
+4 7
+values ((values (2))), ((values ((values (4)))));
+(values (2))
+2
+4
+values ((values (2))), ((values ((select 4))));
+(values (2))
+2
+4
+values ((values (2))), ((values ((values (4)))))
+union
+values ((values (8))), ((values ((select 4))));
+(values (2))
+2
+4
+8
+values ((values (2))), ((values ((values (4)))))
+union all
+values ((values (8))), ((values ((select 4))));
+(values (2))
+2
+4
+8
+4
+select * from (values ((values (2)))) dt;
+(values (2))
+2
+select * from (values ((values (2)), (5), (select 4))) dt;
+(values (2)) 5 (select 4)
+2 5 4
+select * from (values ((values (2))) union values ((values (3)))) dt;
+(values (2))
+2
+3
+select * from (values ((values (2))), ((values (3)))) dt;
+(values (2))
+2
+3
+select * from (values ((values (2))), ((values (3)))) dt;
+(values (2))
+2
+3
+select * from (values ((values (2))), ((select 4)), ((values (3)))) dt;
+(values (2))
+2
+4
+3
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+values ((values ((select a from t1 where a=7))));
+(values ((select a from t1 where a=7)))
+7
+values ((values ((select (values(2)) from t1 where a=8))));
+(values ((select (values(2)) from t1 where a=8)))
+NULL
+values ((values ((select a from t1 where a=7))))
+union
+values ((values ((select (values(2)) from t1 where a=8))));
+(values ((select a from t1 where a=7)))
+7
+NULL
+values ((values ((select a from t1 where a in ((values (7)))))));
+(values ((select a from t1 where a in ((values (7))))))
+7
+values ((values ((select a from t1 where a in ((values (7), (8)))))));
+(values ((select a from t1 where a in ((values (7), (8))))))
+7
+values ((values
+((select a from t1 where a in (values (7) union values (8))))));
+(values
+((select a from t1 where a in (values (7) union values (8)))))
+7
+values ((values ((select (values(2)) from t1 where a=8))));
+(values ((select (values(2)) from t1 where a=8)))
+NULL
+values ((select (values(2)) from t1 where a<7));
+ERROR 21000: Subquery returns more than 1 row
+select * from (values ((values ((select a from t1 where a=7))))) dt;
+(values ((select a from t1 where a=7)))
+7
+select * from (values ((values ((select (values(2)) from t1 where a=8))))) dt;
+(values ((select (values(2)) from t1 where a=8)))
+NULL
+insert into t1(a) values ((values (2))), ((values (3)));
+select * from t1;
+a
+3
+7
+1
+2
+3
+drop table t1;
End of 10.3 tests
diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test
index 88d0ac2..3e976f8 100644
--- a/mysql-test/main/table_value_constr.test
+++ b/mysql-test/main/table_value_constr.test
@@ -1522,4 +1522,110 @@ drop table t1,t2,t3;
select sum((values(1)));
+--echo #
+--echo # MDEV-22786: Nested table values constructors
+--echo #
+
+values ((values (2)));
+
+values ((values (2)), (5), (select 4));
+
+values ((7), (values (2)), (5), (select 4));
+
+values ((values (2))) union values ((values (3)));
+
+values ((values (2))), ((values (3)));
+
+values ((values (2))), ((select 4)), ((values (3)));
+
+values ((values (4)), (values (5))), ((values (1)), (values (7)));
+
+values ((values (4)), (select 5)), ((select 1), (values (7)));
+
+values ((select 2)) union values ((values (3)));
+
+values ((values (2))) union values((select 3));
+
+values ((values (2))) union all values ((values (2)));
+
+values ((values (4)), (values (5))), ((values (1)), (values (7)))
+union
+values ((values (4)), (select 5)), ((select 2), (values (8)));
+
+values ((values (4)), (values (5))), ((values (1)), (values (7)))
+union all
+values ((values (4)), (select 5)), ((select 2), (values (8)));
+
+values ((values (1) union values (1)));
+
+values ((values (1) union values (1) union values (1)));
+
+values ((values ((values (4)))));
+
+values ((values ((select 5))));
+
+values ((select (values (4))), (values ((values(5)))));
+
+values ((select (values (4))), (values ((select 5))));
+
+values ((select (values (4))), (values ((values(5)))))
+union
+values ((select (values (4))), (values ((select 7))));
+
+values ((values (2))), ((values ((values (4)))));
+
+values ((values (2))), ((values ((select 4))));
+
+values ((values (2))), ((values ((values (4)))))
+union
+values ((values (8))), ((values ((select 4))));
+
+values ((values (2))), ((values ((values (4)))))
+union all
+values ((values (8))), ((values ((select 4))));
+
+select * from (values ((values (2)))) dt;
+
+select * from (values ((values (2)), (5), (select 4))) dt;
+
+select * from (values ((values (2))) union values ((values (3)))) dt;
+
+select * from (values ((values (2))), ((values (3)))) dt;
+
+select * from (values ((values (2))), ((values (3)))) dt;
+
+select * from (values ((values (2))), ((select 4)), ((values (3)))) dt;
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+
+values ((values ((select a from t1 where a=7))));
+
+values ((values ((select (values(2)) from t1 where a=8))));
+
+values ((values ((select a from t1 where a=7))))
+union
+values ((values ((select (values(2)) from t1 where a=8))));
+
+values ((values ((select a from t1 where a in ((values (7)))))));
+
+values ((values ((select a from t1 where a in ((values (7), (8)))))));
+
+values ((values
+ ((select a from t1 where a in (values (7) union values (8))))));
+
+values ((values ((select (values(2)) from t1 where a=8))));
+
+--error ER_SUBQUERY_NO_1_ROW
+values ((select (values(2)) from t1 where a<7));
+
+select * from (values ((values ((select a from t1 where a=7))))) dt;
+
+select * from (values ((values ((select (values(2)) from t1 where a=8))))) dt;
+
+insert into t1(a) values ((values (2))), ((values (3)));
+select * from t1;
+
+drop table t1;
+
--echo End of 10.3 tests
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 70d795c..c2bc838 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2421,6 +2421,8 @@ void st_select_lex::init_select()
with_dep= 0;
join= 0;
lock_type= TL_READ_DEFAULT;
+ save_many_values.empty();
+ save_insert_list= 0;
tvc= 0;
in_funcs.empty();
curr_tvc_name= 0;
@@ -8302,16 +8304,52 @@ bool LEX::last_field_generated_always_as_row_end()
}
+void LEX::save_values_list_state()
+{
+ current_select->save_many_values= many_values;
+ current_select->save_insert_list= insert_list;
+}
+
+
+void LEX::restore_values_list_state()
+{
+ many_values= current_select->save_many_values;
+ insert_list= current_select->save_insert_list;
+}
+
+
+void LEX::tvc_start()
+{
+ if (current_select == &select_lex)
+ mysql_init_select(this);
+ else
+ save_values_list_state();
+ many_values.empty();
+ insert_list= 0;
+}
+
+
+bool LEX::tvc_start_derived()
+{
+ if (current_select->linkage == GLOBAL_OPTIONS_TYPE ||
+ unlikely(mysql_new_select(this, 1, NULL)))
+ return true;
+ save_values_list_state();
+ many_values.empty();
+ insert_list= 0;
+ return false;
+}
+
+
bool LEX::tvc_finalize()
{
- mysql_init_select(this);
if (unlikely(!(current_select->tvc=
new (thd->mem_root)
table_value_constr(many_values,
current_select,
current_select->options))))
return true;
- many_values.empty();
+ restore_values_list_state();
if (!current_select->master_unit()->fake_select_lex)
current_select->master_unit()->add_fake_select_lex(thd);
return false;
@@ -8326,9 +8364,6 @@ bool LEX::tvc_finalize_derived()
thd->parse_error();
return true;
}
- if (current_select->linkage == GLOBAL_OPTIONS_TYPE ||
- unlikely(mysql_new_select(this, 1, NULL)))
- return true;
current_select->linkage= DERIVED_TABLE_TYPE;
return tvc_finalize();
}
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 979e212..474f317 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1176,6 +1176,8 @@ class st_select_lex: public st_select_lex_node
/* it is for correct printing SELECT options */
thr_lock_type lock_type;
+ List<List_item> save_many_values;
+ List<Item> *save_insert_list;
table_value_constr *tvc;
bool in_tvc;
@@ -4046,12 +4048,10 @@ struct LEX: public Query_tables_list
return false;
}
- void tvc_start()
- {
- field_list.empty();
- many_values.empty();
- insert_list= 0;
- }
+ void save_values_list_state();
+ void restore_values_list_state();
+ void tvc_start();
+ bool tvc_start_derived();
bool tvc_finalize();
bool tvc_finalize_derived();
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
index cb056b0..96c5223 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -673,6 +673,8 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl,
wrapper_sl->nest_level= tvc_sl->nest_level;
wrapper_sl->parsing_place= tvc_sl->parsing_place;
wrapper_sl->linkage= tvc_sl->linkage;
+ wrapper_sl->exclude_from_table_unique_test=
+ tvc_sl->exclude_from_table_unique_test;
lex->current_select= wrapper_sl;
item= new (thd->mem_root) Item_field(thd, &wrapper_sl->context,
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 88f12e9..b26e2dd 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -12334,7 +12334,8 @@ derived_query_specification:
derived_table_value_constructor:
VALUES
{
- Lex->tvc_start();
+ if (Lex->tvc_start_derived())
+ MYSQL_YYABORT;
}
values_list
{
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index eaeaf2e..4af034d 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -12272,7 +12272,8 @@ derived_query_specification:
derived_table_value_constructor:
VALUES
{
- Lex->tvc_start();
+ if (Lex->tvc_start_derived())
+ MYSQL_YYABORT;
}
values_list
{
1
0
[Commits] ed3a627: MDEV-21104 Wrong result (extra rows and wrong values) with incremental BNLH
by IgorBabaev 04 Mar '21
by IgorBabaev 04 Mar '21
04 Mar '21
revision-id: ed3a627f612e08b99a2f2d7a0ad91966b11fc504 (mariadb-10.2.31-769-ged3a627)
parent(s): 259e5243faa88370bbb890342326a324fb648f7d
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-03-03 19:33:04 -0800
message:
MDEV-21104 Wrong result (extra rows and wrong values) with incremental BNLH
This bug could affect multi-way join queries with embedded outer joins that
contained a conjunctive IS NULL predicate over a non-nullable column from
inner table of an outer join. The predicate could occur in WHERE condition
or in ON condition. Due to this bug a wrong result set could be returned by
the query. The bug manifested itself only when join buffers were employed
for join operations.
The problem appeared because
- a bug in the function JOIN_CACHE::get_match_flag_by_pos that not always
returned proper match flags for embedding outer joins stored together
with table rows put a join buffer.
- bug in the function JOIN_CACHE::join_matching_records that not always
correctly determined that a row from the buffer could be skipped due
to applied 'not_exists' optimization.
The patch introduces a new function that finds the match flag for a record
from join buffer specifying the the buffer where this flag has to be found.
The function is called JOIN_CACHE::get_match_flag_by_pos_from_join_buffer().
Now this function rather than JOIN_CACHE::get_match_flag_by_pos() is used
in JOIN_CACHE::skip_if_matched() to check whether a record from the join
buffer must be ignored when extending the record by null complements.
Also the code of the function JOIN_CACHE::skip_if_not_needed_match() has
been changed. The function checks whether a record from the join buffer
still may produce some useful extensions.
Also some clarifying comments has been added.
Approved by monty(a)mariadb.com.
---
mysql-test/r/join_cache.result | 53 +++++++++++++++++++++++
mysql-test/t/join_cache.test | 36 ++++++++++++++++
sql/sql_join_cache.cc | 95 +++++++++++++++++++++++++++++++++++++-----
sql/sql_join_cache.h | 11 ++++-
4 files changed, 184 insertions(+), 11 deletions(-)
diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index e41c79a..87c4079 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -6054,4 +6054,57 @@ select f2 from t2,t1 where f2 = 0;
f2
drop table t1, t2;
set join_buffer_size=@save_join_buffer_size;
+#
+# MDEV-21104: BNLH used for multi-join query with embedded outer join
+# and possible 'not exists' optimization
+#
+set join_cache_level=4;
+CREATE TABLE t1 (a int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b int, c int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,2),(2,4);
+CREATE TABLE t3 (d int, KEY(d)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1),(2);
+CREATE TABLE t4 (e int primary key) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (1),(2);
+ANALYZE TABLE t1,t2,t3,t4;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+test.t2 analyze status OK
+test.t3 analyze status OK
+test.t4 analyze status OK
+SELECT * FROM t2 LEFT JOIN t3 ON c = d;
+b c d
+1 2 2
+2 4 NULL
+SELECT * FROM (t2 LEFT JOIN t3 ON c = d ) JOIN t4;
+b c d e
+1 2 2 1
+2 4 NULL 1
+1 2 2 2
+2 4 NULL 2
+EXPLAIN SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t3 hash_index d #hash#d:d 5:5 test.t2.c 2 Using where; Using index; Using join buffer (incremental, BNLH join)
+1 SIMPLE t4 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t2.b 2 Using index; Using join buffer (incremental, BNLH join)
+SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e;
+a b c d e
+1 1 2 2 1
+2 1 2 2 1
+1 2 4 NULL 2
+2 2 4 NULL 2
+EXPLAIN SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e
+WHERE e IS NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+1 SIMPLE t3 hash_index d #hash#d:d 5:5 test.t2.c 2 Using where; Using index; Using join buffer (incremental, BNLH join)
+1 SIMPLE t4 hash_index PRIMARY #hash#PRIMARY:PRIMARY 4:4 test.t2.b 2 Using where; Using index; Not exists; Using join buffer (incremental, BNLH join)
+SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e
+WHERE e IS NULL;
+a b c d e
+DROP TABLE t1,t2,t3,t4;
+set join_cache_level=@save_join_cache_level;
set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
index 9576d59..15cd1e9 100644
--- a/mysql-test/t/join_cache.test
+++ b/mysql-test/t/join_cache.test
@@ -4014,5 +4014,41 @@ select f2 from t2,t1 where f2 = 0;
drop table t1, t2;
set join_buffer_size=@save_join_buffer_size;
+
+--echo #
+--echo # MDEV-21104: BNLH used for multi-join query with embedded outer join
+--echo # and possible 'not exists' optimization
+--echo #
+
+set join_cache_level=4;
+
+CREATE TABLE t1 (a int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b int, c int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,2),(2,4);
+CREATE TABLE t3 (d int, KEY(d)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1),(2);
+CREATE TABLE t4 (e int primary key) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (1),(2);
+ANALYZE TABLE t1,t2,t3,t4;
+
+SELECT * FROM t2 LEFT JOIN t3 ON c = d;
+SELECT * FROM (t2 LEFT JOIN t3 ON c = d ) JOIN t4;
+
+let $q1=
+SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e;
+eval EXPLAIN $q1;
+eval $q1;
+
+let $q2=
+SELECT * FROM t1 LEFT JOIN ( ( t2 LEFT JOIN t3 ON c = d ) JOIN t4 ) ON b = e
+ WHERE e IS NULL;
+eval EXPLAIN $q2;
+eval $q2;
+
+DROP TABLE t1,t2,t3,t4;
+
+set join_cache_level=@save_join_cache_level;
+
# The following command must be the last one in the file
set @@optimizer_switch=@save_optimizer_switch;
diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc
index 1dfc938..6437740 100644
--- a/sql/sql_join_cache.cc
+++ b/sql/sql_join_cache.cc
@@ -1649,7 +1649,7 @@ void JOIN_CACHE::get_record_by_pos(uchar *rec_ptr)
}
-/*
+/*
Get the match flag from the referenced record: the default implementation
SYNOPSIS
@@ -1661,6 +1661,7 @@ void JOIN_CACHE::get_record_by_pos(uchar *rec_ptr)
get the match flag for the record pointed by the reference at the position
rec_ptr. If the match flag is placed in one of the previous buffers the
function first reaches the linked record fields in this buffer.
+ The function returns the value of the first encountered match flag.
RETURN VALUE
match flag for the record at the position rec_ptr
@@ -1685,6 +1686,46 @@ enum JOIN_CACHE::Match_flag JOIN_CACHE::get_match_flag_by_pos(uchar *rec_ptr)
/*
+ Get the match flag for the referenced record from specified join buffer
+
+ SYNOPSIS
+ get_match_flag_by_pos_from_join_buffer()
+ rec_ptr position of the first field of the record in the join buffer
+ tab join table with join buffer where to look for the match flag
+
+ DESCRIPTION
+ This default implementation of the get_match_flag_by_pos_from_join_buffer
+ method gets the match flag for the record pointed by the reference at the
+ position rec_ptr from the join buffer attached to the join table tab.
+
+ RETURN VALUE
+ match flag for the record at the position rec_ptr from the join
+ buffer attached to the table tab.
+*/
+
+enum JOIN_CACHE::Match_flag
+JOIN_CACHE::get_match_flag_by_pos_from_join_buffer(uchar *rec_ptr,
+ JOIN_TAB *tab)
+{
+ DBUG_ASSERT(tab->cache && tab->cache->with_match_flag);
+ Match_flag match_fl= MATCH_NOT_FOUND;
+ JOIN_CACHE *prev_cache= 0;
+ for (JOIN_CACHE *cache= this; cache; cache= prev_cache)
+ {
+ if (cache->join_tab == tab)
+ {
+ match_fl= (enum Match_flag) rec_ptr[0];
+ return match_fl;
+ }
+ if ((prev_cache= cache->prev_cache))
+ rec_ptr= prev_cache->get_rec_ref(rec_ptr);
+ }
+ DBUG_ASSERT(0);
+ return match_fl;
+}
+
+
+/*
Calculate the increment of the auxiliary buffer for a record write
SYNOPSIS
@@ -1954,6 +1995,10 @@ bool JOIN_CACHE::read_referenced_field(CACHE_FIELD *copy,
If the record is skipped the value of 'pos' is set to point to the position
right after the record.
+ NOTE
+ Currently this function is called only when generating null complemented
+ records for outer joins (=> only when join_tab->first_unmatched != NULL).
+
RETURN VALUE
TRUE the match flag is set to MATCH_FOUND and the record has been skipped
FALSE otherwise
@@ -1966,7 +2011,9 @@ bool JOIN_CACHE::skip_if_matched()
if (prev_cache)
offset+= prev_cache->get_size_of_rec_offset();
/* Check whether the match flag is MATCH_FOUND */
- if (get_match_flag_by_pos(pos+offset) == MATCH_FOUND)
+ if (get_match_flag_by_pos_from_join_buffer(pos+offset,
+ join_tab->first_unmatched) ==
+ MATCH_FOUND)
{
pos+= size_of_rec_len + get_rec_length(pos);
return TRUE;
@@ -1983,13 +2030,23 @@ bool JOIN_CACHE::skip_if_matched()
DESCRIPTION
This default implementation of the virtual function skip_if_not_needed_match
- skips the next record from the join buffer if its match flag is not
- MATCH_NOT_FOUND, and, either its value is MATCH_FOUND and join_tab is the
- first inner table of an inner join, or, its value is MATCH_IMPOSSIBLE
- and join_tab is the first inner table of an outer join.
+ skips the next record from the join when generating join extensions
+ for the records in the join buffer depending on the value of the match flag.
+ - In the case of a semi-nest the match flag may be in two states
+ {MATCH_NOT_FOUND, MATCH_FOUND}. The record is skipped if the flag is set
+ to MATCH_FOUND.
+ - In the case of a outer join nest when not_exists optimization is applied
+ the match may be in three states {MATCH_NOT_FOUND, MATCH_IMPOSSIBLE,
+ MATCH_FOUND. The record is skipped if the flag is set to MATCH_FOUND or
+ to MATCH_IMPOSSIBLE.
+
If the record is skipped the value of 'pos' is set to point to the position
right after the record.
+ NOTE
+ Currently the function is called only when generating non-null complemented
+ extensions for records in the join buffer.
+
RETURN VALUE
TRUE the record has to be skipped
FALSE otherwise
@@ -2000,11 +2057,19 @@ bool JOIN_CACHE::skip_if_not_needed_match()
DBUG_ASSERT(with_length);
enum Match_flag match_fl;
uint offset= size_of_rec_len;
+ bool skip= FALSE;
if (prev_cache)
offset+= prev_cache->get_size_of_rec_offset();
- if ((match_fl= get_match_flag_by_pos(pos+offset)) != MATCH_NOT_FOUND &&
- (join_tab->check_only_first_match() == (match_fl == MATCH_FOUND)) )
+ if (!join_tab->check_only_first_match())
+ return FALSE;
+
+ match_fl= get_match_flag_by_pos(pos+offset);
+ skip= join_tab->first_sj_inner_tab ?
+ match_fl == MATCH_FOUND : // the case of semi-join
+ match_fl != MATCH_NOT_FOUND; // the case of outer-join
+
+ if (skip)
{
pos+= size_of_rec_len + get_rec_length(pos);
return TRUE;
@@ -2104,7 +2169,14 @@ enum_nested_loop_state JOIN_CACHE::join_records(bool skip_last)
goto finish;
}
join_tab->not_null_compl= FALSE;
- /* Prepare for generation of null complementing extensions */
+ /*
+ Prepare for generation of null complementing extensions.
+ For all inner tables of the outer join operation for which
+ regular matches have been just found the field first_unmatched
+ is set to point the the first inner table. After all null
+ complement rows are generated for this outer join this field
+ is set back to NULL.
+ */
for (tab= join_tab->first_inner; tab <= join_tab->last_inner; tab++)
tab->first_unmatched= join_tab->first_inner;
}
@@ -2221,7 +2293,10 @@ enum_nested_loop_state JOIN_CACHE::join_matching_records(bool skip_last)
int error;
enum_nested_loop_state rc= NESTED_LOOP_OK;
join_tab->table->null_row= 0;
- bool check_only_first_match= join_tab->check_only_first_match();
+ bool check_only_first_match=
+ join_tab->check_only_first_match() &&
+ (!join_tab->first_inner || // semi-join case
+ join_tab->first_inner == join_tab->first_unmatched); // outer join case
bool outer_join_first_inner= join_tab->is_first_inner_for_outer_join();
DBUG_ENTER("JOIN_CACHE::join_matching_records");
diff --git a/sql/sql_join_cache.h b/sql/sql_join_cache.h
index 1cbc6ac..4eaa66b 100644
--- a/sql/sql_join_cache.h
+++ b/sql/sql_join_cache.h
@@ -206,7 +206,9 @@ class JOIN_CACHE :public Sql_alloc
/*
This flag indicates that records written into the join buffer contain
- a match flag field. The flag must be set by the init method.
+ a match flag field. The flag must be set by the init method.
+ Currently any implementation of the virtial init method calls
+ the function JOIN_CACHE::calc_record_fields() to set this flag.
*/
bool with_match_flag;
/*
@@ -646,6 +648,13 @@ class JOIN_CACHE :public Sql_alloc
/* Shall return the value of the match flag for the positioned record */
virtual enum Match_flag get_match_flag_by_pos(uchar *rec_ptr);
+ /*
+ Shall return the value of the match flag for the positioned record
+ from the join buffer attached to the specified table.
+ */
+ virtual enum Match_flag
+ get_match_flag_by_pos_from_join_buffer(uchar *rec_ptr, JOIN_TAB *tab);
+
/* Shall return the position of the current record */
virtual uchar *get_curr_rec() { return curr_rec_pos; }
1
0
03 Mar '21
revision-id: e51dfd2521afd083404331959ef75784a614ddcb (mariadb-10.3.26-100-ge51dfd2)
parent(s): 0f81ca6a0bb21fbba4bca93a7555f7c8e6357b47
author: Igor Babaev
committer: Igor Babaev
timestamp: 2021-03-03 15:00:38 -0800
message:
MDEV-22786 Crashes with nested table value constructors
The bug caused crashes of the server when processing queries with nested
table value constructors (TVC) . It happened because the grammar rules to
parse TVC used the same global lists for both nested TVC and nesting TVC.
As a result invalid select trees were constructed for queries with nested
TVC and this led to crashes at the prepare stage.
This patch provides its own lists structures for each TVC nest level.
Besides the patch fixes a bug in the function wrap_tvc() that missed
inheritance of the SELECT_LEX::exclude_from_table_unique_test for
selects that wrapped TVCs. This inheritance is critical for specifications
of derived tables that employ nested TVCs.
---
mysql-test/main/table_value_constr.result | 175 ++++++++++++++++++++++++++++++
mysql-test/main/table_value_constr.test | 106 ++++++++++++++++++
sql/sql_lex.cc | 45 +++++++-
sql/sql_lex.h | 12 +-
sql/sql_tvc.cc | 2 +
sql/sql_yacc.yy | 3 +-
sql/sql_yacc_ora.yy | 3 +-
7 files changed, 333 insertions(+), 13 deletions(-)
diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index d2965ab..ff6d19a 100644
--- a/mysql-test/main/table_value_constr.result
+++ b/mysql-test/main/table_value_constr.result
@@ -2887,4 +2887,179 @@ drop table t1,t2,t3;
select sum((values(1)));
sum((values(1)))
1
+#
+# MDEV-22786: Nested table values constructors
+#
+values ((values (2)));
+(values (2))
+2
+values ((values (2)), (5), (select 4));
+(values (2)) 5 (select 4)
+2 5 4
+values ((7), (values (2)), (5), (select 4));
+7 (values (2)) 5 (select 4)
+7 2 5 4
+values ((values (2))) union values ((values (3)));
+(values (2))
+2
+3
+values ((values (2))), ((values (3)));
+(values (2))
+2
+3
+values ((values (2))), ((select 4)), ((values (3)));
+(values (2))
+2
+4
+3
+values ((values (4)), (values (5))), ((values (1)), (values (7)));
+(values (4)) (values (5))
+4 5
+1 7
+values ((values (4)), (select 5)), ((select 1), (values (7)));
+(values (4)) (select 5)
+4 5
+1 7
+values ((select 2)) union values ((values (3)));
+(select 2)
+2
+3
+values ((values (2))) union values((select 3));
+(values (2))
+2
+3
+values ((values (2))) union all values ((values (2)));
+(values (2))
+2
+2
+values ((values (4)), (values (5))), ((values (1)), (values (7)))
+union
+values ((values (4)), (select 5)), ((select 2), (values (8)));
+(values (4)) (values (5))
+4 5
+1 7
+2 8
+values ((values (4)), (values (5))), ((values (1)), (values (7)))
+union all
+values ((values (4)), (select 5)), ((select 2), (values (8)));
+(values (4)) (values (5))
+4 5
+1 7
+4 5
+2 8
+values ((values (1) union values (1)));
+(values (1) union values (1))
+1
+values ((values (1) union values (1) union values (1)));
+(values (1) union values (1) union values (1))
+1
+values ((values ((values (4)))));
+(values ((values (4))))
+4
+values ((values ((select 5))));
+(values ((select 5)))
+5
+values ((select (values (4))), (values ((values(5)))));
+(select (values (4))) (values ((values(5))))
+4 5
+values ((select (values (4))), (values ((select 5))));
+(select (values (4))) (values ((select 5)))
+4 5
+values ((select (values (4))), (values ((values(5)))))
+union
+values ((select (values (4))), (values ((select 7))));
+(select (values (4))) (values ((values(5))))
+4 5
+4 7
+values ((values (2))), ((values ((values (4)))));
+(values (2))
+2
+4
+values ((values (2))), ((values ((select 4))));
+(values (2))
+2
+4
+values ((values (2))), ((values ((values (4)))))
+union
+values ((values (8))), ((values ((select 4))));
+(values (2))
+2
+4
+8
+values ((values (2))), ((values ((values (4)))))
+union all
+values ((values (8))), ((values ((select 4))));
+(values (2))
+2
+4
+8
+4
+select * from (values ((values (2)))) dt;
+(values (2))
+2
+select * from (values ((values (2)), (5), (select 4))) dt;
+(values (2)) 5 (select 4)
+2 5 4
+select * from (values ((values (2))) union values ((values (3)))) dt;
+(values (2))
+2
+3
+select * from (values ((values (2))), ((values (3)))) dt;
+(values (2))
+2
+3
+select * from (values ((values (2))), ((values (3)))) dt;
+(values (2))
+2
+3
+select * from (values ((values (2))), ((select 4)), ((values (3)))) dt;
+(values (2))
+2
+4
+3
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+values ((values ((select a from t1 where a=7))));
+(values ((select a from t1 where a=7)))
+7
+values ((values ((select (values(2)) from t1 where a=8))));
+(values ((select (values(2)) from t1 where a=8)))
+NULL
+values ((values ((select a from t1 where a=7))))
+union
+values ((values ((select (values(2)) from t1 where a=8))));
+(values ((select a from t1 where a=7)))
+7
+NULL
+values ((values ((select a from t1 where a in ((values (7)))))));
+(values ((select a from t1 where a in ((values (7))))))
+7
+values ((values ((select a from t1 where a in ((values (7), (8)))))));
+(values ((select a from t1 where a in ((values (7), (8))))))
+7
+values ((values
+((select a from t1 where a in (values (7) union values (8))))));
+(values
+((select a from t1 where a in (values (7) union values (8)))))
+7
+values ((values ((select (values(2)) from t1 where a=8))));
+(values ((select (values(2)) from t1 where a=8)))
+NULL
+values ((select (values(2)) from t1 where a<7));
+ERROR 21000: Subquery returns more than 1 row
+select * from (values ((values ((select a from t1 where a=7))))) dt;
+(values ((select a from t1 where a=7)))
+7
+select * from (values ((values ((select (values(2)) from t1 where a=8))))) dt;
+(values ((select (values(2)) from t1 where a=8)))
+NULL
+insert into t1(a) values ((values (2))), ((values (3)));
+select * from t1;
+a
+3
+7
+1
+2
+3
+drop table t1;
End of 10.3 tests
diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test
index 88d0ac2..3e976f8 100644
--- a/mysql-test/main/table_value_constr.test
+++ b/mysql-test/main/table_value_constr.test
@@ -1522,4 +1522,110 @@ drop table t1,t2,t3;
select sum((values(1)));
+--echo #
+--echo # MDEV-22786: Nested table values constructors
+--echo #
+
+values ((values (2)));
+
+values ((values (2)), (5), (select 4));
+
+values ((7), (values (2)), (5), (select 4));
+
+values ((values (2))) union values ((values (3)));
+
+values ((values (2))), ((values (3)));
+
+values ((values (2))), ((select 4)), ((values (3)));
+
+values ((values (4)), (values (5))), ((values (1)), (values (7)));
+
+values ((values (4)), (select 5)), ((select 1), (values (7)));
+
+values ((select 2)) union values ((values (3)));
+
+values ((values (2))) union values((select 3));
+
+values ((values (2))) union all values ((values (2)));
+
+values ((values (4)), (values (5))), ((values (1)), (values (7)))
+union
+values ((values (4)), (select 5)), ((select 2), (values (8)));
+
+values ((values (4)), (values (5))), ((values (1)), (values (7)))
+union all
+values ((values (4)), (select 5)), ((select 2), (values (8)));
+
+values ((values (1) union values (1)));
+
+values ((values (1) union values (1) union values (1)));
+
+values ((values ((values (4)))));
+
+values ((values ((select 5))));
+
+values ((select (values (4))), (values ((values(5)))));
+
+values ((select (values (4))), (values ((select 5))));
+
+values ((select (values (4))), (values ((values(5)))))
+union
+values ((select (values (4))), (values ((select 7))));
+
+values ((values (2))), ((values ((values (4)))));
+
+values ((values (2))), ((values ((select 4))));
+
+values ((values (2))), ((values ((values (4)))))
+union
+values ((values (8))), ((values ((select 4))));
+
+values ((values (2))), ((values ((values (4)))))
+union all
+values ((values (8))), ((values ((select 4))));
+
+select * from (values ((values (2)))) dt;
+
+select * from (values ((values (2)), (5), (select 4))) dt;
+
+select * from (values ((values (2))) union values ((values (3)))) dt;
+
+select * from (values ((values (2))), ((values (3)))) dt;
+
+select * from (values ((values (2))), ((values (3)))) dt;
+
+select * from (values ((values (2))), ((select 4)), ((values (3)))) dt;
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+
+values ((values ((select a from t1 where a=7))));
+
+values ((values ((select (values(2)) from t1 where a=8))));
+
+values ((values ((select a from t1 where a=7))))
+union
+values ((values ((select (values(2)) from t1 where a=8))));
+
+values ((values ((select a from t1 where a in ((values (7)))))));
+
+values ((values ((select a from t1 where a in ((values (7), (8)))))));
+
+values ((values
+ ((select a from t1 where a in (values (7) union values (8))))));
+
+values ((values ((select (values(2)) from t1 where a=8))));
+
+--error ER_SUBQUERY_NO_1_ROW
+values ((select (values(2)) from t1 where a<7));
+
+select * from (values ((values ((select a from t1 where a=7))))) dt;
+
+select * from (values ((values ((select (values(2)) from t1 where a=8))))) dt;
+
+insert into t1(a) values ((values (2))), ((values (3)));
+select * from t1;
+
+drop table t1;
+
--echo End of 10.3 tests
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 70d795c..c2bc838 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2421,6 +2421,8 @@ void st_select_lex::init_select()
with_dep= 0;
join= 0;
lock_type= TL_READ_DEFAULT;
+ save_many_values.empty();
+ save_insert_list= 0;
tvc= 0;
in_funcs.empty();
curr_tvc_name= 0;
@@ -8302,16 +8304,52 @@ bool LEX::last_field_generated_always_as_row_end()
}
+void LEX::save_values_list_state()
+{
+ current_select->save_many_values= many_values;
+ current_select->save_insert_list= insert_list;
+}
+
+
+void LEX::restore_values_list_state()
+{
+ many_values= current_select->save_many_values;
+ insert_list= current_select->save_insert_list;
+}
+
+
+void LEX::tvc_start()
+{
+ if (current_select == &select_lex)
+ mysql_init_select(this);
+ else
+ save_values_list_state();
+ many_values.empty();
+ insert_list= 0;
+}
+
+
+bool LEX::tvc_start_derived()
+{
+ if (current_select->linkage == GLOBAL_OPTIONS_TYPE ||
+ unlikely(mysql_new_select(this, 1, NULL)))
+ return true;
+ save_values_list_state();
+ many_values.empty();
+ insert_list= 0;
+ return false;
+}
+
+
bool LEX::tvc_finalize()
{
- mysql_init_select(this);
if (unlikely(!(current_select->tvc=
new (thd->mem_root)
table_value_constr(many_values,
current_select,
current_select->options))))
return true;
- many_values.empty();
+ restore_values_list_state();
if (!current_select->master_unit()->fake_select_lex)
current_select->master_unit()->add_fake_select_lex(thd);
return false;
@@ -8326,9 +8364,6 @@ bool LEX::tvc_finalize_derived()
thd->parse_error();
return true;
}
- if (current_select->linkage == GLOBAL_OPTIONS_TYPE ||
- unlikely(mysql_new_select(this, 1, NULL)))
- return true;
current_select->linkage= DERIVED_TABLE_TYPE;
return tvc_finalize();
}
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 979e212..474f317 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1176,6 +1176,8 @@ class st_select_lex: public st_select_lex_node
/* it is for correct printing SELECT options */
thr_lock_type lock_type;
+ List<List_item> save_many_values;
+ List<Item> *save_insert_list;
table_value_constr *tvc;
bool in_tvc;
@@ -4046,12 +4048,10 @@ struct LEX: public Query_tables_list
return false;
}
- void tvc_start()
- {
- field_list.empty();
- many_values.empty();
- insert_list= 0;
- }
+ void save_values_list_state();
+ void restore_values_list_state();
+ void tvc_start();
+ bool tvc_start_derived();
bool tvc_finalize();
bool tvc_finalize_derived();
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
index cb056b0..96c5223 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -673,6 +673,8 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl,
wrapper_sl->nest_level= tvc_sl->nest_level;
wrapper_sl->parsing_place= tvc_sl->parsing_place;
wrapper_sl->linkage= tvc_sl->linkage;
+ wrapper_sl->exclude_from_table_unique_test=
+ tvc_sl->exclude_from_table_unique_test;
lex->current_select= wrapper_sl;
item= new (thd->mem_root) Item_field(thd, &wrapper_sl->context,
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 88f12e9..b26e2dd 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -12334,7 +12334,8 @@ derived_query_specification:
derived_table_value_constructor:
VALUES
{
- Lex->tvc_start();
+ if (Lex->tvc_start_derived())
+ MYSQL_YYABORT;
}
values_list
{
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index eaeaf2e..4af034d 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -12272,7 +12272,8 @@ derived_query_specification:
derived_table_value_constructor:
VALUES
{
- Lex->tvc_start();
+ if (Lex->tvc_start_derived())
+ MYSQL_YYABORT;
}
values_list
{
1
0