summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorOleksandr Byelkin <sanja@mariadb.com>2017-04-25 16:30:39 +0200
committerOleksandr Byelkin <sanja@mariadb.com>2017-04-25 16:30:39 +0200
commit49552cf1f7adb3f3b7bb53696cc178363e4d9cb2 (patch)
tree1f8fe7b3b30e6d3a1bce69913d144200e8ab272a
parent57fea99eeb80829828d43a048d31c46c30684738 (diff)
parent2e7ba70a94b5950a7d1d733c177d1b2a24916213 (diff)
downloadmariadb-git-bb-10.0-merge-5.5.tar.gz
Merge branch '5.5' into bb-10.0-merge-5.5bb-10.0-merge-5.5
-rw-r--r--mysql-test/r/subselect_innodb.result14
-rw-r--r--mysql-test/t/subselect_innodb.test21
-rw-r--r--sql/sql_select.cc21
3 files changed, 50 insertions, 6 deletions
diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result
index be666547f26..15f1d6377e3 100644
--- a/mysql-test/r/subselect_innodb.result
+++ b/mysql-test/r/subselect_innodb.result
@@ -511,6 +511,20 @@ set join_cache_level = default;
drop view v1;
drop table t1,t2;
#
+# MDEV-10693: cost-based choice between materialization and in-to-exists
+# for a subquery from the expression used in ref access
+#
+CREATE TABLE t1 (i1 INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (i2 INT) ENGINE=InnoDB;
+CREATE TABLE t3 (i3 INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (3);
+SELECT * FROM t1
+WHERE NULL IN ( SELECT i2 FROM t2
+WHERE i1 IN ( i2 IN ( SELECT i3 FROM t3 ) ) AND i2 = 2 );
+i1
+DROP TABLE t1,t2,t3;
+#
# MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding
#
create table t1(a int) engine=innodb;
diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test
index b26c5036f3f..2451bc60fee 100644
--- a/mysql-test/t/subselect_innodb.test
+++ b/mysql-test/t/subselect_innodb.test
@@ -503,6 +503,27 @@ drop view v1;
drop table t1,t2;
--echo #
+--echo # MDEV-10693: cost-based choice between materialization and in-to-exists
+--echo # for a subquery from the expression used in ref access
+--echo #
+
+--source include/have_innodb.inc
+
+CREATE TABLE t1 (i1 INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (i2 INT) ENGINE=InnoDB;
+
+CREATE TABLE t3 (i3 INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (3);
+
+SELECT * FROM t1
+ WHERE NULL IN ( SELECT i2 FROM t2
+ WHERE i1 IN ( i2 IN ( SELECT i3 FROM t3 ) ) AND i2 = 2 );
+
+DROP TABLE t1,t2,t3;
+
+--echo #
--echo # MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding
--echo #
create table t1(a int) engine=innodb;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 1353864a344..8d63902d9eb 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8443,8 +8443,6 @@ get_best_combination(JOIN *join)
join->full_join=0;
join->hash_join= FALSE;
- used_tables= OUTER_REF_TABLE_BIT; // Outer row is already read
-
fix_semijoin_strategies_for_picked_join_order(join);
JOIN_TAB_RANGE *root_range;
@@ -8508,7 +8506,6 @@ get_best_combination(JOIN *join)
j->bush_root_tab= sjm_nest_root;
form=join->table[tablenr]=j->table;
- used_tables|= form->map;
form->reginfo.join_tab=j;
DBUG_PRINT("info",("type: %d", j->type));
if (j->type == JT_CONST)
@@ -8535,9 +8532,6 @@ get_best_combination(JOIN *join)
join->best_positions[tablenr].loosescan_picker.loosescan_key);
j->index= join->best_positions[tablenr].loosescan_picker.loosescan_key;
}*/
-
- if (keyuse && create_ref_for_key(join, j, keyuse, TRUE, used_tables))
- DBUG_RETURN(TRUE); // Something went wrong
if ((j->type == JT_REF || j->type == JT_EQ_REF) &&
is_hash_join_key_no(j->ref.key))
@@ -8563,6 +8557,21 @@ get_best_combination(JOIN *join)
}
root_range->end= j;
+ used_tables= OUTER_REF_TABLE_BIT; // Outer row is already read
+ for (j=join_tab, tablenr=0 ; tablenr < table_count ; tablenr++,j++)
+ {
+ if (j->bush_children)
+ j= j->bush_children->start;
+
+ used_tables|= j->table->map;
+ if ((keyuse= join->best_positions[tablenr].key) &&
+ create_ref_for_key(join, j, keyuse, TRUE, used_tables))
+ DBUG_RETURN(TRUE); // Something went wrong
+
+ if (j->last_leaf_in_bush)
+ j= j->bush_root_tab;
+ }
+
join->top_join_tab_count= join->join_tab_ranges.head()->end -
join->join_tab_ranges.head()->start;
/*