summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/ctype_binary.result2
-rw-r--r--mysql-test/r/ctype_cp1251.result2
-rw-r--r--mysql-test/r/ctype_latin1.result2
-rw-r--r--mysql-test/r/ctype_ucs.result2
-rw-r--r--mysql-test/r/ctype_utf8.result2
-rw-r--r--mysql-test/r/distinct.result20
-rw-r--r--mysql-test/r/func_in.result6
-rw-r--r--mysql-test/r/mysqld--help.result5
-rw-r--r--mysql-test/r/old-mode.result13
-rw-r--r--mysql-test/r/partition_pruning.result80
-rw-r--r--mysql-test/r/ps_1general.result2
-rw-r--r--mysql-test/r/subselect.result4
-rw-r--r--mysql-test/r/subselect2.result4
-rw-r--r--mysql-test/r/subselect_no_exists_to_in.result4
-rw-r--r--mysql-test/r/subselect_no_mat.result4
-rw-r--r--mysql-test/r/subselect_no_opts.result4
-rw-r--r--mysql-test/r/subselect_no_scache.result4
-rw-r--r--mysql-test/r/subselect_no_semijoin.result4
-rw-r--r--mysql-test/r/type_datetime.result6
-rw-r--r--mysql-test/r/type_time_6065.result48
20 files changed, 118 insertions, 100 deletions
diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result
index 550c47ccdaa..995ae7321b7 100644
--- a/mysql-test/r/ctype_binary.result
+++ b/mysql-test/r/ctype_binary.result
@@ -2769,7 +2769,7 @@ id select_type table type possible_keys key key_len ref rows Extra
ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range date_column date_column 9 NULL 1 Using index condition
+1 SIMPLE t1 range date_column date_column 6 NULL 1 Using index condition
DROP TABLE t1;
#
# Bug #31384 DATE_ADD() and DATE_SUB() return binary data
diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result
index f7952a18f58..33587b40f6b 100644
--- a/mysql-test/r/ctype_cp1251.result
+++ b/mysql-test/r/ctype_cp1251.result
@@ -3161,7 +3161,7 @@ id select_type table type possible_keys key key_len ref rows Extra
ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range date_column date_column 9 NULL 1 Using index condition
+1 SIMPLE t1 range date_column date_column 6 NULL 1 Using index condition
DROP TABLE t1;
#
# Bug #31384 DATE_ADD() and DATE_SUB() return binary data
diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result
index fac9824401f..f731b1001a7 100644
--- a/mysql-test/r/ctype_latin1.result
+++ b/mysql-test/r/ctype_latin1.result
@@ -3445,7 +3445,7 @@ id select_type table type possible_keys key key_len ref rows Extra
ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range date_column date_column 9 NULL 1 Using index condition
+1 SIMPLE t1 range date_column date_column 6 NULL 1 Using index condition
DROP TABLE t1;
#
# Bug #31384 DATE_ADD() and DATE_SUB() return binary data
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index 3cfc076b8a0..89081077fc6 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -4352,7 +4352,7 @@ id select_type table type possible_keys key key_len ref rows Extra
ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range date_column date_column 9 NULL 1 Using index condition
+1 SIMPLE t1 range date_column date_column 6 NULL 1 Using index condition
DROP TABLE t1;
#
# Bug #31384 DATE_ADD() and DATE_SUB() return binary data
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index 43f3aa4b6c9..8e13b1bd79b 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -5220,7 +5220,7 @@ id select_type table type possible_keys key key_len ref rows Extra
ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range date_column date_column 9 NULL 1 Using index condition
+1 SIMPLE t1 range date_column date_column 6 NULL 1 Using index condition
DROP TABLE t1;
#
# Bug #31384 DATE_ADD() and DATE_SUB() return binary data
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index bd7deee43e9..34c702e15f3 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -925,9 +925,6 @@ set join_buffer_size=1024;
SELECT STRAIGHT_JOIN DISTINCT t1.id FROM
t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
id
-7
-8
-9
18
20
24
@@ -937,34 +934,37 @@ id
51
61
64
+7
71
74
77
78
+8
+9
93
94
set join_buffer_size=1024*16;
SELECT STRAIGHT_JOIN DISTINCT t1.id FROM
t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
id
-7
-9
18
20
24
-8
+43
+45
50
51
61
-43
-45
-71
64
+7
+71
74
77
78
-94
+8
+9
93
+94
set join_buffer_size=default;
SELECT STRAIGHT_JOIN DISTINCT t1.id FROM
t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index fc56660ac62..51817c24932 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -271,7 +271,7 @@ select * from t2 where a NOT IN (
'2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00',
'2006-04-25 10:06:00', '2006-04-25 10:08:00');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range a a 9 NULL 18 Using index condition
+1 SIMPLE t2 range a a 6 NULL 12 Using index condition
select * from t2 where a NOT IN (
'2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00',
'2006-04-25 10:06:00', '2006-04-25 10:08:00');
@@ -701,11 +701,11 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN SELECT * FROM t1 WHERE c_datetime
IN ('2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using index condition
+1 SIMPLE t1 range c_datetime c_datetime 5 NULL 3 Using index condition
EXPLAIN SELECT * FROM t1 WHERE c_datetime
IN (NULL, '2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range c_datetime c_datetime 8 NULL 3 Using index condition
+1 SIMPLE t1 range c_datetime c_datetime 5 NULL 3 Using index condition
EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
index d7ecd0fda5a..929813d303b 100644
--- a/mysql-test/r/mysqld--help.result
+++ b/mysql-test/r/mysqld--help.result
@@ -479,6 +479,10 @@ The following options may be given as the first argument:
NULLS_UNEQUAL (default behavior for 4.1 and later),
NULLS_EQUAL (emulate 4.0 behavior), and NULLS_IGNORED
--myisam-use-mmap Use memory mapping for reading and writing MyISAM tables
+ --mysql56-temporal-format
+ Use MySQL-5.6 (instead of MariaDB-5.3) format for TIME,
+ DATETIME, TIMESTAMP columns.
+ (Defaults to on; use --skip-mysql56-temporal-format to disable.)
--net-buffer-length=#
Buffer length for TCP/IP and socket communication
--net-read-timeout=#
@@ -1226,6 +1230,7 @@ myisam-repair-threads 1
myisam-sort-buffer-size 134216704
myisam-stats-method NULLS_UNEQUAL
myisam-use-mmap FALSE
+mysql56-temporal-format TRUE
net-buffer-length 16384
net-read-timeout 30
net-retry-count 10
diff --git a/mysql-test/r/old-mode.result b/mysql-test/r/old-mode.result
index 4f650c3c781..c2ee3324ede 100644
--- a/mysql-test/r/old-mode.result
+++ b/mysql-test/r/old-mode.result
@@ -104,6 +104,7 @@ DROP TABLE t1;
#
# MDEV-6649 Different warnings for TIME and TIME(N) when @@old_mode=zero_date_time_cast
#
+SET @@global.mysql56_temporal_format=true;
SET @@old_mode=zero_date_time_cast;
CREATE TABLE t1 (a TIME,b TIME(1));
INSERT INTO t1 VALUES (TIME'830:20:30',TIME'830:20:30');
@@ -114,3 +115,15 @@ Warnings:
Warning 1264 Out of range value for column 'a' at row 1
Warning 1264 Out of range value for column 'b' at row 1
DROP TABLE t1;
+SET @@global.mysql56_temporal_format=false;
+SET @@old_mode=zero_date_time_cast;
+CREATE TABLE t1 (a TIME,b TIME(1));
+INSERT INTO t1 VALUES (TIME'830:20:30',TIME'830:20:30');
+SELECT TO_DAYS(a), TO_DAYS(b) FROM t1;
+TO_DAYS(a) TO_DAYS(b)
+NULL NULL
+Warnings:
+Warning 1264 Out of range value for column 'a' at row 1
+Warning 1264 Out of range value for column 'b' at row 1
+DROP TABLE t1;
+SET @@global.mysql56_temporal_format=DEFAULT;
diff --git a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result
index 930a383786e..c6d7c383ce7 100644
--- a/mysql-test/r/partition_pruning.result
+++ b/mysql-test/r/partition_pruning.result
@@ -1636,139 +1636,139 @@ INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
(1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07');
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,p20090402 index NULL PRIMARY 12 NULL 6 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 NULL 6 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,p20090402,p20090403 index NULL PRIMARY 12 NULL 8 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 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 2 Using where; Using index
+1 SIMPLE t1 p20090403 index NULL PRIMARY 9 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 10 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 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 10 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 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 6 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 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 6 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 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 3 Using where; Using index
+1 SIMPLE t1 p20090402 index NULL PRIMARY 9 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
-1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 13 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 12 NULL 10 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 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 6 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 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 8 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 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 2 Using where; Using index
+1 SIMPLE t1 p20090403 index NULL PRIMARY 9 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 10 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 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 10 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 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 6 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 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 8 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 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 2 Using where; Using index
+1 SIMPLE t1 p20090403 index NULL PRIMARY 9 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 10 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 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 10 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 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 6 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 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 6 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 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 3 Using where; Using index
+1 SIMPLE t1 p20090402 index NULL PRIMARY 9 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
+1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 9 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 10 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 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 6 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 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 8 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 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 2 Using where; Using index
+1 SIMPLE t1 p20090403 index NULL PRIMARY 9 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 10 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 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 10 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 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 8 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 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 8 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403 index NULL PRIMARY 9 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 2 Using where; Using index
+1 SIMPLE t1 p20090403 index NULL PRIMARY 9 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 10 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 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 10 Using where; Using index
+1 SIMPLE t1 p20090401,p20090403,p20090404,p20090405 index NULL PRIMARY 9 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 6 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 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 6 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402 index NULL PRIMARY 9 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 3 Using where; Using index
+1 SIMPLE t1 p20090402 index NULL PRIMARY 9 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
-1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 13 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,p20090403,p20090404,p20090405 index NULL PRIMARY 12 NULL 13 Using where; Using index
+1 SIMPLE t1 p20090401,p20090402,p20090403,p20090404,p20090405 index NULL PRIMARY 9 NULL 13 Using where; Using index
DROP TABLE t1;
# Test with DATE column NOT NULL
CREATE TABLE t1 (
diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result
index 9981156bc5f..3ce52cb1595 100644
--- a/mysql-test/r/ps_1general.result
+++ b/mysql-test/r/ps_1general.result
@@ -300,7 +300,7 @@ t2 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # # latin1_swedish_ci NULL
prepare stmt4 from ' show table status from test like ''t9%'' ';
execute stmt4;
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
-t9 MyISAM 10 Dynamic 2 216 432 # 2048 0 NULL # # # latin1_swedish_ci NULL
+t9 MyISAM 10 Dynamic 2 212 424 # 2048 0 NULL # # # latin1_swedish_ci NULL
prepare stmt4 from ' show status like ''Threads_running'' ';
execute stmt4;
Variable_name Value
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 256896a645e..3732f62d5e5 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -3698,8 +3698,8 @@ WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
ORDER BY t1.t DESC LIMIT 1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1
-1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
-2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
+1 PRIMARY t1 index NULL PRIMARY 13 NULL 11 Using where; Using index
+2 SUBQUERY t1 range PRIMARY PRIMARY 13 NULL 5 Using where; Using index
SELECT * FROM t1,t2
WHERE t1.t = (SELECT t1.t FROM t1
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result
index 034cba58c8f..75e8c084026 100644
--- a/mysql-test/r/subselect2.result
+++ b/mysql-test/r/subselect2.result
@@ -287,7 +287,7 @@ ORDER BY mirror_date ASC
) AS calculated_result;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
-2 DERIVED t1 range date date 9 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort
+2 DERIVED t1 range date date 6 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort
SELECT * FROM (
SELECT node_uid, date, mirror_date, @result := 0 AS result
FROM t1
@@ -310,7 +310,7 @@ ORDER BY mirror_date ASC
) AS calculated_result;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
-2 DERIVED t1 range date date 9 NULL 2 Using index condition; Using where; Using filesort
+2 DERIVED t1 range date date 6 NULL 2 Using index condition; Using where; Using filesort
SELECT * FROM (
SELECT node_uid, date, mirror_date, @result := 0 AS result
FROM t1
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index 5585e905431..140b7902660 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -3701,8 +3701,8 @@ WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
ORDER BY t1.t DESC LIMIT 1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1
-1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
-2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
+1 PRIMARY t1 index NULL PRIMARY 13 NULL 11 Using where; Using index
+2 SUBQUERY t1 range PRIMARY PRIMARY 13 NULL 5 Using where; Using index
SELECT * FROM t1,t2
WHERE t1.t = (SELECT t1.t FROM t1
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 249363d8944..99ac9f4b300 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -3701,8 +3701,8 @@ WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
ORDER BY t1.t DESC LIMIT 1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1
-1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
-2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
+1 PRIMARY t1 index NULL PRIMARY 13 NULL 11 Using where; Using index
+2 SUBQUERY t1 range PRIMARY PRIMARY 13 NULL 5 Using where; Using index
SELECT * FROM t1,t2
WHERE t1.t = (SELECT t1.t FROM t1
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 870d1ebfb84..7ecd40c3863 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -3697,8 +3697,8 @@ WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
ORDER BY t1.t DESC LIMIT 1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1
-1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
-2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
+1 PRIMARY t1 index NULL PRIMARY 13 NULL 11 Using where; Using index
+2 SUBQUERY t1 range PRIMARY PRIMARY 13 NULL 5 Using where; Using index
SELECT * FROM t1,t2
WHERE t1.t = (SELECT t1.t FROM t1
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 7420811b5a6..c70db6f8cc0 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -3704,8 +3704,8 @@ WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
ORDER BY t1.t DESC LIMIT 1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1
-1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
-2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
+1 PRIMARY t1 index NULL PRIMARY 13 NULL 11 Using where; Using index
+2 SUBQUERY t1 range PRIMARY PRIMARY 13 NULL 5 Using where; Using index
SELECT * FROM t1,t2
WHERE t1.t = (SELECT t1.t FROM t1
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 16c6d7383e9..6a7d6b3a81c 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -3697,8 +3697,8 @@ WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
ORDER BY t1.t DESC LIMIT 1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1
-1 PRIMARY t1 index NULL PRIMARY 16 NULL 11 Using where; Using index
-2 SUBQUERY t1 range PRIMARY PRIMARY 16 NULL 5 Using where; Using index
+1 PRIMARY t1 index NULL PRIMARY 13 NULL 11 Using where; Using index
+2 SUBQUERY t1 range PRIMARY PRIMARY 13 NULL 5 Using where; Using index
SELECT * FROM t1,t2
WHERE t1.t = (SELECT t1.t FROM t1
WHERE t1.t < t2.t AND t1.i2=1 AND t2.i1=t1.i1
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 82b64d30d96..fc1946b3e6f 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -103,7 +103,7 @@ date numfacture expedition
0000-00-00 00:00:00 1212 0001-00-00 00:00:00
EXPLAIN SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref expedition expedition 8 const 1
+1 SIMPLE t1 ref expedition expedition 5 const 1
drop table t1;
create table t1 (a datetime not null, b datetime not null);
insert into t1 values (now(), now());
@@ -745,7 +745,7 @@ SET NAMES CP850;
INSERT INTO t1 VALUES (1,'2010-04-12 22:30:12'), (2,'2010-04-12 22:30:12'), (3,'2010-04-12 22:30:12');
EXPLAIN EXTENDED SELECT * FROM t1 FORCE INDEX(attime) WHERE AtTime = '2010-02-22 18:40:07';
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ref AtTime AtTime 9 const 1 100.00
+1 SIMPLE t1 ref AtTime AtTime 6 const 1 100.00
Warnings:
Note 1003 select `test`.`t1`.`Id` AS `Id`,`test`.`t1`.`AtTime` AS `AtTime` from `test`.`t1` FORCE INDEX (`attime`) where (`test`.`t1`.`AtTime` = '2010-02-22 18:40:07')
DROP TABLE t1;
@@ -773,7 +773,7 @@ f1
t2 should be const
explain select * from t2 where f1=STR_TO_DATE('4/1/2010', '%m/%d/%Y');
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 const PRIMARY PRIMARY 8 const 1 Using index
+1 SIMPLE t2 const PRIMARY PRIMARY 5 const 1 Using index
DROP TABLE t1,t2;
#
# Bug#57095: Wrongly chosen expression cache type led to a wrong
diff --git a/mysql-test/r/type_time_6065.result b/mysql-test/r/type_time_6065.result
index db3efc3bcbb..a61c658d50e 100644
--- a/mysql-test/r/type_time_6065.result
+++ b/mysql-test/r/type_time_6065.result
@@ -52,7 +52,7 @@ t2 force INDEX (col_datetime_key)
WHERE col_time_key = col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where
-1 SIMPLE t2 ref col_datetime_key col_datetime_key 9 test.t1.col_time_key 1 100.00 Using where; Using index
+1 SIMPLE t2 ref col_datetime_key col_datetime_key 6 test.t1.col_time_key 1 100.00 Using where; Using index
Warnings:
Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`)
SELECT * FROM
@@ -73,7 +73,7 @@ t2 force INDEX (col_datetime_key)
WHERE col_datetime_key = col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where
-1 SIMPLE t2 ref col_datetime_key col_datetime_key 9 test.t1.col_time_key 1 100.00 Using where; Using index
+1 SIMPLE t2 ref col_datetime_key col_datetime_key 6 test.t1.col_time_key 1 100.00 Using where; Using index
Warnings:
Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` IGNORE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`)
SELECT * FROM
@@ -136,7 +136,7 @@ t2 force INDEX (col_datetime_key)
WHERE col_time_key = col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using where; Using index
-1 SIMPLE t2 ref col_datetime_key col_datetime_key 9 test.t1.col_time_key 1 100.00 Using where; Using index
+1 SIMPLE t2 ref col_datetime_key col_datetime_key 6 test.t1.col_time_key 1 100.00 Using where; Using index
Warnings:
Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`)
SELECT * FROM
@@ -157,7 +157,7 @@ t2 force INDEX (col_datetime_key)
WHERE col_datetime_key = col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index col_time_key col_time_key 4 NULL 5 100.00 Using where; Using index
-1 SIMPLE t2 ref col_datetime_key col_datetime_key 9 test.t1.col_time_key 1 100.00 Using where; Using index
+1 SIMPLE t2 ref col_datetime_key col_datetime_key 6 test.t1.col_time_key 1 100.00 Using where; Using index
Warnings:
Note 1003 select `test`.`t1`.`col_time_key` AS `col_time_key`,`test`.`t2`.`col_datetime_key` AS `col_datetime_key` from `test`.`t1` FORCE INDEX (`col_time_key`) straight_join `test`.`t2` FORCE INDEX (`col_datetime_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`)
SELECT * FROM
@@ -261,7 +261,7 @@ STRAIGHT_JOIN
t1 ignore INDEX (col_time_key)
WHERE col_time_key = col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`)
@@ -282,7 +282,7 @@ STRAIGHT_JOIN
t1 ignore INDEX (col_time_key)
WHERE col_datetime_key = col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`)
@@ -303,7 +303,7 @@ STRAIGHT_JOIN
t1 force INDEX (col_time_key)
WHERE col_time_key = col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using where; Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using where; Using index
1 SIMPLE t1 ref col_time_key col_time_key 4 test.t2.col_datetime_key 2 100.00 Using where; Using index
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` = `test`.`t2`.`col_datetime_key`)
@@ -324,7 +324,7 @@ STRAIGHT_JOIN
t1 force INDEX (col_time_key)
WHERE col_datetime_key = col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using where; Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using where; Using index
1 SIMPLE t1 ref col_time_key col_time_key 4 test.t2.col_datetime_key 2 100.00 Using where; Using index
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` = `test`.`t1`.`col_time_key`)
@@ -717,7 +717,7 @@ STRAIGHT_JOIN
t1 ignore INDEX (col_time_key)
WHERE col_time_key >= col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`)
@@ -748,7 +748,7 @@ STRAIGHT_JOIN
t1 ignore INDEX (col_time_key)
WHERE col_datetime_key >= col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`)
@@ -779,7 +779,7 @@ STRAIGHT_JOIN
t1 force INDEX (col_time_key)
WHERE col_time_key >= col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` >= `test`.`t2`.`col_datetime_key`)
@@ -810,7 +810,7 @@ STRAIGHT_JOIN
t1 force INDEX (col_time_key)
WHERE col_datetime_key >= col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` >= `test`.`t1`.`col_time_key`)
@@ -1153,7 +1153,7 @@ STRAIGHT_JOIN
t1 ignore INDEX (col_time_key)
WHERE col_time_key > col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`)
@@ -1179,7 +1179,7 @@ STRAIGHT_JOIN
t1 ignore INDEX (col_time_key)
WHERE col_datetime_key > col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`)
@@ -1205,7 +1205,7 @@ STRAIGHT_JOIN
t1 force INDEX (col_time_key)
WHERE col_time_key > col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` > `test`.`t2`.`col_datetime_key`)
@@ -1231,7 +1231,7 @@ STRAIGHT_JOIN
t1 force INDEX (col_time_key)
WHERE col_datetime_key > col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` > `test`.`t1`.`col_time_key`)
@@ -1629,7 +1629,7 @@ STRAIGHT_JOIN
t1 ignore INDEX (col_time_key)
WHERE col_time_key <= col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`)
@@ -1660,7 +1660,7 @@ STRAIGHT_JOIN
t1 ignore INDEX (col_time_key)
WHERE col_datetime_key <= col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`)
@@ -1691,7 +1691,7 @@ STRAIGHT_JOIN
t1 force INDEX (col_time_key)
WHERE col_time_key <= col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` <= `test`.`t2`.`col_datetime_key`)
@@ -1722,7 +1722,7 @@ STRAIGHT_JOIN
t1 force INDEX (col_time_key)
WHERE col_datetime_key <= col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` <= `test`.`t1`.`col_time_key`)
@@ -2065,7 +2065,7 @@ STRAIGHT_JOIN
t1 ignore INDEX (col_time_key)
WHERE col_time_key < col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`)
@@ -2091,7 +2091,7 @@ STRAIGHT_JOIN
t1 ignore INDEX (col_time_key)
WHERE col_datetime_key < col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` IGNORE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`)
@@ -2117,7 +2117,7 @@ STRAIGHT_JOIN
t1 force INDEX (col_time_key)
WHERE col_time_key < col_datetime_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t1`.`col_time_key` < `test`.`t2`.`col_datetime_key`)
@@ -2143,7 +2143,7 @@ STRAIGHT_JOIN
t1 force INDEX (col_time_key)
WHERE col_datetime_key < col_time_key;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 index col_datetime_key col_datetime_key 9 NULL 5 100.00 Using index
+1 SIMPLE t2 index col_datetime_key col_datetime_key 6 NULL 5 100.00 Using index
1 SIMPLE t1 ALL col_time_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x1)
Warnings:
Note 1003 select `test`.`t2`.`col_datetime_key` AS `col_datetime_key`,`test`.`t1`.`col_time_key` AS `col_time_key` from `test`.`t2` FORCE INDEX (`col_datetime_key`) straight_join `test`.`t1` FORCE INDEX (`col_time_key`) where (`test`.`t2`.`col_datetime_key` < `test`.`t1`.`col_time_key`)