CREATE TABLE numeric_table(num numeric(5, 2));

prepst#!#INSERT INTO numeric_table(num) VALUES(@a) #!#numeric|-|a|-|3|-|5|-|2
prepst#!#exec#!#numeric|-|a|-|123.456|-|5|-|2
prepst#!#exec#!#numeric|-|a|-|123.4|-|5|-|2
prepst#!#exec#!#numeric|-|a|-|123|-|5|-|2
prepst#!#exec#!#numeric|-|a|-|123.45|-|5|-|2
prepst#!#exec#!#numeric|-|a|-|<NULL>|-|5|-|2
prepst#!#exec#!#numeric|-|a|-|-123.456|-|5|-|2
prepst#!#exec#!#numeric|-|a|-|-123.4|-|5|-|2
prepst#!#exec#!#numeric|-|a|-|-123|-|5|-|2
#prepst#!#exec#!#numeric|-|a|-|0|-|5|-|2
prepst#!#exec#!#numeric|-|a|-|-1|-|3|-|2
prepst#!#exec#!#numeric|-|a|-|-123|-|9|-|2

SELECT * FROM numeric_table;

DROP TABLE numeric_table;

CREATE TABLE numeric_table(num numeric(38, 3));

prepst#!#INSERT INTO numeric_table(num) VALUES(@a1) #!#numeric|-|a1|-|3|-|5|-|2
prepst#!#exec#!#numeric|-|a1|-|123.456|-|5|-|2
prepst#!#exec#!#numeric|-|a1|-|123.4|-|5|-|2
prepst#!#exec#!#numeric|-|a1|-|123|-|5|-|2
prepst#!#exec#!#numeric|-|a1|-|123.45|-|5|-|2
prepst#!#exec#!#numeric|-|a1|-|<NULL>|-|5|-|2
prepst#!#exec#!#numeric|-|a1|-|-123.456|-|5|-|2
prepst#!#exec#!#numeric|-|a1|-|-123.4|-|5|-|2
prepst#!#exec#!#numeric|-|a1|-|-123|-|5|-|2
#prepst#!#exec#!#numeric|-|a1|-|0|-|5|-|2
prepst#!#exec#!#numeric|-|a1|-|-1|-|3|-|2
prepst#!#exec#!#numeric|-|a1|-|-123|-|9|-|2

prepst#!#exec#!#numeric|-|a1|-|2147483647|-|10|-|0
prepst#!#exec#!#numeric|-|a1|-|-2147483647|-|10|-|0
prepst#!#exec#!#numeric|-|a1|-|2147483646|-|10|-|0
prepst#!#exec#!#numeric|-|a1|-|-2147483646|-|10|-|0
prepst#!#exec#!#numeric|-|a1|-|2147483648|-|10|-|0
prepst#!#exec#!#numeric|-|a1|-|-2147483648|-|10|-|0

SELECT * FROM numeric_table;

DROP TABLE numeric_table;

# JIRA 507, WORKING FOR BABEL
#CREATE TABLE numeric_table(num numeric(39, 20));

CREATE TABLE numeric_table(num numeric(38, 20));

prepst#!#INSERT INTO numeric_table(num) VALUES(@a2) #!#numeric|-|a2|-|3|-|5|-|2
prepst#!#exec#!#numeric|-|a2|-|123.456|-|5|-|2
prepst#!#exec#!#numeric|-|a2|-|123.4|-|5|-|2
prepst#!#exec#!#numeric|-|a2|-|123|-|5|-|2
prepst#!#exec#!#numeric|-|a2|-|123.45|-|5|-|2
prepst#!#exec#!#numeric|-|a2|-|<NULL>|-|5|-|2
prepst#!#exec#!#numeric|-|a2|-|-123.456|-|5|-|2
prepst#!#exec#!#numeric|-|a2|-|-123.4|-|5|-|2
prepst#!#exec#!#numeric|-|a2|-|-123|-|5|-|2
#prepst#!#exec#!#numeric|-|a2|-|0|-|5|-|2
prepst#!#exec#!#numeric|-|a2|-|-1|-|3|-|2
prepst#!#exec#!#numeric|-|a2|-|-123|-|9|-|2
prepst#!#exec#!#numeric|-|a2|-|2147483647|-|10|-|0
SELECT * FROM numeric_table;

DROP TABLE numeric_table;

