Fixes for:

BUG#13519696 - 62940: SELECT RESULTS VARY WITH VERSION AND
WITH/WITHOUT INDEX RANGE SCAN
BUG#13453382 - REGRESSION SINCE 5.1.39, RANGE OPTIMIZER WRONG
RESULTS WITH DECIMAL CONVERSION
BUG#13463488 - 63437: CHAR & BETWEEN WITH INDEX RETURNS WRONG
RESULT AFTER MYSQL 5.1.
Those are all cases where the range optimizer got it wrong
with > and >=.

mysql-test/r/range.result:
  Without the code fix for DECIMAL, "select count(val) from t2 where val > 0.1155"
  (which uses a range scan) returned 127 instead of 128);
  Moreover, both
  select * from t1 force  index (primary) where a=1 and c>= 2.9;
  and
  select * from t1 force  index (primary) where a=1 and c> 2.9;
  would miss "1	1	3".
  Without the code fix for strings, both
  SELECT * FROM t1 WHERE F1 >= 'A    ';
  and
  SELECT * FROM t1 WHERE F1 BETWEEN 'A    ' AND 'AAAAA';
  would miss "A	A	A".
sql/item.cc:
  Preamble to the explanations below: opt_range.cc:get_mm_leaf() does
  this (this is not changed by the patch): changes
  column > value
  to
  column OP V
  where:
  * V is what is in "column" after we stored "value" in it
  (such store operation may have done rounding...)
  * OP is > or >=, depending on what's correct.
  For example, if c is an INT column,
  c > 2.9 is changed to
  c OP 3
  where OP is >= ('>' would not be correct).
  The bugs below are cases where we chose OP wrongly.
  Note that such transformations are visible in the optimizer trace.
  
  1) Fix for STRING. In the scenario with CHAR(5) in range.test, this happens,
  in get_mm_tree(), for the condition F1>='A    ':
  * value->save_in_field_no_warnings(field, 1) wants to store the right argument
  (named 'item') into the CHAR(5) field; this stores 'A    ' (the item's value)
  padded with spaces (which changes nothing: still 'A    ')
  * we come to
    case Item_func::GE_FUNC:
      /* Don't use open ranges for partial key_segments */
      if ((!(key_part->flag & HA_PART_KEY_SEG)) &&
          (stored_field_cmp_to_item(param->thd, field, value) < 0))
        tree->min_flag= NEAR_MIN;
      tree->max_flag=NO_MAX_RANGE;
  What this wants to do is: if the field's value is strictly smaller
  than the item's, then ">=" can be changed to ">" (this is an optimization,
  it can help pruning one useless partition).
  * stored_field_cmp_to_item() is called; it compares the field's
  and item's values: the item's value (Item_string::val_str()) is
  'A    ') and the field's value (Field_string::val_str()) is
  'A' (yes val_str() removes end spaces unless sql_mode='PAD_CHAR_TO_FULL_LENGTH');
  and the comparison is done with stringcmp() which considers
  end spaces as relevant; as end spaces differ, function returns a
  negative number, and ">='A    '" becomes ">'A'" (i.e. the NEAR_MIN
  flag is turned on).
  During execution the index range scan code will search for "A", find
  a match, but exclude it (because of ">"), wrongly.
  The badness is the string comparison done by stored_field_cmp_to_item():
  we use the reply of this function to determine where the index search
  should start, so it should do comparison like index search does
  comparisons; index search comparisons are ha_key_cmp() which uses
  a collation-aware comparison (in our case, my_strnncollsp_simple(),
  which ignores end spaces); so stored_field_cmp_to_item()
  needs to do the same. When this is fixed, condition becomes
  ">='A    '".
  
  2) Fix for DECIMAL: just like in other comparisons in stored_field_cmp_to_item(),
  we must first pass the field and then the item; otherwise expectations
  on what <0 and >0 mean (inferiority, superiority) get violated.
  In the test in range.test about c>2.9: c is an INT column, so 2.9
  gets stored as 3, then stored_field_cmp_to_item() compares 3
  and 2.9; because of the wrong order of arguments passed
  to my_decimal_cmp(), range optimizer
  thinks that 3 is < 2.9 and thus changes "c> 2.9" to "c> 3".
  After fixing the order, it changes to the correct "c>= 3".
  In the test in range.inc for val > 0.1155, it was changed to
  val > 0.116, now it is changed to val >= 0.116.

