-- source ../include/have_columnstore.inc

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

CREATE DATABASE unsigned_least_db;
USE unsigned_least_db;

Create table datatypetestm (
CIDX            INTEGER,
CBIGINT         BIGINT,
CDECIMAL1       DECIMAL(1),
CDECIMAL4       DECIMAL(4),
CDECIMAL4_2     DECIMAL(4,2),
CDECIMAL5       DECIMAL(5),
CDECIMAL9       DECIMAL(9),
CDECIMAL9_2     DECIMAL(9,2),
CDECIMAL10      DECIMAL(10),
CDECIMAL18      DECIMAL(18),
CDECIMAL18_2    DECIMAL(18,2),
CINTEGER        INTEGER,
CSMALLINT       SMALLINT,
CTINYINT        TINYINT,
CDOUBLE         DOUBLE,
CFLOAT          FLOAT,
CDATE           DATE,
CDATETIME       DATETIME,
CCHAR1  CHAR(1),
CCHAR2  CHAR(2),
CCHAR3  CHAR(3),
CCHAR4  CHAR(4),
CCHAR5  CHAR(5),
CCHAR6  CHAR(6),
CCHAR7  CHAR(7),
CCHAR8  CHAR(8),
CCHAR9  CHAR(9),
CCHAR255        CHAR(255),
CVCHAR1         VARCHAR(1),
CVCHAR2         VARCHAR(2),
CVCHAR3         VARCHAR(3),
CVCHAR4         VARCHAR(4),
CVCHAR5         VARCHAR(5),
CVCHAR6         VARCHAR(6),
CVCHAR7         VARCHAR(7),
CVCHAR8         VARCHAR(8),
CVCHAR255       VARCHAR(255)
)engine=columnstore;

