summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorIgor Babaev <igor@askmonty.org>2017-05-04 22:45:32 -0700
committerIgor Babaev <igor@askmonty.org>2017-05-04 22:45:32 -0700
commit15f9931f6d2b0eb4006fdc42072c2905fd67c1aa (patch)
treefff72f8e5466e551753d589b54243a48c13541e9
parent14fca28ea44dabf62e75e2aa9a90d71bd7be45c0 (diff)
downloadmariadb-git-15f9931f6d2b0eb4006fdc42072c2905fd67c1aa.tar.gz
Fixed the bug mdev-12673.
This patch corrects the fix for the bug mdev-10693. It is critical for the function get_best_combination() not to call create_ref_for_key() for constant tables. This bug could manifest itself only in multi-table subqueries where one of the tables is accessed by a constant primary key.
-rw-r--r--mysql-test/r/subselect_mat_cost_bugs.result42
-rw-r--r--mysql-test/t/subselect_mat_cost_bugs.test39
-rw-r--r--sql/sql_select.cc12
3 files changed, 88 insertions, 5 deletions
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index 24640154c59..9bfd5bd67b5 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/subselect_mat_cost_bugs.result
@@ -400,3 +400,45 @@ id select_type table type possible_keys key key_len ref rows Extra
select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
c1 c2
drop table t1, t2;
+#
+# MDEV-12673: cost-based choice between materialization and in-to-exists
+#
+CREATE TABLE t1 (
+pk1 int, a1 varchar(3), b1 varchar(3), PRIMARY KEY (pk1), KEY(a1), KEY(b1)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'foo','bar'),(2,'bar','foo');
+CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 VARCHAR(3), KEY(a2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'abc'),(2,'xyz'),(3,'foo');
+SELECT 'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 )
+0
+SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 )
+1
+EXPLAIN
+SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
+2 SUBQUERY t2 const PRIMARY,a2 PRIMARY 4 const 1
+2 SUBQUERY t1 ref a1,b1 b1 6 const 1 Using where
+DROP TABLE t1,t2;
+CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (i2 int, c2 varchar(3), KEY(i2,c2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'abc'),(2,'foo');
+CREATE TABLE t3 (pk3 int PRIMARY KEY, c3 varchar(3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1,'foo'),(2,'bar');
+SELECT * FROM t1 WHERE i1 NOT IN (
+SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1
+);
+i1
+1
+EXPLAIN
+SELECT * FROM t1 WHERE i1 NOT IN (
+SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1
+2 DEPENDENT SUBQUERY t3 const PRIMARY PRIMARY 4 const 1
+2 DEPENDENT SUBQUERY t2 index NULL i2 11 NULL 2 Using where; Using index
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test
index 8205e94b203..316ac707bef 100644
--- a/mysql-test/t/subselect_mat_cost_bugs.test
+++ b/mysql-test/t/subselect_mat_cost_bugs.test
@@ -424,3 +424,42 @@ explain select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
select * from t1 where c1 = 'a2' and (c1, c2) not in (select * from t2);
drop table t1, t2;
+
+--echo #
+--echo # MDEV-12673: cost-based choice between materialization and in-to-exists
+--echo #
+
+CREATE TABLE t1 (
+ pk1 int, a1 varchar(3), b1 varchar(3), PRIMARY KEY (pk1), KEY(a1), KEY(b1)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'foo','bar'),(2,'bar','foo');
+
+CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 VARCHAR(3), KEY(a2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'abc'),(2,'xyz'),(3,'foo');
+
+SELECT 'qux' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+EXPLAIN
+SELECT 'bar' IN ( SELECT a1 FROM t1 INNER JOIN t2 WHERE a2 = b1 AND pk2 = 3 );
+
+DROP TABLE t1,t2;
+
+CREATE TABLE t1 (i1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+
+CREATE TABLE t2 (i2 int, c2 varchar(3), KEY(i2,c2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'abc'),(2,'foo');
+
+CREATE TABLE t3 (pk3 int PRIMARY KEY, c3 varchar(3)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1,'foo'),(2,'bar');
+
+SELECT * FROM t1 WHERE i1 NOT IN (
+ SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1
+);
+
+EXPLAIN
+SELECT * FROM t1 WHERE i1 NOT IN (
+ SELECT i2 FROM t2 RIGHT JOIN t3 ON (c3 = c2) WHERE pk3 = i1
+);
+
+DROP TABLE t1,t2,t3;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index c5f2a83fc01..09e6ece7807 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7844,12 +7844,14 @@ get_best_combination(JOIN *join)
{
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->type != JT_CONST && j->type != JT_SYSTEM)
+ {
+ 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;
}