2 files changed, 193 insertions, 0 deletions

diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index ae63edf87b9..5a964baf2c2 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -1767,4 +1767,121 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk;
 pk	i4	pk	i4
 DROP TABLE t1;
+#
+# BUG#13519696 - 62940: SELECT RESULTS VARY WITH VERSION AND
+# WITH/WITHOUT INDEX RANGE SCAN
+#
+create table t1 (id int unsigned not null auto_increment primary key);
+insert into t1 values (null);
+insert into t1 select null from t1;
+insert into t1 select null from t1;
+insert into t1 select null from t1;
+insert into t1 select null from t1;
+insert into t1 select null from t1;
+insert into t1 select null from t1;
+insert into t1 select null from t1;
+insert into t1 select null from t1;
+create table t2 (
+id int unsigned not null auto_increment,
+val decimal(5,3) not null,
+primary key (id,val),
+unique key (val,id),
+unique key (id));
+insert into t2 select null,id*0.0009 from t1;
+select count(val) from t2 ignore index (val) where val > 0.1155;
+count(val)
+128
+select count(val) from t2 force index (val)  where val > 0.1155;
+count(val)
+128
+drop table t2, t1;
+#
+# BUG#13453382 - REGRESSION SINCE 5.1.39, RANGE OPTIMIZER WRONG
+# RESULTS WITH DECIMAL CONVERSION
+#
+create table t1 (a int,b int,c int,primary key (a,c));
+insert into t1 values (1,1,2),(1,1,3),(1,1,4);
+select convert(3, signed integer) > 2.9;
+convert(3, signed integer) > 2.9
+1
+select * from t1 force  index (primary) where a=1 and c>= 2.9;
+a	b	c
+1	1	3
+1	1	4
+select * from t1 ignore index (primary) where a=1 and c>= 2.9;
+a	b	c
+1	1	3
+1	1	4
+select * from t1 force  index (primary) where a=1 and c> 2.9;
+a	b	c
+1	1	3
+1	1	4
+select * from t1 ignore index (primary) where a=1 and c> 2.9;
+a	b	c
+1	1	3
+1	1	4
+drop table t1;
+#
+# BUG#13463488 - 63437: CHAR & BETWEEN WITH INDEX RETURNS WRONG
+# RESULT AFTER MYSQL 5.1.
+#
+CREATE TABLE t1(
+F1 CHAR(5) NOT NULL,
+F2 CHAR(5) NOT NULL,
+F3 CHAR(5) NOT NULL,
+PRIMARY KEY(F1),
+INDEX IDX_F2(F2)
+);
+INSERT INTO t1 VALUES
+('A','A','A'),('AA','AA','AA'),('AAA','AAA','AAA'),
+('AAAA','AAAA','AAAA'),('AAAAA','AAAAA','AAAAA');
+SELECT * FROM t1 WHERE F1 = 'A    ';
+F1	F2	F3
+A	A	A
+SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 = 'A    ';
+F1	F2	F3
+A	A	A
+SELECT * FROM t1 WHERE F1 >= 'A    ';
+F1	F2	F3
+A	A	A
+AA	AA	AA
+AAA	AAA	AAA
+AAAA	AAAA	AAAA
+AAAAA	AAAAA	AAAAA
+SELECT * FROM t1 WHERE F1 > 'A    ';
+F1	F2	F3
+AA	AA	AA
+AAA	AAA	AAA
+AAAA	AAAA	AAAA
+AAAAA	AAAAA	AAAAA
+SELECT * FROM t1 WHERE F1 BETWEEN 'A    ' AND 'AAAAA';
+F1	F2	F3
+A	A	A
+AA	AA	AA
+AAA	AAA	AAA
+AAAA	AAAA	AAAA
+AAAAA	AAAAA	AAAAA
+SELECT * FROM t1 WHERE F2 BETWEEN 'A    ' AND 'AAAAA';
+F1	F2	F3
+A	A	A
+AA	AA	AA
+AAA	AAA	AAA
+AAAA	AAAA	AAAA
+AAAAA	AAAAA	AAAAA
+SELECT * FROM t1 WHERE F3 BETWEEN 'A    ' AND 'AAAAA';
+F1	F2	F3
+A	A	A
+AA	AA	AA
+AAA	AAA	AAA
+AAAA	AAAA	AAAA
+AAAAA	AAAAA	AAAAA
+SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 BETWEEN 'A    ' AND
+'AAAAA';
+F1	F2	F3
+A	A	A
+AA	AA	AA
+AAA	AAA	AAA
+AAAA	AAAA	AAAA
+AAAAA	AAAAA	AAAAA
+DROP TABLE t1;
 End of 5.1 tests
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 6c9320b708a..30d15798336 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -1392,4 +1392,80 @@ SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk;
 
 DROP TABLE t1;
 