CREATE TABLE numeric_table(num numeric(38, 20));
prepst#!#INSERT INTO numeric_table(num) VALUES(@a3) #!#numeric|-|a3|-|3|-|5|-|2
prepst#!#exec#!#numeric|-|a3|-|123.456|-|5|-|2
prepst#!#exec#!#numeric|-|a3|-|123.4|-|5|-|2
prepst#!#exec#!#numeric|-|a3|-|123|-|5|-|2
prepst#!#exec#!#numeric|-|a3|-|123.45|-|5|-|2
prepst#!#exec#!#numeric|-|a3|-|<NULL>|-|5|-|2
prepst#!#exec#!#numeric|-|a3|-|-123.456|-|5|-|2
prepst#!#exec#!#numeric|-|a3|-|-123.4|-|5|-|2
prepst#!#exec#!#numeric|-|a3|-|-123|-|5|-|2
#prepst#!#exec#!#numeric|-|a3|-|0|-|5|-|2
prepst#!#exec#!#numeric|-|a3|-|-1|-|3|-|2
prepst#!#exec#!#numeric|-|a3|-|-123|-|9|-|2
prepst#!#exec#!#numeric|-|a3|-|2147483647|-|10|-|0
SELECT * FROM numeric_table;

DROP TABLE numeric_table;

CREATE TABLE numeric_table(num numeric(38, 21));
prepst#!#INSERT INTO numeric_table(num) VALUES(@a4) #!#numeric|-|a4|-|3|-|5|-|2
prepst#!#exec#!#numeric|-|a4|-|123.456|-|5|-|2
prepst#!#exec#!#numeric|-|a4|-|123.4|-|5|-|2
prepst#!#exec#!#numeric|-|a4|-|123|-|5|-|2
prepst#!#exec#!#numeric|-|a4|-|123.45|-|5|-|2
prepst#!#exec#!#numeric|-|a4|-|<NULL>|-|5|-|2
prepst#!#exec#!#numeric|-|a4|-|-123.456|-|5|-|2
prepst#!#exec#!#numeric|-|a4|-|-123.4|-|5|-|2
prepst#!#exec#!#numeric|-|a4|-|-123|-|5|-|2
#prepst#!#exec#!#numeric|-|a4|-|0|-|5|-|2
prepst#!#exec#!#numeric|-|a4|-|-1|-|3|-|2
prepst#!#exec#!#numeric|-|a4|-|-123|-|9|-|2
prepst#!#exec#!#numeric|-|a4|-|2147483647|-|10|-|0
SELECT * FROM numeric_table;

DROP TABLE numeric_table;

CREATE TABLE numeric_table(num numeric(38, 22));
prepst#!#INSERT INTO numeric_table(num) VALUES(@a5) #!#numeric|-|a5|-|3|-|5|-|2
prepst#!#exec#!#numeric|-|a5|-|123.456|-|5|-|2
prepst#!#exec#!#numeric|-|a5|-|123.4|-|5|-|2
prepst#!#exec#!#numeric|-|a5|-|123|-|5|-|2
prepst#!#exec#!#numeric|-|a5|-|123.45|-|5|-|2
prepst#!#exec#!#numeric|-|a5|-|<NULL>|-|5|-|2
prepst#!#exec#!#numeric|-|a5|-|-123.456|-|5|-|2
prepst#!#exec#!#numeric|-|a5|-|-123.4|-|5|-|2
prepst#!#exec#!#numeric|-|a5|-|-123|-|5|-|2
#prepst#!#exec#!#numeric|-|a5|-|0|-|5|-|2
prepst#!#exec#!#numeric|-|a5|-|-1|-|3|-|2
prepst#!#exec#!#numeric|-|a5|-|-123|-|9|-|2
prepst#!#exec#!#numeric|-|a5|-|2147483647|-|10|-|0
SELECT * FROM numeric_table;

DROP TABLE numeric_table;

CREATE TABLE numeric_table(num numeric(38, 23));
prepst#!#INSERT INTO numeric_table(num) VALUES(@a6) #!#numeric|-|a6|-|3|-|5|-|2
prepst#!#exec#!#numeric|-|a6|-|123.456|-|5|-|2
prepst#!#exec#!#numeric|-|a6|-|123.4|-|5|-|2
prepst#!#exec#!#numeric|-|a6|-|123|-|5|-|2
prepst#!#exec#!#numeric|-|a6|-|123.45|-|5|-|2
prepst#!#exec#!#numeric|-|a6|-|<NULL>|-|5|-|2
prepst#!#exec#!#numeric|-|a6|-|-123.456|-|5|-|2
prepst#!#exec#!#numeric|-|a6|-|-123.4|-|5|-|2
prepst#!#exec#!#numeric|-|a6|-|-123|-|5|-|2
#prepst#!#exec#!#numeric|-|a6|-|0|-|5|-|2
prepst#!#exec#!#numeric|-|a6|-|-1|-|3|-|2
prepst#!#exec#!#numeric|-|a6|-|-123|-|9|-|2
prepst#!#exec#!#numeric|-|a6|-|2147483647|-|10|-|0
SELECT * FROM numeric_table;

