Merge branch '5.5' into 10.0

8 files changed, 277 insertions, 87 deletions

diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result
index b06ec5805a0..760b9c7b205 100644
--- a/mysql-test/r/bigint.result
+++ b/mysql-test/r/bigint.result
@@ -508,3 +508,14 @@ DROP TABLE t1;
 SELECT 100 BETWEEN 1 AND 9223372036854775808;
 100 BETWEEN 1 AND 9223372036854775808
 1
+#
+# MDEV-17724 Wrong result for BETWEEN 0 AND 18446744073709551615
+#
+CREATE TABLE t1 (c1 bigint(20) unsigned NOT NULL);
+INSERT INTO t1 VALUES (0),(101),(255);
+SELECT * FROM t1 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1;
+c1
+0
+101
+255
+DROP TABLE t1;
diff --git a/mysql-test/suite/engines/iuds/r/insert_number.result b/mysql-test/suite/engines/iuds/r/insert_number.result
index ab56b82807c..9f937b33364 100644
--- a/mysql-test/suite/engines/iuds/r/insert_number.result
+++ b/mysql-test/suite/engines/iuds/r/insert_number.result
@@ -31565,8 +31565,33 @@ c1	c2	c3	c4	c5	c6	c7
 0	-9223372036854775808	1	2	3	4	5
 SELECT * FROM t2 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1,c6;
 c1	c2	c3	c4	c5	c6	c7
+0	NULL	5	6	NULL	0	NULL
+0	-9223372036854775808	1	2	3	4	5
+0	0	17	18	19	20	21
+0	124	22	23	24	25	26
+0	124	27	28	29	30	31
+0	-9223372036854775808	31	32	33	34	35
+0	0	32	32	34	35	36
+101	0	37	38	39	40	41
+101	-102	103	104	105	106	107
+102	-109	110	111	112	113	114
+103	-109	110	111	112	113	114
+105	NULL	102	103	104	105	106
+108	-109	110	111	112	101	114
+108	-109	110	111	112	102	114
+108	-109	110	111	112	113	114
+115	-116	117	118	119	120	121
+122	-123	124	125	126	127	128
+255	-2147483648	6	7	8	9	10
+65535	-8388608	11	12	13	14	15
+16777215	-32768	16	17	18	19	20
+4294967295	-128	21	22	23	24	25
+18446744073709551615	9223372036854775807	26	27	28	29	30
+18446744073709551615	9223372036854775807	36	37	38	39	40
 SELECT * FROM t2 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1,c6 LIMIT 2;
 c1	c2	c3	c4	c5	c6	c7
+0	NULL	5	6	NULL	0	NULL
+0	-9223372036854775808	1	2	3	4	5
 SELECT * FROM t2 WHERE c1 IN (0,18446744073709551615) ORDER BY c1,c6;
 c1	c2	c3	c4	c5	c6	c7
 0	NULL	5	6	NULL	0	NULL
@@ -31726,8 +31751,33 @@ c1	c2	c3	c4	c5	c6	c7
 0	-9223372036854775808	31	32	33	34	35
 SELECT * FROM t2 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1,c6 DESC;
 c1	c2	c3	c4	c5	c6	c7
+0	0	32	32	34	35	36
+0	-9223372036854775808	31	32	33	34	35
+0	124	27	28	29	30	31
+0	124	22	23	24	25	26
+0	0	17	18	19	20	21
+0	-9223372036854775808	1	2	3	4	5
+0	NULL	5	6	NULL	0	NULL
+101	-102	103	104	105	106	107
+101	0	37	38	39	40	41
+102	-109	110	111	112	113	114
+103	-109	110	111	112	113	114
+105	NULL	102	103	104	105	106
+108	-109	110	111	112	113	114
+108	-109	110	111	112	102	114
+108	-109	110	111	112	101	114
+115	-116	117	118	119	120	121
+122	-123	124	125	126	127	128
+255	-2147483648	6	7	8	9	10
+65535	-8388608	11	12	13	14	15
+16777215	-32768	16	17	18	19	20
+4294967295	-128	21	22	23	24	25
+18446744073709551615	9223372036854775807	36	37	38	39	40
+18446744073709551615	9223372036854775807	26	27	28	29	30
 SELECT * FROM t2 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1,c6 DESC LIMIT 2;
 c1	c2	c3	c4	c5	c6	c7
