maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #02864
Rev 2788: MWL#90: Subqueries: Inside-out execution for non-semijoin materialized in file:///home/psergey/dev/maria-5.3-subqueries-r12/
At file:///home/psergey/dev/maria-5.3-subqueries-r12/
------------------------------------------------------------
revno: 2788
revision-id: psergey@xxxxxxxxxxxx-20100405201645-kuavh2rxc9whflc0
parent: psergey@xxxxxxxxxxxx-20100329200940-9ikx6gpww0gtsx00
committer: Sergey Petrunya <psergey@xxxxxxxxxxxx>
branch nick: maria-5.3-subqueries-r12
timestamp: Tue 2010-04-06 00:16:45 +0400
message:
MWL#90: Subqueries: Inside-out execution for non-semijoin materialized
subqueries that are AND-parts of the WHERE
- First code (needs cleanup).
=== modified file 'mysql-test/include/mix1.inc'
--- a/mysql-test/include/mix1.inc 2010-03-20 12:01:47 +0000
+++ b/mysql-test/include/mix1.inc 2010-04-05 20:16:45 +0000
@@ -1522,6 +1522,12 @@
SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
+--echo # MariaDB note:
+--echo # This will show 2 for table which has 5 rows.
+--echo # This is because the access method employed is actually range access
+--echo # which scans 2 records (yes, EXPLAIN displays it incorrectly).
+--echo # our correct printing is an artifact of changing in select_describe()
+--echo # from printing table->starts.records() to tab->records.
EXPLAIN
SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
@@ -1535,6 +1541,7 @@
SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
+--echo # See above "MariaDB note"
EXPLAIN
SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
@@ -1549,6 +1556,7 @@
SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
+--echo # See above "MariaDB note"
EXPLAIN
SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result 2010-03-20 12:01:47 +0000
+++ b/mysql-test/r/group_min_max.result 2010-04-05 20:16:45 +0000
@@ -2255,7 +2255,8 @@
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index
+1 PRIMARY SUBQUERY#2 ALL NULL NULL NULL NULL 8
+1 PRIMARY t1_outer ref a a 5 materialized subselect.max(b) 2 Using index
2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by
EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
=== modified file 'mysql-test/r/having.result'
--- a/mysql-test/r/having.result 2009-01-16 15:38:38 +0000
+++ b/mysql-test/r/having.result 2010-04-05 20:16:45 +0000
@@ -249,11 +249,11 @@
group by t2.col1, t2.col2 having t2.col1 <= 10);
col1
10
+10
+10
20
30
10
-10
-10
select t1.col1 from t1
where t1.col2 in
(select t2.col2 from t2
@@ -262,11 +262,11 @@
(select min(t3.col1) from t3));
col1
10
+10
+10
20
30
10
-10
-10
select t1.col1 from t1
where t1.col2 in
(select t2.col2 from t2
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result 2010-03-20 12:01:47 +0000
+++ b/mysql-test/r/innodb_mysql.result 2010-04-05 20:16:45 +0000
@@ -1727,12 +1727,18 @@
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
1
1
+# MariaDB note:
+# This will show 2 for table which has 5 rows.
+# This is because the access method employed is actually range access
+# which scans 2 records (yes, EXPLAIN displays it incorrectly).
+# our correct printing is an artifact of changing in select_describe()
+# from printing table->starts.records() to tab->records.
EXPLAIN
SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
-2 DERIVED t1 index c3,c2 c2 10 NULL 5
+2 DERIVED t1 index c3,c2 c2 10 NULL 2
DROP TABLE t1;
CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3))
ENGINE=InnoDB;
@@ -1741,12 +1747,13 @@
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
1
1
+# See above "MariaDB note"
EXPLAIN
SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
-2 DERIVED t1 index c3,c2 c2 18 NULL 5
+2 DERIVED t1 index c3,c2 c2 18 NULL 2
DROP TABLE t1;
CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2),
KEY (c3), KEY (c2, c3))
@@ -1756,12 +1763,13 @@
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
1
1
+# See above "MariaDB note"
EXPLAIN
SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
-2 DERIVED t1 index c3,c2 c2 14 NULL 5
+2 DERIVED t1 index c3,c2 c2 14 NULL 2
DROP TABLE t1;
End of 5.1 tests
drop table if exists t1, t2, t3;
=== modified file 'mysql-test/r/partition.result'
--- a/mysql-test/r/partition.result 2010-03-04 08:03:07 +0000
+++ b/mysql-test/r/partition.result 2010-04-05 20:16:45 +0000
@@ -1382,7 +1382,7 @@
2
explain partitions select * from t1 where a is null or a < 0 or a > 1;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pn,p2 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t1 pn,p2 ALL NULL NULL NULL NULL 2 Using where
drop table t1;
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, name VARCHAR(20))
ENGINE=MyISAM DEFAULT CHARSET=latin1
=== modified file 'mysql-test/r/partition_hash.result'
--- a/mysql-test/r/partition_hash.result 2008-07-07 20:42:19 +0000
+++ b/mysql-test/r/partition_hash.result 2010-04-05 20:16:45 +0000
@@ -69,25 +69,25 @@
1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
explain partitions select * from t1 where a is null or (a >= 5 and a <= 7);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0,p2,p3 ALL NULL NULL NULL NULL 9 Using where
+1 SIMPLE t1 p0,p2,p3 ALL NULL NULL NULL NULL 7 Using where
explain partitions select * from t1 where a is null;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 9 Using where
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 Using where
explain partitions select * from t1 where a is not null;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
explain partitions select * from t1 where a >= 1 and a < 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 9 Using where
+1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 5 Using where
explain partitions select * from t1 where a >= 3 and a <= 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p1,p2 ALL NULL NULL NULL NULL 9 Using where
+1 SIMPLE t1 p1,p2 ALL NULL NULL NULL NULL 4 Using where
explain partitions select * from t1 where a > 2 and a < 4;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 9 Using where
+1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t1 where a > 3 and a <= 6;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 9 Using where
+1 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 4 Using where
explain partitions select * from t1 where a > 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p0,p1,p2,p3 ALL NULL NULL NULL NULL 9 Using where
=== modified file 'mysql-test/r/partition_pruning.result'
--- a/mysql-test/r/partition_pruning.result 2010-03-20 12:01:47 +0000
+++ b/mysql-test/r/partition_pruning.result 2010-04-05 20:16:45 +0000
@@ -89,7 +89,7 @@
1
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0,p1 index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
+1 SIMPLE t1 p0,p1 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
SELECT * FROM t1 WHERE a <= 2;
a
-1
@@ -98,7 +98,7 @@
2
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 2;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0,p1,p2 index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
+1 SIMPLE t1 p0,p1,p2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index
SELECT * FROM t1 WHERE a <= 3;
a
-1
@@ -108,7 +108,7 @@
3
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0,p1,p2,p3 index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
+1 SIMPLE t1 p0,p1,p2,p3 index PRIMARY PRIMARY 4 NULL 5 Using where; Using index
SELECT * FROM t1 WHERE a <= 4;
a
-1
@@ -119,7 +119,7 @@
4
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 4;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0,p1,p2,p3,p4 index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
+1 SIMPLE t1 p0,p1,p2,p3,p4 index PRIMARY PRIMARY 4 NULL 6 Using where; Using index
SELECT * FROM t1 WHERE a <= 5;
a
-1
@@ -131,7 +131,7 @@
5
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0,p1,p2,p3,p4,p5 index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
+1 SIMPLE t1 p0,p1,p2,p3,p4,p5 index PRIMARY PRIMARY 4 NULL 7 Using where; Using index
SELECT * FROM t1 WHERE a <= 6;
a
-1
@@ -213,7 +213,7 @@
8
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 1;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
+1 SIMPLE t1 p1,p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 8 Using where; Using index
SELECT * FROM t1 WHERE a >= 2;
a
2
@@ -225,7 +225,7 @@
8
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 2;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
+1 SIMPLE t1 p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 7 Using where; Using index
SELECT * FROM t1 WHERE a >= 3;
a
3
@@ -236,7 +236,7 @@
8
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
+1 SIMPLE t1 p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 6 Using where; Using index
SELECT * FROM t1 WHERE a >= 4;
a
4
@@ -246,7 +246,7 @@
8
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 4;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
+1 SIMPLE t1 p4,p5,max index PRIMARY PRIMARY 4 NULL 5 Using where; Using index
SELECT * FROM t1 WHERE a >= 5;
a
5
@@ -255,7 +255,7 @@
8
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
+1 SIMPLE t1 p5,max index PRIMARY PRIMARY 4 NULL 4 Using where; Using index
SELECT * FROM t1 WHERE a >= 6;
a
6
@@ -263,7 +263,7 @@
8
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 6;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
+1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
SELECT * FROM t1 WHERE a >= 7;
a
7
@@ -282,7 +282,7 @@
8
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 1;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
+1 SIMPLE t1 p2,p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 7 Using where; Using index
SELECT * FROM t1 WHERE a > 2;
a
3
@@ -293,7 +293,7 @@
8
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 2;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
+1 SIMPLE t1 p3,p4,p5,max index PRIMARY PRIMARY 4 NULL 6 Using where; Using index
SELECT * FROM t1 WHERE a > 3;
a
4
@@ -303,7 +303,7 @@
8
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p4,p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
+1 SIMPLE t1 p4,p5,max index PRIMARY PRIMARY 4 NULL 5 Using where; Using index
SELECT * FROM t1 WHERE a > 4;
a
5
@@ -312,7 +312,7 @@
8
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 4;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p5,max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
+1 SIMPLE t1 p5,max index PRIMARY PRIMARY 4 NULL 4 Using where; Using index
SELECT * FROM t1 WHERE a > 5;
a
6
@@ -320,7 +320,7 @@
8
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 10 Using where; Using index
+1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
SELECT * FROM t1 WHERE a > 6;
a
7
@@ -408,7 +408,7 @@
1
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0,p1 index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
+1 SIMPLE t1 p0,p1 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
SELECT * FROM t1 WHERE a <= 2;
a
-1
@@ -417,7 +417,7 @@
2
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 2;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0,p1,p2 index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
+1 SIMPLE t1 p0,p1,p2 index PRIMARY PRIMARY 4 NULL 4 Using where; Using index
SELECT * FROM t1 WHERE a <= 3;
a
-1
@@ -427,7 +427,7 @@
3
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0,p1,p2,p3 index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
+1 SIMPLE t1 p0,p1,p2,p3 index PRIMARY PRIMARY 4 NULL 5 Using where; Using index
SELECT * FROM t1 WHERE a <= 4;
a
-1
@@ -438,7 +438,7 @@
4
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 4;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0,p1,p2,p3,p4 index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
+1 SIMPLE t1 p0,p1,p2,p3,p4 index PRIMARY PRIMARY 4 NULL 6 Using where; Using index
SELECT * FROM t1 WHERE a <= 5;
a
-1
@@ -511,7 +511,7 @@
7
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 1;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
+1 SIMPLE t1 p1,p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 7 Using where; Using index
SELECT * FROM t1 WHERE a >= 2;
a
2
@@ -522,7 +522,7 @@
7
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 2;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
+1 SIMPLE t1 p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 6 Using where; Using index
SELECT * FROM t1 WHERE a >= 3;
a
3
@@ -532,7 +532,7 @@
7
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
+1 SIMPLE t1 p3,p4,max index PRIMARY PRIMARY 4 NULL 5 Using where; Using index
SELECT * FROM t1 WHERE a >= 4;
a
4
@@ -541,7 +541,7 @@
7
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 4;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
+1 SIMPLE t1 p4,max index PRIMARY PRIMARY 4 NULL 4 Using where; Using index
SELECT * FROM t1 WHERE a >= 5;
a
5
@@ -549,7 +549,7 @@
7
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
+1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
SELECT * FROM t1 WHERE a >= 6;
a
6
@@ -567,7 +567,7 @@
7
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 1;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
+1 SIMPLE t1 p2,p3,p4,max index PRIMARY PRIMARY 4 NULL 6 Using where; Using index
SELECT * FROM t1 WHERE a > 2;
a
3
@@ -577,7 +577,7 @@
7
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 2;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p3,p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
+1 SIMPLE t1 p3,p4,max index PRIMARY PRIMARY 4 NULL 5 Using where; Using index
SELECT * FROM t1 WHERE a > 3;
a
4
@@ -586,7 +586,7 @@
7
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p4,max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
+1 SIMPLE t1 p4,max index PRIMARY PRIMARY 4 NULL 4 Using where; Using index
SELECT * FROM t1 WHERE a > 4;
a
5
@@ -594,7 +594,7 @@
7
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > 4;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 9 Using where; Using index
+1 SIMPLE t1 max index PRIMARY PRIMARY 4 NULL 3 Using where; Using index
SELECT * FROM t1 WHERE a > 5;
a
6
@@ -880,34 +880,34 @@
1001-01-01
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL,p2001-01-01 ALL NULL NULL NULL NULL 4 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p1001-01-01 system NULL NULL NULL NULL 1
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 3 Using where
# Disabling warnings for the invalid date
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
@@ -917,25 +917,25 @@
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL,p2001-01-01 ALL NULL NULL NULL NULL 4 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL,p2001-01-01 ALL NULL NULL NULL NULL 4 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 3 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL,p1001-01-01,p2001-01-01 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL,p0001-01-01,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
DROP TABLE t1;
# test of LIST and index
CREATE TABLE t1 (a DATE, KEY(a))
@@ -1209,62 +1209,62 @@
1001-01-01
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 4 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p1001-01-01 system NULL NULL NULL NULL 1
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 p2001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 3 Using where
# Disabling warnings for the invalid date
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 4 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 p2001-01-01,pNULL ALL NULL NULL NULL NULL 4 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL ALL NULL NULL NULL NULL 3 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 p0001-01-01,pNULL,p0000-01-02,p1001-01-01 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pNULL,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 pNULL,p1001-01-01 ALL NULL NULL NULL NULL 4 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 p0001-01-01,pNULL,p1001-01-01 ALL NULL NULL NULL NULL 5 Using where
DROP TABLE t1;
# Test with DATETIME column NOT NULL
CREATE TABLE t1 (
@@ -1289,25 +1289,25 @@
1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 8 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 index NULL PRIMARY 12 NULL 8 Using where; Using index
+1 SIMPLE t1 p20090403 index NULL PRIMARY 12 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 6 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 6 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090402 index NULL PRIMARY 12 NULL 3 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
@@ -1315,99 +1315,99 @@
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 6 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 8 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090403 index NULL PRIMARY 12 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 6 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 8 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090403 index NULL PRIMARY 12 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 6 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 6 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090402 index NULL PRIMARY 12 NULL 3 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 6 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 8 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090403 index NULL PRIMARY 12 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 8 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 12 NULL 8 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090403 index NULL PRIMARY 12 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 6 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 12 NULL 6 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090402 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090402 index NULL PRIMARY 12 NULL 3 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
@@ -1440,21 +1440,21 @@
1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index
+1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
@@ -1462,79 +1462,79 @@
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090403 index NULL PRIMARY 7 NULL 2 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 7 NULL 7 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
@@ -1542,19 +1542,19 @@
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090404,p20090405 index NULL PRIMARY 7 NULL 8 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 7 NULL 5 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
@@ -1562,11 +1562,11 @@
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 12 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 7 NULL 10 Using where; Using index
DROP TABLE t1;
# Test with DATETIME column NULL
CREATE TABLE t1 (
@@ -1590,25 +1590,25 @@
1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 8 Using where
+1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090402 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090402 ALL NULL NULL NULL NULL 3 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
@@ -1616,99 +1616,99 @@
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090402 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090402 ALL NULL NULL NULL NULL 3 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 8 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 6 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090402 ALL NULL NULL NULL NULL 13 Using where
+1 SIMPLE t1 p20090402 ALL NULL NULL NULL NULL 3 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
@@ -1740,21 +1740,21 @@
1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 7 Using where
+1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
@@ -1762,79 +1762,79 @@
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b < '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b <= '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b = '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090403 ALL NULL NULL NULL NULL 2 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b >= '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE b > '2009-04-03';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090402,p20090403 ALL NULL NULL NULL NULL 7 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
@@ -1842,19 +1842,19 @@
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090404,p20090405 ALL NULL NULL NULL NULL 8 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090402 ALL NULL NULL NULL NULL 5 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
@@ -1862,11 +1862,11 @@
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
EXPLAIN PARTITIONS SELECT * FROM t1
WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 12 Using where
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 ALL NULL NULL NULL NULL 10 Using where
DROP TABLE t1;
# For better code coverage of the patch
CREATE TABLE t1 (
@@ -1930,7 +1930,7 @@
1 SIMPLE t2 p0,p1 ALL NULL NULL NULL NULL 3 Using where
explain partitions select * from t2 where a=1 and b=1;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 2 Using where
create table t3 (
a int
)
@@ -1988,25 +1988,25 @@
explain partitions select * from t5
where (a=10 and b=1) or (a=10 and b=2) or (a=10 and b = 3);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t5 p0_p0sp0,p0_p0sp1 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t5 p0_p0sp0,p0_p0sp1 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t5 where (a=10 and b=2) or (a=10 and b=3)
or (a=10 and b = 4);
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t5 p0_p0sp0,p0_p0sp1,p1_p1sp0,p1_p1sp1 ALL NULL NULL NULL NULL 4 Using where
explain partitions select * from t5 where (c=1 and d=1);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t5 p0_p0sp0,p1_p1sp0 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t5 p0_p0sp0,p1_p1sp0 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t5 where (c=2 and d=1);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t5 p0_p0sp1,p1_p1sp1 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t5 p0_p0sp1,p1_p1sp1 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or
(c=2 and d=1);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t5 p0_p0sp0,p0_p0sp1,p1_p1sp1 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t5 p0_p0sp0,p0_p0sp1,p1_p1sp1 ALL NULL NULL NULL NULL 3 Using where
explain partitions select * from t5 where (a=10 and b=2 and c=1 and d=1) or
(b=2 and c=2 and d=1);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t5 p0_p0sp0,p0_p0sp1,p1_p1sp1 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t5 p0_p0sp0,p0_p0sp1,p1_p1sp1 ALL NULL NULL NULL NULL 3 Using where
create table t6 (a int not null) partition by LIST(a) (
partition p1 values in (1),
partition p3 values in (3),
@@ -2044,7 +2044,7 @@
1 SIMPLE t6 p5,p7,p9 system NULL NULL NULL NULL 1
explain partitions select * from t6 where a >= 3 and a <= 8;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t6 p3,p5,p7 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t6 p3,p5,p7 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t6 where a > 3 and a < 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
@@ -2086,7 +2086,7 @@
1 SIMPLE t6 p5,p7,p9 system NULL NULL NULL NULL 1
explain partitions select * from t6 where a >= 3 and a <= 8;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t6 p3,p5,p7 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t6 p3,p5,p7 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t6 where a > 3 and a < 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
@@ -2241,7 +2241,7 @@
explain partitions
select * from t1 X, t1 Y where X.a = Y.a and (X.a=1 or X.a=2);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE X p1,p2 ALL a NULL NULL NULL 4 Using where
+1 SIMPLE X p1,p2 ALL a NULL NULL NULL 2 Using where
1 SIMPLE Y p1,p2 ref a a 4 test.X.a 2
drop table t1;
create table t1 (a int) partition by hash(a) partitions 20;
@@ -2254,7 +2254,7 @@
1 SIMPLE t1 p1,p2 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t1 where a > 1 and a <= 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t1 where a >= 1 and a <= 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t1 p1,p2,p3 ALL NULL NULL NULL NULL 3 Using where
@@ -2344,22 +2344,22 @@
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1010
explain partitions select * from t2 where a < 801 and a > 200;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p1,p2,p3,p4 ALL NULL NULL NULL NULL 1010 Using where
+1 SIMPLE t2 p1,p2,p3,p4 ALL NULL NULL NULL NULL 800 Using where
explain partitions select * from t2 where a < 801 and a > 800;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p4 ALL NULL NULL NULL NULL 1010 Using where
+1 SIMPLE t2 p4 ALL NULL NULL NULL NULL 200 Using where
explain partitions select * from t2 where a > 600;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 1010 Using where
+1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
explain partitions select * from t2 where a > 600 and b = 1;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 1010 Using where
+1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
explain partitions select * from t2 where a > 600 and b = 4;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 1010 Using where
+1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
explain partitions select * from t2 where a > 600 and b = 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 1010 Using where
+1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 400 Using where
explain partitions select * from t2 where b = 5;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 1010 Using where
@@ -2414,19 +2414,19 @@
1 SIMPLE t2 p0,p1,p2,p3,p4 ALL NULL NULL NULL NULL 910
explain partitions select * from t2 where a = 101;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 910 Using where
+1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 110 Using where
explain partitions select * from t2 where a = 550;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p2 ALL NULL NULL NULL NULL 910 Using where
+1 SIMPLE t2 p2 ALL NULL NULL NULL NULL 200 Using where
explain partitions select * from t2 where a = 833;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p4 ALL NULL NULL NULL NULL 910 Using where
+1 SIMPLE t2 p4 ALL NULL NULL NULL NULL 200 Using where
explain partitions select * from t2 where (a = 100 OR a = 900);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p0,p4 ALL NULL NULL NULL NULL 910 Using where
+1 SIMPLE t2 p0,p4 ALL NULL NULL NULL NULL 310 Using where
explain partitions select * from t2 where (a > 100 AND a < 600);
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p0,p1,p2 ALL NULL NULL NULL NULL 910 Using where
+1 SIMPLE t2 p0,p1,p2 ALL NULL NULL NULL NULL 510 Using where
explain partitions select * from t2 where b = 4;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE t2 p0,p1,p2,p3,p4 ref b b 5 const 76
@@ -2712,17 +2712,17 @@
explain partitions select * from t1
where a >= 18446744073709551000-1 and a <= 18446744073709551000+1;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p3,p4 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t1 p3,p4 ALL NULL NULL NULL NULL 3 Using where
explain partitions select * from t1
where a between 18446744073709551001 and 18446744073709551002;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t1 where a = 18446744073709551000;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t1 where a = 18446744073709551613;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t1 where a = 18446744073709551614;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
@@ -2749,10 +2749,10 @@
1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t1 where a=0xFE;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t1 p2 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t2 where a=0xFE;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p2 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t2 p2 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t1 where a > 0xFE AND a <= 0xFF;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
@@ -2761,22 +2761,22 @@
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
explain partitions select * from t1 where a >= 0xFE AND a <= 0xFF;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p2 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t1 p2 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t2 where a >= 0xFE AND a <= 0xFF;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p2 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t2 p2 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t1 where a < 64 AND a >= 63;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t2 where a < 64 AND a >= 63;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t2 p0 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t1 where a <= 64 AND a >= 63;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 6 Using where
+1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 4 Using where
explain partitions select * from t2 where a <= 64 AND a >= 63;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 p0,p1 ALL NULL NULL NULL NULL 6 Using where
+1 SIMPLE t2 p0,p1 ALL NULL NULL NULL NULL 4 Using where
drop table t1;
drop table t2;
create table t1(a bigint unsigned not null) partition by range(a+0) (
=== modified file 'mysql-test/r/partition_range.result'
--- a/mysql-test/r/partition_range.result 2009-08-26 10:59:49 +0000
+++ b/mysql-test/r/partition_range.result 2010-04-05 20:16:45 +0000
@@ -59,13 +59,13 @@
1 SIMPLE t1 pnull system NULL NULL NULL NULL 1
explain partitions select * from t1 where a >= 0;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t1 where a < 0;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
explain partitions select * from t1 where a <= 0;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pnull,p0 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t1 pnull,p0 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t1 where a > 1;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
@@ -98,16 +98,16 @@
a b
explain partitions select * from t1 where a is null;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 6 Using where
+1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t1 where a >= 0;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0_p0sp0,p0_p0sp1,p1_p1sp0,p1_p1sp1 ALL NULL NULL NULL NULL 6 Using where
+1 SIMPLE t1 p0_p0sp0,p0_p0sp1,p1_p1sp0,p1_p1sp1 ALL NULL NULL NULL NULL 4 Using where
explain partitions select * from t1 where a < 0;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 6 Using where
+1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 2 Using where
explain partitions select * from t1 where a <= 0;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1,p0_p0sp0,p0_p0sp1 ALL NULL NULL NULL NULL 6 Using where
+1 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1,p0_p0sp0,p0_p0sp1 ALL NULL NULL NULL NULL 4 Using where
explain partitions select * from t1 where a > 1;
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result 2010-03-20 12:01:47 +0000
+++ b/mysql-test/r/subselect.result 2010-04-05 20:16:45 +0000
@@ -3417,7 +3417,8 @@
EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 9
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
ALTER TABLE t1 ADD INDEX(a);
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
@@ -3428,7 +3429,8 @@
EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
+1 PRIMARY t1 ALL a NULL NULL NULL 9
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
DROP TABLE t1;
create table t1( f1 int,f2 int);
@@ -4322,16 +4324,18 @@
INSERT INTO t1 VALUES (1),(2);
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
+1 PRIMARY SUBQUERY#2 const distinct_key distinct_key 4 const 1 100.00
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in ( <materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `materialized subselect`.`1`)))))
+Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`materialized subselect`.`1` = 1)
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
+1 PRIMARY SUBQUERY#2 const distinct_key distinct_key 4 const 1 100.00
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in ( <materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `materialized subselect`.`1`)))))
+Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`materialized subselect`.`1` = 1)
DROP TABLE t1;
#
# Bug#45061: Incorrectly market field caused wrong result.
=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result 2010-03-29 14:04:35 +0000
+++ b/mysql-test/r/subselect3.result 2010-04-05 20:16:45 +0000
@@ -1212,24 +1212,28 @@
2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where
explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY A ALL NULL NULL NULL NULL 10
1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.A.a 1
2 SUBQUERY t2 ALL NULL NULL NULL NULL 10
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.t2.a 1
2 SUBQUERY A ALL NULL NULL NULL NULL 10
2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.t2.a 1
2 SUBQUERY A ALL NULL NULL NULL NULL 10
2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
explain select straight_join * from t2 X, t2 Y
where X.a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY X ALL NULL NULL NULL NULL 10
1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.X.a 1
2 SUBQUERY A ALL NULL NULL NULL NULL 10
2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
create table t0 (a int, b int);
=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- a/mysql-test/r/subselect3_jcl6.result 2010-03-29 14:04:35 +0000
+++ b/mysql-test/r/subselect3_jcl6.result 2010-04-05 20:16:45 +0000
@@ -1217,24 +1217,28 @@
2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where
explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY A ALL NULL NULL NULL NULL 10
1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.A.a 1
2 SUBQUERY t2 ALL NULL NULL NULL NULL 10
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.t2.a 1
2 SUBQUERY A ALL NULL NULL NULL NULL 10
2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.t2.a 1
2 SUBQUERY A ALL NULL NULL NULL NULL 10
2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
explain select straight_join * from t2 X, t2 Y
where X.a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY X ALL NULL NULL NULL NULL 10
1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.X.a 1
2 SUBQUERY A ALL NULL NULL NULL NULL 10
2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
create table t0 (a int, b int);
=== modified file 'mysql-test/r/subselect_mat.result'
--- a/mysql-test/r/subselect_mat.result 2010-03-29 14:04:35 +0000
+++ b/mysql-test/r/subselect_mat.result 2010-04-05 20:16:45 +0000
@@ -38,10 +38,11 @@
explain extended
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 9 test.t1.a1 1 100.00
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`)))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0')) join `test`.`t1` where (`materialized subselect`.`b1` = `test`.`t1`.`a1`)
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
a1 a2
1 - 01 2 - 01
@@ -49,10 +50,11 @@
explain extended
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 9 test.t1.a1 1 100.00
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`)))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`) join `test`.`t1` where (`materialized subselect`.`b1` = `test`.`t1`.`a1`)
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
a1 a2
1 - 01 2 - 01
@@ -60,10 +62,11 @@
explain extended
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`)))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`materialized subselect`.`b2` = `test`.`t1`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1`.`a1`))
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
a1 a2
1 - 01 2 - 01
@@ -71,10 +74,11 @@
explain extended
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,min(`test`.`t2`.`b2`) AS `min(b2)` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`min(b2)`)))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1`,min(`test`.`t2`.`b2`) AS `min(b2)` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`) join `test`.`t1` where ((`materialized subselect`.`min(b2)` = `test`.`t1`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1`.`a1`))
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
a1 a2
1 - 01 2 - 01
@@ -82,10 +86,11 @@
explain extended
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
+1 PRIMARY t1i index it1i1,it1i3 it1i3 18 NULL 3 100.00 Using index
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 9 test.t1i.a1 1 100.00
2 SUBQUERY t2i index it2i1,it2i3 it2i1 9 NULL 5 100.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>(`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( <materialize> (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`)))))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where (`materialized subselect`.`b1` = `test`.`t1i`.`a1`)
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
a1 a2
1 - 01 2 - 01
@@ -93,10 +98,11 @@
explain extended
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
+1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 3 100.00
+1 PRIMARY t1i ref it1i1,it1i3 it1i3 9 materialized subselect.b1 1 100.00 Using index
2 SUBQUERY t2i range it2i1,it2i3 it2i1 9 NULL 3 100.00 Using where; Using index for group-by
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>(`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( <materialize> (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`)))))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (`test`.`t1i`.`a1` = `materialized subselect`.`b1`)
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
a1 a2
1 - 01 2 - 01
@@ -104,10 +110,11 @@
explain extended
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
+1 PRIMARY t1i index it1i1,it1i2,it1i3 it1i3 18 NULL 3 100.00 Using index
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1i.a1,test.t1i.a2 1 100.00
2 SUBQUERY t2i index it2i1,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`)))))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where ((`materialized subselect`.`b2` = `test`.`t1i`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1i`.`a1`))
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
a1 a2
1 - 01 2 - 01
@@ -115,10 +122,11 @@
explain extended
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
+1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 3 100.00
+1 PRIMARY t1i ref it1i1,it1i2,it1i3 it1i3 18 materialized subselect.b1,materialized subselect.b2 1 100.00 Using index
2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`)))))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`,`test`.`t2i`.`b2`) join `test`.`t1i` where ((`test`.`t1i`.`a2` = `materialized subselect`.`b2`) and (`test`.`t1i`.`a1` = `materialized subselect`.`b1`))
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
a1 a2
1 - 01 2 - 01
@@ -126,10 +134,11 @@
explain extended
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
+1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 3 100.00
+1 PRIMARY t1i ref it1i1,it1i2,it1i3 it1i3 18 materialized subselect.b1,materialized subselect.min(b2) 1 100.00 Using index
2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`min(b2)`)))))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where ((`test`.`t1i`.`a2` = `materialized subselect`.`min(b2)`) and (`test`.`t1i`.`a1` = `materialized subselect`.`b1`))
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
a1 a2
1 - 01 2 - 01
@@ -137,10 +146,11 @@
explain extended
select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00
2 SUBQUERY t2i range NULL it2i3 9 NULL 3 100.00 Using index for group-by
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,max(`test`.`t2i`.`b2`) AS `max(b2)` from `test`.`t2i` group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`max(b2)`)))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,max(`test`.`t2i`.`b2`) AS `max(b2)` from `test`.`t2i` group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`materialized subselect`.`max(b2)` = `test`.`t1`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1`.`a1`))
select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
a1 a2
1 - 01 2 - 01
@@ -148,11 +158,13 @@
prepare st1 from "explain select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
execute st1;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1
2 SUBQUERY t2i range NULL it2i3 9 NULL 3 Using index for group-by
execute st1;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1
2 SUBQUERY t2i range NULL it2i3 9 NULL 3 Using index for group-by
prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
execute st2;
@@ -166,10 +178,11 @@
explain extended
select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00
2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`min(b2)`)))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1` where ((`materialized subselect`.`min(b2)` = `test`.`t1`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1`.`a1`))
select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
a1 a2
1 - 01 2 - 01
@@ -206,10 +219,11 @@
explain extended
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`)))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`materialized subselect`.`b2` = `test`.`t1`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1`.`a1`))
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
a1 a2
1 - 01 2 - 01
@@ -217,10 +231,11 @@
explain extended
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
+1 PRIMARY t1i index it1i1,it1i2,it1i3 it1i3 18 NULL 3 100.00 Using index
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 18 test.t1i.a1,test.t1i.a2 1 100.00
2 SUBQUERY t2i index NULL it2i3 18 NULL 5 100.00 Using index
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`)))))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2`) join `test`.`t1i` where ((`materialized subselect`.`b2` = `test`.`t1i`.`a2`) and (`materialized subselect`.`b1` = `test`.`t1i`.`a1`))
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
a1 a2
1 - 01 2 - 01
@@ -270,12 +285,15 @@
(a1, a2) in (select c1, c2 from t3
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
+1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 5 0.00
+1 PRIMARY SUBQUERY#3 eq_ref distinct_key distinct_key 18 materialized subselect.b1,materialized subselect.b2 1 100.00
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
+3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00
+3 SUBQUERY SUBQUERY#4 eq_ref distinct_key distinct_key 18 test.t3.c1,test.t3.c2 1 100.00
4 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`))))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key wh
ere ((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` = `materialized subselect`.`b2`))))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`c2`))))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`materialized subselect`.`b2` = `test`.`t3`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3`.`c1`))) join <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0')) join `test`.`t1` where ((`materialized subselect`.`c2` = `materialized subselect`.`b2`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`) and (`materialized subselect`.`c1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a1` = `materialized subselect`.`b1`))
select * from t1
where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and
(a1, a2) in (select c1, c2 from t3
@@ -289,12 +307,15 @@
(a1, a2) in (select c1, c2 from t3i
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
-3 SUBQUERY t3i index NULL it3i3 18 NULL 4 100.00 Using where; Using index
+1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 5 0.00
+1 PRIMARY SUBQUERY#3 eq_ref distinct_key distinct_key 18 materialized subselect.b1,materialized subselect.b2 1 100.00
+1 PRIMARY t1i ref it1i1,it1i2,it1i3 it1i3 18 materialized subselect.b1,materialized subselect.c2 1 100.00 Using where; Using index
+3 SUBQUERY t3i index it3i1,it3i2,it3i3 it3i3 18 NULL 4 100.00 Using index
+3 SUBQUERY SUBQUERY#4 eq_ref distinct_key distinct_key 18 test.t3i.c1,test.t3i.c2 1 100.00
4 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
2 SUBQUERY t2i index it2i1,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`))))) and <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from `test`.`t3i` where <in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3i`.`c1` in <temporary
table> on distinct_key where ((`test`.`t3i`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3i`.`c2` = `materialized subselect`.`b2`))))) ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`c2`))))))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3i` where ((`materialized subselect`.`b2` = `test`.`t3i`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3i`.`c1`))) join <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i` where ((`materialized subselect`.`c2` = `materialized subselect`.`b2`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`) and (`materialized subselect`.`c1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a1` = `materialized subselect`.`b1`))
select * from t1i
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
(a1, a2) in (select c1, c2 from t3i
@@ -310,14 +331,17 @@
(a1, a2) in (select c1, c2 from t3
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-5 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
+1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 5 0.00
+1 PRIMARY SUBQUERY#5 eq_ref distinct_key distinct_key 18 materialized subselect.b1,materialized subselect.b2 1 100.00
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
+5 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00
+5 SUBQUERY SUBQUERY#6 eq_ref distinct_key distinct_key 18 test.t3.c1,test.t3.c2 1 100.00
6 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materi
alized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`))))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key where ((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` = `materialized subselect`.`b2`))))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`c2`))))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`materialized subselect`.`b2` = `test`.`t3`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3`.`c1`))) join <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(
`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))))) join `test`.`t1` where ((`materialized subselect`.`c2` = `materialized subselect`.`b2`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`) and (`materialized subselect`.`c1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a1` = `materialized subselect`.`b1`))
select * from t1
where (a1, a2) in (select b1, b2 from t2
where b2 in (select c2 from t3 where c2 LIKE '%02') or
@@ -335,14 +359,16 @@
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-5 SUBQUERY t3c ALL NULL NULL NULL NULL 4 100.00 Using where
+1 PRIMARY SUBQUERY#5 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00
+5 SUBQUERY t3c ALL NULL NULL NULL NULL 4 100.00
+5 SUBQUERY SUBQUERY#6 eq_ref distinct_key distinct_key 18 test.t3c.c1,test.t3c.c2 1 100.00
6 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
4 SUBQUERY t3b ALL NULL NULL NULL NULL 4 100.00 Using where
3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c
`.`c2` AS `c2` from `test`.`t3` `t3c` where <in_optimizer>((`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),(`test`.`t3c`.`c1`,`test`.`t3c`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3c`.`c1` in <temporary table> on distinct_key where ((`test`.`t3c`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3c`.`c2` = `materialized subselect`.`b2`))))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`c2`))))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` `t3c` where ((`materialized subselect`.`b2` = `test`.`t3c`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3c`.`c1`))) join `test`.`t1` where ((`materialized subselect`.`c2` = `test`.`t1`.`a2`) and (`materialized subselect`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3b`.`c2` AS `c2` fr
om `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))))
select * from t1
where (a1, a2) in (select b1, b2 from t2
where b2 in (select c2 from t3 t3a where c1 = a1) or
@@ -366,19 +392,25 @@
(a1, a2) in (select c1, c2 from t3i
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-5 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
+1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 5 0.00
+1 PRIMARY SUBQUERY#5 eq_ref distinct_key distinct_key 18 materialized subselect.b1,materialized subselect.b2 1 100.00
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer
+5 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00
+5 SUBQUERY SUBQUERY#6 eq_ref distinct_key distinct_key 18 test.t3.c1,test.t3.c2 1 100.00
6 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
-7 UNION t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
-9 SUBQUERY t3i index NULL it3i3 18 NULL 4 100.00 Using where; Using index
+7 UNION SUBQUERY#8 ALL distinct_key NULL NULL NULL 5 0.00
+7 UNION SUBQUERY#9 eq_ref distinct_key distinct_key 18 materialized subselect.b1,materialized subselect.b2 1 100.00
+7 UNION t1i ref it1i1,it1i2,it1i3 it1i3 18 materialized subselect.b1,materialized subselect.c2 1 100.00 Using where; Using index
+9 SUBQUERY t3i index it3i1,it3i2,it3i3 it3i3 18 NULL 4 100.00 Using index
+9 SUBQUERY SUBQUERY#10 eq_ref distinct_key distinct_key 18 test.t3i.c1,test.t3i.c2 1 100.00
10 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
8 SUBQUERY t2i index it2i1,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index
NULL UNION RESULT <union1,7> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on dis
tinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`))))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key where ((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` = `materialized subselect`.`b2`))))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`c2`))))))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` A
S `a2` from `test`.`t1i` where (<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`))))) and <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from `test`.`t3i` where <in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3i`.`c1` in <temporary table> on distinct_key where ((`test`.`t3i`.`c1` = `materiali
zed subselect`.`b1`) and (`test`.`t3i`.`c2` = `materialized subselect`.`b2`))))) ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`c2`)))))))
+Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`materialized subselect`.`b2` = `test`.`t3`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3`.`c1`))) join <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>
(`test`.`t2`.`b2` in <temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2`) join `test`.`t1` where ((`materialized subselect`.`c2` = `materialized subselect`.`b2`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`) and (`materialized subselect`.`c1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a1` = `materialized subselect`.`b1`))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3i` where ((`materialized subselect`.`b2` = `test`.`t3i`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3i`.`c1`))) join <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')) join `test`.`t1i
` where ((`materialized subselect`.`c2` = `materialized subselect`.`b2`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`) and (`materialized subselect`.`c1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a1` = `materialized subselect`.`b1`)))
(select * from t1
where (a1, a2) in (select b1, b2 from t2
where b2 in (select c2 from t3 where c2 LIKE '%02') or
@@ -401,13 +433,15 @@
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
-4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
+1 PRIMARY SUBQUERY#4 eq_ref distinct_key distinct_key 18 test.t1.a1,test.t1.a2 1 100.00
+4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00
+4 SUBQUERY SUBQUERY#5 eq_ref distinct_key distinct_key 18 test.t3.c1,test.t3.c2 1 100.00
5 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <p
rimary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key where ((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` = `materialized subselect`.`b2`))))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`c2`))))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`materialized subselect`.`b2` = `test`.`t3`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3`.`c1`))) join `test`.`t1` where ((`materialized subselect`.`c2` = `test`.`t1`.`a2`) and (`materialized subselect`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`t
est`.`t1`.`a2`) = `test`.`t2`.`b2`)))))
select * from t1
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
(a1, a2) in (select c1, c2 from t3
@@ -423,14 +457,16 @@
a1 = c1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY SUBQUERY#4 ALL distinct_key NULL NULL NULL 4 75.00 Using where; Using join buffer
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer
-4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
+4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00
+4 SUBQUERY SUBQUERY#5 eq_ref distinct_key distinct_key 18 test.t3.c1,test.t3.c2 1 100.00
5 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( <materialize> (selec
t `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key where ((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` = `materialized subselect`.`b2`))))) ), <primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key where ((`test`.`t3`.`c1` = `materialized subselect`.`c1`) and (`test`.`t3`.`c2` = `materialized subselect`.`c2`))))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0')) join `test`.`t3` where ((`materialized subselect`.`b2` = `test`.`t3`.`c2`) and (`materialized subselect`.`b1` = `test`.`t3`.`c1`))) join `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`c2` = `materialized subselect`.`c2`) and (`materialized subselect`.`c1` = `test`.`t1`.`a1`) and (`test`.`t3`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `
test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))))
select * from t1, t3
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
(c1, c2) in (select c1, c2 from t3
@@ -608,10 +644,11 @@
from t1_16
where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 20 test.t1_16.a1 1 100.00 Using where
2 SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
-Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where <in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( <materialize> (select substr(`test`.`t2_16`.`b1`,1,16) AS `substring(b1,1,16)` from `test`.`t2_16` where (`test`.`t2_16`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1_16`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_16`.`a1` = `materialized subselect`.`substring(b1,1,16)`)))))
+Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from <materialize> (select substr(`test`.`t2_16`.`b1`,1,16) AS `substring(b1,1,16)` from `test`.`t2_16` where (`test`.`t2_16`.`b1` > '0')) join `test`.`t1_16` where (`test`.`t1_16`.`a1` = `materialized subselect`.`substring(b1,1,16)`)
select left(a1,7), left(a2,7)
from t1_16
where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
@@ -637,10 +674,11 @@
from t1_16
where a1 in (select group_concat(b1) from t2_16 group by b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_16.a1 1 100.00 Using where
2 SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
-Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from `test`.`t1_16` where <in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( <materialize> (select group_concat(`test`.`t2_16`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_16` group by `test`.`t2_16`.`b2` ), <primary_index_lookup>(`test`.`t1_16`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_16`.`a1` = `materialized subselect`.`group_concat(b1)`)))))
+Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_16`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_16` group by `test`.`t2_16`.`b2`) join `test`.`t1_16` where (`test`.`t1_16`.`a1` = `materialized subselect`.`group_concat(b1)`)
select left(a1,7), left(a2,7)
from t1_16
where a1 in (select group_concat(b1) from t2_16 group by b2);
@@ -660,9 +698,10 @@
2 DEPENDENT SUBQUERY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
3 DEPENDENT SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where
3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer
+3 DEPENDENT SUBQUERY SUBQUERY#4 eq_ref distinct_key distinct_key 9 test.t2.b1 1 100.00 Using where
4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(concat(`test`.`t1`.`a1`,'x'),<exists>(select 1 AS `Not_used` from `test`.`t1_16` where (<in_optimizer>((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),<exists>(select `test`.`t2_16`.`b1` AS `b1`,`test`.`t2_16`.`b2` AS `b2` from `test`.`t2_16` join `test`.`t2` where ((`test`.`t2`.`b2` = substr(`test`.`t2_16`.`b2`,1,6)) and <in_optimizer>(`test`.`t2`.`b1`,`test`.`t2`.`b1` in ( <materialize> (select `test`.`t3`.`c1` AS `c1` from `test`.`t3` where (`test`.`t3`.`c2` > '0') ), <primary_index_lookup>(`test`.`t2`.`b1` in <temporary table> on distinct_key where ((`test`.`t2`.`b1` = `materialized subselect`.`c1`))))) and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`) and (<cache>(`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`)))) and (<cache>(concat(`test`.`t1`.`a1`,'x')) = left(`test`.`t1_16`.`a1`,8)))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(concat(`test`.`t1`.`a1`,'x'),<exists>(select 1 AS `Not_used` from `test`.`t1_16` where (<in_optimizer>((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),<exists>(select `test`.`t2_16`.`b1` AS `b1`,`test`.`t2_16`.`b2` AS `b2` from <materialize> (select `test`.`t3`.`c1` AS `c1` from `test`.`t3` where (`test`.`t3`.`c2` > '0')) join `test`.`t2_16` join `test`.`t2` where ((`materialized subselect`.`c1` = `test`.`t2`.`b1`) and (`test`.`t2`.`b2` = substr(`test`.`t2_16`.`b2`,1,6)) and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`) and (<cache>(`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`)))) and (<cache>(concat(`test`.`t1`.`a1`,'x')) = left(`test`.`t1_16`.`a1`,8)))))
drop table t1_16, t2_16, t3_16;
set @blob_len = 512;
set @suffix_len = @blob_len - @prefix_len;
@@ -721,10 +760,11 @@
from t1_512
where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 517 test.t1_512.a1 1 100.00 Using where
2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
-Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where <in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize> (select substr(`test`.`t2_512`.`b1`,1,512) AS `substring(b1,1,512)` from `test`.`t2_512` where (`test`.`t2_512`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_512`.`a1` = `materialized subselect`.`substring(b1,1,512)`)))))
+Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select substr(`test`.`t2_512`.`b1`,1,512) AS `substring(b1,1,512)` from `test`.`t2_512` where (`test`.`t2_512`.`b1` > '0')) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `materialized subselect`.`substring(b1,1,512)`)
select left(a1,7), left(a2,7)
from t1_512
where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
@@ -735,10 +775,11 @@
from t1_512
where a1 in (select group_concat(b1) from t2_512 group by b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_512.a1 1 100.00 Using where
2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
-Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where <in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2` ), <primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_512`.`a1` = `materialized subselect`.`group_concat(b1)`)))))
+Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `materialized subselect`.`group_concat(b1)`)
select left(a1,7), left(a2,7)
from t1_512
where a1 in (select group_concat(b1) from t2_512 group by b2);
@@ -748,10 +789,11 @@
from t1_512
where a1 in (select group_concat(b1) from t2_512 group by b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_512.a1 1 100.00 Using where
2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
-Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where <in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2` ), <primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_512`.`a1` = `materialized subselect`.`group_concat(b1)`)))))
+Note 1003 select left(`test`.`t1_512`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_512` group by `test`.`t2_512`.`b2`) join `test`.`t1_512` where (`test`.`t1_512`.`a1` = `materialized subselect`.`group_concat(b1)`)
select left(a1,7), left(a2,7)
from t1_512
where a1 in (select group_concat(b1) from t2_512 group by b2);
@@ -817,7 +859,7 @@
1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
2 DEPENDENT SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
-Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where <in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in (select 1 AS `Not_used` from `test`.`t2_1024` where ((`test`.`t2_1024`.`b1` > '0') and (<cache>(`test`.`t1_1024`.`a1`) = substr(`test`.`t2_1024`.`b1`,1,1024)))))
+Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where <in_optimizer>(`test`.`t1_1024`.`a1`,<exists>(select 1 AS `Not_used` from `test`.`t2_1024` where ((`test`.`t2_1024`.`b1` > '0') and (<cache>(`test`.`t1_1024`.`a1`) = substr(`test`.`t2_1024`.`b1`,1,1024)) and (<cache>(`test`.`t1_1024`.`a1`) = 1))))
select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
@@ -828,10 +870,11 @@
from t1_1024
where a1 in (select group_concat(b1) from t2_1024 group by b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_1024.a1 1 100.00 Using where
2 SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
-Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where <in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2` ), <primary_index_lookup>(`test`.`t1_1024`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_1024`.`a1` = `materialized subselect`.`group_concat(b1)`)))))
+Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `materialized subselect`.`group_concat(b1)`)
select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select group_concat(b1) from t2_1024 group by b2);
@@ -841,10 +884,11 @@
from t1_1024
where a1 in (select group_concat(b1) from t2_1024 group by b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_1024.a1 1 100.00 Using where
2 SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
-Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where <in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2` ), <primary_index_lookup>(`test`.`t1_1024`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_1024`.`a1` = `materialized subselect`.`group_concat(b1)`)))))
+Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1024` group by `test`.`t2_1024`.`b2`) join `test`.`t1_1024` where (`test`.`t1_1024`.`a1` = `materialized subselect`.`group_concat(b1)`)
select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select group_concat(b1) from t2_1024 group by b2);
@@ -910,7 +954,7 @@
1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
2 DEPENDENT SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
-Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where <in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in (select 1 AS `Not_used` from `test`.`t2_1025` where ((`test`.`t2_1025`.`b1` > '0') and (<cache>(`test`.`t1_1025`.`a1`) = substr(`test`.`t2_1025`.`b1`,1,1025)))))
+Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where <in_optimizer>(`test`.`t1_1025`.`a1`,<exists>(select 1 AS `Not_used` from `test`.`t2_1025` where ((`test`.`t2_1025`.`b1` > '0') and (<cache>(`test`.`t1_1025`.`a1`) = substr(`test`.`t2_1025`.`b1`,1,1025)) and (<cache>(`test`.`t1_1025`.`a1`) = 1))))
select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
@@ -921,10 +965,11 @@
from t1_1025
where a1 in (select group_concat(b1) from t2_1025 group by b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_1025.a1 1 100.00 Using where
2 SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
-Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where <in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2` ), <primary_index_lookup>(`test`.`t1_1025`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_1025`.`a1` = `materialized subselect`.`group_concat(b1)`)))))
+Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `materialized subselect`.`group_concat(b1)`)
select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select group_concat(b1) from t2_1025 group by b2);
@@ -934,10 +979,11 @@
from t1_1025
where a1 in (select group_concat(b1) from t2_1025 group by b2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 261 test.t1_1025.a1 1 100.00 Using where
2 SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
-Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where <in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2` ), <primary_index_lookup>(`test`.`t1_1025`.`a1` in <temporary table> on distinct_key where ((`test`.`t1_1025`.`a1` = `materialized subselect`.`group_concat(b1)`)))))
+Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from <materialize> (select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_1025` group by `test`.`t2_1025`.`b2`) join `test`.`t1_1025` where (`test`.`t1_1025`.`a1` = `materialized subselect`.`group_concat(b1)`)
select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select group_concat(b1) from t2_1025 group by b2);
@@ -956,10 +1002,11 @@
from t1bit
where (a1, a2) in (select b1, b2 from t2bit);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1bit ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t1bit ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 10 test.t1bit.a1,test.t1bit.a2 1 100.00 Using where
2 SUBQUERY t2bit ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 select conv(`test`.`t1bit`.`a1`,10,2) AS `bin(a1)`,conv(`test`.`t1bit`.`a2`,10,2) AS `bin(a2)` from `test`.`t1bit` where <in_optimizer>((`test`.`t1bit`.`a1`,`test`.`t1bit`.`a2`),(`test`.`t1bit`.`a1`,`test`.`t1bit`.`a2`) in ( <materialize> (select `test`.`t2bit`.`b1` AS `b1`,`test`.`t2bit`.`b2` AS `b2` from `test`.`t2bit` ), <primary_index_lookup>(`test`.`t1bit`.`a1` in <temporary table> on distinct_key where ((`test`.`t1bit`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1bit`.`a2` = `materialized subselect`.`b2`)))))
+Note 1003 select conv(`test`.`t1bit`.`a1`,10,2) AS `bin(a1)`,conv(`test`.`t1bit`.`a2`,10,2) AS `bin(a2)` from <materialize> (select `test`.`t2bit`.`b1` AS `b1`,`test`.`t2bit`.`b2` AS `b2` from `test`.`t2bit`) join `test`.`t1bit` where ((`test`.`t1bit`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1bit`.`a2` = `materialized subselect`.`b2`))
select bin(a1), bin(a2)
from t1bit
where (a1, a2) in (select b1, b2 from t2bit);
@@ -1027,10 +1074,11 @@
explain extended
select a from t1 where a in (select c from t2 where d >= 20);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`c`)))))
+Note 1003 select `test`.`t1`.`a` AS `a` from <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20)) join `test`.`t1` where (`materialized subselect`.`c` = `test`.`t1`.`a`)
select a from t1 where a in (select c from t2 where d >= 20);
a
2
@@ -1041,10 +1089,11 @@
explain extended
select a from t1 where a in (select c from t2 where d >= 20);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using where; Using index
+1 PRIMARY t1 index it1a it1a 4 NULL 7 100.00 Using index
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`c`)))))
+Note 1003 select `test`.`t1`.`a` AS `a` from <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20)) join `test`.`t1` where (`materialized subselect`.`c` = `test`.`t1`.`a`)
select a from t1 where a in (select c from t2 where d >= 20);
a
2
@@ -1055,10 +1104,11 @@
explain extended
select a from t1 where a in (select c from t2 where d >= 20);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using where; Using index
+1 PRIMARY t1 index it1a it1a 4 NULL 7 100.00 Using index
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 4 test.t1.a 1 100.00
2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `materialized subselect`.`c`)))))
+Note 1003 select `test`.`t1`.`a` AS `a` from <materialize> (select `test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20)) join `test`.`t1` where (`materialized subselect`.`c` = `test`.`t1`.`a`)
select a from t1 where a in (select c from t2 where d >= 20);
a
2
@@ -1138,10 +1188,12 @@
insert into t1 values (5);
explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
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 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+1 PRIMARY t1 system NULL NULL NULL NULL 1
+1 PRIMARY SUBQUERY#2 const distinct_key distinct_key 5 const 1
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
min(a1)
+NULL
set @@optimizer_switch='default,materialization=off';
explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
id select_type table type possible_keys key key_len ref rows Extra
@@ -1152,17 +1204,27 @@
set @@optimizer_switch='default,semijoin=off';
explain select min(a1) from t1 where 7 in (select b1 from t2);
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 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+1 PRIMARY t1 system NULL NULL NULL NULL 1
+1 PRIMARY SUBQUERY#2 const distinct_key distinct_key 5 const 1
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
select min(a1) from t1 where 7 in (select b1 from t2);
min(a1)
+NULL
set @@optimizer_switch='default,materialization=off';
+# with MariaDB and MWL#90, this particular case is solved:
explain select min(a1) from t1 where 7 in (select b1 from t2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
select min(a1) from t1 where 7 in (select b1 from t2);
min(a1)
NULL
+# but when we go around MWL#90 code, the problem still shows up:
+explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
+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 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
+min(a1)
drop table t1,t2;
create table t1 (a char(2), b varchar(10));
insert into t1 values ('a', 'aaa');
@@ -1170,7 +1232,8 @@
set @@optimizer_switch='default,semijoin=off';
explain select a,b from t1 where b in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 3 test.t1.b 1 Using where
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
select a,b from t1 where b in (select a from t1);
a b
=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result 2010-03-20 12:01:47 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result 2010-04-05 20:16:45 +0000
@@ -1299,31 +1299,34 @@
4
explain extended select * from t2 where t2.a in (select a from t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
+1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 4 100.00
+1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer
2 SUBQUERY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))
+Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1`) join `test`.`t2` where (`test`.`t2`.`a` = `materialized subselect`.`a`)
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
+1 PRIMARY SUBQUERY#2 ALL distinct_key NULL NULL NULL 4 100.00
+1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer
2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))
+Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30)) join `test`.`t2` where (`test`.`t2`.`a` = `materialized subselect`.`a`)
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
3
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
+1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 4 test.t2.a 1 100.00
2 SUBQUERY t3 index PRIMARY PRIMARY 4 NULL 3 100.00 Using index
2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))
+Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`)) join `test`.`t2` where (`materialized subselect`.`a` = `test`.`t2`.`a`)
drop table t1, t2, t3;
create table t1 (a int, b int, index a (a,b));
create table t2 (a int, index a (a));
@@ -1342,31 +1345,34 @@
4
explain extended select * from t2 where t2.a in (select a from t1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.t2.a 1 100.00
2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using index
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))
+Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1`) join `test`.`t2` where (`materialized subselect`.`a` = `test`.`t2`.`a`)
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.t2.a 1 100.00
2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))
+Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30)) join `test`.`t2` where (`materialized subselect`.`a` = `test`.`t2`.`a`)
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
3
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.t2.a 1 100.00
2 SUBQUERY t3 index a a 5 NULL 3 100.00 Using index
2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index; Using join buffer
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))
+Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` = `test`.`t3`.`a`)) join `test`.`t2` where (`materialized subselect`.`a` = `test`.`t2`.`a`)
insert into t1 values (3,31);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
@@ -1379,10 +1385,11 @@
4
explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.t2.a 1 100.00
2 SUBQUERY t1 index NULL a 10 NULL 10005 100.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))
+Note 1003 select `test`.`t2`.`a` AS `a` from <materialize> (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30)) join `test`.`t2` where (`materialized subselect`.`a` = `test`.`t2`.`a`)
drop table t0, t1, t2, t3;
create table t1 (a int, b int);
create table t2 (a int, b int);
@@ -2833,10 +2840,11 @@
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 10 test.t1.one,test.t1.two 1 100.00
2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),(`test`.`t1`.`one`,`test`.`t1`.`two`) in ( <materialize> (select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N') ), <primary_index_lookup>(`test`.`t1`.`one` in <temporary table> on distinct_key where ((`test`.`t1`.`one` = `materialized subselect`.`one`) and (`test`.`t1`.`two` = `materialized subselect`.`two`)))))
+Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from <materialize> (select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N')) join `test`.`t1` where ((`materialized subselect`.`two` = `test`.`t1`.`two`) and (`materialized subselect`.`one` = `test`.`t1`.`one`))
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
@@ -3421,7 +3429,8 @@
EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 9
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
ALTER TABLE t1 ADD INDEX(a);
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
@@ -3432,7 +3441,8 @@
EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
+1 PRIMARY t1 ALL a NULL NULL NULL 9
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 23 test.t1.a,test.t1.b 1
2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
DROP TABLE t1;
create table t1( f1 int,f2 int);
@@ -4206,7 +4216,8 @@
CREATE INDEX I2 ON t1 (b);
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t1 ALL I2 NULL NULL NULL 2
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 2 test.t1.b 1 Using where
2 SUBQUERY t1 index NULL I1 2 NULL 2 Using index
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
a b
@@ -4216,14 +4227,16 @@
CREATE INDEX I2 ON t2 (b);
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t2 ALL I2 NULL NULL NULL 2
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 5 test.t2.b 1 Using where
2 SUBQUERY t2 index NULL I1 4 NULL 2 Using index
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
a b
EXPLAIN
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t1 ALL I2 NULL NULL NULL 2
+1 PRIMARY SUBQUERY#2 eq_ref distinct_key distinct_key 2 test.t1.b 1 Using where
2 SUBQUERY t1 index NULL I1 2 NULL 2 Using where; Using index
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
a b
@@ -4326,16 +4339,18 @@
INSERT INTO t1 VALUES (1),(2);
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
+1 PRIMARY SUBQUERY#2 const distinct_key distinct_key 4 const 1 100.00
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in ( <materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `materialized subselect`.`1`)))))
+Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` where (`materialized subselect`.`1` = 1)
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
+1 PRIMARY SUBQUERY#2 const distinct_key distinct_key 4 const 1 100.00
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in ( <materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `materialized subselect`.`1`)))))
+Note 1003 select 1 AS `1` from <materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a`) join `test`.`t1` where (`materialized subselect`.`1` = 1)
DROP TABLE t1;
#
# Bug#45061: Incorrectly market field caused wrong result.
=== modified file 'mysql-test/suite/parts/r/partition_alter3_innodb.result'
--- a/mysql-test/suite/parts/r/partition_alter3_innodb.result 2008-11-12 17:51:47 +0000
+++ b/mysql-test/suite/parts/r/partition_alter3_innodb.result 2010-04-05 20:16:45 +0000
@@ -141,7 +141,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 7 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -165,7 +165,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 5 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -190,7 +190,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -226,7 +226,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 3 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -248,7 +248,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 4 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -269,7 +269,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -289,7 +289,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 5 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -308,7 +308,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 7 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -326,7 +326,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 10 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -452,7 +452,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 7 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -476,7 +476,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 5 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -504,7 +504,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 3 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -538,7 +538,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -563,7 +563,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 3 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -587,7 +587,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 23 Using where
+1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 10 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -610,7 +610,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 5 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -632,7 +632,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 7 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -653,7 +653,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 10 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
=== modified file 'mysql-test/suite/parts/r/partition_alter3_myisam.result'
--- a/mysql-test/suite/parts/r/partition_alter3_myisam.result 2008-11-12 17:51:47 +0000
+++ b/mysql-test/suite/parts/r/partition_alter3_myisam.result 2010-04-05 20:16:45 +0000
@@ -155,7 +155,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 7 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -187,7 +187,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 5 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -228,7 +228,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 3 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -278,7 +278,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 3 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -312,7 +312,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 4 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -343,7 +343,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -371,7 +371,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 5 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -396,7 +396,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 part1 ALL NULL NULL NULL NULL 7 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -418,7 +418,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 WHERE f_date = '1000-02-10';
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 10 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -552,7 +552,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 7 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -584,7 +584,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 5 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -628,7 +628,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p6 ALL NULL NULL NULL NULL 3 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -676,7 +676,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 4 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -713,7 +713,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 3 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -747,7 +747,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p4 ALL NULL NULL NULL NULL 10 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -778,7 +778,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 5 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -806,7 +806,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 part7 ALL NULL NULL NULL NULL 7 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
@@ -831,7 +831,7 @@
t1.par
EXPLAIN PARTITIONS SELECT COUNT(*) <> 1 FROM t1 WHERE f_int1 = 3;
id select_type table partitions type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 20 Using where
+1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 10 Using where
# check read single success: 1
# check read all success: 1
# check read row by row success: 1
=== modified file 'mysql-test/t/subselect_mat.test'
--- a/mysql-test/t/subselect_mat.test 2010-03-13 20:04:52 +0000
+++ b/mysql-test/t/subselect_mat.test 2010-04-05 20:16:45 +0000
@@ -829,8 +829,12 @@
# This is the only correct result of all four queries. This difference is
# filed as BUG#40037.
set @@optimizer_switch='default,materialization=off';
+-- echo # with MariaDB and MWL#90, this particular case is solved:
explain select min(a1) from t1 where 7 in (select b1 from t2);
select min(a1) from t1 where 7 in (select b1 from t2);
+-- echo # but when we go around MWL#90 code, the problem still shows up:
+explain select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
+select min(a1) from t1 where 7 in (select b1 from t2) or 2> 4;
drop table t1,t2;
#
=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc 2010-03-29 14:04:35 +0000
+++ b/sql/item_subselect.cc 2010-04-05 20:16:45 +0000
@@ -468,6 +468,12 @@
return (res);
}
+int Item_subselect::optimize()
+{
+ int res;
+ res= engine->optimize();
+ return res;
+}
/*
Compute the IN predicate if the left operand's cache changed.
@@ -2088,7 +2094,7 @@
@retval FALSE an execution method was chosen successfully
*/
-bool Item_in_subselect::setup_engine()
+bool Item_in_subselect::setup_engine(bool dont_switch_arena)
{
subselect_hash_sj_engine *new_engine= NULL;
bool res= FALSE;
@@ -2103,7 +2109,7 @@
old_engine= (subselect_single_select_engine*) engine;
- if (arena->is_conventional())
+ if (arena->is_conventional() || dont_switch_arena)
arena= 0;
else
thd->set_n_backup_active_arena(arena, &backup);
@@ -3457,7 +3463,7 @@
bitmap_set_bit(&partial_match_key_parts, i);
++count_partial_match_columns;
}
- }
+ };
}
/* If no column contains NULLs use regular hash index lookups. */
@@ -3970,6 +3976,17 @@
}
+int subselect_hash_sj_engine::optimize()
+{
+ int res= 0;
+ SELECT_LEX *save_select= thd->lex->current_select;
+ thd->lex->current_select= materialize_join->select_lex;
+ res= materialize_join->optimize();
+ thd->lex->current_select= save_select;
+
+ return res;
+}
+
/**
Execute a subquery IN predicate via materialization.
=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h 2010-03-29 14:04:35 +0000
+++ b/sql/item_subselect.h 2010-04-05 20:16:45 +0000
@@ -52,9 +52,9 @@
public:
/* unit of subquery */
st_select_lex_unit *unit;
-protected:
/* engine that perform execution of subselect (single select or union) */
subselect_engine *engine;
+protected:
/* old engine if engine was changed */
subselect_engine *old_engine;
/* cache of used external tables */
@@ -139,6 +139,7 @@
bool mark_as_dependent(THD *thd, st_select_lex *select, Item *item);
void fix_after_pullout(st_select_lex *new_parent, Item **ref);
void recalc_used_tables(st_select_lex *new_parent, bool after_pullout);
+ virtual int optimize();
virtual bool exec();
virtual void fix_length_and_dec();
table_map used_tables() const;
@@ -333,7 +334,9 @@
all JOIN in UNION
*/
Item *expr;
+public:
Item_in_optimizer *optimizer;
+protected:
bool was_null;
bool abort_on_null;
public:
@@ -379,6 +382,10 @@
};
enum_exec_method exec_method;
+ /* JTBM: temporary measure to tell JTBM predicates from SJ predicates */
+ bool convert_to_semi_join;
+ double startup_cost;
+
bool *get_cond_guard(int i)
{
return pushed_cond_guards ? pushed_cond_guards + i : NULL;
@@ -428,7 +435,7 @@
bool fix_fields(THD *thd, Item **ref);
void fix_after_pullout(st_select_lex *new_parent, Item **ref);
void update_used_tables();
- bool setup_engine();
+ bool setup_engine(bool dont_switch_arena);
bool init_left_expr_cache();
/* Inform 'this' that it was computed, and contains a valid result. */
void set_first_execution() { if (first_execution) first_execution= FALSE; }
@@ -502,6 +509,7 @@
THD * get_thd() { return thd; }
virtual int prepare()= 0;
virtual void fix_length_and_dec(Item_cache** row)= 0;
+ virtual int optimize() { DBUG_ASSERT(0); return 0; }
/*
Execute the engine
@@ -734,7 +742,7 @@
class subselect_hash_sj_engine : public subselect_engine
{
-protected:
+public:
/* The table into which the subquery is materialized. */
TABLE *tmp_table;
/* TRUE if the subquery was materialized into a temp table. */
@@ -746,14 +754,16 @@
of subselect_single_select_engine::[prepare | cols].
*/
subselect_single_select_engine *materialize_engine;
+protected:
/* The engine used to compute the IN predicate. */
subselect_engine *lookup_engine;
/*
QEP to execute the subquery and materialize its result into a
temporary table. Created during the first call to exec().
*/
+public:
JOIN *materialize_join;
-
+protected:
/* Keyparts of the only non-NULL composite index in a rowid merge. */
MY_BITMAP non_null_key_parts;
/* Keyparts of the single column indexes with NULL, one keypart per index. */
@@ -766,7 +776,9 @@
IN results because index lookups sometimes match values that are actually
not equal to the search key in SQL terms.
*/
+public:
Item_cond_and *semi_join_conds;
+protected:
/* Possible execution strategies that can be used to compute hash semi-join.*/
enum exec_strategy {
UNDEFINED,
@@ -806,6 +818,7 @@
bool init_runtime();
void cleanup();
int prepare() { return 0; } /* Override virtual function in base class. */
+ int optimize();
int exec();
virtual void print(String *str, enum_query_type query_type);
uint cols()
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2010-03-20 12:01:47 +0000
+++ b/sql/opt_range.cc 2010-04-05 20:16:45 +0000
@@ -2285,7 +2285,7 @@
quick=0;
needed_reg.clear_all();
quick_keys.clear_all();
- if (keys_to_use.is_clear_all())
+ if (keys_to_use.is_clear_all() || head->pos_in_table_list->jtbm_subselect)
DBUG_RETURN(0);
records= head->file->stats.records;
if (!records)
=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc 2010-03-15 19:52:58 +0000
+++ b/sql/opt_subselect.cc 2010-04-05 20:16:45 +0000
@@ -25,6 +25,8 @@
static int subq_sj_candidate_cmp(Item_in_subselect* const *el1,
Item_in_subselect* const *el2);
static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred);
+static bool convert_subq_to_jtbm(JOIN *parent_join,
+ Item_in_subselect *subq_pred, bool *remove);
static TABLE_LIST *alloc_join_nest(THD *thd);
static
void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist);
@@ -166,6 +168,7 @@
(void)subquery_types_allow_materialization(in_subs);
in_subs->emb_on_expr_nest= thd->thd_marker.emb_on_expr_nest;
+ in_subs->convert_to_semi_join= TRUE; //JTBM
/* Register the subquery for further processing in flatten_subqueries() */
select_lex->
@@ -220,10 +223,21 @@
(in_subs->is_top_level_item() ||
optimizer_flag(thd, OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE) ||
optimizer_flag(thd, OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN)) &&//4
- !in_subs->is_correlated && // 5
- in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED) // 6
+ !in_subs->is_correlated) // 5
{
+ if (in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED)
in_subs->exec_method= Item_in_subselect::MATERIALIZATION;
+
+ // psergey-jtbm: "if we're top-level, register for
+ // conversion-to-join-tab".
+ if (in_subs->exec_method == Item_in_subselect::MATERIALIZATION &&
+ thd->thd_marker.emb_on_expr_nest == (TABLE_LIST*)0x1)
+ {
+ in_subs->emb_on_expr_nest= thd->thd_marker.emb_on_expr_nest;
+ in_subs->convert_to_semi_join= FALSE; //JTBM
+ select_lex->outer_select()->
+ join->sj_subselects.append(thd->mem_root, in_subs);//JTBM
+ }
}
Item_subselect::trans_res trans_res;
@@ -338,6 +352,60 @@
}
+static bool make_in_exists_conversion(THD *thd, JOIN *join, Item_in_subselect *item)
+{
+ DBUG_ENTER("make_in_exists_conversion");
+ JOIN *child_join= item->unit->first_select()->join;
+ Item_subselect::trans_res res;
+ item->changed= 0;
+ item->fixed= 0;
+
+ SELECT_LEX *save_select_lex= thd->lex->current_select;
+ thd->lex->current_select= item->unit->first_select();
+
+ res= item->select_transformer(child_join);
+
+ thd->lex->current_select= save_select_lex;
+
+ if (res == Item_subselect::RES_ERROR)
+ DBUG_RETURN(TRUE);
+
+ item->changed= 1;
+ item->fixed= 1;
+
+ Item *substitute= item->substitution;
+ bool do_fix_fields= !item->substitution->fixed;
+ Item **tree= (item->emb_on_expr_nest == (TABLE_LIST*)1)?
+ &join->conds : &(item->emb_on_expr_nest->on_expr);
+
+ Item *replace_me= item;
+ /*
+ JTBM: the subquery was already mapped with Item_in_optimizer, so we
+ should search for that, not for original Item_in_subselect.
+ TODO: what about delaying that rewrite until here?
+ */
+ if (!item->convert_to_semi_join)
+ {
+ replace_me= item->optimizer;
+ }
+
+ if (replace_where_subcondition(join, tree, replace_me, substitute,
+ do_fix_fields))
+ DBUG_RETURN(TRUE);
+ item->substitution= NULL;
+
+ if (!thd->stmt_arena->is_conventional())
+ {
+ tree= (item->emb_on_expr_nest == (TABLE_LIST*)1)?
+ &join->select_lex->prep_where :
+ &(item->emb_on_expr_nest->prep_on_expr);
+
+ if (replace_where_subcondition(join, tree, replace_me, substitute,
+ FALSE))
+ DBUG_RETURN(TRUE);
+ }
+ DBUG_RETURN(FALSE);
+}
/*
Convert semi-join subquery predicates into semi-join join nests
@@ -445,6 +513,7 @@
// #tables-in-parent-query + #tables-in-subquery < MAX_TABLES
/* Replace all subqueries to be flattened with Item_int(1) */
arena= thd->activate_stmt_arena_if_needed(&backup);
+#if 0
for (in_subq= join->sj_subselects.front();
in_subq != in_subq_end &&
join->tables + (*in_subq)->unit->first_select()->join->tables < MAX_TABLES;
@@ -452,18 +521,58 @@
{
Item **tree= ((*in_subq)->emb_on_expr_nest == (TABLE_LIST*)1)?
&join->conds : &((*in_subq)->emb_on_expr_nest->on_expr);
- if (replace_where_subcondition(join, tree, *in_subq, new Item_int(1),
+ Item *replace_me= *in_subq;
+ /*
+ JTBM: the subquery was already mapped with Item_in_optimizer, so we
+ should search for that, not for original Item_in_subselect.
+ TODO: what about delaying that rewrite until here?
+ */
+ if (!(*in_subq)->convert_to_semi_join)
+ {
+ replace_me= (*in_subq)->optimizer;
+ }
+ if (replace_where_subcondition(join, tree, replace_me, new Item_int(1),
FALSE))
DBUG_RETURN(TRUE); /* purecov: inspected */
}
+#endif
for (in_subq= join->sj_subselects.front();
in_subq != in_subq_end &&
join->tables + (*in_subq)->unit->first_select()->join->tables < MAX_TABLES;
in_subq++)
{
- if (convert_subq_to_sj(join, *in_subq))
- DBUG_RETURN(TRUE);
+ bool remove_item= TRUE;
+ //psergey-jtbm: todo: here: check if we should convert to semi-join or
+ // to JTBM nest.
+ if ((*in_subq)->convert_to_semi_join)
+ {
+ if (convert_subq_to_sj(join, *in_subq))
+ DBUG_RETURN(TRUE);
+ }
+ else
+ {
+ if (convert_subq_to_jtbm(join, *in_subq, &remove_item))
+ DBUG_RETURN(TRUE);
+ }
+ if (remove_item)
+ {
+ Item **tree= ((*in_subq)->emb_on_expr_nest == (TABLE_LIST*)1)?
+ &join->conds : &((*in_subq)->emb_on_expr_nest->on_expr);
+ Item *replace_me= *in_subq;
+ /*
+ JTBM: the subquery was already mapped with Item_in_optimizer, so we
+ should search for that, not for original Item_in_subselect.
+ TODO: what about delaying that rewrite until here?
+ */
+ if (!(*in_subq)->convert_to_semi_join)
+ {
+ replace_me= (*in_subq)->optimizer;
+ }
+ if (replace_where_subcondition(join, tree, replace_me, new Item_int(1),
+ FALSE))
+ DBUG_RETURN(TRUE); /* purecov: inspected */
+ }
}
skip_conversion:
/*
@@ -494,7 +603,19 @@
bool do_fix_fields= !(*in_subq)->substitution->fixed;
Item **tree= ((*in_subq)->emb_on_expr_nest == (TABLE_LIST*)1)?
&join->conds : &((*in_subq)->emb_on_expr_nest->on_expr);
- if (replace_where_subcondition(join, tree, *in_subq, substitute,
+
+ Item *replace_me= *in_subq;
+ /*
+ JTBM: the subquery was already mapped with Item_in_optimizer, so we
+ should search for that, not for original Item_in_subselect.
+ TODO: what about delaying that rewrite until here?
+ */
+ if (!(*in_subq)->convert_to_semi_join)
+ {
+ replace_me= (*in_subq)->optimizer;
+ }
+
+ if (replace_where_subcondition(join, tree, replace_me, substitute,
do_fix_fields))
DBUG_RETURN(TRUE);
(*in_subq)->substitution= NULL;
@@ -505,7 +626,7 @@
&join->select_lex->prep_where :
&((*in_subq)->emb_on_expr_nest->prep_on_expr);
- if (replace_where_subcondition(join, tree, *in_subq, substitute,
+ if (replace_where_subcondition(join, tree, replace_me, substitute,
FALSE))
DBUG_RETURN(TRUE);
}
@@ -517,6 +638,36 @@
DBUG_RETURN(FALSE);
}
+
+void get_temptable_params(Item_in_subselect *item, ha_rows *out_rows,
+ ha_rows *scan_time)
+{
+ item->optimize();
+
+ DBUG_ASSERT(item->engine->engine_type() ==
+ subselect_engine::HASH_SJ_ENGINE);
+
+ subselect_hash_sj_engine *hash_sj_engine=
+ ((subselect_hash_sj_engine*)item->engine);
+ JOIN *join= hash_sj_engine->materialize_join;
+
+ double rows= 1;
+ double read_time= 0.0;
+
+ /* Calculate #rows and cost of join execution */
+ for (uint i= join->const_tables; i < join->tables; i++)
+ {
+ rows *= join->best_positions[i].records_read;
+ read_time += join->best_positions[i].read_time;
+ }
+ *out_rows= rows;
+ item->startup_cost= read_time;
+ /* Calculate cost of scanning the temptable */
+ double data_size= rows * hash_sj_engine->tmp_table->s->reclength;
+ /* Do like in handler::read_time */
+ *scan_time= data_size/IO_SIZE + 2;
+}
+
/**
@brief Replaces an expression destructively inside the expression tree of
the WHERE clase.
@@ -887,6 +1038,114 @@
DBUG_RETURN(FALSE);
}
+
+static bool convert_subq_to_jtbm(JOIN *parent_join,
+ Item_in_subselect *subq_pred,
+ bool *remove_item)
+{
+ SELECT_LEX *parent_lex= parent_join->select_lex;
+ List<TABLE_LIST> *emb_join_list= &parent_lex->top_join_list;
+ TABLE_LIST *emb_tbl_nest= NULL; // will change when we learn to handle outer joins
+ TABLE_LIST *tl;//, *last_leaf;
+ DBUG_ENTER("convert_subq_to_jtbm");
+
+ if (subq_pred->setup_engine(TRUE))
+ DBUG_RETURN(TRUE);
+
+ if (subq_pred->engine->engine_type() != subselect_engine::HASH_SJ_ENGINE)
+ {
+ *remove_item= FALSE;
+ make_in_exists_conversion(parent_join->thd, parent_join, subq_pred);
+ DBUG_RETURN(FALSE);
+ }
+ *remove_item= TRUE;
+
+ TABLE_LIST *jtbm;
+ char *tbl_alias;
+ const char alias_mask[]="SUBQUERY#%d";
+ if (!(tbl_alias= (char*)parent_join->thd->calloc(sizeof(alias_mask)+5)) ||
+ !(jtbm= alloc_join_nest(parent_join->thd))) //todo: this is not a join nest!
+ {
+ DBUG_RETURN(TRUE);
+ }
+
+ jtbm->join_list= emb_join_list;
+ jtbm->embedding= emb_tbl_nest;
+ jtbm->alias= (char*)"(jtbm)";
+ jtbm->jtbm_subselect= subq_pred;
+ jtbm->nested_join= NULL;
+
+ /* Nests do not participate in those 'chains', so: */
+ /* jtbm->next_leaf= jtbm->next_local= jtbm->next_global == NULL*/
+ emb_join_list->push_back(jtbm);
+
+ /* Inject ourselves into next-leaf list */
+ /*
+ JTBM: Inject us into next_leaf and lext_local chains..
+ so that make_join_statistics et al find us.
+ */
+
+ /*
+ Reconnect the next_leaf chain.
+ TODO: Do we have to put subquery's tables at the end of the chain?
+ Inserting them at the beginning would be a bit faster.
+ NOTE: We actually insert them at the front! That's because the order is
+ reversed in this list.
+ */
+ for (tl= parent_lex->leaf_tables; tl->next_leaf; tl= tl->next_leaf) ;
+ tl->next_leaf= jtbm;
+
+ /*
+ Same as above for next_local chain
+ (a theory: a next_local chain always starts with ::leaf_tables
+ because view's tables are inserted after the view)
+ */
+ for (tl= parent_lex->leaf_tables; tl->next_local; tl= tl->next_local) ;
+ tl->next_local= jtbm;
+
+ /* A theory: no need to re-connect the next_global chain */
+
+ subselect_hash_sj_engine *hash_sj_engine=
+ ((subselect_hash_sj_engine*)subq_pred->engine);
+ jtbm->table= hash_sj_engine->tmp_table;
+
+ jtbm->table->tablenr= parent_join->tables;
+ jtbm->table->map= table_map(1) << (parent_join->tables);
+
+ parent_join->tables++;
+
+ Item *conds= hash_sj_engine->semi_join_conds;
+ conds->fix_after_pullout(parent_lex, &conds);
+
+ DBUG_EXECUTE("where", print_where(conds,"SJ-EXPR", QT_ORDINARY););
+
+ my_snprintf(tbl_alias, sizeof(alias_mask)+5, alias_mask,
+ hash_sj_engine->materialize_join->select_lex->select_number);
+ jtbm->alias= tbl_alias;
+
+ /* Inject sj_on_expr into the parent's WHERE or ON */
+ if (emb_tbl_nest)
+ {
+ DBUG_ASSERT(0);
+ /*emb_tbl_nest->on_expr= and_items(emb_tbl_nest->on_expr,
+ sj_nest->sj_on_expr);
+ emb_tbl_nest->on_expr->fix_fields(parent_join->thd, &emb_tbl_nest->on_expr);
+ */
+ }
+ else
+ {
+ /* Inject into the WHERE */
+ parent_join->conds= and_items(parent_join->conds, conds);
+ parent_join->conds->fix_fields(parent_join->thd, &parent_join->conds);
+ parent_join->select_lex->where= parent_join->conds;
+ }
+
+ /* Don't unlink the child, the subquery is still there and used */
+
+ DBUG_RETURN(FALSE);
+}
+
+
static TABLE_LIST *alloc_join_nest(THD *thd)
{
TABLE_LIST *tbl;
@@ -3503,4 +3762,26 @@
}
}
+int do_jtbm_materialization_if_needed(JOIN_TAB *tab)
+{
+ Item_in_subselect *in_subs;
+ if (tab->table->pos_in_table_list &&
+ (in_subs= tab->table->pos_in_table_list->jtbm_subselect))
+ {
+ DBUG_ASSERT(in_subs->engine->engine_type() ==
+ subselect_engine::HASH_SJ_ENGINE);
+
+ subselect_hash_sj_engine *hash_sj_engine=
+ ((subselect_hash_sj_engine*)in_subs->engine);
+ if (!hash_sj_engine->is_materialized)
+ {
+ hash_sj_engine->materialize_join->exec();
+ hash_sj_engine->is_materialized= TRUE;
+
+ if (hash_sj_engine->materialize_join->error || tab->join->thd->is_fatal_error)
+ return 1;
+ }
+ }
+ return 0;
+}
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2010-03-20 12:01:47 +0000
+++ b/sql/sql_base.cc 2010-04-05 20:16:45 +0000
@@ -7661,6 +7661,17 @@
if (res)
DBUG_RETURN(1);
}
+ if (table_list->jtbm_subselect)
+ {
+ Item *item= table_list->jtbm_subselect;
+ if (item->fix_fields(thd, &item))
+ {
+ my_error(ER_TOO_MANY_TABLES,MYF(0),MAX_TABLES);
+ DBUG_RETURN(1);
+ }
+ DBUG_ASSERT(item == table_list->jtbm_subselect);
+ table_list->jtbm_subselect->setup_engine(FALSE);
+ }
}
/* Precompute and store the row types of NATURAL/USING joins. */
=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc 2010-03-07 15:41:45 +0000
+++ b/sql/sql_join_cache.cc 2010-04-05 20:16:45 +0000
@@ -33,6 +33,7 @@
#define NO_MORE_RECORDS_IN_BUFFER (uint)(-1)
+int do_jtbm_materialization_if_needed(JOIN_TAB *tab);
/*****************************************************************************
* Join cache module
@@ -1778,6 +1779,10 @@
}
/* Start retrieving all records of the joined table */
+
+ //jtbm-todo: error handling!
+ do_jtbm_materialization_if_needed(join_tab);
+
if ((error= join_init_read_record(join_tab)))
{
rc= error < 0 ? NESTED_LOOP_NO_MORE_ROWS: NESTED_LOOP_ERROR;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-03-29 20:09:40 +0000
+++ b/sql/sql_select.cc 2010-04-05 20:16:45 +0000
@@ -243,6 +243,10 @@
Item_equal *find_item_equal(COND_EQUAL *cond_equal, Field *field,
bool *inherited_fl);
+void get_temptable_params(Item_in_subselect *item, ha_rows *out_rows,
+ ha_rows *scan_time);
+int do_jtbm_materialization_if_needed(JOIN_TAB *tab);
+
/**
This handles SELECT with and without UNION.
*/
@@ -2445,7 +2449,7 @@
{
Item_in_subselect *in_subs= (Item_in_subselect*) subquery_predicate;
if (in_subs->exec_method == Item_in_subselect::MATERIALIZATION &&
- in_subs->setup_engine())
+ in_subs->setup_engine(FALSE))
return TRUE;
}
}
@@ -2531,9 +2535,10 @@
DBUG_ENTER("make_join_statistics");
table_count=join->tables;
- stat=(JOIN_TAB*) join->thd->calloc(sizeof(JOIN_TAB)*table_count);
+
+ stat=(JOIN_TAB*) join->thd->calloc(sizeof(JOIN_TAB)*(table_count));
stat_ref=(JOIN_TAB**) join->thd->alloc(sizeof(JOIN_TAB*)*MAX_TABLES);
- table_vector=(TABLE**) join->thd->alloc(sizeof(TABLE*)*(table_count*2));
+ table_vector=(TABLE**) join->thd->alloc(sizeof(TABLE*)*((table_count)*2));
if (!stat || !stat_ref || !table_vector)
DBUG_RETURN(1); // Eom /* purecov: inspected */
@@ -2542,7 +2547,7 @@
stat_end=stat+table_count;
found_const_table_map= all_table_map=0;
const_count=0;
-
+
for (s= stat, i= 0;
tables;
s++, tables= tables->next_leaf, i++)
@@ -2565,7 +2570,7 @@
table->reginfo.join_tab=s;
table->reginfo.not_exists_optimize=0;
bzero((char*) table->const_key_parts, sizeof(key_part_map)*table->s->keys);
- all_table_map|= table->map;
+ all_table_map|= s->table->map;
s->join=join;
s->info=0; // For describe
@@ -2574,15 +2579,17 @@
if (tables->schema_table)
table->file->stats.records= 2;
table->quick_condition_rows= table->file->stats.records;
-
+
s->on_expr_ref= &tables->on_expr;
if (*s->on_expr_ref)
{
/* s is the only inner table of an outer join */
#ifdef WITH_PARTITION_STORAGE_ENGINE
- if ((!table->file->stats.records || table->no_partitions_used) && !embedding)
+ if (!table->pos_in_table_list->jtbm_subselect &&
+ (!table->file->stats.records || table->no_partitions_used) && !embedding)
#else
- if (!table->file->stats.records && !embedding)
+ if (!table->pos_in_table_list->jtbm_subselect &&
+ !table->file->stats.records && !embedding)
#endif
{ // Empty table
s->dependent= 0; // Ignore LEFT JOIN depend.
@@ -2616,7 +2623,8 @@
#else
const bool no_partitions_used= FALSE;
#endif
- if ((table->s->system || table->file->stats.records <= 1 ||
+ if (!table->pos_in_table_list->jtbm_subselect &&
+ (table->s->system || table->file->stats.records <= 1 ||
no_partitions_used) &&
!s->dependent &&
(table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) &&
@@ -2626,6 +2634,8 @@
no_rows_const_tables |= table->map;
}
}
+ //psergey-todo: inject jtbm JOIN_TABS here.
+
stat_vector[i]=0;
join->outer_join=outer_join;
@@ -2714,6 +2724,9 @@
{
table=s->table;
+ if (table->pos_in_table_list->jtbm_subselect)
+ continue;
+
/*
If equi-join condition by a key is null rejecting and after a
substitution of a const table the key value happens to be null
@@ -2873,8 +2886,21 @@
continue;
}
/* Approximate found rows and time to read them */
- s->found_records=s->records=s->table->file->stats.records;
- s->read_time=(ha_rows) s->table->file->scan_time();
+
+ if (s->table->pos_in_table_list->jtbm_subselect)
+ {
+ get_temptable_params(s->table->pos_in_table_list->jtbm_subselect,
+ &s->records,
+ &s->read_time);
+ s->found_records= s->records;
+ table->quick_condition_rows=s->records;
+ }
+ else
+ {
+ s->found_records=s->records=s->table->file->stats.records;
+ s->read_time=(ha_rows) s->table->file->scan_time();
+ }
+
/*
Set a max range of how many seeks we can expect when using keys
@@ -2897,10 +2923,11 @@
Don't do range analysis if we're on the inner side of an outer join (2).
Do range analysis if we're on the inner side of a semi-join (3).
*/
- if (!s->const_keys.is_clear_all() && // (1)
- (!s->table->pos_in_table_list->embedding || // (2)
- (s->table->pos_in_table_list->embedding && // (3)
- s->table->pos_in_table_list->embedding->sj_on_expr))) // (3)
+ if (!s->const_keys.is_clear_all() && // (1)
+ (!s->table->pos_in_table_list->embedding || // (2)
+ (s->table->pos_in_table_list->embedding && // (3)
+ s->table->pos_in_table_list->embedding->sj_on_expr)) && // (3)
+ !s->table->pos_in_table_list->jtbm_subselect)
{
ha_rows records;
SQL_SELECT *select;
@@ -4265,6 +4292,8 @@
double tmp;
ha_rows rec;
bool best_uses_jbuf= FALSE;
+ Item_in_subselect* jtbm_subselect= s->table->pos_in_table_list->jtbm_subselect;
+ bool jtbm_ref_used= FALSE;
Loose_scan_opt loose_scan_opt;
DBUG_ENTER("best_access_path");
@@ -4617,8 +4646,9 @@
else
tmp= best_time; // Do nothing
}
+ if (jtbm_subselect)
+ tmp += jtbm_subselect->startup_cost;
loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp);
-
} /* not ft_key */
if (tmp < best_time - records/(double) TIME_FOR_COMPARE)
{
@@ -4628,6 +4658,8 @@
best_key= start_key;
best_max_key_part= max_key_part;
best_ref_depends_map= found_ref;
+ if (jtbm_subselect)
+ jtbm_ref_used= TRUE;
}
} /* for each key */
records= best_records;
@@ -4666,7 +4698,8 @@
best_max_key_part >= s->table->quick_key_parts[best_key->key]) &&// (2)
!((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) && // (3)
! s->table->covering_keys.is_clear_all() && best_key && !s->quick) &&// (3)
- !(s->table->force_index && best_key && !s->quick)) // (4)
+ !(s->table->force_index && best_key && !s->quick) && // (4)
+ !jtbm_ref_used)
{ // Check full join
ha_rows rnd_records= s->found_records;
/*
@@ -4714,7 +4747,11 @@
else
{
/* Estimate cost of reading table. */
- tmp= s->table->file->scan_time();
+ if (jtbm_subselect)
+ tmp= s->read_time;
+ else
+ tmp= s->table->file->scan_time();
+
if ((s->table->map & join->outer_join) || disable_jbuf) // Can't use join cache
{
/*
@@ -4743,6 +4780,8 @@
}
}
+ if (jtbm_subselect)
+ tmp += jtbm_subselect->startup_cost;
/*
We estimate the cost of evaluating WHERE clause for found records
as record_count * rnd_records / TIME_FOR_COMPARE. This cost plus
@@ -7684,6 +7723,12 @@
table->file->extra(HA_EXTRA_NO_KEYREAD);
}
table->file->ha_index_or_rnd_end();
+ //psergey-jtbm2:
+ if (table->pos_in_table_list &&
+ table->pos_in_table_list->jtbm_subselect)
+ {
+ table->pos_in_table_list->jtbm_subselect->cleanup();
+ }
/*
We need to reset this for next select
(Tested in part_of_refkey)
@@ -11396,6 +11441,8 @@
share->keys=1;
share->uniques= test(using_unique_constraint);
table->key_info= table->s->key_info= keyinfo;
+ table->keys_in_use_for_query.set_bit(0);
+ share->keys_in_use.set_bit(0);
keyinfo->key_part=key_part_info;
keyinfo->flags=HA_NOSAME;
keyinfo->usable_key_parts=keyinfo->key_parts= param->group_parts;
@@ -11411,6 +11458,8 @@
bool maybe_null=(*cur_group->item)->maybe_null;
key_part_info->null_bit=0;
key_part_info->field= field;
+ if (cur_group == group)
+ field->key_start.set_bit(0);
key_part_info->offset= field->offset(table->record[0]);
key_part_info->length= (uint16) field->key_length();
key_part_info->type= (uint8) field->key_type();
@@ -11481,6 +11530,8 @@
keyinfo->key_parts * sizeof(KEY_PART_INFO))))
goto err;
bzero((void*) key_part_info, keyinfo->key_parts * sizeof(KEY_PART_INFO));
+ table->keys_in_use_for_query.set_bit(0);
+ share->keys_in_use.set_bit(0);
table->key_info= table->s->key_info= keyinfo;
keyinfo->key_part=key_part_info;
keyinfo->flags=HA_NOSAME | HA_NULL_ARE_EQUAL;
@@ -11519,6 +11570,14 @@
{
key_part_info->null_bit=0;
key_part_info->field= *reg_field;
+ //psergey-jtbm:
+ (*reg_field)->flags |= PART_KEY_FLAG;
+ if (key_part_info == keyinfo->key_part)
+ (*reg_field)->key_start.set_bit(0);
+ key_part_info->null_bit= (*reg_field)->null_bit;
+ key_part_info->null_offset= (uint) ((*reg_field)->null_ptr -
+ (uchar*) table->record[0]);
+
key_part_info->offset= (*reg_field)->offset(table->record[0]);
key_part_info->length= (uint16) (*reg_field)->pack_length();
/* TODO:
@@ -12804,7 +12863,9 @@
join_tab->last_inner->first_unmatched= join_tab;
}
join->thd->row_count= 0;
-
+
+ //jtbm-todo: error handling!
+ do_jtbm_materialization_if_needed(join_tab);
error= (*join_tab->read_first_record)(join_tab);
if (join_tab->keep_current_rowid)
@@ -18169,7 +18230,7 @@
if (tab->select && tab->select->quick)
examined_rows= tab->select->quick->records;
else if (tab->type == JT_NEXT || tab->type == JT_ALL)
- examined_rows= tab->limit ? tab->limit : tab->table->file->records();
+ examined_rows= tab->limit ? tab->limit : tab->records;
else
examined_rows=(ha_rows)join->best_positions[i].records_read;
@@ -18658,6 +18719,14 @@
print_join(thd, eliminated_tables, str, &nested_join->join_list, query_type);
str->append(')');
}
+ else if (jtbm_subselect)
+ {
+ str->append(STRING_WITH_LEN(" <materialize> ("));
+ subselect_hash_sj_engine *hash_engine;
+ hash_engine= (subselect_hash_sj_engine*)jtbm_subselect->engine;
+ hash_engine->materialize_engine->print(str, query_type);
+ str->append(')');
+ }
else
{
const char *cmp_name; // Name to compare with alias
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2010-03-20 12:01:47 +0000
+++ b/sql/sql_select.h 2010-04-05 20:16:45 +0000
@@ -1551,7 +1551,18 @@
bool union_part; ///< this subselect is part of union
bool optimized; ///< flag to avoid double optimization in EXPLAIN
+ /*
+ Subqueries that will need to be converted to semi-join nests (the list
+ is emptied when conversion is done
+ */
Array<Item_in_subselect> sj_subselects;
+
+ /*
+ Subqueries that will need to be converted to JOIN_TABs
+ (Note this is different from the above in the respect that it's part
+ of WHERE clause or something like that?)
+ */
+ //Array<Item_in_subselect> jtbm_subselects;
/* Temporary tables used to weed-out semi-join duplicates */
List<TABLE> sj_tmp_tables;
@@ -1575,6 +1586,7 @@
JOIN(THD *thd_arg, List<Item> &fields_arg, ulonglong select_options_arg,
select_result *result_arg)
:fields_list(fields_arg), sj_subselects(thd_arg->mem_root, 4)
+ //jtbm_subselects(thd_arg->mem_root, 4)
{
init(thd_arg, fields_arg, select_options_arg, result_arg);
}
=== modified file 'sql/table.h'
--- a/sql/table.h 2010-03-20 12:01:47 +0000
+++ b/sql/table.h 2010-04-05 20:16:45 +0000
@@ -1194,6 +1194,7 @@
/* Number of IN-compared expressions */
uint sj_in_exprs;
Item_in_subselect *sj_subq_pred;
+ Item_in_subselect *jtbm_subselect;
SJ_MATERIALIZATION_INFO *sj_mat_info;
/*
