~drizzle-trunk/drizzle/development

#
# Initialize

--disable_warnings
drop table if exists t1, t2;
--enable_warnings

#
# Test of reading of bigint values
#
select 0,256,00000000000000065536,2147483647,-2147483648,2147483648,+4294967296;
select 9223372036854775807,-009223372036854775808;
select +9999999999999999999,-9999999999999999999;
select 9223372036854775808 +1;
select 9223372036854775808+1;
select -(0-3),round(-(0-3)), round(9999999999999999999);
select 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;

#
# In 3.23 we have to disable the test of column to bigint as
# this fails on AIX powerpc (the resolution for double is not good enough)
# This will work on 4.0 as we then have internal handling of bigint variables.
#

create table t1 (a bigint unsigned not null, primary key(a));
insert into t1 values (9223372036854775808);
insert into t1 values (0x7000000000000000);
select * from t1;
select * from t1 where a=18446744073709551615;
# select * from t1 where a='18446744073709551615';
delete from t1 where a=18446744073709551615;
select * from t1;
drop table t1;

create table t1 ( a int not null default 1, big bigint unsigned);
--error ER_WARN_DATA_OUT_OF_RANGE
insert into t1 (big) values (-1),(9223372036854775808),(9223372036854775807),(9223372036854775808);
--error ER_WARN_DATA_OUT_OF_RANGE
insert into t1 (big) values (-1);
insert into t1 (big) values (9223372036854775808),(9223372036854775807),(9223372036854775808);
select * from t1;
select min(big),max(big),max(big)-1 from t1;
select min(big),max(big),max(big)-1 from t1 group by a;
alter table t1 modify big bigint not null;
select min(big),max(big),max(big)-1 from t1;
select min(big),max(big),max(big)-1 from t1 group by a;
--error ER_WARN_DATA_OUT_OF_RANGE
insert into t1 (big) values (9223372036854775808);
select * from t1;
select min(big),max(big),max(big)-1 from t1;
select min(big),max(big),max(big)-1 from t1 group by a;
alter table t1 add key (big);
select min(big),max(big),max(big)-1 from t1;
select min(big),max(big),max(big)-1 from t1 group by a;
alter table t1 modify big bigint not null;
select * from t1;
select min(big),max(big),max(big)-1 from t1;
select min(big),max(big),max(big)-1 from t1 group by a;
drop table t1;

#
# Test problem with big values for auto_increment
#

create table t1 (id bigint auto_increment primary key, a int) auto_increment=9999999999;
insert into t1 values (null,1);
select * from t1;
select * from t1 limit 9999999999;
drop table t1;

#
# Item_uint::save_to_field()
# BUG#1845
# This can't be fixed in MySQL 4.0 without loosing precisions for bigints
#

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;
drop table t1;

# atof() behaviour is different of different systems. to be fixed in 4.1
SELECT '0x8000000000000001'+0;

# Test for BUG#8562: joins over BIGINT value + constant propagation
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 ORDER BY value32;
select * from t2 ORDER BY value32;

--sorted_result
select * from t1, t2 where t1.value64=17156792991891826145 and t2.value64=17156792991891826145;
--sorted_result
select * from t1, t2 where t1.value64=17156792991891826145 and t2.value64=t1.value64;

--sorted_result
select * from t1, t2 where t1.value64= 9223372036854775807 and t2.value64=9223372036854775807;
--sorted_result
select * from t1, t2 where t1.value64= 9223372036854775807 and t2.value64=t1.value64;

drop table t1, t2;

# Test for BUG#30069, can't handle bigint -9223372036854775808 on
# x86_64, with some GCC versions and optimizations.

create table t1 (sint64 bigint not null);
insert into t1 values (-9223372036854775808);
select * from t1;

drop table t1;

# End of 4.1 tests

#
# Test of CREATE ... SELECT and integers
#

create table t1 select 1 as 'a';
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
show create table t1;
drop table t1;

create table t1 select 9223372036854775809 as 'a';
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
show create table t1;
drop table t1;

create table t1 select 72036854775809 as 'a';
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
show create table t1;
select * from t1;
drop table t1;

DROP DATABASE IF EXISTS `scott`;


#
# Check various conversions from/to bigint.
#

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,-1,-1,-1);
--sorted_result
select * from t1;
drop table t1;

create table t1 ( quantity decimal(2));
insert into t1 values (50), (-50), (0), (-1);
--sorted_result
select * from t1;
drop table t1;

#
# Test of storing decimal values in BIGINT range
# (Bug #12750: Incorrect storage of 9999999999999999999 in DECIMAL(19, 0))
#

CREATE TABLE t1 (
  `col1` INT NULL,
  `col2` INT NULL,
  `col3` INT NULL,
  `col4` INT NULL,
  `col5` INT NULL,
  `col6` INT NULL,
  `col7` INT NULL,
  `col8` INT NULL,
  `col9` INT NULL,
  `col10` BIGINT NULL,
  `col11` BIGINT NULL,
  `col12` BIGINT NULL,
  `col13` BIGINT NULL,
  `col14` BIGINT NULL,
  `col15` BIGINT NULL,
  `col16` BIGINT NULL,
  `col17` BIGINT NULL,
  `col18` BIGINT 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);

--sorted_result
SELECT * FROM t1;
DROP TABLE t1;

#bug #9088 BIGINT WHERE CLAUSE
create table t1 (bigint_col bigint unsigned);
insert into t1 values (17666000000000000000);
select * from t1 where bigint_col=17666000000000000000;
select * from t1 where bigint_col='17666000000000000000';
drop table t1;

--echo
--echo bug 19955 -- mod is signed with bigint

select 10000002383263201056 mod 50 as result;

create table t1 (c1 bigint unsigned);
insert into t1 values (10000002383263201056);
select c1 mod 50 as result from t1;
drop table t1;

#
# Bug #28625: -9223372036854775808 doesn't fit in BIGINT.
#

# PS protocol gives different metadata for `Max length' column
--disable_ps_protocol
--enable_metadata
select -9223372036854775808;
select -(9223372036854775808);
select -((9223372036854775808));
select -(-(9223372036854775808));
--disable_metadata
--enable_ps_protocol
select --9223372036854775808, ---9223372036854775808, ----9223372036854775808;
select -(-9223372036854775808), -(-(-9223372036854775808));

# Bug #28005 Partitions: can't use -9223372036854775808 
create table t1 select -9223372036854775808 bi;
describe t1;
drop table t1;
create table t1 select -9223372036854775809 bi;
describe t1;
drop table t1;