SELECT * FROM numeric_table1;

SELECT * FROM numeric_table2;

# JIRA 507, WORKING FOR BABEL
SELECT * FROM numeric_table3;

SELECT * FROM numeric_table4;

SELECT * FROM numeric_table5;

SELECT * FROM numeric_table6;

SELECT * FROM numeric_table7;

SELECT * FROM numeric_table8;

SELECT * FROM numeric_table9;

# BABEL-1459
declare @numvar numeric(5,4); set @numvar=-0.010; SELECT @numvar as N'@var';
declare @numvar numeric(4,4); set @numvar=-0.010; SELECT @numvar as N'@var';
declare @numvar numeric(5,4); set @numvar=1.01; SELECT @numvar as N'@var';
declare @numvar numeric(4,4); set @numvar=0.01; SELECT @numvar as N'@var';
declare @numvar numeric(4,4); set @numvar=0; SELECT @numvar as N'@var';

# BABEL-2048
select b - 1 from babel_2048_t1;
select b - a from babel_2048_t1;
select a - b from babel_2048_t1;
select a + b from babel_2048_t1;
select a * b from babel_2048_t1;
select a / b from babel_2048_t1;
select b / a from babel_2048_t1;
select b * NULL from babel_2048_t1;
select b / NULL from babel_2048_t1;
select b - NULL from babel_2048_t1;
select b + NULL from babel_2048_t1;
select SQRT(a / b) from babel_2048_t1;
select ROUND(a / b, 3) from babel_2048_t1;
select SQRT(7);
select ROUND(10.1234567, 5);

# test operation between int and numeric(20, 9)
select a+c, a-c, a*c, a/c, a%c, a%NULL from babel_2048_t1;
# test operation between numeric and numeric
select b+c, b-c, b*c, b/c, b%c, b%NULL from babel_2048_t1;
# test operation between numeric and float
select c+d, c-d, c*d, c/d, c%d, c%NULL from babel_2048_t1;

# test NULLIF with numeric args
select nullif(c, b) from babel_2048_t1;

# test Coalesce with numeric args
select coalesce(NULL, NULL, b) from babel_2048_t1;

# test Case expression with numeric args
select case when a <= 1 then b - a when a > 1 then c - a end from babel_2048_t1;

# test Max() and Min() with numeric args
select Max(c-b), Min(c-b) from babel_2048_t1;

# BABEL-3074
SELECT count(*), sum(amount) FROM testnumeric_overflow_test1;

# BABEL-3157
#testing default precision for both sum() and avg()
#o/p scale = 2 [i/p scale for sum()], o/p scale = 6 [max(6, i/p scale) for avg()]
With Top10 as (select top (10) * from testnumeric_overflow_test2) select sum(amount), avg(amount) from Top10;
With Top11 as (select top (11) * from testnumeric_overflow_test2) select sum(amount), avg(amount) from Top11;

#testing resultant scale when i/p scale > 6
#o/p scale = 8 [i/p scale for sum()], o/p scale = 8 [max(6, i/p scale) for avg()]
SELECT sum(amount) from testnumeric_overflow_test3;
SELECT avg(amount) from testnumeric_overflow_test3;

#o/p precision = 38, so sum() won't cause an overflow even when #output digits > input precision
SELECT sum(amount) from testnumeric_overflow_test4;
SELECT avg(amount) from testnumeric_overflow_test4;

select sum(amount1), sum(amount2), sum(amount3), avg(amount1), avg(amount2), avg(amount3) from testnumeric_overflow_test5;
With Top10 as (select top (10) * from testnumeric_overflow_test5) select sum(amount1), sum(amount2), sum(amount3), avg(amount1), avg(amount2), avg(amount3) from Top10;
With Top11 as (select top (11) * from testnumeric_overflow_test5) select sum(amount1), sum(amount2), sum(amount3), avg(amount1), avg(amount2), avg(amount3) from Top11;

#BABEL-3345
select COALESCE( int1, int2 / COALESCE( NULLIF( rate, 0 ), 1 )) * CASE WHEN type IS NULL THEN 1 ELSE -1 END AS FTD_AMOUNT_FC from testnumeric_vu_prepare_tab1;

#resultant precision = 49 > 38, scale = 12 > 6, precision - scale = 37 > 32; o/p precision set to 38, o/p scale set to 6
select in1 * in2 from testnumeric_vu_prepare_tab2;

#resultant precision = 40 > 38, scale = 5, precision - scale = 35 <= 38; delta = precision - 38 = 2,  o/p scale = scale - delta = 3(scale adjustment), o/p precision = 38
select in4 * in5 from testnumeric_vu_prepare_tab2;

#resultant precision = 49 > 38, scale = 3 < 6, precision - scale = 46 > 38; will cause arithmetic overflow
select in3 * in4 from testnumeric_vu_prepare_tab2;
