From 452c9a4d72c18a19136a91f4d59ee60eedd486be Mon Sep 17 00:00:00 2001 From: Rucha Deodhar Date: Fri, 24 Dec 2021 14:00:47 +0530 Subject: MDEV-26698: Incorrect row number upon INSERT .. SELECT from the same table: rows are counted twice Analysis: When the table we are trying to insert into and the SELECT table are same for INSERT ... SELECT, rows from the SELECT table are copied into internal temporary table and then to the INSERT table. We only want to count the rows when we start inserting into the table. Fix: Reset the counter to 1 before starting to copy from internal temporary table to select table and then increment the counter. --- sql/sql_select.cc | 19 ++++++++++++------- 1 file changed, 12 insertions(+), 7 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index a331f4f3dbc..a7e2ac4e374 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -19146,11 +19146,8 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, */ if (shortcut_for_distinct && found_records != join->found_records) DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS); - } - else - { - join->thd->get_stmt_da()->inc_current_row_for_warning(); - join_tab->read_record.unlock_row(join_tab); + + DBUG_RETURN(NESTED_LOOP_OK); } } else @@ -19160,9 +19157,11 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, with the beginning coinciding with the current partial join. */ join->join_examined_rows++; - join->thd->get_stmt_da()->inc_current_row_for_warning(); - join_tab->read_record.unlock_row(join_tab); } + + join->thd->get_stmt_da()->inc_current_row_for_warning(); + join_tab->read_record.unlock_row(join_tab); + DBUG_RETURN(NESTED_LOOP_OK); } @@ -26945,6 +26944,12 @@ AGGR_OP::end_send() table->reginfo.lock_type= TL_UNLOCK; bool in_first_read= true; + + /* + Reset the counter before copying rows from internal temporary table to + INSERT table. + */ + join_tab->join->thd->get_stmt_da()->reset_current_row_for_warning(); while (rc == NESTED_LOOP_OK) { int error; -- cgit v1.2.1 From 810ef9117a54f8dfbd362d959d46a2322f86a9d0 Mon Sep 17 00:00:00 2001 From: Dmitry Shulga Date: Wed, 19 Jan 2022 11:15:22 +0700 Subject: MDEV-24827: MariaDB 10.5.5 crash (sig 11) during a SELECT Running a query using cursor could lead to a server crash on building a temporary table used for handling the query. For example, the following cursor DECLARE cur1 CURSOR FOR SELECT t2.c1 AS c1 FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 WHERE EXISTS (SELECT 1 FROM t1 WHERE c2 = -1) ORDER BY c1; declared and executed inside a stored routine could result in server crash on creating a temporary table used for handling the ORDER BY clause. Crash occurred on attempt to create the temporary table's fields based on fields whose data located in a memory root that already freed. It happens inside the function return_zero_rows() where the method Select_materialize::send_result_set_metadata() is invoked for cursor case. This method calls the st_select_lex_unit::get_column_types() in order to get a list of items with types of columns for the temporary table being created. The method st_select_lex_unit::get_column_types() returns first_select()->join->fields in case it is invoked for a cursor. Unfortunately, this memory has been already deallocated bit earlier by calling join->join_free(); inside the function return_zero_rows(). In case the query listed in the example is run in conventional way (without using cursor) the method st_select_lex_unit::get_column_types() returns first_select()->item_list that is not touched by invocation of the method join->join_free() so everything is fine for that. So, to fix the issue the resources allocated for the JOIN class should be released after any activities with the JOIN class has been completed, that is as the last statement before returning from the function return_zero_rows(). This patch includes tests both for the case when a cursor is run explicitly from within a stored routine and for the case when a cursor is opened implicitly as prescribed by the STMT_ATTR_CURSOR_TYPE attribute of binary protocol (the case of prepared statement). --- sql/sql_select.cc | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index a7e2ac4e374..abdc79c1bf3 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -12914,8 +12914,6 @@ return_zero_rows(JOIN *join, select_result *result, List &tables, DBUG_RETURN(0); } - join->join_free(); - if (send_row) { /* @@ -12962,6 +12960,8 @@ return_zero_rows(JOIN *join, select_result *result, List &tables, if (!send_error) result->send_eof(); // Should be safe } + join->join_free(); + DBUG_RETURN(0); } -- cgit v1.2.1 From 7922fbf7b7704db520ceec4ef5878fba632618f4 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Tue, 23 Nov 2021 17:55:08 +0300 Subject: MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log The problem affected queries in form: SELECT FROM (SELECT where Split Materialized is applicable) WHERE 1=0 The problem was caused by this: - The select in derived table uses two-phase optimization (due to a possible Split Materialized). - The primary select has "Impossible where" and so it short-cuts its optimization. - The optimization for the SELECT in the derived table is never finished, and EXPLAIN data structure has a dangling pointer to select #2. Fixed with this: make JOIN::optimize_stage2() invoke optimization of derived tables when it is handing a degenerate JOIN with zero tables. We will not execute the derived tables but we need their query plans for [SHOW]EXPLAIN. --- sql/sql_select.cc | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 40ecc45df45..93e1c0ccba8 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2732,6 +2732,14 @@ setup_subq_exit: } if (make_aggr_tables_info()) DBUG_RETURN(1); + + /* + It could be that we've only done optimization stage 1 for + some of the derived tables, and never did stage 2. + Do it now, otherwise Explain data structure will not be complete. + */ + if (select_lex->handle_derived(thd->lex, DT_OPTIMIZE)) + DBUG_RETURN(1); } /* Even with zero matching rows, subqueries in the HAVING clause may -- cgit v1.2.1 From 7dcef6504620114f503dea92eadbb0e0fe95eb17 Mon Sep 17 00:00:00 2001 From: Dmitry Shulga Date: Thu, 20 Jan 2022 16:25:43 +0700 Subject: MDEV-24827: Follow-up patch to fix compilation warning Mixed declarations and code is not allowed for C90 so fix it to avoid compilation break on some platforms. --- sql/sql_select.cc | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index abdc79c1bf3..db8a63eeb48 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -12960,6 +12960,12 @@ return_zero_rows(JOIN *join, select_result *result, List &tables, if (!send_error) result->send_eof(); // Should be safe } + /* + JOIN::join_free() must be called after the virtual method + select::send_result_set_metadata() returned control since + implementation of this method could use data strutcures + that are released by the method JOIN::join_free(). + */ join->join_free(); DBUG_RETURN(0); -- cgit v1.2.1 From da37bfd8d6df4aca0476051daa321fef2787f937 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Tue, 28 Dec 2021 17:43:40 +0400 Subject: MDEV-18918 SQL mode EMPTY_STRING_IS_NULL breaks RBR upon CREATE TABLE .. SELECT Removing DEFAULT from INFORMATION_SCHEMA columns. DEFAULT in read-only tables is rather meaningless. Upgrade should go smoothly. Also fixes: MDEV-20254 Problems with EMPTY_STRING_IS_NULL and I_S tables --- sql/sql_select.cc | 13 ++++++++++--- 1 file changed, 10 insertions(+), 3 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 93e1c0ccba8..966220e6b73 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -17850,8 +17850,15 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List &fields, The test for item->marker == 4 is ensure we don't create a group-by key over a bit field as heap tables can't handle that. */ - Field *new_field= (param->schema_table) ? - item->create_field_for_schema(thd, table) : + Field *new_field; + if (param->schema_table) + { + if ((new_field= item->create_field_for_schema(thd, table))) + new_field->flags|= NO_DEFAULT_VALUE_FLAG; + } + else + { + new_field= create_tmp_field(thd, table, item, type, ©_func, tmp_from_field, &default_field[fieldnr], group != 0, @@ -17866,7 +17873,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List &fields, */ item->marker == 4 || param->bit_fields_as_long, force_copy_fields); - + } if (unlikely(!new_field)) { if (unlikely(thd->is_fatal_error)) -- cgit v1.2.1 From 00412656719bba79cf9a350db05065b0b7007680 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 24 Jan 2022 23:14:46 -0800 Subject: MDEV-27510 Query returns wrong result when using split optimization This bug may affect the queries that uses a grouping derived table with grouping list containing references to columns from different tables if the optimizer decides to employ the split optimization for the derived table. In some very specific cases it may affect queries with a grouping derived table that refers only one base table. This bug was caused by an improper fix for the bug MDEV-25128. The fix tried to get rid of the equality conditions pushed into the where clause of the grouping derived table T to which the split optimization had been applied. The fix erroneously assumed that only those pushed equalities that were used for ref access of the tables referenced by T were needed. In fact the function remove_const() that figures out what columns from the group list can be removed if the split optimization is applied can uses other pushed equalities as well. This patch actually provides a proper fix for MDEV-25128. Rather than trying to remove invalid pushed equalities referencing the fields of SJM tables with a look-up access the patch attempts not to push such equalities. Approved by Oleksandr Byelkin --- sql/sql_select.cc | 18 +++--------------- 1 file changed, 3 insertions(+), 15 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 966220e6b73..28f018fdace 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -9755,6 +9755,9 @@ bool JOIN::get_best_combination() hash_join= FALSE; fix_semijoin_strategies_for_picked_join_order(this); + + if (inject_splitting_cond_for_all_tables_with_split_opt()) + DBUG_RETURN(TRUE); JOIN_TAB_RANGE *root_range; if (!(root_range= new (thd->mem_root) JOIN_TAB_RANGE)) @@ -21863,21 +21866,6 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, cond->marker=3; // Checked when read return (COND*) 0; } - /* - If cond is an equality injected for split optimization then - a. when retain_ref_cond == false : cond is removed unconditionally - (cond that supports ref access is removed by the preceding code) - b. when retain_ref_cond == true : cond is removed if it does not - support ref access - */ - if (left_item->type() == Item::FIELD_ITEM && - is_eq_cond_injected_for_split_opt((Item_func_eq *) cond) && - (!retain_ref_cond || - !test_if_ref(root_cond, (Item_field*) left_item,right_item))) - { - cond->marker=3; - return (COND*) 0; - } } cond->marker=2; cond->set_join_tab_idx(join_tab_idx_arg); -- cgit v1.2.1