MDEV-20305 Data loss on DOUBLE and DECIMAL conversion to INT

Bit operators (~ ^ | & << >>) and the function BIT_COUNT()
always called val_int() for their arguments.
It worked correctly only for INT type arguments.

In case of DECIMAL and DOUBLE arguments it did not work well:
the argument values were truncated to the maximum SIGNED BIGINT value
of 9223372036854775807.

Fixing the code as follows:

- If the argument if of an integer data type,
  it works using val_int() as before.

- If the argument if of some other data type, it gets the argument value
  using val_decimal(), to avoid truncation, and then converts the result
  to ulonglong.

Using Item_handled_func to switch between the two approaches easier.

As an additional advantage, with Item_handled_func it will be easier
to implement overloading in the future, so data type plugings will be able
to define their own behavioir of bit operators and BIT_COUNT().

Moving the code from the former val_int() implementations
as methods to Longlong_null, to avoid code duplication in the
INT and DECIMAL branches.

1 files changed, 367 insertions, 0 deletions

diff --git a/mysql-test/main/func_bit.result b/mysql-test/main/func_bit.result
new file mode 100644
index 00000000000..4eb44f76ef3
--- /dev/null
+++ b/mysql-test/main/func_bit.result
@@ -0,0 +1,367 @@
+#
+# Start of 10.5 tests
+#
+#
+# MDEV-20305 Data loss on DOUBLE and DECIMAL conversion to INT
+#
+CREATE PROCEDURE p1(type VARCHAR(64), val VARCHAR(64))
+BEGIN
+EXECUTE IMMEDIATE CONCAT('CREATE TABLE t1 (a ', type, ')');
+SHOW CREATE TABLE t1;
+EXECUTE IMMEDIATE CONCAT('INSERT INTO t1 VALUES (', val, ')');
+SELECT
+a,
+~a,
+a & 18446744073709551615,
+18446744073709551615 & a,
+0 | a,
+a | 0,
+a << 0,
+a >> 0,
+a ^ 1,
+1 ^ a,
+BIT_COUNT(a)
+FROM t1;
+SHOW WARNINGS;
+DROP TABLE t1;
+END;
+$$
+CALL p1('BIGINT UNSIGNED', 18446744073709551615);
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `a` bigint(20) unsigned DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+a	18446744073709551615
+~a	0
+a & 18446744073709551615	18446744073709551615
+18446744073709551615 & a	18446744073709551615
+0 | a	18446744073709551615
+a | 0	18446744073709551615
+a << 0	18446744073709551615
+a >> 0	18446744073709551615
+a ^ 1	18446744073709551614
+1 ^ a	18446744073709551614
+BIT_COUNT(a)	64
+CALL p1('DOUBLE',          18446744073709551615);
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `a` double DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+a	1.8446744073709552e19
+~a	0
+a & 18446744073709551615	18446744073709551615
+18446744073709551615 & a	18446744073709551615
+0 | a	18446744073709551615
+a | 0	18446744073709551615
+a << 0	18446744073709551615
+a >> 0	18446744073709551615
+a ^ 1	18446744073709551614
+1 ^ a	18446744073709551614
+BIT_COUNT(a)	64
+Level	Warning
+Code	1916
+Message	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+CALL p1('DECIMAL(30,0)',   18446744073709551615);
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `a` decimal(30,0) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+a	18446744073709551615
+~a	0
+a & 18446744073709551615	18446744073709551615
+18446744073709551615 & a	18446744073709551615
+0 | a	18446744073709551615
+a | 0	18446744073709551615
+a << 0	18446744073709551615
+a >> 0	18446744073709551615
+a ^ 1	18446744073709551614
+1 ^ a	18446744073709551614
+BIT_COUNT(a)	64
+CALL p1('BIGINT',          -1);
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `a` bigint(20) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+a	-1
+~a	0
+a & 18446744073709551615	18446744073709551615
+18446744073709551615 & a	18446744073709551615
+0 | a	18446744073709551615
+a | 0	18446744073709551615
+a << 0	18446744073709551615
+a >> 0	18446744073709551615
+a ^ 1	18446744073709551614
+1 ^ a	18446744073709551614
+BIT_COUNT(a)	64
+CALL p1('DOUBLE',          -1);
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `a` double DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+a	-1
+~a	0
+a & 18446744073709551615	18446744073709551615
+18446744073709551615 & a	18446744073709551615
+0 | a	18446744073709551615
+a | 0	18446744073709551615
+a << 0	18446744073709551615
+a >> 0	18446744073709551615
+a ^ 1	18446744073709551614
+1 ^ a	18446744073709551614
+BIT_COUNT(a)	64
+CALL p1('DECIMAL(30,0)',   -1);
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `a` decimal(30,0) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+a	-1
+~a	0
+a & 18446744073709551615	18446744073709551615
+18446744073709551615 & a	18446744073709551615
+0 | a	18446744073709551615
+a | 0	18446744073709551615
+a << 0	18446744073709551615
+a >> 0	18446744073709551615
+a ^ 1	18446744073709551614
+1 ^ a	18446744073709551614
+BIT_COUNT(a)	64
+CALL p1('BIGINT',          -9223372036854775808);
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `a` bigint(20) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+a	-9223372036854775808
+~a	9223372036854775807
+a & 18446744073709551615	9223372036854775808
+18446744073709551615 & a	9223372036854775808
+0 | a	9223372036854775808
+a | 0	9223372036854775808
+a << 0	9223372036854775808
+a >> 0	9223372036854775808
+a ^ 1	9223372036854775809
+1 ^ a	9223372036854775809
+BIT_COUNT(a)	1
+CALL p1('DOUBLE',          -9223372036854775808);
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `a` double DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+a	-9.223372036854776e18
+~a	9223372036854775807
+a & 18446744073709551615	9223372036854775808
+18446744073709551615 & a	9223372036854775808
+0 | a	9223372036854775808
+a | 0	9223372036854775808
+a << 0	9223372036854775808
+a >> 0	9223372036854775808
+a ^ 1	9223372036854775809
+1 ^ a	9223372036854775809
+BIT_COUNT(a)	1
+Level	Warning
+Code	1916
+Message	Got overflow when converting '-9223372036854776000' to INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '-9223372036854776000' to INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '-9223372036854776000' to INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '-9223372036854776000' to INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '-9223372036854776000' to INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '-9223372036854776000' to INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '-9223372036854776000' to INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '-9223372036854776000' to INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '-9223372036854776000' to INT. Value truncated
+Level	Warning
+Code	1916
+Message	Got overflow when converting '-9223372036854776000' to INT. Value truncated
+CALL p1('DECIMAL(30,0)',   -9223372036854775808);
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `a` decimal(30,0) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+a	-9223372036854775808
+~a	9223372036854775807
+a & 18446744073709551615	9223372036854775808
+18446744073709551615 & a	9223372036854775808
+0 | a	9223372036854775808
+a | 0	9223372036854775808
+a << 0	9223372036854775808
+a >> 0	9223372036854775808
+a ^ 1	9223372036854775809
+1 ^ a	9223372036854775809
+BIT_COUNT(a)	1
+DROP PROCEDURE p1;
+SELECT CAST(CAST(18446744073709551615 AS UNSIGNED) AS DECIMAL(32))<<0 AS c1;
+c1
+18446744073709551615
+SELECT CAST(CAST(18446744073709551615 AS UNSIGNED) AS DOUBLE)<<0 AS c1;
+c1
+18446744073709551615
+Warnings:
+Warning	1916	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+SELECT COALESCE(CAST(CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED) AS DECIMAL(32))) << 0 AS c1;
+c1
+18446744073709551615
+SELECT COALESCE(CAST(CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED) AS DOUBLE)) << 0 AS c1;
+c1
+18446744073709551615
+Warnings:
+Warning	1916	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+SELECT 18446744073709551615 ^ 1 AS c1;
+c1
+18446744073709551614
+SELECT 18446744073709551615.0 ^ 1 AS c1;
+c1
+18446744073709551614
+SELECT 18446744073709551615e0 ^ 1 AS c1;
+c1
+18446744073709551614
+Warnings:
+Warning	1916	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+SELECT LAST_VALUE(18446744073709551615) ^ 1 AS c1;
+c1
+18446744073709551614
+SELECT LAST_VALUE(18446744073709551615.0) ^ 1 AS c1;
+c1
+18446744073709551614
+SELECT LAST_VALUE(18446744073709551615e0) ^ 1 AS c1;
+c1
+18446744073709551614
+Warnings:
+Warning	1916	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+SELECT 18446744073709551615 & 18446744073709551615 AS c1;
+c1
+18446744073709551615
+SELECT 18446744073709551615 & 18446744073709551615.0 AS c1;
+c1
+18446744073709551615
+SELECT 18446744073709551615 & 18446744073709551615e0 AS c1;
+c1
+18446744073709551615
+Warnings:
+Warning	1916	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+SELECT 18446744073709551615.0 & 18446744073709551615 AS c1;
+c1
+18446744073709551615
+SELECT 18446744073709551615.0 & 18446744073709551615.0 AS c1;
+c1
+18446744073709551615
+SELECT 18446744073709551615.0 & 18446744073709551615e0 AS c1;
+c1
+18446744073709551615
+Warnings:
+Warning	1916	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+SELECT 18446744073709551615e0 & 18446744073709551615 AS c1;
+c1
+18446744073709551615
+Warnings:
+Warning	1916	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+SELECT 18446744073709551615e0 & 18446744073709551615.0 AS c1;
+c1
+18446744073709551615
+Warnings:
+Warning	1916	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+SELECT 18446744073709551615e0 & 18446744073709551615e0 AS c1;
+c1
+18446744073709551615
+Warnings:
+Warning	1916	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+Warning	1916	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+SELECT 0 | 18446744073709551615 AS c1;
+c1
+18446744073709551615
+SELECT 0 | 18446744073709551615.0 AS c1;
+c1
+18446744073709551615
+SELECT 0 | 18446744073709551615e0 AS c1;
+c1
+18446744073709551615
+Warnings:
+Warning	1916	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+SELECT 18446744073709551615 | 0 AS c1;
+c1
+18446744073709551615
+SELECT 18446744073709551615.0 | 0 AS c1;
+c1
+18446744073709551615
+SELECT 18446744073709551615e0 | 0 AS c1;
+c1
+18446744073709551615
+Warnings:
+Warning	1916	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+SELECT ~18446744073709551615 AS c1;
+c1
+0
+SELECT ~18446744073709551615.0 AS c1;
+c1
+0
+SELECT ~18446744073709551615e0 AS c1;
+c1
+0
+Warnings:
+Warning	1916	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+SELECT BIT_COUNT(18446744073709551615) AS c1;
+c1
+64
+SELECT BIT_COUNT(18446744073709551615.0) AS c1;
+c1
+64
+SELECT BIT_COUNT(18446744073709551615e0) AS c1;
+c1
+64
+Warnings:
+Warning	1916	Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated
+SELECT BIT_COUNT(-9223372036854775808) AS c1;
+c1
+1
+SELECT BIT_COUNT(-9223372036854775808.0) AS c1;
+c1
+1
+SELECT BIT_COUNT(-9223372036854775808e0) AS c1;
+c1
+1
+Warnings:
+Warning	1916	Got overflow when converting '-9223372036854776000' to INT. Value truncated
+#
+# End of 10.5 tests
+#