[Commits] 3390736: MDEV-16188 Post-merge corrections and adjustments

revision-id: 33907360f56789cd9b467b40e66412eb0a0aff28 (mariadb-10.3.6-101-g3390736) parent(s): 07b7b2e4efb82a50e4575c309bf6987975c4607f author: Igor Babaev committer: Igor Babaev timestamp: 2019-02-04 22:44:33 -0800 message: MDEV-16188 Post-merge corrections and adjustments --- mysql-test/include/gis_keys.inc | 4 +- mysql-test/main/gis.result | 10 +- mysql-test/suite/gcol/inc/gcol_select.inc | 2 +- mysql-test/suite/gcol/r/gcol_keys_myisam.result | 2 +- mysql-test/suite/gcol/r/gcol_select_innodb.result | 16 +-- mysql-test/suite/gcol/r/gcol_select_myisam.result | 38 ++--- mysql-test/suite/heap/heap.result | 2 +- mysql-test/suite/heap/heap_btree.result | 20 +-- mysql-test/suite/heap/heap_hash.result | 8 +- mysql-test/suite/innodb/r/innodb-isolation.result | 6 +- mysql-test/suite/innodb/r/innodb_gis.result | 10 +- .../innodb/r/innodb_skip_innodb_is_tables.result | 3 + mysql-test/suite/innodb/r/mdev-117.result | 2 + mysql-test/suite/innodb/r/monitor.result | 15 +- mysql-test/suite/innodb_gis/r/0.result | 10 +- mysql-test/suite/innodb_gis/r/1.result | 10 +- mysql-test/suite/innodb_gis/r/gis.result | 10 +- .../suite/innodb_gis/r/rtree_estimate.result | 6 +- mysql-test/suite/innodb_gis/t/rtree_estimate.test | 1 + mysql-test/suite/maria/icp.result | 16 ++- mysql-test/suite/maria/ps_maria.result | 2 +- mysql-test/suite/parts/r/optimizer.result | 4 +- .../sys_vars/r/sysvars_server_embedded.result | 22 ++- .../sys_vars/r/sysvars_server_notembedded.result | 2 +- mysql-test/suite/vcol/inc/vcol_select.inc | 2 +- mysql-test/suite/vcol/r/vcol_select_innodb.result | 16 +-- mysql-test/suite/vcol/r/vcol_select_myisam.result | 32 ++--- sql/sys_vars.cc | 2 +- .../mysql-test/connect/r/mysql_index.result | 156 ++++++++++----------- .../connect/mysql-test/connect/r/part_file.result | 9 +- .../connect/mysql-test/connect/t/mysql_index.test | 4 +- .../connect/mysql-test/connect/t/part_file.test | 4 +- storage/innobase/srv/srv0mon.cc | 2 +- storage/myisam/mi_range.c | 6 +- 34 files changed, 242 insertions(+), 212 deletions(-) diff --git a/mysql-test/include/gis_keys.inc b/mysql-test/include/gis_keys.inc index cc8ec68..388c7b4 100644 --- a/mysql-test/include/gis_keys.inc +++ b/mysql-test/include/gis_keys.inc @@ -27,8 +27,8 @@ SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); # the "most rows covered" rule doesn't kick in anymore # now EXPLAIN shows the index used on the table # and we're getting the wrong result again -INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); -INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); +INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(3 4)')); +INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(3 4)')); EXPLAIN SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); diff --git a/mysql-test/main/gis.result b/mysql-test/main/gis.result index 7695cef..1919555 100644 --- a/mysql-test/main/gis.result +++ b/mysql-test/main/gis.result @@ -964,29 +964,29 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) 1 -INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); -INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); +INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(3 4)')); +INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(3 4)')); EXPLAIN SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) -2 +1 EXPLAIN SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref p p 28 const # Using where SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) -2 +1 EXPLAIN SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) -2 +1 DROP TABLE t1, t2; End of 5.0 tests # diff --git a/mysql-test/suite/gcol/inc/gcol_select.inc b/mysql-test/suite/gcol/inc/gcol_select.inc index efaffd5..7dcbcc7 100644 --- a/mysql-test/suite/gcol/inc/gcol_select.inc +++ b/mysql-test/suite/gcol/inc/gcol_select.inc @@ -35,7 +35,7 @@ insert into t2 (a) values (1); create table t3 (a int primary key, b int generated always as (-a) virtual, c int generated always as (-a) stored unique); -insert into t3 (a) values (2),(1),(3); +insert into t3 (a) values (2),(1),(3),(5),(4),(7); analyze table t1,t2,t3; --echo # select_type=SIMPLE, type=system diff --git a/mysql-test/suite/gcol/r/gcol_keys_myisam.result b/mysql-test/suite/gcol/r/gcol_keys_myisam.result index 8ef6736..91bd8fc 100644 --- a/mysql-test/suite/gcol/r/gcol_keys_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_keys_myisam.result @@ -205,7 +205,7 @@ outr.col_varchar_nokey in ('c', 'x', 'i') AND (outr.col_time_key IS NULL OR outr.col_datetime_key = '2009-09-27'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE outr ALL col_time_key,col_datetime_key NULL NULL NULL 4 x +1 SIMPLE outr index_merge col_time_key,col_datetime_key col_time_key,col_datetime_key 4,6 NULL 2 x SELECT outr.col_time_key AS x FROM c AS outr diff --git a/mysql-test/suite/gcol/r/gcol_select_innodb.result b/mysql-test/suite/gcol/r/gcol_select_innodb.result index 983e1fb..8288588 100644 --- a/mysql-test/suite/gcol/r/gcol_select_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_select_innodb.result @@ -17,7 +17,7 @@ insert into t2 (a) values (1); create table t3 (a int primary key, b int generated always as (-a) virtual, c int generated always as (-a) stored unique); -insert into t3 (a) values (2),(1),(3); +insert into t3 (a) values (2),(1),(3),(5),(4),(7); analyze table t1,t2,t3; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected @@ -79,8 +79,8 @@ a b c 3 -3 -3 explain select * from t1 where b in (select c from t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 3 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t3 ref c c 5 test.t1.b 1 Using index # select_type=PRIMARY, type=range,ref select * from t1 where c in (select c from t3 where c between -2 and -1); a b c @@ -89,7 +89,7 @@ a b c 2 -2 -2 explain select * from t1 where c in (select c from t3 where c between -2 and -1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 3 Using where; Using index +1 PRIMARY t3 index c c 5 NULL 6 Using where; Using index 1 PRIMARY t1 ALL c NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) # select_type=UNION, type=system # select_type=UNION RESULT, type=<union1,2> @@ -173,7 +173,7 @@ a b c 2 -2 -2 explain select * from t3 where b between -2 and -1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where # SELECT * FROM tbl_name WHERE <indexed gcol expr> select * from t3 where c between -2 and -1; a b c @@ -236,7 +236,7 @@ a b c 2 -2 -2 explain select * from t3 where b between -2 and -1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index NULL PRIMARY 4 NULL 3 Using where +1 SIMPLE t3 index NULL PRIMARY 4 NULL 6 Using where # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-indexed gcol> select * from t3 where b between -2 and -1 order by b; a b c @@ -244,7 +244,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-indexed gcol> select * from t3 where c between -2 and -1 order by b; a b c @@ -260,7 +260,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <indexed gcol> select * from t3 where c between -2 and -1 order by c; a b c diff --git a/mysql-test/suite/gcol/r/gcol_select_myisam.result b/mysql-test/suite/gcol/r/gcol_select_myisam.result index e823458..039484b 100644 --- a/mysql-test/suite/gcol/r/gcol_select_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_select_myisam.result @@ -17,7 +17,7 @@ insert into t2 (a) values (1); create table t3 (a int primary key, b int generated always as (-a) virtual, c int generated always as (-a) stored unique); -insert into t3 (a) values (2),(1),(3); +insert into t3 (a) values (2),(1),(3),(5),(4),(7); analyze table t1,t2,t3; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected @@ -60,7 +60,7 @@ a b c 1 -1 -1 explain select * from t3 where c>=-1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 2 Using index condition +1 SIMPLE t3 range c c 5 NULL 1 Using index condition # select_type=SIMPLE, type=ref select * from t1,t3 where t1.c=t3.c and t3.c=-1; a b c a b c @@ -79,8 +79,8 @@ a b c 3 -3 -3 explain select * from t1 where b in (select c from t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 3 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t3 ref c c 5 test.t1.b 1 Using index # select_type=PRIMARY, type=range,ref select * from t1 where c in (select c from t3 where c between -2 and -1); a b c @@ -89,7 +89,7 @@ a b c 2 -2 -2 explain select * from t1 where c in (select c from t3 where c between -2 and -1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 3 Using where; Using index +1 PRIMARY t3 index c c 5 NULL 6 Using where; Using index 1 PRIMARY t1 ref c c 5 test.t3.c 1 # select_type=UNION, type=system # select_type=UNION RESULT, type=<union1,2> @@ -165,7 +165,7 @@ a b c 2 -2 -2 explain select * from t3 where a between 1 and 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using index condition +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> select * from t3 where b between -2 and -1; a b c @@ -173,7 +173,7 @@ a b c 2 -2 -2 explain select * from t3 where b between -2 and -1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where # SELECT * FROM tbl_name WHERE <indexed gcol expr> select * from t3 where c between -2 and -1; a b c @@ -181,7 +181,7 @@ a b c 2 -2 -2 explain select * from t3 where c between -2 and -1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using index condition +1 SIMPLE t3 range c c 5 NULL 2 Using index condition # bug#20022189: WL411:DEBUG ASSERT AT FIELD_LONG::VAL_INT IN SQL/FIELD.CC CREATE TABLE t4 ( `pk` int(11) NOT NULL , @@ -211,7 +211,7 @@ a b c 1 -1 -1 explain select * from t3 where a between 1 and 2 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using filesort +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-gcol> select * from t3 where b between -2 and -1 order by a; a b c @@ -219,7 +219,7 @@ a b c 2 -2 -2 explain select * from t3 where b between -2 and -1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-gcol> select * from t3 where c between -2 and -1 order by a; a b c @@ -227,7 +227,7 @@ a b c 2 -2 -2 explain select * from t3 where c between -2 and -1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Using filesort +1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-indexed gcol> select * from t3 where b between -2 and -1 order by b; a b c @@ -235,7 +235,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-indexed gcol> select * from t3 where c between -2 and -1 order by b; a b c @@ -243,7 +243,7 @@ a b c 1 -1 -1 explain select * from t3 where c between -2 and -1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Using filesort +1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <indexed gcol> select * from t3 where b between -2 and -1 order by c; a b c @@ -251,7 +251,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <indexed gcol> select * from t3 where c between -2 and -1 order by c; a b c @@ -259,7 +259,7 @@ a b c 1 -1 -1 explain select * from t3 where c between -2 and -1 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using index condition +1 SIMPLE t3 range c c 5 NULL 2 Using index condition # SELECT sum(<non-indexed gcol>) FROM tbl_name GROUP BY <non-indexed gcol> select sum(b) from t1 group by b; sum(b) @@ -794,15 +794,15 @@ KEY (col_int_key) INSERT INTO cc (col_int_nokey) VALUES (0),(1),(7),(0),(4),(5); EXPLAIN SELECT pk FROM cc WHERE col_int_key > 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE cc ALL col_int_key NULL NULL NULL 6 # +1 SIMPLE cc range col_int_key col_int_key 5 NULL 3 # SELECT pk FROM cc WHERE col_int_key > 3; pk -3 5 6 +3 EXPLAIN SELECT pk FROM cc WHERE col_int_key > 3 ORDER BY 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE cc ALL col_int_key NULL NULL NULL 6 # +1 SIMPLE cc range col_int_key col_int_key 5 NULL 3 # SELECT pk FROM cc WHERE col_int_key > 3 ORDER BY 1; pk 3 @@ -1211,7 +1211,7 @@ FROM t0 AS a0, t0 AS a1, t0 AS a2; EXPLAIN SELECT * FROM t1 WHERE i1 > 41 AND i1 <= 43; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx idx 4 NULL 19 Using index condition +1 SIMPLE t1 range idx idx 4 NULL 20 Using index condition SELECT * FROM t1 WHERE i1 > 41 AND i1 <= 43; pk i1 i2 v1 v2 diff --git a/mysql-test/suite/heap/heap.result b/mysql-test/suite/heap/heap.result index 320966d..326142a 100644 --- a/mysql-test/suite/heap/heap.result +++ b/mysql-test/suite/heap/heap.result @@ -66,7 +66,7 @@ a alter table t1 engine=myisam; explain select * from t1 where a in (869751,736494,226312,802616); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range uniq_id uniq_id 4 NULL 4 Using where; Using index +1 SIMPLE t1 index uniq_id uniq_id 4 NULL 5 Using where; Using index drop table t1; create table t1 (x int not null, y int not null, key x (x), unique y (y)) engine=heap; diff --git a/mysql-test/suite/heap/heap_btree.result b/mysql-test/suite/heap/heap_btree.result index 83d1bcb..28292d9 100644 --- a/mysql-test/suite/heap/heap_btree.result +++ b/mysql-test/suite/heap/heap_btree.result @@ -48,8 +48,8 @@ a alter table t1 add unique uniq_id using BTREE (a); select * from t1 where a > 736494; a -802616 869751 +802616 select * from t1 where a = 736494; a 736494 @@ -59,14 +59,14 @@ a 869751 select * from t1 where a in (869751,736494,226312,802616); a -226312 +869751 736494 +226312 802616 -869751 alter table t1 engine=myisam; explain select * from t1 where a in (869751,736494,226312,802616); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range uniq_id uniq_id 4 NULL 4 Using where; Using index +1 SIMPLE t1 index uniq_id uniq_id 4 NULL 5 Using where; Using index drop table t1; create table t1 (x int not null, y int not null, key x using BTREE (x,y), unique y using BTREE (y)) engine=heap; @@ -178,7 +178,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range btn btn 10 NULL 1 Using where explain select * from t1 where btn like "h%"; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range btn btn 10 NULL # Using where +1 SIMPLE t1 ALL btn NULL NULL NULL # Using where explain select * from t1 where btn like "a%"; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range btn btn 10 NULL 1 Using where @@ -350,11 +350,11 @@ insert into t1 values (869751),(736494),(226312),(802616),(728912); alter table t1 add unique uniq_id using BTREE (a); select 0+a from t1 where a > 736494; 0+a -802616 869751 +802616 explain select 0+a from t1 where a > 736494; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range uniq_id uniq_id 8 NULL 3 Using where +1 SIMPLE t1 ALL uniq_id NULL NULL NULL 5 Using where select 0+a from t1 where a = 736494; 0+a 736494 @@ -370,13 +370,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range uniq_id uniq_id 8 NULL 2 Using where select 0+a from t1 where a in (869751,736494,226312,802616); 0+a -226312 +869751 736494 +226312 802616 -869751 explain select 0+a from t1 where a in (869751,736494,226312,802616); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range uniq_id uniq_id 8 NULL 4 Using where +1 SIMPLE t1 ALL uniq_id NULL NULL NULL 5 Using where drop table t1; End of 5.3 tests create table t1 (id int, a varchar(300) not null, key using btree(a)) engine=heap; diff --git a/mysql-test/suite/heap/heap_hash.result b/mysql-test/suite/heap/heap_hash.result index 55d4358..4f96827 100644 --- a/mysql-test/suite/heap/heap_hash.result +++ b/mysql-test/suite/heap/heap_hash.result @@ -66,7 +66,7 @@ a alter table t1 engine=myisam; explain select * from t1 where a in (869751,736494,226312,802616); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range uniq_id uniq_id 4 NULL 4 Using where; Using index +1 SIMPLE t1 index uniq_id uniq_id 4 NULL 5 Using where; Using index drop table t1; create table t1 (x int not null, y int not null, key x using HASH (x), unique y using HASH (y)) engine=heap; @@ -428,13 +428,13 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range uniq_id uniq_id 8 NULL 2 Using where select 0+a from t1 where a in (869751,736494,226312,802616); 0+a -226312 +869751 736494 +226312 802616 -869751 explain select 0+a from t1 where a in (869751,736494,226312,802616); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range uniq_id uniq_id 8 NULL 4 Using where +1 SIMPLE t1 ALL uniq_id NULL NULL NULL 5 Using where drop table t1; End of 5.3 tests # diff --git a/mysql-test/suite/innodb/r/innodb-isolation.result b/mysql-test/suite/innodb/r/innodb-isolation.result index ce9c530..a308f10 100644 --- a/mysql-test/suite/innodb/r/innodb-isolation.result +++ b/mysql-test/suite/innodb/r/innodb-isolation.result @@ -963,15 +963,15 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 12 # EXPLAIN SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 8 # +1 PRIMARY t1 index PRIMARY k2 5 NULL 12 # 2 SUBQUERY t1 index NULL k2 5 NULL 12 # EXPLAIN SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 8 # +1 PRIMARY t1 index PRIMARY k2 5 NULL 12 # 2 SUBQUERY t1 index NULL k2 5 NULL 12 # EXPLAIN SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 7 # +1 PRIMARY t1 index PRIMARY k2 5 NULL 12 # 2 SUBQUERY t1 index NULL k2 5 NULL 12 # # # Make all indexes in t2 obsolete to the active repeatable read transaction diff --git a/mysql-test/suite/innodb/r/innodb_gis.result b/mysql-test/suite/innodb/r/innodb_gis.result index f8b02bb..162219c 100644 --- a/mysql-test/suite/innodb/r/innodb_gis.result +++ b/mysql-test/suite/innodb/r/innodb_gis.result @@ -560,29 +560,29 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) 1 -INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); -INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); +INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(3 4)')); +INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(3 4)')); EXPLAIN SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) -2 +1 EXPLAIN SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref p p 28 const # Using where SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) -2 +1 EXPLAIN SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) -2 +1 DROP TABLE t1, t2; End of 5.0 tests # diff --git a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result index a90cd22..8640783 100644 --- a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result +++ b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result @@ -286,6 +286,9 @@ icp_attempts icp 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled icp_no_match icp 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Index push-down condition does not match icp_out_of_range icp 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Index push-down condition out of range icp_match icp 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Index push-down condition matches +pk-filter checks pk-filter 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter Number of lookups into PK-filters +pk-filter_positive pk-filter 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter PK-filter test is positive +pk-filter_negative pk-filter 0 NULL NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL disabled counter PK-filter test is negative select * from information_schema.innodb_ft_default_stopword; value a diff --git a/mysql-test/suite/innodb/r/mdev-117.result b/mysql-test/suite/innodb/r/mdev-117.result index 979f1ae..39a1fc5 100644 --- a/mysql-test/suite/innodb/r/mdev-117.result +++ b/mysql-test/suite/innodb/r/mdev-117.result @@ -12,6 +12,8 @@ DELETE IGNORE FROM t1;; connection con1; DELETE FROM t1 WHERE col_int_key IN (1, 40000000); connection default; +Warnings: +Warning 1205 Lock wait timeout exceeded; try restarting transaction disconnect con1; drop table t1; SET GLOBAL innodb_lock_wait_timeout=default; diff --git a/mysql-test/suite/innodb/r/monitor.result b/mysql-test/suite/innodb/r/monitor.result index 4a72a37..a160eb5 100644 --- a/mysql-test/suite/innodb/r/monitor.result +++ b/mysql-test/suite/innodb/r/monitor.result @@ -251,6 +251,9 @@ icp_attempts disabled icp_no_match disabled icp_out_of_range disabled icp_match disabled +pk-filter checks disabled +pk-filter_positive disabled +pk-filter_negative disabled set global innodb_monitor_enable = all; select name from information_schema.innodb_metrics where status!='enabled'; name @@ -461,7 +464,7 @@ max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "dml%"; name max_count min_count count max_count_reset min_count_reset count_reset status -dml_reads 4 NULL 4 4 NULL 4 enabled +dml_reads 2 NULL 2 2 NULL 2 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 0 NULL 0 0 NULL 0 enabled dml_updates 2 NULL 2 2 NULL 2 enabled @@ -475,7 +478,7 @@ max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "dml%"; name max_count min_count count max_count_reset min_count_reset count_reset status -dml_reads 6 NULL 6 6 NULL 6 enabled +dml_reads 4 NULL 4 4 NULL 4 enabled dml_inserts 1 NULL 1 1 NULL 1 enabled dml_deletes 2 NULL 2 2 NULL 2 enabled dml_updates 2 NULL 2 2 NULL 2 enabled @@ -489,7 +492,7 @@ max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "dml%"; name max_count min_count count max_count_reset min_count_reset count_reset status -dml_reads 6 NULL 6 0 NULL 0 enabled +dml_reads 4 NULL 4 0 NULL 0 enabled dml_inserts 1 NULL 1 0 NULL 0 enabled dml_deletes 2 NULL 2 0 NULL 0 enabled dml_updates 2 NULL 2 0 NULL 0 enabled @@ -505,7 +508,7 @@ max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "dml%"; name max_count min_count count max_count_reset min_count_reset count_reset status -dml_reads 8 NULL 8 2 NULL 2 enabled +dml_reads 6 NULL 6 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled @@ -519,7 +522,7 @@ max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "dml%"; name max_count min_count count max_count_reset min_count_reset count_reset status -dml_reads 8 NULL 8 2 NULL 2 enabled +dml_reads 6 NULL 6 2 NULL 2 enabled dml_inserts 3 NULL 3 2 NULL 2 enabled dml_deletes 4 NULL 4 2 NULL 2 enabled dml_updates 2 NULL 2 0 NULL 0 enabled @@ -533,7 +536,7 @@ max_count_reset, min_count_reset, count_reset, status from information_schema.innodb_metrics where name like "dml%"; name max_count min_count count max_count_reset min_count_reset count_reset status -dml_reads 8 NULL 8 2 NULL 2 disabled +dml_reads 6 NULL 6 2 NULL 2 disabled dml_inserts 3 NULL 3 2 NULL 2 disabled dml_deletes 4 NULL 4 2 NULL 2 disabled dml_updates 2 NULL 2 0 NULL 0 disabled diff --git a/mysql-test/suite/innodb_gis/r/0.result b/mysql-test/suite/innodb_gis/r/0.result index ffe4230..6dd2cd1 100644 --- a/mysql-test/suite/innodb_gis/r/0.result +++ b/mysql-test/suite/innodb_gis/r/0.result @@ -560,29 +560,29 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) 1 -INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); -INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); +INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(3 4)')); +INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(3 4)')); EXPLAIN SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) -2 +1 EXPLAIN SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref p p 28 const # Using where SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) -2 +1 EXPLAIN SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) -2 +1 DROP TABLE t1, t2; End of 5.0 tests # diff --git a/mysql-test/suite/innodb_gis/r/1.result b/mysql-test/suite/innodb_gis/r/1.result index 84e7bec..8de9fd3 100644 --- a/mysql-test/suite/innodb_gis/r/1.result +++ b/mysql-test/suite/innodb_gis/r/1.result @@ -943,29 +943,29 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) 1 -INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); -INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); +INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(3 4)')); +INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(3 4)')); EXPLAIN SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) -2 +1 EXPLAIN SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref p p 28 const # Using where SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) -2 +1 EXPLAIN SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) -2 +1 DROP TABLE t1, t2; End of 5.0 tests # diff --git a/mysql-test/suite/innodb_gis/r/gis.result b/mysql-test/suite/innodb_gis/r/gis.result index 65d1e94..6929afd 100644 --- a/mysql-test/suite/innodb_gis/r/gis.result +++ b/mysql-test/suite/innodb_gis/r/gis.result @@ -939,29 +939,29 @@ id select_type table type possible_keys key key_len ref rows Extra SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) 1 -INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); -INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); +INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(3 4)')); +INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(3 4)')); EXPLAIN SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) -2 +1 EXPLAIN SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref p p 28 const # Using where SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) -2 +1 EXPLAIN SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); COUNT(*) -2 +1 DROP TABLE t1, t2; End of 5.0 tests # diff --git a/mysql-test/suite/innodb_gis/r/rtree_estimate.result b/mysql-test/suite/innodb_gis/r/rtree_estimate.result index dafcc40..edb3777 100644 --- a/mysql-test/suite/innodb_gis/r/rtree_estimate.result +++ b/mysql-test/suite/innodb_gis/r/rtree_estimate.result @@ -30,7 +30,7 @@ ST_AsText(g) POINT(10 10) EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRDisjoint(g, @g1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range g g 34 NULL 2 Using where +1 SIMPLE t1 ALL g NULL NULL NULL 3 Using where SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g1); ST_AsText(g) POINT(10 10) @@ -75,14 +75,14 @@ POINT(10 10) POLYGON((5 5,20 5,20 21,5 21,5 5)) EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRIntersects(g, @g2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range g g 34 NULL 2 Using where +1 SIMPLE t1 ALL g NULL NULL NULL 3 Using where SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g2); ST_AsText(g) POINT(10 10) POLYGON((5 5,20 5,20 21,5 21,5 5)) EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range g g 34 NULL 2 Using where +1 SIMPLE t1 ALL g NULL NULL NULL 3 Using where SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g2); ST_AsText(g) POINT(10 10) diff --git a/mysql-test/suite/innodb_gis/t/rtree_estimate.test b/mysql-test/suite/innodb_gis/t/rtree_estimate.test index 4caa5fe..7038799 100644 --- a/mysql-test/suite/innodb_gis/t/rtree_estimate.test +++ b/mysql-test/suite/innodb_gis/t/rtree_estimate.test @@ -15,6 +15,7 @@ SET @g1 = ST_GeomFromText('POINT(10 10)'); SET @g2 = ST_GeomFromText('POLYGON((5 5, 20 5, 20 21, 5 21, 5 5))'); SET @g3 = ST_GeomFromText('POLYGON((1.79769e+308 1.79769e+308, 20 5, -1.79769e+308 -1.79769e+308, 1.79769e+308 1.79769e+308))'); + # Test empty table EXPLAIN SELECT ST_AsText(g) FROM t1 WHERE MBRContains(g, @g1); SELECT ST_AsText(g) FROM t1 WHERE MBRWithin(g, @g1); diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result index 8fc93e8..14517fe 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 k1 5 NULL 4 Using where +1 SIMPLE t1 range PRIMARY,k1 PRIMARY 4 NULL 3 Using index condition; Using where; Rowid-ordered scan; Using filesort DROP TABLE t1; # # @@ -590,6 +590,12 @@ i1 INTEGER NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t2 VALUES (4,1); +ANALYZE TABLE t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK EXPLAIN SELECT t1.d1, t2.pk, t2.i1 FROM t1 STRAIGHT_JOIN t2 ON t2.i1 WHERE t2.pk <> t1.d1 AND t2.pk = 4; @@ -795,6 +801,12 @@ INSERT INTO t2 (g,h) VALUES (0,'p'),(0,'f'),(0,'p'),(7,'d'),(7,'f'),(5,'j'), (3,'e'),(1,'u'),(4,'v'),(9,'u'),(6,'i'),(1,'x'), (7,'f'),(5,'j'),(3,'e'),(1,'u'),(4,'v'),(9,'u'); +ANALYZE TABLE t1,t2; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status Table is already up to date SET @save_optimize_switch=@@optimizer_switch; SET optimizer_switch='materialization=on'; EXPLAIN @@ -804,7 +816,7 @@ AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b) OR a = 0 AND h < 'z' ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ALL PRIMARY,c NULL NULL NULL 64 Using where -1 PRIMARY t2 ref g g 5 test.t.c 19 Using where +1 PRIMARY t2 ref g g 5 test.t.c 18 Using where 2 DEPENDENT SUBQUERY t1 index PRIMARY PRIMARY 4 NULL 64 Using where; Using index 2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where SELECT COUNT(*) FROM t1 AS t, t2 diff --git a/mysql-test/suite/maria/ps_maria.result b/mysql-test/suite/maria/ps_maria.result index bd5f2b4..6f5d557 100644 --- a/mysql-test/suite/maria/ps_maria.result +++ b/mysql-test/suite/maria/ps_maria.result @@ -1161,7 +1161,7 @@ def possible_keys 253 4_OR_8_K 0 Y 0 39 8 def key 253 64 0 Y 0 39 8 def key_len 253 4_OR_8_K 0 Y 0 39 8 def ref 253 2048 0 Y 0 39 8 -def rows 8 10 1 Y 32928 0 63 +def rows 253 64 1 Y 0 39 8 def Extra 253 255 0 N 1 39 8 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t9 ALL NULL NULL NULL NULL 2 diff --git a/mysql-test/suite/parts/r/optimizer.result b/mysql-test/suite/parts/r/optimizer.result index 465c6c7..42d85db 100644 --- a/mysql-test/suite/parts/r/optimizer.result +++ b/mysql-test/suite/parts/r/optimizer.result @@ -25,7 +25,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 2 Using where; Using index EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range a a 5 NULL 2 Using where; Using index for group-by +1 SIMPLE t2 range a a 5 NULL 2 Using where; Using index FLUSH status; SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a; a MAX(b) @@ -41,5 +41,5 @@ a MAX(b) # Should be no more than 4 reads. SHOW status LIKE 'handler_read_key'; Variable_name Value -Handler_read_key 4 +Handler_read_key 2 DROP TABLE t1, t2; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 926ac8b..ddaa495 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -2252,6 +2252,20 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL +VARIABLE_NAME MAX_ROWID_FILTER_SIZE +SESSION_VALUE 131072 +GLOBAL_VALUE 131072 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 131072 +VARIABLE_SCOPE SESSION +VARIABLE_TYPE BIGINT UNSIGNED +VARIABLE_COMMENT The maximum size of the container of a rowid filter +NUMERIC_MIN_VALUE 1024 +NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME MAX_SEEKS_FOR_KEY SESSION_VALUE 4294967295 GLOBAL_VALUE 4294967295 @@ -2729,17 +2743,17 @@ ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_SWITCH -SESSION_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on -GLOBAL_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on +SESSION_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on +GLOBAL_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on GLOBAL_VALUE_ORIGIN COMPILE-TIME -DEFAULT_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on +DEFAULT_VALUE index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on VARIABLE_SCOPE SESSION VARIABLE_TYPE FLAGSET VARIABLE_COMMENT Fine-tune the optimizer behavior NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL -ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,default +ENUM_VALUE_LIST index_merge,index_merge_union,index_merge_sort_union,index_merge_intersection,index_merge_sort_intersection,engine_condition_pushdown,index_condition_pushdown,derived_merge,derived_with_keys,firstmatch,loosescan,materialization,in_to_exists,semijoin,partial_match_rowid_merge,partial_match_table_scan,subquery_cache,mrr,mrr_cost_based,mrr_sort_keys,outer_join_with_cache,semijoin_with_cache,join_cache_incremental,join_cache_hashed,join_cache_bka,optimize_join_buffer_size,table_elimination,extended_keys,exists_to_in,orderby_uses_equalities,condition_pushdown_for_derived,split_materialized,condition_pushdown_for_subquery,rowid_filter,default READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME OPTIMIZER_USE_CONDITION_SELECTIVITY diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 4f1b898..6c57061 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -2483,7 +2483,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 131072 VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED -VARIABLE_COMMENT The maximum number of rows that fit in memory +VARIABLE_COMMENT The maximum size of the container of a rowid filter NUMERIC_MIN_VALUE 1024 NUMERIC_MAX_VALUE 18446744073709551615 NUMERIC_BLOCK_SIZE 1 diff --git a/mysql-test/suite/vcol/inc/vcol_select.inc b/mysql-test/suite/vcol/inc/vcol_select.inc index 0641e14..cbd1f2c 100644 --- a/mysql-test/suite/vcol/inc/vcol_select.inc +++ b/mysql-test/suite/vcol/inc/vcol_select.inc @@ -35,7 +35,7 @@ insert into t2 (a) values (1); create table t3 (a int primary key, b int as (-a), c int as (-a) persistent unique); -insert into t3 (a) values (2),(1),(3); +insert into t3 (a) values (2),(1),(3),(5),(4),(7); --echo # select_type=SIMPLE, type=system diff --git a/mysql-test/suite/vcol/r/vcol_select_innodb.result b/mysql-test/suite/vcol/r/vcol_select_innodb.result index 63c35ba..6ebdd87 100644 --- a/mysql-test/suite/vcol/r/vcol_select_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_select_innodb.result @@ -9,7 +9,7 @@ insert into t2 (a) values (1); create table t3 (a int primary key, b int as (-a), c int as (-a) persistent unique); -insert into t3 (a) values (2),(1),(3); +insert into t3 (a) values (2),(1),(3),(5),(4),(7); # select_type=SIMPLE, type=system select * from t2; a b c @@ -63,8 +63,8 @@ a b c 3 -3 -3 explain select * from t1 where b in (select c from t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 3 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t3 ref c c 5 test.t1.b 1 Using index # select_type=PRIMARY, type=range,ref select * from t1 where c in (select c from t3 where c between -2 and -1); a b c @@ -73,7 +73,7 @@ a b c 1 -1 -1 explain select * from t1 where c in (select c from t3 where c between -2 and -1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 3 Using where; Using index +1 PRIMARY t3 index c c 5 NULL 6 Using where; Using index 1 PRIMARY t1 ALL c NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) # select_type=UNION, type=system # select_type=UNION RESULT, type=<union1,2> @@ -160,7 +160,7 @@ a b c 2 -2 -2 explain select * from t3 where b between -2 and -1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where # SELECT * FROM tbl_name WHERE <indexed vcol expr> select * from t3 where c between -2 and -1; a b c @@ -192,7 +192,7 @@ a b c 2 -2 -2 explain select * from t3 where b between -2 and -1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index NULL PRIMARY 4 NULL 3 Using where +1 SIMPLE t3 index NULL PRIMARY 4 NULL 6 Using where # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-indexed vcol> select * from t3 where b between -2 and -1 order by b; a b c @@ -200,7 +200,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-indexed vcol> select * from t3 where c between -2 and -1 order by b; a b c @@ -216,7 +216,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <indexed vcol> select * from t3 where c between -2 and -1 order by c; a b c diff --git a/mysql-test/suite/vcol/r/vcol_select_myisam.result b/mysql-test/suite/vcol/r/vcol_select_myisam.result index be3ea81..4e21582 100644 --- a/mysql-test/suite/vcol/r/vcol_select_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_select_myisam.result @@ -9,7 +9,7 @@ insert into t2 (a) values (1); create table t3 (a int primary key, b int as (-a), c int as (-a) persistent unique); -insert into t3 (a) values (2),(1),(3); +insert into t3 (a) values (2),(1),(3),(5),(4),(7); # select_type=SIMPLE, type=system select * from t2; a b c @@ -44,7 +44,7 @@ a b c 1 -1 -1 explain select * from t3 where c>=-1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 2 Using index condition +1 SIMPLE t3 range c c 5 NULL 1 Using index condition # select_type=SIMPLE, type=ref select * from t1,t3 where t1.c=t3.c and t3.c=-1; a b c a b c @@ -63,8 +63,8 @@ a b c 3 -3 -3 explain select * from t1 where b in (select c from t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 3 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t3 ref c c 5 test.t1.b 2 Using index # select_type=PRIMARY, type=range,ref select * from t1 where c in (select c from t3 where c between -2 and -1); a b c @@ -73,8 +73,8 @@ a b c 1 -1 -1 explain select * from t1 where c in (select c from t3 where c between -2 and -1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 3 Using where; Using index -1 PRIMARY t1 ref c c 5 test.t3.c 2 +1 PRIMARY t1 range c c 5 NULL 3 Using index condition +1 PRIMARY t3 index c c 5 NULL 6 Using where; Using index; Using join buffer (flat, BNL join) # select_type=UNION, type=system # select_type=UNION RESULT, type=<union1,2> select * from t1 union select * from t2; @@ -152,7 +152,7 @@ a b c 2 -2 -2 explain select * from t3 where a between 1 and 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using index condition +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> select * from t3 where b between -2 and -1; a b c @@ -160,7 +160,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where # SELECT * FROM tbl_name WHERE <indexed vcol expr> select * from t3 where c between -2 and -1; a b c @@ -168,7 +168,7 @@ a b c 1 -1 -1 explain select * from t3 where c between -2 and -1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using index condition +1 SIMPLE t3 range c c 5 NULL 2 Using index condition # SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <indexed vcol> select * from t3 where a between 1 and 2 order by c; a b c @@ -176,7 +176,7 @@ a b c 1 -1 -1 explain select * from t3 where a between 1 and 2 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using filesort +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-vcol> select * from t3 where b between -2 and -1 order by a; a b c @@ -184,7 +184,7 @@ a b c 2 -2 -2 explain select * from t3 where b between -2 and -1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-vcol> select * from t3 where c between -2 and -1 order by a; a b c @@ -192,7 +192,7 @@ a b c 2 -2 -2 explain select * from t3 where c between -2 and -1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Using filesort +1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-indexed vcol> select * from t3 where b between -2 and -1 order by b; a b c @@ -200,7 +200,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-indexed vcol> select * from t3 where c between -2 and -1 order by b; a b c @@ -208,7 +208,7 @@ a b c 1 -1 -1 explain select * from t3 where c between -2 and -1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Using filesort +1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <indexed vcol> select * from t3 where b between -2 and -1 order by c; a b c @@ -216,7 +216,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <indexed vcol> select * from t3 where c between -2 and -1 order by c; a b c @@ -224,7 +224,7 @@ a b c 1 -1 -1 explain select * from t3 where c between -2 and -1 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using index condition +1 SIMPLE t3 range c c 5 NULL 2 Using index condition # SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol> select sum(b) from t1 group by b; sum(b) diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 575af82..195f937 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -6184,7 +6184,7 @@ static Sys_var_enum Sys_secure_timestamp( static Sys_var_ulonglong Sys_max_rowid_filter_size( "max_rowid_filter_size", - "The maximum number of rows that fit in memory", + "The maximum size of the container of a rowid filter", SESSION_VAR(max_rowid_filter_size), CMD_LINE(REQUIRED_ARG), VALID_RANGE(1024, (ulonglong)~(intptr)0), DEFAULT(128*1024), BLOCK_SIZE(1)); diff --git a/storage/connect/mysql-test/connect/r/mysql_index.result b/storage/connect/mysql-test/connect/r/mysql_index.result index dd18645..b0c88b1 100644 --- a/storage/connect/mysql-test/connect/r/mysql_index.result +++ b/storage/connect/mysql-test/connect/r/mysql_index.result @@ -45,10 +45,9 @@ id msg SELECT * FROM t2 WHERE id IN (2,4) AND msg = 'Two'; id msg 2 Two -SELECT * FROM t2 WHERE id > 3; +SELECT * FROM t2 WHERE id > 4; id msg 5 Cinq -4 Four 6 Six SELECT * FROM t2 WHERE id >= 3; id msg @@ -60,10 +59,9 @@ SELECT * FROM t2 WHERE id < 3; id msg 1 Un 2 Two -SELECT * FROM t2 WHERE id < 3 OR id > 4; +SELECT * FROM t2 WHERE id < 2 OR id > 4; id msg 1 Un -2 Two 5 Cinq 6 Six SELECT * FROM t2 WHERE id <= 3; @@ -166,141 +164,141 @@ 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 -5707 FOCH DENIS +4080 FOCH SERGE 2552 FOCH FRANCK +5707 FOCH DENIS 2634 FOCH JOCELYNE 5765 FOCH ROBERT -4080 FOCH SERGE -3368 MOGADOR ALAIN SELECT matricule, nom, prenom FROM t2 WHERE nom = 'FOCH' OR nom = 'MOGADOR'; matricule nom prenom +3368 MOGADOR ALAIN 1977 FOCH BERNADETTE -5707 FOCH DENIS +4080 FOCH SERGE 2552 FOCH FRANCK +5707 FOCH DENIS 2634 FOCH JOCELYNE 5765 FOCH ROBERT -4080 FOCH SERGE -3368 MOGADOR ALAIN SELECT matricule, nom, prenom FROM t2 WHERE nom < 'ADDAX'; matricule nom prenom -4552 ABBADIE MONIQUE -307 ABBAYE ANNICK -6627 ABBAYE GERALD -7961 ABBE KATIA 1340 ABBE MICHELE -9270 ABBE SOPHIE +2728 ABOUT CATHERINE MARIE +895 ABORD CHANTAL +4038 ADAM JANICK +6627 ABBAYE GERALD +6124 ABELIAS DELIA +4552 ABBADIE MONIQUE +8673 ABEL JEAN PIERRE +3395 ADAM JEAN CLAUDE 2945 ABBEVILLE PASCAL -8596 ABEBERRY PATRICK +115 ACHILLE JACQUES 6399 ABEILLES RENE -8673 ABEL JEAN PIERRE -6124 ABELIAS DELIA -6314 ABERDEN EVELYNE -895 ABORD CHANTAL -2728 ABOUT CATHERINE MARIE +8596 ABEBERRY PATRICK +9270 ABBE SOPHIE 398 ABREUVOIR JEAN LUC -1122 ACACIAS SERGE +7961 ABBE KATIA +307 ABBAYE ANNICK +6314 ABERDEN EVELYNE 1644 ACARDIE BEATE -115 ACHILLE JACQUES -4038 ADAM JANICK -3395 ADAM JEAN CLAUDE +1122 ACACIAS SERGE SELECT matricule, nom, prenom FROM t2 WHERE nom <= 'ABEL'; matricule nom prenom -4552 ABBADIE MONIQUE -307 ABBAYE ANNICK -6627 ABBAYE GERALD -7961 ABBE KATIA 1340 ABBE MICHELE -9270 ABBE SOPHIE +6627 ABBAYE GERALD +4552 ABBADIE MONIQUE +8673 ABEL JEAN PIERRE 2945 ABBEVILLE PASCAL -8596 ABEBERRY PATRICK 6399 ABEILLES RENE -8673 ABEL JEAN PIERRE +8596 ABEBERRY PATRICK +9270 ABBE SOPHIE +7961 ABBE KATIA +307 ABBAYE ANNICK SELECT matricule, nom, prenom FROM t2 WHERE nom > 'YVON'; matricule nom prenom 9742 YZENGREMER MICHEL -8738 ZILINA JEAN LOUIS 5357 ZOLA BERNARD 5441 ZOLA BRIGITTE -1325 ZOLA CHRISTINE -4859 ZORI CATHERINE 4102 ZOUAVES ALAIN +4859 ZORI CATHERINE +1325 ZOLA CHRISTINE +8738 ZILINA JEAN LOUIS SELECT matricule, nom, prenom FROM t2 WHERE nom >= 'YVON'; matricule nom prenom -5389 YVON CAROLE 9742 YZENGREMER MICHEL -8738 ZILINA JEAN LOUIS 5357 ZOLA BERNARD +5389 YVON CAROLE 5441 ZOLA BRIGITTE -1325 ZOLA CHRISTINE -4859 ZORI CATHERINE 4102 ZOUAVES ALAIN +4859 ZORI CATHERINE +1325 ZOLA CHRISTINE +8738 ZILINA JEAN LOUIS SELECT matricule, nom, prenom FROM t2 WHERE nom <= 'ABEL' OR nom > 'YVON'; matricule nom prenom -4552 ABBADIE MONIQUE -307 ABBAYE ANNICK -6627 ABBAYE GERALD -7961 ABBE KATIA -1340 ABBE MICHELE -9270 ABBE SOPHIE -2945 ABBEVILLE PASCAL -8596 ABEBERRY PATRICK -6399 ABEILLES RENE -8673 ABEL JEAN PIERRE 9742 YZENGREMER MICHEL -8738 ZILINA JEAN LOUIS +1340 ABBE MICHELE 5357 ZOLA BERNARD +6627 ABBAYE GERALD +4552 ABBADIE MONIQUE 5441 ZOLA BRIGITTE -1325 ZOLA CHRISTINE -4859 ZORI CATHERINE 4102 ZOUAVES ALAIN +8673 ABEL JEAN PIERRE +4859 ZORI CATHERINE +2945 ABBEVILLE PASCAL +1325 ZOLA CHRISTINE +6399 ABEILLES RENE +8596 ABEBERRY PATRICK +9270 ABBE SOPHIE +7961 ABBE KATIA +307 ABBAYE ANNICK +8738 ZILINA JEAN LOUIS SELECT matricule, nom, prenom FROM t2 WHERE nom > 'HELEN' AND nom < 'HEROS'; matricule nom prenom -9096 HELENA PHILIPPE -3309 HELENE ISABELLE -8365 HELIOTROPES LISE -4666 HELLEN PIERRE -5781 HELSINKI DANIELLE +2085 HEOL GUY PAUL +2673 HENNER LILIANE +7093 HERAULTS DANIEL 7626 HENIN PHILIPPE +403 HERMITTE PHILIPPE 4254 HENIN SERGE -2673 HENNER LILIANE +4666 HELLEN PIERRE +3309 HELENE ISABELLE +9749 HEROLD ISABELLE 9716 HENRI JACQUES -2085 HEOL GUY PAUL -2579 HERANDIERE PIERRE -7093 HERAULTS DANIEL +1291 HERMITAGE XAVIER +8365 HELIOTROPES LISE 4050 HERBILLON FRANCOIS 9231 HERBILLON MADELEINE -1291 HERMITAGE XAVIER +9096 HELENA PHILIPPE +5781 HELSINKI DANIELLE +2579 HERANDIERE PIERRE 6185 HERMITTE FRANCOIS -403 HERMITTE PHILIPPE -9749 HEROLD ISABELLE SELECT matricule, nom, prenom FROM t2 WHERE nom BETWEEN 'HELEN' AND 'HEROS'; matricule nom prenom -6199 HELEN MARTIAL -9096 HELENA PHILIPPE -3309 HELENE ISABELLE -8365 HELIOTROPES LISE -4666 HELLEN PIERRE -5781 HELSINKI DANIELLE +2085 HEOL GUY PAUL +2673 HENNER LILIANE +7093 HERAULTS DANIEL 7626 HENIN PHILIPPE +403 HERMITTE PHILIPPE 4254 HENIN SERGE -2673 HENNER LILIANE +4666 HELLEN PIERRE +3309 HELENE ISABELLE +9749 HEROLD ISABELLE 9716 HENRI JACQUES -2085 HEOL GUY PAUL -2579 HERANDIERE PIERRE -7093 HERAULTS DANIEL +1291 HERMITAGE XAVIER +8365 HELIOTROPES LISE 4050 HERBILLON FRANCOIS 9231 HERBILLON MADELEINE -1291 HERMITAGE XAVIER -6185 HERMITTE FRANCOIS -403 HERMITTE PHILIPPE -9749 HEROLD ISABELLE 8445 HEROS SYLVIE +9096 HELENA PHILIPPE +5781 HELSINKI DANIELLE +2579 HERANDIERE PIERRE +6199 HELEN MARTIAL +6185 HERMITTE FRANCOIS SELECT matricule, nom, prenom FROM t2 WHERE nom BETWEEN 'HELEN' AND 'HEROS' AND prenom = 'PHILIPPE'; matricule nom prenom -9096 HELENA PHILIPPE 7626 HENIN PHILIPPE 403 HERMITTE PHILIPPE +9096 HELENA PHILIPPE SELECT matricule, nom, prenom FROM t2 ORDER BY nom LIMIT 10; matricule nom prenom 4552 ABBADIE MONIQUE diff --git a/storage/connect/mysql-test/connect/r/part_file.result b/storage/connect/mysql-test/connect/r/part_file.result index c679ed9..3dabd94 100644 --- a/storage/connect/mysql-test/connect/r/part_file.result +++ b/storage/connect/mysql-test/connect/r/part_file.result @@ -145,14 +145,11 @@ id select_type table partitions type possible_keys key key_len ref rows Extra SELECT * FROM t1 WHERE id = 10; rwid rnum prtn tbn fid id msg 1 1 2 t1 part2 10 ten -EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id >= 10; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id >= 40; id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t1 2,3 range PRIMARY PRIMARY 4 NULL 7 Using where -SELECT * FROM t1 WHERE id >= 10; +1 SIMPLE t1 2,3 range PRIMARY PRIMARY 4 NULL 4 Using where +SELECT * FROM t1 WHERE id >= 40; rwid rnum prtn tbn fid id msg -1 1 2 t1 part2 10 ten -3 3 2 t1 part2 20 twenty -4 4 2 t1 part2 35 thirty five 2 2 2 t1 part2 40 forty 1 1 3 t1 part3 60 sixty 3 3 3 t1 part3 72 seventy two diff --git a/storage/connect/mysql-test/connect/t/mysql_index.test b/storage/connect/mysql-test/connect/t/mysql_index.test index 81fdcad..74dc48f 100644 --- a/storage/connect/mysql-test/connect/t/mysql_index.test +++ b/storage/connect/mysql-test/connect/t/mysql_index.test @@ -49,10 +49,10 @@ SELECT * FROM t2; SELECT * FROM t2 WHERE id = 3; SELECT * FROM t2 WHERE id IN (2,4); SELECT * FROM t2 WHERE id IN (2,4) AND msg = 'Two'; -SELECT * FROM t2 WHERE id > 3; +SELECT * FROM t2 WHERE id > 4; SELECT * FROM t2 WHERE id >= 3; SELECT * FROM t2 WHERE id < 3; -SELECT * FROM t2 WHERE id < 3 OR id > 4; +SELECT * FROM t2 WHERE id < 2 OR id > 4; SELECT * FROM t2 WHERE id <= 3; SELECT * FROM t2 WHERE id BETWEEN 3 AND 5; SELECT * FROM t2 WHERE id > 2 AND id < 6; diff --git a/storage/connect/mysql-test/connect/t/part_file.test b/storage/connect/mysql-test/connect/t/part_file.test index 8ee43a9..2e5127f 100644 --- a/storage/connect/mysql-test/connect/t/part_file.test +++ b/storage/connect/mysql-test/connect/t/part_file.test @@ -82,8 +82,8 @@ SELECT * FROM t1; SELECT * FROM t1 order by id; EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id = 10; SELECT * FROM t1 WHERE id = 10; -EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id >= 10; -SELECT * FROM t1 WHERE id >= 10; +EXPLAIN PARTITIONS SELECT * FROM t1 WHERE id >= 40; +SELECT * FROM t1 WHERE id >= 40; SELECT count(*) FROM t1 WHERE id < 10; SELECT case when id < 10 then 1 when id < 50 then 2 else 3 end as pn, count(*) FROM t1 group by pn; SELECT prtn, count(*) FROM t1 group by prtn; diff --git a/storage/innobase/srv/srv0mon.cc b/storage/innobase/srv/srv0mon.cc index 4d796d9..8bc9673 100644 --- a/storage/innobase/srv/srv0mon.cc +++ b/storage/innobase/srv/srv0mon.cc @@ -1414,7 +1414,7 @@ static monitor_info_t innodb_counter_info[] = MONITOR_NONE, MONITOR_DEFAULT_START, MONITOR_PK_FILTER_POSITIVE}, - {"pk-filter_negarive", "pk-filter", "PK-filter test is negative", + {"pk-filter_negative", "pk-filter", "PK-filter test is negative", MONITOR_NONE, MONITOR_DEFAULT_START, MONITOR_PK_FILTER_NEGATIVE}, diff --git a/storage/myisam/mi_range.c b/storage/myisam/mi_range.c index 185f2ca..a464b3d 100644 --- a/storage/myisam/mi_range.c +++ b/storage/myisam/mi_range.c @@ -103,7 +103,7 @@ ha_rows mi_records_in_range(MI_INFO *info, int inx, max_key->keypart_map, max_key->flag) : (double) info->state->records); res= (end_pos < start_pos ? (ha_rows) 0 : - (end_pos == start_pos ? (ha_rows) 1 : end_pos-start_pos)); + (end_pos == start_pos ? (ha_rows) 1 : (ha_rows) (end_pos-start_pos))); if (start_pos == HA_POS_ERROR || end_pos == HA_POS_ERROR) res=HA_POS_ERROR; else @@ -111,7 +111,7 @@ ha_rows mi_records_in_range(MI_INFO *info, int inx, diff= end_pos - start_pos; if (diff >= 0) { - if (!(res= (diff + 0.5))) + if (!(res= (ha_rows) (diff + 0.5))) res= 1; } else @@ -208,7 +208,7 @@ static double _mi_record_pos(MI_INFO *info, const uchar *key, DBUG_PRINT("exit",("pos: %g",(pos*info->state->records))); DBUG_RETURN(pos*info->state->records); } - DBUG_RETURN(HA_POS_ERROR); + DBUG_RETURN((double) (HA_POS_ERROR)); }