diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/include/icp_tests.inc | 21 | ||||
| -rw-r--r-- | mysql-test/r/innodb_icp.result | 27 | ||||
| -rw-r--r-- | mysql-test/r/mrr_icp_extra.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/myisam_icp.result | 29 | ||||
| -rw-r--r-- | mysql-test/r/null_key.result | 20 | ||||
| -rw-r--r-- | mysql-test/r/order_by.result | 2 | ||||
| -rw-r--r-- | mysql-test/suite/maria/icp.result | 29 |
7 files changed, 116 insertions, 14 deletions
diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc index ffe8d7f1eb1..7c9feea55c2 100644 --- a/mysql-test/include/icp_tests.inc +++ b/mysql-test/include/icp_tests.inc @@ -892,3 +892,24 @@ insert into t1 values ('',1); select 1 from t1 where b <= 1 and a <> ''; drop table t1; +--echo # +--echo # MDEV-4778: Incorrect results from Aria/MyISAM SELECT using index with prefix length on TEXT column +--echo # +CREATE TABLE t1 ( + c1 TEXT , + c2 VARCHAR(2) , + INDEX idx1 (c2,c1(2)), + INDEX idx2 (c2,c1(1)) +); + +INSERT INTO t1 (c1,c2) VALUES ('aa','x'), ('a' ,'y'); + +SELECT * FROM t1 IGNORE INDEX(idx1,idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); + +EXPLAIN +SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); + +SELECT * FROM t1 FORCE INDEX(idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); + +DROP TABLE t1; diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result index 54ad9ecafad..07c75986392 100644 --- a/mysql-test/r/innodb_icp.result +++ b/mysql-test/r/innodb_icp.result @@ -852,6 +852,33 @@ select 1 from t1 where b <= 1 and a <> ''; 1 drop table t1; # +# MDEV-4778: Incorrect results from Aria/MyISAM SELECT using index with prefix length on TEXT column +# +CREATE TABLE t1 ( +c1 TEXT , +c2 VARCHAR(2) , +INDEX idx1 (c2,c1(2)), +INDEX idx2 (c2,c1(1)) +); +INSERT INTO t1 (c1,c2) VALUES ('aa','x'), ('a' ,'y'); +SELECT * FROM t1 IGNORE INDEX(idx1,idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +c1 c2 +aa x +a y +EXPLAIN +SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx1 idx1 10 NULL 2 Using index condition; Using where +SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +c1 c2 +aa x +a y +SELECT * FROM t1 FORCE INDEX(idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +c1 c2 +aa x +a y +DROP TABLE t1; +# # BUG#920132: Assert trx->n_active_thrs == 1 failed at que0que.c line 1050 # CREATE TABLE t1 ( a INT ) diff --git a/mysql-test/r/mrr_icp_extra.result b/mysql-test/r/mrr_icp_extra.result index 5e49fb6e956..f7adcfb19fd 100644 --- a/mysql-test/r/mrr_icp_extra.result +++ b/mysql-test/r/mrr_icp_extra.result @@ -105,7 +105,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range FieldKey FieldKey 38 NULL 4 Using index condition; Rowid-ordered scan; Using filesort EXPLAIN SELECT * FROM t1 IGNORE INDEX (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range StringField StringField 38 NULL 4 Using index condition; Using filesort +1 SIMPLE t1 range StringField StringField 38 NULL 4 Using where; Using filesort SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; FieldKey LongVal StringVal 3 1 2 diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result index bc7ebf9c439..2c157102270 100644 --- a/mysql-test/r/myisam_icp.result +++ b/mysql-test/r/myisam_icp.result @@ -211,7 +211,7 @@ c-1006=w EXPLAIN SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c1 c1 12 NULL 2 Using index condition; Using where +1 SIMPLE t3 range c1 c1 12 NULL 2 Using where SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; c1 EXPLAIN @@ -855,6 +855,33 @@ insert into t1 values ('',1); select 1 from t1 where b <= 1 and a <> ''; 1 drop table t1; +# +# MDEV-4778: Incorrect results from Aria/MyISAM SELECT using index with prefix length on TEXT column +# +CREATE TABLE t1 ( +c1 TEXT , +c2 VARCHAR(2) , +INDEX idx1 (c2,c1(2)), +INDEX idx2 (c2,c1(1)) +); +INSERT INTO t1 (c1,c2) VALUES ('aa','x'), ('a' ,'y'); +SELECT * FROM t1 IGNORE INDEX(idx1,idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +c1 c2 +aa x +a y +EXPLAIN +SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx1 idx1 10 NULL 2 Using where +SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +c1 c2 +aa x +a y +SELECT * FROM t1 FORCE INDEX(idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +c1 c2 +aa x +a y +DROP TABLE t1; drop table if exists t0, t1, t1i, t1m; # # BUG#826935 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result index ba79011f53e..e80851329a4 100644 --- a/mysql-test/r/null_key.result +++ b/mysql-test/r/null_key.result @@ -78,13 +78,13 @@ insert into t2 select * from t1; alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); explain select * from t1 where a is null and b = 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where +1 SIMPLE t1 ref a,b a 5 const 3 Using where explain select * from t1 where a is null and b = 2 and c=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where +1 SIMPLE t1 ref a,b a 5 const 3 Using where explain select * from t1 where a is null and b = 7 and c=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where +1 SIMPLE t1 ref a,b a 5 const 3 Using where explain select * from t1 where a=2 and b = 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a,b a 5 const 1 Using where @@ -93,25 +93,25 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using where explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 and c=0 limit 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 5 NULL 5 Using index condition; Using where +1 SIMPLE t1 range a,b a 5 NULL 5 Using where explain select * from t1 where (a is null or a = 7) and b=7 and c=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using index condition; Using where +1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where +1 SIMPLE t1 ref a,b a 5 const 3 Using where 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 ref a,b a 5 const 3 Using index condition; Using where +1 SIMPLE t1 ref a,b a 5 const 3 Using where 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 index condition +1 SIMPLE t1 range a a 5 NULL 1 Using where explain select * from t1 where a is null and b=7 or 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,b a 5 NULL 4 Using index condition; Using where +1 SIMPLE t1 range a,b a 5 NULL 4 Using where explain select * from t1 where a > 8 and a < 9; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a a 5 NULL 1 Using index condition +1 SIMPLE t1 range a a 5 NULL 1 Using where explain select * from t1 where b like "6%"; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 12 NULL 1 Using where diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 94e7d5e757a..e37d64d6d44 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -618,7 +618,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range FieldKey FieldKey 38 NULL 4 Using index condition; Using filesort EXPLAIN SELECT * FROM t1 IGNORE INDEX (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range StringField StringField 38 NULL 4 Using index condition; Using filesort +1 SIMPLE t1 range StringField StringField 38 NULL 4 Using where; Using filesort SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal; FieldKey LongVal StringVal 3 1 2 diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result index a8756a4a6f1..9552580bb88 100644 --- a/mysql-test/suite/maria/icp.result +++ b/mysql-test/suite/maria/icp.result @@ -213,7 +213,7 @@ c-1006=w EXPLAIN SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c1 c1 12 NULL 2 Using index condition; Using where +1 SIMPLE t3 range c1 c1 12 NULL 2 Using where SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2; c1 EXPLAIN @@ -857,5 +857,32 @@ insert into t1 values ('',1); select 1 from t1 where b <= 1 and a <> ''; 1 drop table t1; +# +# MDEV-4778: Incorrect results from Aria/MyISAM SELECT using index with prefix length on TEXT column +# +CREATE TABLE t1 ( +c1 TEXT , +c2 VARCHAR(2) , +INDEX idx1 (c2,c1(2)), +INDEX idx2 (c2,c1(1)) +); +INSERT INTO t1 (c1,c2) VALUES ('aa','x'), ('a' ,'y'); +SELECT * FROM t1 IGNORE INDEX(idx1,idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +c1 c2 +aa x +a y +EXPLAIN +SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range idx1 idx1 10 NULL 2 Using where +SELECT * FROM t1 FORCE INDEX(idx1) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +c1 c2 +aa x +a y +SELECT * FROM t1 FORCE INDEX(idx2) WHERE (c1='aa' AND c2='x') OR (c1='a' AND c2='y'); +c1 c2 +aa x +a y +DROP TABLE t1; set storage_engine= @save_storage_engine; set optimizer_switch=@maria_icp_tmp; |