[Commits] e1e04c3d681: Correct a merge error.

marko.makela at mariadb.com marko.makela at mariadb.com
Wed Mar 8 14:40:02 EET 2017


revision-id: e1e04c3d681eee64e4b4e219cd89a64a84d43976 (mariadb-10.0.29-66-ge1e04c3d681)
parent(s): fc0a6dd57fa81194648278150546899bff603029
author: Marko Mäkelä
committer: Marko Mäkelä
timestamp: 2017-03-08 14:40:02 +0200
message:

Correct a merge error.

---
 mysql-test/t/subselect_innodb.test | 54 ++++++++++++++++++++++++++++++++++++++
 1 file changed, 54 insertions(+)

diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test
index 384464a9b7a..b26c5036f3f 100644
--- a/mysql-test/t/subselect_innodb.test
+++ b/mysql-test/t/subselect_innodb.test
@@ -501,3 +501,57 @@ SELECT * FROM v1, t2 WHERE ( f1, f2 ) IN ( SELECT f1, f1 FROM t1 );
 set join_cache_level = default;
 drop view v1;
 drop table t1,t2;
+
+--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;
+
+--echo #
+--echo # MDEV-6081: ORDER BY+ref(const): selectivity is very incorrect (MySQL Bug#14338686)
+--echo #
+
+alter table t2 add key2 int;
+update t2 set key2=key1;
+alter table t2 add key(key2);
+analyze table t2;
+flush tables;
+--echo # Table tsubq must use 'ref' + Using filesort (not 'index' w/o filesort)
+--replace_column 9 #
+explain select 
+   (SELECT 
+      concat(id, '-', key1, '-', col1)
+    FROM t2
+    WHERE t2.key1 = t1.a
+    ORDER BY t2.key2 ASC LIMIT 1)
+from 
+  t1;
+
+drop table t1,t2;


More information about the commits mailing list