1drop table if exists t1, t2; 2select 0,256,00000000000000065536,2147483647,-2147483648,2147483648,+4294967296; 30 256 00000000000000065536 2147483647 -2147483648 2147483648 4294967296 40 256 65536 2147483647 -2147483648 2147483648 4294967296 5select 9223372036854775807,-009223372036854775808; 69223372036854775807 -009223372036854775808 79223372036854775807 -9223372036854775808 8select +9999999999999999999,-9999999999999999999; 99999999999999999999 -9999999999999999999 109999999999999999999 -9999999999999999999 11select cast(9223372036854775808 as unsigned)+1; 12cast(9223372036854775808 as unsigned)+1 139223372036854775809 14select 9223372036854775808+1; 159223372036854775808+1 169223372036854775809 17select -(0-3),round(-(0-3)), round(9999999999999999999); 18-(0-3) round(-(0-3)) round(9999999999999999999) 193 3 9999999999999999999 20select 1,11,101,1001,10001,100001,1000001,10000001,100000001,1000000001,10000000001,100000000001,1000000000001,10000000000001,100000000000001,1000000000000001,10000000000000001,100000000000000001,1000000000000000001,10000000000000000001; 211 11 101 1001 10001 100001 1000001 10000001 100000001 1000000001 10000000001 100000000001 1000000000001 10000000000001 100000000000001 1000000000000001 10000000000000001 100000000000000001 1000000000000000001 10000000000000000001 221 11 101 1001 10001 100001 1000001 10000001 100000001 1000000001 10000000001 100000000001 1000000000001 10000000000001 100000000000001 1000000000000001 10000000000000001 100000000000000001 1000000000000000001 10000000000000000001 23select -1,-11,-101,-1001,-10001,-100001,-1000001,-10000001,-100000001,-1000000001,-10000000001,-100000000001,-1000000000001,-10000000000001,-100000000000001,-1000000000000001,-10000000000000001,-100000000000000001,-1000000000000000001,-10000000000000000001; 24-1 -11 -101 -1001 -10001 -100001 -1000001 -10000001 -100000001 -1000000001 -10000000001 -100000000001 -1000000000001 -10000000000001 -100000000000001 -1000000000000001 -10000000000000001 -100000000000000001 -1000000000000000001 -10000000000000000001 25-1 -11 -101 -1001 -10001 -100001 -1000001 -10000001 -100000001 -1000000001 -10000000001 -100000000001 -1000000000001 -10000000000001 -100000000000001 -1000000000000001 -10000000000000001 -100000000000000001 -1000000000000000001 -10000000000000000001 26select conv(1,10,16),conv((1<<2)-1,10,16),conv((1<<10)-2,10,16),conv((1<<16)-3,10,16),conv((1<<25)-4,10,16),conv((1<<31)-5,10,16),conv((1<<36)-6,10,16),conv((1<<47)-7,10,16),conv((1<<48)-8,10,16),conv((1<<55)-9,10,16),conv((1<<56)-10,10,16),conv((1<<63)-11,10,16); 27conv(1,10,16) conv((1<<2)-1,10,16) conv((1<<10)-2,10,16) conv((1<<16)-3,10,16) conv((1<<25)-4,10,16) conv((1<<31)-5,10,16) conv((1<<36)-6,10,16) conv((1<<47)-7,10,16) conv((1<<48)-8,10,16) conv((1<<55)-9,10,16) conv((1<<56)-10,10,16) conv((1<<63)-11,10,16) 281 3 3FE FFFD 1FFFFFC 7FFFFFFB FFFFFFFFA 7FFFFFFFFFF9 FFFFFFFFFFF8 7FFFFFFFFFFFF7 FFFFFFFFFFFFF6 7FFFFFFFFFFFFFF5 29create table t1 (a bigint unsigned not null, primary key(a)); 30insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612); 31select * from t1; 32a 3318446744073709551612 3418446744073709551613 3518446744073709551614 3618446744073709551615 37select * from t1 where a=18446744073709551615; 38a 3918446744073709551615 40delete from t1 where a=18446744073709551615; 41select * from t1; 42a 4318446744073709551612 4418446744073709551613 4518446744073709551614 46drop table t1; 47create table t1 ( a int not null default 1, big bigint ); 48insert into t1 (big) values (-1),(12345678901234567),(9223372036854775807),(18446744073709551615); 49Warnings: 50Warning 1264 Out of range value for column 'big' at row 4 51select * from t1; 52a big 531 -1 541 12345678901234567 551 9223372036854775807 561 9223372036854775807 57select min(big),max(big),max(big)-1 from t1; 58min(big) max(big) max(big)-1 59-1 9223372036854775807 9223372036854775806 60select min(big),max(big),max(big)-1 from t1 group by a; 61min(big) max(big) max(big)-1 62-1 9223372036854775807 9223372036854775806 63SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 64alter table t1 modify big bigint unsigned not null; 65Warnings: 66Warning 1264 Out of range value for column 'big' at row 1 67select min(big),max(big),max(big)-1 from t1; 68min(big) max(big) max(big)-1 690 9223372036854775807 9223372036854775806 70select min(big),max(big),max(big)-1 from t1 group by a; 71min(big) max(big) max(big)-1 720 9223372036854775807 9223372036854775806 73insert into t1 (big) values (18446744073709551615); 74select * from t1; 75a big 761 0 771 12345678901234567 781 9223372036854775807 791 9223372036854775807 801 18446744073709551615 81select min(big),max(big),max(big)-1 from t1; 82min(big) max(big) max(big)-1 830 18446744073709551615 18446744073709551614 84select min(big),max(big),max(big)-1 from t1 group by a; 85min(big) max(big) max(big)-1 860 18446744073709551615 18446744073709551614 87alter table t1 add key (big); 88select min(big),max(big),max(big)-1 from t1; 89min(big) max(big) max(big)-1 900 18446744073709551615 18446744073709551614 91select min(big),max(big),max(big)-1 from t1 group by a; 92min(big) max(big) max(big)-1 930 18446744073709551615 18446744073709551614 94SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 95alter table t1 modify big bigint not null; 96Warnings: 97Warning 1264 Out of range value for column 'big' at row 5 98select * from t1; 99a big 1001 0 1011 12345678901234567 1021 9223372036854775807 1031 9223372036854775807 1041 9223372036854775807 105select min(big),max(big),max(big)-1 from t1; 106min(big) max(big) max(big)-1 1070 9223372036854775807 9223372036854775806 108select min(big),max(big),max(big)-1 from t1 group by a; 109min(big) max(big) max(big)-1 1100 9223372036854775807 9223372036854775806 111drop table t1; 112create table t1 (id bigint auto_increment primary key, a int) auto_increment=9999999999; 113insert into t1 values (null,1); 114select * from t1; 115id a 1169999999999 1 117select * from t1 limit 9999999999; 118id a 1199999999999 1 120drop table t1; 121CREATE TABLE t1 ( quantity decimal(60,0)); 122insert into t1 values (10000000000000000000); 123insert into t1 values (10000000000000000000.0); 124insert into t1 values ('10000000000000000000'); 125select * from t1; 126quantity 12710000000000000000000 12810000000000000000000 12910000000000000000000 130drop table t1; 131SELECT '0x8000000000000001'+0; 132'0x8000000000000001'+0 1330 134Warnings: 135Warning 1292 Truncated incorrect DOUBLE value: '0x8000000000000001' 136create table t1 ( 137value64 bigint unsigned not null, 138value32 integer not null, 139primary key(value64, value32) 140); 141create table t2 ( 142value64 bigint unsigned not null, 143value32 integer not null, 144primary key(value64, value32) 145); 146insert into t1 values(17156792991891826145, 1); 147insert into t1 values( 9223372036854775807, 2); 148insert into t2 values(17156792991891826145, 3); 149insert into t2 values( 9223372036854775807, 4); 150select * from t1; 151value64 value32 1529223372036854775807 2 15317156792991891826145 1 154select * from t2; 155value64 value32 1569223372036854775807 4 15717156792991891826145 3 158select * from t1, t2 where t1.value64=17156792991891826145 and 159t2.value64=17156792991891826145; 160value64 value32 value64 value32 16117156792991891826145 1 17156792991891826145 3 162select * from t1, t2 where t1.value64=17156792991891826145 and 163t2.value64=t1.value64; 164value64 value32 value64 value32 16517156792991891826145 1 17156792991891826145 3 166select * from t1, t2 where t1.value64= 9223372036854775807 and 167t2.value64=9223372036854775807; 168value64 value32 value64 value32 1699223372036854775807 2 9223372036854775807 4 170select * from t1, t2 where t1.value64= 9223372036854775807 and 171t2.value64=t1.value64; 172value64 value32 value64 value32 1739223372036854775807 2 9223372036854775807 4 174drop table t1, t2; 175create table t1 (sint64 bigint not null); 176insert into t1 values (-9223372036854775808); 177select * from t1; 178sint64 179-9223372036854775808 180drop table t1; 181create table t1 select 1 as 'a'; 182show create table t1; 183Table Create Table 184t1 CREATE TABLE `t1` ( 185 `a` int(1) NOT NULL 186) ENGINE=MyISAM DEFAULT CHARSET=latin1 187drop table t1; 188create table t1 select 9223372036854775809 as 'a'; 189show create table t1; 190Table Create Table 191t1 CREATE TABLE `t1` ( 192 `a` bigint(19) unsigned NOT NULL 193) ENGINE=MyISAM DEFAULT CHARSET=latin1 194select * from t1; 195a 1969223372036854775809 197drop table t1; 198DROP DATABASE IF EXISTS `scott`; 199Warnings: 200Note 1008 Can't drop database 'scott'; database doesn't exist 201create table t1 (a char(100), b varchar(100), c text, d blob); 202insert into t1 values( 20318446744073709551615,18446744073709551615, 20418446744073709551615, 18446744073709551615 205); 206insert into t1 values (-1 | 0,-1 | 0,-1 | 0 ,-1 | 0); 207select * from t1; 208a b c d 20918446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615 21018446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615 211drop table t1; 212create table t1 ( quantity decimal(2) unsigned); 213insert ignore into t1 values (500), (-500), (~0), (-1); 214Warnings: 215Warning 1264 Out of range value for column 'quantity' at row 1 216Warning 1264 Out of range value for column 'quantity' at row 2 217Warning 1264 Out of range value for column 'quantity' at row 3 218Warning 1264 Out of range value for column 'quantity' at row 4 219select * from t1; 220quantity 22199 2220 22399 2240 225drop table t1; 226CREATE TABLE t1 ( 227`col1` INT(1) NULL, 228`col2` INT(2) NULL, 229`col3` INT(3) NULL, 230`col4` INT(4) NULL, 231`col5` INT(5) NULL, 232`col6` INT(6) NULL, 233`col7` INT(7) NULL, 234`col8` INT(8) NULL, 235`col9` INT(9) NULL, 236`col10` BIGINT(10) NULL, 237`col11` BIGINT(11) NULL, 238`col12` BIGINT(12) NULL, 239`col13` BIGINT(13) NULL, 240`col14` BIGINT(14) NULL, 241`col15` BIGINT(15) NULL, 242`col16` BIGINT(16) NULL, 243`col17` BIGINT(17) NULL, 244`col18` BIGINT(18) NULL, 245`col19` DECIMAL(19, 0) NULL, 246`col20` DECIMAL(20, 0) NULL, 247`col21` DECIMAL(21, 0) NULL, 248`col22` DECIMAL(22, 0) NULL, 249`col23` DECIMAL(23, 0) NULL, 250`col24` DECIMAL(24, 0) NULL, 251`col25` DECIMAL(25, 0) NULL, 252`col26` DECIMAL(26, 0) NULL, 253`col27` DECIMAL(27, 0) NULL, 254`col28` DECIMAL(28, 0) NULL, 255`col29` DECIMAL(29, 0) NULL, 256`col30` DECIMAL(30, 0) NULL, 257`col31` DECIMAL(31, 0) NULL, 258`col32` DECIMAL(32, 0) NULL, 259`col33` DECIMAL(33, 0) NULL, 260`col34` DECIMAL(34, 0) NULL, 261`col35` DECIMAL(35, 0) NULL, 262`col36` DECIMAL(36, 0) NULL, 263`col37` DECIMAL(37, 0) NULL, 264`col38` DECIMAL(38, 0) NULL, 265`fix1` DECIMAL(38, 1) NULL, 266`fix2` DECIMAL(38, 2) NULL, 267`fix3` DECIMAL(38, 3) NULL, 268`fix4` DECIMAL(38, 4) NULL, 269`fix5` DECIMAL(38, 5) NULL, 270`fix6` DECIMAL(38, 6) NULL, 271`fix7` DECIMAL(38, 7) NULL, 272`fix8` DECIMAL(38, 8) NULL, 273`fix9` DECIMAL(38, 9) NULL, 274`fix10` DECIMAL(38, 10) NULL, 275`fix11` DECIMAL(38, 11) NULL, 276`fix12` DECIMAL(38, 12) NULL, 277`fix13` DECIMAL(38, 13) NULL, 278`fix14` DECIMAL(38, 14) NULL, 279`fix15` DECIMAL(38, 15) NULL, 280`fix16` DECIMAL(38, 16) NULL, 281`fix17` DECIMAL(38, 17) NULL, 282`fix18` DECIMAL(38, 18) NULL, 283`fix19` DECIMAL(38, 19) NULL, 284`fix20` DECIMAL(38, 20) NULL, 285`fix21` DECIMAL(38, 21) NULL, 286`fix22` DECIMAL(38, 22) NULL, 287`fix23` DECIMAL(38, 23) NULL, 288`fix24` DECIMAL(38, 24) NULL, 289`fix25` DECIMAL(38, 25) NULL, 290`fix26` DECIMAL(38, 26) NULL, 291`fix27` DECIMAL(38, 27) NULL, 292`fix28` DECIMAL(38, 28) NULL, 293`fix29` DECIMAL(38, 29) NULL, 294`fix30` DECIMAL(38, 30) NULL 295); 296INSERT INTO t1(`col1`, `col2`, `col3`, `col4`, `col5`, `col6`, `col7`, `col8`, `col9`, `col10`, `col11`, `col12`, `col13`, `col14`, `col15`, `col16`, `col17`, `col18`, `col19`, `col20`, `col21`, `col22`, `col23`, `col24`, `col25`, `col26`, `col27`, `col28`, `col29`, `col30`, `col31`, `col32`, `col33`, `col34`, `col35`, `col36`, `col37`, `col38`, `fix1`, `fix2`, `fix3`, `fix4`, `fix5`, `fix6`, `fix7`, `fix8`, `fix9`, `fix10`, `fix11`, `fix12`, `fix13`, `fix14`, `fix15`, `fix16`, `fix17`, `fix18`, `fix19`, `fix20`, `fix21`, `fix22`, `fix23`, `fix24`, `fix25`, `fix26`, `fix27`, `fix28`, `fix29`, `fix30`) 297VALUES (9, 99, 999, 9999, 99999, 999999, 9999999, 99999999, 999999999, 2989999999999, 99999999999, 999999999999, 9999999999999, 99999999999999, 299999999999999999, 9999999999999999, 99999999999999999, 999999999999999999, 3009999999999999999999, 99999999999999999999, 999999999999999999999, 3019999999999999999999999, 99999999999999999999999, 999999999999999999999999, 3029999999999999999999999999, 99999999999999999999999999, 303999999999999999999999999999, 9999999999999999999999999999, 30499999999999999999999999999999, 999999999999999999999999999999, 3059999999999999999999999999999999, 99999999999999999999999999999999, 306999999999999999999999999999999999, 9999999999999999999999999999999999, 30799999999999999999999999999999999999, 999999999999999999999999999999999999, 3089999999999999999999999999999999999999, 99999999999999999999999999999999999999, 3099999999999999999999999999999999999999.9, 310999999999999999999999999999999999999.99, 31199999999999999999999999999999999999.999, 3129999999999999999999999999999999999.9999, 313999999999999999999999999999999999.99999, 31499999999999999999999999999999999.999999, 3159999999999999999999999999999999.9999999, 316999999999999999999999999999999.99999999, 31799999999999999999999999999999.999999999, 3189999999999999999999999999999.9999999999, 319999999999999999999999999999.99999999999, 32099999999999999999999999999.999999999999, 3219999999999999999999999999.9999999999999, 322999999999999999999999999.99999999999999, 32399999999999999999999999.999999999999999, 3249999999999999999999999.9999999999999999, 325999999999999999999999.99999999999999999, 32699999999999999999999.999999999999999999, 3279999999999999999999.9999999999999999999, 328999999999999999999.99999999999999999999, 32999999999999999999.999999999999999999999, 3309999999999999999.9999999999999999999999, 331999999999999999.99999999999999999999999, 33299999999999999.999999999999999999999999, 3339999999999999.9999999999999999999999999, 334999999999999.99999999999999999999999999, 33599999999999.999999999999999999999999999, 3369999999999.9999999999999999999999999999, 337999999999.99999999999999999999999999999, 33899999999.999999999999999999999999999999); 339SELECT * FROM t1; 340col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23 col24 col25 col26 col27 col28 col29 col30 col31 col32 col33 col34 col35 col36 col37 col38 fix1 fix2 fix3 fix4 fix5 fix6 fix7 fix8 fix9 fix10 fix11 fix12 fix13 fix14 fix15 fix16 fix17 fix18 fix19 fix20 fix21 fix22 fix23 fix24 fix25 fix26 fix27 fix28 fix29 fix30 3419 99 999 9999 99999 999999 9999999 99999999 999999999 9999999999 99999999999 999999999999 9999999999999 99999999999999 999999999999999 9999999999999999 99999999999999999 999999999999999999 9999999999999999999 99999999999999999999 999999999999999999999 9999999999999999999999 99999999999999999999999 999999999999999999999999 9999999999999999999999999 99999999999999999999999999 999999999999999999999999999 9999999999999999999999999999 99999999999999999999999999999 999999999999999999999999999999 9999999999999999999999999999999 99999999999999999999999999999999 999999999999999999999999999999999 9999999999999999999999999999999999 99999999999999999999999999999999999 999999999999999999999999999999999999 9999999999999999999999999999999999999 99999999999999999999999999999999999999 9999999999999999999999999999999999999.9 999999999999999999999999999999999999.99 99999999999999999999999999999999999.999 9999999999999999999999999999999999.9999 999999999999999999999999999999999.99999 99999999999999999999999999999999.999999 9999999999999999999999999999999.9999999 999999999999999999999999999999.99999999 99999999999999999999999999999.999999999 9999999999999999999999999999.9999999999 999999999999999999999999999.99999999999 99999999999999999999999999.999999999999 9999999999999999999999999.9999999999999 999999999999999999999999.99999999999999 99999999999999999999999.999999999999999 9999999999999999999999.9999999999999999 999999999999999999999.99999999999999999 99999999999999999999.999999999999999999 9999999999999999999.9999999999999999999 999999999999999999.99999999999999999999 99999999999999999.999999999999999999999 9999999999999999.9999999999999999999999 999999999999999.99999999999999999999999 99999999999999.999999999999999999999999 9999999999999.9999999999999999999999999 999999999999.99999999999999999999999999 99999999999.999999999999999999999999999 9999999999.9999999999999999999999999999 999999999.99999999999999999999999999999 99999999.999999999999999999999999999999 342DROP TABLE t1; 343create table t1 (bigint_col bigint unsigned); 344insert into t1 values (17666000000000000000); 345select * from t1 where bigint_col=17666000000000000000; 346bigint_col 34717666000000000000000 348select * from t1 where bigint_col='17666000000000000000'; 349bigint_col 35017666000000000000000 351drop table t1; 352 353bug 19955 -- mod is signed with bigint 354select cast(10000002383263201056 as unsigned) mod 50 as result; 355result 3566 357create table t1 (c1 bigint unsigned); 358insert into t1 values (10000002383263201056); 359select c1 mod 50 as result from t1; 360result 3616 362drop table t1; 363select cast(19999999999999999999 as signed); 364cast(19999999999999999999 as signed) 3659223372036854775807 366Warnings: 367Warning 1916 Got overflow when converting '19999999999999999999' to INT. Value truncated 368select cast(-19999999999999999999 as signed); 369cast(-19999999999999999999 as signed) 370-9223372036854775808 371Warnings: 372Warning 1916 Got overflow when converting '-19999999999999999999' to INT. Value truncated 373select -9223372036854775808; 374Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 375def -9223372036854775808 8 20 20 N 32897 0 63 376-9223372036854775808 377-9223372036854775808 378select -(9223372036854775808); 379Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 380def -(9223372036854775808) 8 20 20 N 32897 0 63 381-(9223372036854775808) 382-9223372036854775808 383select -((9223372036854775808)); 384Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 385def -((9223372036854775808)) 8 20 20 N 32897 0 63 386-((9223372036854775808)) 387-9223372036854775808 388select -(-(9223372036854775808)); 389Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr 390def -(-(9223372036854775808)) 246 20 19 N 32897 0 63 391-(-(9223372036854775808)) 3929223372036854775808 393select --9223372036854775808, ---9223372036854775808, ----9223372036854775808; 394--9223372036854775808 ---9223372036854775808 ----9223372036854775808 3959223372036854775808 -9223372036854775808 9223372036854775808 396select -(-9223372036854775808), -(-(-9223372036854775808)); 397-(-9223372036854775808) -(-(-9223372036854775808)) 3989223372036854775808 -9223372036854775808 399create table t1 select -9223372036854775808 bi; 400describe t1; 401Field Type Null Key Default Extra 402bi bigint(20) NO NULL 403drop table t1; 404create table t1 select -9223372036854775809 bi; 405describe t1; 406Field Type Null Key Default Extra 407bi decimal(19,0) NO NULL 408drop table t1; 409# 410# Bug #45360: wrong results 411# 412CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY, 413a BIGINT(20) UNSIGNED, 414b VARCHAR(20)); 415INSERT INTO t1 (a) VALUES 416(0), 417(CAST(0x7FFFFFFFFFFFFFFF AS UNSIGNED)), 418(CAST(0x8000000000000000 AS UNSIGNED)), 419(CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED)); 420UPDATE t1 SET b = a; 421# FFFFFFFFFFFFFFFF 422EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a = 18446744073709551615 AND TRIM(a) = b; 423SHOW WARNINGS; 424Level Code Message 425Note 1003 select 1 AS `1` from `test`.`t1` where `test`.`t1`.`a` = 18446744073709551615 and '18446744073709551615' = `test`.`t1`.`b` 426# 8000000000000000 427EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a = 9223372036854775808 AND TRIM(a) = b; 428SHOW WARNINGS; 429Level Code Message 430Note 1003 select 1 AS `1` from `test`.`t1` where `test`.`t1`.`a` = 9223372036854775808 and '9223372036854775808' = `test`.`t1`.`b` 431# 7FFFFFFFFFFFFFFF 432EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a = 9223372036854775807 AND TRIM(a) = b; 433SHOW WARNINGS; 434Level Code Message 435Note 1003 select 1 AS `1` from `test`.`t1` where `test`.`t1`.`a` = 9223372036854775807 and '9223372036854775807' = `test`.`t1`.`b` 436# 0 437EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a = 0 AND TRIM(a) = b; 438SHOW WARNINGS; 439Level Code Message 440Note 1003 select 1 AS `1` from `test`.`t1` where `test`.`t1`.`a` = 0 and '0' = `test`.`t1`.`b` 441DROP TABLE t1; 442# End of 5.1 tests 443# 444# Bug#13463415 63502: INCORRECT RESULTS OF BIGINT AND DECIMAL COMPARISON 445# 446CREATE TABLE t_bigint(id BIGINT); 447INSERT INTO t_bigint VALUES (1), (2); 448SELECT id, id >= 1.1 FROM t_bigint; 449id id >= 1.1 4501 0 4512 1 452SELECT id, 1.1 <= id FROM t_bigint; 453id 1.1 <= id 4541 0 4552 1 456SELECT id, id = 1.1 FROM t_bigint; 457id id = 1.1 4581 0 4592 0 460SELECT id, 1.1 = id FROM t_bigint; 461id 1.1 = id 4621 0 4632 0 464SELECT * from t_bigint WHERE id = 1.1; 465id 466SELECT * from t_bigint WHERE id = 1.1e0; 467id 468SELECT * from t_bigint WHERE id = '1.1'; 469id 470SELECT * from t_bigint WHERE id = '1.1e0'; 471id 472SELECT * from t_bigint WHERE id IN (1.1, 2.2); 473id 474SELECT * from t_bigint WHERE id IN (1.1e0, 2.2e0); 475id 476SELECT * from t_bigint WHERE id IN ('1.1', '2.2'); 477id 478SELECT * from t_bigint WHERE id IN ('1.1e0', '2.2e0'); 479id 480SELECT * from t_bigint WHERE id BETWEEN 1.1 AND 1.9; 481id 482SELECT * from t_bigint WHERE id BETWEEN 1.1e0 AND 1.9e0; 483id 484SELECT * from t_bigint WHERE id BETWEEN '1.1' AND '1.9'; 485id 486SELECT * from t_bigint WHERE id BETWEEN '1.1e0' AND '1.9e0'; 487id 488DROP TABLE t_bigint; 489# 490# Bug#11758543 50756: BIGINT '100' MATCHES 1.001E2 491# 492CREATE TABLE t1 (a BIGINT); 493INSERT INTO t1 VALUES (1); 494SELECT * FROM t1 WHERE coalesce(a) BETWEEN 0 and 0.9; 495a 496SELECT * FROM t1 WHERE coalesce(a)=0.9; 497a 498SELECT * FROM t1 WHERE coalesce(a) in (0.8,0.9); 499a 500SELECT * FROM t1 WHERE a BETWEEN 0 AND 0.9; 501a 502SELECT * FROM t1 WHERE a=0.9; 503a 504SELECT * FROM t1 WHERE a IN (0.8,0.9); 505a 506DROP TABLE t1; 507# 508# MDEV-9372 select 100 between 1 and 9223372036854775808 returns false 509# 510SELECT 100 BETWEEN 1 AND 9223372036854775808; 511100 BETWEEN 1 AND 9223372036854775808 5121 513# 514# MDEV-17724 Wrong result for BETWEEN 0 AND 18446744073709551615 515# 516CREATE TABLE t1 (c1 bigint(20) unsigned NOT NULL); 517INSERT INTO t1 VALUES (0),(101),(255); 518SELECT * FROM t1 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1; 519c1 5200 521101 522255 523DROP TABLE t1; 524