insert into datatypetestm values 
(1,-72036854775806,-9,-999,-9.99,-999,-999999,-9999.99,-9999999,-9999999999999,-99999999999.99,-7483646,-766,-26,-1.797693231E+108,-3.402866E+18,'1997-01-01','1997-01-01 00:00:00','a','aa','aaa','aaaa','aaaaa','aaaaaa','aaaaaaa','aaaaaaaa','aaaaaaaaa','aaaaaaaaaa','a','aa','aaa','aaaa','aaaaa','aaaaaa','aaaaaaa','aaaaaaaa','aaaaaaaaaa'),
(2,-72036854775805,-8,-998,-9.98,-998,-999998,-9999.98,-9999998,-9999999999998,-99999999999.98,-7483645,-765,-25,-1.797693230E+108,-3.402865E+18,'1997-01-01','1997-01-01 00:00:01','a','b','c','d','e','f','g','h','i','j','a','b','c','d','e','f','g','h','j'),
(3,-72036854775804,-7,-997,-9.97,-997,-999997,-9999.97,-9999997,-9999999999997,-99999999999.97,-7483644,-764,-24,-1.797693229E+108,-3.402864E+18,'1997-01-02','1997-01-02 00:00:01','a','bb','cc','dd','ee','ff','gg','hh','ii','jj','a','bb','cc','dd','ee','ff','gg','hh','jj'),
(4,-72036854775803,-6,-996,-9.96,-996,-999996,-9999.96,-9999996,-9999999999996,-99999999999.96,-7483643,-763,-23,-1.797693228E+108,-3.402863E+18,'1997-01-03','1997-01-03 00:00:02','a','bb','ccc','ddd','eee','fff','ggg','hhh','iii','jjj','a','bb','ccc','ddd','eee','fff','ggg','hhh','jjj'),
(5,-72036854775802,-5,-995,-9.95,-995,-999995,-9999.95,-9999995,-9999999999995,-99999999999.95,-7483642,-762,-22,-1.797693227E+108,-3.402862E+18,'1997-01-04','1997-01-04 00:00:03','a','bb','ccc','dddd','eeee','ffff','gggg','hhhh','iiii','jjjj','a','bb','ccc','dddd','eeee','ffff','gggg','hhhh','jjjj'),
(6,72036854775803,5,995,9.95,995,999995,9999.95,9999995,9999999999995,99999999999.95,7483643,763,23,1.797693227E+108,3.402862E+18,'2009-12-28','2009-12-31 23:59:56','z','yy','xxx','wwww','vvvvv','uuuuuu','ttttttt','sssssss','rrrrrrr','qqqqqqq','z','yy','xxx','wwww','vvvvv','uuuuuu','ttttttt','sssssss','qqqqqqq'),
(7,72036854775804,6,996,9.96,996,999996,9999.96,9999996,9999999999996,99999999999.96,7483644,764,24,1.797693228E+108,3.402863E+18,'2009-12-29','2009-12-31 23:59:57','z','yy','xxx','wwww','vvvvv','uuuuuu','ttttttt','ssssssss','rrrrrrrr','qqqqqqqq','z','yy','xxx','wwww','vvvvv','uuuuuu','ttttttt','ssssssss','qqqqqqqq'),
(8,72036854775805,7,997,9.97,997,999997,9999.97,9999997,9999999999997,99999999999.97,7483645,765,25,1.797693229E+108,3.402864E+18,'2009-12-30','2009-12-31 23:59:58','z','yy','xxx','wwww','vvvvv','uuuuuu','ttttttt','ssssssss','rrrrrrrrr','qqqqqqqqq','z','yy','xxx','wwww','vvvvv','uuuuuu','ttttttt','ssssssss','qqqqqqqqq'),
(9,72036854775806,8,998,9.98,998,999998,9999.98,9999998,9999999999998,99999999999.98,7483646,766,26,1.797693230E+108,3.402865E+18,'2009-12-31','2009-12-31 23:59:59','z','yy','xxx','wwww','vvvvv','uuuuuu','ttttttt','ssssssss','rrrrrrrrr','qqqqqqqqqq','z','yy','xxx','wwww','vvvvv','uuuuuu','ttttttt','ssssssss','qqqqqqqqqq'),
(10,72036854775807,9,999,9.99,999,999999,9999.99,9999999,9999999999999,99999999999.99,7483647,767,27,1.797693231E+108,3.402866E+18,'2009-12-31','2009-12-31 23:59:59','z','zz','zzz','zzzz','zzzzz','zzzzzz','zzzzzzz','zzzzzzzz','zzzzzzzzz','zzzzzzzzzz','z','zz','zzz','zzzz','zzzzz','zzzzzz','zzzzzzz','zzzzzzzz','zzzzzzzzzz'),
(11,0,0,0,0.00,0,0,0.00,0,0,0.00,0,0,0,0.00,0.00,'2009-12-31','2009-12-31 23:59:59',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

ALTER TABLE datatypetestm ADD COLUMN CUTINYINT TINYINT UNSIGNED;
ALTER TABLE datatypetestm ADD COLUMN CUSMALLINT SMALLINT UNSIGNED;
ALTER TABLE datatypetestm ADD COLUMN CUINTEGER INT UNSIGNED;
ALTER TABLE datatypetestm ADD COLUMN CUBIGINT BIGINT UNSIGNED;
UPDATE datatypetestm SET CUTINYINT=CTINYINT + 120;
UPDATE datatypetestm SET CUSMALLINT=CSMALLINT + 20000;
UPDATE datatypetestm SET CUINTEGER=CINTEGER + 1140000000;
UPDATE datatypetestm SET CUBIGINT=CBIGINT + 5223000000000000000;

select cidx, CBIGINT, CUBIGINT, LEAST(CUBIGINT,CBIGINT,CUSMALLINT,CSMALLINT,CUTINYINT,CTINYINT,10) from datatypetestm;
select cidx, CDECIMAL1, CUSMALLINT, LEAST(CDECIMAL1,CUSMALLINT,CUTINYINT,10) from datatypetestm;
select cidx, CDECIMAL1, CUTINYINT, LEAST(CDECIMAL1,CUTINYINT) from datatypetestm;
select cidx, CDECIMAL1, CUBIGINT, LEAST(CDECIMAL1,CUBIGINT) from datatypetestm;
select cidx, CDECIMAL4, CUSMALLINT, LEAST(CDECIMAL4,CUSMALLINT,CUTINYINT,10) from datatypetestm;
select cidx, CDECIMAL4, CUTINYINT, LEAST(CDECIMAL4,CUTINYINT) from datatypetestm;
select cidx, CDECIMAL4, CUBIGINT, LEAST(CDECIMAL4,CUBIGINT) from datatypetestm;
select cidx, CDECIMAL4_2, CUSMALLINT, LEAST(CDECIMAL4_2,CUSMALLINT,CUTINYINT,10) from datatypetestm;
select cidx, CDECIMAL4_2, CUTINYINT, LEAST(CDECIMAL4_2,CUTINYINT) from datatypetestm;
select cidx, CDECIMAL4_2, CUBIGINT, LEAST(CDECIMAL4_2,CUBIGINT) from datatypetestm;
select cidx, CDECIMAL9, CUSMALLINT, LEAST(CDECIMAL9,CUSMALLINT,CUTINYINT,10) from datatypetestm;
select cidx, CDECIMAL9, CUTINYINT, LEAST(CDECIMAL9,CUTINYINT) from datatypetestm;
select cidx, CDECIMAL9, CUBIGINT, LEAST(CDECIMAL9,CUBIGINT) from datatypetestm;
select cidx, CDECIMAL9_2, CUSMALLINT, LEAST(CDECIMAL9_2,CUSMALLINT,CUTINYINT,10) from datatypetestm;
select cidx, CDECIMAL9_2, CUTINYINT, LEAST(CDECIMAL9_2,CUTINYINT) from datatypetestm;
select cidx, CDECIMAL9_2, CUBIGINT, LEAST(CDECIMAL9_2,CUBIGINT) from datatypetestm;
select cidx, CDECIMAL18, CUSMALLINT, LEAST(CDECIMAL18,CUSMALLINT,CUTINYINT,10) from datatypetestm;
select cidx, CDECIMAL18, CUTINYINT, LEAST(CDECIMAL18,CUTINYINT) from datatypetestm;
select cidx, CDECIMAL18, CUBIGINT, LEAST(CDECIMAL18,CUBIGINT) from datatypetestm;
select cidx, CDECIMAL18_2, CUSMALLINT, LEAST(CDECIMAL18_2,CUSMALLINT,CUTINYINT,10) from datatypetestm;
select cidx, CDECIMAL18_2, CUTINYINT, LEAST(CDECIMAL18_2,CUTINYINT) from datatypetestm;
select cidx, CDECIMAL18_2, CUBIGINT, LEAST(CDECIMAL18_2,CUBIGINT) from datatypetestm;
select cidx, CINTEGER, CUINTEGER, LEAST(CUINTEGER,CINTEGER,CUSMALLINT,CSMALLINT,CUTINYINT,CTINYINT,10) from datatypetestm;
select cidx, CINTEGER, CUSMALLINT, LEAST(CINTEGER,CUSMALLINT) from datatypetestm;
select cidx, CINTEGER, CUTINYINT, LEAST(CINTEGER,CUTINYINT) from datatypetestm;
select cidx, CSMALLINT, CUSMALLINT, LEAST(CSMALLINT,CUSMALLINT,CUTINYINT,10) from datatypetestm;
select cidx, CSMALLINT, CUSMALLINT, LEAST(CSMALLINT,CUSMALLINT) from datatypetestm;
select cidx, CSMALLINT, CUTINYINT, LEAST(CSMALLINT,CUTINYINT) from datatypetestm;
select cidx, CUBIGINT from datatypetestm where LEAST(CUBIGINT,CSMALLINT,CUTINYINT,10) <> CUBIGINT;
select cidx, CUSMALLINT from datatypetestm where LEAST(CUSMALLINT,CSMALLINT) <> CUSMALLINT;
select cidx, CUBIGINT from datatypetestm where LEAST(CDECIMAL18,CUBIGINT,CUSMALLINT,CUTINYINT,10) <> CUBIGINT;
select cidx, CDECIMAL18 from datatypetestm where LEAST(CDECIMAL18,CUSMALLINT) <> CDECIMAL18;
select cidx, CDECIMAL18 from datatypetestm where LEAST(CDECIMAL18,CUTINYINT) <> CDECIMAL18;
select cidx, CDECIMAL18_2 from datatypetestm where LEAST(CDECIMAL18_2,CUSMALLINT,CUTINYINT,10) <> CUSMALLINT;
select cidx, CUSMALLINT from datatypetestm where LEAST(CINTEGER,CUSMALLINT,CUTINYINT,10) <> CINTEGER;
select cidx, CINTEGER from datatypetestm where LEAST(CINTEGER,CUTINYINT) <> CINTEGER;
select cidx, CUSMALLINT from datatypetestm where LEAST(CSMALLINT,CUSMALLINT,CUTINYINT,10) <> CUSMALLINT;
select cidx, CSMALLINT from datatypetestm where LEAST(CSMALLINT,CUTINYINT) <> CSMALLINT;
select cidx, CUTINYINT from datatypetestm where LEAST(CTINYINT,CSMALLINT,CUTINYINT,10) <> CUTINYINT;
select cidx, CUTINYINT from datatypetestm where LEAST(CTINYINT,CUTINYINT) <> CUTINYINT;

--error ER_WARN_DATA_OUT_OF_RANGE
UPDATE datatypetestm SET CUTINYINT=CTINYINT-1;
--error ER_WARN_DATA_OUT_OF_RANGE
UPDATE datatypetestm SET CUSMALLINT=CSMALLINT-1;
--error ER_WARN_DATA_OUT_OF_RANGE
UPDATE datatypetestm SET CUINTEGER=CINTEGER-1;
--error ER_WARN_DATA_OUT_OF_RANGE
UPDATE datatypetestm SET CUBIGINT=CBIGINT-1;

UPDATE datatypetestm SET CUTINYINT=CTINYINT-1 WHERE CTINYINT > 0;
UPDATE datatypetestm SET CUSMALLINT=CSMALLINT-1 WHERE CSMALLINT > 0;
UPDATE datatypetestm SET CUINTEGER=CINTEGER-1 WHERE CINTEGER > 0;
UPDATE datatypetestm SET CUBIGINT=CBIGINT-1 WHERE CBIGINT > 0;

select cidx, CBIGINT, CUBIGINT, LEAST(CUBIGINT,CBIGINT,CUSMALLINT,CSMALLINT,CUTINYINT,CTINYINT,10) from datatypetestm;
select cidx, CDECIMAL1, CUSMALLINT, LEAST(CDECIMAL1,CUSMALLINT,CUTINYINT,10) from datatypetestm;
select cidx, CDECIMAL1, CUTINYINT, LEAST(CDECIMAL1,CUTINYINT) from datatypetestm;
select cidx, CDECIMAL1, CUBIGINT, LEAST(CDECIMAL1,CUBIGINT) from datatypetestm;
select cidx, CDECIMAL4, CUSMALLINT, LEAST(CDECIMAL4,CUSMALLINT,CUTINYINT,10) from datatypetestm;
select cidx, CDECIMAL4, CUTINYINT, LEAST(CDECIMAL4,CUTINYINT) from datatypetestm;
select cidx, CDECIMAL4, CUBIGINT, LEAST(CDECIMAL4,CUBIGINT) from datatypetestm;
select cidx, CDECIMAL4_2, CUSMALLINT, LEAST(CDECIMAL4_2,CUSMALLINT,CUTINYINT,10) from datatypetestm;
select cidx, CDECIMAL4_2, CUTINYINT, LEAST(CDECIMAL4_2,CUTINYINT) from datatypetestm;
select cidx, CDECIMAL4_2, CUBIGINT, LEAST(CDECIMAL4_2,CUBIGINT) from datatypetestm;
select cidx, CDECIMAL9, CUSMALLINT, LEAST(CDECIMAL9,CUSMALLINT,CUTINYINT,10) from datatypetestm;
select cidx, CDECIMAL9, CUTINYINT, LEAST(CDECIMAL9,CUTINYINT) from datatypetestm;
select cidx, CDECIMAL9, CUBIGINT, LEAST(CDECIMAL9,CUBIGINT) from datatypetestm;
select cidx, CDECIMAL9_2, CUSMALLINT, LEAST(CDECIMAL9_2,CUSMALLINT,CUTINYINT,10) from datatypetestm;
select cidx, CDECIMAL9_2, CUTINYINT, LEAST(CDECIMAL9_2,CUTINYINT) from datatypetestm;
select cidx, CDECIMAL9_2, CUBIGINT, LEAST(CDECIMAL9_2,CUBIGINT) from datatypetestm;
select cidx, CDECIMAL18, CUSMALLINT, LEAST(CDECIMAL18,CUSMALLINT,CUTINYINT,10) from datatypetestm;
select cidx, CDECIMAL18, CUTINYINT, LEAST(CDECIMAL18,CUTINYINT) from datatypetestm;
select cidx, CDECIMAL18, CUBIGINT, LEAST(CDECIMAL18,CUBIGINT) from datatypetestm;
select cidx, CDECIMAL18_2, CUSMALLINT, LEAST(CDECIMAL18_2,CUSMALLINT,CUTINYINT,10) from datatypetestm;
select cidx, CDECIMAL18_2, CUTINYINT, LEAST(CDECIMAL18_2,CUTINYINT) from datatypetestm;
select cidx, CDECIMAL18_2, CUBIGINT, LEAST(CDECIMAL18_2,CUBIGINT) from datatypetestm;
select cidx, CINTEGER, CUINTEGER, LEAST(CUINTEGER,CINTEGER,CUSMALLINT,CSMALLINT,CUTINYINT,CTINYINT,10) from datatypetestm;
select cidx, CINTEGER, CUSMALLINT, LEAST(CINTEGER,CUSMALLINT) from datatypetestm;
select cidx, CINTEGER, CUTINYINT, LEAST(CINTEGER,CUTINYINT) from datatypetestm;
select cidx, CSMALLINT, CUSMALLINT, LEAST(CSMALLINT,CUSMALLINT,CUTINYINT,10) from datatypetestm;
select cidx, CSMALLINT, CUSMALLINT, LEAST(CSMALLINT,CUSMALLINT) from datatypetestm;
select cidx, CSMALLINT, CUTINYINT, LEAST(CSMALLINT,CUTINYINT) from datatypetestm;
select cidx, CUBIGINT from datatypetestm where LEAST(CUBIGINT,CSMALLINT,CUTINYINT,10) <> CUBIGINT;
select cidx, CUSMALLINT from datatypetestm where LEAST(CUSMALLINT,CSMALLINT) <> CUSMALLINT;
select cidx, CUBIGINT from datatypetestm where LEAST(CDECIMAL18,CUBIGINT,CUSMALLINT,CUTINYINT,10) <> CUBIGINT;
select cidx, CDECIMAL18 from datatypetestm where LEAST(CDECIMAL18,CUSMALLINT) <> CDECIMAL18;
select cidx, CDECIMAL18 from datatypetestm where LEAST(CDECIMAL18,CUTINYINT) <> CDECIMAL18;
select cidx, CDECIMAL18_2 from datatypetestm where LEAST(CDECIMAL18_2,CUSMALLINT,CUTINYINT,10) <> CUSMALLINT;
select cidx, CUSMALLINT from datatypetestm where LEAST(CINTEGER,CUSMALLINT,CUTINYINT,10) <> CINTEGER;
select cidx, CINTEGER from datatypetestm where LEAST(CINTEGER,CUTINYINT) <> CINTEGER;
select cidx, CUSMALLINT from datatypetestm where LEAST(CSMALLINT,CUSMALLINT,CUTINYINT,10) <> CUSMALLINT;
select cidx, CSMALLINT from datatypetestm where LEAST(CSMALLINT,CUTINYINT) <> CSMALLINT;
select cidx, CUTINYINT from datatypetestm where LEAST(CTINYINT,CSMALLINT,CUTINYINT,10) <> CUTINYINT;
select cidx, CUTINYINT from datatypetestm where LEAST(CTINYINT,CUTINYINT) <> CUTINYINT;

ALTER TABLE datatypetestm DROP COLUMN CUTINYINT;
ALTER TABLE datatypetestm DROP COLUMN CUSMALLINT;
ALTER TABLE datatypetestm DROP COLUMN CUINTEGER;
ALTER TABLE datatypetestm DROP COLUMN CUBIGINT;

# Clean UP
DROP DATABASE unsigned_least_db;