revision-id: 6fd4ac82ce251c3428f2041983c760967d3651e8 (mariadb-10.5.2-566-g6fd4ac82ce2) parent(s): cee7b072c2dfaa46b46e52338be5bb1bad59a8c0 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-05 14:15:05 +0300 message: MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set (Also fixes MDEV-25254). Re-work Name Resolution for the argument of JSON_TABLE(json_doc, ....) function. The json_doc argument can refer to other tables, but it can only refer to the tables that precede[*] the JSON_TABLE(...) call. [*] - For queries with RIGHT JOINs, the "preceding" is determined after the query is normalized by converting RIGHT JOIN into left one. The implementation is as follows: - Table function arguments use their own Name_resolution_context. - The Name_resolution_context now has a bitmap of tables that should be ignored when searching for a field. - get_disallowed_table_deps() walks the TABLE_LIST::nested_join tree and computes a bitmap of tables that do not "precede" the given JSON_TABLE(...) invocation (according the above definition of "preceding"). --- mysql-test/suite/json/r/json_table.result | 88 +++++++++++++- mysql-test/suite/json/r/json_table_mysql.result | 21 ++-- mysql-test/suite/json/t/json_table.test | 103 ++++++++++++++++- mysql-test/suite/json/t/json_table_mysql.test | 14 ++- sql/item.cc | 5 + sql/item.h | 7 ++ sql/json_table.cc | 146 +++++++++++++++++++++++- sql/json_table.h | 7 ++ sql/sql_base.cc | 11 +- sql/sql_base.h | 1 + sql/sql_help.cc | 1 + sql/sql_lex.cc | 2 +- sql/sql_lex.h | 4 +- sql/sql_parse.cc | 4 + sql/sql_select.cc | 4 +- sql/sql_yacc.yy | 19 ++- 16 files changed, 398 insertions(+), 39 deletions(-) diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result index e9bee553542..e1c87853379 100644 --- a/mysql-test/suite/json/r/json_table.result +++ b/mysql-test/suite/json/r/json_table.result @@ -55,7 +55,7 @@ item_name item_props color Laptop {"color": "black", "price": 1000} black Jeans {"color": "blue", "price": 50} blue select * from t1 right join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1; -ERROR 42000: Cross dependency found in OUTER JOIN; examine your ON conditions +ERROR 42S22: Unknown column 't1.item_props' in 'JSON_TABLE argument' DROP TABLE t1; select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; a b @@ -251,8 +251,12 @@ SELECT t1.x*2 m, jt.* FROM t1, JSON_TABLE(m, '$[*]' COLUMNS (i INT PATH '$')) jt; ERROR 42S22: Unknown column 'm' in 'JSON_TABLE argument' DROP TABLE t1; -select * from json_table(JS3.size, '$' columns (size INT PATH '$.size')) as JS1, json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS2, json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS3 where 1; -ERROR 42S02: Unknown table 'JS3' in JSON_TABLE argument +select * +from +json_table(JS3.size, '$' columns (size INT PATH '$.size')) as JS1, +json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS2, +json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS3 where 1; +ERROR 42S22: Unknown column 'JS3.size' in 'JSON_TABLE argument' create table t1 (json varchar(100) character set utf8); insert into t1 values ('{"value":"АБВ"}'); create table tj1 as @@ -279,7 +283,7 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze Warning Engine-independent statistics are not collected for column 'f1' test.t1 analyze status OK SELECT * FROM JSON_TABLE(tt3.f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN t1 AS tt3; -ERROR 42S02: Unknown table 'tt3' in JSON_TABLE argument +ERROR 42S22: Unknown column 'tt3.f1' in 'JSON_TABLE argument' SELECT * FROM t1 as jj1, (SELECT tt2.* FROM @@ -589,5 +593,81 @@ f NULL DROP TABLE t1; # +# MDEV-25254: JSON_TABLE: Inconsistent name resolution with right joins +# +CREATE TABLE t1 (a INT); +SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; +ERROR 42S22: Unknown column 't1.a' in 'JSON_TABLE argument' +CREATE VIEW v AS +SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; +ERROR 42S22: Unknown column 't1.a' in 'JSON_TABLE argument' +insert into t1 values (1),(2),(3); +SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; +ERROR 42S22: Unknown column 't1.a' in 'JSON_TABLE argument' +drop table t1; +# +# MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set, server crash +# +CREATE TABLE t1 (o INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (3),(4); +SELECT * FROM JSON_TABLE(a, '$' COLUMNS(o FOR ORDINALITY)) AS jt1 NATURAL JOIN t1 JOIN t2; +ERROR 42S22: Unknown column 'a' in 'JSON_TABLE argument' +SELECT * FROM JSON_TABLE(a, '$' COLUMNS(o FOR ORDINALITY)) AS jt1 NATURAL JOIN t1 STRAIGHT_JOIN t2; +ERROR 42S22: Unknown column 'a' in 'JSON_TABLE argument' +drop table t1,t2; +# Now, try a JSON_TABLE that has a subquery that has an outside reference: +create table t1(a int, js varchar(32)); +create table t2(a varchar(100)); +insert into t2 values(''); +explain +select * +from +t1 left join +json_table(concat('',js), +'$' columns ( color varchar(32) path '$.color') +) as JT on 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain +select * +from +t1 right join +json_table(concat('',js), +'$' columns ( color varchar(32) path '$.color') +) as JT on 1; +ERROR 42S22: Unknown column 'js' in 'JSON_TABLE argument' +explain +select * +from +t1 left join +json_table((select concat(a,js) from t2), +'$' columns ( color varchar(32) path '$.color') +) as JT on 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t2 system NULL NULL NULL NULL 1 +explain +select * +from +t1 right join +json_table((select concat(a,js) from t2), +'$' columns ( color varchar(32) path '$.color') +) as JT on 1; +ERROR 42S22: Unknown column 'js' in 'field list' +drop table t1,t2; +# +# MDEV-25256: JSON_TABLE: Error ER_VIEW_INVALID upon running query via view +# +SELECT * FROM +JSON_TABLE('[]', '$' COLUMNS(a TEXT PATH '$[*]')) AS jt1 +RIGHT JOIN JSON_TABLE(jt1.a, '$' COLUMNS(o2 FOR ORDINALITY)) AS jt2 +ON(1) +RIGHT JOIN JSON_TABLE('[]', '$' COLUMNS(o3 FOR ORDINALITY)) AS jt3 +ON(1) +WHERE 0; +ERROR 42S22: Unknown column 'jt1.a' in 'JSON_TABLE argument' +# # End of 10.6 tests # diff --git a/mysql-test/suite/json/r/json_table_mysql.result b/mysql-test/suite/json/r/json_table_mysql.result index 24cc5fa64b6..ffe94da2750 100644 --- a/mysql-test/suite/json/r/json_table_mysql.result +++ b/mysql-test/suite/json/r/json_table_mysql.result @@ -640,13 +640,13 @@ DEALLOCATE PREPARE stmt; CREATE TABLE t1 (id INT, jc JSON); SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id; -id jc id +ERROR 42S22: Unknown column 't1.jc' in 'JSON_TABLE argument' SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt LEFT JOIN t1 ON t1.jc=jt.id; -ERROR 42S02: Unknown table 't1' in JSON_TABLE argument +ERROR 42S22: Unknown column 't1.jc' in 'JSON_TABLE argument' SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt RIGHT JOIN t1 ON t1.jc=jt.id; -ERROR 42S02: Unknown table 't1' in JSON_TABLE argument +id id jc SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id; id jc id @@ -667,34 +667,33 @@ SELECT * FROM t1 AS t1o RIGHT JOIN (t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) ON t1o.id=t1i.id; -id jc id jc id +ERROR 42S22: Unknown column 't1o.jc' in 'JSON_TABLE argument' SELECT * FROM t1 AS t1o RIGHT JOIN (t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) ON t1o.id=t1i.id; -id jc id jc id +ERROR 42S22: Unknown column 't1o.jc' in 'JSON_TABLE argument' WITH qn AS (SELECT jt.* FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id) SELECT * from qn; -id +ERROR 42S22: Unknown column 't1.jc' in 'JSON_TABLE argument' WITH qn AS (SELECT 1 UNION SELECT jt.id FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id) SELECT * from qn; -1 -1 +ERROR 42S22: Unknown column 't1.jc' in 'JSON_TABLE argument' SELECT * FROM t1 AS t1o RIGHT JOIN (t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) ON t1o.id=t1i.id; -id jc id jc id +ERROR 42S22: Unknown column 't1o.jc' in 'JSON_TABLE argument' SELECT * FROM t1 AS t1o RIGHT JOIN (t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) ON t1o.id=t1i.id; -id jc id jc id +ERROR 42S22: Unknown column 't1o.jc' in 'JSON_TABLE argument' INSERT INTO t1 VALUES(1,"1"),(2,"4"),(3,"3"); ANALYZE TABLE t1; Table Op Msg_type Msg_text @@ -717,7 +716,7 @@ LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id RIGHT JOIN JSON_TABLE(jt.id, '$' COLUMNS (id FOR ORDINALITY)) as jt1 ON jt.id=jt1.id; -ERROR 42000: Cross dependency found in OUTER JOIN; examine your ON conditions +ERROR 42S22: Unknown column 'jt.id' in 'JSON_TABLE argument' DROP TABLE t1; # # Bug#25525409: ASSERTION `TABLE_LIST->TABLE' FAILED IN SQL/SQL_BASE.CC diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test index 151d35e1424..ad3770e84ce 100644 --- a/mysql-test/suite/json/t/json_table.test +++ b/mysql-test/suite/json/t/json_table.test @@ -25,7 +25,7 @@ insert into t1 values ('Jeans', '{"color": "blue", "price": 50}'); select * from t1 left join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1; ---error ER_WRONG_OUTER_JOIN +--error ER_BAD_FIELD_ERROR select * from t1 right join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1; DROP TABLE t1; @@ -173,8 +173,12 @@ SELECT t1.x*2 m, jt.* FROM t1, JSON_TABLE(m, '$[*]' COLUMNS (i INT PATH '$')) jt; DROP TABLE t1; ---error ER_UNKNOWN_TABLE -select * from json_table(JS3.size, '$' columns (size INT PATH '$.size')) as JS1, json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS2, json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS3 where 1; +--error ER_BAD_FIELD_ERROR +select * +from + json_table(JS3.size, '$' columns (size INT PATH '$.size')) as JS1, + json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS2, + json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS3 where 1; create table t1 (json varchar(100) character set utf8); insert into t1 values ('{"value":"АБВ"}'); @@ -195,7 +199,7 @@ INSERT INTO t1 VALUES (6, '{\"1\": 6}'); ANALYZE TABLE t1; ---error ER_UNKNOWN_TABLE +--error ER_BAD_FIELD_ERROR SELECT * FROM JSON_TABLE(tt3.f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN t1 AS tt3; --error ER_BAD_FIELD_ERROR SELECT * FROM t1 as jj1, @@ -460,6 +464,97 @@ CREATE TABLE t1 AS SELECT * FROM JSON_TABLE('{"x":1}', '$' COLUMNS(f DATE PATH ' SELECT * FROM t1; DROP TABLE t1; +--echo # +--echo # MDEV-25254: JSON_TABLE: Inconsistent name resolution with right joins +--echo # +CREATE TABLE t1 (a INT); +--error ER_BAD_FIELD_ERROR +SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; + +--error ER_BAD_FIELD_ERROR +CREATE VIEW v AS +SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; + +insert into t1 values (1),(2),(3); +--error ER_BAD_FIELD_ERROR +SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; + +drop table t1; +--echo # +--echo # MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set, server crash +--echo # +CREATE TABLE t1 (o INT); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (3),(4); + +--error ER_BAD_FIELD_ERROR +SELECT * FROM JSON_TABLE(a, '$' COLUMNS(o FOR ORDINALITY)) AS jt1 NATURAL JOIN t1 JOIN t2; + +--error ER_BAD_FIELD_ERROR +SELECT * FROM JSON_TABLE(a, '$' COLUMNS(o FOR ORDINALITY)) AS jt1 NATURAL JOIN t1 STRAIGHT_JOIN t2; + +drop table t1,t2; + +--echo # Now, try a JSON_TABLE that has a subquery that has an outside reference: + +create table t1(a int, js varchar(32)); + +create table t2(a varchar(100)); +insert into t2 values(''); + +# First, without subquery: +explain +select * +from + t1 left join + json_table(concat('',js), + '$' columns ( color varchar(32) path '$.color') + ) as JT on 1; + +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 right join + json_table(concat('',js), + '$' columns ( color varchar(32) path '$.color') + ) as JT on 1; + +# Now, with subquery: +explain +select * +from + t1 left join + json_table((select concat(a,js) from t2), + '$' columns ( color varchar(32) path '$.color') + ) as JT on 1; + +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 right join + json_table((select concat(a,js) from t2), + '$' columns ( color varchar(32) path '$.color') + ) as JT on 1; + +drop table t1,t2; + +--echo # +--echo # MDEV-25256: JSON_TABLE: Error ER_VIEW_INVALID upon running query via view +--echo # + +--error ER_BAD_FIELD_ERROR +SELECT * FROM +JSON_TABLE('[]', '$' COLUMNS(a TEXT PATH '$[*]')) AS jt1 + RIGHT JOIN JSON_TABLE(jt1.a, '$' COLUMNS(o2 FOR ORDINALITY)) AS jt2 + ON(1) + RIGHT JOIN JSON_TABLE('[]', '$' COLUMNS(o3 FOR ORDINALITY)) AS jt3 + ON(1) +WHERE 0; + --echo # --echo # End of 10.6 tests diff --git a/mysql-test/suite/json/t/json_table_mysql.test b/mysql-test/suite/json/t/json_table_mysql.test index 821d262283d..eebc4ba11ee 100644 --- a/mysql-test/suite/json/t/json_table_mysql.test +++ b/mysql-test/suite/json/t/json_table_mysql.test @@ -549,15 +549,17 @@ CREATE TABLE t1 (id INT, jc JSON); # psergey! #--error ER_UNKNOWN_TABLE +--error ER_BAD_FIELD_ERROR SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id; # psergey! ---error ER_UNKNOWN_TABLE +#--error ER_UNKNOWN_TABLE +--error ER_BAD_FIELD_ERROR SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt LEFT JOIN t1 ON t1.jc=jt.id; ---error ER_UNKNOWN_TABLE +#--error ER_UNKNOWN_TABLE SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt RIGHT JOIN t1 ON t1.jc=jt.id; @@ -580,6 +582,7 @@ EXPLAIN SELECT * FROM t1 t1o RIGHT JOIN t1 ON t1o.id=t1.id # psergey: #--error ER_BAD_FIELD_ERROR +--error ER_BAD_FIELD_ERROR SELECT * FROM t1 AS t1o RIGHT JOIN (t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) @@ -587,6 +590,7 @@ SELECT * FROM t1 AS t1o RIGHT JOIN # psergey: #--error ER_UNKNOWN_TABLE +--error ER_BAD_FIELD_ERROR SELECT * FROM t1 AS t1o RIGHT JOIN (t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) @@ -594,12 +598,14 @@ SELECT * FROM t1 AS t1o RIGHT JOIN # psergey: #--error ER_UNKNOWN_TABLE +--error ER_BAD_FIELD_ERROR WITH qn AS (SELECT jt.* FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id) SELECT * from qn; #--error ER_UNKNOWN_TABLE +--error ER_BAD_FIELD_ERROR WITH qn AS (SELECT 1 UNION SELECT jt.id FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS @@ -607,12 +613,14 @@ WITH qn AS SELECT * from qn; #--error ER_BAD_FIELD_ERROR +--error ER_BAD_FIELD_ERROR SELECT * FROM t1 AS t1o RIGHT JOIN (t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) ON t1o.id=t1i.id; #--error ER_UNKNOWN_TABLE +--error ER_BAD_FIELD_ERROR SELECT * FROM t1 AS t1o RIGHT JOIN (t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) @@ -628,7 +636,7 @@ SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS EXPLAIN SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id INT PATH '$')) as jt ON t1.id=jt.id; ---error ER_WRONG_OUTER_JOIN +--error ER_BAD_FIELD_ERROR SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id diff --git a/sql/item.cc b/sql/item.cc index 3dbe3badadf..18bb26e18eb 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -5534,6 +5534,8 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) first_name_resolution_table, outer_context-> last_name_resolution_table, + outer_context-> + ignored_tables, reference, IGNORE_EXCEPT_NON_UNIQUE, TRUE, TRUE)) != @@ -5679,6 +5681,7 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) find_field_in_tables(thd, this, context->first_name_resolution_table, context->last_name_resolution_table, + context->ignored_tables, reference, REPORT_ALL_ERRORS, !any_privileges, TRUE); } @@ -5845,6 +5848,7 @@ bool Item_field::fix_fields(THD *thd, Item **reference) if ((from_field= find_field_in_tables(thd, this, context->first_name_resolution_table, context->last_name_resolution_table, + context->ignored_tables, reference, thd->lex->use_only_table_context ? REPORT_ALL_ERRORS : @@ -7904,6 +7908,7 @@ bool Item_ref::fix_fields(THD *thd, Item **reference) first_name_resolution_table, outer_context-> last_name_resolution_table, + outer_context->ignored_tables, reference, IGNORE_EXCEPT_NON_UNIQUE, TRUE, TRUE); diff --git a/sql/item.h b/sql/item.h index a1c288ab1f0..747f366e33d 100644 --- a/sql/item.h +++ b/sql/item.h @@ -232,6 +232,12 @@ struct Name_resolution_context: Sql_alloc */ bool resolve_in_select_list; + /* + Bitmap of tables that should be ignored when doing name resolution. + Normally it is {0}. Non-zero values are used by table functions. + */ + table_map ignored_tables; + /* Security context of this name resolution context. It's used for views and is non-zero only if the view is defined with SQL SECURITY DEFINER. @@ -241,6 +247,7 @@ struct Name_resolution_context: Sql_alloc Name_resolution_context() :outer_context(0), table_list(0), select_lex(0), error_processor_data(0), + ignored_tables(0), security_ctx(0) {} diff --git a/sql/json_table.cc b/sql/json_table.cc index 95ca1aae3f5..9fd647dc6c5 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -22,6 +22,7 @@ #include "item_jsonfunc.h" #include "json_table.h" #include "sql_show.h" +#include "sql_select.h" #define HA_ERR_JSON_TABLE (HA_ERR_LAST+1) @@ -40,6 +41,111 @@ class table_function_handlerton static table_function_handlerton table_function_hton; +/* + @brief + Collect a bitmap of tables that a given table function cannot have + references to. + + @detail + According to the SQL standard, a table function can refer to any table + that's "preceding" it in the FROM clause. + + The other limitation we would like to enforce is that the inner side of + an outer join cannot refer to the outer side. An example: + + SELECT * from JSON_TABLE(t1.col, ...) left join t1 on ... + + This function implements both of the above restrictions. + + Basic idea: the "join_list" contains the tables in the order that's a + reverse of the order they were specified in the query. + If we walk the join_list, we will encounter: + 1. First, the tables that table function cannot refer to (collect them in a + bitmap) + 2. Then, the table function itself (put it in the bitmap, too, as self- + references are not allowed, and stop the walk) + 3. Tables that the table function CAN refer to (we don't walk these as + we've stopped on step #2). + + The above can be applied recursively for nested joins (this covers NATURAL + JOIN, and JOIN ... USING constructs). + + Enforcing the "refer to only preceding tables" rule means that outer side + of LEFT JOIN cannot refer to the inner side. + + Handing RIGHT JOINs: There are no RIGHT JOINs in the join_list data + structures. They were converted to LEFT JOINs (see calls to st_select_lex:: + convert_right_join). This conversion changes the order of tables, but + we are ok with operating on the tables "in the left join order". + + @return + TRUE - enumeration has found the Table Function instance. The bitmap is + ready. + FALSE - Otherwise + +*/ + +static +bool get_disallowed_table_deps_for_list(table_map table_func_bit, + List<TABLE_LIST> *join_list, + table_map *disallowed_tables) +{ + TABLE_LIST *table; + NESTED_JOIN *nested_join; + List_iterator<TABLE_LIST> li(*join_list); + + while ((table= li++)) + { + if ((nested_join= table->nested_join)) + { + if (get_disallowed_table_deps_for_list(table_func_bit, + &nested_join->join_list, + disallowed_tables)) + return true; + } + else + { + *disallowed_tables |= table->table->map; + if (table_func_bit == table->table->map) + { + // This is the JSON_TABLE(...) that are we're computing dependencies + // for. + return true; + } + } + } + return false; +} + + +/* + @brief + Given a join and a table function in it (specified by its table_func_bit), + produce a bitmap of tables that the table function can NOT have references + to. + + @detail + See get_disallowed_table_deps_for_list + + @return + Bitmap of tables that table function can NOT have references to. +*/ + +static +table_map get_disallowed_table_deps(JOIN *join, table_map table_func_bit) +{ + table_map disallowed_tables= 0; + if (get_disallowed_table_deps_for_list(table_func_bit, join->join_list, + &disallowed_tables)) + return disallowed_tables; + else + { + DBUG_ASSERT(0); + return disallowed_tables; + } +} + + /* A table that produces output rows for JSON_TABLE(). @@ -1113,6 +1219,34 @@ void Table_function_json_table::end_nested_path() } +/* + @brief Create a name resolution context for doing name resolution in table + function argument. + + @seealso + push_new_name_resolution_context +*/ + +bool push_table_function_arg_context(LEX *lex, MEM_ROOT *alloc) +{ + // Walk the context stack until we find a context that is select-level + // context. + List_iterator<Name_resolution_context> it(lex->context_stack); + Name_resolution_context *ctx= NULL; + while ((ctx= it++)) + { + if (ctx->select_lex && ctx == &ctx->select_lex->context) + break; + } + DBUG_ASSERT(ctx); + + // Then, create a copy of it and return it. + Name_resolution_context *new_ctx= new (alloc) Name_resolution_context; + *new_ctx= *ctx; + return lex->push_context(new_ctx); +} + + /* @brief Perform name-resolution phase tasks @@ -1139,9 +1273,17 @@ int Table_function_json_table::setup(THD *thd, TABLE_LIST *sql_table, bool res; save_is_item_list_lookup= thd->lex->current_select->is_item_list_lookup; thd->lex->current_select->is_item_list_lookup= 0; - s_lex->end_lateral_table= sql_table; + + // Prepare the name resolution context. First, copy the context that + // is using for name resolution of the WHERE clause + *m_context= thd->lex->current_select->context; + + // Then, restrict it to only allow to refer to tables that come before the + // table function reference + m_context->ignored_tables= get_disallowed_table_deps(s_lex->join, t->map); + res= m_json->fix_fields_if_needed(thd, &m_json); - s_lex->end_lateral_table= NULL; + thd->lex->current_select->is_item_list_lookup= save_is_item_list_lookup; if (res) return TRUE; diff --git a/sql/json_table.h b/sql/json_table.h index c9df5d8322a..07d53c55638 100644 --- a/sql/json_table.h +++ b/sql/json_table.h @@ -224,6 +224,10 @@ class Table_function_json_table : public Sql_alloc void start_nested_path(Json_table_nested_path *np); void end_nested_path(); Json_table_nested_path *get_cur_nested_path() { return cur_parent; } + void set_name_resolution_context(Name_resolution_context *arg) + { + m_context= arg; + } /* SQL Parser: current column in JSON_TABLE (...) syntax */ Json_table_column *m_cur_json_table_column; @@ -232,6 +236,8 @@ class Table_function_json_table : public Sql_alloc CHARSET_INFO *m_text_literal_cs; private: + /* Context to be used for resolving the first argument. */ + Name_resolution_context *m_context; /* the JSON argument can be taken from other tables. @@ -251,6 +257,7 @@ class Table_function_json_table : public Sql_alloc Json_table_nested_path **last_sibling_hook; }; +bool push_table_function_arg_context(LEX *lex, MEM_ROOT *alloc); TABLE *create_table_for_function(THD *thd, TABLE_LIST *sql_table); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index fd945ec9d5d..c30c94fb799 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -6315,6 +6315,8 @@ Field *find_field_in_table_sef(TABLE *table, const char *name) first_table list of tables to be searched for item last_table end of the list of tables to search for item. If NULL then search to the end of the list 'first_table'. + ignored_tables Bitmap of tables that should be ignored. Do not try + to find the field in those. ref if 'item' is resolved to a view field, ref is set to point to the found view field report_error Degree of error reporting: @@ -6342,6 +6344,7 @@ Field *find_field_in_table_sef(TABLE *table, const char *name) Field * find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *first_table, TABLE_LIST *last_table, + table_map ignored_tables, Item **ref, find_item_error_report_type report_error, bool check_privileges, bool register_tree_change) { @@ -6458,15 +6461,15 @@ find_field_in_tables(THD *thd, Item_ident *item, db= name_buff; } - if (first_table && first_table->select_lex && - first_table->select_lex->end_lateral_table) - last_table= first_table->select_lex->end_lateral_table; - else if (last_table) + if (last_table) last_table= last_table->next_name_resolution_table; for (; cur_table != last_table ; cur_table= cur_table->next_name_resolution_table) { + if (cur_table->table && (cur_table->table->map & ignored_tables)) + continue; + Field *cur_field= find_field_in_table_ref(thd, cur_table, name, length, item->name.str, db, table_name, ref, (thd->lex->sql_command == diff --git a/sql/sql_base.h b/sql/sql_base.h index ccfacaf9086..765bf426b45 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -195,6 +195,7 @@ bool fill_record(THD *thd, TABLE *table, Field **field, List<Item> &values, Field * find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *first_table, TABLE_LIST *last_table, + table_map ignored_tables, Item **ref, find_item_error_report_type report_error, bool check_privileges, bool register_tree_change); Field * diff --git a/sql/sql_help.cc b/sql/sql_help.cc index ebda874d1fe..916315cf67e 100644 --- a/sql/sql_help.cc +++ b/sql/sql_help.cc @@ -99,6 +99,7 @@ static bool init_fields(THD *thd, TABLE_LIST *tables, Lex_cstring_strlen(find_fields->table_name), Lex_cstring_strlen(find_fields->field_name))); if (!(find_fields->field= find_field_in_tables(thd, field, tables, NULL, + table_map(0), 0, REPORT_ALL_ERRORS, 1, TRUE))) DBUG_RETURN(1); diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index baf243484b6..c6a6d4fb92e 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -10103,7 +10103,7 @@ SELECT_LEX *LEX::parsed_subselect(SELECT_LEX_UNIT *unit) (curr_sel == NULL && current_select == &builtin_select)); if (curr_sel) { - curr_sel->register_unit(unit, &curr_sel->context); + curr_sel->register_unit(unit, context_stack.head()); curr_sel->add_statistics(unit); } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 2a3312a02f2..5ce9e0ac409 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1184,8 +1184,6 @@ class st_select_lex: public st_select_lex_node enum leaf_list_state {UNINIT, READY, SAVED}; enum leaf_list_state prep_leaf_list_state; uint insert_tables; - // Last table for LATERAL join, used by table functions - TABLE_LIST *end_lateral_table; st_select_lex *merged_into; /* select which this select is merged into */ /* (not 0 only for views/derived tables) */ @@ -1442,7 +1440,7 @@ class st_select_lex: public st_select_lex_node ha_rows get_limit(); friend struct LEX; - st_select_lex() : group_list_ptrs(NULL), end_lateral_table(NULL), braces(0), + st_select_lex() : group_list_ptrs(NULL), braces(0), automatic_brackets(0), n_sum_items(0), n_child_sum_items(0) {} void make_empty_select() diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 5659aacc6af..46bd119b106 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -8990,6 +8990,10 @@ bool st_select_lex_unit::add_fake_select_lex(THD *thd_arg) @param left_op left operand of the JOIN @param right_op rigth operand of the JOIN + @seealso + push_table_function_arg_context() serves similar purpose for table + functions + @retval FALSE if all is OK @retval diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 6eb7e69343d..7ec2232a350 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -24563,8 +24563,8 @@ find_order_in_list(THD *thd, Ref_ptr_array ref_pointer_array, order_item_type == Item::REF_ITEM) { from_field= find_field_in_tables(thd, (Item_ident*) order_item, tables, - NULL, &view_ref, IGNORE_ERRORS, FALSE, - FALSE); + NULL, table_map(0), &view_ref, + IGNORE_ERRORS, FALSE, FALSE); if (!from_field) from_field= (Field*) not_found_field; } diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index e53321af3cc..8483ca5ff50 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -11674,29 +11674,38 @@ json_on_empty_response: ; table_function: - JSON_TABLE_SYM '(' expr ',' + JSON_TABLE_SYM '(' + { + push_table_function_arg_context(Lex, thd->mem_root); + //TODO: introduce IN_TABLE_FUNC_ARGUMENT? + Select->parsing_place= IN_ON; + } + expr ',' { Table_function_json_table *jt= - new (thd->mem_root) Table_function_json_table($3); + new (thd->mem_root) Table_function_json_table($4); if (unlikely(!jt)) MYSQL_YYABORT; Lex->json_table= jt; + + Select->parsing_place= NO_MATTER; + jt->set_name_resolution_context(Lex->pop_context()); } json_text_literal json_table_columns_clause ')' opt_table_alias_clause { SELECT_LEX *sel= Select; - if (unlikely($9 == NULL)) + if (unlikely($10 == NULL)) { /* Alias is not optional. */ my_error(ER_JSON_TABLE_ALIAS_REQUIRED, MYF(0)); MYSQL_YYABORT; } - if (unlikely(Lex->json_table->m_nested_path.set_path(thd, $6))) + if (unlikely(Lex->json_table->m_nested_path.set_path(thd, $7))) MYSQL_YYABORT; sel->table_join_options= 0; if (!($$= Select->add_table_to_list(thd, new (thd->mem_root) Table_ident(thd, &empty_clex_str, - $9, TRUE), + $10, TRUE), NULL, Select->get_table_join_options() | TL_OPTION_TABLE_FUNCTION,