+0	0	32	32	34	35	36
+0	-9223372036854775808	31	32	33	34	35
 SELECT * FROM t2 WHERE c1 IN (0,18446744073709551615) ORDER BY c1,c6 DESC;
 c1	c2	c3	c4	c5	c6	c7
 0	0	32	32	34	35	36
@@ -31993,8 +32043,33 @@ c1	c2	c3	c4	c5	c6	c7
 18446744073709551615	9223372036854775807	36	37	38	39	40
 SELECT * FROM t2 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1,c6;
 c1	c2	c3	c4	c5	c6	c7
+0	NULL	5	6	NULL	0	NULL
+0	-9223372036854775808	1	2	3	4	5
+0	0	17	18	19	20	21
+0	124	22	23	24	25	26
+0	124	27	28	29	30	31
+0	-9223372036854775808	31	32	33	34	35
+0	0	32	32	34	35	36
+101	0	37	38	39	40	41
+101	-102	103	104	105	106	107
+102	-109	110	111	112	113	114
+103	-109	110	111	112	113	114
+105	NULL	102	103	104	105	106
+108	-109	110	111	112	101	114
+108	-109	110	111	112	102	114
+108	-109	110	111	112	113	114
+115	-116	117	118	119	120	121
+122	-123	124	125	126	127	128
+255	-2147483648	6	7	8	9	10
+65535	-8388608	11	12	13	14	15
+16777215	-32768	16	17	18	19	20
+4294967295	-128	21	22	23	24	25
+18446744073709551615	9223372036854775807	26	27	28	29	30
+18446744073709551615	9223372036854775807	36	37	38	39	40
 SELECT * FROM t2 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1,c6 LIMIT 2;
 c1	c2	c3	c4	c5	c6	c7
+0	NULL	5	6	NULL	0	NULL
+0	-9223372036854775808	1	2	3	4	5
 SELECT * FROM t2 WHERE c1 IN (0,18446744073709551615) ORDER BY c1,c6;
 c1	c2	c3	c4	c5	c6	c7
 0	NULL	5	6	NULL	0	NULL
@@ -32154,8 +32229,33 @@ c1	c2	c3	c4	c5	c6	c7
 18446744073709551615	9223372036854775807	26	27	28	29	30
 SELECT * FROM t2 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1,c6 DESC;
 c1	c2	c3	c4	c5	c6	c7
+0	0	32	32	34	35	36
+0	-9223372036854775808	31	32	33	34	35
+0	124	27	28	29	30	31
+0	124	22	23	24	25	26
+0	0	17	18	19	20	21
+0	-9223372036854775808	1	2	3	4	5
+0	NULL	5	6	NULL	0	NULL
+101	-102	103	104	105	106	107
+101	0	37	38	39	40	41
+102	-109	110	111	112	113	114
+103	-109	110	111	112	113	114
+105	NULL	102	103	104	105	106
+108	-109	110	111	112	113	114
+108	-109	110	111	112	102	114
+108	-109	110	111	112	101	114
+115	-116	117	118	119	120	121
+122	-123	124	125	126	127	128
+255	-2147483648	6	7	8	9	10
+65535	-8388608	11	12	13	14	15
+16777215	-32768	16	17	18	19	20
+4294967295	-128	21	22	23	24	25
+18446744073709551615	9223372036854775807	36	37	38	39	40
+18446744073709551615	9223372036854775807	26	27	28	29	30
 SELECT * FROM t2 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1,c6 DESC LIMIT 2;
 c1	c2	c3	c4	c5	c6	c7
+0	0	32	32	34	35	36
+0	-9223372036854775808	31	32	33	34	35
 SELECT * FROM t2 WHERE c1 IN (0,18446744073709551615) ORDER BY c1,c6 DESC;
 c1	c2	c3	c4	c5	c6	c7
 0	0	32	32	34	35	36
diff --git a/mysql-test/suite/engines/iuds/r/update_delete_number.result b/mysql-test/suite/engines/iuds/r/update_delete_number.result
index 15de16ed714..b89dea75d96 100644
--- a/mysql-test/suite/engines/iuds/r/update_delete_number.result
+++ b/mysql-test/suite/engines/iuds/r/update_delete_number.result
@@ -3768,8 +3768,25 @@ c1	c2	c3
 -12	18446744073709551615	12
 SELECT * FROM t2 WHERE c2 BETWEEN 0 AND 18446744073709551615 ORDER BY c2,c1;
 c1	c2	c3
