summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorIgor Babaev <igor@askmonty.org>2013-03-28 19:18:36 -0700
committerIgor Babaev <igor@askmonty.org>2013-03-28 19:18:36 -0700
commite91e8c8c0b997f05d634fb7187731d0dd8082142 (patch)
tree60c5d34deb703538ebd6ed5f8456e3025160917e
parenta2c3d7d371b51f0a41dd1bbf834f033f62db18b3 (diff)
parentd4de82d93e58a74172960853b07c717377f57e6b (diff)
downloadmariadb-git-e91e8c8c0b997f05d634fb7187731d0dd8082142.tar.gz
Merge 5.3->5.5.
-rw-r--r--mysql-test/r/join_outer_innodb.result292
-rw-r--r--mysql-test/t/join_outer_innodb.test207
-rw-r--r--sql/sql_select.cc3
3 files changed, 498 insertions, 4 deletions
diff --git a/mysql-test/r/join_outer_innodb.result b/mysql-test/r/join_outer_innodb.result
index 650946d8b06..0184e236d14 100644
--- a/mysql-test/r/join_outer_innodb.result
+++ b/mysql-test/r/join_outer_innodb.result
@@ -183,6 +183,296 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
1 SIMPLE t3 ALL NULL NULL NULL NULL 1 Using join buffer (incremental, BNL join)
1 SIMPLE t5 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
-1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
1 SIMPLE t6b ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
+1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join)
drop table t1,t2,t3,t4,t5,t6;
+#
+Bug mdev-4318: view over a complex query with outer joins
+#
+CREATE TABLE t1 (
+a1 int NOT NULL, a2 int NOT NULL, a3 int DEFAULT NULL, a4 tinyint NOT NULL,
+a5 int NOT NULL, a6 tinyint NOT NULL, a7 tinyint(4) DEFAULT NULL,
+a8 smallint(6) DEFAULT NULL, a9 smallint(6) DEFAULT NULL, a10 tinyint NOT NULL,
+PRIMARY KEY (a1), KEY a2 (a2), KEY a3 (a3), KEY a4 (a4), KEY a6 (a6),
+KEY a5 (a5), KEY a7 (a7), KEY a8 (a8), KEY a9 (a9)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t1 VALUES
+(3360,5684,2219,1,316832,1,0,NULL,NULL,NULL),
+(3362,2754,597,2,316844,1,0,NULL,NULL,NULL),
+(3363,369,NULL,1,317295,1,0,NULL,NULL,NULL);
+Warnings:
+Warning 1048 Column 'a10' cannot be null
+Warning 1048 Column 'a10' cannot be null
+Warning 1048 Column 'a10' cannot be null
+CREATE TABLE t2 (
+b1 int NOT NULL, b2 int NOT NULL, PRIMARY KEY (b1,b2), KEY b2 (b2)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+CREATE TABLE t3 (
+c1 int NOT NULL, PRIMARY KEY (c1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t3 VALUES
+(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
+(1000),(1001),(1002),(1003),(9999);
+CREATE TABLE t4 (
+d1 int NOT NULL, PRIMARY KEY (d1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t4 VALUES (5674),(5676),(5680),(5684),(5685);
+CREATE TABLE t5 (
+e1 int NOT NULL, e2 varchar(64) NOT NULL, PRIMARY KEY (e1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t5 VALUES
+(5684,'51a5de7a9f56314e082094d78f58be082c3cf0c1'),
+(5685,'754dc8292cb9f5eb9ade126fe7e961c62412a349'),
+(5686,'75eeb33f1c819bac21f6d023b4c5b24185eeda5c');
+CREATE TABLE t6 (
+f1 int NOT NULL, PRIMARY KEY (f1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t6 VALUES (5542),(5620),(5686);
+CREATE TABLE t7 (
+g1 tinyint NOT NULL DEFAULT '0', g2 varchar(20) NOT NULL, PRIMARY KEY (g1)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t7 VALUES
+(1,'60feec2b20ed19f55ad0'),(3,'9ddb18bff7fcbd1e3133'),
+(5,'a05599df9222bb160d11'),(7,'e31bae372f7d01df0589'),
+(9,'8f8372dd7fc8eb46c8a3'),(11,'f8d0e28529e990a09309');
+CREATE TABLE t8 (
+h1 tinyint NOT NULL, h2 varchar(128) DEFAULT NULL, PRIMARY KEY (h1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t8 VALUES (1,'b'),(2,'c'),(3,'d'),(4,'e');
+CREATE TABLE t9 (
+i1 tinyint NOT NULL, i2 varchar(7) NOT NULL, i3 varchar(128) NOT NULL,
+PRIMARY KEY (i1,i2), KEY i2 (i2), KEY i3 (i3)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t9 VALUES
+(2,'a','07630d223c7e5f7b1feb19b3caafb0833fd028eb'),
+(3,'b','1ca53dcc50b68af86f4b1b4676dbed917b543c30'),
+(1,'b','2c01ac36c1ce9a7de66be89f85d8aa5f0052e2e8'),
+(4,'a','496c486b3a9edc439477fef7d34cbefdebba86df'),
+(3,'a','98bf72d8d467201058a5f69bd7709bfc74a8637e'),
+(2,'b','9a45425f6160fb59d7f8a02c721498d4ce945302'),
+(4,'b','9c9a7300f3e708f8e430f9f3376d966f5951f583'),
+(1,'a','c0af3f076b905f31cbb51af304b9c7ad539e0861');
+CREATE TABLE t10 (
+j1 tinyint NOT NULL, j2 varchar(20) NOT NULL, PRIMARY KEY (j1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t10 VALUES (1,'b'),(2,'c'),(3,'d');
+CREATE TABLE t11 (
+k1 int NOT NULL, k2 datetime DEFAULT NULL, k3 int DEFAULT NULL,
+k4 int DEFAULT NULL, PRIMARY KEY (k1), KEY k3 (k3), KEY k4 (k4)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t11 VALUES
+(317422,'2013-03-18 11:43:03',1,NULL),(317423,'2013-03-18 11:43:11',1,NULL),
+(317424,'2013-03-18 11:52:01',1,1),(317425,'2013-03-18 11:52:01',1,1),
+(317426,'2013-03-18 11:56:38',1,1),(317427,'2013-03-18 12:18:25',1,NULL),
+(317428,'2013-03-18 12:46:28',1,NULL),(317429,'2013-03-18 12:46:28',1,NULL),
+(317430,'2013-03-18 12:46:28',1,NULL),(317431,'2013-03-18 12:46:28',1,NULL),
+(317432,'2013-03-18 12:46:28',1,NULL),(317433,'2013-03-18 12:46:28',1,NULL),
+(317434,'2013-03-18 12:46:28',1,NULL),(317435,'2013-03-18 12:46:28',1,NULL),
+(317436,'2013-03-18 12:46:28',1,NULL),(317437,'2013-03-18 12:46:28',1,NULL),
+(317438,'2013-03-18 12:46:28',1,NULL),(317439,'2013-03-18 12:46:28',1,NULL),
+(317440,'2013-03-18 12:55:20',1,NULL),(317441,'2013-03-18 12:58:29',1,NULL),
+(317442,'2013-03-18 13:06:02',1,NULL),(317443,'2013-03-18 15:23:18',21,NULL);
+CREATE TABLE t12 (
+l1 int NOT NULL, l2 varchar(64) NOT NULL, PRIMARY KEY (l1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t12 VALUES
+(552,'59a498252ef59f96fbdc13a414abe244d8e8bc30'),
+(554,'c6025c7cb2d9dfb1be7ce4a61f35b45bb9e61ba3'),
+(555,'b245bcc672082bb6d10794b2b4ac972dd14b1cf5');
+CREATE TABLE t13 (
+m1 int NOT NULL, m2 int NOT NULL, m3 int NOT NULL,
+PRIMARY KEY (m1,m2,m3), KEY m3 (m3), KEY m2 (m2)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t13 VALUES (3324,43,4),(3332,263,1),(3348,27,3);
+CREATE TABLE t14 (
+n1 smallint NOT NULL, n2 varchar(64) NOT NULL, PRIMARY KEY (n1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t14 VALUES
+(21,'685bf7ca576af964c7cff564d5e4473b81499b8b'),
+(23,'b8e42dab1ab952406b3accfb47089c61478138a8'),
+(25,'3fea441e411db8c70bf039b50c8f18f59515be53'),
+(27,'998aecc30fd0e0b8a1cac6590e5eccc2d7822223');
+CREATE TABLE t15 (
+o1 smallint NOT NULL, PRIMARY KEY (o1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t15 VALUES (1),(3);
+CREATE TABLE t16 (
+p1 smallint NOT NULL, p2 varchar(7) NOT NULL, p3 varchar(64) NOT NULL,
+PRIMARY KEY (p1,p2)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t16 VALUES
+(1,'a','66bdbb389456f3ae97206da115a7b397c31400e8'),
+(1,'b','66bdbb389456f3ae97206da115a7b397c31400e8'),
+(3,'a','386c10e454278c6e27feb16258089166422f79b4'),
+(3,'b','386c10e454278c6e27feb16258089166422f79b4');
+create view v1 as select t3.c1,t5.e2,t1.a1,t14.n2,t16.p3,t10.j2,t7.g2,t11.k2,l3.l2,
+t9.i3,t12.l2 AS l_l2,l2.l2 AS l2_l2,l4.l1 AS l4_l1,t6.f1
+from
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+t1
+left join t2 on t1.a1 = t2.b1
+)
+left join t3 on t2.b2 = t3.c1
+)
+left join t4 on t1.a2 = t4.d1
+)
+left join t5 on t4.d1 = t5.e1
+)
+left join t6 on t1.a3 = t6.f1
+)
+left join t5 e2 on t6.f1 = e2.e1
+)
+join t7 on t1.a7 = t7.g1
+)
+join t8 on t1.a4 = t8.h1
+)
+join t9 on t8.h1 = t9.i1
+)
+join t10 on t1.a6 = t10.j1
+)
+join t11 on t1.a5 = t11.k1
+)
+left join t12 on t11.k3 = t12.l1
+)
+left join t12 l2 on t11.k4 = l2.l1
+)
+left join t13 on t1.a1 = t13.m1 and t13.m3 = 4
+)
+left join t12 l4 on l4.l1 = t13.m2
+)
+left join t13 m2 on t1.a1 = m2.m1 and m2.m3 = 3
+)
+left join t12 l3 on l3.l1 = m2.m2
+)
+left join t14 on t1.a8 = t14.n1
+)
+left join t15 on t1.a9 = t15.o1
+)
+left join t16 on t15.o1 = t16.p1
+where t1.a10 = 1;
+explain select t3.c1,t5.e2,t1.a1,t14.n2,t16.p3,t10.j2,t7.g2,t11.k2,l3.l2,
+t9.i3,t12.l2 AS l_l2,l2.l2 AS l2_l2,l4.l1 AS l4_l1,t6.f1
+from
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+(
+t1
+left join t2 on t1.a1 = t2.b1
+)
+left join t3 on t2.b2 = t3.c1
+)
+left join t4 on t1.a2 = t4.d1
+)
+left join t5 on t4.d1 = t5.e1
+)
+left join t6 on t1.a3 = t6.f1
+)
+left join t5 e2 on t6.f1 = e2.e1
+)
+join t7 on t1.a7 = t7.g1
+)
+join t8 on t1.a4 = t8.h1
+)
+join t9 on t8.h1 = t9.i1
+)
+join t10 on t1.a6 = t10.j1
+)
+join t11 on t1.a5 = t11.k1
+)
+left join t12 on t11.k3 = t12.l1
+)
+left join t12 l2 on t11.k4 = l2.l1
+)
+left join t13 on t1.a1 = t13.m1 and t13.m3 = 4
+)
+left join t12 l4 on l4.l1 = t13.m2
+)
+left join t13 m2 on t1.a1 = m2.m1 and m2.m3 = 3
+)
+left join t12 l3 on l3.l1 = m2.m2
+)
+left join t14 on t1.a8 = t14.n1
+)
+left join t15 on t1.a9 = t15.o1
+)
+left join t16 on t15.o1 = t16.p1
+where t1.a10 = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL a4,a6,a5,a7 NULL NULL NULL 3 Using where
+1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b2 1 Using where; Using index
+1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.a2 1 Using index
+1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.d1 1 Using where
+1 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index
+1 SIMPLE t8 eq_ref PRIMARY PRIMARY 1 test.t1.a4 1 Using index
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1
+1 SIMPLE t9 ref PRIMARY PRIMARY 1 test.t1.a4 1
+1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1
+1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where
+1 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where
+1 SIMPLE t13 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index
+1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index
+1 SIMPLE m2 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index
+1 SIMPLE l3 eq_ref PRIMARY PRIMARY 4 test.m2.m2 1 Using where
+1 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where
+1 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index
+1 SIMPLE t16 ref PRIMARY PRIMARY 2 test.t15.o1 1 Using where
+1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
+explain select * from v1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL a4,a6,a5,a7 NULL NULL NULL 3 Using where
+1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 Using index
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.b2 1 Using where; Using index
+1 SIMPLE t4 eq_ref PRIMARY PRIMARY 4 test.t1.a2 1 Using index
+1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.d1 1 Using where
+1 SIMPLE t6 eq_ref PRIMARY PRIMARY 4 test.t1.a3 1 Using where; Using index
+1 SIMPLE t8 eq_ref PRIMARY PRIMARY 1 test.t1.a4 1 Using index
+1 SIMPLE t7 eq_ref PRIMARY PRIMARY 1 test.t1.a7 1
+1 SIMPLE t9 ref PRIMARY PRIMARY 1 test.t1.a4 1
+1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t1.a5 1
+1 SIMPLE t12 eq_ref PRIMARY PRIMARY 4 test.t11.k3 1 Using where
+1 SIMPLE l2 eq_ref PRIMARY PRIMARY 4 test.t11.k4 1 Using where
+1 SIMPLE t13 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index
+1 SIMPLE l4 eq_ref PRIMARY PRIMARY 4 test.t13.m2 1 Using where; Using index
+1 SIMPLE m2 ref PRIMARY,m3 PRIMARY 4 test.t1.a1 1 Using where; Using index
+1 SIMPLE l3 eq_ref PRIMARY PRIMARY 4 test.m2.m2 1 Using where
+1 SIMPLE t14 eq_ref PRIMARY PRIMARY 2 test.t1.a8 1 Using where
+1 SIMPLE t15 eq_ref PRIMARY PRIMARY 2 test.t1.a9 1 Using where; Using index
+1 SIMPLE t16 ref PRIMARY PRIMARY 2 test.t15.o1 1 Using where
+1 SIMPLE t10 ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
+drop view v1;
+drop table t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16;
diff --git a/mysql-test/t/join_outer_innodb.test b/mysql-test/t/join_outer_innodb.test
index cca1dde481d..adb555d5f85 100644
--- a/mysql-test/t/join_outer_innodb.test
+++ b/mysql-test/t/join_outer_innodb.test
@@ -143,3 +143,210 @@ eval SELECT $rest_of_query;
eval EXPLAIN SELECT $rest_of_query;
drop table t1,t2,t3,t4,t5,t6;
+
+--echo #
+--echo Bug mdev-4318: view over a complex query with outer joins
+--echo #
+
+CREATE TABLE t1 (
+ a1 int NOT NULL, a2 int NOT NULL, a3 int DEFAULT NULL, a4 tinyint NOT NULL,
+ a5 int NOT NULL, a6 tinyint NOT NULL, a7 tinyint(4) DEFAULT NULL,
+ a8 smallint(6) DEFAULT NULL, a9 smallint(6) DEFAULT NULL, a10 tinyint NOT NULL,
+ PRIMARY KEY (a1), KEY a2 (a2), KEY a3 (a3), KEY a4 (a4), KEY a6 (a6),
+ KEY a5 (a5), KEY a7 (a7), KEY a8 (a8), KEY a9 (a9)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t1 VALUES
+ (3360,5684,2219,1,316832,1,0,NULL,NULL,NULL),
+ (3362,2754,597,2,316844,1,0,NULL,NULL,NULL),
+ (3363,369,NULL,1,317295,1,0,NULL,NULL,NULL);
+
+CREATE TABLE t2 (
+ b1 int NOT NULL, b2 int NOT NULL, PRIMARY KEY (b1,b2), KEY b2 (b2)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE t3 (
+ c1 int NOT NULL, PRIMARY KEY (c1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t3 VALUES
+ (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
+ (1000),(1001),(1002),(1003),(9999);
+
+CREATE TABLE t4 (
+ d1 int NOT NULL, PRIMARY KEY (d1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t4 VALUES (5674),(5676),(5680),(5684),(5685);
+
+CREATE TABLE t5 (
+ e1 int NOT NULL, e2 varchar(64) NOT NULL, PRIMARY KEY (e1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t5 VALUES
+ (5684,'51a5de7a9f56314e082094d78f58be082c3cf0c1'),
+ (5685,'754dc8292cb9f5eb9ade126fe7e961c62412a349'),
+ (5686,'75eeb33f1c819bac21f6d023b4c5b24185eeda5c');
+
+CREATE TABLE t6 (
+ f1 int NOT NULL, PRIMARY KEY (f1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t6 VALUES (5542),(5620),(5686);
+
+CREATE TABLE t7 (
+ g1 tinyint NOT NULL DEFAULT '0', g2 varchar(20) NOT NULL, PRIMARY KEY (g1)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO t7 VALUES
+ (1,'60feec2b20ed19f55ad0'),(3,'9ddb18bff7fcbd1e3133'),
+ (5,'a05599df9222bb160d11'),(7,'e31bae372f7d01df0589'),
+ (9,'8f8372dd7fc8eb46c8a3'),(11,'f8d0e28529e990a09309');
+
+CREATE TABLE t8 (
+ h1 tinyint NOT NULL, h2 varchar(128) DEFAULT NULL, PRIMARY KEY (h1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t8 VALUES (1,'b'),(2,'c'),(3,'d'),(4,'e');
+
+CREATE TABLE t9 (
+ i1 tinyint NOT NULL, i2 varchar(7) NOT NULL, i3 varchar(128) NOT NULL,
+ PRIMARY KEY (i1,i2), KEY i2 (i2), KEY i3 (i3)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t9 VALUES
+ (2,'a','07630d223c7e5f7b1feb19b3caafb0833fd028eb'),
+ (3,'b','1ca53dcc50b68af86f4b1b4676dbed917b543c30'),
+ (1,'b','2c01ac36c1ce9a7de66be89f85d8aa5f0052e2e8'),
+ (4,'a','496c486b3a9edc439477fef7d34cbefdebba86df'),
+ (3,'a','98bf72d8d467201058a5f69bd7709bfc74a8637e'),
+ (2,'b','9a45425f6160fb59d7f8a02c721498d4ce945302'),
+ (4,'b','9c9a7300f3e708f8e430f9f3376d966f5951f583'),
+ (1,'a','c0af3f076b905f31cbb51af304b9c7ad539e0861');
+
+CREATE TABLE t10 (
+ j1 tinyint NOT NULL, j2 varchar(20) NOT NULL, PRIMARY KEY (j1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t10 VALUES (1,'b'),(2,'c'),(3,'d');
+
+CREATE TABLE t11 (
+ k1 int NOT NULL, k2 datetime DEFAULT NULL, k3 int DEFAULT NULL,
+ k4 int DEFAULT NULL, PRIMARY KEY (k1), KEY k3 (k3), KEY k4 (k4)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t11 VALUES
+ (317422,'2013-03-18 11:43:03',1,NULL),(317423,'2013-03-18 11:43:11',1,NULL),
+ (317424,'2013-03-18 11:52:01',1,1),(317425,'2013-03-18 11:52:01',1,1),
+ (317426,'2013-03-18 11:56:38',1,1),(317427,'2013-03-18 12:18:25',1,NULL),
+ (317428,'2013-03-18 12:46:28',1,NULL),(317429,'2013-03-18 12:46:28',1,NULL),
+ (317430,'2013-03-18 12:46:28',1,NULL),(317431,'2013-03-18 12:46:28',1,NULL),
+ (317432,'2013-03-18 12:46:28',1,NULL),(317433,'2013-03-18 12:46:28',1,NULL),
+ (317434,'2013-03-18 12:46:28',1,NULL),(317435,'2013-03-18 12:46:28',1,NULL),
+ (317436,'2013-03-18 12:46:28',1,NULL),(317437,'2013-03-18 12:46:28',1,NULL),
+ (317438,'2013-03-18 12:46:28',1,NULL),(317439,'2013-03-18 12:46:28',1,NULL),
+ (317440,'2013-03-18 12:55:20',1,NULL),(317441,'2013-03-18 12:58:29',1,NULL),
+ (317442,'2013-03-18 13:06:02',1,NULL),(317443,'2013-03-18 15:23:18',21,NULL);
+
+CREATE TABLE t12 (
+ l1 int NOT NULL, l2 varchar(64) NOT NULL, PRIMARY KEY (l1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t12 VALUES
+ (552,'59a498252ef59f96fbdc13a414abe244d8e8bc30'),
+ (554,'c6025c7cb2d9dfb1be7ce4a61f35b45bb9e61ba3'),
+ (555,'b245bcc672082bb6d10794b2b4ac972dd14b1cf5');
+
+CREATE TABLE t13 (
+ m1 int NOT NULL, m2 int NOT NULL, m3 int NOT NULL,
+ PRIMARY KEY (m1,m2,m3), KEY m3 (m3), KEY m2 (m2)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t13 VALUES (3324,43,4),(3332,263,1),(3348,27,3);
+
+CREATE TABLE t14 (
+ n1 smallint NOT NULL, n2 varchar(64) NOT NULL, PRIMARY KEY (n1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t14 VALUES
+ (21,'685bf7ca576af964c7cff564d5e4473b81499b8b'),
+ (23,'b8e42dab1ab952406b3accfb47089c61478138a8'),
+ (25,'3fea441e411db8c70bf039b50c8f18f59515be53'),
+ (27,'998aecc30fd0e0b8a1cac6590e5eccc2d7822223');
+
+CREATE TABLE t15 (
+ o1 smallint NOT NULL, PRIMARY KEY (o1)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t15 VALUES (1),(3);
+
+CREATE TABLE t16 (
+ p1 smallint NOT NULL, p2 varchar(7) NOT NULL, p3 varchar(64) NOT NULL,
+ PRIMARY KEY (p1,p2)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO t16 VALUES
+ (1,'a','66bdbb389456f3ae97206da115a7b397c31400e8'),
+ (1,'b','66bdbb389456f3ae97206da115a7b397c31400e8'),
+ (3,'a','386c10e454278c6e27feb16258089166422f79b4'),
+ (3,'b','386c10e454278c6e27feb16258089166422f79b4');
+
+let $Q=
+select t3.c1,t5.e2,t1.a1,t14.n2,t16.p3,t10.j2,t7.g2,t11.k2,l3.l2,
+ t9.i3,t12.l2 AS l_l2,l2.l2 AS l2_l2,l4.l1 AS l4_l1,t6.f1
+from
+(
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ (
+ t1
+ left join t2 on t1.a1 = t2.b1
+ )
+ left join t3 on t2.b2 = t3.c1
+ )
+ left join t4 on t1.a2 = t4.d1
+ )
+ left join t5 on t4.d1 = t5.e1
+ )
+ left join t6 on t1.a3 = t6.f1
+ )
+ left join t5 e2 on t6.f1 = e2.e1
+ )
+ join t7 on t1.a7 = t7.g1
+ )
+ join t8 on t1.a4 = t8.h1
+ )
+ join t9 on t8.h1 = t9.i1
+ )
+ join t10 on t1.a6 = t10.j1
+ )
+ join t11 on t1.a5 = t11.k1
+ )
+ left join t12 on t11.k3 = t12.l1
+ )
+ left join t12 l2 on t11.k4 = l2.l1
+ )
+ left join t13 on t1.a1 = t13.m1 and t13.m3 = 4
+ )
+ left join t12 l4 on l4.l1 = t13.m2
+ )
+ left join t13 m2 on t1.a1 = m2.m1 and m2.m3 = 3
+ )
+ left join t12 l3 on l3.l1 = m2.m2
+ )
+ left join t14 on t1.a8 = t14.n1
+ )
+ left join t15 on t1.a9 = t15.o1
+)
+left join t16 on t15.o1 = t16.p1
+where t1.a10 = 1;
+
+eval create view v1 as $Q;
+
+eval explain $Q;
+
+explain select * from v1;
+
+drop view v1;
+
+drop table t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 73759c9a0ce..c84c4156d2a 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -12853,9 +12853,6 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top,
table->prep_on_expr= table->on_expr= 0;
}
}
-
- if (!top)
- continue;
/*
Only inner tables of non-convertible outer joins