Fix for bug#57030: ('BETWEEN' evaluation is incorrect')

            
Root cause for this bug is that the optimizer try to detect&
optimize the special case:
      
'<field>  BETWEEN c1 AND c1' and handle this as the condition '<field>  = c1'
            
This was implemented inside add_key_field(.. *field, *value[]...)
which assumed field to refer key Field, and value[] to refer a [low...high]
constant pair. value[0] and value[1] was then compared for equality.
            
In a 'normal' BETWEEN condition of the form '<field>  BETWEEN val1 and val2' the
BETWEEN operation is represented with an argementlist containing the
values [<field>, val1, val2] - add_key_field() is then called with
parameters field=<field>, *value=val1.
            
However, if the BETWEEN predicate specified:
            
 1)  '<const1>  BETWEEN<const2>  AND<field>
            
the 'field' and 'value' arguments to add_key_field() had to be swapped.
This was implemented by trying to cheat add_key_field() to handle it like:
            
 2) '<const1>  GE<const2>  AND<const1>  LE<field>'
            
As we didn't really replace the BETWEEN operation with 'ge' and 'le',
add_key_field() still handled it as a 'BETWEEN' and compared the (swapped)
arguments<const1>  and<const2>  for equality. If they was equal, the
condition 1) was incorrectly 'optimized' to:
            
 3) '<field>  EQ <const1>'
            
This fix moves this optimization of '<field>  BETWEEN c1 AND c1' into
add_key_fields() which then calls add_key_equal_fields() to collect 
key equality / comparison for the key fields in the BETWEEN condition.

1 files changed, 101 insertions, 0 deletions

diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index d989896514c..ae63edf87b9 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -1666,4 +1666,105 @@ c_key	c_notkey
 1	1
 3	3
 DROP TABLE t1;
+#
+# Bug #57030: 'BETWEEN' evaluation is incorrect
+#
+CREATE TABLE t1(pk INT PRIMARY KEY, i4 INT);
+CREATE UNIQUE INDEX i4_uq ON t1(i4);
+INSERT INTO t1 VALUES (1,10), (2,20), (3,30);
+EXPLAIN
+SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	i4_uq	i4_uq	5	const	1	
+SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10;
+pk	i4
+1	10
+EXPLAIN
+SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	i4_uq	i4_uq	5	const	1	
+SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4;
+pk	i4
+1	10
+EXPLAIN
+SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	i4_uq	i4_uq	5	NULL	3	Using where
+SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4;
+pk	i4
+1	10
+2	20
+3	30
+EXPLAIN
+SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	i4_uq	i4_uq	5	NULL	1	Using where
+SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10;
+pk	i4
+1	10
+EXPLAIN
+SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10;
+pk	i4
+1	10
+2	20
+3	30
+EXPLAIN
+SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11;
+pk	i4
+EXPLAIN
+SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0;
+pk	i4
+EXPLAIN
+SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0;
+pk	i4
+EXPLAIN
+SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	i4_uq	i4_uq	5	NULL	2	Using where
+SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999;
+pk	i4
+1	10
+2	20
+3	30
+EXPLAIN
+SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30;
+pk	i4
+EXPLAIN
+SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	i4_uq	i4_uq	5	NULL	1	Using where
+SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20';
+pk	i4
+1	10
+2	20
+EXPLAIN
+SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	i4_uq	NULL	NULL	NULL	3	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.i4	1	Using where
+SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4;
+pk	i4	pk	i4
+EXPLAIN
+SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	i4_uq	NULL	NULL	NULL	3	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.i4	1	Using where
+SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk;
+pk	i4	pk	i4
+DROP TABLE t1;
 End of 5.1 tests