# -------------------------------------------------------------- #
# Test case migrated from regression test suite: bug4388.sql
#
# Author: Daniel Lee, daniel.lee@mariadb.com
# -------------------------------------------------------------- #
#
--source ../include/have_columnstore.inc
#
USE tpch1;
#

set default_storage_engine=columnstore;

--disable_warnings
drop table if exists bug4388;
--enable_warnings
create table bug4388(
`venta_clave` int(10) DEFAULT NULL,
  `cantidad` decimal(10,3) DEFAULT NULL,
  `changev` decimal(18,4) DEFAULT NULL
);
insert into bug4388 values (null,null,6.8000);
select coalesce(sum(changev),0) as col1 from bug4388;
select sum(co) + 0 from ( select sum(col1) as co from  ( select 0 
as col1 union all select coalesce(sum(changev),0) as col1 from 
bug4388 ) t ) res;
select sum(co) + 0 from ( select sum(col1) as co from  ( select 
coalesce(sum(changev),0) as col1 from bug4388 union all select 0 
as col1 ) t ) res;
select sum(co) + 0 from ( select sum(col1) as co from  ( select 0 
as col1 union all select coalesce(sum(6.800),0) as col1  ) t ) res;
select sum(col1) + 0 from( select 0 as col1 union all
  select sum(changev) as col1 from bug4388 ) res;
select sum(col1) from
( select 0 as col1 union all
  select sum(changev) as col1 from bug4388 ) res;
select sum(co) from (select sum(changev) as co from bug4388 ) t;
select sum(co) from ( select sum(col1) as co from (select sum(changev)
as col1 from bug4388 ) t ) res;

select sum(col1) as co from  ( select 984467440737095516
as col1 union all select coalesce(sum(changev),0) as col1 from
bug4388 ) t;

select sum(col1) as co from  ( select 18446744073709551612
as col1 union all select coalesce(sum(changev),0) as col1 from
bug4388 ) t;

drop table bug4388;

--echo #
--echo # MCOL-4613 Garbage result of a union between huge narrow DECIMAL and BIGINT
--echo #

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

# Original test case from the MCOL-4613 issue description
create table t1 (a decimal(17,1), b bigint);
insert into t1 values (9999999999999999.9, 999999999999999999);
select * from (select a from t1 union select b from t1) tu order by a;
drop table t1;

--echo #
--echo # MCOL-4612 A subquery with a union for DECIMAL and BIGINT returns zeros
--echo #

# Original test case from the MCOL-4612 issue description
create table t1 (a decimal(17,1), b bigint);
insert into t1 values (1, 1);
insert into t1 values (9999999999999999, 99999999999999999);
select * from (select a from t1 union select b from t1) tu order by a;
select * from (select a from t1 union all select b from t1) tu order by a;
drop table t1;

# Additional test cases for MCOL-4613 and MCOL-4612
create table t1 (a decimal(18,5), b decimal(18,5) unsigned);
create table t2 (a bigint, b bigint unsigned);
create table t3 (a decimal(38,10), b decimal(38,10) unsigned);

insert into t1 values
(-1234567890123.12345, 1234567890123.12345),
(-1234567890123.1234, 1234567890123.1234),
(-9999999999999.99999, 9999999999999.99999),
(-999999999999.99999, 999999999999.99999),
(-99999999999.99999, 99999999999.99999);

insert into t2 values
(-123456789012345, 123456789012345),
(9223372036854775807, 18446744073709551613),
(-9223372036854775806, 0);

insert into t3 values
(-9999999999999999999999999999.9999999999, 9999999999999999999999999999.9999999999),
(-1234567890123456789012345678.9012345678, 1234567890123456789012345678.9012345678);

select * from (select a,b from t1 union select a,b from t2 union select a,b from t3) tu order by a,b;

--disable_warnings
drop table t1;
drop table t2;
drop table t3;
--enable_warnings
#