DROP DATABASE IF EXISTS unsigned_basic_db; 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; q1 ukey c1 c2 c3 c4 q1 1 2 3 4 5 q1 2 253 65533 4294967293 18446744073709551613 insert into utest1 values (3,-254,-32766,-2147483646,-9223372036854775806); ERROR 22003: Out of range value for column 'c1' at row 1 insert into utest1 values (4,254,65534,4294967294,18446744073709551614),(5,255,65535,4294967295,18446744073709551615); ERROR 22003: CAL0001: insert into utest1 values (6,1255,165535,14294967295,118446744073709551615); ERROR 22003: Out of range value for column 'c1' at row 1 select 'q2', utest1.* from utest1 where ukey > 2 order by 1; q2 ukey c1 c2 c3 c4 insert into utest1 values (7,NULL,NULL,NULL,NULL); select 'q3', utest1.* from utest1 where ukey=7; q3 ukey c1 c2 c3 c4 q3 7 NULL NULL NULL NULL update utest1 set c4=-9223372036854775806 where ukey=1; ERROR 22003: CAL0002: MCS-2025: Data truncated for column 'c4' update utest1 set c3=-2147483646 where ukey=1; ERROR 22003: CAL0002: MCS-2025: Data truncated for column 'c3' update utest1 set c2=-32766 where ukey=1; ERROR 22003: CAL0002: MCS-2025: Data truncated for column 'c2' update utest1 set c1=-254 where ukey between 0 and 2; ERROR 22003: CAL0002: MCS-2025: Data truncated for column 'c1' select 'q4', utest1.* from utest1 where ukey<2; q4 ukey c1 c2 c3 c4 q4 1 2 3 4 5 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; q5 ukey c1 c2 c3 c4 q5 7 NULL NULL NULL NULL 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; q6 ukey c1 c2 c3 c4 q6 8 249 65529 4294967289 18446744073709551609 q6 9 250 65530 4294967290 18446744073709551610 q6 10 251 65531 4294967291 18446744073709551611 select 'q7', utest1.* from utest1 where c2 between 65529 and 65531 order by 2; q7 ukey c1 c2 c3 c4 q7 8 249 65529 4294967289 18446744073709551609 q7 9 250 65530 4294967290 18446744073709551610 q7 10 251 65531 4294967291 18446744073709551611 select 'q8', utest1.* from utest1 where c3 between 4294967289 and 4294967291 order by 2; q8 ukey c1 c2 c3 c4 q8 8 249 65529 4294967289 18446744073709551609 q8 9 250 65530 4294967290 18446744073709551610 q8 10 251 65531 4294967291 18446744073709551611 select 'q9', utest1.* from utest1 where c3 between 18446744073709551609 and 18446744073709551611 order by 2; q9 ukey c1 c2 c3 c4 drop table if exists utest1; DROP TABLE if exists utest2; 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); Warnings: Note 1265 Data truncated for column 'c4' at row 1 select 'q10', utest2.* from utest2 order by utest2.ukey; q10 ukey c1 c2 c3 c4 q10 9223372036854775806 2.22507e-18 2.225073858507201e-307 123.45 1234567890.123457 insert into utest2 values (0,-2.22507385E-18, -2.225073858507201E-307, -123.45, -1234567890.12345678); ERROR 22003: Out of range value for column 'c1' at row 1 select 'q11', utest2.* from utest2 order by utest2.ukey; q11 ukey c1 c2 c3 c4 q11 9223372036854775806 2.22507e-18 2.225073858507201e-307 123.45 1234567890.123457 insert into utest2 values (0,0.0, 0.0, 43123.45, 34321234567890.12345678); ERROR 22003: Out of range value for column 'c3' at row 1 select 'q12', utest2.* from utest2 order by utest2.ukey; q12 ukey c1 c2 c3 c4 q12 9223372036854775806 2.22507e-18 2.225073858507201e-307 123.45 1234567890.123457 select 'q13', utest2.* from utest2 where c2 > 0 order by utest2.ukey; q13 ukey c1 c2 c3 c4 q13 9223372036854775806 2.22507e-18 2.225073858507201e-307 123.45 1234567890.123457 insert into utest2 values (0,0.0, 0.0, 0.0, 0); select 'q12', utest2.* from utest2 order by utest2.ukey; q12 ukey c1 c2 c3 c4 q12 9223372036854775806 2.22507e-18 2.225073858507201e-307 123.45 1234567890.123457 q12 9223372036854775807 0 0 0.00 0.000000 DROP TABLE if exists utest2; DROP TABLE IF EXISTS utest3; 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); INSERT INTO utest3 VALUES (0,5); ERROR HY000: Internal error: CAL0001: Insert Failed: MCS-4012: The maximum allowed value has been exceeded for the autoincrement column data type. SELECT 'q13', utest3.* FROM utest3 ORDER BY utest3.ukey; q13 ukey c1 q13 250 1 q13 251 2 q13 252 3 q13 253 4 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); INSERT INTO utest3 VALUES (0,5); ERROR HY000: Internal error: CAL0001: Insert Failed: MCS-4012: The maximum allowed value has been exceeded for the autoincrement column data type. SELECT 'q13', utest3.* FROM utest3 ORDER BY utest3.ukey; q13 ukey c1 q13 65530 1 q13 65531 2 q13 65532 3 q13 65533 4 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); INSERT INTO utest3 VALUES (0,5); ERROR HY000: Internal error: CAL0001: Insert Failed: MCS-4012: The maximum allowed value has been exceeded for the autoincrement column data type. SELECT 'q13', utest3.* FROM utest3 ORDER BY utest3.ukey; q13 ukey c1 q13 4294967290 1 q13 4294967291 2 q13 4294967292 3 q13 4294967293 4 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); INSERT INTO utest3 VALUES (0,5); ERROR HY000: Internal error: CAL0001: Insert Failed: MCS-4012: The maximum allowed value has been exceeded for the autoincrement column data type. SELECT 'q13', utest3.* FROM utest3 ORDER BY utest3.ukey; q13 ukey c1 q13 18446744073709551610 1 q13 18446744073709551611 2 q13 18446744073709551612 3 q13 18446744073709551613 4 DROP TABLE IF EXISTS utest3; DROP DATABASE unsigned_basic_db;