+-4	4	4
+-9	9	9
+0	255	13
+-9223372036854775808	18446744073709551615	12
+-12	18446744073709551615	12
+-11	18446744073709551615	11
+-8	18446744073709551615	8
+-7	18446744073709551615	7
+-6	18446744073709551615	6
+-5	18446744073709551615	5
+-3	18446744073709551615	3
+-2	18446744073709551615	2
+-1	18446744073709551615	1
+50	18446744073709551615	10
+9223372036854775807	18446744073709551615	14
 SELECT * FROM t2 WHERE c2 BETWEEN 0 AND 18446744073709551615 ORDER BY c2,c1 DESC LIMIT 2;
 c1	c2	c3
+-4	4	4
+-9	9	9
 SELECT * FROM t2 WHERE c2 IN(0,18446744073709551615) ORDER BY c2,c1 DESC;
 c1	c2	c3
 9223372036854775807	18446744073709551615	14
diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test
index fb18d60edd9..71fef813a8d 100644
--- a/mysql-test/t/bigint.test
+++ b/mysql-test/t/bigint.test
@@ -414,3 +414,12 @@ DROP TABLE t1;
 --echo # MDEV-9372 select 100 between 1 and 9223372036854775808 returns false
 --echo #
 SELECT 100 BETWEEN 1 AND 9223372036854775808;
+
+--echo #
+--echo # MDEV-17724 Wrong result for BETWEEN 0 AND 18446744073709551615
+--echo #
+
+CREATE TABLE t1 (c1 bigint(20) unsigned NOT NULL);
+INSERT INTO t1 VALUES (0),(101),(255);
+SELECT * FROM t1 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1;
+DROP TABLE t1;
diff --git a/sql/item.h b/sql/item.h
index 5bcdc45ce23..11efac08757 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -825,6 +825,10 @@ public:
       If value is not null null_value flag will be reset to FALSE.
   */
   virtual longlong val_int()=0;
+  Longlong_hybrid to_longlong_hybrid()
+  {
+    return Longlong_hybrid(val_int(), unsigned_flag);
+  }
   /*
     This is just a shortcut to avoid the cast. You should still use
     unsigned_flag to check the sign of the item.
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index bdef3c1a89f..a0c5798cb69 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -159,7 +159,10 @@ static int cmp_row_type(Item* item1, Item* item2)
     0  otherwise
 */
 
-static int agg_cmp_type(Item_result *type, Item **items, uint nitems)
+static int agg_cmp_type(Item_result *type,
+                        Item **items,
+                        uint nitems,
+                        bool int_uint_as_dec)
 {
   uint unsigned_count= items[0]->unsigned_flag;
   type[0]= items[0]->cmp_type();
@@ -181,7 +184,9 @@ static int agg_cmp_type(Item_result *type, Item **items, uint nitems)
     If all arguments are of INT type but have different unsigned_flag values,
     switch to DECIMAL_RESULT.
   */
-  if (type[0] == INT_RESULT && unsigned_count != nitems && unsigned_count != 0)
+  if (int_uint_as_dec &&
+      type[0] == INT_RESULT &&
+      unsigned_count != nitems && unsigned_count != 0)
     type[0]= DECIMAL_RESULT;
   return 0;
 }
@@ -2359,7 +2364,7 @@ void Item_func_between::fix_length_and_dec()
   */
   if (!args[0] || !args[1] || !args[2])
     return;
-  if ( agg_cmp_type(&cmp_type, args, 3))
+  if (agg_cmp_type(&cmp_type, args, 3, false))
     return;
   if (cmp_type == STRING_RESULT &&
       agg_arg_charsets_for_comparison(cmp_collation, args, 3))
@@ -2393,6 +2398,97 @@ void Item_func_between::fix_length_and_dec()
 }
 
 
