diff options
| -rw-r--r-- | mysql-test/r/subselect_mat.result | 27 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj_mat.result | 27 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj_mat.test | 21 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 21 | ||||
| -rw-r--r-- | sql/sql_base.cc | 3 | ||||
| -rw-r--r-- | sql/sql_select.cc | 12 |
6 files changed, 102 insertions, 9 deletions
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 7039bbfca15..8c25577eb01 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2145,6 +2145,33 @@ drop database mysqltest1; drop database mysqltest2; drop database mysqltest3; drop database mysqltest4; +# +# MDEV-7810 Wrong result on execution of a query as a PS +# (both 1st and further executions) +CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES (0),(8); +SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2; +a +0 +8 +SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a)); +MIN(t3.a) +0 +SELECT a FROM ( SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2 ) AS sq +WHERE a IN ( SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a) ) ); +a +0 +PREPARE stmt FROM " +SELECT a FROM ( SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2 ) AS sq +WHERE a IN ( SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a) ) ) +"; +execute stmt; +a +0 +execute stmt; +a +0 +drop table t1; # End of 5.5 tests set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 58810670395..6c69d195bd3 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2185,4 +2185,31 @@ drop database mysqltest1; drop database mysqltest2; drop database mysqltest3; drop database mysqltest4; +# +# MDEV-7810 Wrong result on execution of a query as a PS +# (both 1st and further executions) +CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES (0),(8); +SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2; +a +0 +8 +SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a)); +MIN(t3.a) +0 +SELECT a FROM ( SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2 ) AS sq +WHERE a IN ( SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a) ) ); +a +0 +PREPARE stmt FROM " +SELECT a FROM ( SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2 ) AS sq +WHERE a IN ( SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a) ) ) +"; +execute stmt; +a +0 +execute stmt; +a +0 +drop table t1; # End of 5.5 tests diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 912e9d5befd..63c72f20e21 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1841,5 +1841,24 @@ drop database mysqltest2; drop database mysqltest3; drop database mysqltest4; ---echo # End of 5.5 tests +--echo # +--echo # MDEV-7810 Wrong result on execution of a query as a PS +--echo # (both 1st and further executions) + +CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES (0),(8); + +SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2; +SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a)); +SELECT a FROM ( SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2 ) AS sq +WHERE a IN ( SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a) ) ); +PREPARE stmt FROM " +SELECT a FROM ( SELECT DISTINCT t9.* FROM t1 AS t9, t1 AS t2 ) AS sq +WHERE a IN ( SELECT MIN(t3.a) FROM ( t1 AS t3 INNER JOIN t1 AS t4 ON (t3.a = t4.a) ) ) +"; +execute stmt; +execute stmt; +drop table t1; + +--echo # End of 5.5 tests diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 62df666b71f..8b0c6efc9ee 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1999,12 +1999,31 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN * join, during JOIN::optimize: this->tmp_having= this->having; this->having= 0; */ Item* join_having= join->having ? join->having : join->tmp_having; - DBUG_ENTER("Item_in_subselect::create_single_in_to_exists_cond"); *where_item= NULL; *having_item= NULL; + /* + For PS we have to do fix_fields(expr) here to ensure that it's + evaluated in the outer context. If not, then fix_having() will do + a fix_fields(expr) in the inner context and mark expr as + 'depended', which will cause update_ref_and_keys() to find wrong + keys. + When not running PS, fix_fields(expr) as already been done earlier and + the following test does nothing. + */ + if (expr && !expr->fixed) + { + SELECT_LEX *save_current_select= thd->lex->current_select; + thd->lex->current_select= thd->lex->current_select->outer_select(); + bool tmp; + tmp= expr->fix_fields(thd, 0); + thd->lex->current_select= save_current_select; + if (tmp) + DBUG_RETURN(true); + } + if (join_having || select_lex->with_sum_func || select_lex->group_list.elements) { diff --git a/sql/sql_base.cc b/sql/sql_base.cc index fcd17b25b2d..aefa82405ff 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -6859,6 +6859,7 @@ find_field_in_tables(THD *thd, Item_ident *item, if (item->cached_table) { + DBUG_PRINT("info", ("using cached table")); /* This shortcut is used by prepared statements. We assume that TABLE_LIST *first_table is not changed during query execution (which @@ -6935,8 +6936,6 @@ find_field_in_tables(THD *thd, Item_ident *item, return found; } } - else - item->can_be_depended= TRUE; if (db && lower_case_table_names) { diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5ed7a67da61..e542b08f911 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4904,6 +4904,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, KEY_FIELD *key_fields, *end, *field; uint sz; uint m= max(select_lex->max_equal_elems,1); + DBUG_ENTER("update_ref_and_keys"); + DBUG_PRINT("enter", ("normal_tables: %llx", normal_tables)); SELECT_LEX *sel=thd->lex->current_select; sel->cond_count= 0; @@ -4950,7 +4952,7 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, sz= max(sizeof(KEY_FIELD),sizeof(SARGABLE_PARAM))* ((sel->cond_count*2 + sel->between_count)*m+1); if (!(key_fields=(KEY_FIELD*) thd->alloc(sz))) - return TRUE; /* purecov: inspected */ + DBUG_RETURN(TRUE); /* purecov: inspected */ and_level= 0; field= end= key_fields; *sargables= (SARGABLE_PARAM *) key_fields + @@ -4959,7 +4961,7 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, (*sargables)[0].field= 0; if (my_init_dynamic_array(keyuse,sizeof(KEYUSE),20,64)) - return TRUE; + DBUG_RETURN(TRUE); if (cond) { @@ -5009,16 +5011,16 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, for ( ; field != end ; field++) { if (add_key_part(keyuse,field)) - return TRUE; + DBUG_RETURN(TRUE); } if (select_lex->ftfunc_list->elements) { if (add_ft_keys(keyuse,join_tab,cond,normal_tables)) - return TRUE; + DBUG_RETURN(TRUE); } - return FALSE; + DBUG_RETURN(FALSE); } |