From 709a0a131021135e9fb7a2095fcfcbc223dfb126 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 26 May 2010 13:18:18 -0700 Subject: MWL#106: Backport optimizations for derived tables and views. The main consolidated patch. --- mysql-test/r/derived.result | 45 ++++++++++++++++++++------------------------- 1 file changed, 20 insertions(+), 25 deletions(-) (limited to 'mysql-test/r/derived.result') diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 80f04ffd455..00e1a376261 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -57,9 +57,8 @@ a b a b 3 c 3 c explain select * from t1 as x1, (select * from t1) as x2; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY x1 ALL NULL NULL NULL NULL 4 -1 PRIMARY ALL NULL NULL NULL NULL 4 Using join buffer -2 DERIVED t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE x1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using join buffer drop table if exists t2,t3; select * from (select 1) as a; 1 @@ -91,7 +90,7 @@ a b 2 b explain select * from (select * from t1 union select * from t1) a; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL NULL NULL NULL NULL 3 +1 PRIMARY ALL NULL NULL NULL NULL 8 2 DERIVED t1 ALL NULL NULL NULL NULL 4 3 UNION t1 ALL NULL NULL NULL NULL 4 NULL UNION RESULT ALL NULL NULL NULL NULL NULL @@ -113,9 +112,8 @@ a b 3 c explain select * from (select t1.*, t2.a as t2a from t1,t2 where t1.a=t2.a) t1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY system NULL NULL NULL NULL 1 -2 DERIVED t2 system NULL NULL NULL NULL 1 -2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using where +1 SIMPLE t2 system NULL NULL NULL NULL 1 +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where drop table t1, t2; create table t1(a int not null, t char(8), index(a)); SELECT * FROM (SELECT * FROM t1) as b ORDER BY a ASC LIMIT 0,20; @@ -142,8 +140,7 @@ a t 20 20 explain select count(*) from t1 as tt1, (select * from t1) as tt2; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away -2 DERIVED t1 ALL NULL NULL NULL NULL 10000 +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away drop table t1; SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b; (SELECT * FROM (SELECT 1 as a) as a ) @@ -172,30 +169,30 @@ insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd' insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105); SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; pla_id mat_id -100 1 -101 1 102 1 -103 2 +101 1 +100 1 104 2 +103 2 105 3 SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; pla_id test -100 1 -101 1 102 1 -103 2 +101 1 +100 1 104 2 +103 2 105 3 explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY m2 ALL NULL NULL NULL NULL 9 -1 PRIMARY ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 PRIMARY ref key0 key0 7 test.m2.matintnum 2 Using where 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY m2 ALL NULL NULL NULL NULL 9 -1 PRIMARY ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 PRIMARY ref key0 key0 7 test.m2.matintnum 2 Using where 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 drop table t1,t2; @@ -230,9 +227,8 @@ count(*) 2 explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL NULL NULL NULL NULL 2 -1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 THEMAX.E2 1 Using where -2 DERIVED A ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE A ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.A.E2 1 Using where 3 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 2 Using where drop table t1; create table t1 (a int); @@ -245,8 +241,8 @@ a a 2 2 explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL NULL NULL NULL NULL 2 -1 PRIMARY ALL NULL NULL NULL NULL 2 Using join buffer +1 PRIMARY ALL NULL NULL NULL NULL 4 +1 PRIMARY ALL NULL NULL NULL NULL 4 Using join buffer 4 DERIVED t1 ALL NULL NULL NULL NULL 2 5 UNION t1 ALL NULL NULL NULL NULL 2 NULL UNION RESULT ALL NULL NULL NULL NULL NULL @@ -311,7 +307,7 @@ a 7.0000 b 3.5000 explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY ALL NULL NULL NULL NULL 3 Using temporary; Using filesort +1 PRIMARY ALL NULL NULL NULL NULL 289 Using temporary; Using filesort 2 DERIVED x ALL NULL NULL NULL NULL 17 Using temporary; Using filesort 2 DERIVED y ALL NULL NULL NULL NULL 17 Using where; Using join buffer drop table t1; @@ -322,8 +318,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index explain select a from (select a from t2 where a>1) tt; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY system NULL NULL NULL NULL 1 -2 DERIVED t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index +1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index drop table t2; CREATE TABLE `t1` ( `itemid` int(11) NOT NULL default '0', `grpid` varchar(15) NOT NULL default '', `vendor` int(11) NOT NULL default '0', `date_` date NOT NULL default '0000-00-00', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`)); insert into t1 values (128, 'rozn', 2, curdate(), 10), -- cgit v1.2.1 From 25f5debdc71105820aceaac25799eb3fc09479cd Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 18 Oct 2010 13:33:05 -0700 Subject: MWL#128: Added into EXPLAIN output info about types of the used join buffers and about the employed join algorithms. Refactored constructors of the JOIN_CACHE* classes. --- mysql-test/r/derived.result | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) (limited to 'mysql-test/r/derived.result') diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 80f04ffd455..e707f01edbf 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -58,7 +58,7 @@ a b a b explain select * from t1 as x1, (select * from t1) as x2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY x1 ALL NULL NULL NULL NULL 4 -1 PRIMARY ALL NULL NULL NULL NULL 4 Using join buffer +1 PRIMARY ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 2 DERIVED t1 ALL NULL NULL NULL NULL 4 drop table if exists t2,t3; select * from (select 1) as a; @@ -189,13 +189,13 @@ pla_id test explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY m2 ALL NULL NULL NULL NULL 9 -1 PRIMARY ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 PRIMARY ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY m2 ALL NULL NULL NULL NULL 9 -1 PRIMARY ALL NULL NULL NULL NULL 6 Using where; Using join buffer +1 PRIMARY ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 drop table t1,t2; @@ -246,7 +246,7 @@ a a explain select * from ( select * from t1 union select * from t1) a,(select * from t1 union select * from t1) b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ALL NULL NULL NULL NULL 2 -1 PRIMARY ALL NULL NULL NULL NULL 2 Using join buffer +1 PRIMARY ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) 4 DERIVED t1 ALL NULL NULL NULL NULL 2 5 UNION t1 ALL NULL NULL NULL NULL 2 NULL UNION RESULT ALL NULL NULL NULL NULL NULL @@ -313,7 +313,7 @@ explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ALL NULL NULL NULL NULL 3 Using temporary; Using filesort 2 DERIVED x ALL NULL NULL NULL NULL 17 Using temporary; Using filesort -2 DERIVED y ALL NULL NULL NULL NULL 17 Using where; Using join buffer +2 DERIVED y ALL NULL NULL NULL NULL 17 Using where; Using join buffer (flat, BNL join) drop table t1; create table t2 (a int, b int, primary key (a)); insert into t2 values (1,7),(2,7); -- cgit v1.2.1 From db0c3406011d9a6d6fdb98c1c1f7925d243bd1f9 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 6 Jun 2011 12:19:35 -0700 Subject: Fixed LP bug #793436. When looking for the execution plan of a derived table to be materialized JOIN::optimize finds out that all joined tables of the derived table contain not more than one row then the derived table should be maretialized at the optimization stage. Added a test case for the bug. Adjusted results in other test cases. --- mysql-test/r/derived.result | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) (limited to 'mysql-test/r/derived.result') diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 7cc2af3616f..fe803ed37a5 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -407,3 +407,21 @@ MIN(i) 1 DROP TABLE t1; # End of 5.0 tests +# +# LP bug #793436: query with a derived table for which optimizer proves +# that it contains not more than 1 row +# +CREATE TABLE t1 (a int, KEY (a)) ; +INSERT INTO t1 VALUES (3), (1); +CREATE TABLE t2 (a int); +INSERT INTO t2 VALUES (3); +EXPLAIN +SELECT * FROM (SELECT DISTINCT * FROM t2) t, t1 WHERE t1.a = t.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY system NULL NULL NULL NULL 1 +1 PRIMARY t1 ref a a 5 const 1 Using index +2 DERIVED t2 system NULL NULL NULL NULL 1 +SELECT * FROM (SELECT DISTINCT * FROM t2) t, t1 WHERE t1.a = t.a; +a a +3 3 +DROP TABLE t1,t2; -- cgit v1.2.1 From 3cf0d6f446b51b76a53378a11a117a134158f407 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 23 Jun 2011 14:48:45 -0700 Subject: Fixed LP bug #800518. The function simple_pred did not take into account that a multiple equality could include ref items (more exactly items of the class Item_direct_view_ref). It caused crashes for queries over derived tables or views if the min/max optimization could be applied to these queries. --- mysql-test/r/derived.result | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) (limited to 'mysql-test/r/derived.result') diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index fe803ed37a5..0340ebcbe98 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -425,3 +425,19 @@ SELECT * FROM (SELECT DISTINCT * FROM t2) t, t1 WHERE t1.a = t.a; a a 3 3 DROP TABLE t1,t2; +# +# LP bug #800518: crash with a query over a derived table +# when a min/max optimization is applied +# +CREATE TABLE t1 (a int, b int, c varchar(10), INDEX idx(a,b)) ; +INSERT INTO t1 VALUES +(100, 3, 'xxx'), (200, 7, 'yyyyyyy'), (100, 1, 't'), +(200, 4, 'aaaa'), (100, 3, 'eee'), (100, 5, 'zzzzz'); +EXPLAIN +SELECT MAX(b) FROM (SELECT * FROM t1) AS t WHERE a = 100; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +SELECT MAX(b) FROM (SELECT * FROM t1) AS t WHERE a = 100; +MAX(b) +5 +DROP TABLE t1; -- cgit v1.2.1 From 37bac085da110a42b1d7dc3ced5e555070cb8c36 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 24 Jun 2011 18:42:14 -0700 Subject: Fixed LP bug #799499. The following were missing in the patch for mwl106: - KEY_PART_INFO::fieldnr were not set for generated keys to access tmp tables storing the rows of materialized derived tables/views - TABLE_SHARE::column_bitmap_size was not set for tmp tables storing the rows of materialized derived tables/views. These could cause crashes or memory overwrite. --- mysql-test/r/derived.result | 23 +++++++++++++++++++++++ 1 file changed, 23 insertions(+) (limited to 'mysql-test/r/derived.result') diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 0340ebcbe98..4805616841c 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -441,3 +441,26 @@ SELECT MAX(b) FROM (SELECT * FROM t1) AS t WHERE a = 100; MAX(b) 5 DROP TABLE t1; +# +# LP bug #799499: query over a materialized view +# accessed by a key +# +CREATE TABLE t1 (a int) ; +INSERT INTO t1 VALUES (8); +CREATE TABLE t2 (a int, b int) ; +INSERT INTO t2 VALUES +(262, NULL), (253, 190), (260, NULL), (250, 163), (188, 8), +(257,200), (256, NULL), (255, 8), (249, NULL), (259, 7); +CREATE VIEW v1 AS SELECT a, MIN(b) AS b FROM t2 GROUP BY a; +EXPLAIN +SELECT * FROM v1, t1 WHERE v1.b=t1.a ORDER BY v1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 Using filesort +1 PRIMARY ref key0 key0 5 const 1 Using where +2 DERIVED t2 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort +SELECT * FROM v1, t1 WHERE v1.b=t1.a ORDER BY v1.a; +a b a +188 8 8 +255 8 8 +DROP VIEW v1; +DROP TABLE t1,t2; -- cgit v1.2.1 From c68020210fa7f9ab41ba35108a109640f0b50eb5 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 24 Jun 2011 21:18:20 -0700 Subject: Added test cases for LP bug #798625 and LP bug #800085 fixed by the patch for LP bug 798621. --- mysql-test/r/derived.result | 13 +++++++++++++ 1 file changed, 13 insertions(+) (limited to 'mysql-test/r/derived.result') diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 4805616841c..5ea398a6c09 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -464,3 +464,16 @@ a b a 255 8 8 DROP VIEW v1; DROP TABLE t1,t2; +# +# LP bug #800085: crash with a query using a simple derived table +# (fixed by the patch for bug 798621) +# +CREATE TABLE t1 (f1 int, f2 varchar(32)) ; +INSERT INTO t1 VALUES (NULL,'j'), (8,'c'); +CREATE TABLE t2 (f1 int); +INSERT INTO t2 VALUES (1), (5); +SELECT DISTINCT t.f1 FROM (SELECT * FROM t1) AS t, t2 +WHERE t.f2='s' AND t.f2 LIKE '%a%' OR t.f1<>0 ORDER BY t.f2; +f1 +8 +DROP TABLE t1, t2; -- cgit v1.2.1 From 63abf00a62313107884f0b304d2c53de73f4eacd Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 21 Jul 2011 14:23:08 -0700 Subject: Made the optimizer switches 'derived_merge' and 'derived_with_keys' off by default. --- mysql-test/r/derived.result | 108 +++++++++----------------------------------- 1 file changed, 22 insertions(+), 86 deletions(-) (limited to 'mysql-test/r/derived.result') diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 5ea398a6c09..b1225c31575 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -57,8 +57,9 @@ a b a b 3 c 3 c explain select * from t1 as x1, (select * from t1) as x2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE x1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) +1 PRIMARY x1 ALL NULL NULL NULL NULL 4 +1 PRIMARY ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) +2 DERIVED t1 ALL NULL NULL NULL NULL 4 drop table if exists t2,t3; select * from (select 1) as a; 1 @@ -112,8 +113,9 @@ a b 3 c explain select * from (select t1.*, t2.a as t2a from t1,t2 where t1.a=t2.a) t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 system NULL NULL NULL NULL 1 -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY ALL NULL NULL NULL NULL 4 +2 DERIVED t2 system NULL NULL NULL NULL 1 +2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using where drop table t1, t2; create table t1(a int not null, t char(8), index(a)); SELECT * FROM (SELECT * FROM t1) as b ORDER BY a ASC LIMIT 0,20; @@ -140,7 +142,9 @@ a t 20 20 explain select count(*) from t1 as tt1, (select * from t1) as tt2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +1 PRIMARY tt1 index NULL a 4 NULL 10000 Using index +1 PRIMARY ALL NULL NULL NULL NULL 10000 Using join buffer (flat, BNL join) +2 DERIVED t1 ALL NULL NULL NULL NULL 10000 drop table t1; SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b; (SELECT * FROM (SELECT 1 as a) as a ) @@ -169,30 +173,30 @@ insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd' insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105); SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; pla_id mat_id -102 1 -101 1 100 1 -104 2 +101 1 +102 1 103 2 +104 2 105 3 SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; pla_id test -102 1 -101 1 100 1 -104 2 +101 1 +102 1 103 2 +104 2 105 3 explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY m2 ALL NULL NULL NULL NULL 9 -1 PRIMARY ref key0 key0 7 test.m2.matintnum 2 +1 PRIMARY ALL NULL $hj 7 test.m2.matintnum 9 Using where 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY m2 ALL NULL NULL NULL NULL 9 -1 PRIMARY ref key0 key0 7 test.m2.matintnum 2 +1 PRIMARY ALL NULL $hj 7 test.m2.matintnum 9 Using where 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 drop table t1,t2; @@ -227,8 +231,9 @@ count(*) 2 explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE A ALL NULL NULL NULL NULL 2 Using where -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.A.E2 1 Using where +1 PRIMARY ALL NULL NULL NULL NULL 2 +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 THEMAX.E2 1 Using where +2 DERIVED A ALL NULL NULL NULL NULL 2 Using where 3 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 2 Using where drop table t1; create table t1 (a int); @@ -318,7 +323,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index explain select a from (select a from t2 where a>1) tt; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index +1 PRIMARY ALL NULL NULL NULL NULL 2 +2 DERIVED t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index drop table t2; CREATE TABLE `t1` ( `itemid` int(11) NOT NULL default '0', `grpid` varchar(15) NOT NULL default '', `vendor` int(11) NOT NULL default '0', `date_` date NOT NULL default '0000-00-00', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`)); insert into t1 values (128, 'rozn', 2, curdate(), 10), @@ -407,73 +413,3 @@ MIN(i) 1 DROP TABLE t1; # End of 5.0 tests -# -# LP bug #793436: query with a derived table for which optimizer proves -# that it contains not more than 1 row -# -CREATE TABLE t1 (a int, KEY (a)) ; -INSERT INTO t1 VALUES (3), (1); -CREATE TABLE t2 (a int); -INSERT INTO t2 VALUES (3); -EXPLAIN -SELECT * FROM (SELECT DISTINCT * FROM t2) t, t1 WHERE t1.a = t.a; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY system NULL NULL NULL NULL 1 -1 PRIMARY t1 ref a a 5 const 1 Using index -2 DERIVED t2 system NULL NULL NULL NULL 1 -SELECT * FROM (SELECT DISTINCT * FROM t2) t, t1 WHERE t1.a = t.a; -a a -3 3 -DROP TABLE t1,t2; -# -# LP bug #800518: crash with a query over a derived table -# when a min/max optimization is applied -# -CREATE TABLE t1 (a int, b int, c varchar(10), INDEX idx(a,b)) ; -INSERT INTO t1 VALUES -(100, 3, 'xxx'), (200, 7, 'yyyyyyy'), (100, 1, 't'), -(200, 4, 'aaaa'), (100, 3, 'eee'), (100, 5, 'zzzzz'); -EXPLAIN -SELECT MAX(b) FROM (SELECT * FROM t1) AS t WHERE a = 100; -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away -SELECT MAX(b) FROM (SELECT * FROM t1) AS t WHERE a = 100; -MAX(b) -5 -DROP TABLE t1; -# -# LP bug #799499: query over a materialized view -# accessed by a key -# -CREATE TABLE t1 (a int) ; -INSERT INTO t1 VALUES (8); -CREATE TABLE t2 (a int, b int) ; -INSERT INTO t2 VALUES -(262, NULL), (253, 190), (260, NULL), (250, 163), (188, 8), -(257,200), (256, NULL), (255, 8), (249, NULL), (259, 7); -CREATE VIEW v1 AS SELECT a, MIN(b) AS b FROM t2 GROUP BY a; -EXPLAIN -SELECT * FROM v1, t1 WHERE v1.b=t1.a ORDER BY v1.a; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 Using filesort -1 PRIMARY ref key0 key0 5 const 1 Using where -2 DERIVED t2 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort -SELECT * FROM v1, t1 WHERE v1.b=t1.a ORDER BY v1.a; -a b a -188 8 8 -255 8 8 -DROP VIEW v1; -DROP TABLE t1,t2; -# -# LP bug #800085: crash with a query using a simple derived table -# (fixed by the patch for bug 798621) -# -CREATE TABLE t1 (f1 int, f2 varchar(32)) ; -INSERT INTO t1 VALUES (NULL,'j'), (8,'c'); -CREATE TABLE t2 (f1 int); -INSERT INTO t2 VALUES (1), (5); -SELECT DISTINCT t.f1 FROM (SELECT * FROM t1) AS t, t2 -WHERE t.f2='s' AND t.f2 LIKE '%a%' OR t.f1<>0 ORDER BY t.f2; -f1 -8 -DROP TABLE t1, t2; -- cgit v1.2.1