- commits - lists.mariadb.org

[Commits] 95c8a1dc5cb: MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set
by psergey 16 Apr '21

16 Apr '21

revision-id: 95c8a1dc5cb1ae1953da6872f30ddda78cab67f0 (mariadb-10.5.2-589-g95c8a1dc5cb) parent(s): 36c3be336616cb95443c5164a0e082862c6270d0 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-16 19:50:08 +0300 message: MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set Address review input: switch Name_resolution_context::ignored_tables from table_map to a list of TABLE_LIST objects. The rationale is that table bits may be changed due to query rewrites, etc, which may potentially require updating ignored_tables. --- sql/item.cc | 15 +++++++ sql/item.h | 7 +++- sql/json_table.cc | 121 ++++++++++++++++++++++++++++++++---------------------- sql/json_table.h | 7 +++- sql/sql_base.cc | 19 ++++++--- sql/sql_base.h | 5 ++- 6 files changed, 113 insertions(+), 61 deletions(-) diff --git a/sql/item.cc b/sql/item.cc index 18bb26e18eb..6b593925369 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -10704,3 +10704,18 @@ bool Item::cleanup_excluding_immutables_processor (void *arg) return false; } } + + +bool ignored_list_includes_table(ignored_tables_list_t list, TABLE_LIST *tbl) +{ + if (!list) + return false; + List_iterator<TABLE_LIST> it(*list); + TABLE_LIST *list_tbl; + while ((list_tbl = it++)) + { + if (list_tbl == tbl) + return true; + } + return false; +} diff --git a/sql/item.h b/sql/item.h index 747f366e33d..4fdac92f357 100644 --- a/sql/item.h +++ b/sql/item.h @@ -162,6 +162,9 @@ void dummy_error_processor(THD *thd, void *data); void view_error_processor(THD *thd, void *data); +typedef List<TABLE_LIST>* ignored_tables_list_t; +bool ignored_list_includes_table(ignored_tables_list_t list, TABLE_LIST *tbl); + /* Instances of Name_resolution_context store the information necessary for name resolution of Items and other context analysis of a query made in @@ -236,7 +239,7 @@ struct Name_resolution_context: Sql_alloc 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; + ignored_tables_list_t ignored_tables; /* Security context of this name resolution context. It's used for views @@ -247,7 +250,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), + ignored_tables(NULL), security_ctx(0) {} diff --git a/sql/json_table.cc b/sql/json_table.cc index a16adc25f08..8e453cffcda 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -44,9 +44,14 @@ static table_function_handlerton table_function_hton; /* @brief - Collect a bitmap of tables that a given table function cannot have + Collect a set of tables that a given table function cannot have references to. + @param + table_func The table function we are connecting info for + join_list The nested join to be processed + disallowed_tables Collect the tables here. + @detail According to the SQL standard, a table function can refer to any table that's "preceding" it in the FROM clause. @@ -80,16 +85,15 @@ static table_function_handlerton table_function_hton; 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 - + 0 - Continue + 1 - Finish the process, success + -1 - Finish the process, failure */ static -bool get_disallowed_table_deps_for_list(table_map table_func_bit, - List<TABLE_LIST> *join_list, - table_map *disallowed_tables) +int get_disallowed_table_deps_for_list(TABLE_LIST *table_func, + List<TABLE_LIST> *join_list, + List<TABLE_LIST> *disallowed_tables) { TABLE_LIST *table; NESTED_JOIN *nested_join; @@ -99,23 +103,25 @@ bool get_disallowed_table_deps_for_list(table_map table_func_bit, { if ((nested_join= table->nested_join)) { - if (get_disallowed_table_deps_for_list(table_func_bit, - &nested_join->join_list, - disallowed_tables)) - return true; + int res; + if ((res= get_disallowed_table_deps_for_list(table_func, + &nested_join->join_list, + disallowed_tables))) + return res; } else { - *disallowed_tables |= table->table->map; - if (table_func_bit == table->table->map) + if (disallowed_tables->push_back(table)) + return -1; + if (table == table_func) { // This is the JSON_TABLE(...) that are we're computing dependencies // for. - return true; + return 1; // Finish the processing } } } - return false; + return 0; // Continue } @@ -129,19 +135,29 @@ bool get_disallowed_table_deps_for_list(table_map table_func_bit, See get_disallowed_table_deps_for_list @return - Bitmap of tables that table function can NOT have references to. + NULL - Out of memory + Other - A list of tables that the function cannot have references to. May + be empty. */ static -table_map get_disallowed_table_deps(JOIN *join, table_map table_func_bit) +List<TABLE_LIST>* get_disallowed_table_deps(SELECT_LEX *select, + TABLE_LIST *table_func) { - table_map disallowed_tables= 0; - if (!get_disallowed_table_deps_for_list(table_func_bit, join->join_list, - &disallowed_tables)) - { - // We haven't found the table with table_func_bit in all tables? - DBUG_ASSERT(0); - } + List<TABLE_LIST> *disallowed_tables; + + if (!(disallowed_tables = new List<TABLE_LIST>)) + return NULL; + + int res= get_disallowed_table_deps_for_list(table_func, select->join_list, + disallowed_tables); + + // The collection process must have finished + DBUG_ASSERT(res != 0); + + if (res == -1) + return NULL; // Out of memory + return disallowed_tables; } @@ -1034,48 +1050,55 @@ bool push_table_function_arg_context(LEX *lex, MEM_ROOT *alloc) Perform name-resolution phase tasks @detail - - The only argument that needs resolution is the JSON text - - Then, we need to set dependencies: if JSON_TABLE refers to table's - column, e.g. + The only argument that needs name resolution is the first parameter which + has the JSON text: + + JSON_TABLE(json_doc, ... ) - JSON_TABLE (t1.col ... ) AS t2 + The argument may refer to other tables and uses special name resolution + rules (see get_disallowed_table_deps_for_list for details). This function + sets up Name_resolution_context object appropriately before calling + fix_fields for the argument. - then it can be computed only after table t1. - - The dependencies must not form a loop. + @return + false OK + true Fatal error */ -int Table_function_json_table::setup(THD *thd, TABLE_LIST *sql_table, +bool Table_function_json_table::setup(THD *thd, TABLE_LIST *sql_table, SELECT_LEX *s_lex) { - TABLE *t= sql_table->table; - thd->where= "JSON_TABLE argument"; - { - bool save_is_item_list_lookup; - bool res; - save_is_item_list_lookup= s_lex->is_item_list_lookup; - s_lex->is_item_list_lookup= 0; + if (!m_context_setup_done) + { + m_context_setup_done= true; // Prepare the name resolution context. First, copy the context that is // used for name resolution of the WHERE clause *m_context= s_lex->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); + if (!(m_context->ignored_tables= get_disallowed_table_deps(s_lex, + sql_table))) + return TRUE; // Error + } - // Do the same what setup_without_group() does: do not count the referred - // fields in non_agg_field_used: - const bool saved_non_agg_field_used= s_lex->non_agg_field_used(); + bool save_is_item_list_lookup; + save_is_item_list_lookup= s_lex->is_item_list_lookup; + s_lex->is_item_list_lookup= 0; - res= m_json->fix_fields_if_needed(thd, &m_json); + // Do the same what setup_without_group() does: do not count the referred + // fields in non_agg_field_used: + const bool saved_non_agg_field_used= s_lex->non_agg_field_used(); - s_lex->is_item_list_lookup= save_is_item_list_lookup; - s_lex->set_non_agg_field_used(saved_non_agg_field_used); + bool res= m_json->fix_fields_if_needed(thd, &m_json); - if (res) - return TRUE; - } + s_lex->is_item_list_lookup= save_is_item_list_lookup; + s_lex->set_non_agg_field_used(saved_non_agg_field_used); + + if (res) + return TRUE; // Error return FALSE; } diff --git a/sql/json_table.h b/sql/json_table.h index 09e4295d80c..beae5405d25 100644 --- a/sql/json_table.h +++ b/sql/json_table.h @@ -198,7 +198,7 @@ class Table_function_json_table : public Sql_alloc List<Json_table_column> m_columns; /*** Name resolution functions ***/ - int setup(THD *thd, TABLE_LIST *sql_table, SELECT_LEX *s_lex); + bool setup(THD *thd, TABLE_LIST *sql_table, SELECT_LEX *s_lex); int walk_items(Item_processor processor, bool walk_subquery, void *argument); @@ -226,7 +226,8 @@ class Table_function_json_table : public Sql_alloc /*** Construction interface to be used from the parser ***/ Table_function_json_table(Item *json): - m_json(json) + m_json(json), + m_context_setup_done(false) { cur_parent= &m_nested_path; last_sibling_hook= &m_nested_path.m_nested; @@ -250,6 +251,8 @@ class Table_function_json_table : public Sql_alloc /* Context to be used for resolving the first argument. */ Name_resolution_context *m_context; + bool m_context_setup_done; + /* Current NESTED PATH level being parsed */ Json_table_nested_path *cur_parent; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index a859f83c5f7..0fc5159f7b4 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -6097,7 +6097,8 @@ Field * find_field_in_table_ref(THD *thd, TABLE_LIST *table_list, const char *name, size_t length, const char *item_name, const char *db_name, - const char *table_name, table_map ignored_tables, + const char *table_name, + ignored_tables_list_t ignored_tables, Item **ref, bool check_privileges, bool allow_rowid, uint *cached_field_index_ptr, @@ -6190,8 +6191,13 @@ find_field_in_table_ref(THD *thd, TABLE_LIST *table_list, TABLE_LIST *table; while ((table= it++)) { - if (table->table && (table->table->map & ignored_tables)) + /* + Check if the table is in the ignore list. Only base tables can be in + the ignore list. + */ + if (table->table && ignored_list_includes_table(ignored_tables, table)) continue; + if ((fld= find_field_in_table_ref(thd, table, name, length, item_name, db_name, table_name, ignored_tables, ref, check_privileges, allow_rowid, @@ -6318,8 +6324,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. + ignored_tables Set 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: @@ -6347,7 +6353,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, + ignored_tables_list_t ignored_tables, Item **ref, find_item_error_report_type report_error, bool check_privileges, bool register_tree_change) { @@ -6471,7 +6477,8 @@ find_field_in_tables(THD *thd, Item_ident *item, for (; cur_table != last_table ; cur_table= cur_table->next_name_resolution_table) { - if (cur_table->table && (cur_table->table->map & ignored_tables)) + if (cur_table->table && + ignored_list_includes_table(ignored_tables, cur_table)) continue; Field *cur_field= find_field_in_table_ref(thd, cur_table, name, length, diff --git a/sql/sql_base.h b/sql/sql_base.h index 1836c07497a..922c61ca123 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -195,14 +195,15 @@ 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, + ignored_tables_list_t ignored_tables, Item **ref, find_item_error_report_type report_error, bool check_privileges, bool register_tree_change); Field * find_field_in_table_ref(THD *thd, TABLE_LIST *table_list, const char *name, size_t length, const char *item_name, const char *db_name, - const char *table_name, table_map ignored_tables, + const char *table_name, + ignored_tables_list_t ignored_tables, Item **ref, bool check_privileges, bool allow_rowid, uint *cached_field_index_ptr, bool register_tree_change, TABLE_LIST **actual_table);

1 0

[Commits] 08cc519cd09: Cleanup: fake_select_lex->select_number=FAKE_SELECT_LEX_ID, not [U]INT_MAX
by psergey 16 Apr '21

16 Apr '21

revision-id: 08cc519cd09dfdc5783a04c4eeba587c08c24ebb (mariadb-10.5.2-567-g08cc519cd09) parent(s): c071cc3455b61b8f757b13ed1e4a5aa8b43423c9 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-16 17:45:35 +0300 message: Cleanup: fake_select_lex->select_number=FAKE_SELECT_LEX_ID, not [U]INT_MAX SELECT_LEX objects that are "fake_select_lex" (i.e read UNION output) used both INT_MAX and UINT_MAX as select_number. - mysql_explain_union() assigned UINT_MAX - st_select_lex_unit::add_fake_select_lex assigned INT_MAX This didn't matter initially (before EXPLAIN FORMAT=JSON), because the code had no checks for this value. EXPLAIN FORMAT=JSON and later other features did introduce checks for select_number values. The check had to check for two constants and looked really confusing. This patch joins the two constants into one - FAKE_SELECT_LEX_ID. --- sql/my_json_writer.h | 2 +- sql/sql_explain.h | 16 ++++++++-------- sql/sql_parse.cc | 2 +- sql/sql_select.cc | 11 ++++------- sql/sql_tvc.cc | 3 +-- 5 files changed, 15 insertions(+), 19 deletions(-) diff --git a/sql/my_json_writer.h b/sql/my_json_writer.h index bc8002de529..27aec74d08d 100644 --- a/sql/my_json_writer.h +++ b/sql/my_json_writer.h @@ -491,7 +491,7 @@ class Json_writer_object : public Json_writer_struct if (my_writer) { add_member("select_id"); - if (unlikely(select_number >= INT_MAX)) + if (unlikely(select_number == FAKE_SELECT_LEX_ID)) context.add_str("fake"); else context.add_ll(static_cast<longlong>(select_number)); diff --git a/sql/sql_explain.h b/sql/sql_explain.h index 9090416847f..42590e0bea0 100644 --- a/sql/sql_explain.h +++ b/sql/sql_explain.h @@ -74,7 +74,7 @@ class Json_writer; *************************************************************************************/ -const int FAKE_SELECT_LEX_ID= (int)UINT_MAX; +const uint FAKE_SELECT_LEX_ID= UINT_MAX; class Explain_query; @@ -108,7 +108,7 @@ class Explain_node : public Sql_alloc }; virtual enum explain_node_type get_type()= 0; - virtual int get_select_id()= 0; + virtual uint get_select_id()= 0; /** expression cache statistics @@ -166,9 +166,9 @@ class Explain_basic_join : public Explain_node bool add_table(Explain_table_access *tab, Explain_query *query); - int get_select_id() { return select_id; } + uint get_select_id() { return select_id; } - int select_id; + uint select_id; int print_explain(Explain_query *query, select_result_sink *output, uint8 explain_flags, bool is_analyze); @@ -352,7 +352,7 @@ class Explain_union : public Explain_node enum explain_node_type get_type() { return EXPLAIN_UNION; } unit_common_op operation; - int get_select_id() + uint get_select_id() { DBUG_ASSERT(union_members.elements() > 0); return union_members.at(0); @@ -879,7 +879,7 @@ class Explain_update : public Explain_node {} virtual enum explain_node_type get_type() { return EXPLAIN_UPDATE; } - virtual int get_select_id() { return 1; /* always root */ } + virtual uint get_select_id() { return 1; /* always root */ } const char *select_type; @@ -959,7 +959,7 @@ class Explain_insert : public Explain_node StringBuffer<64> table_name; enum explain_node_type get_type() { return EXPLAIN_INSERT; } - int get_select_id() { return 1; /* always root */ } + uint get_select_id() { return 1; /* always root */ } int print_explain(Explain_query *query, select_result_sink *output, uint8 explain_flags, bool is_analyze); @@ -986,7 +986,7 @@ class Explain_delete: public Explain_update bool deleting_all_rows; virtual enum explain_node_type get_type() { return EXPLAIN_DELETE; } - virtual int get_select_id() { return 1; /* always root */ } + virtual uint get_select_id() { return 1; /* always root */ } virtual int print_explain(Explain_query *query, select_result_sink *output, uint8 explain_flags, bool is_analyze); diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 3f0e71c747f..ddfc49c6d52 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -8954,7 +8954,7 @@ bool st_select_lex_unit::add_fake_select_lex(THD *thd_arg) DBUG_RETURN(1); fake_select_lex->include_standalone(this, (SELECT_LEX_NODE**)&fake_select_lex); - fake_select_lex->select_number= INT_MAX; + fake_select_lex->select_number= FAKE_SELECT_LEX_ID; fake_select_lex->parent_lex= thd_arg->lex; /* Used in init_query. */ fake_select_lex->make_empty_select(); fake_select_lex->set_linkage(GLOBAL_OPTIONS_TYPE); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index dacd636985a..5f974397b10 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1585,7 +1585,7 @@ bool JOIN::build_explain() JOIN_TAB *curr_tab= join_tab + exec_join_tab_cnt(); for (uint i= 0; i < aggr_tables; i++, curr_tab++) { - if (select_nr == INT_MAX) + if (select_nr == FAKE_SELECT_LEX_ID) { /* this is a fake_select_lex of a union */ select_nr= select_lex->master_unit()->first_select()->select_number; @@ -4208,14 +4208,12 @@ bool JOIN::save_explain_data(Explain_query *output, bool can_overwrite, If there is SELECT in this statement with the same number it must be the same SELECT */ - DBUG_ASSERT(select_lex->select_number == UINT_MAX || - select_lex->select_number == INT_MAX || !output || + DBUG_ASSERT(select_lex->select_number == FAKE_SELECT_LEX_ID || !output || !output->get_select(select_lex->select_number) || output->get_select(select_lex->select_number)->select_lex == select_lex); - if (select_lex->select_number != UINT_MAX && - select_lex->select_number != INT_MAX /* this is not a UNION's "fake select */ && + if (select_lex->select_number != FAKE_SELECT_LEX_ID && have_query_plan != JOIN::QEP_NOT_PRESENT_YET && have_query_plan != JOIN::QEP_DELETED && // this happens when there was // no QEP ever, but then @@ -27780,8 +27778,7 @@ void st_select_lex::print(THD *thd, String *str, enum_query_type query_type) if ((query_type & QT_SHOW_SELECT_NUMBER) && thd->lex->all_selects_list && thd->lex->all_selects_list->link_next && - select_number != UINT_MAX && - select_number != INT_MAX) + select_number != FAKE_SELECT_LEX_ID) { str->append("/* select#"); str->append_ulonglong(select_number); diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 6984fdd6bcf..576927ea086 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -397,8 +397,7 @@ bool table_value_constr::optimize(THD *thd) create_explain_query_if_not_exists(thd->lex, thd->mem_root); have_query_plan= QEP_AVAILABLE; - if (select_lex->select_number != UINT_MAX && - select_lex->select_number != INT_MAX /* this is not a UNION's "fake select */ && + if (select_lex->select_number != FAKE_SELECT_LEX_ID && have_query_plan != QEP_NOT_PRESENT_YET && thd->lex->explain && // for "SET" command in SPs. (!thd->lex->explain->get_select(select_lex->select_number)))

1 0

[Commits] b0824e63f9a: JSON_TABLE: post-rebase fixes
by psergey 15 Apr '21

15 Apr '21

revision-id: b0824e63f9ad99773d493af55a453e5cd9e976eb (mariadb-10.5.2-672-gb0824e63f9a) parent(s): 4b4abd8d146fba5d5fff9c29b811d30bbc4aeb13 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-15 13:15:36 +0300 message: JSON_TABLE: post-rebase fixes --- mysql-test/suite/json/r/json_table.result | 2 +- .../r/start_server_low_digest_sql_length.result | 4 +-- sql/ha_partition.cc | 7 +++++ sql/json_table.cc | 35 ++++++++++++---------- sql/share/errmsg-utf8.txt | 8 +++-- sql/sql_explain.cc | 2 +- sql/sql_yacc.yy | 2 +- 7 files changed, 38 insertions(+), 22 deletions(-) diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result index 34f25f316d4..a98efa70b48 100644 --- a/mysql-test/suite/json/r/json_table.result +++ b/mysql-test/suite/json/r/json_table.result @@ -583,7 +583,7 @@ select a from json_table('[[]]', '$' columns(a char(8) path '$' error on error)) ERROR HY000: Can't store an array or an object in the scalar column 'a' of JSON_TABLE 't'. show warnings; Level Code Message -Error 4177 Can't store an array or an object in the scalar column 'a' of JSON_TABLE 't'. +Error 4182 Can't store an array or an object in the scalar column 'a' of JSON_TABLE 't'. # # MDEV-JSON_TABLE: CREATE TABLE ignores NULL ON ERROR (implicit or explicit) and fails. # diff --git a/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result b/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result index 9ab986a1314..91ae3cb40e1 100644 --- a/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result +++ b/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result @@ -8,5 +8,5 @@ SELECT 1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1 #################################### SELECT event_name, digest, digest_text, sql_text FROM events_statements_history_long; event_name digest digest_text sql_text -statement/sql/select 36b60eb4c0a269b4d8e3c2037f5c6c36 SELECT ? + ? + SELECT ... -statement/sql/truncate eddd22a5c210f96a5160acbb5d7563b3 TRUNCATE TABLE truncat... +statement/sql/select cf4e66f3ef1e0c9905538c220053cbda SELECT ? + ? + SELECT ... +statement/sql/truncate f874a3c85ab326928c042dbd544916f9 TRUNCATE TABLE truncat... diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index a3f83d551bd..fee6cf87173 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -458,6 +458,13 @@ void ha_partition::init_handler_variables() } +const char *ha_partition::real_table_type() const +{ + // we can do this since we only support a single engine type + return m_file[0]->table_type(); +} + + /* Destructor method diff --git a/sql/json_table.cc b/sql/json_table.cc index 2e890aa8c22..d778434d19a 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -693,6 +693,9 @@ bool Create_json_table::add_json_table_fields(THD *thd, TABLE *table, thd->mem_root= &table->mem_root; current_counter= other; + //psergey-todo: + const Column_derived_attributes dattr(&my_charset_utf8mb4_bin); + while ((jc= jc_i++)) { Create_field *sql_f= jc->m_field; @@ -708,7 +711,7 @@ bool Create_json_table::add_json_table_fields(THD *thd, TABLE *table, sql_f->charset= &my_charset_utf8mb4_general_ci; if (sql_f->prepare_stage1(thd, thd->mem_root, table->file, - table->file->ha_table_flags())) + table->file->ha_table_flags(), &dattr)) goto err_exit; while ((jc2= it2++) != jc) @@ -868,7 +871,7 @@ int Json_table_column::print(THD *thd, Field **f, String *str) switch (m_column_type) { case FOR_ORDINALITY: - if (str->append("FOR ORDINALITY")) + if (str->append(STRING_WITH_LEN("FOR ORDINALITY"))) return 1; break; case EXISTS_PATH: @@ -877,9 +880,11 @@ int Json_table_column::print(THD *thd, Field **f, String *str) if (str->append(column_type) || ((*f)->has_charset() && m_explicit_cs && - (str->append(" CHARSET ") || str->append(m_explicit_cs->csname))) || - str->append(m_column_type == PATH ? " PATH " : " EXISTS PATH ") || - print_path(str, &m_path)) + (str->append(STRING_WITH_LEN(" CHARSET ")) || + str->append(m_explicit_cs->cs_name)))) + return 1; + const char *path= (m_column_type == PATH) ? " PATH " : " EXISTS PATH "; + if (str->append(path, strlen(path)) || print_path(str, &m_path)) return 1; break; }; @@ -970,12 +975,12 @@ int Json_table_column::On_response::print(const char *name, String *str) const } return - (str->append(' ') || str->append(resp) || - (ds && (str->append(" '") || + (str->append(' ') || str->append(resp, strlen(resp)) || + (ds && (str->append(STRING_WITH_LEN(" '")) || str->append_for_single_quote(ds->str, ds->length) || str->append('\''))) || - str->append(" ON ") || - str->append(name)); + str->append(STRING_WITH_LEN(" ON ")) || + str->append(name, strlen(name))); } @@ -1171,7 +1176,7 @@ int Json_table_nested_path::print(THD *thd, Field ***f, String *str, Json_table_column *jc= *last_column; bool first_column= TRUE; - if (str->append("COLUMNS (")) + if (str->append(STRING_WITH_LEN("COLUMNS ("))) return 1; /* loop while jc belongs to the current or nested paths. */ @@ -1180,7 +1185,7 @@ int Json_table_nested_path::print(THD *thd, Field ***f, String *str, { if (first_column) first_column= FALSE; - else if (str->append(", ")) + else if (str->append(STRING_WITH_LEN(", "))) return 1; if (jc->m_nest == c_path) @@ -1193,7 +1198,7 @@ int Json_table_nested_path::print(THD *thd, Field ***f, String *str, else { DBUG_ASSERT(column_in_this_or_nested(c_nested, jc)); - if (str->append("NESTED PATH ") || + if (str->append(STRING_WITH_LEN("NESTED PATH ")) || print_path(str, &jc->m_nest->m_path) || str->append(' ') || c_nested->print(thd, f, str, it, &jc)) @@ -1202,7 +1207,7 @@ int Json_table_nested_path::print(THD *thd, Field ***f, String *str, } } - if (str->append(")")) + if (str->append(')')) return 1; *last_column= jc; @@ -1228,12 +1233,12 @@ int Table_function_json_table::print(THD *thd, TABLE_LIST *sql_table, DBUG_ENTER("Table_function_json_table::print"); - if (str->append("JSON_TABLE(")) + if (str->append(STRING_WITH_LEN("JSON_TABLE("))) DBUG_RETURN(TRUE); m_json->print(str, query_type); - if (str->append(", ") || + if (str->append(STRING_WITH_LEN(", ")) || print_path(str, &m_nested_path.m_path) || str->append(' ') || m_nested_path.print(thd, &f_list, str, jc_i, &jc) || diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index 348bea7573e..ae6400525d4 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -7981,9 +7981,13 @@ ER_JSON_TABLE_ERROR_ON_FIELD eng "Field '%s' can't be set for JSON_TABLE '%s'." ER_JSON_TABLE_ALIAS_REQUIRED eng "Every table function must have an alias." +ER_ORACLE_COMPAT_FUNCTION_ERROR + eng "Oracle compatibility function error: %s" +ER_REMOVED_ORPHAN_TRIGGER + eng "Dropped orphan trigger '%-.64s', originally created for table: '%-.192s'" +ER_STORAGE_ENGINE_DISABLED + eng "Storage engine %s is disabled" ER_JSON_TABLE_SCALAR_EXPECTED eng "Can't store an array or an object in the scalar column '%s' of JSON_TABLE '%s'." ER_JSON_TABLE_MULTIPLE_MATCHES eng "Can't store multiple matches of the path in the column '%s' of JSON_TABLE '%s'." -ER_STORAGE_ENGINE_DISABLED - eng "Storage engine %s is disabled" diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc index a06033b4c29..d13f175f706 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -2039,7 +2039,7 @@ void Explain_table_access::append_tag_name(String *str, enum explain_extra_tag t str->append(STRING_WITH_LEN(" (scanning)")); break; case ET_TABLE_FUNCTION: - str->append("Table function: json_table"); + str->append(STRING_WITH_LEN("Table function: json_table")); break; } default: diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 50fcbf6b86f..d32c35d4f68 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -202,7 +202,7 @@ void _CONCAT_UNDERSCORED(turn_parser_debug_on,yyparse)() { \ if (unlikely(Lex->charset && !my_charset_same(Lex->charset,X))) \ my_yyabort_error((ER_COLLATION_CHARSET_MISMATCH, MYF(0), \ - X->name,Lex->charset->csname)); \ + X->cs_name,Lex->charset->cs_name)); \ Lex->charset= X; \ } \ } while(0)

1 0

[Commits] 017eea4: MDEV-25362 Incorrect name resolution for subqueries in ON expressions
by IgorBabaev 14 Apr '21

14 Apr '21

revision-id: 017eea4d18ef394bd6c7a4be8b18e002486cb906 (mariadb-10.2.31-867-g017eea4) parent(s): 55a7682a30963c1ee5e367d9f3b9b2e50e12d663 author: Igor Babaev committer: Igor Babaev timestamp: 2021-04-14 14:54:15 -0700 message: MDEV-25362 Incorrect name resolution for subqueries in ON expressions This patch sets the proper name resolution context for outer references used in a subquery from an ON clause. Usually this context is more narrow than the name resolution context of the parent select that were used before this fix. This fix revealed another problem that concerned ON expressions used in from clauses of specifications of derived tables / views / CTEs. The name resolution outer context for such ON expression must be set to NULL to prevent name resolution beyond the derived table where it is used. The solution to resolve this problem applied in sql_derived.cc was provided by Sergei Petrunia <sergey(a)mariadb.com>. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/r/join_outer.result | 40 +++++++++++++++++++++++++++++ mysql-test/r/join_outer_jcl6.result | 40 +++++++++++++++++++++++++++++ mysql-test/t/join_outer.test | 50 +++++++++++++++++++++++++++++++++++++ sql/sql_derived.cc | 31 +++++++++++++++++++++++ sql/sql_parse.cc | 4 ++- 5 files changed, 164 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index fdc36b7..1995640 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -2682,4 +2682,44 @@ id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV 12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 DROP TABLE t1,t2,t3,t4; # end of 10.1 tests +# +# MDEV-25362: name resolution for subqueries in ON expressions +# +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=t1.a) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'on clause' +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=(select max(g) from t4 where t4.h=t1.a)) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'where clause' +drop table t1,t2,t3,t4; +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); +select * from ( select * from t1 left join t2 +on b in (select x from t3 as sq1) +) as sq2; +ERROR 42S22: Unknown column 'x' in 'field list' +drop table t1,t2,t3; +# end of 10.2 tests SET optimizer_switch=@org_optimizer_switch; diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 6a6b100..58df420 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -2690,6 +2690,46 @@ id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV 12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 DROP TABLE t1,t2,t3,t4; # end of 10.1 tests +# +# MDEV-25362: name resolution for subqueries in ON expressions +# +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=t1.a) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'on clause' +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=(select max(g) from t4 where t4.h=t1.a)) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'where clause' +drop table t1,t2,t3,t4; +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); +select * from ( select * from t1 left join t2 +on b in (select x from t3 as sq1) +) as sq2; +ERROR 42S22: Unknown column 'x' in 'field list' +drop table t1,t2,t3; +# end of 10.2 tests SET optimizer_switch=@org_optimizer_switch; set join_cache_level=default; set @@optimizer_switch=@save_optimizer_switch_jcl6; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index c5b2c98..9ad2c48 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -2190,4 +2190,54 @@ DROP TABLE t1,t2,t3,t4; --echo # end of 10.1 tests +--echo # +--echo # MDEV-25362: name resolution for subqueries in ON expressions +--echo # + +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); + +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 left join + (t2 + join + t3 on + (t3.f=t1.a) + ) on (t2.c=t1.a ); + +# This must produce an error: +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 left join + (t2 + join + t3 on + (t3.f=(select max(g) from t4 where t4.h=t1.a)) + ) on (t2.c=t1.a ); + +drop table t1,t2,t3,t4; + +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); + +--error ER_BAD_FIELD_ERROR +select * from ( select * from t1 left join t2 + on b in (select x from t3 as sq1) + ) as sq2; + +drop table t1,t2,t3; + +--echo # end of 10.2 tests + SET optimizer_switch=@org_optimizer_switch; diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index be5905d..5a85b7e 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -563,6 +563,32 @@ bool mysql_derived_init(THD *thd, LEX *lex, TABLE_LIST *derived) /* + @brief + Prevent name resolution out of context of ON expressions in derived tables + + @param + join_list list of tables used in from list of a derived + + @details + The function sets the Name_resolution_context::outer_context to NULL + for all ON expressions contexts in the given join list. It does this + recursively for all nested joins the list contains. +*/ + +static void nullify_outer_context_for_on_clauses(List<TABLE_LIST>& join_list) +{ + List_iterator<TABLE_LIST> li(join_list); + while (TABLE_LIST *table= li++) + { + if (table->on_context) + table->on_context->outer_context= NULL; + if (table->nested_join) + nullify_outer_context_for_on_clauses(table->nested_join->join_list); + } +} + + +/* Create temporary table structure (but do not fill it) @param thd Thread handle @@ -695,7 +721,12 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) /* prevent name resolving out of derived table */ for (SELECT_LEX *sl= first_select; sl; sl= sl->next_select()) { + // Prevent it for the WHERE clause sl->context.outer_context= 0; + + // And for ON clauses, if there are any + nullify_outer_context_for_on_clauses(*sl->join_list); + if (!derived->is_with_table_recursive_reference() || (!derived->with->with_anchor && !derived->with->is_with_prepared_anchor())) diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 573df24..9436e11 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -7418,6 +7418,7 @@ mysql_new_select(LEX *lex, bool move_down) DBUG_RETURN(1); select_lex->select_number= ++thd->lex->stmt_lex->current_select_number; select_lex->parent_lex= lex; /* Used in init_query. */ + Name_resolution_context *curr_context= lex->context_stack.head(); select_lex->init_query(); select_lex->init_select(); lex->nest_level++; @@ -7448,7 +7449,8 @@ mysql_new_select(LEX *lex, bool move_down) By default we assume that it is usual subselect and we have outer name resolution context, if no we will assign it to 0 later */ - select_lex->context.outer_context= &select_lex->outer_select()->context; + + select_lex->context.outer_context= curr_context; } else {

1 0

[Commits] 03cb044684e: Cleanup: fake_select_lex->select_number=FAKE_SELECT_LEX_ID, not [U]INT_MAX
by psergey 14 Apr '21

14 Apr '21

revision-id: 03cb044684ead29af076d8ce3075faaf890bea9b (mariadb-10.5.2-567-g03cb044684e) parent(s): c071cc3455b61b8f757b13ed1e4a5aa8b43423c9 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-15 00:19:13 +0300 message: Cleanup: fake_select_lex->select_number=FAKE_SELECT_LEX_ID, not [U]INT_MAX SELECT_LEX objects that are "fake_select_lex" (i.e read UNION output) used both INT_MAX and UINT_MAX as select_number. - mysql_explain_union() assigned UINT_MAX - st_select_lex_unit::add_fake_select_lex assigned INT_MAX This didn't matter initially (before EXPLAIN FORMAT=JSON), because the code had no checks for this value. EXPLAIN FORMAT=JSON and later other features did introduce checks for select_number values. The check had to check for two constants and looked really confusing. This patch joins the two constants into one - FAKE_SELECT_LEX_ID. --- sql/my_json_writer.h | 2 +- sql/sql_explain.h | 16 ++++++++-------- sql/sql_parse.cc | 2 +- sql/sql_select.cc | 9 +++------ sql/sql_tvc.cc | 3 +-- 5 files changed, 14 insertions(+), 18 deletions(-) diff --git a/sql/my_json_writer.h b/sql/my_json_writer.h index bc8002de529..27aec74d08d 100644 --- a/sql/my_json_writer.h +++ b/sql/my_json_writer.h @@ -491,7 +491,7 @@ class Json_writer_object : public Json_writer_struct if (my_writer) { add_member("select_id"); - if (unlikely(select_number >= INT_MAX)) + if (unlikely(select_number == FAKE_SELECT_LEX_ID)) context.add_str("fake"); else context.add_ll(static_cast<longlong>(select_number)); diff --git a/sql/sql_explain.h b/sql/sql_explain.h index 9090416847f..42590e0bea0 100644 --- a/sql/sql_explain.h +++ b/sql/sql_explain.h @@ -74,7 +74,7 @@ class Json_writer; *************************************************************************************/ -const int FAKE_SELECT_LEX_ID= (int)UINT_MAX; +const uint FAKE_SELECT_LEX_ID= UINT_MAX; class Explain_query; @@ -108,7 +108,7 @@ class Explain_node : public Sql_alloc }; virtual enum explain_node_type get_type()= 0; - virtual int get_select_id()= 0; + virtual uint get_select_id()= 0; /** expression cache statistics @@ -166,9 +166,9 @@ class Explain_basic_join : public Explain_node bool add_table(Explain_table_access *tab, Explain_query *query); - int get_select_id() { return select_id; } + uint get_select_id() { return select_id; } - int select_id; + uint select_id; int print_explain(Explain_query *query, select_result_sink *output, uint8 explain_flags, bool is_analyze); @@ -352,7 +352,7 @@ class Explain_union : public Explain_node enum explain_node_type get_type() { return EXPLAIN_UNION; } unit_common_op operation; - int get_select_id() + uint get_select_id() { DBUG_ASSERT(union_members.elements() > 0); return union_members.at(0); @@ -879,7 +879,7 @@ class Explain_update : public Explain_node {} virtual enum explain_node_type get_type() { return EXPLAIN_UPDATE; } - virtual int get_select_id() { return 1; /* always root */ } + virtual uint get_select_id() { return 1; /* always root */ } const char *select_type; @@ -959,7 +959,7 @@ class Explain_insert : public Explain_node StringBuffer<64> table_name; enum explain_node_type get_type() { return EXPLAIN_INSERT; } - int get_select_id() { return 1; /* always root */ } + uint get_select_id() { return 1; /* always root */ } int print_explain(Explain_query *query, select_result_sink *output, uint8 explain_flags, bool is_analyze); @@ -986,7 +986,7 @@ class Explain_delete: public Explain_update bool deleting_all_rows; virtual enum explain_node_type get_type() { return EXPLAIN_DELETE; } - virtual int get_select_id() { return 1; /* always root */ } + virtual uint get_select_id() { return 1; /* always root */ } virtual int print_explain(Explain_query *query, select_result_sink *output, uint8 explain_flags, bool is_analyze); diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 3f0e71c747f..ddfc49c6d52 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -8954,7 +8954,7 @@ bool st_select_lex_unit::add_fake_select_lex(THD *thd_arg) DBUG_RETURN(1); fake_select_lex->include_standalone(this, (SELECT_LEX_NODE**)&fake_select_lex); - fake_select_lex->select_number= INT_MAX; + fake_select_lex->select_number= FAKE_SELECT_LEX_ID; fake_select_lex->parent_lex= thd_arg->lex; /* Used in init_query. */ fake_select_lex->make_empty_select(); fake_select_lex->set_linkage(GLOBAL_OPTIONS_TYPE); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index dacd636985a..6f31936b1e9 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4208,14 +4208,12 @@ bool JOIN::save_explain_data(Explain_query *output, bool can_overwrite, If there is SELECT in this statement with the same number it must be the same SELECT */ - DBUG_ASSERT(select_lex->select_number == UINT_MAX || - select_lex->select_number == INT_MAX || !output || + DBUG_ASSERT(select_lex->select_number == FAKE_SELECT_LEX_ID || !output || !output->get_select(select_lex->select_number) || output->get_select(select_lex->select_number)->select_lex == select_lex); - if (select_lex->select_number != UINT_MAX && - select_lex->select_number != INT_MAX /* this is not a UNION's "fake select */ && + if (select_lex->select_number != FAKE_SELECT_LEX_ID && have_query_plan != JOIN::QEP_NOT_PRESENT_YET && have_query_plan != JOIN::QEP_DELETED && // this happens when there was // no QEP ever, but then @@ -27780,8 +27778,7 @@ void st_select_lex::print(THD *thd, String *str, enum_query_type query_type) if ((query_type & QT_SHOW_SELECT_NUMBER) && thd->lex->all_selects_list && thd->lex->all_selects_list->link_next && - select_number != UINT_MAX && - select_number != INT_MAX) + select_number != FAKE_SELECT_LEX_ID) { str->append("/* select#"); str->append_ulonglong(select_number); diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 6984fdd6bcf..576927ea086 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -397,8 +397,7 @@ bool table_value_constr::optimize(THD *thd) create_explain_query_if_not_exists(thd->lex, thd->mem_root); have_query_plan= QEP_AVAILABLE; - if (select_lex->select_number != UINT_MAX && - select_lex->select_number != INT_MAX /* this is not a UNION's "fake select */ && + if (select_lex->select_number != FAKE_SELECT_LEX_ID && have_query_plan != QEP_NOT_PRESENT_YET && thd->lex->explain && // for "SET" command in SPs. (!thd->lex->explain->get_select(select_lex->select_number)))

1 0

[Commits] 103e3428ae4: MDEV-25407: EXISTS subquery with correlation in ON expression crashes
by psergey 13 Apr '21

13 Apr '21

revision-id: 103e3428ae443826bad6f9b67d4f0f9d810b7ace (mariadb-10.2.31-794-g103e3428ae4) parent(s): 66106130a6cbe701ec07477950094fe40d9a4716 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-13 20:32:16 +0300 message: MDEV-25407: EXISTS subquery with correlation in ON expression crashes Make Item_subselect::walk() walk the ON expressions, too. --- mysql-test/r/subselect_exists2in.result | 18 ++++++++++++++++++ mysql-test/t/subselect_exists2in.test | 23 +++++++++++++++++++++++ sql/item_subselect.cc | 30 +++++++++++++++++++++++++++++- 3 files changed, 70 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/subselect_exists2in.result b/mysql-test/r/subselect_exists2in.result index de4e428e462..ff5605575ba 100644 --- a/mysql-test/r/subselect_exists2in.result +++ b/mysql-test/r/subselect_exists2in.result @@ -1099,4 +1099,22 @@ U5.`storage_target_id` = V0.`id` ); id drop table t1,t2,t3; +# +# MDEV-25407: EXISTS subquery with correlation in ON expression crashes +# +create table t10(a int primary key); +insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t11(a int primary key); +insert into t11 select a.a + b.a* 10 + c.a * 100 from t10 a, t10 b, t10 c; +create table t1 (a int, b int); +insert into t1 select a,a from t10; +create table t2 (a int, b int); +insert into t2 select a,a from t11; +create table t3 as select * from t2; +explain select * from t1 where exists (select t2.a from t2 left join t3 on (t3.b=t1.b) where t2.a=t1.a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 10 +1 PRIMARY t2 ALL NULL NULL NULL NULL 1000 Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 1000 Using where; End temporary; Using join buffer (incremental, BNL join) +drop table t1, t2, t3, t10, t11; set optimizer_switch=default; diff --git a/mysql-test/t/subselect_exists2in.test b/mysql-test/t/subselect_exists2in.test index e27ce57038b..e70d643138b 100644 --- a/mysql-test/t/subselect_exists2in.test +++ b/mysql-test/t/subselect_exists2in.test @@ -941,5 +941,28 @@ WHERE ( drop table t1,t2,t3; +--echo # +--echo # MDEV-25407: EXISTS subquery with correlation in ON expression crashes +--echo # +create table t10(a int primary key); +insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t11(a int primary key); +insert into t11 select a.a + b.a* 10 + c.a * 100 from t10 a, t10 b, t10 c; + +create table t1 (a int, b int); +insert into t1 select a,a from t10; + +create table t2 (a int, b int); +insert into t2 select a,a from t11; + +create table t3 as select * from t2; + + +explain select * from t1 where exists (select t2.a from t2 left join t3 on (t3.b=t1.b) where t2.a=t1.a); + +drop table t1, t2, t3, t10, t11; + + #restore defaults set optimizer_switch=default; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 4b8f118ca43..ed8e5e900a2 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -664,6 +664,31 @@ bool Item_subselect::is_expensive() } +static +int walk_items_for_table_list(Item_processor processor, + bool walk_subquery, void *argument, + List<TABLE_LIST>& join_list) +{ + List_iterator<TABLE_LIST> li(join_list); + int res; + while (TABLE_LIST *table= li++) + { + if (table->on_expr) + { + if ((res= table->on_expr->walk(processor, walk_subquery, argument))) + return res; + } + if (table->nested_join) + { + if ((res= walk_items_for_table_list(processor, walk_subquery, argument, + table->nested_join->join_list))) + return res; + } + } + return 0; +} + + bool Item_subselect::walk(Item_processor processor, bool walk_subquery, void *argument) { @@ -695,7 +720,10 @@ bool Item_subselect::walk(Item_processor processor, bool walk_subquery, if (lex->having && (lex->having)->walk(processor, walk_subquery, argument)) return 1; - /* TODO: why does this walk WHERE/HAVING but not ON expressions of outer joins? */ + + if (walk_items_for_table_list(processor, walk_subquery, argument, + *lex->join_list)) + return 1; while ((item=li++)) {

1 0

[Commits] d9a80ae193e: Code cleanup: thd->lex->current_select->context == s_lex here, so use s_lex
by psergey 13 Apr '21

13 Apr '21

revision-id: d9a80ae193e6b94ecd470208bfa479cb7b48e7ee (mariadb-10.5.2-583-gd9a80ae193e) parent(s): 5cae53b4bde6b5736633a34eee5e509c0637f99e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-13 12:37:47 +0300 message: Code cleanup: thd->lex->current_select->context == s_lex here, so use s_lex --- sql/json_table.cc | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) diff --git a/sql/json_table.cc b/sql/json_table.cc index 000af50c391..5302fbd94c3 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -1044,12 +1044,12 @@ int Table_function_json_table::setup(THD *thd, TABLE_LIST *sql_table, { bool save_is_item_list_lookup; bool res; - save_is_item_list_lookup= thd->lex->current_select->is_item_list_lookup; - thd->lex->current_select->is_item_list_lookup= 0; + save_is_item_list_lookup= s_lex->is_item_list_lookup; + s_lex->is_item_list_lookup= 0; // Prepare the name resolution context. First, copy the context that is // used for name resolution of the WHERE clause - *m_context= thd->lex->current_select->context; + *m_context= s_lex->context; // Then, restrict it to only allow to refer to tables that come before the // table function reference @@ -1061,7 +1061,7 @@ int Table_function_json_table::setup(THD *thd, TABLE_LIST *sql_table, res= m_json->fix_fields_if_needed(thd, &m_json); - thd->lex->current_select->is_item_list_lookup= save_is_item_list_lookup; + s_lex->is_item_list_lookup= save_is_item_list_lookup; s_lex->set_non_agg_field_used(saved_non_agg_field_used); if (res)

1 0

[Commits] 5cae53b4bde: MDEV-25397: JSON_TABLE: Unexpected ER_MIX_OF_GROUP_FUNC_AND_FIELDS
by psergey 13 Apr '21

13 Apr '21

revision-id: 5cae53b4bde6b5736633a34eee5e509c0637f99e (mariadb-10.5.2-582-g5cae53b4bde) parent(s): eab2d0bb7ea31e2b4d468516c44d84c3e7768fab author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-13 12:34:14 +0300 message: MDEV-25397: JSON_TABLE: Unexpected ER_MIX_OF_GROUP_FUNC_AND_FIELDS When doing name resolution, do the same what WHERE/ON clauses do: they don't count in select_lex->non_agg_field_used(). --- mysql-test/suite/json/r/json_table.result | 11 +++++++++++ mysql-test/suite/json/t/json_table.test | 11 +++++++++++ sql/json_table.cc | 6 ++++++ 3 files changed, 28 insertions(+) diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result index 619aeea985e..998dc84ca35 100644 --- a/mysql-test/suite/json/r/json_table.result +++ b/mysql-test/suite/json/r/json_table.result @@ -803,5 +803,16 @@ SELECT * FROM v; b DROP VIEW v; # +# MDEV-25397: JSON_TABLE: Unexpected ER_MIX_OF_GROUP_FUNC_AND_FIELDS upon query with JOIN +# +set @save_sql_mode= @@sql_mode; +SET sql_mode='ONLY_FULL_GROUP_BY'; +CREATE TABLE t1 (a TEXT); +SELECT SUM(o) FROM t1 JOIN JSON_TABLE(t1.a, '$' COLUMNS(o FOR ORDINALITY)) jt; +SUM(o) +NULL +set sql_mode=@save_sql_mode; +drop table t1; +# # End of 10.6 tests # diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test index 9b31ddeb3b6..636ba2ca2de 100644 --- a/mysql-test/suite/json/t/json_table.test +++ b/mysql-test/suite/json/t/json_table.test @@ -698,6 +698,17 @@ SELECT * FROM v; DROP VIEW v; +--echo # +--echo # MDEV-25397: JSON_TABLE: Unexpected ER_MIX_OF_GROUP_FUNC_AND_FIELDS upon query with JOIN +--echo # +set @save_sql_mode= @@sql_mode; +SET sql_mode='ONLY_FULL_GROUP_BY'; +CREATE TABLE t1 (a TEXT); +SELECT SUM(o) FROM t1 JOIN JSON_TABLE(t1.a, '$' COLUMNS(o FOR ORDINALITY)) jt; + +set sql_mode=@save_sql_mode; +drop table t1; + --echo # --echo # End of 10.6 tests --echo # diff --git a/sql/json_table.cc b/sql/json_table.cc index fe76f9c44f0..000af50c391 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -1055,9 +1055,15 @@ int Table_function_json_table::setup(THD *thd, TABLE_LIST *sql_table, // table function reference m_context->ignored_tables= get_disallowed_table_deps(s_lex->join, t->map); + // Do the same what setup_without_group() does: do not count the referred + // fields in non_agg_field_used: + const bool saved_non_agg_field_used= s_lex->non_agg_field_used(); + res= m_json->fix_fields_if_needed(thd, &m_json); thd->lex->current_select->is_item_list_lookup= save_is_item_list_lookup; + s_lex->set_non_agg_field_used(saved_non_agg_field_used); + if (res) return TRUE; }

1 0

[Commits] 6dfeeee6fd3: MDEV-25381: JSON_TABLE: ER_WRONG_OUTER_JOIN upon query with LEFT and RIGHT joins and view
by psergey 12 Apr '21

12 Apr '21

revision-id: 6dfeeee6fd3f60c53d9afaef13cdf4bc1fa1f6da (mariadb-10.5.2-576-g6dfeeee6fd3) parent(s): a534d27a1c110a868d434741910eafb678a2a0ea author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-12 16:43:45 +0300 message: MDEV-25381: JSON_TABLE: ER_WRONG_OUTER_JOIN upon query with LEFT and RIGHT joins and view Table_function_json_table::m_dep_tables attempts to cache the value of m_json->used_tables(), poorly. Remove the cache and use the value directly. --- mysql-test/suite/json/r/json_table.result | 43 +++++++++++++++++++++++++++++++ mysql-test/suite/json/t/json_table.test | 41 +++++++++++++++++++++++++++++ sql/json_table.cc | 20 +------------- sql/json_table.h | 19 +++++++------- 4 files changed, 95 insertions(+), 28 deletions(-) diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result index d913da18c50..8789f4ced8d 100644 --- a/mysql-test/suite/json/r/json_table.result +++ b/mysql-test/suite/json/r/json_table.result @@ -750,5 +750,48 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY jt ALL NULL NULL NULL NULL 40 Table function: json_table; Using where; FirstMatch(t1) drop table t1; # +# MDEV-25381: JSON_TABLE: ER_WRONG_OUTER_JOIN upon query with LEFT and RIGHT joins and view +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT, c TEXT); +INSERT INTO t2 VALUES (1,'{}'),(2,'[]'); +CREATE VIEW v2 AS SELECT * FROM t2; +SELECT * +FROM +t1 RIGHT JOIN +t2 AS tt +LEFT JOIN +JSON_TABLE(tt.c, '$' COLUMNS(o FOR ORDINALITY)) AS jt +ON tt.b = jt.o +ON t1.a = tt.b; +a b c o +1 1 {} 1 +2 2 [] NULL +SELECT * +FROM +t1 RIGHT JOIN +v2 AS tt +LEFT JOIN +JSON_TABLE(tt.c, '$' COLUMNS(o FOR ORDINALITY)) AS jt +ON tt.b = jt.o +ON t1.a = tt.b; +a b c o +1 1 {} 1 +2 2 [] NULL +SELECT * +FROM +t1 RIGHT JOIN +v2 AS tt +LEFT JOIN +JSON_TABLE(CONCAT(tt.c,''), '$' COLUMNS(o FOR ORDINALITY)) AS jt +ON tt.b = jt.o +ON t1.a = tt.b; +a b c o +1 1 {} 1 +2 2 [] NULL +DROP VIEW v2; +DROP TABLE t1, t2; +# # End of 10.6 tests # diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test index 83cbe61b6ef..e91e89ab72f 100644 --- a/mysql-test/suite/json/t/json_table.test +++ b/mysql-test/suite/json/t/json_table.test @@ -644,6 +644,47 @@ WHERE drop table t1; +--echo # +--echo # MDEV-25381: JSON_TABLE: ER_WRONG_OUTER_JOIN upon query with LEFT and RIGHT joins and view +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (b INT, c TEXT); +INSERT INTO t2 VALUES (1,'{}'),(2,'[]'); +CREATE VIEW v2 AS SELECT * FROM t2; + +SELECT * +FROM + t1 RIGHT JOIN + t2 AS tt + LEFT JOIN + JSON_TABLE(tt.c, '$' COLUMNS(o FOR ORDINALITY)) AS jt + ON tt.b = jt.o + ON t1.a = tt.b; + +SELECT * +FROM + t1 RIGHT JOIN + v2 AS tt + LEFT JOIN + JSON_TABLE(tt.c, '$' COLUMNS(o FOR ORDINALITY)) AS jt + ON tt.b = jt.o + ON t1.a = tt.b; + +SELECT * +FROM + t1 RIGHT JOIN + v2 AS tt + LEFT JOIN + JSON_TABLE(CONCAT(tt.c,''), '$' COLUMNS(o FOR ORDINALITY)) AS jt + ON tt.b = jt.o + ON t1.a = tt.b; + +DROP VIEW v2; +DROP TABLE t1, t2; + --echo # --echo # End of 10.6 tests --echo # diff --git a/sql/json_table.cc b/sql/json_table.cc index b89f6fecbd6..e8e50347fa7 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -1324,20 +1324,6 @@ int Table_function_json_table::setup(THD *thd, TABLE_LIST *sql_table, jc->m_field->charset= jc->m_explicit_cs; } } - - m_dep_tables= m_json->used_tables(); - - if (m_dep_tables) - { - t->no_cache= TRUE; - if (unlikely(m_dep_tables & sql_table->get_map())) - { - /* Table itself is used in the argument. */ - my_error(ER_WRONG_USAGE, MYF(0), "JSON_TABLE", "argument"); - return TRUE; - } - } - return FALSE; } @@ -1475,12 +1461,8 @@ 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) { - sql_table->dep_tables&= ~m_dep_tables; - m_json->fix_after_pullout(new_parent, &m_json, merge); - m_dep_tables= m_json->used_tables(); - - sql_table->dep_tables|= m_dep_tables; + sql_table->dep_tables= used_tables(); } diff --git a/sql/json_table.h b/sql/json_table.h index 90fc667731f..04b0e6c07d2 100644 --- a/sql/json_table.h +++ b/sql/json_table.h @@ -208,8 +208,16 @@ class Table_function_json_table : public Sql_alloc 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; } + table_map used_tables() const { return m_json->used_tables(); } + bool join_cache_allowed() const + { + /* + Can use join cache when we have an outside reference. + If there's dependency on any other table or randomness, + cannot use it. + */ + return !(used_tables() & ~OUTER_REF_TABLE_BIT); + } void get_estimates(ha_rows *out_rows, double *scan_time, double *startup_cost); @@ -242,13 +250,6 @@ class Table_function_json_table : public Sql_alloc /* Context to be used for resolving the first argument. */ Name_resolution_context *m_context; - /* - 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; - /* Current NESTED PATH level being parsed */ Json_table_nested_path *cur_parent;

1 0

[Commits] a534d27a1c1: MDEV-25380: JSON_TABLE: Assertion `join->best_read < double(1.797...) fails
by psergey 12 Apr '21

12 Apr '21

revision-id: a534d27a1c110a868d434741910eafb678a2a0ea (mariadb-10.5.2-575-ga534d27a1c1) parent(s): f5f6af6bee4271f48c76a5b3724a974e426a5c70 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-12 16:22:18 +0300 message: MDEV-25380: JSON_TABLE: Assertion `join->best_read < double(1.797...) fails The query used a subquery of this form: SELECT ... WHERE EXISTS( SELECT ... FROM JSON_TABLE(outer_ref, ..) as JT WHERE trivial_correlation_cond) EXISTS-to-IN conversion code was unable to see that the subquery will still be correlated after the trivial_correlation is removed, which eventually caused a crash due to inability to construct a query plan. Fixed by making Item_subselect::walk() also walk arguments of Table Functions. --- mysql-test/suite/json/r/json_table.result | 16 +++++++++++++++ mysql-test/suite/json/t/json_table.test | 17 ++++++++++++++++ sql/item_subselect.cc | 33 +++++++++++++++++++++++++++++++ sql/json_table.cc | 6 ++++++ sql/json_table.h | 3 +++ 5 files changed, 75 insertions(+) diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result index 9f1d1bce99e..d913da18c50 100644 --- a/mysql-test/suite/json/r/json_table.result +++ b/mysql-test/suite/json/r/json_table.result @@ -734,5 +734,21 @@ Shirt blue 20 Shirt white 20 drop table t1; # +# MDEV-25380: JSON_TABLE: Assertion `join->best_read < double(1.797...) fails +# +CREATE TABLE t1 (a INT, b TEXT); +INSERT INTO t1 VALUES (1,'{}'),(2,'[]'); +explain +SELECT * +FROM t1 +WHERE +EXISTS(SELECT * +FROM JSON_TABLE(b, '$' COLUMNS(o FOR ORDINALITY)) AS jt +WHERE jt.o = t1.a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY jt ALL NULL NULL NULL NULL 40 Table function: json_table; Using where; FirstMatch(t1) +drop table t1; +# # End of 10.6 tests # diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test index aa4e7397a6a..83cbe61b6ef 100644 --- a/mysql-test/suite/json/t/json_table.test +++ b/mysql-test/suite/json/t/json_table.test @@ -627,6 +627,23 @@ select t.item_name, jt.* from (select t1.item_name, concat(concat(concat("{\"col drop table t1; +--echo # +--echo # MDEV-25380: JSON_TABLE: Assertion `join->best_read < double(1.797...) fails +--echo # + +CREATE TABLE t1 (a INT, b TEXT); +INSERT INTO t1 VALUES (1,'{}'),(2,'[]'); + +explain +SELECT * +FROM t1 +WHERE + EXISTS(SELECT * + FROM JSON_TABLE(b, '$' COLUMNS(o FOR ORDINALITY)) AS jt + WHERE jt.o = t1.a); + +drop table t1; + --echo # --echo # End of 10.6 tests --echo # diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 76b09efec17..e4e8bf2a1e8 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -719,6 +719,34 @@ bool Item_subselect::unknown_splocal_processor(void *argument) } +static +int walk_table_functions_for_list(Item_processor processor, + bool walk_subquery, void *argument, + List<TABLE_LIST>& join_list) +{ + List_iterator<TABLE_LIST> li(join_list); + int res; + while (TABLE_LIST *table= li++) + { + if (Table_function_json_table *tf= table->table_function) + { + if ((res= tf->walk_items(processor, walk_subquery, argument))) + { + return res; + } + } + if (table->nested_join) + { + if ((res= walk_table_functions_for_list(processor, walk_subquery, + argument, + table->nested_join->join_list))) + return res; + } + } + return 0; +} + + bool Item_subselect::walk(Item_processor processor, bool walk_subquery, void *argument) { @@ -751,6 +779,11 @@ bool Item_subselect::walk(Item_processor processor, bool walk_subquery, argument)) return 1; /* TODO: why does this walk WHERE/HAVING but not ON expressions of outer joins? */ + /* Consider walking ON epxression in walk_table_functions_for_list */ + + if (walk_table_functions_for_list(processor, walk_subquery, argument, + *lex->join_list)) + return 1; while ((item=li++)) { diff --git a/sql/json_table.cc b/sql/json_table.cc index dae1c694d78..b89f6fecbd6 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -1341,6 +1341,12 @@ int Table_function_json_table::setup(THD *thd, TABLE_LIST *sql_table, return FALSE; } +int Table_function_json_table::walk_items(Item_processor processor, + bool walk_subquery, void *argument) +{ + return m_json->walk(processor, walk_subquery, argument); +} + void Table_function_json_table::get_estimates(ha_rows *out_rows, double *scan_time, double *startup_cost) diff --git a/sql/json_table.h b/sql/json_table.h index 07d53c55638..90fc667731f 100644 --- a/sql/json_table.h +++ b/sql/json_table.h @@ -200,6 +200,9 @@ class Table_function_json_table : public Sql_alloc /*** Name resolution functions ***/ int setup(THD *thd, TABLE_LIST *sql_table, SELECT_LEX *s_lex); + int walk_items(Item_processor processor, bool walk_subquery, + void *argument); + /*** Functions for interaction with the Query Optimizer ***/ void fix_after_pullout(TABLE_LIST *sql_table, st_select_lex *new_parent, bool merge);

1 0