diff options
| -rw-r--r-- | mysql-test/main/insert_select.result | 70 | ||||
| -rw-r--r-- | mysql-test/main/insert_select.test | 54 | ||||
| -rw-r--r-- | mysql-test/main/view.result | 10 | ||||
| -rw-r--r-- | mysql-test/main/view.test | 2 | ||||
| -rw-r--r-- | sql/sql_base.cc | 178 | ||||
| -rw-r--r-- | sql/sql_insert.cc | 4 | ||||
| -rw-r--r-- | sql/sql_select.cc | 4 | ||||
| -rw-r--r-- | sql/sql_select.h | 1 |
8 files changed, 264 insertions, 59 deletions
diff --git a/mysql-test/main/insert_select.result b/mysql-test/main/insert_select.result index e85c4982137..ea770535e8f 100644 --- a/mysql-test/main/insert_select.result +++ b/mysql-test/main/insert_select.result @@ -883,4 +883,74 @@ INSERT INTO t1 SELECT a*2 FROM t1 ORDER BY a; Warnings: Warning 1264 Out of range value for column 'a' at row 4 DROP TABLE t1; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 (a) SELECT SUM(1); +INSERT INTO t1 (a, b) SELECT AVG(2), MIN(3); +INSERT INTO t1 (b) SELECT AVG('x') OVER (); +ERROR 22007: Truncated incorrect DOUBLE value: 'x' +INSERT INTO t1 SELECT MIN(7) OVER (), MAX(8) OVER(); +SELECT * FROM t1; +a b +1 NULL +2 3 +7 8 +PREPARE stmt FROM 'INSERT INTO t1 (a) SELECT AVG(?)'; +EXECUTE stmt USING 9; +EXECUTE stmt USING 10; +PREPARE stmt FROM 'INSERT INTO t1 SELECT MIN(?), MAX(?)'; +EXECUTE stmt USING 11, 12; +EXECUTE stmt USING 13, 14; +DEALLOCATE PREPARE stmt; +SELECT * FROM t1; +a b +1 NULL +2 3 +7 8 +9 NULL +10 NULL +11 12 +13 14 +CREATE PROCEDURE p1(param_a INT, param_b INT) +BEGIN +INSERT INTO t1 SELECT MIN(param_a) OVER (), MAX(param_b); +END// +CALL p1(21, 22); +CALL p1(23, 24); +SELECT * FROM t1; +a b +1 NULL +2 3 +7 8 +9 NULL +10 NULL +11 12 +13 14 +21 22 +23 24 +CREATE TABLE t2 ( +a DECIMAL UNIQUE CHECK (CASE 0 * 27302337.000000 WHEN 34 THEN ++ 'x' LIKE 'x' OR a NOT IN (-1 / TRUE ^ 2) ELSE 7105743.000000 END)); +INSERT INTO t2 VALUES (90),( -1),(31152443.000000),(-32768),(NULL),(NULL); +INSERT INTO t2 SELECT AVG('x') OVER ( +PARTITION BY ((NOT AVG(76698761.000000))) IS NOT NULL); +ERROR 22007: Truncated incorrect DOUBLE value: 'x' +INSERT IGNORE INTO t2 () VALUES (0),('x'),(3751286.000000), +('x'),((a = 'x' AND 0 AND 0)); +Warnings: +Warning 1366 Incorrect decimal value: 'x' for column `test`.`t2`.`a` at row 2 +Warning 1062 Duplicate entry '0' for key 'a' +Warning 1366 Incorrect decimal value: 'x' for column `test`.`t2`.`a` at row 4 +Warning 1062 Duplicate entry '0' for key 'a' +Warning 1062 Duplicate entry '0' for key 'a' +INSERT INTO t2 VALUES (127); +INSERT INTO t2 SELECT -2147483648 END FROM t2 AS TEXT JOIN t2 JOIN t2 TABLES; +ERROR 23000: Duplicate entry '-2147483648' for key 'a' +ALTER TABLE t2 ADD ( +b INT UNIQUE CHECK ((a = 'x' AND ((-(+(BINARY 49730460.000000)))) = 'x' +BETWEEN 'x' AND 'x'))); +ERROR 22007: Truncated incorrect DECIMAL value: 'x' +UPDATE t2 SET a = -128 WHERE a IS NULL ORDER BY 78 IN ('x','x'),a; +ERROR 23000: Duplicate entry '-128' for key 'a' +DROP TABLE t1, t2; +DROP PROCEDURE p1; # End of 10.2 test diff --git a/mysql-test/main/insert_select.test b/mysql-test/main/insert_select.test index 91b2cc6f981..1f672acc203 100644 --- a/mysql-test/main/insert_select.test +++ b/mysql-test/main/insert_select.test @@ -459,4 +459,58 @@ INSERT INTO t1 SELECT a*2 FROM t1 ORDER BY a; DROP TABLE t1; +# +# MDEV-26427 MariaDB Server SEGV on INSERT .. SELECT +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 (a) SELECT SUM(1); +INSERT INTO t1 (a, b) SELECT AVG(2), MIN(3); + +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 (b) SELECT AVG('x') OVER (); +INSERT INTO t1 SELECT MIN(7) OVER (), MAX(8) OVER(); +SELECT * FROM t1; + +PREPARE stmt FROM 'INSERT INTO t1 (a) SELECT AVG(?)'; +EXECUTE stmt USING 9; +EXECUTE stmt USING 10; + +PREPARE stmt FROM 'INSERT INTO t1 SELECT MIN(?), MAX(?)'; +EXECUTE stmt USING 11, 12; +EXECUTE stmt USING 13, 14; +DEALLOCATE PREPARE stmt; +SELECT * FROM t1; + +DELIMITER //; +CREATE PROCEDURE p1(param_a INT, param_b INT) +BEGIN +INSERT INTO t1 SELECT MIN(param_a) OVER (), MAX(param_b); +END// +DELIMITER ;// +CALL p1(21, 22); +CALL p1(23, 24); +SELECT * FROM t1; + +CREATE TABLE t2 ( + a DECIMAL UNIQUE CHECK (CASE 0 * 27302337.000000 WHEN 34 THEN + + 'x' LIKE 'x' OR a NOT IN (-1 / TRUE ^ 2) ELSE 7105743.000000 END)); +INSERT INTO t2 VALUES (90),( -1),(31152443.000000),(-32768),(NULL),(NULL); +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t2 SELECT AVG('x') OVER ( + PARTITION BY ((NOT AVG(76698761.000000))) IS NOT NULL); +INSERT IGNORE INTO t2 () VALUES (0),('x'),(3751286.000000), + ('x'),((a = 'x' AND 0 AND 0)); +INSERT INTO t2 VALUES (127); +--error ER_DUP_ENTRY +INSERT INTO t2 SELECT -2147483648 END FROM t2 AS TEXT JOIN t2 JOIN t2 TABLES; +--error ER_TRUNCATED_WRONG_VALUE +ALTER TABLE t2 ADD ( + b INT UNIQUE CHECK ((a = 'x' AND ((-(+(BINARY 49730460.000000)))) = 'x' + BETWEEN 'x' AND 'x'))); +--error ER_DUP_ENTRY +UPDATE t2 SET a = -128 WHERE a IS NULL ORDER BY 78 IN ('x','x'),a; + +DROP TABLE t1, t2; +DROP PROCEDURE p1; + --echo # End of 10.2 test diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 5262c9f2bf2..82f618066a8 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -1502,6 +1502,8 @@ execute stmt1 using @a; set @a= 301; execute stmt1 using @a; deallocate prepare stmt1; +insert into v3(a) select sum(302); +insert into v3(a) select sum(303) over (); select * from v3; a b 100 0 @@ -1520,6 +1522,14 @@ a b 301 10 301 1000 301 2000 +302 0 +302 10 +302 1000 +302 2000 +303 0 +303 10 +303 1000 +303 2000 drop view v3; drop tables t1,t2; create table t1(f1 int); diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 46232b1bcdc..770be8dbb5f 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -1334,6 +1334,8 @@ execute stmt1 using @a; set @a= 301; execute stmt1 using @a; deallocate prepare stmt1; +insert into v3(a) select sum(302); +insert into v3(a) select sum(303) over (); --sorted_result select * from v3; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index e825c247fc1..60b0b88bf5f 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -7577,46 +7577,118 @@ bool setup_fields(THD *thd, Ref_ptr_array ref_pointer_array, DBUG_RETURN(MY_TEST(thd->is_error())); } +/* + make list of leaves for a single TABLE_LIST + + SYNOPSIS + make_leaves_for_single_table() + thd Thread handler + leaves List of leaf tables to be filled + table TABLE_LIST object to process + full_table_list Whether to include tables from mergeable derived table/view +*/ +void make_leaves_for_single_table(THD *thd, List<TABLE_LIST> &leaves, + TABLE_LIST *table, bool& full_table_list, + TABLE_LIST *boundary) +{ + if (table == boundary) + full_table_list= !full_table_list; + if (full_table_list && table->is_merged_derived()) + { + SELECT_LEX *select_lex= table->get_single_select(); + /* + It's safe to use select_lex->leaf_tables because all derived + tables/views were already prepared and has their leaf_tables + set properly. + */ + make_leaves_list(thd, leaves, select_lex->get_table_list(), + full_table_list, boundary); + } + else + { + leaves.push_back(table, thd->mem_root); + } +} + /* make list of leaves of join table tree SYNOPSIS make_leaves_list() - list pointer to pointer on list first element - tables table list - full_table_list whether to include tables from mergeable derived table/view. - we need them for checks for INSERT/UPDATE statements only. - - RETURN pointer on pointer to next_leaf of last element + leaves List of leaf tables to be filled + tables Table list + full_table_list Whether to include tables from mergeable derived table/view. + We need them for checks for INSERT/UPDATE statements only. */ -void make_leaves_list(THD *thd, List<TABLE_LIST> &list, TABLE_LIST *tables, +void make_leaves_list(THD *thd, List<TABLE_LIST> &leaves, TABLE_LIST *tables, bool full_table_list, TABLE_LIST *boundary) { for (TABLE_LIST *table= tables; table; table= table->next_local) { - if (table == boundary) - full_table_list= !full_table_list; - if (full_table_list && table->is_merged_derived()) - { - SELECT_LEX *select_lex= table->get_single_select(); - /* - It's safe to use select_lex->leaf_tables because all derived - tables/views were already prepared and has their leaf_tables - set properly. - */ - make_leaves_list(thd, list, select_lex->get_table_list(), - full_table_list, boundary); - } - else - { - list.push_back(table, thd->mem_root); - } + make_leaves_for_single_table(thd, leaves, table, full_table_list, + boundary); + } +} + + +/* + Setup the map and other attributes for a single TABLE_LIST object + + SYNOPSIS + setup_table_attributes() + thd Thread handler + table_list TABLE_LIST object to process + first_select_table First table participating in SELECT for INSERT..SELECT + statements, NULL for other cases + tablenr Serial number of the table in the SQL statement + + RETURN + false Success + true Failure +*/ +bool setup_table_attributes(THD *thd, TABLE_LIST *table_list, + TABLE_LIST *first_select_table, + uint &tablenr) +{ + TABLE *table= table_list->table; + if (table) + table->pos_in_table_list= table_list; + if (first_select_table && table_list->top_table() == first_select_table) + { + /* new counting for SELECT of INSERT ... SELECT command */ + first_select_table= 0; + thd->lex->select_lex.insert_tables= tablenr; + tablenr= 0; + } + if (table_list->jtbm_subselect) + { + table_list->jtbm_table_no= tablenr; + } + else if (table) + { + table->pos_in_table_list= table_list; + setup_table_map(table, table_list, tablenr); + + if (table_list->process_index_hints(table)) + return true; + } + tablenr++; + /* + We test the max tables here as we setup_table_map() should not be called + with tablenr >= 64 + */ + if (tablenr > MAX_TABLES) + { + my_error(ER_TOO_MANY_TABLES, MYF(0), static_cast<int>(MAX_TABLES)); + return true; } + return false; } + /* prepare tables @@ -7673,7 +7745,14 @@ bool setup_tables(THD *thd, Name_resolution_context *context, leaves.empty(); if (select_lex->prep_leaf_list_state != SELECT_LEX::SAVED) { - make_leaves_list(thd, leaves, tables, full_table_list, first_select_table); + /* + For INSERT ... SELECT statements we must not include the first table + (where the data is being inserted into) in the list of leaves + */ + TABLE_LIST *tables_for_leaves= + select_insert ? first_select_table : tables; + make_leaves_list(thd, leaves, tables_for_leaves, full_table_list, + first_select_table); select_lex->prep_leaf_list_state= SELECT_LEX::READY; select_lex->leaf_tables_exec.empty(); } @@ -7684,37 +7763,34 @@ bool setup_tables(THD *thd, Name_resolution_context *context, leaves.push_back(table_list, thd->mem_root); } + List_iterator<TABLE_LIST> ti(leaves); while ((table_list= ti++)) { - TABLE *table= table_list->table; - if (table) - table->pos_in_table_list= table_list; - if (first_select_table && - table_list->top_table() == first_select_table) - { - /* new counting for SELECT of INSERT ... SELECT command */ - first_select_table= 0; - thd->lex->select_lex.insert_tables= tablenr; - tablenr= 0; - } - if(table_list->jtbm_subselect) - { - table_list->jtbm_table_no= tablenr; - } - else if (table) - { - table->pos_in_table_list= table_list; - setup_table_map(table, table_list, tablenr); + if (setup_table_attributes(thd, table_list, first_select_table, tablenr)) + DBUG_RETURN(1); + } - if (table_list->process_index_hints(table)) + if (select_insert) + { + /* + The table/view in which the data is inserted must not be included into + the leaf_tables list. But we need this table/view to setup attributes + for it. So build a temporary list of leaves and setup attributes for + the tables included + */ + List<TABLE_LIST> leaves; + TABLE_LIST *table= tables; + + make_leaves_for_single_table(thd, leaves, table, full_table_list, + first_select_table); + + List_iterator<TABLE_LIST> ti(leaves); + while ((table_list= ti++)) + { + if (setup_table_attributes(thd, table_list, first_select_table, + tablenr)) DBUG_RETURN(1); } - tablenr++; - } - if (tablenr > MAX_TABLES) - { - my_error(ER_TOO_MANY_TABLES,MYF(0), static_cast<int>(MAX_TABLES)); - DBUG_RETURN(1); } } else diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 38551867902..a180147190b 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -1429,8 +1429,7 @@ static bool mysql_prepare_insert_check_table(THD *thd, TABLE_LIST *table_list, if (insert_into_view && !fields.elements) { thd->lex->empty_field_list_on_rset= 1; - if (!thd->lex->select_lex.leaf_tables.head()->table || - table_list->is_multitable()) + if (!table_list->table || table_list->is_multitable()) { my_error(ER_VIEW_NO_INSERT_FIELD_LIST, MYF(0), table_list->view_db.str, table_list->view_name.str); @@ -3641,7 +3640,6 @@ bool mysql_insert_select_prepare(THD *thd) &select_lex->where, TRUE)) DBUG_RETURN(TRUE); - DBUG_ASSERT(select_lex->leaf_tables.elements != 0); List_iterator<TABLE_LIST> ti(select_lex->leaf_tables); TABLE_LIST *table; uint insert_tables; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index cceb50dc9ab..0803e785302 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1597,7 +1597,6 @@ JOIN::optimize_inner() /* Merge all mergeable derived tables/views in this SELECT. */ if (select_lex->handle_derived(thd->lex, DT_MERGE)) DBUG_RETURN(TRUE); - table_count= select_lex->leaf_tables.elements; } if (select_lex->first_cond_optimization && @@ -1640,8 +1639,6 @@ JOIN::optimize_inner() eval_select_list_used_tables(); - table_count= select_lex->leaf_tables.elements; - if (select_lex->options & OPTION_SCHEMA_TABLE && optimize_schema_tables_memory_usage(select_lex->leaf_tables)) DBUG_RETURN(1); @@ -13162,7 +13159,6 @@ void JOIN::cleanup(bool full) /* Free the original optimized join created for the group_by_handler */ join_tab= original_join_tab; original_join_tab= 0; - table_count= original_table_count; } if (join_tab) diff --git a/sql/sql_select.h b/sql/sql_select.h index 4591aa7e143..c8ff8b24385 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1237,7 +1237,6 @@ public: Pushdown_query *pushdown_query; JOIN_TAB *original_join_tab; - uint original_table_count; /******* Join optimization state members start *******/ /* |