DROP TABLE numeric_table;

CREATE TABLE numeric_table(num numeric(38, 25));
prepst#!#INSERT INTO numeric_table(num) VALUES(@a7) #!#numeric|-|a7|-|3|-|5|-|2
prepst#!#exec#!#numeric|-|a7|-|123.456|-|5|-|2
prepst#!#exec#!#numeric|-|a7|-|123.4|-|5|-|2
prepst#!#exec#!#numeric|-|a7|-|123|-|5|-|2
prepst#!#exec#!#numeric|-|a7|-|123.45|-|5|-|2
prepst#!#exec#!#numeric|-|a7|-|<NULL>|-|5|-|2
prepst#!#exec#!#numeric|-|a7|-|-123.456|-|5|-|2
prepst#!#exec#!#numeric|-|a7|-|-123.4|-|5|-|2
prepst#!#exec#!#numeric|-|a7|-|-123|-|5|-|2
#prepst#!#exec#!#numeric|-|a7|-|0|-|5|-|2
prepst#!#exec#!#numeric|-|a7|-|-1|-|3|-|2
prepst#!#exec#!#numeric|-|a7|-|-123|-|9|-|2
prepst#!#exec#!#numeric|-|a7|-|247483647|-|10|-|0
prepst#!#exec#!#numeric|-|a7|-|-247483647|-|10|-|0
SELECT * FROM numeric_table;

DROP TABLE numeric_table;


CREATE TABLE numeric_table(num numeric(38, 25));

insert into numeric_table values (2147483647)
insert into numeric_table values (-2147483647)

insert into numeric_table values (2147483646)
insert into numeric_table values (-2147483646)

insert into numeric_table values (2147483648)
insert into numeric_table values (-2147483648)

#insert into numeric_table values(123456789123456789.1234567891234567891234567);
#insert into numeric_table values(1234567891234567891.1234567891234567891234567);
#insert into numeric_table values(123456789123456789.12345678912345678912345678);
insert into numeric_table values(0.0);
insert into numeric_table values(0.0000000000000000000000000);
insert into numeric_table values(0.00000000000000000000000000);

SELECT * FROM numeric_table;

DROP TABLE numeric_table;

# 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
CREATE TABLE babel_2048_t1(a int, b NUMERIC(19,4), c NUMERIC(20, 9), d FLOAT);
INSERT INTO babel_2048_t1 VALUES (1, 2.3, 3.123456789, 4.123456789);
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
INSERT INTO babel_2048_t1 VALUES (2, 3.7, 4.123456789, 5.123456789);
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;

drop table babel_2048_t1;

# BABEL-3074
CREATE TABLE overflow_test (id integer PRIMARY KEY, amount numeric(6, 2));
INSERT INTO overflow_test VALUES (1, 5000.00);
INSERT INTO overflow_test VALUES (2, 6000.00);
SELECT count(*), sum(amount) FROM overflow_test;
SELECT count(*), sum(amount) + 100 FROM overflow_test;
SELECT count(*), sum(amount) - 100 FROM overflow_test;
SELECT count(*), sum(amount) * 100 FROM overflow_test;
SELECT count(*), sum(amount) / 100 FROM overflow_test;
SELECT count(*), sum(amount) % 100 FROM overflow_test;
SELECT count(*), avg(amount) + 100 FROM overflow_test;
SELECT count(*), avg(amount) - 100 FROM overflow_test;
SELECT count(*), avg(amount) * 100 FROM overflow_test;
SELECT count(*), avg(amount) / 100 FROM overflow_test;
SELECT count(*), avg(amount) % 100 FROM overflow_test;
SELECT (100 / SUM((((amount) * 8.00) / 1024))) FROM overflow_test;
DROP TABLE overflow_test;

# BABEL-3157
CREATE TABLE overflow_test (id integer PRIMARY KEY, amount numeric(6, 2));
INSERT INTO overflow_test VALUES (1, 9999.99), (2, 9999.99), (3, 9999.99), (4,9999.99), (5, 9999.99), (6, 9999.99), (7, 9999.99), (8,9999.99), (9, 9999.99), (10,9999.99), (11, 9999.99), (12, 9999.99), (13, 9999.99), (14,9999.99), (15, 9999.99), (16,9999.99);
#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 overflow_test) select sum(amount), avg(amount) from Top10;
With Top11 as (select top (11) * from overflow_test) select sum(amount), avg(amount) from Top11;
DROP TABLE overflow_test;

