Materialize/test/testdrive/decimal-sum.td

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

#
# Test the implementation of SUM over decimals using the EXCEPT method to issue retractions
#

#
# The name of each table corresponds to the number it stores. We use multiple tables per value in order
# to prevent the optimizer from folding our UNION + EXCEPT constructs in a way that will case no
# retractions to actually be processed
#

> CREATE TABLE "A-999999999999999999999999999999999999999" (f1 DECIMAL);

> INSERT INTO "A-999999999999999999999999999999999999999" VALUES ('-999999999999999999999999999999999999999');

> CREATE TABLE "A-999999999999999999999999999999999999998" (f1 DECIMAL);

> INSERT INTO "A-999999999999999999999999999999999999998" VALUES ('-999999999999999999999999999999999999999');

> CREATE TABLE "A+999999999999999999999999999999999999999" (f1 DECIMAL);

> INSERT INTO "A+999999999999999999999999999999999999999" VALUES ('999999999999999999999999999999999999999');

> CREATE TABLE "A+999999999999999999999999999999999999998" (f1 DECIMAL);

> INSERT INTO "A+999999999999999999999999999999999999998" VALUES ('999999999999999999999999999999999999999');


> CREATE TABLE "B-999999999999999999999999999999999999999" (f1 DECIMAL);

> INSERT INTO "B-999999999999999999999999999999999999999" VALUES ('-999999999999999999999999999999999999999');

> CREATE TABLE "B-999999999999999999999999999999999999998" (f1 DECIMAL);

> INSERT INTO "B-999999999999999999999999999999999999998" VALUES ('-999999999999999999999999999999999999999');

> CREATE TABLE "B+999999999999999999999999999999999999999" (f1 DECIMAL);

> INSERT INTO "B+999999999999999999999999999999999999999" VALUES ('999999999999999999999999999999999999999');

> CREATE TABLE "B+999999999999999999999999999999999999998" (f1 DECIMAL);

> INSERT INTO "B+999999999999999999999999999999999999998" VALUES ('999999999999999999999999999999999999999');


> CREATE TABLE "C-999999999999999999999999999999999999999" (f1 DECIMAL);

> INSERT INTO "C-999999999999999999999999999999999999999" VALUES ('-999999999999999999999999999999999999999');

> CREATE TABLE "C-999999999999999999999999999999999999998" (f1 DECIMAL);

> INSERT INTO "C-999999999999999999999999999999999999998" VALUES ('-999999999999999999999999999999999999999');

> CREATE TABLE "C+999999999999999999999999999999999999999" (f1 DECIMAL);

> INSERT INTO "C+999999999999999999999999999999999999999" VALUES ('999999999999999999999999999999999999999');

> CREATE TABLE "C+999999999999999999999999999999999999998" (f1 DECIMAL);

> INSERT INTO "C+999999999999999999999999999999999999998" VALUES ('999999999999999999999999999999999999999');

> SELECT SUM(f1) FROM (SELECT * FROM "A-999999999999999999999999999999999999999");
-999999999999999999999999999999999999999

> SELECT SUM(DISTINCT f1) FROM (
             SELECT * FROM "A-999999999999999999999999999999999999999"
   UNION ALL SELECT * FROM "B-999999999999999999999999999999999999999"
  );
-999999999999999999999999999999999999999

> SELECT SUM(f1) FROM (
             SELECT * FROM "A-999999999999999999999999999999999999999"
   UNION ALL SELECT * FROM "B-999999999999999999999999999999999999999"
  );
-Infinity

# Retracting the offending value causes the sum to go back within bounds

> SELECT SUM(f1) FROM (
             SELECT * FROM "A-999999999999999999999999999999999999999"
   UNION ALL SELECT * FROM "B-999999999999999999999999999999999999999"
  EXCEPT ALL SELECT * FROM "C-999999999999999999999999999999999999999"
  );
-999999999999999999999999999999999999999

> SELECT SUM(ABS(f1)) FROM (
             SELECT * FROM "A-999999999999999999999999999999999999999"
   UNION ALL SELECT * FROM "B-999999999999999999999999999999999999999"
  EXCEPT ALL SELECT * FROM "C-999999999999999999999999999999999999999"
  );
999999999999999999999999999999999999999


> CREATE TABLE "A+0.000000000000000000000000000000000000001" (f1 DECIMAL);

> INSERT INTO "A+0.000000000000000000000000000000000000001" VALUES ('0.000000000000000000000000000000000000001');

> CREATE TABLE "B+0.000000000000000000000000000000000000001" (f1 DECIMAL);

> INSERT INTO "B+0.000000000000000000000000000000000000001" VALUES ('0.000000000000000000000000000000000000001');

> CREATE TABLE "C+0.000000000000000000000000000000000000001" (f1 DECIMAL);

> INSERT INTO "C+0.000000000000000000000000000000000000001" VALUES ('0.000000000000000000000000000000000000001');

> SELECT SUM(f1) FROM (
             SELECT * FROM "A+0.000000000000000000000000000000000000001"
   UNION ALL SELECT * FROM "B+0.000000000000000000000000000000000000001"
  );
0.000000000000000000000000000000000000002

> SELECT SUM(f1) FROM (
             SELECT * FROM "A+0.000000000000000000000000000000000000001"
   UNION ALL SELECT * FROM "B+0.000000000000000000000000000000000000001"
  EXCEPT ALL SELECT * FROM "C+0.000000000000000000000000000000000000001"
  );
0.000000000000000000000000000000000000001

> SELECT SUM(f1) FROM (
             SELECT  f1 FROM "A+0.000000000000000000000000000000000000001"
   UNION ALL SELECT -f1 FROM "B+0.000000000000000000000000000000000000001"
  );
0

> SELECT SUM(f1) FROM (
             SELECT f1 FROM "A+0.000000000000000000000000000000000000001"
   UNION ALL SELECT -f1 FROM "B+0.000000000000000000000000000000000000001"
  EXCEPT ALL SELECT -f1 FROM "C+0.000000000000000000000000000000000000001"
  );
0.000000000000000000000000000000000000001

> SELECT SUM(f1) FROM (
             SELECT f1 FROM "A+0.000000000000000000000000000000000000001"
   UNION ALL SELECT -f1 FROM "B+0.000000000000000000000000000000000000001"
  EXCEPT ALL SELECT f1 FROM "C+0.000000000000000000000000000000000000001"
  );
-0.000000000000000000000000000000000000001