[Commits] Rev 4144: MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding in file:///home/psergey/dev2/10.0/

Sergey Petrunya psergey at askmonty.org
Mon Apr 7 12:49:50 EEST 2014


At file:///home/psergey/dev2/10.0/

------------------------------------------------------------
revno: 4144
revision-id: psergey at askmonty.org-20140407094948-yuhs1nte94zhieew
parent: psergey at askmonty.org-20140402105604-2xxxhm60mxzzlu5m
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 10.0
timestamp: Mon 2014-04-07 13:49:48 +0400
message:
  MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding
  - Make JOIN::const_key_parts include keyparts for which 
    the WHERE clause has an equality in form 
    "t.key_part=reference_outside_this_select"
  - This allows to avoid filesort'ing in some cases (and also 
    avoid a difficult choice between using filesort or using an index)
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2014-02-26 14:28:07 +0000
+++ b/mysql-test/r/subselect.result	2014-04-07 09:49:48 +0000
@@ -4968,7 +4968,7 @@ SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
 1	PRIMARY	t3	ref	b,b_2	b	5	test.t1.a	1	Using index
-2	DEPENDENT SUBQUERY	t2	ref	b,b_2,c	b	10	test.t3.c,test.t1.a	1	Using where; Using index; Using filesort
+2	DEPENDENT SUBQUERY	t2	ref	b,b_2,c	b	10	test.t3.c,test.t1.a	1	Using where; Using index
 SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
 a	incorrect
 1	1

=== modified file 'mysql-test/r/subselect_exists_to_in.result'
--- a/mysql-test/r/subselect_exists_to_in.result	2014-02-26 14:28:07 +0000
+++ b/mysql-test/r/subselect_exists_to_in.result	2014-04-07 09:49:48 +0000
@@ -4974,7 +4974,7 @@ SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
 1	PRIMARY	t3	ref	b,b_2	b	5	test.t1.a	1	Using index
-2	DEPENDENT SUBQUERY	t2	ref	b,b_2,c	b	10	test.t3.c,test.t1.a	1	Using where; Using index; Using filesort
+2	DEPENDENT SUBQUERY	t2	ref	b,b_2,c	b	10	test.t3.c,test.t1.a	1	Using where; Using index
 SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
 a	incorrect
 1	1

=== modified file 'mysql-test/r/subselect_innodb.result'
--- a/mysql-test/r/subselect_innodb.result	2013-09-14 01:09:36 +0000
+++ b/mysql-test/r/subselect_innodb.result	2014-04-07 09:49:48 +0000
@@ -313,7 +313,7 @@ EXPLAIN SELECT 1 FROM t1 WHERE NOT EXIST
 (SELECT 1 FROM t2 WHERE d = (SELECT d FROM t2 WHERE a >= 1) ORDER BY d);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	1	Using where
-2	DEPENDENT SUBQUERY	t2	eq_ref	PRIMARY,d	d	2	func	1	Using where
+2	DEPENDENT SUBQUERY	t2	eq_ref	PRIMARY,d	PRIMARY	1	func	1	Using where
 3	DEPENDENT SUBQUERY	t2	index	NULL	d	2	NULL	1	Using where; Using index
 DROP TABLE t2;
 CREATE TABLE t2 (b INT, c INT, UNIQUE KEY (b), UNIQUE KEY (b, c )) ENGINE=INNODB;
@@ -495,3 +495,35 @@ HAVING SQ2_alias1 . col_int_key >= 7
 1
 drop table t1;
 set optimizer_switch=@subselect_innodb_tmp;
+#
+# MDEV-6041: ORDER BY+subqueries: subquery_table.key=outer_table.col is not recongized as binding
+#
+create table t1(a int) engine=innodb;
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2(
+id int primary key,
+key1 int,
+col1 int,
+key(key1)
+) engine=innodb;
+insert into t2 
+select 
+A.a + B.a*10 + C.a*100 + D.a* 1000, 
+A.a + 10*B.a, 
+123456 
+from t1 A, t1 B, t1 C, t1 D;
+# Table tsubq:
+#   - must use 'ref' (not 'index'), and must not use 'Using filesort'
+#   - shows a bad estimate for 'rows' (but I'm not sure if one can do better w/o histograms)
+explain select 
+(SELECT 
+concat(id, '-', key1, '-', col1)
+FROM t2
+WHERE t2.key1 = t1.a
+ORDER BY t2.id ASC LIMIT 1)
+from 
+t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+2	DEPENDENT SUBQUERY	t2	ref	key1	key1	5	test.t1.a	1	Using where
+drop table t1, t2;

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2014-02-26 14:28:07 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2014-04-07 09:49:48 +0000
@@ -4970,7 +4970,7 @@ SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
 1	PRIMARY	t3	ref	b,b_2	b	5	test.t1.a	1	Using index
-2	DEPENDENT SUBQUERY	t2	ref	b,b_2,c	b	10	test.t3.c,test.t1.a	1	Using where; Using index; Using filesort
+2	DEPENDENT SUBQUERY	t2	ref	b,b_2,c	b	10	test.t3.c,test.t1.a	1	Using where; Using index
 SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
 a	incorrect
 1	1

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2014-02-26 14:28:07 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2014-04-07 09:49:48 +0000
@@ -4966,7 +4966,7 @@ SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
 1	PRIMARY	t3	ref	b,b_2	b	5	test.t1.a	1	Using index