#testing resultant scale when i/p scale > 6
CREATE TABLE #overflow_test (amount numeric(10, 8));
INSERT INTO #overflow_test VALUES(99.123);
INSERT INTO #overflow_test VALUES(1.123);
#o/p scale = 8 [i/p scale for sum()], o/p scale = 8 [max(6, i/p scale) for avg()]
SELECT sum(amount) from #overflow_test;
SELECT avg(amount) from #overflow_test;
DROP TABLE #overflow_test;

CREATE TABLE #overflow_test (amount numeric(5, 2));
INSERT INTO #overflow_test VALUES(999.12);
INSERT INTO #overflow_test VALUES(1.12);
#o/p precision = 38, so sum() won't cause an overflow even when #output digits > input precision
SELECT sum(amount) from #overflow_test;
SELECT avg(amount) from #overflow_test;
DROP TABLE #overflow_test;

CREATE TABLE overflow_test (id integer PRIMARY KEY, amount1 numeric(6, 2), amount2 numeric(6,3), amount3 numeric(11,8));
INSERT INTO overflow_test VALUES (1, 9999.99, 999.999, 999.12345678), (2, 9999.99, 999.999, 999.12345678), (3, 9999.99, 999.999, 999.12345678), (4, 9999.99, 999.999, 999.12345678), (5, 9999.99, 999.999, 999.12345678), (6, 9999.99, 999.999, 999.12345678), (7, 9999.99, 999.999, 999.12345678), (8, 9999.99, 999.999, 999.12345678), (9, 9999.99, 999.999, 999.12345678), (10,9999.99, 999.999, 999.12345678), (11, 9999.99, 999.999, 999.12345678), (12, 9999.9, 999.999, 999.12345678), (13, 9999.99, 999.999, 999.12345678), (14,9999.99, 999.999, 999.12345678), (15, 9999.99, 999.999, 999.12345678), (16, 9999.99, 999.999, 999.12345678), (17, 9999.99, 999.999, 999.12345678), (18, 9999.99, 999.999, 999.12345678), (19, 9999.99, 999.999, 999.12345678), (20,9999.99, 999.999, 999.12345678), (21, 9999.99, 999.999, 999.12345678), (22,9999.99, 999.999, 999.12345678);
select sum(amount1), sum(amount2), sum(amount3), avg(amount1), avg(amount2), avg(amount3) from overflow_test;
With Top10 as (select top (10) * from overflow_test) select sum(amount1), sum(amount2), sum(amount3), avg(amount1), avg(amount2), avg(amount3) from Top10;
With Top11 as (select top (11) * from overflow_test) select sum(amount1), sum(amount2), sum(amount3), avg(amount1), avg(amount2), avg(amount3) from Top11;
DROP TABLE overflow_test;

#BABEL-3180
CREATE TABLE overflow_test (id integer PRIMARY KEY, amount numeric(38, 2));
INSERT INTO overflow_test VALUES (1, 555555555555555555555555555555555555.23);
SELECT amount + 100 FROM overflow_test where id = 1;
SELECT amount - 100 FROM overflow_test where id = 1;
SELECT amount * 100 FROM overflow_test where id = 1;
SELECT amount / 100 FROM overflow_test where id = 1;
SELECT amount % 100 FROM overflow_test where id = 1;
DROP TABLE overflow_test;

CREATE TABLE overflow_test (num1 numeric(38,0), num2 numeric(38, 0));
INSERT INTO overflow_test VALUES (55555555555555555555555555555555555, 55555555555555555555555555555555555);
SELECT num1 + num2 from overflow_test;
SELECT num1 - num2 from overflow_test;
DROP TABLE overflow_test;

CREATE TABLE overflow_test (num1 numeric(38,0), num2 int);
INSERT INTO overflow_test VALUES (55555555555555555555555555555555555, 555555555);
SELECT num1 + num2 from overflow_test;
SELECT num1 - num2 from overflow_test;
DROP TABLE overflow_test;

#BABEL-3345
create table testnumeric_tab (int1 numeric(24,6),int2 numeric(24,6),rate numeric(24,6), type varchar(1000));
insert into testnumeric_tab values (1879323.2222, 2383823.343, 4.5484, NULL);
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_tab;
drop table testnumeric_tab;

create table testnumeric_tab (in1 numeric(24, 6), in2 numeric(24, 6), in3 numeric(24, 1), in4 numeric(24, 2), in5 numeric(15, 3));
insert into testnumeric_tab values (1879323.2222, 2383823.343, 4.5484, 232.79, 345.2345);

#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_tab;

#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_tab;

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

drop table testnumeric_tab;
