1# 2# Start of 10.5 tests 3# 4# 5# MDEV-20305 Data loss on DOUBLE and DECIMAL conversion to INT 6# 7CREATE PROCEDURE p1(type VARCHAR(64), val VARCHAR(64)) 8BEGIN 9EXECUTE IMMEDIATE CONCAT('CREATE TABLE t1 (a ', type, ')'); 10SHOW CREATE TABLE t1; 11EXECUTE IMMEDIATE CONCAT('INSERT INTO t1 VALUES (', val, ')'); 12SELECT 13a, 14~a, 15a & 18446744073709551615, 1618446744073709551615 & a, 170 | a, 18a | 0, 19a << 0, 20a >> 0, 21a ^ 1, 221 ^ a, 23BIT_COUNT(a) 24FROM t1; 25SHOW WARNINGS; 26DROP TABLE t1; 27END; 28$$ 29CALL p1('BIGINT UNSIGNED', 18446744073709551615); 30Table t1 31Create Table CREATE TABLE `t1` ( 32 `a` bigint(20) unsigned DEFAULT NULL 33) ENGINE=MyISAM DEFAULT CHARSET=latin1 34a 18446744073709551615 35~a 0 36a & 18446744073709551615 18446744073709551615 3718446744073709551615 & a 18446744073709551615 380 | a 18446744073709551615 39a | 0 18446744073709551615 40a << 0 18446744073709551615 41a >> 0 18446744073709551615 42a ^ 1 18446744073709551614 431 ^ a 18446744073709551614 44BIT_COUNT(a) 64 45CALL p1('DOUBLE', 18446744073709551615); 46Table t1 47Create Table CREATE TABLE `t1` ( 48 `a` double DEFAULT NULL 49) ENGINE=MyISAM DEFAULT CHARSET=latin1 50a 1.8446744073709552e19 51~a 0 52a & 18446744073709551615 18446744073709551615 5318446744073709551615 & a 18446744073709551615 540 | a 18446744073709551615 55a | 0 18446744073709551615 56a << 0 18446744073709551615 57a >> 0 18446744073709551615 58a ^ 1 18446744073709551614 591 ^ a 18446744073709551614 60BIT_COUNT(a) 64 61Level Warning 62Code 1916 63Message Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 64Level Warning 65Code 1916 66Message Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 67Level Warning 68Code 1916 69Message Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 70Level Warning 71Code 1916 72Message Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 73Level Warning 74Code 1916 75Message Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 76Level Warning 77Code 1916 78Message Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 79Level Warning 80Code 1916 81Message Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 82Level Warning 83Code 1916 84Message Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 85Level Warning 86Code 1916 87Message Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 88Level Warning 89Code 1916 90Message Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 91CALL p1('DECIMAL(30,0)', 18446744073709551615); 92Table t1 93Create Table CREATE TABLE `t1` ( 94 `a` decimal(30,0) DEFAULT NULL 95) ENGINE=MyISAM DEFAULT CHARSET=latin1 96a 18446744073709551615 97~a 0 98a & 18446744073709551615 18446744073709551615 9918446744073709551615 & a 18446744073709551615 1000 | a 18446744073709551615 101a | 0 18446744073709551615 102a << 0 18446744073709551615 103a >> 0 18446744073709551615 104a ^ 1 18446744073709551614 1051 ^ a 18446744073709551614 106BIT_COUNT(a) 64 107CALL p1('BIGINT', -1); 108Table t1 109Create Table CREATE TABLE `t1` ( 110 `a` bigint(20) DEFAULT NULL 111) ENGINE=MyISAM DEFAULT CHARSET=latin1 112a -1 113~a 0 114a & 18446744073709551615 18446744073709551615 11518446744073709551615 & a 18446744073709551615 1160 | a 18446744073709551615 117a | 0 18446744073709551615 118a << 0 18446744073709551615 119a >> 0 18446744073709551615 120a ^ 1 18446744073709551614 1211 ^ a 18446744073709551614 122BIT_COUNT(a) 64 123CALL p1('DOUBLE', -1); 124Table t1 125Create Table CREATE TABLE `t1` ( 126 `a` double DEFAULT NULL 127) ENGINE=MyISAM DEFAULT CHARSET=latin1 128a -1 129~a 0 130a & 18446744073709551615 18446744073709551615 13118446744073709551615 & a 18446744073709551615 1320 | a 18446744073709551615 133a | 0 18446744073709551615 134a << 0 18446744073709551615 135a >> 0 18446744073709551615 136a ^ 1 18446744073709551614 1371 ^ a 18446744073709551614 138BIT_COUNT(a) 64 139CALL p1('DECIMAL(30,0)', -1); 140Table t1 141Create Table CREATE TABLE `t1` ( 142 `a` decimal(30,0) DEFAULT NULL 143) ENGINE=MyISAM DEFAULT CHARSET=latin1 144a -1 145~a 0 146a & 18446744073709551615 18446744073709551615 14718446744073709551615 & a 18446744073709551615 1480 | a 18446744073709551615 149a | 0 18446744073709551615 150a << 0 18446744073709551615 151a >> 0 18446744073709551615 152a ^ 1 18446744073709551614 1531 ^ a 18446744073709551614 154BIT_COUNT(a) 64 155CALL p1('BIGINT', -9223372036854775808); 156Table t1 157Create Table CREATE TABLE `t1` ( 158 `a` bigint(20) DEFAULT NULL 159) ENGINE=MyISAM DEFAULT CHARSET=latin1 160a -9223372036854775808 161~a 9223372036854775807 162a & 18446744073709551615 9223372036854775808 16318446744073709551615 & a 9223372036854775808 1640 | a 9223372036854775808 165a | 0 9223372036854775808 166a << 0 9223372036854775808 167a >> 0 9223372036854775808 168a ^ 1 9223372036854775809 1691 ^ a 9223372036854775809 170BIT_COUNT(a) 1 171CALL p1('DOUBLE', -9223372036854775808); 172Table t1 173Create Table CREATE TABLE `t1` ( 174 `a` double DEFAULT NULL 175) ENGINE=MyISAM DEFAULT CHARSET=latin1 176a -9.223372036854776e18 177~a 9223372036854775807 178a & 18446744073709551615 9223372036854775808 17918446744073709551615 & a 9223372036854775808 1800 | a 9223372036854775808 181a | 0 9223372036854775808 182a << 0 9223372036854775808 183a >> 0 9223372036854775808 184a ^ 1 9223372036854775809 1851 ^ a 9223372036854775809 186BIT_COUNT(a) 1 187Level Warning 188Code 1916 189Message Got overflow when converting '-9223372036854776000' to INT. Value truncated 190Level Warning 191Code 1916 192Message Got overflow when converting '-9223372036854776000' to INT. Value truncated 193Level Warning 194Code 1916 195Message Got overflow when converting '-9223372036854776000' to INT. Value truncated 196Level Warning 197Code 1916 198Message Got overflow when converting '-9223372036854776000' to INT. Value truncated 199Level Warning 200Code 1916 201Message Got overflow when converting '-9223372036854776000' to INT. Value truncated 202Level Warning 203Code 1916 204Message Got overflow when converting '-9223372036854776000' to INT. Value truncated 205Level Warning 206Code 1916 207Message Got overflow when converting '-9223372036854776000' to INT. Value truncated 208Level Warning 209Code 1916 210Message Got overflow when converting '-9223372036854776000' to INT. Value truncated 211Level Warning 212Code 1916 213Message Got overflow when converting '-9223372036854776000' to INT. Value truncated 214Level Warning 215Code 1916 216Message Got overflow when converting '-9223372036854776000' to INT. Value truncated 217CALL p1('DECIMAL(30,0)', -9223372036854775808); 218Table t1 219Create Table CREATE TABLE `t1` ( 220 `a` decimal(30,0) DEFAULT NULL 221) ENGINE=MyISAM DEFAULT CHARSET=latin1 222a -9223372036854775808 223~a 9223372036854775807 224a & 18446744073709551615 9223372036854775808 22518446744073709551615 & a 9223372036854775808 2260 | a 9223372036854775808 227a | 0 9223372036854775808 228a << 0 9223372036854775808 229a >> 0 9223372036854775808 230a ^ 1 9223372036854775809 2311 ^ a 9223372036854775809 232BIT_COUNT(a) 1 233DROP PROCEDURE p1; 234SELECT CAST(CAST(18446744073709551615 AS UNSIGNED) AS DECIMAL(32))<<0 AS c1; 235c1 23618446744073709551615 237SELECT CAST(CAST(18446744073709551615 AS UNSIGNED) AS DOUBLE)<<0 AS c1; 238c1 23918446744073709551615 240Warnings: 241Warning 1916 Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 242SELECT COALESCE(CAST(CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED) AS DECIMAL(32))) << 0 AS c1; 243c1 24418446744073709551615 245SELECT COALESCE(CAST(CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED) AS DOUBLE)) << 0 AS c1; 246c1 24718446744073709551615 248Warnings: 249Warning 1916 Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 250SELECT 18446744073709551615 ^ 1 AS c1; 251c1 25218446744073709551614 253SELECT 18446744073709551615.0 ^ 1 AS c1; 254c1 25518446744073709551614 256SELECT 18446744073709551615e0 ^ 1 AS c1; 257c1 25818446744073709551614 259Warnings: 260Warning 1916 Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 261SELECT LAST_VALUE(18446744073709551615) ^ 1 AS c1; 262c1 26318446744073709551614 264SELECT LAST_VALUE(18446744073709551615.0) ^ 1 AS c1; 265c1 26618446744073709551614 267SELECT LAST_VALUE(18446744073709551615e0) ^ 1 AS c1; 268c1 26918446744073709551614 270Warnings: 271Warning 1916 Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 272SELECT 18446744073709551615 & 18446744073709551615 AS c1; 273c1 27418446744073709551615 275SELECT 18446744073709551615 & 18446744073709551615.0 AS c1; 276c1 27718446744073709551615 278SELECT 18446744073709551615 & 18446744073709551615e0 AS c1; 279c1 28018446744073709551615 281Warnings: 282Warning 1916 Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 283SELECT 18446744073709551615.0 & 18446744073709551615 AS c1; 284c1 28518446744073709551615 286SELECT 18446744073709551615.0 & 18446744073709551615.0 AS c1; 287c1 28818446744073709551615 289SELECT 18446744073709551615.0 & 18446744073709551615e0 AS c1; 290c1 29118446744073709551615 292Warnings: 293Warning 1916 Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 294SELECT 18446744073709551615e0 & 18446744073709551615 AS c1; 295c1 29618446744073709551615 297Warnings: 298Warning 1916 Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 299SELECT 18446744073709551615e0 & 18446744073709551615.0 AS c1; 300c1 30118446744073709551615 302Warnings: 303Warning 1916 Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 304SELECT 18446744073709551615e0 & 18446744073709551615e0 AS c1; 305c1 30618446744073709551615 307Warnings: 308Warning 1916 Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 309Warning 1916 Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 310SELECT 0 | 18446744073709551615 AS c1; 311c1 31218446744073709551615 313SELECT 0 | 18446744073709551615.0 AS c1; 314c1 31518446744073709551615 316SELECT 0 | 18446744073709551615e0 AS c1; 317c1 31818446744073709551615 319Warnings: 320Warning 1916 Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 321SELECT 18446744073709551615 | 0 AS c1; 322c1 32318446744073709551615 324SELECT 18446744073709551615.0 | 0 AS c1; 325c1 32618446744073709551615 327SELECT 18446744073709551615e0 | 0 AS c1; 328c1 32918446744073709551615 330Warnings: 331Warning 1916 Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 332SELECT ~18446744073709551615 AS c1; 333c1 3340 335SELECT ~18446744073709551615.0 AS c1; 336c1 3370 338SELECT ~18446744073709551615e0 AS c1; 339c1 3400 341Warnings: 342Warning 1916 Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 343SELECT BIT_COUNT(18446744073709551615) AS c1; 344c1 34564 346SELECT BIT_COUNT(18446744073709551615.0) AS c1; 347c1 34864 349SELECT BIT_COUNT(18446744073709551615e0) AS c1; 350c1 35164 352Warnings: 353Warning 1916 Got overflow when converting '18446744073709552000' to UNSIGNED INT. Value truncated 354SELECT BIT_COUNT(-9223372036854775808) AS c1; 355c1 3561 357SELECT BIT_COUNT(-9223372036854775808.0) AS c1; 358c1 3591 360SELECT BIT_COUNT(-9223372036854775808e0) AS c1; 361c1 3621 363Warnings: 364Warning 1916 Got overflow when converting '-9223372036854776000' to INT. Value truncated 365# 366# End of 10.5 tests 367# 368