+--echo #
+--echo # BUG#13519696 - 62940: SELECT RESULTS VARY WITH VERSION AND
+--echo # WITH/WITHOUT INDEX RANGE SCAN
+--echo #
+
+create table t1 (id int unsigned not null auto_increment primary key);
+insert into t1 values (null);  
+insert into t1 select null from t1;  
+insert into t1 select null from t1;  
+insert into t1 select null from t1;  
+insert into t1 select null from t1;  
+insert into t1 select null from t1;  
+insert into t1 select null from t1;  
+insert into t1 select null from t1;  
+insert into t1 select null from t1; 
+create table t2 (
+ id int unsigned not null auto_increment,
+ val decimal(5,3) not null,
+ primary key (id,val),
+ unique key (val,id),
+ unique key (id));  
+--disable_warnings
+insert into t2 select null,id*0.0009 from t1;
+--enable_warnings
+
+select count(val) from t2 ignore index (val) where val > 0.1155;
+select count(val) from t2 force index (val)  where val > 0.1155;
+
+drop table t2, t1;
+
+--echo #
+--echo # BUG#13453382 - REGRESSION SINCE 5.1.39, RANGE OPTIMIZER WRONG
+--echo # RESULTS WITH DECIMAL CONVERSION
+--echo #
+
+create table t1 (a int,b int,c int,primary key (a,c));
+insert into t1 values (1,1,2),(1,1,3),(1,1,4);
+# show that the integer 3 is bigger than the decimal 2.9,
+# which should also apply to comparing "c" with 2.9
+# when c is 3.
+select convert(3, signed integer) > 2.9;
+select * from t1 force  index (primary) where a=1 and c>= 2.9;
+select * from t1 ignore index (primary) where a=1 and c>= 2.9;
+select * from t1 force  index (primary) where a=1 and c> 2.9;
+select * from t1 ignore index (primary) where a=1 and c> 2.9;
+drop table t1;
+
+--echo #
+--echo # BUG#13463488 - 63437: CHAR & BETWEEN WITH INDEX RETURNS WRONG
+--echo # RESULT AFTER MYSQL 5.1.
+--echo #
+
+CREATE TABLE t1(
+ F1 CHAR(5) NOT NULL,
+ F2 CHAR(5) NOT NULL,
+ F3 CHAR(5) NOT NULL,
+ PRIMARY KEY(F1),
+ INDEX IDX_F2(F2)
+);
+
+INSERT INTO t1 VALUES
+('A','A','A'),('AA','AA','AA'),('AAA','AAA','AAA'),
+('AAAA','AAAA','AAAA'),('AAAAA','AAAAA','AAAAA');
+
+SELECT * FROM t1 WHERE F1 = 'A    ';
+SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 = 'A    ';
+SELECT * FROM t1 WHERE F1 >= 'A    ';
+SELECT * FROM t1 WHERE F1 > 'A    ';
+SELECT * FROM t1 WHERE F1 BETWEEN 'A    ' AND 'AAAAA';
+SELECT * FROM t1 WHERE F2 BETWEEN 'A    ' AND 'AAAAA';
+SELECT * FROM t1 WHERE F3 BETWEEN 'A    ' AND 'AAAAA';
+SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 BETWEEN 'A    ' AND
+'AAAAA';
+
+DROP TABLE t1;
+
 --echo End of 5.1 tests