#
# 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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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
#