Bug#8663 cant use bgint unsigned as input to cast

Problem: cast to unsigned limited result to 
max signed bigint 9223372036854775808,
instead of max unsigned bigint 18446744073709551615.

Fix: don't use args[0]->val_int() when casting from
a floating point number, use val() instead, with range checkings,
special to unsigned data type.

item_func.cc:
  Special handling of cast from REAL_RESULT
  to unsigned int: we cannot execute args[0]->val_int()
  because it cuts max allowed value to LONGLONG_INT,
  instead of ULONGLONG_INT required.
count_distinct3.test:
  Getting rid of "Data truncated; out of range ..." warnings.
cast.test, cast.result:
  Adding test case.
ps.result:
  Fixing that cast from 6570515219.6535 
  to unsigned didn't round to 6570515220,
  and returned 6570515219 instead.


mysql-test/r/cast.result:
  Adding test case.
mysql-test/r/ps.result:
  Fixing that cast from 6570515219.6535 
  to unsigned didn't round to 6570515220,
  and returned 6570515219 instead.
mysql-test/t/cast.test:
  Adding test case.
mysql-test/t/count_distinct3.test:
  Get rid of "wring unsigned value"
  warnings.
sql/item_func.cc:
  Special handling of cast from REAL)RESULT
  to unsigned int: we cannot execute args[0]->val_int()
  because it cuts max allowed value to LONGLONG_INT,
  instead of ULONGLONG_INT required.

5 files changed, 38 insertions, 7 deletions

diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index 68687670e17..101b9ac3f7e 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -264,6 +264,9 @@ cast(repeat('1',20) as signed)
 -7335632962598440505
 Warnings:
 Warning	1105	Cast to signed converted positive out-of-range integer to it's negative complement
+select cast(19999999999999999999 as unsigned);
+cast(19999999999999999999 as unsigned)
+18446744073709551615
 select cast(1.0e+300 as signed int);
 cast(1.0e+300 as signed int)
 9223372036854775807
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 2be5366b180..9a2e7bd262a 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -340,7 +340,7 @@ set @precision=10000000000;
 select rand(), 
 cast(rand(10)*@precision as unsigned integer) from t1;
 rand()	cast(rand(10)*@precision as unsigned integer)
--	6570515219
+-	6570515220
 -	1282061302
 -	6698761160
 -	9647622201
@@ -351,23 +351,23 @@ prepare stmt from
 set @var=1;
 execute stmt using @var;
 rand()	cast(rand(10)*@precision as unsigned integer)	cast(rand(?)*@precision as unsigned integer)
--	6570515219	-
+-	6570515220	-
 -	1282061302	-
 -	6698761160	-
 -	9647622201	-
 set @var=2;
 execute stmt using @var;
 rand()	cast(rand(10)*@precision as unsigned integer)	cast(rand(?)*@precision as unsigned integer)
--	6570515219	6555866465
--	1282061302	1223466192
--	6698761160	6449731873
+-	6570515220	6555866465
+-	1282061302	1223466193
+-	6698761160	6449731874
 -	9647622201	8578261098
 set @var=3;
 execute stmt using @var;
 rand()	cast(rand(10)*@precision as unsigned integer)	cast(rand(?)*@precision as unsigned integer)
--	6570515219	9057697559
+-	6570515220	9057697560
 -	1282061302	3730790581
--	6698761160	1480860534
+-	6698761160	1480860535
 -	9647622201	6211931236
 drop table t1;
 deallocate prepare stmt;
diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test
index 4d73783dd52..b214cef10fa 100644
--- a/mysql-test/t/cast.test
+++ b/mysql-test/t/cast.test
@@ -148,6 +148,12 @@ select cast(repeat('1',20) as unsigned);
 select cast(repeat('1',20) as signed);
 
 #
+# Bug#8663 cant use bgint unsigned as input to cast
+#
+select cast(19999999999999999999 as unsigned);
+
+
+#
 # Bug #13344: cast of large decimal to signed int not handled correctly
 #
 select cast(1.0e+300 as signed int);
diff --git a/mysql-test/t/count_distinct3.test b/mysql-test/t/count_distinct3.test
index 52a4f271dac..9c3e7f439c2 100644
--- a/mysql-test/t/count_distinct3.test
+++ b/mysql-test/t/count_distinct3.test
@@ -9,6 +9,7 @@ DROP TABLE IF EXISTS t1, t2;
 
 CREATE TABLE t1 (id INTEGER, grp TINYINT, id_rev INTEGER);
 
+--disable_warnings
 --disable_query_log
 SET @rnd_max= 2147483647;
 let $1 = 1000;
@@ -43,6 +44,7 @@ INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
 INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
 DROP TABLE t2;
 --enable_query_log
+--enable_warnings
 
 SELECT COUNT(*) FROM t1;
 
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 66300d129d4..2ceedaa51c6 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -508,6 +508,26 @@ longlong Item_func_unsigned::val_int()
   longlong value;
   int error;
 
+  if (args[0]->result_type() == REAL_RESULT)
+  {
+    double dvalue= args[0]->val();
+    if ((null_value= args[0]->null_value))
+      return 0;
+    if (dvalue <= (double) LONGLONG_MIN)
+    {
+      return LONGLONG_MIN;
+    }
+    if (dvalue >= (double) (ulonglong) ULONGLONG_MAX)
+    {
+      return (longlong) ULONGLONG_MAX;
+    }
+    if (dvalue >= (double) (ulonglong) LONGLONG_MAX)
+    {
+      return (ulonglong) (dvalue + (dvalue > 0 ? 0.5 : -0.5));
+    }
+    return (longlong)  (dvalue + (dvalue > 0 ? 0.5 : -0.5));
+  }
+
   if (args[0]->cast_to_int_type() != STRING_RESULT)
   {
     value= args[0]->val_int();