-2	DEPENDENT SUBQUERY	t2	ref	b,b_2,c	b	10	test.t3.c,test.t1.a	1	Using where; Using index; Using filesort
+2	DEPENDENT SUBQUERY	t2	ref	b,b_2,c	b	10	test.t3.c,test.t1.a	1	Using where; Using index
 SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
 a	incorrect
 1	1

=== modified file 'mysql-test/r/subselect_no_scache.result'
--- a/mysql-test/r/subselect_no_scache.result	2014-02-26 14:28:07 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2014-04-07 09:49:48 +0000
@@ -4974,7 +4974,7 @@ SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
 1	PRIMARY	t3	ref	b,b_2	b	5	test.t1.a	1	Using index
-2	DEPENDENT SUBQUERY	t2	ref	b,b_2,c	b	10	test.t3.c,test.t1.a	1	Using where; Using index; Using filesort
+2	DEPENDENT SUBQUERY	t2	ref	b,b_2,c	b	10	test.t3.c,test.t1.a	1	Using where; Using index
 SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
 a	incorrect
 1	1

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2014-02-26 14:28:07 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2014-04-07 09:49:48 +0000
@@ -4966,7 +4966,7 @@ SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
 1	PRIMARY	t3	ref	b,b_2	b	5	test.t1.a	1	Using index
-2	DEPENDENT SUBQUERY	t2	ref	b,b_2,c	b	10	test.t3.c,test.t1.a	1	Using where; Using index; Using filesort
+2	DEPENDENT SUBQUERY	t2	ref	b,b_2,c	b	10	test.t3.c,test.t1.a	1	Using where; Using index
 SELECT t1.a, (SELECT 1 FROM t2 WHERE t2.b=t3.c AND t2.c=t1.a ORDER BY t2.d LIMIT 1) AS incorrect FROM t1, t3 WHERE t3.b=t1.a;
 a	incorrect
 1	1

=== modified file 'mysql-test/t/subselect_innodb.test'
--- a/mysql-test/t/subselect_innodb.test	2013-09-14 01:09:36 +0000
+++ b/mysql-test/t/subselect_innodb.test	2014-04-07 09:49:48 +0000
@@ -481,3 +481,37 @@ drop table t1;
 
 
 set optimizer_switch=@subselect_innodb_tmp;
+
+--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;
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t2(
+  id int primary key,
+  key1 int,
+  col1 int,
+  key(key1)
+) engine=innodb;
+
+insert into t2 
+  select 
+    A.a + B.a*10 + C.a*100 + D.a* 1000, 
+    A.a + 10*B.a, 
+    123456 
+from t1 A, t1 B, t1 C, t1 D;
+
+--echo # Table tsubq:
+--echo #   - must use 'ref' (not 'index'), and must not use 'Using filesort'
+--echo #   - shows a bad estimate for 'rows' (but I'm not sure if one can do better w/o histograms)
+explain select 
+   (SELECT 
+      concat(id, '-', key1, '-', col1)
+    FROM t2
+    WHERE t2.key1 = t1.a
+    ORDER BY t2.id ASC LIMIT 1)
+from 
+  t1;
+drop table t1, t2;
+

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2014-04-01 16:59:51 +0000
+++ b/sql/sql_select.cc	2014-04-07 09:49:48 +0000
@@ -5274,7 +5274,8 @@ static bool sort_and_filter_keyuse(THD *
   {
     if (!use->is_for_hash_join())
     {
-      if (!use->used_tables && use->optimize != KEY_OPTIMIZE_REF_OR_NULL)
+      if (!(use->used_tables & ~OUTER_REF_TABLE_BIT) && 
+          use->optimize != KEY_OPTIMIZE_REF_OR_NULL)
         use->table->const_key_parts[use->key]|= use->keypart_map;
       if (use->keypart != FT_KEYPART)
       {
@@ -14912,7 +14913,7 @@ remove_eq_conds(THD *thd, COND *cond, It
 static bool
 test_if_equality_guarantees_uniqueness(Item *l, Item *r)
 {
-  return r->const_item() &&
+  return (r->const_item() || !(r->used_tables() & ~OUTER_REF_TABLE_BIT)) &&
     item_cmp_type(l->cmp_type(), r->cmp_type()) == l->cmp_type() &&
     (l->cmp_type() != STRING_RESULT ||
      l->collation.collation == r->collation.collation);

=== modified file 'sql/table.h'
--- a/sql/table.h	2014-03-26 21:32:15 +0000
+++ b/sql/table.h	2014-04-07 09:49:48 +0000
@@ -1114,7 +1114,11 @@ struct TABLE
   */
   ha_rows	quick_rows[MAX_KEY];
 
-  /* Bitmaps of key parts that =const for the entire join. */
+  /* 
+    Bitmaps of key parts that =const for the duration of join execution. If
+    we're in a subquery, then the constant may be different across subquery
+    re-executions.
+  */
   key_part_map  const_key_parts[MAX_KEY];
 
   uint		quick_key_parts[MAX_KEY];



More information about the commits mailing list