summaryrefslogtreecommitdiff
path: root/mysql-test/main/range.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/range.result')
-rw-r--r--mysql-test/main/range.result83
1 files changed, 44 insertions, 39 deletions
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result
index 32e0cf2868c..2c2f7be096d 100644
--- a/mysql-test/main/range.result
+++ b/mysql-test/main/range.result
@@ -416,7 +416,9 @@ count(*)
1026
analyze table t1,t2;
Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
+test.t2 analyze status Engine-independent statistics collected
test.t2 analyze status Table is already up to date
explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0;
id select_type table type possible_keys key key_len ref rows Extra
@@ -1101,7 +1103,7 @@ id select_type table type possible_keys key key_len ref rows Extra
SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
item started price
Warnings:
-Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00'
+Warning 1292 Truncated incorrect datetime value: '2005-12-01 24:00:00'
SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00';
item started price
A1 2005-11-01 08:00:00 1000.000
@@ -1113,7 +1115,7 @@ id select_type table type possible_keys key key_len ref rows Extra
SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 24:00:00';
item started price
Warnings:
-Warning 1292 Incorrect datetime value: '2005-12-01 24:00:00'
+Warning 1292 Truncated incorrect datetime value: '2005-12-01 24:00:00'
SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00';
item started price
A1 2005-11-01 08:00:00 1000.000
@@ -1563,7 +1565,7 @@ str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND
str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20'
1
Warnings:
-Warning 1292 Incorrect datetime value: ''
+Warning 1292 Truncated incorrect datetime value: ''
SELECT str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND
str_to_date('2007-20-00', '%Y-%m-%d') <= '';
str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND
@@ -1578,7 +1580,7 @@ SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20';
str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20'
1
Warnings:
-Warning 1292 Incorrect datetime value: ''
+Warning 1292 Truncated incorrect datetime value: ''
SELECT str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND '';
str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND ''
NULL
@@ -1886,6 +1888,7 @@ alter table t1 add key2 int not null, add index i2(key2);
update t1 set key2=key1;
analyze table t1;
Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
create table t2 (a int);
insert into t2 values (1),(2),(3),(4),(5),(6),(7),(8);
@@ -2084,6 +2087,7 @@ insert into t2 select * from t2;
insert into t2 values (0, 0, 0, 0), (1, 1, 1, 1);
analyze table t2;
Table Op Msg_type Msg_text
+test.t2 analyze status Engine-independent statistics collected
test.t2 analyze status OK
select a, b from t2 where (a, b) in ((0, 0), (1, 1));
a b
@@ -2473,7 +2477,7 @@ EXPLAIN
"key_length": "5",
"used_key_parts": ["d"],
"rows": 3,
- "filtered": 100,
+ "filtered": 55,
"index_condition": "t2.d is not null",
"attached_condition": "(t2.d,t2.e) in (<cache>((3,3)),<cache>((7,7)),<cache>((2,2)))"
},
@@ -2529,8 +2533,8 @@ insert into t2 values
explain select * from t1,t2
where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range idx idx 5 NULL 6 Using index condition
-1 SIMPLE t2 ref idx1,idx2 idx1 5 test.t1.a 12 Using where
+1 SIMPLE t2 range idx1,idx2 idx1 5 NULL 8 Using index condition; Using where
+1 SIMPLE t1 ref idx idx 5 test.t2.d 8
explain format=json select * from t1,t2
where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1;
EXPLAIN
@@ -2538,27 +2542,27 @@ EXPLAIN
"query_block": {
"select_id": 1,
"table": {
- "table_name": "t1",
+ "table_name": "t2",
"access_type": "range",
- "possible_keys": ["idx"],
- "key": "idx",
+ "possible_keys": ["idx1", "idx2"],
+ "key": "idx1",
"key_length": "5",
- "used_key_parts": ["a"],
- "rows": 6,
- "filtered": 100,
- "index_condition": "t1.a is not null"
+ "used_key_parts": ["d"],
+ "rows": 8,
+ "filtered": 12.5,
+ "index_condition": "t2.d is not null",
+ "attached_condition": "(t2.d,t2.e) in (<cache>((3,3)),<cache>((7,7)),<cache>((8,8))) and octet_length(t2.f) = 1"
},
"table": {
- "table_name": "t2",
+ "table_name": "t1",
"access_type": "ref",
- "possible_keys": ["idx1", "idx2"],
- "key": "idx1",
+ "possible_keys": ["idx"],
+ "key": "idx",
"key_length": "5",
- "used_key_parts": ["d"],
- "ref": ["test.t1.a"],
- "rows": 12,
- "filtered": 100,
- "attached_condition": "(t1.a,t2.e) in (<cache>((3,3)),<cache>((7,7)),<cache>((8,8))) and octet_length(t2.f) = 1"
+ "used_key_parts": ["a"],
+ "ref": ["test.t2.d"],
+ "rows": 8,
+ "filtered": 100
}
}
}
@@ -2566,16 +2570,16 @@ select * from t1,t2
where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1;
a b c d e f
3 2 uuuw 3 3 i
-3 2 uuuw 3 3 i
-3 2 uuua 3 3 i
3 2 uuua 3 3 i
3 3 zzzz 3 3 i
-3 3 zzzz 3 3 i
-3 3 zyxw 3 3 i
3 3 zyxw 3 3 i
3 3 zzza 3 3 i
-3 3 zzza 3 3 i
3 3 zyxa 3 3 i
+3 2 uuuw 3 3 i
+3 2 uuua 3 3 i
+3 3 zzzz 3 3 i
+3 3 zyxw 3 3 i
+3 3 zzza 3 3 i
3 3 zyxa 3 3 i
7 7 xxxyy 7 7 h
7 7 xxxya 7 7 h
@@ -2586,16 +2590,16 @@ where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1";
execute stmt;
a b c d e f
3 2 uuuw 3 3 i
-3 2 uuuw 3 3 i
-3 2 uuua 3 3 i
3 2 uuua 3 3 i
3 3 zzzz 3 3 i
-3 3 zzzz 3 3 i
-3 3 zyxw 3 3 i
3 3 zyxw 3 3 i
3 3 zzza 3 3 i
-3 3 zzza 3 3 i
3 3 zyxa 3 3 i
+3 2 uuuw 3 3 i
+3 2 uuua 3 3 i
+3 3 zzzz 3 3 i
+3 3 zyxw 3 3 i
+3 3 zzza 3 3 i
3 3 zyxa 3 3 i
7 7 xxxyy 7 7 h
7 7 xxxya 7 7 h
@@ -2604,16 +2608,16 @@ a b c d e f
execute stmt;
a b c d e f
3 2 uuuw 3 3 i
-3 2 uuuw 3 3 i
3 2 uuua 3 3 i
-3 2 uuua 3 3 i
-3 3 zzzz 3 3 i
3 3 zzzz 3 3 i
3 3 zyxw 3 3 i
-3 3 zyxw 3 3 i
-3 3 zzza 3 3 i
3 3 zzza 3 3 i
3 3 zyxa 3 3 i
+3 2 uuuw 3 3 i
+3 2 uuua 3 3 i
+3 3 zzzz 3 3 i
+3 3 zyxw 3 3 i
+3 3 zzza 3 3 i
3 3 zyxa 3 3 i
7 7 xxxyy 7 7 h
7 7 xxxya 7 7 h
@@ -2642,7 +2646,7 @@ EXPLAIN
"key_length": "5",
"used_key_parts": ["e"],
"rows": 6,
- "filtered": 100,
+ "filtered": 6.7308,
"attached_condition": "(t2.d,t2.e) in (<cache>((4,4)),<cache>((7,7)),<cache>((8,8))) and octet_length(t2.f) = 1 and t2.d is not null"
},
"table": {
@@ -2993,6 +2997,7 @@ insert into t1 select a+15, concat(b,'yy') from t1;
insert into t1 select a+100, concat(b,'xx') from t1;
analyze table t1;
Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
select cast(count(a)/count(distinct a) as unsigned) as rec_per_key from t1;
rec_per_key
@@ -3011,7 +3016,7 @@ a b
set eq_range_index_dive_limit=2;
explain select * from t1 where a in (8, 15, 31, 1, 9);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range idx idx 5 NULL 10 Using index condition
+1 SIMPLE t1 range idx idx 5 NULL 5 Using index condition
select * from t1 where a in (8, 15, 31, 1, 9);
a b
1 yy