mariadb-columnstore-engine/mysql-test/columnstore/basic/t/unsigned_basic.test

-- source ../include/have_columnstore.inc

--disable_warnings
DROP DATABASE IF EXISTS unsigned_basic_db;
--enable_warnings

CREATE DATABASE unsigned_basic_db;
USE unsigned_basic_db;

create table utest1 (ukey int, c1 tinyint unsigned, c2 smallint unsigned, c3 int unsigned, c4 bigint unsigned)engine=columnstore;
insert into utest1 values (1,2,3,4,5), (2,253,65533,4294967293,18446744073709551613);
select 'q1', utest1.* from utest1 order by 1;
--error ER_WARN_DATA_OUT_OF_RANGE
insert into utest1 values (3,-254,-32766,-2147483646,-9223372036854775806);
# Limitation with boundary values in Columnstore. Remove the error after limitation is fixed
--error 1264
insert into utest1 values (4,254,65534,4294967294,18446744073709551614),(5,255,65535,4294967295,18446744073709551615);
--error ER_WARN_DATA_OUT_OF_RANGE
insert into utest1 values (6,1255,165535,14294967295,118446744073709551615);
select 'q2', utest1.* from utest1 where ukey > 2 order by 1;

insert into utest1 values (7,NULL,NULL,NULL,NULL);
select 'q3', utest1.* from utest1 where ukey=7;

--error ER_WARN_DATA_OUT_OF_RANGE
update utest1 set c4=-9223372036854775806 where ukey=1;
--error ER_WARN_DATA_OUT_OF_RANGE
update utest1 set c3=-2147483646 where ukey=1;
--error ER_WARN_DATA_OUT_OF_RANGE
update utest1 set c2=-32766 where ukey=1;
--error ER_WARN_DATA_OUT_OF_RANGE
update utest1 set c1=-254 where ukey between 0 and 2;
select 'q4', utest1.* from utest1 where ukey<2;

update utest1 set c3=118446744073709551615 where ukey=4;
update utest1 set c2=14294967295 where ukey=4;
update utest1 set c1=1255 where ukey between 4 and 5;
select 'q5', utest1.* from utest1 where ukey>3 order by 2;

insert into utest1 values (8,249,65529,4294967289,18446744073709551609), (9,250,65530,4294967290,18446744073709551610),(10,251,65531,4294967291,18446744073709551611);
select 'q6', utest1.* from utest1 where c1 between 249 and 251 order by 2;
select 'q7', utest1.* from utest1 where c2 between 65529 and 65531 order by 2;
select 'q8', utest1.* from utest1 where c3 between 4294967289 and 4294967291 order by 2;
select 'q9', utest1.* from utest1 where c3 between 18446744073709551609 and 18446744073709551611 order by 2;

--disable_warnings
drop table if exists utest1;
DROP TABLE if exists utest2;
--enable_warnings

create table utest2 (ukey bigint unsigned, c1 float unsigned, c2 double unsigned, c3 decimal(5,2) unsigned, c4 decimal(18,6) unsigned) engine=columnstore COMMENT='autoincrement=ukey,9223372036854775806';
insert into utest2 values (0,2.22507385E-18, 2.225073858507201E-307, 123.45, 1234567890.12345678); 
select 'q10', utest2.* from utest2 order by utest2.ukey;

--error ER_WARN_DATA_OUT_OF_RANGE
insert into utest2 values (0,-2.22507385E-18, -2.225073858507201E-307, -123.45, -1234567890.12345678); 
select 'q11', utest2.* from utest2 order by utest2.ukey;

--error ER_WARN_DATA_OUT_OF_RANGE
insert into utest2 values (0,0.0, 0.0, 43123.45, 34321234567890.12345678); 
select 'q12', utest2.* from utest2 order by utest2.ukey;
select 'q13', utest2.* from utest2 where c2 > 0 order by utest2.ukey;
insert into utest2 values (0,0.0, 0.0, 0.0, 0); 
select 'q12', utest2.* from utest2 order by utest2.ukey;

DROP TABLE if exists utest2;

--disable_warnings
DROP TABLE IF EXISTS utest3;
--enable_warnings

CREATE TABLE utest3 (ukey TINYINT UNSIGNED, c1 INT UNSIGNED) engine=columnstore COMMENT='autoincrement=ukey,250';
INSERT INTO utest3 VALUES (0,1); 
INSERT INTO utest3 VALUES (0,2); 
INSERT INTO utest3 VALUES (0,3); 
INSERT INTO utest3 VALUES (0,4); 
--error 1815
INSERT INTO utest3 VALUES (0,5); 
SELECT 'q13', utest3.* FROM utest3 ORDER BY utest3.ukey;

DROP TABLE IF EXISTS utest3;
CREATE TABLE utest3 (ukey SMALLINT UNSIGNED, c1 INT UNSIGNED) engine=columnstore COMMENT='autoincrement=ukey,65530';
INSERT INTO utest3 VALUES (0,1); 
INSERT INTO utest3 VALUES (0,2); 
INSERT INTO utest3 VALUES (0,3); 
INSERT INTO utest3 VALUES (0,4); 
--error 1815
INSERT INTO utest3 VALUES (0,5); 
SELECT 'q13', utest3.* FROM utest3 ORDER BY utest3.ukey;

DROP TABLE IF EXISTS utest3;
CREATE TABLE utest3 (ukey INT UNSIGNED, c1 INT UNSIGNED) engine=columnstore COMMENT='autoincrement=ukey,4294967290';
INSERT INTO utest3 VALUES (0,1); 
INSERT INTO utest3 VALUES (0,2); 
INSERT INTO utest3 VALUES (0,3); 
INSERT INTO utest3 VALUES (0,4); 
--error 1815
INSERT INTO utest3 VALUES (0,5); 
SELECT 'q13', utest3.* FROM utest3 ORDER BY utest3.ukey;

DROP TABLE IF EXISTS utest3;
CREATE TABLE utest3 (ukey BIGINT UNSIGNED, c1 INT UNSIGNED) engine=columnstore COMMENT='autoincrement=ukey,18446744073709551610';
INSERT INTO utest3 VALUES (0,1); 
INSERT INTO utest3 VALUES (0,2); 
INSERT INTO utest3 VALUES (0,3); 
INSERT INTO utest3 VALUES (0,4); 
--error 1815
INSERT INTO utest3 VALUES (0,5); 
SELECT 'q13', utest3.* FROM utest3 ORDER BY utest3.ukey;
DROP TABLE IF EXISTS utest3;

# Clean UP
DROP DATABASE unsigned_basic_db;