CREATE TABLE decimal_table(num decimal(5, 2));

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

SELECT * FROM decimal_table;

DROP TABLE decimal_table;

CREATE TABLE decimal_table(num decimal(38, 3));

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

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

SELECT * FROM decimal_table;

DROP TABLE decimal_table;

# JIRA 507, WORKING FOR BABEL
#CREATE TABLE decimal_table(num decimal(39, 20));

CREATE TABLE decimal_table(num decimal(38, 20));

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

DROP TABLE decimal_table;

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

DROP TABLE decimal_table;

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

DROP TABLE decimal_table;

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

DROP TABLE decimal_table;

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

DROP TABLE decimal_table;

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

DROP TABLE decimal_table;


CREATE TABLE decimal_table(num decimal(38, 25));

insert into decimal_table values (2147483647)
insert into decimal_table values (-2147483647)

insert into decimal_table values (2147483646)
insert into decimal_table values (-2147483646)

insert into decimal_table values (2147483648)
insert into decimal_table values (-2147483648)

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

SELECT * FROM decimal_table;

DROP TABLE decimal_table;

# BABEL-3074
CREATE TABLE overflow_test (id integer PRIMARY KEY, amount decimal(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 decimal(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 decimal(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 decimal(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 decimal(6, 2), amount2 decimal(6,3), amount3 decimal(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 decimal(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 decimal(38,0), num2 decimal(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 decimal(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 testdecimal_tab (int1 decimal(24,6),int2 decimal(24,6),rate decimal(24,6), type varchar(1000));
insert into testdecimal_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 testdecimal_tab;
drop table testdecimal_tab;

create table testdecimal_tab (in1 decimal(24, 6), in2 decimal(24, 6), in3 decimal(24, 1), in4 decimal(24, 2), in5 decimal(15, 3));
insert into testdecimal_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 testdecimal_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 testdecimal_tab;

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

drop table testdecimal_tab;
