BUG#8924 'Explain' shows different strategy

 - If number of records in table is 4, the calculated cost for using "index" and "range" become so close so that any rounding errors becomes visible.
 - Added one more record to the tables for heap test and expoect "range" to be selected
 - Decrease number of records in t1 for range and expect "index" to be choosen.


mysql-test/r/heap.result:
  Add one more record to table t1 and expect explain to use "range"
mysql-test/r/heap_btree.result:
  Add one more record to table t1 and expect explain to use "range"
mysql-test/r/heap_hash.result:
  Add one more record to table t1 and expect explain to use "range"
mysql-test/r/range.result:
  Update results
mysql-test/t/heap.test:
  Add one more record to table t1 and expect explain to use "range"
mysql-test/t/heap_btree.test:
  Add one more record to table t1 and expect explain to use "range"
mysql-test/t/heap_hash.test:
  Add one more record to table t1 in order for optimizer to select use of "range" deterministic
mysql-test/t/range.test:
  Remove one record from table t1 to avoid that cost for "index" and "range" are so close that rounding error become visible.

8 files changed, 13 insertions, 13 deletions

diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result
index 631bd8c713c..37214e12d63 100644
--- a/mysql-test/r/heap.result
+++ b/mysql-test/r/heap.result
@@ -40,7 +40,7 @@ a	b
 4	4
 drop table t1;
 create table t1 (a int not null) engine=heap;
-insert into t1 values (869751),(736494),(226312),(802616);
+insert into t1 values (869751),(736494),(226312),(802616),(728912);
 select * from t1 where a > 736494;
 a
 869751
@@ -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	index	uniq_id	uniq_id	4	NULL	4	Using where; Using index
+1	SIMPLE	t1	range	uniq_id	uniq_id	4	NULL	4	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/r/heap_btree.result b/mysql-test/r/heap_btree.result
index 8f9349ce316..374d2c63632 100644
--- a/mysql-test/r/heap_btree.result
+++ b/mysql-test/r/heap_btree.result
@@ -40,7 +40,7 @@ a	b
 4	4
 drop table t1;
 create table t1 (a int not null) engine=heap;
-insert into t1 values (869751),(736494),(226312),(802616);
+insert into t1 values (869751),(736494),(226312),(802616),(728912);
 select * from t1 where a > 736494;
 a
 869751
@@ -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	index	uniq_id	uniq_id	4	NULL	4	Using where; Using index
+1	SIMPLE	t1	range	uniq_id	uniq_id	4	NULL	4	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;
diff --git a/mysql-test/r/heap_hash.result b/mysql-test/r/heap_hash.result
index 1e6eb866fd6..9720fe4843a 100644
--- a/mysql-test/r/heap_hash.result
+++ b/mysql-test/r/heap_hash.result
@@ -40,7 +40,7 @@ a	b
 4	4
 drop table t1;
 create table t1 (a int not null) engine=heap;
-insert into t1 values (869751),(736494),(226312),(802616);
+insert into t1 values (869751),(736494),(226312),(802616),(728912);
 select * from t1 where a > 736494;
 a
 869751
@@ -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	index	uniq_id	uniq_id	4	NULL	4	Using where; Using index
+1	SIMPLE	t1	range	uniq_id	uniq_id	4	NULL	4	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;
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 8a1d80e9f79..d98ed90e158 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -250,18 +250,18 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	x	x	5	NULL	9	Using where; Using index
 drop table t1;
 CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1));
-INSERT INTO t1 VALUES (0),(0),(1),(1);
+INSERT INTO t1 VALUES (0),(1),(1);
 CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya));
 INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2);
 explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ref	j1	j1	4	const	1	Using index
-1	SIMPLE	t1	index	i1	i1	4	NULL	4	Using where; Using index
+1	SIMPLE	t1	index	i1	i1	4	NULL	3	Using where; Using index
 explain select * from t1 force index(i1), t2 force index(j1) where 
 (t1.key1 <t2.keya + 1) and t2.keya=3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ref	j1	j1	4	const	1	Using index
-1	SIMPLE	t1	index	i1	i1	4	NULL	4	Using where; Using index
+1	SIMPLE	t1	index	i1	i1	4	NULL	3	Using where; Using index
 DROP TABLE t1,t2;
 CREATE TABLE t1 (
 a int(11) default NULL,
diff --git a/mysql-test/t/heap.test b/mysql-test/t/heap.test
index c36474bda30..55429aa5c09 100644
--- a/mysql-test/t/heap.test
+++ b/mysql-test/t/heap.test
@@ -34,7 +34,7 @@ select * from t1;
 drop table t1;
 
 create table t1 (a int not null) engine=heap;
-insert into t1 values (869751),(736494),(226312),(802616);
+insert into t1 values (869751),(736494),(226312),(802616),(728912);
 select * from t1 where a > 736494;
 alter table t1 add unique uniq_id(a);
 select * from t1 where a > 736494;
diff --git a/mysql-test/t/heap_btree.test b/mysql-test/t/heap_btree.test
index d156d059634..6fec2ac1703 100644
--- a/mysql-test/t/heap_btree.test
+++ b/mysql-test/t/heap_btree.test
@@ -34,7 +34,7 @@ select * from t1;
 drop table t1;
 
 create table t1 (a int not null) engine=heap;
-insert into t1 values (869751),(736494),(226312),(802616);
+insert into t1 values (869751),(736494),(226312),(802616),(728912);
 select * from t1 where a > 736494;
 alter table t1 add unique uniq_id using BTREE (a);
 select * from t1 where a > 736494;
diff --git a/mysql-test/t/heap_hash.test b/mysql-test/t/heap_hash.test
index 6d27f19dfad..46669dd2b8f 100644
--- a/mysql-test/t/heap_hash.test
+++ b/mysql-test/t/heap_hash.test
@@ -34,7 +34,7 @@ select * from t1;
 drop table t1;
 
 create table t1 (a int not null) engine=heap;
-insert into t1 values (869751),(736494),(226312),(802616);
+insert into t1 values (869751),(736494),(226312),(802616),(728912);
 select * from t1 where a > 736494;
 alter table t1 add unique uniq_id using HASH (a);
 select * from t1 where a > 736494;
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 18cf614f338..16be850548c 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -198,7 +198,7 @@ drop table t1;
 # bug #1172: "Force index" option caused server crash
 #
 CREATE TABLE t1 (key1 int(11) NOT NULL default '0', KEY i1 (key1));
-INSERT INTO t1 VALUES (0),(0),(1),(1);
+INSERT INTO t1 VALUES (0),(1),(1);
 CREATE TABLE t2 (keya int(11) NOT NULL default '0', KEY j1 (keya));
 INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2);
 explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3;