diff options
| author | Monty <monty@mariadb.org> | 2020-03-13 15:28:42 +0200 |
|---|---|---|
| committer | Monty <monty@mariadb.org> | 2020-03-17 02:16:48 +0200 |
| commit | 1242eb3d32f2863f847aa96a10e2ab983a1a643b (patch) | |
| tree | b7e28488f2f6fa75a6a83e3b18c95ef8b53b4e8c | |
| parent | 96b472c0ae798da43ca9f4735dfafe35b2f38fda (diff) | |
| download | mariadb-git-1242eb3d32f2863f847aa96a10e2ab983a1a643b.tar.gz | |
Removed double records_in_range calls from multi_range_read_info_const
This was to remove a performance regression between 10.3 and 10.4
In 10.5 we will have a better implementation of records_in_range
that will enable us to get more statistics.
This change was not done in 10.4 because the 10.5 will be part of
a larger change that is not suitable for the GA 10.4 version
Other things:
- Changed default handler block_size to 8192 to fix things statistics
for engines that doesn't set the block size.
- Fixed a bug in spider when using multiple part const ranges
(Patch from Kentoku)
68 files changed, 634 insertions, 483 deletions
diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc index 1ff34a936c6..aa09b0025d4 100644 --- a/mysql-test/include/icp_tests.inc +++ b/mysql-test/include/icp_tests.inc @@ -693,6 +693,7 @@ INSERT INTO t1 VALUES ('fkxdmbdkpjdanpje','o'), ('f','Pennsylvan'), ('Virginia','ei'); SET SESSION optimizer_switch='index_condition_pushdown=off'; +--replace_column 9 # EXPLAIN SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' @@ -702,6 +703,7 @@ SELECT * FROM t1 ORDER BY a; SET SESSION optimizer_switch='index_condition_pushdown=on'; +--replace_column 9 # EXPLAIN SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' diff --git a/mysql-test/main/func_in.result b/mysql-test/main/func_in.result index 79f5f555681..9a3c1dba045 100644 --- a/mysql-test/main/func_in.result +++ b/mysql-test/main/func_in.result @@ -717,11 +717,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN ('2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL c_timestamp NULL NULL NULL 20 Using where +1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL, '2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL c_timestamp NULL NULL NULL 20 Using where +1 SIMPLE t1 range c_timestamp c_timestamp 4 NULL 3 Using index condition EXPLAIN SELECT * FROM t1 WHERE c_timestamp 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/main/innodb_icp.result b/mysql-test/main/innodb_icp.result index d65acd5a48d..949bc9a00d8 100644 --- a/mysql-test/main/innodb_icp.result +++ b/mysql-test/main/innodb_icp.result @@ -658,7 +658,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 13 NULL 2 Using where; Rowid-ordered scan; Using filesort +1 SIMPLE t1 ALL b NULL NULL NULL # Using where; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; @@ -670,7 +670,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort +1 SIMPLE t1 ALL b NULL NULL NULL # Using where; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index cb9cdfa0214..290aa492a98 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -2968,6 +2968,7 @@ CREATE INDEX t3_quality ON t3(quality); INSERT INTO t3 VALUES (6, 4, 8, 0, "010101_anastacia_spmidi.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255), (3343, 3, 8, 1, "010102_4VN4bsPwnxRQUJW5Zp1RhG2IL9vvl_8.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255); +insert into t3 (mediaid, formatid) values (1000,1),(1001,2),(1002,3),(1003,1),(1004,2),(1005,3); CREATE TABLE t4( formatid int NOT NULL , name varchar(60) NOT NULL default '', @@ -3068,10 +3069,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 ref PRIMARY,t5_formattypeid t5_formattypeid 4 const 1 1 SIMPLE t1 ref t1_affiliateid,t1_metaid t1_affiliateid 4 const 2 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaidformatid 4 test.t1.metaid 1 Using index condition; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE t4 eq_ref PRIMARY,t4_formatclassid,t4_formats_idx PRIMARY 4 test.t3.formatid 1 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index 1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaidformatid 4 test.t1.metaid 1 Using index condition; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t4 eq_ref PRIMARY,t4_formatclassid,t4_formats_idx PRIMARY 4 test.t3.formatid 1 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t9 index PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8 NULL 2 Using where; Using index; Using join buffer (incremental, BNL join) 1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan 1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1 Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index edee05cb6a5..ce84ccb56fc 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -1117,6 +1117,7 @@ CREATE INDEX t3_quality ON t3(quality); INSERT INTO t3 VALUES (6, 4, 8, 0, "010101_anastacia_spmidi.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255), (3343, 3, 8, 1, "010102_4VN4bsPwnxRQUJW5Zp1RhG2IL9vvl_8.mid", "2004-03-16 13:40:00", 1, NULL, NULL, 255); +insert into t3 (mediaid, formatid) values (1000,1),(1001,2),(1002,3),(1003,1),(1004,2),(1005,3); CREATE TABLE t4( formatid int NOT NULL , diff --git a/mysql-test/main/key.result b/mysql-test/main/key.result index f341c4be2c6..12f6c36edd8 100644 --- a/mysql-test/main/key.result +++ b/mysql-test/main/key.result @@ -216,7 +216,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index explain select 1 from t1 where id =2 or id=3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using index +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 7 Using where; Using index explain select name from t1 where id =2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 diff --git a/mysql-test/main/key_cache.result b/mysql-test/main/key_cache.result index 36c75ad4a5d..322a2be6a4e 100644 --- a/mysql-test/main/key_cache.result +++ b/mysql-test/main/key_cache.result @@ -440,25 +440,25 @@ VARIABLE_NAME VARIABLE_VALUE KEY_BLOCKS_NOT_FLUSHED 0 KEY_BLOCKS_USED 4 KEY_BLOCKS_WARM 0 -KEY_READ_REQUESTS 24 +KEY_READ_REQUESTS 22 KEY_READS 0 KEY_WRITE_REQUESTS 26 KEY_WRITES 6 select variable_value into @key_blocks_unused from information_schema.session_status where variable_name = 'Key_blocks_unused'; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default NULL NULL 2097152 1024 4 # 0 24 0 26 6 +default NULL NULL 2097152 1024 4 # 0 22 0 26 6 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 delete from t2 where a='zzzz'; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default NULL NULL 2097152 1024 4 # 0 32 0 32 9 +default NULL NULL 2097152 1024 4 # 0 29 0 32 9 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 delete from t1; delete from t2; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default NULL NULL 2097152 1024 4 # 0 32 0 32 9 +default NULL NULL 2097152 1024 4 # 0 29 0 32 9 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 set global key_cache_segments=2; select @@key_cache_segments; @@ -488,7 +488,7 @@ VARIABLE_NAME VARIABLE_VALUE KEY_BLOCKS_NOT_FLUSHED 0 KEY_BLOCKS_USED 4 KEY_BLOCKS_WARM 0 -KEY_READ_REQUESTS 24 +KEY_READ_REQUESTS 22 KEY_READS 0 KEY_WRITE_REQUESTS 26 KEY_WRITES 6 @@ -497,13 +497,13 @@ variable_value < @key_blocks_unused 1 select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 2097152 1024 4 # 0 24 0 26 6 +default 2 NULL 2097152 1024 4 # 0 22 0 26 6 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 delete from t1; delete from t2; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 2097152 1024 4 # 0 24 0 26 6 +default 2 NULL 2097152 1024 4 # 0 22 0 26 6 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 set global key_cache_segments=1; select @@key_cache_segments; @@ -533,7 +533,7 @@ VARIABLE_NAME VARIABLE_VALUE KEY_BLOCKS_NOT_FLUSHED 0 KEY_BLOCKS_USED 4 KEY_BLOCKS_WARM 0 -KEY_READ_REQUESTS 24 +KEY_READ_REQUESTS 22 KEY_READS 0 KEY_WRITE_REQUESTS 26 KEY_WRITES 6 @@ -542,13 +542,13 @@ variable_value = @key_blocks_unused 1 select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 1 NULL 2097152 1024 4 # 0 24 0 26 6 +default 1 NULL 2097152 1024 4 # 0 22 0 26 6 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 delete from t1; delete from t2; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 1 NULL 2097152 1024 4 # 0 24 0 26 6 +default 1 NULL 2097152 1024 4 # 0 22 0 26 6 small NULL NULL 1048576 1024 1 # 0 1 0 2 1 flush tables; flush status; @@ -586,7 +586,7 @@ update t1 set p=3 where p=1; update t2 set i=2 where i=1; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 4 # 0 24 0 26 6 +default 2 NULL 32768 1024 4 # 0 22 0 26 6 small NULL NULL 1048576 1024 1 # 0 0 0 0 0 insert into t1(a) select a from t1; insert into t1(a) select a from t1; @@ -606,7 +606,7 @@ insert into t2(i,a) select i,a from t2; insert into t2(i,a) select i,a from t2; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 6735 # 3684 103 +default 2 NULL 32768 1024 # # 0 6733 # 3684 103 small NULL NULL 1048576 1024 # # 0 0 # 0 0 select * from t1 where p between 1010 and 1020 ; p a @@ -625,7 +625,7 @@ p i a 1020 3 zzzz select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 6756 # 3684 103 +default 2 NULL 32768 1024 # # 0 6750 # 3684 103 small NULL NULL 1048576 1024 # # 0 0 # 0 0 flush tables; flush status; @@ -633,7 +633,7 @@ update t1 set a='zzzz' where a='qqqq'; update t2 set i=1 where i=2; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 3078 18 1552 18 +default 2 NULL 32768 1024 # # 0 3076 18 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 set global keycache1.key_buffer_size=256*1024; select @@keycache1.key_buffer_size; @@ -645,7 +645,7 @@ select @@keycache1.key_cache_segments; 7 select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 3078 18 1552 18 +default 2 NULL 32768 1024 # # 0 3076 18 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache1 7 NULL 262143 2048 # # 0 0 0 0 0 select * from information_schema.key_caches where key_cache_name like "key%" @@ -685,13 +685,13 @@ count(*) 256 select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 -keycache1 7 NULL 262143 2048 # # 0 18 3 0 0 +keycache1 7 NULL 262143 2048 # # 0 14 3 0 0 select * from information_schema.key_caches where key_cache_name like "key%" and segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -keycache1 7 NULL 262143 2048 3 # 0 18 3 0 0 +keycache1 7 NULL 262143 2048 3 # 0 14 3 0 0 cache index t2 in keycache1; Table Op Msg_type Msg_text test.t2 assign_to_keycache status OK @@ -699,7 +699,7 @@ update t2 set p=p+3000, i=2 where a='qqqq'; select * from information_schema.key_caches where key_cache_name like "key%" and segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -keycache1 7 NULL 262143 2048 25 # 0 2088 25 1071 19 +keycache1 7 NULL 262143 2048 25 # 0 2082 25 1071 19 set global keycache2.key_buffer_size=1024*1024; cache index t2 in keycache2; Table Op Msg_type Msg_text @@ -712,7 +712,7 @@ keycache2 NULL NULL 1048576 1024 6 # 0 6 6 3 3 select * from information_schema.key_caches where key_cache_name like "key%" and segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -keycache1 7 NULL 262143 2048 25 # 0 2088 25 1071 19 +keycache1 7 NULL 262143 2048 25 # 0 2082 25 1071 19 keycache2 NULL NULL 1048576 1024 6 # 0 6 6 3 3 cache index t2 in keycache1; Table Op Msg_type Msg_text @@ -751,80 +751,80 @@ a yyyy select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 -keycache1 7 NULL 262143 2048 # # 0 3231 43 1594 30 +keycache1 7 NULL 262143 2048 # # 0 3201 43 1594 30 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_cache_block_size=2*1024; insert into t2 values (7000, 3, 'yyyy'); select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache1 7 NULL 262143 2048 # # 0 6 6 3 3 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_cache_block_size=8*1024; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache1 3 NULL 262143 8192 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 insert into t2 values (8000, 3, 'yyyy'); select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache1 3 NULL 262143 8192 # # 0 6 5 3 3 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_buffer_size=64*1024; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_cache_block_size=2*1024; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache1 3 NULL 65535 2048 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_cache_block_size=8*1024; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_buffer_size=0; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_cache_block_size=8*1024; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_buffer_size=0; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_buffer_size=128*1024; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache1 1 NULL 131072 8192 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 set global keycache1.key_cache_block_size=1024; select * from information_schema.key_caches where segment_number is null; KEY_CACHE_NAME SEGMENTS SEGMENT_NUMBER FULL_SIZE BLOCK_SIZE USED_BLOCKS UNUSED_BLOCKS DIRTY_BLOCKS READ_REQUESTS READS WRITE_REQUESTS WRITES -default 2 NULL 32768 1024 # # 0 3178 24 1552 18 +default 2 NULL 32768 1024 # # 0 3172 24 1552 18 small NULL NULL 1048576 1024 # # 0 0 0 0 0 keycache1 7 NULL 131068 1024 # # 0 0 0 0 0 keycache2 NULL NULL 1048576 1024 # # 0 6 6 3 3 diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index e11438be48d..7f24cb4896d 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -752,12 +752,12 @@ Warnings: Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead EXPLAIN DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 1 Using where; Using filesort +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 1 100.00 Using where; Using filesort +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 3 @@ -781,8 +781,6 @@ Handler_read_rnd_next 1 # Status of testing query execution: Variable_name Value Handler_read_key 3 -Handler_read_rnd_next 1 -Sort_scan 1 INSERT INTO t1 VALUES (1), (2), (3), (-1), (-2), (-3); # diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result index 0fdc3f11627..7f34b274764 100644 --- a/mysql-test/main/myisam_icp.result +++ b/mysql-test/main/myisam_icp.result @@ -407,7 +407,7 @@ WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 ORDER BY c1 LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range|filter PRIMARY,k1 PRIMARY|k1 4|5 NULL 3 (50%) Using index condition; Using where; Rowid-ordered scan; Using filesort; Using rowid filter +1 SIMPLE t1 range PRIMARY,k1 PRIMARY 4 NULL 3 Using index condition; Using where; Rowid-ordered scan; Using filesort DROP TABLE t1; # # @@ -651,7 +651,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 13 NULL 2 Using where; Rowid-ordered scan; Using filesort +1 SIMPLE t1 range b b 13 NULL # Using where; Rowid-ordered scan; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; @@ -663,7 +663,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort +1 SIMPLE t1 range b b 13 NULL # Using index condition; Rowid-ordered scan; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; diff --git a/mysql-test/main/negation_elimination.result b/mysql-test/main/negation_elimination.result index 6e40074c317..f9be5b55e46 100644 --- a/mysql-test/main/negation_elimination.result +++ b/mysql-test/main/negation_elimination.result @@ -192,7 +192,7 @@ a 15 explain select * from t1 where not(a < 15 and a > 5); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 11 Using where; Using index +1 SIMPLE t1 index a a 5 NULL 21 Using where; Using index select * from t1 where not(a < 15 and a > 5); a 0 @@ -208,7 +208,7 @@ a 19 explain select * from t1 where a = 2 or not(a < 10); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 11 Using where; Using index +1 SIMPLE t1 index a a 5 NULL 21 Using where; Using index select * from t1 where a = 2 or not(a < 10); a 2 @@ -255,7 +255,7 @@ a 19 explain select * from t1 where a = 2 or not(a < 5 or a > 15); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 12 Using where; Using index +1 SIMPLE t1 index a a 5 NULL 21 Using where; Using index select * from t1 where a = 2 or not(a < 5 or a > 15); a 2 @@ -289,7 +289,7 @@ a 19 explain select * from t1 where NULL or not(a < 15 and a > 5); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 11 Using where; Using index +1 SIMPLE t1 index a a 5 NULL 21 Using where; Using index select * from t1 where NULL or not(a < 15 and a > 5); a 0 diff --git a/mysql-test/main/null_key.result b/mysql-test/main/null_key.result index 8eca97d5e70..55b2060475f 100644 --- a/mysql-test/main/null_key.result +++ b/mysql-test/main/null_key.result @@ -20,7 +20,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 9 NULL 12 Using where; Using index explain select * from t1 where (a is null or a > 0 and a < 2) and b < 5 limit 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 9 NULL 2 Using where; Using index +1 SIMPLE t1 index a,b a 9 NULL 12 Using where; Using index explain select * from t1 where (a is null or a = 7) and b=7; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref_or_null a,b a 9 const,const 2 Using where; Using index @@ -32,7 +32,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 3 Using where; Using index explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 9 NULL 3 Using where; Using index +1 SIMPLE t1 ref a,b a 5 const 2 Using where; Using index explain select * from t1 where a > 1 and a < 3 limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 72cee2a428f..110ce60edfd 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -2044,7 +2044,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "using_mrr": false, "index_only": false, "rows": 180, - "cost": 231.72, + "cost": 229.72, "chosen": true }, { @@ -2226,7 +2226,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "using_mrr": false, "index_only": false, "rows": 180, - "cost": 231.72, + "cost": 229.72, "chosen": true } ], @@ -2243,7 +2243,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "ranges": ["(1) <= (a) <= (1)"] }, "rows_for_plan": 180, - "cost_for_plan": 231.72, + "cost_for_plan": 229.72, "chosen": true } } @@ -7568,7 +7568,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 107, - "cost": 10.955, + "cost": 8.9549, "chosen": true } ], @@ -7605,7 +7605,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1000, - "cost": 1282.2, + "cost": 1273.2, "chosen": true } ], @@ -8009,7 +8009,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1000, - "cost": 1282.2, + "cost": 1273.2, "chosen": true } ], diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index 7f1bd5163f3..40d75d549ec 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -323,7 +323,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2243, - "cost": 2862.1, + "cost": 2844.1, "chosen": true }, @@ -337,7 +337,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2243, - "cost": 2862.1, + "cost": 2844.1, "chosen": false, "cause": "cost" }, @@ -352,7 +352,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2243, - "cost": 2862.1, + "cost": 2844.1, "chosen": false, "cause": "cost" } @@ -501,7 +501,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 170.53, + "cost": 152.53, "chosen": true }, @@ -515,13 +515,13 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 170.53, + "cost": 152.53, "chosen": false, "cause": "cost" } ], "index_to_merge": "key1", - "cumulated_cost": 170.53 + "cumulated_cost": 152.53 }, { @@ -538,7 +538,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 170.53, + "cost": 152.53, "chosen": true }, @@ -552,16 +552,16 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 170.53, + "cost": 152.53, "chosen": false, "cause": "cost" } ], "index_to_merge": "key3", - "cumulated_cost": 341.05 + "cumulated_cost": 305.05 } ], - "cost_of_reading_ranges": 341.05, + "cost_of_reading_ranges": 305.05, "use_roworder_union": true, "cause": "always cheaper than non roworder retrieval", "analyzing_roworder_scans": diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index afcf0b03dae..4b047315bf5 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -121,9 +121,8 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { "using_mrr": false, "index_only": false, "rows": 1000, - "cost": 206.39, - "chosen": false, - "cause": "cost" + "cost": 203.39, + "chosen": true }, { "index": "key1", diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index 268cac4daac..00b131a3252 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -714,7 +714,7 @@ FieldKey LongVal StringVal 3 3 3 EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range FieldKey,LongField,StringField LongField 38 NULL 3 Using index condition +1 SIMPLE t1 range FieldKey,LongField,StringField LongField 38 NULL 3 Using where SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal; FieldKey LongVal StringVal 3 1 2 diff --git a/mysql-test/main/partition.result b/mysql-test/main/partition.result index 5a7795394ec..ea12158862e 100644 --- a/mysql-test/main/partition.result +++ b/mysql-test/main/partition.result @@ -551,7 +551,7 @@ INSERT INTO `t1` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13), INSERT INTO `t2` VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20); EXPLAIN PARTITIONS SELECT c1 FROM t1 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 NULL range c1 c1 5 NULL 4 Using where; Using index +1 SIMPLE t1 NULL index c1 c1 5 NULL 20 Using where; Using index FLUSH STATUS; SELECT c1 FROM t1 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); c1 @@ -561,10 +561,10 @@ c1 19 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value -Handler_read_first 0 -Handler_read_key 2 +Handler_read_first 1 +Handler_read_key 0 Handler_read_last 0 -Handler_read_next 4 +Handler_read_next 20 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 @@ -572,7 +572,7 @@ Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 EXPLAIN PARTITIONS SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 a range c1 c1 5 NULL 4 Using where; Using index +1 SIMPLE t2 a index c1 c1 5 NULL 20 Using where; Using index FLUSH STATUS; SELECT c1 FROM t2 WHERE (c1 > 10 AND c1 < 13) OR (c1 > 17 AND c1 < 20); c1 @@ -582,10 +582,10 @@ c1 19 SHOW STATUS LIKE 'Handler_read_%'; Variable_name Value -Handler_read_first 0 -Handler_read_key 2 +Handler_read_first 1 +Handler_read_key 0 Handler_read_last 0 -Handler_read_next 4 +Handler_read_next 20 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 diff --git a/mysql-test/main/partition_mrr_aria.result b/mysql-test/main/partition_mrr_aria.result index e2b409fa4ae..ccef491242b 100644 --- a/mysql-test/main/partition_mrr_aria.result +++ b/mysql-test/main/partition_mrr_aria.result @@ -129,7 +129,7 @@ set join_cache_level=6; set optimizer_switch='mrr=on'; explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1 @@ -146,7 +146,7 @@ tp a b c a 3 4 30 zzzyy 4 explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1 @@ -163,7 +163,7 @@ tp a b c a 3 4 30 zzzyy 4 explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = 4 @@ -189,7 +189,7 @@ tp a b c a 2 4 40 xxzy 4 explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = 4 @@ -221,7 +221,7 @@ test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 23 17.39 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null @@ -229,7 +229,7 @@ select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; a tp a b c explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 23 17.39 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null diff --git a/mysql-test/main/partition_mrr_innodb.result b/mysql-test/main/partition_mrr_innodb.result index 2efb10de694..4aad8134866 100644 --- a/mysql-test/main/partition_mrr_innodb.result +++ b/mysql-test/main/partition_mrr_innodb.result @@ -129,7 +129,7 @@ set join_cache_level=6; set optimizer_switch='mrr=on'; explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1 @@ -146,7 +146,7 @@ tp a b c a 3 4 30 zzzyy 4 explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1 @@ -163,7 +163,7 @@ tp a b c a 3 4 30 zzzyy 4 explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = 4 @@ -189,7 +189,7 @@ tp a b c a 2 4 40 xxzy 4 explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = 4 @@ -221,7 +221,7 @@ test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 23 17.39 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null @@ -229,7 +229,7 @@ select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; a tp a b c explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 23 17.39 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null diff --git a/mysql-test/main/partition_mrr_myisam.result b/mysql-test/main/partition_mrr_myisam.result index 4123ae575e0..7e060b35c53 100644 --- a/mysql-test/main/partition_mrr_myisam.result +++ b/mysql-test/main/partition_mrr_myisam.result @@ -129,7 +129,7 @@ set join_cache_level=6; set optimizer_switch='mrr=on'; explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1 @@ -146,7 +146,7 @@ tp a b c a 3 4 30 zzzyy 4 explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1 @@ -163,7 +163,7 @@ tp a b c a 3 4 30 zzzyy 4 explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = 4 @@ -189,7 +189,7 @@ tp a b c a 2 4 40 xxzy 4 explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 10.00 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = 4 @@ -221,7 +221,7 @@ test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 23 17.39 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null @@ -229,7 +229,7 @@ select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; a tp a b c explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 23 17.39 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null @@ -287,13 +287,13 @@ set join_cache_level=6, optimizer_switch='mrr=on'; explain select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx idx 5 NULL 2 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 Using where; Using index 1 SIMPLE t0 ref idx idx 5 test.t2.a 12 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan # This will use "Using index condition(BKA)" explain select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range idx idx 5 NULL 2 Using where; Using index +1 SIMPLE t2 index idx idx 5 NULL 20 Using where; Using index 1 SIMPLE t1 ref idx idx 5 test.t2.a 12 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan set join_cache_level=@tmp1, optimizer_switch=@tmp2; drop table t0,t1,t2; diff --git a/mysql-test/main/partition_range.result b/mysql-test/main/partition_range.result index 261a05e7023..6eb7147b461 100644 --- a/mysql-test/main/partition_range.result +++ b/mysql-test/main/partition_range.result @@ -11,7 +11,7 @@ PARTITION BY HASH (a) PARTITIONS 1; INSERT INTO t1 VALUES (0, 580092), (3, 894076), (4, 805483), (4, 913540), (6, 611137), (8, 171602), (9, 599495), (9, 746305), (10, 272829), (10, 847519), (12, 258869), (12, 929028), (13, 288970), (15, 20971), (15, 105839), (16, 788272), (17, 76914), (18, 827274), (19, 802258), (20, 123677), (20, 587729), (22, 701449), (25, 31565), (25, 230782), (25, 442887), (25, 733139), (25, 851020); EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100, 3) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 4 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 1 Using where; Using index for group-by DROP TABLE t1; create table t1 (a DATETIME) partition by range (TO_DAYS(a)) diff --git a/mysql-test/main/preload.result b/mysql-test/main/preload.result index 529c7ac2690..7ed0c62f33a 100644 --- a/mysql-test/main/preload.result +++ b/mysql-test/main/preload.result @@ -55,14 +55,14 @@ count(*) 4181 show status like "key_read%"; Variable_name Value -Key_read_requests 297 +Key_read_requests 294 Key_reads 60 select count(*) from t1 where b = 'test1'; count(*) 4181 show status like "key_read%"; Variable_name Value -Key_read_requests 594 +Key_read_requests 588 Key_reads 60 flush tables; flush status; @@ -81,7 +81,7 @@ count(*) 4181 show status like "key_read%"; Variable_name Value -Key_read_requests 1071 +Key_read_requests 1068 Key_reads 774 flush tables; flush status; @@ -105,7 +105,7 @@ count(*) 4181 show status like "key_read%"; Variable_name Value -Key_read_requests 314 +Key_read_requests 311 Key_reads 75 flush tables; flush status; @@ -133,7 +133,7 @@ count(*) 2584 show status like "key_read%"; Variable_name Value -Key_read_requests 1272 +Key_read_requests 1266 Key_reads 821 flush tables; flush status; diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index bb111f25a07..54ac0de0c0f 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -1183,6 +1183,7 @@ a varchar(32), index (a) ) DEFAULT CHARSET=latin1 COLLATE=latin1_bin; INSERT INTO t1 VALUES ('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A'), ('C'), ('A'); +INSERT INTO t1 VALUES ('X'),('Y'),('Z'),('X1'),('Y1'),('Z1'),('X1'),('Y1'),('Z1'); SELECT a FROM t1 WHERE a='b' OR a='B'; a B @@ -1268,6 +1269,7 @@ INSERT INTO t3 VALUES (16, 0), (17, 0), (18, 0), (19, 0), (20, 0); INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; +insert into t1 select -a,b from t1; SELECT * FROM t1 WHERE 23 <= a AND a < 25 OR 25 < a AND b = 23 OR @@ -1425,7 +1427,7 @@ SELECT * FROM t3 WHERE a < 5 OR a < 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range a a 5 NULL 9 Using where; Using index +1 SIMPLE t3 index a a 10 NULL 23 Using where; Using index DROP TABLE t1, t2, t3; # # Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN @@ -3103,12 +3105,12 @@ insert into t2 select A.a + B.a*10 + C.a*100 from ten A, ten B,ten C where A.a + # expected type=range, rows=1487 , reason=using index dives analyze SELECT * FROM t1 where a in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t1 range a a 5 NULL 1487 1199.00 100.00 100.00 Using where; Using index +1 SIMPLE t1 index a a 5 NULL 2000 2000.00 74.35 59.95 Using where; Using index insert into t2 values (200),(201); # expected type=range, rows=201 , reason=using index statistics analyze SELECT * FROM t1 where a in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,200,201); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t1 range a a 5 NULL 201 1201.00 100.00 100.00 Using where; Using index +1 SIMPLE t1 index a a 5 NULL 2000 2000.00 10.05 60.05 Using where; Using index drop table t1,ten,t2; # # End of 10.2 tests diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index a52443990be..93305e72b22 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -960,6 +960,7 @@ CREATE TABLE t1 ( INSERT INTO t1 VALUES ('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A'), ('C'), ('A'); +INSERT INTO t1 VALUES ('X'),('Y'),('Z'),('X1'),('Y1'),('Z1'),('X1'),('Y1'),('Z1'); SELECT a FROM t1 WHERE a='b' OR a='B'; EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B'; @@ -1077,7 +1078,7 @@ INSERT INTO t3 VALUES # To make range scan compelling to the optimizer INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; - +insert into t1 select -a,b from t1; # # Problem#1 Test queries. Will give missing results unless Problem#1 is fixed. diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index c286c49cac0..5ca1b6603c9 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -1186,6 +1186,7 @@ a varchar(32), index (a) ) DEFAULT CHARSET=latin1 COLLATE=latin1_bin; INSERT INTO t1 VALUES ('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A'), ('C'), ('A'); +INSERT INTO t1 VALUES ('X'),('Y'),('Z'),('X1'),('Y1'),('Z1'),('X1'),('Y1'),('Z1'); SELECT a FROM t1 WHERE a='b' OR a='B'; a B @@ -1271,6 +1272,7 @@ INSERT INTO t3 VALUES (16, 0), (17, 0), (18, 0), (19, 0), (20, 0); INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; INSERT INTO t3 SELECT * FROM t3 WHERE a = 10; +insert into t1 select -a,b from t1; SELECT * FROM t1 WHERE 23 <= a AND a < 25 OR 25 < a AND b = 23 OR @@ -1428,7 +1430,7 @@ SELECT * FROM t3 WHERE a < 5 OR a < 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range a a 5 NULL 9 Using where; Using index +1 SIMPLE t3 index a a 10 NULL 23 Using where; Using index DROP TABLE t1, t2, t3; # # Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN @@ -3100,12 +3102,12 @@ insert into t2 select A.a + B.a*10 + C.a*100 from ten A, ten B,ten C where A.a + # expected type=range, rows=1487 , reason=using index dives analyze SELECT * FROM t1 where a in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t1 range a a 5 NULL 1487 1199.00 100.00 100.00 Using where; Using index +1 SIMPLE t1 index a a 5 NULL 2000 2000.00 74.35 59.95 Using where; Using index insert into t2 values (200),(201); # expected type=range, rows=201 , reason=using index statistics analyze SELECT * FROM t1 where a in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,200,201); id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE t1 range a a 5 NULL 201 1201.00 100.00 100.00 Using where; Using index +1 SIMPLE t1 index a a 5 NULL 2000 2000.00 10.05 60.05 Using where; Using index drop table t1,ten,t2; # # End of 10.2 tests diff --git a/mysql-test/main/range_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result index 5c3e5441b8b..f0bf2224c92 100644 --- a/mysql-test/main/range_vs_index_merge.result +++ b/mysql-test/main/range_vs_index_merge.result @@ -705,9 +705,9 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') -AND Country='FIN'; +AND Country='EST'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 7 Using index condition; Using where +1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 2 Using index condition; Using where SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') AND Country='USA'; @@ -1079,7 +1079,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE (Name='Samara' AND Country='RUS') OR (Name='Seattle' AND Country='USA'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 28 Using index condition +1 SIMPLE City index_merge Country,CountryPopulation,CountryName,CityName CountryName,CityName 38,35 NULL 28 Using sort_union(CountryName,CityName); Using where SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR (Name='Addis Abeba' AND Country='ETH') OR @@ -1109,33 +1109,33 @@ SELECT Name, Country, Population FROM City WHERE (Name='Samara' AND Country='RUS') OR (Name='Seattle' AND Country='USA'); Name Country Population -Toronto CAN 688275 -Vancouver CAN 514008 -Basel CHE 166700 -Peking CHN 7472000 -Praha CZE 1181126 -Dresden DEU 476668 Addis Abeba ETH 2495000 -Paris FRA 2125246 -Jakarta IDN 9604900 +Ankara TUR 3038159 Bangalore IND 2660088 +Basel CHE 166700 +Caracas VEN 1975294 +Dakar SEN 785071 Delhi IND 7206704 -Teheran IRN 6758845 -Roma ITA 2643581 -Venezia ITA 277305 -Tokyo JPN 7980230 -Seoul KOR 9981619 +Dresden DEU 476668 +Jakarta IDN 9604900 Kaunas LTU 412639 -Rabat MAR 623457 -Tijuana MEX 1212232 Lagos NGA 1518000 +Lugansk UKR 469000 Manila PHL 1581082 +Paris FRA 2125246 +Peking CHN 7472000 +Praha CZE 1181126 +Rabat MAR 623457 +Roma ITA 2643581 Samara RUS 1156100 -Dakar SEN 785071 -Ankara TUR 3038159 -Lugansk UKR 469000 Seattle USA 563374 -Caracas VEN 1975294 +Seoul KOR 9981619 +Teheran IRN 6758845 +Tijuana MEX 1212232 +Tokyo JPN 7980230 +Toronto CAN 688275 +Vancouver CAN 514008 +Venezia ITA 277305 set optimizer_switch='index_merge=off'; EXPLAIN SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR @@ -1196,33 +1196,33 @@ SELECT Name, Country, Population FROM City WHERE (Name='Samara' AND Country='RUS') OR (Name='Seattle' AND Country='USA'); Name Country Population -Toronto CAN 688275 -Vancouver CAN 514008 -Basel CHE 166700 -Peking CHN 7472000 -Praha CZE 1181126 -Dresden DEU 476668 Addis Abeba ETH 2495000 -Paris FRA 2125246 -Jakarta IDN 9604900 +Ankara TUR 3038159 Bangalore IND 2660088 +Basel CHE 166700 +Caracas VEN 1975294 +Dakar SEN 785071 Delhi IND 7206704 -Teheran IRN 6758845 -Roma ITA 2643581 -Venezia ITA 277305 -Tokyo JPN 7980230 -Seoul KOR 9981619 +Dresden DEU 476668 +Jakarta IDN 9604900 Kaunas LTU 412639 -Rabat MAR 623457 -Tijuana MEX 1212232 Lagos NGA 1518000 +Lugansk UKR 469000 Manila PHL 1581082 +Paris FRA 2125246 +Peking CHN 7472000 +Praha CZE 1181126 +Rabat MAR 623457 +Roma ITA 2643581 Samara RUS 1156100 -Dakar SEN 785071 -Ankara TUR 3038159 -Lugansk UKR 469000 Seattle USA 563374 -Caracas VEN 1975294 +Seoul KOR 9981619 +Teheran IRN 6758845 +Tijuana MEX 1212232 +Tokyo JPN 7980230 +Toronto CAN 688275 +Vancouver CAN 514008 +Venezia ITA 277305 set optimizer_switch=@save_optimizer_switch; # # Bug mdev-585: range vs index-merge with ORDER BY ... LIMIT n @@ -1674,7 +1674,9 @@ CREATE TABLE t1 ( f1 int, f2 int, f3 int, f4 int, PRIMARY KEY (f1), KEY (f3), KEY (f4) ); +INSERT INTO t1 VALUES (1,0,0,0), (2,0,0,0), (3,0,0,0); INSERT INTO t1 VALUES (9,0,2,6), (9930,0,0,NULL); +insert into t1 select seq,seq,seq,seq from seq_100_to_400; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected @@ -1685,24 +1687,24 @@ SET SESSION optimizer_switch='index_merge_union=off'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 2 Using where +1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 306 Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); f1 f2 f3 f4 9 0 2 6 SET SESSION optimizer_switch='index_merge_union=on'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge PRIMARY,f3,f4 f3,PRIMARY,f3 5,4,5 NULL 3 Using union(f3,PRIMARY,f3); Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); f1 f2 f3 f4 9 0 2 6 INSERT INTO t1 VALUES @@ -1718,24 +1720,24 @@ SET SESSION optimizer_switch='index_merge_union=off'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 16 Using where +1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 320 Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); f1 f2 f3 f4 9 0 2 6 SET SESSION optimizer_switch='index_merge_union=on'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge PRIMARY,f3,f4 f3,PRIMARY,f3 5,4,5 NULL 3 Using union(f3,PRIMARY,f3); Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); f1 f2 f3 f4 9 0 2 6 SET SESSION optimizer_switch=DEFAULT; diff --git a/mysql-test/main/range_vs_index_merge.test b/mysql-test/main/range_vs_index_merge.test index f4d9024fe08..5ed5f621ab6 100644 --- a/mysql-test/main/range_vs_index_merge.test +++ b/mysql-test/main/range_vs_index_merge.test @@ -1,5 +1,6 @@ --source include/default_optimizer_switch.inc --source include/default_charset.inc +--source include/have_sequence.inc --disable_warnings DROP TABLE IF EXISTS t1,t2,t3,t4; @@ -387,7 +388,7 @@ SELECT * FROM City EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') - AND Country='FIN'; + AND Country='EST'; # The following 4 queries check that the plans # for the previous 2 plans are valid @@ -628,7 +629,7 @@ SELECT * FROM City # -# LP bug #954262: index merge oover long disjunction in WHERE +# LP bug #954262: index merge over long disjunction in WHERE # set @save_optimizer_switch=@@optimizer_switch; @@ -667,6 +668,7 @@ let $cond = eval EXPLAIN SELECT Name, Country, Population FROM City WHERE $cond; +--sorted_result eval SELECT Name, Country, Population FROM City WHERE $cond; @@ -676,6 +678,7 @@ set optimizer_switch='index_merge=off'; eval EXPLAIN SELECT Name, Country, Population FROM City WHERE $cond; +--sorted_result eval SELECT Name, Country, Population FROM City WHERE $cond; @@ -1087,7 +1090,9 @@ CREATE TABLE t1 ( PRIMARY KEY (f1), KEY (f3), KEY (f4) ); +INSERT INTO t1 VALUES (1,0,0,0), (2,0,0,0), (3,0,0,0); INSERT INTO t1 VALUES (9,0,2,6), (9930,0,0,NULL); +insert into t1 select seq,seq,seq,seq from seq_100_to_400; analyze table t1; @@ -1099,22 +1104,22 @@ SET SESSION optimizer_switch='index_merge_union=off'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 - OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); + OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 - OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); + OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); SET SESSION optimizer_switch='index_merge_union=on'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 - OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); + OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 - OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); + OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); INSERT INTO t1 VALUES @@ -1130,22 +1135,22 @@ SET SESSION optimizer_switch='index_merge_union=off'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 - OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); + OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 - OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); + OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); SET SESSION optimizer_switch='index_merge_union=on'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 - OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); + OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 - OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); + OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); SET SESSION optimizer_switch=DEFAULT; diff --git a/mysql-test/main/range_vs_index_merge_innodb.result b/mysql-test/main/range_vs_index_merge_innodb.result index e2fd8020049..b82b6942c5b 100644 --- a/mysql-test/main/range_vs_index_merge_innodb.result +++ b/mysql-test/main/range_vs_index_merge_innodb.result @@ -711,9 +711,9 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'P%') -AND Country='FIN'; +AND Country='EST'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 7 Using index condition; Using where +1 SIMPLE City ref Population,Country,Name,CountryPopulation Country 3 const 2 Using index condition; Using where SELECT * FROM City WHERE ((Population > 101000 AND Population < 103000) OR Name LIKE 'Pas%') AND Country='USA'; @@ -1116,32 +1116,32 @@ SELECT Name, Country, Population FROM City WHERE (Name='Seattle' AND Country='USA'); Name Country Population Addis Abeba ETH 2495000 -Manila PHL 1581082 -Jakarta IDN 9604900 -Delhi IND 7206704 +Ankara TUR 3038159 Bangalore IND 2660088 -Teheran IRN 6758845 -Roma ITA 2643581 -Venezia ITA 277305 -Tokyo JPN 7980230 -Toronto CAN 688275 -Vancouver CAN 514008 -Peking CHN 7472000 -Seoul KOR 9981619 +Basel CHE 166700 +Caracas VEN 1975294 +Dakar SEN 785071 +Delhi IND 7206704 +Dresden DEU 476668 +Jakarta IDN 9604900 Kaunas LTU 412639 -Rabat MAR 623457 -Tijuana MEX 1212232 Lagos NGA 1518000 +Lugansk UKR 469000 +Manila PHL 1581082 Paris FRA 2125246 -Dresden DEU 476668 -Dakar SEN 785071 -Basel CHE 166700 +Peking CHN 7472000 Praha CZE 1181126 -Ankara TUR 3038159 -Lugansk UKR 469000 -Caracas VEN 1975294 +Rabat MAR 623457 +Roma ITA 2643581 Samara RUS 1156100 Seattle USA 563374 +Seoul KOR 9981619 +Teheran IRN 6758845 +Tijuana MEX 1212232 +Tokyo JPN 7980230 +Toronto CAN 688275 +Vancouver CAN 514008 +Venezia ITA 277305 set optimizer_switch='index_merge=off'; EXPLAIN SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR @@ -1172,7 +1172,7 @@ EXPLAIN SELECT Name, Country, Population FROM City WHERE (Name='Samara' AND Country='RUS') OR (Name='Seattle' AND Country='USA'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CityName 35 NULL 28 Using index condition; Using where +1 SIMPLE City range Country,CountryPopulation,CountryName,CityName CountryName 38 NULL 27 Using index condition SELECT Name, Country, Population FROM City WHERE (Name='Manila' AND Country='PHL') OR (Name='Addis Abeba' AND Country='ETH') OR @@ -1680,7 +1680,9 @@ CREATE TABLE t1 ( f1 int, f2 int, f3 int, f4 int, PRIMARY KEY (f1), KEY (f3), KEY (f4) ); +INSERT INTO t1 VALUES (1,0,0,0), (2,0,0,0), (3,0,0,0); INSERT INTO t1 VALUES (9,0,2,6), (9930,0,0,NULL); +insert into t1 select seq,seq,seq,seq from seq_100_to_400; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected @@ -1691,24 +1693,24 @@ SET SESSION optimizer_switch='index_merge_union=off'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 2 Using where +1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 306 Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); f1 f2 f3 f4 9 0 2 6 SET SESSION optimizer_switch='index_merge_union=on'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge PRIMARY,f3,f4 PRIMARY,f3 4,5 NULL 2 Using union(PRIMARY,f3); Using where +1 SIMPLE t1 index_merge PRIMARY,f3,f4 PRIMARY,f3 4,5 NULL 5 Using union(PRIMARY,f3); Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); f1 f2 f3 f4 9 0 2 6 INSERT INTO t1 VALUES @@ -1724,24 +1726,24 @@ SET SESSION optimizer_switch='index_merge_union=off'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 16 Using where +1 SIMPLE t1 ALL PRIMARY,f3,f4 NULL NULL NULL 320 Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); f1 f2 f3 f4 9 0 2 6 SET SESSION optimizer_switch='index_merge_union=on'; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge PRIMARY,f3,f4 PRIMARY,f3 4,5 NULL 2 Using union(PRIMARY,f3); Using where +1 SIMPLE t1 index_merge PRIMARY,f3,f4 PRIMARY,f3 4,5 NULL 5 Using union(PRIMARY,f3); Using where SELECT * FROM t1 FORCE KEY (PRIMARY,f3,f4) WHERE ( f3 = 1 OR f1 = 7 ) AND f1 < 10 -OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 != 1 ); +OR f3 BETWEEN 2 AND 2 AND ( f3 = 1 OR f4 < 7 ); f1 f2 f3 f4 9 0 2 6 SET SESSION optimizer_switch=DEFAULT; @@ -1914,23 +1916,23 @@ Country='NOR' AND Name IN ('Oslo', 'Bergen') OR Country='ITA' AND Name IN ('Napoli', 'Venezia'); ID Name Country Population 175 Antwerpen BEL 446525 -176 Gent BEL 224180 +2808 Bergen NOR 230948 3068 Berlin DEU 3386667 3087 Bonn DEU 301048 +2918 Braga PRT 90535 +176 Gent BEL 224180 3242 Lahti FIN 96921 -2974 Paris FRA 2125246 +3580 Moscow RUS 8389200 1466 Napoli ITA 1002619 -1474 Venezia ITA 277305 -2808 Bergen NOR 230948 2807 Oslo NOR 508726 -2928 Warszawa POL 1615369 -2931 Wroclaw POL 636765 -2918 Braga PRT 90535 +2974 Paris FRA 2125246 2915 Porto PRT 273060 -3580 Moscow RUS 8389200 3581 St Petersburg RUS 4694000 3048 Stockholm SWE 750348 3051 Uppsala SWE 189569 +1474 Venezia ITA 277305 +2928 Warszawa POL 1615369 +2931 Wroclaw POL 636765 explain select * from City where Country='FIN' AND Name IN ('Lahti','Imatra') OR @@ -1944,7 +1946,7 @@ Country='POL' AND Name IN ('Warszawa', 'Wroclaw') OR Country='NOR' AND Name IN ('Oslo', 'Bergen') OR Country='ITA' AND Name IN ('Napoli', 'Venezia'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range CountryName,Name CountryName 38 NULL 20 Using index condition +1 SIMPLE City range CountryName,Name Name 35 NULL 20 Using index condition; Using where DROP DATABASE world; set session optimizer_switch='index_merge_sort_intersection=default'; set global innodb_stats_persistent= @innodb_stats_persistent_save; diff --git a/mysql-test/main/show_explain.result b/mysql-test/main/show_explain.result index 1f44f7bc5ed..69dc506527b 100644 --- a/mysql-test/main/show_explain.result +++ b/mysql-test/main/show_explain.result @@ -28,7 +28,7 @@ select count(*) from t1 where a < 100000; connection default; show explain for $thr2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index a a 5 NULL 1000 Using where; Using index +1 SIMPLE t1 range a a 5 NULL 999 Using where; Using index Warnings: Note 1003 select count(*) from t1 where a < 100000 connection con1; diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result index 589144f1238..e2c9adbfc37 100644 --- a/mysql-test/main/subselect_sj2_mat.result +++ b/mysql-test/main/subselect_sj2_mat.result @@ -1753,6 +1753,7 @@ insert into t1(`id`,`local_name`) values (11,'Rollover - Internet Payday'), (12,'AL Monthly Installment'), (13,'AL Semi-Monthly Installment'); +insert into t1 select seq,"ignore" from seq_1000_to_1100; ANALYZE TABLE t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected @@ -1766,8 +1767,8 @@ OR (t.id IN (0,4,12,13,1,10,3,11)) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t index PRIMARY PRIMARY 4 NULL 13 Using where; Using index -2 MATERIALIZED A ALL PRIMARY NULL NULL NULL 13 +1 PRIMARY t index PRIMARY PRIMARY 4 NULL 114 Using where; Using index +2 MATERIALIZED A ALL PRIMARY NULL NULL NULL 114 2 MATERIALIZED <subquery3> eq_ref distinct_key distinct_key 67 func 1 3 MATERIALIZED B range PRIMARY PRIMARY 4 NULL 8 Using where SELECT SQL_NO_CACHE t.id diff --git a/mysql-test/main/subselect_sj2_mat.test b/mysql-test/main/subselect_sj2_mat.test index 96798ca0e48..4b768652670 100644 --- a/mysql-test/main/subselect_sj2_mat.test +++ b/mysql-test/main/subselect_sj2_mat.test @@ -2,6 +2,7 @@ # Run subselect_sj2.test with subquery materialization. # --source include/default_optimizer_switch.inc +--source include/have_sequence.inc set optimizer_switch='materialization=on'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; @@ -338,7 +339,7 @@ insert into t1(`id`,`local_name`) values (11,'Rollover - Internet Payday'), (12,'AL Monthly Installment'), (13,'AL Semi-Monthly Installment'); - +insert into t1 select seq,"ignore" from seq_1000_to_1100; ANALYZE TABLE t1; explain diff --git a/mysql-test/main/table_elim.result b/mysql-test/main/table_elim.result index bc03e1b251d..f054df036e9 100644 --- a/mysql-test/main/table_elim.result +++ b/mysql-test/main/table_elim.result @@ -419,7 +419,7 @@ select t1.* from t1 left join t2 on (t2.pk=3 and t2.b=3) or (t2.pk= 4 and t2.b=3); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 2 Using where drop table t1, t2; # # LPBUG#523593: Running RQG optimizer_no_subquery crashes MariaDB diff --git a/mysql-test/main/type_blob.result b/mysql-test/main/type_blob.result index cfb47f7b850..967fb37b230 100644 --- a/mysql-test/main/type_blob.result +++ b/mysql-test/main/type_blob.result @@ -625,6 +625,9 @@ id txt 3 NULL explain select * from t1 where txt='Chevy' or txt is NULL; id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL txt_index NULL NULL NULL 6 Using where +explain select * from t1 FORCE INDEX (`txt_index`) where txt='Chevy' or txt is NULL; +id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 3 Using where select * from t1 where txt='Chevy '; id txt diff --git a/mysql-test/main/type_blob.test b/mysql-test/main/type_blob.test index df565b187b4..7801280a8d2 100644 --- a/mysql-test/main/type_blob.test +++ b/mysql-test/main/type_blob.test @@ -362,6 +362,7 @@ insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL), ('Honda'), ('Subaru'), ('Honda'); select * from t1 where txt='Chevy' or txt is NULL; explain select * from t1 where txt='Chevy' or txt is NULL; +explain select * from t1 FORCE INDEX (`txt_index`) where txt='Chevy' or txt is NULL; select * from t1 where txt='Chevy '; select * from t1 where txt='Chevy ' or txt='Chevy'; select * from t1 where txt='Chevy' or txt='Chevy '; diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index b83dba5345e..acd66549d42 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -3034,7 +3034,7 @@ EXPLAIN SELECT * FROM t1 WHERE f1 IN 3784744,4180925,4559596,3963734,3856391,4494153) AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,idx1,idx2 PRIMARY 4 NULL 18 Using where +1 SIMPLE t1 index_merge PRIMARY,idx1,idx2 idx2,idx1,PRIMARY 7,60,4 NULL 1 Using intersect(idx2,idx1,PRIMARY); Using where set optimizer_switch=@tmp_innodb_mysql; DROP TABLE t1; # diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result index 0a7105fcfad..96793beae8a 100644 --- a/mysql-test/suite/maria/icp.result +++ b/mysql-test/suite/maria/icp.result @@ -409,7 +409,7 @@ WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 ORDER BY c1 LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,k1 PRIMARY 4 NULL 3 Using index condition; Using where; Rowid-ordered scan; Using filesort +1 SIMPLE t1 range PRIMARY,k1 k1 5 NULL 4 Using where DROP TABLE t1; # # @@ -653,7 +653,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 13 NULL 2 Using where; Rowid-ordered scan; Using filesort +1 SIMPLE t1 range b b 13 NULL # Using where; Rowid-ordered scan; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; @@ -665,7 +665,7 @@ SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range b b 13 NULL 2 Using index condition; Rowid-ordered scan; Using filesort +1 SIMPLE t1 range b b 13 NULL # Using index condition; Rowid-ordered scan; Using filesort SELECT * FROM t1 WHERE NOT(b = 'Texas') AND b BETWEEN 'wy' AND 'y' OR b = 'Pennsylvania' ORDER BY a; diff --git a/mysql-test/suite/maria/maria.result b/mysql-test/suite/maria/maria.result index 7a4283c4cc5..913bf4efbdf 100644 --- a/mysql-test/suite/maria/maria.result +++ b/mysql-test/suite/maria/maria.result @@ -400,7 +400,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 4 test.t2.a 1 explain select * from t1 where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 4 NULL 5 Using index condition +1 SIMPLE t1 ALL a NULL NULL NULL 10 Using where explain select * from t1 force index (a) where a=0 or a=2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 4 NULL 5 Using index condition diff --git a/sql/handler.h b/sql/handler.h index 421941c0b35..e9b52b16aa8 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -2905,7 +2905,7 @@ public: data_file_length(0), max_data_file_length(0), index_file_length(0), max_index_file_length(0), delete_length(0), auto_increment_value(0), records(0), deleted(0), mean_rec_length(0), - create_time(0), check_time(0), update_time(0), block_size(0), + create_time(0), check_time(0), update_time(0), block_size(8192), checksum(0), checksum_null(FALSE), mrr_length_per_rec(0) {} }; diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc index c000187626f..601268ec2f6 100644 --- a/sql/multi_range_read.cc +++ b/sql/multi_range_read.cc @@ -21,18 +21,6 @@ #include "sql_statistics.h" #include "rowid_filter.h" -static ulonglong key_block_no(TABLE *table, uint keyno, ha_rows keyentry_pos) -{ - size_t len= table->key_info[keyno].key_length + table->file->ref_length; - if (keyno == table->s->primary_key && - table->file->primary_key_is_clustered()) - len= table->s->stored_rec_length; - uint keys_per_block= (uint) (table->file->stats.block_size/2.0/len+1); - ulonglong block_no= !keyentry_pos ? 0 : - (keyentry_pos - 1) / keys_per_block + 1; - return block_no; -} - /**************************************************************************** * Default MRR implementation (MRR to non-MRR converter) ***************************************************************************/ @@ -60,24 +48,6 @@ static ulonglong key_block_no(TABLE *table, uint keyno, ha_rows keyentry_pos) for a user to be able to interrupt the calculation by killing the connection/query. - @note - Starting from 10.4 the implementation of this method tries to take into - account gaps between range intervals. Before this we had such paradoxical - cases when, for example, the cost of the index scan by range [1..3] was - almost twice as less than the cost of of the index scan by two intervals - [1..1] and [3..3]. - - @note - The current implementation of the method is not efficient for it - requires extra dives for gaps. Although these dives are not expensive - as they touch the index nodes that with very high probability are in - cache this is still not good. We could avoid it if records in range - also returned positions of the ends of range intervals. It's not - hard to implement it now for MyISAM as this engine provides a function - returning an approximation of the relative position of a key tuple - among other index key tuples. Unfortunately InnoDB now does not provide - anything like this function. - @retval HA_POS_ERROR Error or the engine is unable to perform the requested scan. Values of OUT parameters are undefined. @@ -92,24 +62,23 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, uint *bufsz, uint *flags, Cost_estimate *cost) { KEY_MULTI_RANGE range; - key_range prev_start_key; range_seq_t seq_it; - ha_rows min_pos= 0; ha_rows total_rows= 0; uint n_ranges=0; uint n_eq_ranges= 0; ulonglong total_touched_blocks= 0; - key_range *prev_min_endp= 0; - ulonglong prev_max_block_no=0; ha_rows max_rows= stats.records; THD *thd= table->in_use; - StringBuffer<64> key_value; - uint limit= thd->variables.eq_range_index_dive_limit; - bool use_statistics_for_eq_range= eq_ranges_exceeds_limit(seq, seq_init_param, limit); + uint len= table->key_info[keyno].key_length + table->file->ref_length; + if (keyno == table->s->primary_key && table->file->primary_key_is_clustered()) + len= table->s->stored_rec_length; + /* Assume block is 75 % full */ + uint avg_block_records= ((uint) (table->file->stats.block_size*3/4))/len + 1; + DBUG_ENTER("multi_range_read_info_const"); /* Default MRR implementation doesn't need buffer */ *bufsz= 0; @@ -118,10 +87,9 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, while (!seq->next(seq_it, &range)) { ha_rows rows; - ulonglong new_touched_blocks= 0; if (unlikely(thd->killed != 0)) - return HA_POS_ERROR; + DBUG_RETURN(HA_POS_ERROR); n_ranges++; if (range.range_flag & EQ_RANGE) @@ -153,8 +121,6 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, } else { - ulonglong min_block_no; - ulonglong max_block_no; if ((range.range_flag & UNIQUE_RANGE) && !(range.range_flag & NULL_RANGE)) rows= 1; /* there can be at most one row */ else if (HA_POS_ERROR == (rows= this->records_in_range(keyno, min_endp, @@ -164,41 +130,9 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, total_rows= HA_POS_ERROR; break; } - if (!max_endp && !(prev_min_endp && prev_min_endp->length)) - min_pos+= max_rows - rows; - else - { - key_range *start_endp= prev_min_endp; - if (start_endp && !start_endp->keypart_map) - start_endp= 0; - /* - Get the estimate of rows in the previous gap - and two ranges surrounding this gap - */ - ha_rows r= this->records_in_range(keyno,start_endp,max_endp); - if (r == HA_POS_ERROR) - { - /* Some engine cannot estimate such ranges */ - total_rows += rows; - continue; - } - min_pos+= r - rows; - } - min_block_no= key_block_no(this->table, keyno, min_pos); - max_block_no= key_block_no(this->table, keyno, min_pos + rows); - new_touched_blocks= max_block_no - min_block_no + - MY_TEST(min_block_no != prev_max_block_no); - prev_max_block_no= max_block_no; - if (!prev_min_endp) - prev_min_endp= &prev_start_key; - /* Save range.start_key for the next iteration step */ - prev_start_key= range.start_key; - key_value.copy((const char *) prev_start_key.key, prev_start_key.length, - key_value.charset()); - prev_start_key.key= (const uchar *) key_value.ptr(); } total_rows += rows; - total_touched_blocks+= new_touched_blocks; + total_touched_blocks+= (rows / avg_block_records +1); } if (total_rows != HA_POS_ERROR) @@ -225,13 +159,18 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, } else { - cost->io_count= read_time(keyno, - (uint)total_touched_blocks, - (uint) total_rows); + cost->io_count= read_time(keyno, n_ranges, (uint) total_rows); cost->cpu_cost= (double) total_rows / TIME_FOR_COMPARE + 0.01; } } - return total_rows; + DBUG_PRINT("statistics", + ("key: %s rows: %llu total_cost: %.3f io_blocks: %llu " + "idx_io_count: %.3f cpu_cost: %.3f io_count: %.3f", + table->s->keynames.type_names[keyno], + (ulonglong) total_rows, cost->total_cost(), + (ulonglong) total_touched_blocks, + cost->idx_io_count, cost->cpu_cost, cost->io_count)); + DBUG_RETURN(total_rows); } diff --git a/storage/connect/mysql-test/connect/r/mysql_index.result b/storage/connect/mysql-test/connect/r/mysql_index.result index b0c88b16fef..e81caf671a4 100644 --- a/storage/connect/mysql-test/connect/r/mysql_index.result +++ b/storage/connect/mysql-test/connect/r/mysql_index.result @@ -164,270 +164,273 @@ matricule nom prenom sexe aanais mmnais ddentree ddnom brut net service sitmat f 4974 LONES GERARD 1 1959 10 1979-01-01 1994-12-01 16081 12916.70 0 M SANS SELECT matricule, nom, prenom FROM t2 WHERE nom IN ('FOCH','MOGADOR'); matricule nom prenom -3368 MOGADOR ALAIN 1977 FOCH BERNADETTE -4080 FOCH SERGE -2552 FOCH FRANCK 5707 FOCH DENIS +2552 FOCH FRANCK 2634 FOCH JOCELYNE 5765 FOCH ROBERT +4080 FOCH SERGE +3368 MOGADOR ALAIN +explain SELECT matricule, nom, prenom FROM t2 WHERE nom IN ('FOCH','MOGADOR'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range NP NP 18 NULL 4 Using where SELECT matricule, nom, prenom FROM t2 WHERE nom = 'FOCH' OR nom = 'MOGADOR'; matricule nom prenom -3368 MOGADOR ALAIN 1977 FOCH BERNADETTE -4080 FOCH SERGE -2552 FOCH FRANCK 5707 FOCH DENIS +2552 FOCH FRANCK 2634 FOCH JOCELYNE 5765 FOCH ROBERT +4080 FOCH SERGE +3368 MOGADOR ALAIN SELECT matricule, nom, prenom FROM t2 WHERE nom < 'ADDAX'; matricule nom prenom +1122 ACACIAS SERGE +115 ACHILLE JACQUES 1340 ABBE MICHELE +1644 ACARDIE BEATE 2728 ABOUT CATHERINE MARIE -895 ABORD CHANTAL +2945 ABBEVILLE PASCAL +307 ABBAYE ANNICK +3395 ADAM JEAN CLAUDE +398 ABREUVOIR JEAN LUC 4038 ADAM JANICK -6627 ABBAYE GERALD -6124 ABELIAS DELIA 4552 ABBADIE MONIQUE -8673 ABEL JEAN PIERRE -3395 ADAM JEAN CLAUDE -2945 ABBEVILLE PASCAL -115 ACHILLE JACQUES +6124 ABELIAS DELIA +6314 ABERDEN EVELYNE 6399 ABEILLES RENE +6627 ABBAYE GERALD +7961 ABBE KATIA 8596 ABEBERRY PATRICK +8673 ABEL JEAN PIERRE +895 ABORD CHANTAL 9270 ABBE SOPHIE -398 ABREUVOIR JEAN LUC -7961 ABBE KATIA -307 ABBAYE ANNICK -6314 ABERDEN EVELYNE -1644 ACARDIE BEATE -1122 ACACIAS SERGE SELECT matricule, nom, prenom FROM t2 WHERE nom <= 'ABEL'; matricule nom prenom 1340 ABBE MICHELE -6627 ABBAYE GERALD -4552 ABBADIE MONIQUE -8673 ABEL JEAN PIERRE 2945 ABBEVILLE PASCAL +307 ABBAYE ANNICK +4552 ABBADIE MONIQUE 6399 ABEILLES RENE +6627 ABBAYE GERALD +7961 ABBE KATIA 8596 ABEBERRY PATRICK +8673 ABEL JEAN PIERRE 9270 ABBE SOPHIE -7961 ABBE KATIA -307 ABBAYE ANNICK SELECT matricule, nom, prenom FROM t2 WHERE nom > 'YVON'; matricule nom prenom -9742 YZENGREMER MICHEL -5357 ZOLA BERNARD -5441 ZOLA BRIGITTE +1325 ZOLA CHRISTINE 4102 ZOUAVES ALAIN 4859 ZORI CATHERINE -1325 ZOLA CHRISTINE +5357 ZOLA BERNARD +5441 ZOLA BRIGITTE 8738 ZILINA JEAN LOUIS +9742 YZENGREMER MICHEL SELECT matricule, nom, prenom FROM t2 WHERE nom >= 'YVON'; matricule nom prenom -9742 YZENGREMER MICHEL +1325 ZOLA CHRISTINE +4102 ZOUAVES ALAIN +4859 ZORI CATHERINE 5357 ZOLA BERNARD 5389 YVON CAROLE 5441 ZOLA BRIGITTE -4102 ZOUAVES ALAIN -4859 ZORI CATHERINE -1325 ZOLA CHRISTINE 8738 ZILINA JEAN LOUIS +9742 YZENGREMER MICHEL SELECT matricule, nom, prenom FROM t2 WHERE nom <= 'ABEL' OR nom > 'YVON'; matricule nom prenom -9742 YZENGREMER MICHEL +1325 ZOLA CHRISTINE 1340 ABBE MICHELE -5357 ZOLA BERNARD -6627 ABBAYE GERALD -4552 ABBADIE MONIQUE -5441 ZOLA BRIGITTE +2945 ABBEVILLE PASCAL +307 ABBAYE ANNICK 4102 ZOUAVES ALAIN -8673 ABEL JEAN PIERRE +4552 ABBADIE MONIQUE 4859 ZORI CATHERINE -2945 ABBEVILLE PASCAL -1325 ZOLA CHRISTINE +5357 ZOLA BERNARD +5441 ZOLA BRIGITTE 6399 ABEILLES RENE -8596 ABEBERRY PATRICK -9270 ABBE SOPHIE +6627 ABBAYE GERALD 7961 ABBE KATIA -307 ABBAYE ANNICK +8596 ABEBERRY PATRICK +8673 ABEL JEAN PIERRE 8738 ZILINA JEAN LOUIS +9270 ABBE SOPHIE +9742 YZENGREMER MICHEL SELECT matricule, nom, prenom FROM t2 WHERE nom > 'HELEN' AND nom < 'HEROS'; matricule nom prenom +1291 HERMITAGE XAVIER 2085 HEOL GUY PAUL +2579 HERANDIERE PIERRE 2673 HENNER LILIANE -7093 HERAULTS DANIEL -7626 HENIN PHILIPPE +3309 HELENE ISABELLE 403 HERMITTE PHILIPPE +4050 HERBILLON FRANCOIS 4254 HENIN SERGE 4666 HELLEN PIERRE -3309 HELENE ISABELLE -9749 HEROLD ISABELLE -9716 HENRI JACQUES -1291 HERMITAGE XAVIER -8365 HELIOTROPES LISE -4050 HERBILLON FRANCOIS -9231 HERBILLON MADELEINE -9096 HELENA PHILIPPE 5781 HELSINKI DANIELLE -2579 HERANDIERE PIERRE 6185 HERMITTE FRANCOIS +7093 HERAULTS DANIEL +7626 HENIN PHILIPPE +8365 HELIOTROPES LISE +9096 HELENA PHILIPPE +9231 HERBILLON MADELEINE +9716 HENRI JACQUES +9749 HEROLD ISABELLE SELECT matricule, nom, prenom FROM t2 WHERE nom BETWEEN 'HELEN' AND 'HEROS'; matricule nom prenom +1291 HERMITAGE XAVIER 2085 HEOL GUY PAUL +2579 HERANDIERE PIERRE 2673 HENNER LILIANE -7093 HERAULTS DANIEL -7626 HENIN PHILIPPE +3309 HELENE ISABELLE 403 HERMITTE PHILIPPE +4050 HERBILLON FRANCOIS 4254 HENIN SERGE 4666 HELLEN PIERRE -3309 HELENE ISABELLE -9749 HEROLD ISABELLE -9716 HENRI JACQUES -1291 HERMITAGE XAVIER +5781 HELSINKI DANIELLE +6185 HERMITTE FRANCOIS +6199 HELEN MARTIAL +7093 HERAULTS DANIEL +7626 HENIN PHILIPPE 8365 HELIOTROPES LISE -4050 HERBILLON FRANCOIS -9231 HERBILLON MADELEINE 8445 HEROS SYLVIE 9096 HELENA PHILIPPE -5781 HELSINKI DANIELLE -2579 HERANDIERE PIERRE -6199 HELEN MARTIAL -6185 HERMITTE FRANCOIS +9231 HERBILLON MADELEINE +9716 HENRI JACQUES +9749 HEROLD ISABELLE SELECT matricule, nom, prenom FROM t2 WHERE nom BETWEEN 'HELEN' AND 'HEROS' AND prenom = 'PHILIPPE'; matricule nom prenom -7626 HENIN PHILIPPE 403 HERMITTE PHILIPPE +7626 HENIN PHILIPPE 9096 HELENA PHILIPPE SELECT matricule, nom, prenom FROM t2 ORDER BY nom LIMIT 10; matricule nom prenom +1340 ABBE MICHELE +2945 ABBEVILLE PASCAL +307 ABBAYE ANNICK 4552 ABBADIE MONIQUE +6399 ABEILLES RENE 6627 ABBAYE GERALD -307 ABBAYE ANNICK 7961 ABBE KATIA -1340 ABBE MICHELE -9270 ABBE SOPHIE -2945 ABBEVILLE PASCAL 8596 ABEBERRY PATRICK -6399 ABEILLES RENE 8673 ABEL JEAN PIERRE +9270 ABBE SOPHIE SELECT a.nom, a.prenom, b.nom FROM t1 a STRAIGHT_JOIN t2 b ON a.prenom = b.prenom WHERE a.nom = 'FOCH' AND a.nom != b.nom; nom prenom nom -FOCH SERGE ACACIAS -FOCH ROBERT AGRIANT -FOCH JOCELYNE ALEXIS +FOCH BERNADETTE BERTIN +FOCH BERNADETTE BOISSY +FOCH BERNADETTE HUNTZIGER +FOCH BERNADETTE LATECOERE +FOCH BERNADETTE LEGER +FOCH BERNADETTE MONTJUSTIN +FOCH BERNADETTE ONZE +FOCH BERNADETTE PALMAROLE +FOCH BERNADETTE PLOUHARNEL FOCH DENIS AMBOISE -FOCH SERGE ANDALUCIA -FOCH ROBERT ANNECY -FOCH SERGE ARCACHON -FOCH JOCELYNE AUGUSTE -FOCH JOCELYNE BASSE -FOCH SERGE BEACH -FOCH FRANCK BEARN -FOCH SERGE BELLES FOCH DENIS BERARD FOCH DENIS BERIN -FOCH BERNADETTE BERTIN FOCH DENIS BILLEHOU FOCH DENIS BOILEAU -FOCH BERNADETTE BOISSY -FOCH ROBERT BONVIN -FOCH SERGE BOUTON -FOCH SERGE BREUIL -FOCH SERGE CARREFOUR -FOCH JOCELYNE CARRERE -FOCH JOCELYNE CHAPELLE -FOCH SERGE CHATEAU -FOCH ROBERT CHENIER -FOCH SERGE COLLETTE FOCH DENIS CONNE -FOCH SERGE COOLE FOCH DENIS COULOUBRIER FOCH DENIS COUTURIER -FOCH ROBERT CURAT -FOCH ROBERT DAUDET -FOCH SERGE ECLUSE -FOCH SERGE EGUILLON FOCH DENIS EPINETTES FOCH DENIS FIGOURNAS -FOCH JOCELYNE FLEMING -FOCH JOCELYNE GAMBADES -FOCH ROBERT GIOTERAIE -FOCH SERGE GOAS -FOCH ROBERT GRAFFIANE -FOCH SERGE GREFFIER -FOCH ROBERT GUILLOTIERE -FOCH SERGE HENIN -FOCH BERNADETTE HUNTZIGER -FOCH FRANCK ILLIERS FOCH DENIS ISTANBUL FOCH DENIS ITALIE -FOCH SERGE JARDIN +FOCH DENIS LACATE +FOCH DENIS MAROLLES +FOCH DENIS MONTELIER +FOCH DENIS MONTILS +FOCH DENIS POINTE +FOCH DENIS PORTO +FOCH DENIS REINOTS +FOCH DENIS REMPART +FOCH DENIS ROUSSIER +FOCH DENIS TORTE +FOCH DENIS TOULON +FOCH DENIS VALMANTE +FOCH FRANCK BEARN +FOCH FRANCK ILLIERS FOCH FRANCK JEANPIERRE -FOCH JOCELYNE KENNEDY FOCH FRANCK LABBE -FOCH DENIS LACATE FOCH FRANCK LACOMBE -FOCH ROBERT LAMOTHE -FOCH BERNADETTE LATECOERE -FOCH BERNADETTE LEGER -FOCH SERGE LEONIE FOCH FRANCK LEROY -FOCH SERGE LOZERE -FOCH DENIS MAROLLES -FOCH ROBERT MARRONIERS -FOCH SERGE MARSAT -FOCH SERGE MONTAGNE FOCH FRANCK MONTALEIGNE -FOCH DENIS MONTELIER -FOCH DENIS MONTILS -FOCH BERNADETTE MONTJUSTIN -FOCH SERGE MORIZET -FOCH ROBERT NIMES -FOCH ROBERT NORD -FOCH SERGE NOVEMBRE -FOCH BERNADETTE ONZE -FOCH SERGE ORANGERIE FOCH FRANCK ORVEAU -FOCH BERNADETTE PALMAROLE +FOCH FRANCK PURPAN +FOCH FRANCK ROCQUENCOURT +FOCH FRANCK RUSSIE +FOCH JOCELYNE ALEXIS +FOCH JOCELYNE AUGUSTE +FOCH JOCELYNE BASSE +FOCH JOCELYNE CARRERE +FOCH JOCELYNE CHAPELLE +FOCH JOCELYNE FLEMING +FOCH JOCELYNE GAMBADES +FOCH JOCELYNE KENNEDY FOCH JOCELYNE PEYBERT +FOCH JOCELYNE PIED +FOCH JOCELYNE PONTAROUX +FOCH ROBERT AGRIANT +FOCH ROBERT ANNECY +FOCH ROBERT BONVIN +FOCH ROBERT CHENIER +FOCH ROBERT CURAT +FOCH ROBERT DAUDET +FOCH ROBERT GIOTERAIE +FOCH ROBERT GRAFFIANE +FOCH ROBERT GUILLOTIERE +FOCH ROBERT LAMOTHE +FOCH ROBERT MARRONIERS +FOCH ROBERT NIMES +FOCH ROBERT NORD FOCH ROBERT PEYNIBLOU FOCH ROBERT PIECE -FOCH JOCELYNE PIED FOCH ROBERT PLAGNE -FOCH SERGE PLAISANCE -FOCH BERNADETTE PLOUHARNEL -FOCH DENIS POINTE FOCH ROBERT POMMERY -FOCH JOCELYNE PONTAROUX -FOCH DENIS PORTO FOCH ROBERT PRESIDENT FOCH ROBERT PUJADE -FOCH FRANCK PURPAN FOCH ROBERT QUILICHINI -FOCH DENIS REINOTS -FOCH DENIS REMPART -FOCH SERGE RESISTANCE -FOCH SERGE RESTANQUES FOCH ROBERT RIOU -FOCH FRANCK ROCQUENCOURT FOCH ROBERT ROLL FOCH ROBERT ROSSA -FOCH SERGE ROSSAYS -FOCH DENIS ROUSSIER -FOCH FRANCK RUSSIE FOCH ROBERT SABLONS -FOCH SERGE SARTRE -FOCH SERGE SAVIGNAC -FOCH SERGE SEGUR FOCH ROBERT STRASBOURG FOCH ROBERT TIRE -FOCH DENIS TORTE -FOCH DENIS TOULON FOCH ROBERT TUBY -FOCH DENIS VALMANTE -FOCH SERGE VANOEL FOCH ROBERT VIARMES +FOCH SERGE ACACIAS +FOCH SERGE ANDALUCIA +FOCH SERGE ARCACHON +FOCH SERGE BEACH +FOCH SERGE BELLES +FOCH SERGE BOUTON +FOCH SERGE BREUIL +FOCH SERGE CARREFOUR +FOCH SERGE CHATEAU +FOCH SERGE COLLETTE +FOCH SERGE COOLE +FOCH SERGE ECLUSE +FOCH SERGE EGUILLON +FOCH SERGE GOAS +FOCH SERGE GREFFIER +FOCH SERGE HENIN +FOCH SERGE JARDIN +FOCH SERGE LEONIE +FOCH SERGE LOZERE +FOCH SERGE MARSAT +FOCH SERGE MONTAGNE +FOCH SERGE MORIZET +FOCH SERGE NOVEMBRE +FOCH SERGE ORANGERIE +FOCH SERGE PLAISANCE +FOCH SERGE RESISTANCE +FOCH SERGE RESTANQUES +FOCH SERGE ROSSAYS +FOCH SERGE SARTRE +FOCH SERGE SAVIGNAC +FOCH SERGE SEGUR +FOCH SERGE VANOEL FOCH SERGE WILSON DROP TABLE t2; DROP TABLE t1; diff --git a/storage/connect/mysql-test/connect/t/mysql_index.test b/storage/connect/mysql-test/connect/t/mysql_index.test index 74dc48f42c8..ebc4965e8e1 100644 --- a/storage/connect/mysql-test/connect/t/mysql_index.test +++ b/storage/connect/mysql-test/connect/t/mysql_index.test @@ -111,16 +111,28 @@ CREATE TABLE t2 ) ENGINE=CONNECT TABLE_TYPE=MYSQL CONNECTIOn='mysql://root@localhost/test/t1'; SELECT * FROM t2 limit 10; SELECT matricule, nom, prenom FROM t2 WHERE nom IN ('FOCH','MOGADOR'); +--sorted_result +explain SELECT matricule, nom, prenom FROM t2 WHERE nom IN ('FOCH','MOGADOR'); SELECT matricule, nom, prenom FROM t2 WHERE nom = 'FOCH' OR nom = 'MOGADOR'; +--sorted_result SELECT matricule, nom, prenom FROM t2 WHERE nom < 'ADDAX'; +--sorted_result SELECT matricule, nom, prenom FROM t2 WHERE nom <= 'ABEL'; +--sorted_result SELECT matricule, nom, prenom FROM t2 WHERE nom > 'YVON'; +--sorted_result SELECT matricule, nom, prenom FROM t2 WHERE nom >= 'YVON'; +--sorted_result SELECT matricule, nom, prenom FROM t2 WHERE nom <= 'ABEL' OR nom > 'YVON'; +--sorted_result SELECT matricule, nom, prenom FROM t2 WHERE nom > 'HELEN' AND nom < 'HEROS'; +--sorted_result SELECT matricule, nom, prenom FROM t2 WHERE nom BETWEEN 'HELEN' AND 'HEROS'; +--sorted_result SELECT matricule, nom, prenom FROM t2 WHERE nom BETWEEN 'HELEN' AND 'HEROS' AND prenom = 'PHILIPPE'; +--sorted_result SELECT matricule, nom, prenom FROM t2 ORDER BY nom LIMIT 10; +--sorted_result SELECT a.nom, a.prenom, b.nom FROM t1 a STRAIGHT_JOIN t2 b ON a.prenom = b.prenom WHERE a.nom = 'FOCH' AND a.nom != b.nom; DROP TABLE t2; diff --git a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_between.result b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_between.result index 9af4a4fedcc..c4b1dbf7141 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_between.result +++ b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_between.result @@ -9,6 +9,7 @@ INSERT INTO users (age) VALUES (28); INSERT INTO users (age) VALUES (29); INSERT INTO users (age) VALUES (30); INSERT INTO users (age) VALUES (31); +INSERT INTO users (age) VALUES (32),(33),(34),(35),(36),(37); SELECT COUNT(*) FROM users WHERE age BETWEEN 28 AND 30; COUNT(*) 3 diff --git a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_greater.result b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_greater.result index d5c033083cc..4eba1922134 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_greater.result +++ b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_greater.result @@ -9,6 +9,7 @@ INSERT INTO users (age) VALUES (28); INSERT INTO users (age) VALUES (29); INSERT INTO users (age) VALUES (30); INSERT INTO users (age) VALUES (31); +INSERT INTO users (age) VALUES (1),(2),(3),(4),(5),(6); SELECT COUNT(*) FROM users WHERE age > 29; COUNT(*) 2 diff --git a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_greater_equal.result b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_greater_equal.result index 59b812d1484..4de49651b81 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_greater_equal.result +++ b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_greater_equal.result @@ -9,6 +9,7 @@ INSERT INTO users (age) VALUES (28); INSERT INTO users (age) VALUES (29); INSERT INTO users (age) VALUES (30); INSERT INTO users (age) VALUES (31); +INSERT INTO users (age) VALUES (1),(2),(3),(4),(5),(6); SELECT COUNT(*) FROM users WHERE age >= 29; COUNT(*) 3 diff --git a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_less.result b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_less.result index b5e4fc1c3de..a57a3b8fa6f 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_less.result +++ b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_less.result @@ -9,6 +9,7 @@ INSERT INTO users (age) VALUES (28); INSERT INTO users (age) VALUES (29); INSERT INTO users (age) VALUES (30); INSERT INTO users (age) VALUES (31); +INSERT INTO users (age) VALUES (32),(33),(34),(35),(36),(37); SELECT COUNT(*) FROM users WHERE age < 29; COUNT(*) 2 diff --git a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_less_equal.result b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_less_equal.result index f062fe0092b..c770b63daf8 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_less_equal.result +++ b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_less_equal.result @@ -9,6 +9,7 @@ INSERT INTO users (age) VALUES (28); INSERT INTO users (age) VALUES (29); INSERT INTO users (age) VALUES (30); INSERT INTO users (age) VALUES (31); +INSERT INTO users (age) VALUES (32),(33),(34),(35),(36),(37); SELECT COUNT(*) FROM users WHERE age <= 29; COUNT(*) 3 diff --git a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_not_equal.result b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_not_equal.result index a1a123e7d5f..199edf9d758 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_not_equal.result +++ b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_index_not_equal.result @@ -14,5 +14,5 @@ COUNT(*) 2 SHOW STATUS LIKE 'mroonga_count_skip'; Variable_name Value -Mroonga_count_skip 2 +Mroonga_count_skip 0 DROP TABLE users; diff --git a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_primary_key_between.result b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_primary_key_between.result index 68a0575ab97..26c8b6a9d94 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_primary_key_between.result +++ b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_primary_key_between.result @@ -8,6 +8,7 @@ INSERT INTO users (id) VALUES (2); INSERT INTO users (id) VALUES (3); INSERT INTO users (id) VALUES (4); INSERT INTO users (id) VALUES (5); +INSERT INTO users (id) VALUES (32),(33),(34),(35),(36),(37); SELECT COUNT(*) FROM users WHERE id BETWEEN 2 AND 4; COUNT(*) 3 diff --git a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_primary_key_greater.result b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_primary_key_greater.result index 3ae04a0246f..fe9de9e631d 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_primary_key_greater.result +++ b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_primary_key_greater.result @@ -8,6 +8,7 @@ INSERT INTO users (id) VALUES (2); INSERT INTO users (id) VALUES (3); INSERT INTO users (id) VALUES (4); INSERT INTO users (id) VALUES (5); +INSERT INTO users (id) VALUES (-1),(-2),(-3),(-4); SELECT COUNT(*) FROM users WHERE id > 3; COUNT(*) 2 diff --git a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_primary_key_greater_equal.result b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_primary_key_greater_equal.result index 0f0643110f0..2fa5213ea49 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_primary_key_greater_equal.result +++ b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_primary_key_greater_equal.result @@ -8,6 +8,7 @@ INSERT INTO users (id) VALUES (2); INSERT INTO users (id) VALUES (3); INSERT INTO users (id) VALUES (4); INSERT INTO users (id) VALUES (5); +INSERT INTO users (id) VALUES (-1),(-2),(-3),(-4); SELECT COUNT(*) FROM users WHERE id >= 3; COUNT(*) 3 diff --git a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_primary_key_not_equal.result b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_primary_key_not_equal.result index 64761c9565d..1b253a8edc6 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_primary_key_not_equal.result +++ b/storage/mroonga/mysql-test/mroonga/storage/r/optimization_count_skip_primary_key_not_equal.result @@ -13,5 +13,5 @@ COUNT(*) 4 SHOW STATUS LIKE 'mroonga_count_skip'; Variable_name Value -Mroonga_count_skip 1 +Mroonga_count_skip 0 DROP TABLE users; diff --git a/storage/mroonga/mysql-test/mroonga/storage/r/variable_max_n_records_for_estimate_not_found_in_limit.result b/storage/mroonga/mysql-test/mroonga/storage/r/variable_max_n_records_for_estimate_not_found_in_limit.result index e2d8a8de493..d318a654c6c 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/r/variable_max_n_records_for_estimate_not_found_in_limit.result +++ b/storage/mroonga/mysql-test/mroonga/storage/r/variable_max_n_records_for_estimate_not_found_in_limit.result @@ -6,6 +6,7 @@ INDEX (id) INSERT INTO ids VALUES (1); INSERT INTO ids VALUES (2); INSERT INTO ids VALUES (3); +INSERT INTO ids VALUES (4),(5),(6),(7); DELETE FROM ids WHERE id < 2; SET mroonga_max_n_records_for_estimate = 1; EXPLAIN SELECT * FROM ids WHERE id > 0; diff --git a/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_between.test b/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_between.test index c3fb2bec507..76675fdeb76 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_between.test +++ b/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_between.test @@ -32,6 +32,7 @@ INSERT INTO users (age) VALUES (28); INSERT INTO users (age) VALUES (29); INSERT INTO users (age) VALUES (30); INSERT INTO users (age) VALUES (31); +INSERT INTO users (age) VALUES (32),(33),(34),(35),(36),(37); SELECT COUNT(*) FROM users WHERE age BETWEEN 28 AND 30; diff --git a/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_greater.test b/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_greater.test index 4b814e494f9..fb5c336cd4c 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_greater.test +++ b/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_greater.test @@ -32,6 +32,7 @@ INSERT INTO users (age) VALUES (28); INSERT INTO users (age) VALUES (29); INSERT INTO users (age) VALUES (30); INSERT INTO users (age) VALUES (31); +INSERT INTO users (age) VALUES (1),(2),(3),(4),(5),(6); SELECT COUNT(*) FROM users WHERE age > 29; diff --git a/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_greater_equal.test b/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_greater_equal.test index f26ed636b5a..c897d8d88de 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_greater_equal.test +++ b/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_greater_equal.test @@ -32,6 +32,7 @@ INSERT INTO users (age) VALUES (28); INSERT INTO users (age) VALUES (29); INSERT INTO users (age) VALUES (30); INSERT INTO users (age) VALUES (31); +INSERT INTO users (age) VALUES (1),(2),(3),(4),(5),(6); SELECT COUNT(*) FROM users WHERE age >= 29; diff --git a/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_less.test b/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_less.test index 0a7be348bd5..f03156d545b 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_less.test +++ b/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_less.test @@ -32,6 +32,7 @@ INSERT INTO users (age) VALUES (28); INSERT INTO users (age) VALUES (29); INSERT INTO users (age) VALUES (30); INSERT INTO users (age) VALUES (31); +INSERT INTO users (age) VALUES (32),(33),(34),(35),(36),(37); SELECT COUNT(*) FROM users WHERE age < 29; diff --git a/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_less_equal.test b/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_less_equal.test index 190d242d932..20863705bf4 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_less_equal.test +++ b/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_index_less_equal.test @@ -32,6 +32,7 @@ INSERT INTO users (age) VALUES (28); INSERT INTO users (age) VALUES (29); INSERT INTO users (age) VALUES (30); INSERT INTO users (age) VALUES (31); +INSERT INTO users (age) VALUES (32),(33),(34),(35),(36),(37); SELECT COUNT(*) FROM users WHERE age <= 29; diff --git a/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_primary_key_between.test b/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_primary_key_between.test index 73754afda63..25d6c734fe1 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_primary_key_between.test +++ b/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_primary_key_between.test @@ -31,6 +31,7 @@ INSERT INTO users (id) VALUES (2); INSERT INTO users (id) VALUES (3); INSERT INTO users (id) VALUES (4); INSERT INTO users (id) VALUES (5); +INSERT INTO users (id) VALUES (32),(33),(34),(35),(36),(37); SELECT COUNT(*) FROM users WHERE id BETWEEN 2 AND 4; diff --git a/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_primary_key_greater.test b/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_primary_key_greater.test index 44ba5ead9f2..f91cf19310f 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_primary_key_greater.test +++ b/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_primary_key_greater.test @@ -31,9 +31,9 @@ INSERT INTO users (id) VALUES (2); INSERT INTO users (id) VALUES (3); INSERT INTO users (id) VALUES (4); INSERT INTO users (id) VALUES (5); +INSERT INTO users (id) VALUES (-1),(-2),(-3),(-4); SELECT COUNT(*) FROM users WHERE id > 3; - SHOW STATUS LIKE 'mroonga_count_skip'; DROP TABLE users; diff --git a/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_primary_key_greater_equal.test b/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_primary_key_greater_equal.test index 8d442692950..dd374012257 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_primary_key_greater_equal.test +++ b/storage/mroonga/mysql-test/mroonga/storage/t/optimization_count_skip_primary_key_greater_equal.test @@ -31,6 +31,7 @@ INSERT INTO users (id) VALUES (2); INSERT INTO users (id) VALUES (3); INSERT INTO users (id) VALUES (4); INSERT INTO users (id) VALUES (5); +INSERT INTO users (id) VALUES (-1),(-2),(-3),(-4); SELECT COUNT(*) FROM users WHERE id >= 3; diff --git a/storage/mroonga/mysql-test/mroonga/storage/t/variable_max_n_records_for_estimate_not_found_in_limit.test b/storage/mroonga/mysql-test/mroonga/storage/t/variable_max_n_records_for_estimate_not_found_in_limit.test index d67a832ad95..79c75308c9f 100644 --- a/storage/mroonga/mysql-test/mroonga/storage/t/variable_max_n_records_for_estimate_not_found_in_limit.test +++ b/storage/mroonga/mysql-test/mroonga/storage/t/variable_max_n_records_for_estimate_not_found_in_limit.test @@ -29,6 +29,7 @@ CREATE TABLE ids ( INSERT INTO ids VALUES (1); INSERT INTO ids VALUES (2); INSERT INTO ids VALUES (3); +INSERT INTO ids VALUES (4),(5),(6),(7); DELETE FROM ids WHERE id < 2; diff --git a/storage/oqgraph/mysql-test/oqgraph/regression_mdev6282.result b/storage/oqgraph/mysql-test/oqgraph/regression_mdev6282.result index e0d9b3efe99..3ef61cc3e37 100644 --- a/storage/oqgraph/mysql-test/oqgraph/regression_mdev6282.result +++ b/storage/oqgraph/mysql-test/oqgraph/regression_mdev6282.result @@ -28,12 +28,14 @@ SELECT `db`.`version`, `db`.`nodeID` FROM `version_history` AS `v` INNER JOIN `db_history` AS `db` ON `db`.`nodeID` = `v`.`linkid` WHERE `latch` = 'breadth_first' AND `origid` = '1' ORDER BY `weight` DESC LIMIT 1; version nodeID +0.0.3 3 disconnect con1; connect con2,localhost,root,,test; SELECT `db`.`version`, `db`.`nodeID` FROM `version_history` AS `v` INNER JOIN `db_history` AS `db` ON `db`.`nodeID` = `v`.`linkid` WHERE `latch` = 'breadth_first' AND `origid` = '1' ORDER BY `weight` DESC LIMIT 1; version nodeID +0.0.3 3 disconnect con2; connect con3,localhost,root,,test; DROP TABLE version_history; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb2.result b/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb2.result index b34ec78cd03..efe4eaf2141 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb2.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/index_merge_rocksdb2.result @@ -479,7 +479,7 @@ a filler b must use union, not sort-union: explain select * from t2 where a=4 or b=4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL a,b NULL NULL NULL # Using where +1 SIMPLE t2 index_merge a,b a,b 5,5 NULL # Using union(a,b); Using where select * from t2 where a=4 or b=4; a filler b 4 4 0 diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_char_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_char_indexes.result index 413c3f69f23..3a99e6a1ac8 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_char_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_char_indexes.result @@ -45,7 +45,7 @@ t1 1 v16 1 v16 A 500 NULL NULL YES LSMTREE INSERT INTO t1 (c,c20,v16,v128,pk) VALUES ('a','char1','varchar1a','varchar1b','1'),('a','char2','varchar2a','varchar2b','2'),('b','char3','varchar1a','varchar1b','3'),('c','char4','varchar3a','varchar3b','4'); EXPLAIN SELECT SUBSTRING(v16,0,3) FROM t1 WHERE v16 LIKE 'varchar%'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index v16 v16 21 NULL # Using where; Using index +1 SIMPLE t1 range v16 v16 19 NULL # Using where; Using index SELECT SUBSTRING(v16,7,3) FROM t1 WHERE v16 LIKE 'varchar%'; SUBSTRING(v16,7,3) r1a diff --git a/storage/rocksdb/mysql-test/rocksdb/r/type_date_time_indexes.result b/storage/rocksdb/mysql-test/rocksdb/r/type_date_time_indexes.result index 120d0d81b55..77d07035a96 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/type_date_time_indexes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/type_date_time_indexes.result @@ -62,7 +62,7 @@ INSERT INTO t1 (d,dt,ts,t,y,pk) VALUES (DATE(@tm),@tm,TIMESTAMP(@tm),TIME(@tm),YEAR(@tm),'12:05:00'); EXPLAIN SELECT ts FROM t1 WHERE ts > NOW(); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index ts ts 5 NULL # Using where; Using index +1 SIMPLE t1 range ts ts 5 NULL # Using where; Using index SELECT ts FROM t1 WHERE ts > NOW(); ts EXPLAIN SELECT ts FROM t1 USE INDEX () WHERE ts > NOW(); diff --git a/storage/spider/spd_db_conn.cc b/storage/spider/spd_db_conn.cc index 70e125b6def..afdd3a4c828 100644 --- a/storage/spider/spd_db_conn.cc +++ b/storage/spider/spd_db_conn.cc @@ -1860,6 +1860,7 @@ int spider_db_append_key_where_internal( key_part++, key_count++ ) { + DBUG_PRINT("info", ("spider tgt_key_part_map=%lu", tgt_key_part_map)); store_length = key_part->store_length; field = key_part->field; key_name_length = dbton_share->get_column_name_length(field->field_index); @@ -1885,7 +1886,6 @@ int spider_db_append_key_where_internal( #endif } } else { - DBUG_PRINT("info", ("spider tgt_key_part_map=%lu", tgt_key_part_map)); if (tgt_key_part_map > 1) key_eq = TRUE; else @@ -1895,7 +1895,8 @@ int spider_db_append_key_where_internal( (key_eq && use_key == start_key) || (!key_eq && start_key_part_map) ) { - bool tgt_final = (use_key == start_key && tgt_key_part_map == 1); + bool tgt_final = (use_key == start_key && + (tgt_key_part_map == 1 || !end_key_part_map)); ptr = start_key->key + length; if ( (error_num = dbton_hdl->append_is_null_part(sql_type, key_part, @@ -1941,10 +1942,23 @@ int spider_db_append_key_where_internal( if (use_key == start_key) { +#ifdef SPIDER_HANDLER_SUPPORT_MULTIPLE_KEY_PARTS if (spider_dbton[dbton_id].db_util-> append_column_value(spider, str_part, field, ptr, share->access_charset)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); +#else + if (str_part->length() == SPIDER_SQL_OPEN_PAREN_LEN) + { + if (str->reserve(SPIDER_SQL_EQUAL_LEN)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + str->q_append(SPIDER_SQL_EQUAL_STR, SPIDER_SQL_EQUAL_LEN); + if (spider_dbton[dbton_id].db_util-> + append_column_value(spider, str_part, field, ptr, + share->access_charset)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + } +#endif } } #if defined(HS_HAS_SQLCOM) && defined(HAVE_HANDLERSOCKET) @@ -1992,7 +2006,8 @@ int spider_db_append_key_where_internal( if (use_key == start_key) { - if (tgt_key_part_map == 1) +#ifdef SPIDER_HANDLER_SUPPORT_MULTIPLE_KEY_PARTS + if (tgt_key_part_map == 1 || !end_key_part_map) { if (str->reserve(SPIDER_SQL_EQUAL_LEN)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); @@ -2002,6 +2017,18 @@ int spider_db_append_key_where_internal( append_column_value(spider, str_part, field, ptr, share->access_charset)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); +#else + if (str_part->length() == SPIDER_SQL_OPEN_PAREN_LEN) + { + if (str->reserve(SPIDER_SQL_EQUAL_LEN)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + str->q_append(SPIDER_SQL_EQUAL_STR, SPIDER_SQL_EQUAL_LEN); + if (spider_dbton[dbton_id].db_util-> + append_column_value(spider, str_part, field, ptr, + share->access_charset)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + } +#endif } } #if defined(HS_HAS_SQLCOM) && defined(HAVE_HANDLERSOCKET) @@ -2051,7 +2078,8 @@ int spider_db_append_key_where_internal( if (use_key == start_key) { - if (tgt_key_part_map == 1) +#ifdef SPIDER_HANDLER_SUPPORT_MULTIPLE_KEY_PARTS + if (tgt_key_part_map == 1 || !end_key_part_map) { if (str->reserve(SPIDER_SQL_GT_LEN)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); @@ -2061,6 +2089,18 @@ int spider_db_append_key_where_internal( append_column_value(spider, str_part, field, ptr, share->access_charset)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); +#else + if (str_part->length() == SPIDER_SQL_OPEN_PAREN_LEN) + { + if (str->reserve(SPIDER_SQL_GT_LEN)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + str->q_append(SPIDER_SQL_GT_STR, SPIDER_SQL_GT_LEN); + if (spider_dbton[dbton_id].db_util-> + append_column_value(spider, str_part, field, ptr, + share->access_charset)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + } +#endif } } #if defined(HS_HAS_SQLCOM) && defined(HAVE_HANDLERSOCKET) @@ -2111,7 +2151,8 @@ int spider_db_append_key_where_internal( if (use_key == start_key) { - if (tgt_key_part_map == 1) +#ifdef SPIDER_HANDLER_SUPPORT_MULTIPLE_KEY_PARTS + if (tgt_key_part_map == 1 || !end_key_part_map) { if (str->reserve(SPIDER_SQL_LT_LEN)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); @@ -2121,6 +2162,18 @@ int spider_db_append_key_where_internal( append_column_value(spider, str_part, field, ptr, share->access_charset)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); +#else + if (str_part->length() == SPIDER_SQL_OPEN_PAREN_LEN) + { + if (str->reserve(SPIDER_SQL_LT_LEN)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + str->q_append(SPIDER_SQL_LT_STR, SPIDER_SQL_LT_LEN); + if (spider_dbton[dbton_id].db_util-> + append_column_value(spider, str_part, field, ptr, + share->access_charset)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + } +#endif } } #if defined(HS_HAS_SQLCOM) && defined(HAVE_HANDLERSOCKET) @@ -2177,7 +2230,8 @@ int spider_db_append_key_where_internal( if (use_key == start_key) { - if (tgt_key_part_map == 1) +#ifdef SPIDER_HANDLER_SUPPORT_MULTIPLE_KEY_PARTS + if (tgt_key_part_map == 1 || !end_key_part_map) { if (str->reserve(SPIDER_SQL_LTEQUAL_LEN)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); @@ -2188,6 +2242,19 @@ int spider_db_append_key_where_internal( append_column_value(spider, str_part, field, ptr, share->access_charset)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); +#else + if (str_part->length() == SPIDER_SQL_OPEN_PAREN_LEN) + { + if (str->reserve(SPIDER_SQL_LTEQUAL_LEN)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + str->q_append(SPIDER_SQL_LTEQUAL_STR, + SPIDER_SQL_LTEQUAL_LEN); + if (spider_dbton[dbton_id].db_util-> + append_column_value(spider, str_part, field, ptr, + share->access_charset)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + } +#endif } } #if defined(HS_HAS_SQLCOM) && defined(HAVE_HANDLERSOCKET) @@ -2326,7 +2393,8 @@ int spider_db_append_key_where_internal( if (use_key == start_key) { - if (tgt_key_part_map == 1) +#ifdef SPIDER_HANDLER_SUPPORT_MULTIPLE_KEY_PARTS + if (tgt_key_part_map == 1 || !end_key_part_map) { if (str->reserve(SPIDER_SQL_GTEQUAL_LEN)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); @@ -2337,6 +2405,19 @@ int spider_db_append_key_where_internal( append_column_value(spider, str_part, field, ptr, share->access_charset)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); +#else + if (str_part->length() == SPIDER_SQL_OPEN_PAREN_LEN) + { + if (str->reserve(SPIDER_SQL_GTEQUAL_LEN)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + str->q_append(SPIDER_SQL_GTEQUAL_STR, + SPIDER_SQL_GTEQUAL_LEN); + if (spider_dbton[dbton_id].db_util-> + append_column_value(spider, str_part, field, ptr, + share->access_charset)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + } +#endif } } #if defined(HS_HAS_SQLCOM) && defined(HAVE_HANDLERSOCKET) @@ -2367,12 +2448,14 @@ int spider_db_append_key_where_internal( str_part2->q_append(SPIDER_SQL_AND_STR, SPIDER_SQL_AND_LEN); +#ifdef SPIDER_HANDLER_SUPPORT_MULTIPLE_KEY_PARTS if (use_key == start_key) { if (str_part->reserve(SPIDER_SQL_COMMA_LEN)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); str_part->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN); } +#endif } } @@ -2428,18 +2511,23 @@ int spider_db_append_key_where_internal( if (use_key == end_key) { -/* - if (tgt_key_part_map == 1) +#ifdef SPIDER_HANDLER_SUPPORT_MULTIPLE_KEY_PARTS + if (spider_dbton[dbton_id].db_util-> + append_column_value(spider, str_part, field, ptr, + share->access_charset)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); +#else + if (str_part->length() == SPIDER_SQL_OPEN_PAREN_LEN) { if (str->reserve(SPIDER_SQL_EQUAL_LEN)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); str->q_append(SPIDER_SQL_EQUAL_STR, SPIDER_SQL_EQUAL_LEN); + if (spider_dbton[dbton_id].db_util-> + append_column_value(spider, str_part, field, ptr, + share->access_charset)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); } -*/ - if (spider_dbton[dbton_id].db_util-> - append_column_value(spider, str_part, field, ptr, - share->access_charset)) - DBUG_RETURN(HA_ERR_OUT_OF_MEM); +#endif } } } else { @@ -2480,7 +2568,8 @@ int spider_db_append_key_where_internal( if (use_key == end_key) { - if (tgt_key_part_map == 1) +#ifdef SPIDER_HANDLER_SUPPORT_MULTIPLE_KEY_PARTS + if (tgt_key_part_map == 1 || !start_key_part_map) { if (str->reserve(SPIDER_SQL_LT_LEN)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); @@ -2490,6 +2579,18 @@ int spider_db_append_key_where_internal( append_column_value(spider, str_part, field, ptr, share->access_charset)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); +#else + if (str_part->length() == SPIDER_SQL_OPEN_PAREN_LEN) + { + if (str->reserve(SPIDER_SQL_LT_LEN)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + str->q_append(SPIDER_SQL_LT_STR, SPIDER_SQL_LT_LEN); + if (spider_dbton[dbton_id].db_util-> + append_column_value(spider, str_part, field, ptr, + share->access_charset)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + } +#endif } } break; @@ -2526,7 +2627,8 @@ int spider_db_append_key_where_internal( if (use_key == end_key) { - if (tgt_key_part_map == 1) +#ifdef SPIDER_HANDLER_SUPPORT_MULTIPLE_KEY_PARTS + if (tgt_key_part_map == 1 || !start_key_part_map) { if (str->reserve(SPIDER_SQL_LTEQUAL_LEN)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); @@ -2537,6 +2639,19 @@ int spider_db_append_key_where_internal( append_column_value(spider, str_part, field, ptr, share->access_charset)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); +#else + if (str_part->length() == SPIDER_SQL_OPEN_PAREN_LEN) + { + if (str->reserve(SPIDER_SQL_LTEQUAL_LEN)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + str->q_append(SPIDER_SQL_LTEQUAL_STR, + SPIDER_SQL_LTEQUAL_LEN); + if (spider_dbton[dbton_id].db_util-> + append_column_value(spider, str_part, field, ptr, + share->access_charset)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + } +#endif } } break; @@ -2554,12 +2669,14 @@ int spider_db_append_key_where_internal( str_part2->q_append(SPIDER_SQL_AND_STR, SPIDER_SQL_AND_LEN); +#ifdef SPIDER_HANDLER_SUPPORT_MULTIPLE_KEY_PARTS if (use_key == end_key) { if (str_part->reserve(SPIDER_SQL_COMMA_LEN)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); str_part->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN); } +#endif } } if (use_both && (!start_key_part_map || !end_key_part_map)) diff --git a/storage/spider/spd_db_mysql.cc b/storage/spider/spd_db_mysql.cc index 1e8b5c40a03..015ca373577 100644 --- a/storage/spider/spd_db_mysql.cc +++ b/storage/spider/spd_db_mysql.cc @@ -10373,6 +10373,7 @@ int spider_mbase_handler::append_is_null( key->flag == HA_READ_KEY_EXACT || key->flag == HA_READ_KEY_OR_NEXT ) { +#ifdef SPIDER_HANDLER_SUPPORT_MULTIPLE_KEY_PARTS if (tgt_final) { if (str->reserve(SPIDER_SQL_EQUAL_LEN)) @@ -10383,11 +10384,23 @@ int spider_mbase_handler::append_is_null( if (str->reserve(SPIDER_SQL_NULL_LEN)) DBUG_RETURN(HA_ERR_OUT_OF_MEM); str->q_append(SPIDER_SQL_NULL_STR, SPIDER_SQL_NULL_LEN); +#else + if (str_part->length() == SPIDER_SQL_OPEN_PAREN_LEN) + { + if (str->reserve(SPIDER_SQL_EQUAL_LEN)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + str->q_append(SPIDER_SQL_EQUAL_STR, SPIDER_SQL_EQUAL_LEN); + str = str_part; + if (str->reserve(SPIDER_SQL_NULL_LEN)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + str->q_append(SPIDER_SQL_NULL_STR, SPIDER_SQL_NULL_LEN); + } +#endif } else { +#ifdef SPIDER_HANDLER_SUPPORT_MULTIPLE_KEY_PARTS if (str_part->length() == SPIDER_SQL_OPEN_PAREN_LEN) { str = str_part; - /* first index column */ str->length(str->length() - SPIDER_SQL_OPEN_PAREN_LEN); ha_next_pos = str->length(); if (str->reserve(SPIDER_SQL_FIRST_LEN)) @@ -10404,6 +10417,19 @@ int spider_mbase_handler::append_is_null( DBUG_RETURN(HA_ERR_OUT_OF_MEM); str->q_append(SPIDER_SQL_NULL_STR, SPIDER_SQL_NULL_LEN); } +#else + if (str_part->length() == SPIDER_SQL_OPEN_PAREN_LEN) + { + str = str_part; + /* first index column */ + str->length(str->length() - SPIDER_SQL_OPEN_PAREN_LEN); + ha_next_pos = str->length(); + if (str->reserve(SPIDER_SQL_FIRST_LEN)) + DBUG_RETURN(HA_ERR_OUT_OF_MEM); + str->q_append(SPIDER_SQL_FIRST_STR, SPIDER_SQL_FIRST_LEN); + spider->result_list.ha_read_kind = 1; + } +#endif } str = str_part2; } @@ -10485,7 +10511,9 @@ int spider_mbase_handler::append_where_terminator( } else { str_part2->length(str_part2->length() - SPIDER_SQL_AND_LEN); +#ifdef SPIDER_HANDLER_SUPPORT_MULTIPLE_KEY_PARTS str_part->length(str_part->length() - SPIDER_SQL_COMMA_LEN); +#endif if (!result_list->ha_read_kind) str_part->q_append(SPIDER_SQL_CLOSE_PAREN_STR, SPIDER_SQL_CLOSE_PAREN_LEN); diff --git a/storage/tokudb/mysql-test/tokudb/r/type_blob.result b/storage/tokudb/mysql-test/tokudb/r/type_blob.result index 85f9d343e04..f3d634d84c1 100644 --- a/storage/tokudb/mysql-test/tokudb/r/type_blob.result +++ b/storage/tokudb/mysql-test/tokudb/r/type_blob.result @@ -595,6 +595,9 @@ id txt explain select * from t1 where txt='Chevy' or txt is NULL; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL txt_index NULL NULL NULL 3 Using where +explain select * from t1 FORCE INDEX (`txt_index`) where txt='Chevy' or txt is NULL; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 3 Using where select * from t1 where txt='Chevy '; id txt 1 Chevy @@ -665,6 +668,9 @@ id txt alter table t1 modify column txt blob; explain select * from t1 where txt='Chevy' or txt is NULL; id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL txt_index NULL NULL NULL 4 Using where +explain select * from t1 FORCE INDEX (`txt_index`) where txt='Chevy' or txt is NULL; +id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 2 Using where select * from t1 where txt='Chevy' or txt is NULL; id txt @@ -672,6 +678,9 @@ id txt 3 NULL explain select * from t1 where txt='Chevy' or txt is NULL order by txt; id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL txt_index NULL NULL NULL 4 Using where; Using filesort +explain select * from t1 FORCE INDEX (`txt_index`) where txt='Chevy' or txt is NULL order by txt; +id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 2 Using where; Using filesort select * from t1 where txt='Chevy' or txt is NULL order by txt; id txt diff --git a/storage/tokudb/mysql-test/tokudb/t/type_blob.test b/storage/tokudb/mysql-test/tokudb/t/type_blob.test index 7cf77e386c7..c66986e6c71 100644 --- a/storage/tokudb/mysql-test/tokudb/t/type_blob.test +++ b/storage/tokudb/mysql-test/tokudb/t/type_blob.test @@ -364,6 +364,7 @@ create table t1 (id integer primary key auto_increment, txt text, index txt_inde insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL); select * from t1 where txt='Chevy' or txt is NULL; explain select * from t1 where txt='Chevy' or txt is NULL; +explain select * from t1 FORCE INDEX (`txt_index`) where txt='Chevy' or txt is NULL; select * from t1 where txt='Chevy '; select * from t1 where txt='Chevy ' or txt='Chevy'; select * from t1 where txt='Chevy' or txt='Chevy '; @@ -384,8 +385,10 @@ select * from t1 where txt > 'Chevy'; select * from t1 where txt >= 'Chevy'; alter table t1 modify column txt blob; explain select * from t1 where txt='Chevy' or txt is NULL; +explain select * from t1 FORCE INDEX (`txt_index`) where txt='Chevy' or txt is NULL; select * from t1 where txt='Chevy' or txt is NULL; explain select * from t1 where txt='Chevy' or txt is NULL order by txt; +explain select * from t1 FORCE INDEX (`txt_index`) where txt='Chevy' or txt is NULL order by txt; select * from t1 where txt='Chevy' or txt is NULL order by txt; drop table t1; |