+longlong Item_func_between::val_int_cmp_string()
+{
+  String *value,*a,*b;
+  value=args[0]->val_str(&value0);
+  if ((null_value=args[0]->null_value))
+    return 0;
+  a= args[1]->val_str(&value1);
+  b= args[2]->val_str(&value2);
+  if (!args[1]->null_value && !args[2]->null_value)
+    return (longlong) ((sortcmp(value,a,cmp_collation.collation) >= 0 &&
+                        sortcmp(value,b,cmp_collation.collation) <= 0) !=
+                       negated);
+  if (args[1]->null_value && args[2]->null_value)
+    null_value= true;
+  else if (args[1]->null_value)
+  {
+    // Set to not null if false range.
+    null_value= sortcmp(value,b,cmp_collation.collation) <= 0;
+  }
+  else
+  {
+    // Set to not null if false range.
+    null_value= sortcmp(value,a,cmp_collation.collation) >= 0;
+  }
+  return (longlong) (!null_value && negated);
+}
+
+
+longlong Item_func_between::val_int_cmp_int()
+{
+  Longlong_hybrid value= args[0]->to_longlong_hybrid();
+  if ((null_value= args[0]->null_value))
+    return 0;					/* purecov: inspected */
+  Longlong_hybrid a= args[1]->to_longlong_hybrid();
+  Longlong_hybrid b= args[2]->to_longlong_hybrid();
+  if (!args[1]->null_value && !args[2]->null_value)
+    return (longlong) ((value.cmp(a) >= 0 && value.cmp(b) <= 0) != negated);
+  if (args[1]->null_value && args[2]->null_value)
+    null_value= true;
+  else if (args[1]->null_value)
+    null_value= value.cmp(b) <= 0;              // not null if false range.
+  else
+    null_value= value.cmp(a) >= 0;
+  return (longlong) (!null_value && negated);
+}
+
+
+longlong Item_func_between::val_int_cmp_decimal()
+{
+  my_decimal dec_buf, *dec= args[0]->val_decimal(&dec_buf),
+             a_buf, *a_dec, b_buf, *b_dec;
+  if ((null_value=args[0]->null_value))
+    return 0;					/* purecov: inspected */
+  a_dec= args[1]->val_decimal(&a_buf);
+  b_dec= args[2]->val_decimal(&b_buf);
+  if (!args[1]->null_value && !args[2]->null_value)
+    return (longlong) ((my_decimal_cmp(dec, a_dec) >= 0 &&
+                        my_decimal_cmp(dec, b_dec) <= 0) != negated);
+  if (args[1]->null_value && args[2]->null_value)
+    null_value= true;
+  else if (args[1]->null_value)
+    null_value= (my_decimal_cmp(dec, b_dec) <= 0);
+  else
+    null_value= (my_decimal_cmp(dec, a_dec) >= 0);
+  return (longlong) (!null_value && negated);
+}
+
+
+longlong Item_func_between::val_int_cmp_real()
+{
+  double value= args[0]->val_real(),a,b;
+  if ((null_value=args[0]->null_value))
+    return 0;					/* purecov: inspected */
+  a= args[1]->val_real();
+  b= args[2]->val_real();
+  if (!args[1]->null_value && !args[2]->null_value)
+    return (longlong) ((value >= a && value <= b) != negated);
+  if (args[1]->null_value && args[2]->null_value)
+    null_value= true;
+  else if (args[1]->null_value)
+  {
+    null_value= value <= b;			// not null if false range.
+  }
+  else
+  {
+    null_value= value >= a;
+  }
+  return (longlong) (!null_value && negated);
+}
+
+
 longlong Item_func_between::val_int()
 {
   DBUG_ASSERT(fixed == 1);
@@ -2434,94 +2530,14 @@ longlong Item_func_between::val_int()
       null_value= value >= a;
     break;
   }
-
   case STRING_RESULT:
-  {
-    String *value,*a,*b;
-    value=args[0]->val_str(&value0);
-    if ((null_value=args[0]->null_value))
-      return 0;
-    a=args[1]->val_str(&value1);
-    b=args[2]->val_str(&value2);
-    if (!args[1]->null_value && !args[2]->null_value)
-      return (longlong) ((sortcmp(value,a,cmp_collation.collation) >= 0 &&
-                          sortcmp(value,b,cmp_collation.collation) <= 0) !=
-                         negated);
-    if (args[1]->null_value && args[2]->null_value)
-      null_value=1;
-    else if (args[1]->null_value)
-    {
-      // Set to not null if false range.
-      null_value= sortcmp(value,b,cmp_collation.collation) <= 0;
-    }
-    else
-    {
-      // Set to not null if false range.
-      null_value= sortcmp(value,a,cmp_collation.collation) >= 0;
-    }
-    break;
-  }
+    return val_int_cmp_string();
   case INT_RESULT:
-  {
-    longlong value=args[0]->val_int(), a, b;
-    if ((null_value=args[0]->null_value))
-      return 0;					/* purecov: inspected */
-    a=args[1]->val_int();
-    b=args[2]->val_int();
-    if (!args[1]->null_value && !args[2]->null_value)
-      return (longlong) ((value >= a && value <= b) != negated);
-    if (args[1]->null_value && args[2]->null_value)
-      null_value=1;
-    else if (args[1]->null_value)
-    {
-      null_value= value <= b;			// not null if false range.
-    }
-    else
-    {
-      null_value= value >= a;
-    }
-    break;
-  }
+    return val_int_cmp_int();
   case DECIMAL_RESULT:
-  {
-    my_decimal dec_buf, *dec= args[0]->val_decimal(&dec_buf),
-               a_buf, *a_dec, b_buf, *b_dec;
-    if ((null_value=args[0]->null_value))
-      return 0;					/* purecov: inspected */
-    a_dec= args[1]->val_decimal(&a_buf);
-    b_dec= args[2]->val_decimal(&b_buf);
-    if (!args[1]->null_value && !args[2]->null_value)
-      return (longlong) ((my_decimal_cmp(dec, a_dec) >= 0 &&
-                          my_decimal_cmp(dec, b_dec) <= 0) != negated);
-    if (args[1]->null_value && args[2]->null_value)
-      null_value=1;
-    else if (args[1]->null_value)
-      null_value= (my_decimal_cmp(dec, b_dec) <= 0);
-    else
-      null_value= (my_decimal_cmp(dec, a_dec) >= 0);
-    break;
-  }
+    return val_int_cmp_decimal();
   case REAL_RESULT:
-  {
-    double value= args[0]->val_real(),a,b;
-    if ((null_value=args[0]->null_value))
-      return 0;					/* purecov: inspected */
-    a= args[1]->val_real();
-    b= args[2]->val_real();
-    if (!args[1]->null_value && !args[2]->null_value)
-      return (longlong) ((value >= a && value <= b) != negated);
-    if (args[1]->null_value && args[2]->null_value)
-      null_value=1;
-    else if (args[1]->null_value)
-    {
-      null_value= value <= b;			// not null if false range.
-    }
-    else
-    {
-      null_value= value >= a;
-    }
-    break;
-  }
+    return val_int_cmp_real();
   case ROW_RESULT:
   case IMPOSSIBLE_RESULT:
     DBUG_ASSERT(0);
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index fdefcc86c64..eec3684c2c1 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -698,6 +698,11 @@ public:
   bool eval_not_null_tables(uchar *opt_arg);
   void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
   bool count_sargable_conds(uchar *arg);
+
+  longlong val_int_cmp_string();
+  longlong val_int_cmp_int();
+  longlong val_int_cmp_real();
+  longlong val_int_cmp_decimal();
 };
 
 
diff --git a/sql/sql_type_int.h b/sql/sql_type_int.h
index 1eda5651df5..7433bd5249f 100644
--- a/sql/sql_type_int.h
+++ b/sql/sql_type_int.h
@@ -24,12 +24,25 @@ class Longlong_hybrid
 protected:
   longlong m_value;
   bool m_unsigned;
+  int cmp_signed(const Longlong_hybrid& other) const
+  {
+    return m_value < other.m_value ? -1 : m_value == other.m_value ? 0 : 1;
+  }
+  int cmp_unsigned(const Longlong_hybrid& other) const
+  {
+    return (ulonglong) m_value < (ulonglong) other.m_value ? -1 :
+            m_value == other.m_value ? 0 : 1;
+  }
 public:
   Longlong_hybrid(longlong nr, bool unsigned_flag)
    :m_value(nr), m_unsigned(unsigned_flag)
   { }
   longlong value() const { return m_value; }
   bool is_unsigned() const { return m_unsigned; }
+  bool is_unsigned_outside_of_signed_range() const
+  {
+    return m_unsigned && ((ulonglong) m_value) > (ulonglong) LONGLONG_MAX;
+  }
   bool neg() const { return m_value < 0 && !m_unsigned; }
   ulonglong abs() const
   {
@@ -39,6 +52,21 @@ public:
       return ((ulonglong) LONGLONG_MAX) + 1;
     return m_value < 0 ? -m_value : m_value;
   }
+  int cmp(const Longlong_hybrid& other) const
+  {
+    if (m_unsigned == other.m_unsigned)
+      return m_unsigned ? cmp_unsigned(other) : cmp_signed(other);
+    if (is_unsigned_outside_of_signed_range())
+      return 1;
+    if (other.is_unsigned_outside_of_signed_range())
+      return -1;
+    /*
+      The unsigned argument is in the range 0..LONGLONG_MAX.
+      The signed argument is in the range LONGLONG_MIN..LONGLONG_MAX.
+      Safe to compare as signed.
+    */
+    return cmp_signed(other);
+  }
 };
 
 #endif // SQL_TYPE_INT_INCLUDED