diff options
author | Igor Babaev <igor@askmonty.org> | 2019-02-03 18:41:18 -0800 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2019-02-03 18:41:18 -0800 |
commit | 37deed3f37561f264f65e162146bbc2ad35fb1a2 (patch) | |
tree | c1cfb6c0995bfcc4ac3fb335f518a8b404413e32 /mysql-test/main/range.result | |
parent | 658128af43b4d7c6db445164f8ed25ed4d1e3109 (diff) | |
parent | 5b996782be6b752ce50a0ecaa222b0688aa9e75d (diff) | |
download | mariadb-git-37deed3f37561f264f65e162146bbc2ad35fb1a2.tar.gz |
Merge branch '10.4' into bb-10.4-mdev16188
Diffstat (limited to 'mysql-test/main/range.result')
-rw-r--r-- | mysql-test/main/range.result | 87 |
1 files changed, 46 insertions, 41 deletions
diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 83237a9d2d4..8bef87d303b 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -421,7 +421,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 @@ -1938,6 +1940,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); @@ -2112,22 +2115,22 @@ SELECT * FROM t1 WHERE fd='😁'; 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 Warnings: -Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1 +Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81' for column `test`.`t1`.`fd` at row 1 SELECT * FROM t1 WHERE fd='😁'; id fd Warnings: -Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1 +Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81' for column `test`.`t1`.`fd` at row 1 # The following must not use range access: explain select count(*) from t1 where fd <'😁'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index ix_fd ix_fd 63 NULL # Using where; Using index Warnings: -Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1 +Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81' for column `test`.`t1`.`fd` at row 1 select count(*) from t1 where fd <'😁'; count(*) 40960 Warnings: -Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1 +Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81' for column `test`.`t1`.`fd` at row 1 select count(*) from t1 ignore index (ix_fd) where fd <'😁'; count(*) 40960 @@ -2140,6 +2143,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 @@ -2349,7 +2353,7 @@ EXPLAIN SELECT * FROM t1 WHERE a<=>'😎'; 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 Warnings: -Warning 1366 Incorrect string value: '\xF0\x9F\x98\x8E' for column 'a' at row 1 +Warning 1366 Incorrect string value: '\xF0\x9F\x98\x8E' for column `test`.`t1`.`a` at row 1 DROP TABLE t1; # # MDEV-10185: Assertion `tree1->keys[key_no] && tree2->keys[key_no]' failed in @@ -2529,7 +2533,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["d"], "rows": 3, - "filtered": 100, + "filtered": 60, "index_condition": "t2.d is not null", "attached_condition": "(t2.d,t2.e) in (<cache>((3,3)),<cache>((7,7)),<cache>((2,2)))" }, @@ -2585,8 +2589,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|filter idx1,idx2 idx1|idx2 5|5 test.t1.a 12 (14%) Using where; Using filter +1 SIMPLE t2 range|filter idx1,idx2 idx1|idx2 5|5 NULL 8 (14%) Using index condition; Using where; Using filter +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 @@ -2594,24 +2598,12 @@ EXPLAIN "query_block": { "select_id": 1, "table": { - "table_name": "t1", - "access_type": "range", - "possible_keys": ["idx"], - "key": "idx", - "key_length": "5", - "used_key_parts": ["a"], - "rows": 6, - "filtered": 100, - "index_condition": "t1.a is not null" - }, - "table": { "table_name": "t2", - "access_type": "ref", + "access_type": "range", "possible_keys": ["idx1", "idx2"], "key": "idx1", "key_length": "5", "used_key_parts": ["d"], - "ref": ["test.t1.a"], "rowid_filter": { "range": { "key": "idx2", @@ -2620,9 +2612,21 @@ EXPLAIN "rows": 15, "selectivity_pct": 14.423 }, - "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" + "rows": 8, + "filtered": 14.423, + "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": "t1", + "access_type": "ref", + "possible_keys": ["idx"], + "key": "idx", + "key_length": "5", + "used_key_parts": ["a"], + "ref": ["test.t2.d"], + "rows": 8, + "filtered": 100 } } } @@ -2630,16 +2634,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 @@ -2650,16 +2654,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 @@ -2668,16 +2672,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 @@ -2714,7 +2718,7 @@ EXPLAIN "selectivity_pct": 6.7308 }, "rows": 7, - "filtered": 100, + "filtered": 6.7308, "index_condition": "t2.d is not null", "attached_condition": "(t2.d,t2.e) in (<cache>((4,4)),<cache>((7,7)),<cache>((8,8))) and octet_length(t2.f) = 1" }, @@ -3066,6 +3070,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 @@ -3084,7 +3089,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 |