path: root/mysql-test/main/bigint.result

drop table if exists t1, t2;
select 0,256,00000000000000065536,2147483647,-2147483648,2147483648,+4294967296;
0	256	00000000000000065536	2147483647	-2147483648	2147483648	4294967296
0	256	65536	2147483647	-2147483648	2147483648	4294967296
select 9223372036854775807,-009223372036854775808;
9223372036854775807	-009223372036854775808
9223372036854775807	-9223372036854775808
select +9999999999999999999,-9999999999999999999;
9999999999999999999	-9999999999999999999
9999999999999999999	-9999999999999999999
select cast(9223372036854775808 as unsigned)+1;
cast(9223372036854775808 as unsigned)+1
9223372036854775809
select 9223372036854775808+1;
9223372036854775808+1
9223372036854775809
select -(0-3),round(-(0-3)), round(9999999999999999999);
-(0-3)	round(-(0-3))	round(9999999999999999999)
3	3	9999999999999999999
select 1,11,101,1001,10001,100001,1000001,10000001,100000001,1000000001,10000000001,100000000001,1000000000001,10000000000001,100000000000001,1000000000000001,10000000000000001,100000000000000001,1000000000000000001,10000000000000000001;
1	11	101	1001	10001	100001	1000001	10000001	100000001	1000000001	10000000001	100000000001	1000000000001	10000000000001	100000000000001	1000000000000001	10000000000000001	100000000000000001	1000000000000000001	10000000000000000001
1	11	101	1001	10001	100001	1000001	10000001	100000001	1000000001	10000000001	100000000001	1000000000001	10000000000001	100000000000001	1000000000000001	10000000000000001	100000000000000001	1000000000000000001	10000000000000000001
select -1,-11,-101,-1001,-10001,-100001,-1000001,-10000001,-100000001,-1000000001,-10000000001,-100000000001,-1000000000001,-10000000000001,-100000000000001,-1000000000000001,-10000000000000001,-100000000000000001,-1000000000000000001,-10000000000000000001;
-1	-11	-101	-1001	-10001	-100001	-1000001	-10000001	-100000001	-1000000001	-10000000001	-100000000001	-1000000000001	-10000000000001	-100000000000001	-1000000000000001	-10000000000000001	-100000000000000001	-1000000000000000001	-10000000000000000001
-1	-11	-101	-1001	-10001	-100001	-1000001	-10000001	-100000001	-1000000001	-10000000001	-100000000001	-1000000000001	-10000000000001	-100000000000001	-1000000000000001	-10000000000000001	-100000000000000001	-1000000000000000001	-10000000000000000001
select 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);
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)
1	3	3FE	FFFD	1FFFFFC	7FFFFFFB	FFFFFFFFA	7FFFFFFFFFF9	FFFFFFFFFFF8	7FFFFFFFFFFFF7	FFFFFFFFFFFFF6	7FFFFFFFFFFFFFF5
create table t1 (a bigint unsigned not null, primary key(a));
insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612);
select * from t1;
a
18446744073709551612
18446744073709551613
18446744073709551614
18446744073709551615
select * from t1 where a=18446744073709551615;
a
18446744073709551615
delete from t1 where a=18446744073709551615;
select * from t1;
a
18446744073709551612
18446744073709551613
18446744073709551614
drop table t1;
create table t1 ( a int not null default 1, big bigint );
insert into t1 (big) values (-1),(12345678901234567),(9223372036854775807),(18446744073709551615);
Warnings:
Warning	1264	Out of range value for column 'big' at row 4
select * from t1;
a	big
1	-1
1	12345678901234567
1	9223372036854775807
1	9223372036854775807
select min(big),max(big),max(big)-1 from t1;
min(big)	max(big)	max(big)-1
-1	9223372036854775807	9223372036854775806
select min(big),max(big),max(big)-1 from t1 group by a;
min(big)	max(big)	max(big)-1
-1	9223372036854775807	9223372036854775806
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
alter table t1 modify big bigint unsigned not null;
Warnings:
Warning	1264	Out of range value for column 'big' at row 1
select min(big),max(big),max(big)-1 from t1;
min(big)	max(big)	max(big)-1
0	9223372036854775807	9223372036854775806
select min(big),max(big),max(big)-1 from t1 group by a;
min(big)	max(big)	max(big)-1
0	9223372036854775807	9223372036854775806
insert into t1 (big) values (18446744073709551615);
select * from t1;
a	big
1	0
1	12345678901234567
1	9223372036854775807
1	9223372036854775807
1	18446744073709551615
select min(big),max(big),max(big)-1 from t1;
min(big)	max(big)	max(big)-1
0	18446744073709551615	18446744073709551614
select min(big),max(big),max(big)-1 from t1 group by a;
min(big)	max(big)	max(big)-1
0	18446744073709551615	18446744073709551614
alter table t1 add key (big);
select min(big),max(big),max(big)-1 from t1;
min(big)	max(big)	max(big)-1
0	18446744073709551615	18446744073709551614
select min(big),max(big),max(big)-1 from t1 group by a;
min(big)	max(big)	max(big)-1
0	18446744073709551615	18446744073709551614
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
alter table t1 modify big bigint not null;
Warnings:
Warning	1264	Out of range value for column 'big' at row 5
select * from t1;
a	big
1	0
1	12345678901234567
1	9223372036854775807
1	9223372036854775807
1	9223372036854775807
select min(big),max(big),max(big)-1 from t1;
min(big)	max(big)	max(big)-1
0	9223372036854775807	9223372036854775806
select min(big),max(big),max(big)-1 from t1 group by a;
min(big)	max(big)	max(big)-1
0	9223372036854775807	9223372036854775806
drop table t1;
create table t1 (id bigint auto_increment primary key, a int) auto_increment=9999999999;
insert into t1 values (null,1);
select * from t1;
id	a
9999999999	1
select * from t1 limit 9999999999;
id	a
9999999999	1
drop table t1;
CREATE TABLE t1 ( quantity decimal(60,0));
insert into t1 values (10000000000000000000);
insert into t1 values (10000000000000000000.0);
insert into t1 values ('10000000000000000000');
select * from t1;
quantity
10000000000000000000
10000000000000000000
10000000000000000000
drop table t1;
SELECT '0x8000000000000001'+0;
'0x8000000000000001'+0
0
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: '0x8000000000000001'
create table t1 (
value64  bigint unsigned  not null,
value32  integer          not null,
primary key(value64, value32)
);
create table t2 (
value64  bigint unsigned  not null,
value32  integer          not null,
primary key(value64, value32)
);
insert into t1 values(17156792991891826145, 1);
insert into t1 values( 9223372036854775807, 2);
insert into t2 values(17156792991891826145, 3);
insert into t2 values( 9223372036854775807, 4);
select * from t1;
value64	value32
9223372036854775807	2
17156792991891826145	1
select * from t2;
value64	value32
9223372036854775807	4
17156792991891826145	3
select * from t1, t2 where t1.value64=17156792991891826145 and
t2.value64=17156792991891826145;
value64	value32	value64	value32
17156792991891826145	1	17156792991891826145	3
select * from t1, t2 where t1.value64=17156792991891826145 and
t2.value64=t1.value64;
value64	value32	value64	value32
17156792991891826145	1	17156792991891826145	3
select * from t1, t2 where t1.value64= 9223372036854775807 and
t2.value64=9223372036854775807;
value64	value32	value64	value32
9223372036854775807	2	9223372036854775807	4
select * from t1, t2 where t1.value64= 9223372036854775807 and
t2.value64=t1.value64;
value64	value32	value64	value32
9223372036854775807	2	9223372036854775807	4
drop table t1, t2;
create table t1 (sint64 bigint not null);
insert into t1 values (-9223372036854775808);
select * from t1;
sint64
-9223372036854775808
drop table t1;
create table t1 select 1 as 'a';
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
drop table t1;
create table t1 select 9223372036854775809 as 'a';
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(19) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
select * from t1;
a
9223372036854775809
drop table t1;
DROP DATABASE IF EXISTS `scott`;
Warnings:
Note	1008	Can't drop database 'scott'; database doesn't exist
create table t1 (a char(100), b varchar(100), c text, d blob);
insert into t1 values(
18446744073709551615,18446744073709551615,
18446744073709551615, 18446744073709551615
);
insert into t1 values (-1 | 0,-1 | 0,-1 | 0 ,-1 | 0);
select * from t1;
a	b	c	d
18446744073709551615	18446744073709551615	18446744073709551615	18446744073709551615
18446744073709551615	18446744073709551615	18446744073709551615	18446744073709551615
drop table t1;
create table t1 ( quantity decimal(2) unsigned);
insert ignore into t1 values (500), (-500), (~0), (-1);
Warnings:
Warning	1264	Out of range value for column 'quantity' at row 1
Warning	1264	Out of range value for column 'quantity' at row 2
Warning	1264	Out of range value for column 'quantity' at row 3
Warning	1264	Out of range value for column 'quantity' at row 4
select * from t1;
quantity
99
0
99
0
drop table t1;
CREATE TABLE t1 (
`col1` INT(1) NULL,
`col2` INT(2) NULL,
`col3` INT(3) NULL,
`col4` INT(4) NULL,
`col5` INT(5) NULL,
`col6` INT(6) NULL,
`col7` INT(7) NULL,
`col8` INT(8) NULL,
`col9` INT(9) NULL,
`col10` BIGINT(10) NULL,
`col11` BIGINT(11) NULL,
`col12` BIGINT(12) NULL,
`col13` BIGINT(13) NULL,
`col14` BIGINT(14) NULL,
`col15` BIGINT(15) NULL,
`col16` BIGINT(16) NULL,
`col17` BIGINT(17) NULL,
`col18` BIGINT(18) NULL,
`col19` DECIMAL(19, 0) NULL,
`col20` DECIMAL(20, 0) NULL,
`col21` DECIMAL(21, 0) NULL,
`col22` DECIMAL(22, 0) NULL,
`col23` DECIMAL(23, 0) NULL,
`col24` DECIMAL(24, 0) NULL,
`col25` DECIMAL(25, 0) NULL,
`col26` DECIMAL(26, 0) NULL,
`col27` DECIMAL(27, 0) NULL,
`col28` DECIMAL(28, 0) NULL,
`col29` DECIMAL(29, 0) NULL,
`col30` DECIMAL(30, 0) NULL,
`col31` DECIMAL(31, 0) NULL,
`col32` DECIMAL(32, 0) NULL,
`col33` DECIMAL(33, 0) NULL,
`col34` DECIMAL(34, 0) NULL,
`col35` DECIMAL(35, 0) NULL,
`col36` DECIMAL(36, 0) NULL,
`col37` DECIMAL(37, 0) NULL,
`col38` DECIMAL(38, 0) NULL,
`fix1` DECIMAL(38, 1) NULL,
`fix2` DECIMAL(38, 2) NULL,
`fix3` DECIMAL(38, 3) NULL,
`fix4` DECIMAL(38, 4) NULL,
`fix5` DECIMAL(38, 5) NULL,
`fix6` DECIMAL(38, 6) NULL,
`fix7` DECIMAL(38, 7) NULL,
`fix8` DECIMAL(38, 8) NULL,
`fix9` DECIMAL(38, 9) NULL,
`fix10` DECIMAL(38, 10) NULL,
`fix11` DECIMAL(38, 11) NULL,
`fix12` DECIMAL(38, 12) NULL,
`fix13` DECIMAL(38, 13) NULL,
`fix14` DECIMAL(38, 14) NULL,
`fix15` DECIMAL(38, 15) NULL,
`fix16` DECIMAL(38, 16) NULL,
`fix17` DECIMAL(38, 17) NULL,
`fix18` DECIMAL(38, 18) NULL,
`fix19` DECIMAL(38, 19) NULL,
`fix20` DECIMAL(38, 20) NULL,
`fix21` DECIMAL(38, 21) NULL,
`fix22` DECIMAL(38, 22) NULL,
`fix23` DECIMAL(38, 23) NULL,
`fix24` DECIMAL(38, 24) NULL,
`fix25` DECIMAL(38, 25) NULL,
`fix26` DECIMAL(38, 26) NULL,
`fix27` DECIMAL(38, 27) NULL,
`fix28` DECIMAL(38, 28) NULL,
`fix29` DECIMAL(38, 29) NULL,
`fix30` DECIMAL(38, 30) NULL
);
INSERT 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`)
VALUES (9, 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);
SELECT * FROM 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
9	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
DROP TABLE t1;
create table t1 (bigint_col bigint unsigned);
insert into t1 values (17666000000000000000);
select * from t1 where bigint_col=17666000000000000000;
bigint_col
17666000000000000000
select * from t1 where bigint_col='17666000000000000000';
bigint_col
17666000000000000000
drop table t1;

bug 19955 -- mod is signed with bigint
select cast(10000002383263201056 as unsigned) mod 50 as result;
result
6
create table t1 (c1 bigint unsigned);
insert into t1 values (10000002383263201056);
select c1 mod 50 as result from t1;
result
6
drop table t1;
select cast(19999999999999999999 as signed);
cast(19999999999999999999 as signed)
9223372036854775807
Warnings:
Warning	1916	Got overflow when converting '19999999999999999999' to INT. Value truncated
select cast(-19999999999999999999 as signed);
cast(-19999999999999999999 as signed)
-9223372036854775808
Warnings:
Warning	1916	Got overflow when converting '-19999999999999999999' to INT. Value truncated
select -9223372036854775808;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					-9223372036854775808	8	20	20	N	32897	0	63
-9223372036854775808
-9223372036854775808
select -(9223372036854775808);
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					-(9223372036854775808)	8	20	20	N	32897	0	63
-(9223372036854775808)
-9223372036854775808
select -((9223372036854775808));
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					-((9223372036854775808))	8	20	20	N	32897	0	63
-((9223372036854775808))
-9223372036854775808
select -(-(9223372036854775808));
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					-(-(9223372036854775808))	246	20	19	N	32897	0	63
-(-(9223372036854775808))
9223372036854775808
select --9223372036854775808, ---9223372036854775808, ----9223372036854775808;
--9223372036854775808	---9223372036854775808	----9223372036854775808
9223372036854775808	-9223372036854775808	9223372036854775808
select -(-9223372036854775808), -(-(-9223372036854775808));
-(-9223372036854775808)	-(-(-9223372036854775808))
9223372036854775808	-9223372036854775808
create table t1 select -9223372036854775808 bi;
describe t1;
Field	Type	Null	Key	Default	Extra
bi	bigint(20)	NO		NULL	
drop table t1;
create table t1 select -9223372036854775809 bi;
describe t1;
Field	Type	Null	Key	Default	Extra
bi	decimal(19,0)	NO		NULL	
drop table t1;
#
# Bug #45360: wrong results
#
CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY,
a BIGINT(20) UNSIGNED,
b VARCHAR(20));
INSERT INTO t1 (a) VALUES
(0),
(CAST(0x7FFFFFFFFFFFFFFF AS UNSIGNED)),
(CAST(0x8000000000000000 AS UNSIGNED)),
(CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED));
UPDATE t1 SET b = a;
# FFFFFFFFFFFFFFFF
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a = 18446744073709551615 AND TRIM(a) = b;
SHOW WARNINGS;
Level	Code	Message
Note	1003	select 1 AS `1` from `test`.`t1` where `test`.`t1`.`a` = 18446744073709551615 and '18446744073709551615' = `test`.`t1`.`b`
# 8000000000000000
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a = 9223372036854775808 AND TRIM(a) = b;
SHOW WARNINGS;
Level	Code	Message
Note	1003	select 1 AS `1` from `test`.`t1` where `test`.`t1`.`a` = 9223372036854775808 and '9223372036854775808' = `test`.`t1`.`b`
# 7FFFFFFFFFFFFFFF
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a = 9223372036854775807 AND TRIM(a) = b;
SHOW WARNINGS;
Level	Code	Message
Note	1003	select 1 AS `1` from `test`.`t1` where `test`.`t1`.`a` = 9223372036854775807 and '9223372036854775807' = `test`.`t1`.`b`
# 0
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a = 0 AND TRIM(a) = b;
SHOW WARNINGS;
Level	Code	Message
Note	1003	select 1 AS `1` from `test`.`t1` where `test`.`t1`.`a` = 0 and '0' = `test`.`t1`.`b`
DROP TABLE t1;
# End of 5.1 tests
#
# Bug#13463415 63502: INCORRECT RESULTS OF BIGINT AND DECIMAL COMPARISON
#
CREATE TABLE t_bigint(id BIGINT);
INSERT INTO t_bigint VALUES (1), (2);
SELECT id, id >= 1.1 FROM t_bigint;
id	id >= 1.1
1	0
2	1
SELECT id, 1.1 <= id FROM t_bigint;
id	1.1 <= id
1	0
2	1
SELECT id, id = 1.1 FROM t_bigint;
id	id = 1.1
1	0
2	0
SELECT id, 1.1 = id FROM t_bigint;
id	1.1 = id
1	0
2	0
SELECT * from t_bigint WHERE id = 1.1;
id
SELECT * from t_bigint WHERE id = 1.1e0;
id
SELECT * from t_bigint WHERE id = '1.1';
id
SELECT * from t_bigint WHERE id = '1.1e0';
id
SELECT * from t_bigint WHERE id IN (1.1, 2.2);
id
SELECT * from t_bigint WHERE id IN (1.1e0, 2.2e0);
id
SELECT * from t_bigint WHERE id IN ('1.1', '2.2');
id
SELECT * from t_bigint WHERE id IN ('1.1e0', '2.2e0');
id
SELECT * from t_bigint WHERE id BETWEEN 1.1 AND 1.9;
id
SELECT * from t_bigint WHERE id BETWEEN 1.1e0 AND 1.9e0;
id
SELECT * from t_bigint WHERE id BETWEEN '1.1' AND '1.9';
id
SELECT * from t_bigint WHERE id BETWEEN '1.1e0' AND '1.9e0';
id
DROP TABLE t_bigint;
#
# Bug#11758543 50756: BIGINT '100' MATCHES 1.001E2
#
CREATE TABLE t1 (a BIGINT);
INSERT INTO t1 VALUES (1);
SELECT * FROM t1 WHERE coalesce(a) BETWEEN 0 and 0.9;
a
SELECT * FROM t1 WHERE coalesce(a)=0.9;
a
SELECT * FROM t1 WHERE coalesce(a) in (0.8,0.9);
a
SELECT * FROM t1 WHERE a BETWEEN 0 AND 0.9;
a
SELECT * FROM t1 WHERE a=0.9;
a
SELECT * FROM t1 WHERE a IN (0.8,0.9);
a
DROP TABLE t1;
#
# MDEV-9372 select 100 between 1 and 9223372036854775808 returns false
#
SELECT 100 BETWEEN 1 AND 9223372036854775808;
100 BETWEEN 1 AND 9223372036854775808
1
#
# MDEV-17724 Wrong result for BETWEEN 0 AND 18446744073709551615
#
CREATE TABLE t1 (c1 bigint(20) unsigned NOT NULL);
INSERT INTO t1 VALUES (0),(101),(255);
SELECT * FROM t1 WHERE c1 BETWEEN 0 AND 18446744073709551615 ORDER BY c1;
c1
0
101
255
DROP TABLE t1;