~drizzle-trunk/drizzle/development

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

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

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 DEFAULT '0'

) ENGINE=MyISAM DEFAULT CHARSET=latin1

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 DEFAULT '0'

) ENGINE=MyISAM DEFAULT CHARSET=latin1

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 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:

Error	1292	Truncated incorrect DECIMAL value: ''

select cast(-19999999999999999999 as signed);

cast(-19999999999999999999 as signed)

-9223372036854775808

Warnings:

Error	1292	Truncated incorrect DECIMAL value: ''

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	21	19	N	129	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		0	

drop table t1;

create table t1 select -9223372036854775809 bi;

describe t1;

Field	Type	Null	Key	Default	Extra

bi	decimal(19,0)	NO		0	

drop table t1;