Materialize/test/testdrive/decimal-distinct.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 that decimal numbers that are the same are considered identical
# and those that are truly different are considered distinct
#

#
# Truly distinct numbers
#

> CREATE TABLE decimal_distinct (f1 DECIMAL);

> INSERT INTO decimal_distinct VALUES ('-999999999999999999999999999999999999999');

> INSERT INTO decimal_distinct VALUES ('-1.00000000000000000000000000000000000001');

> INSERT INTO decimal_distinct VALUES ('-0.999999999999999999999999999999999999999');

> INSERT INTO decimal_distinct VALUES ('-0.000000000000000000000000000000000000001');

> INSERT INTO decimal_distinct VALUES ('0.000000000000000000000000000000000000001');

> INSERT INTO decimal_distinct VALUES ('0.000000000000000000000000000000000000002');

> INSERT INTO decimal_distinct VALUES ('0.999999999999999999999999999999999999998');

> INSERT INTO decimal_distinct VALUES ('0.999999999999999999999999999999999999999');

> INSERT INTO decimal_distinct VALUES ('1.00000000000000000000000000000000000001');

> INSERT INTO decimal_distinct VALUES ('1.00000000000000000000000000000000000002');

> INSERT INTO decimal_distinct VALUES ('999999999999999999999999999999999999999');

> INSERT INTO decimal_distinct VALUES ('999999999999999999999999999999999999998');

> SELECT DISTINCT f1 FROM decimal_distinct;
-999999999999999999999999999999999999999
-1.00000000000000000000000000000000000001
-0.999999999999999999999999999999999999999
-0.000000000000000000000000000000000000001
0.000000000000000000000000000000000000001
0.000000000000000000000000000000000000002
0.999999999999999999999999999999999999998
0.999999999999999999999999999999999999999
1.00000000000000000000000000000000000001
1.00000000000000000000000000000000000002
999999999999999999999999999999999999998
999999999999999999999999999999999999999

> SELECT f1 FROM decimal_distinct UNION DISTINCT SELECT * FROM decimal_distinct;
-999999999999999999999999999999999999999
-1.00000000000000000000000000000000000001
-0.999999999999999999999999999999999999999
-0.000000000000000000000000000000000000001
0.000000000000000000000000000000000000001
0.000000000000000000000000000000000000002
0.999999999999999999999999999999999999998
0.999999999999999999999999999999999999999
1.00000000000000000000000000000000000001
1.00000000000000000000000000000000000002
999999999999999999999999999999999999998
999999999999999999999999999999999999999

> SELECT COUNT(DISTINCT f1), COUNT(*) FROM decimal_distinct;
12 12

> SELECT COUNT(DISTINCT f1::DECIMAL(5,2)) FROM decimal_distinct WHERE f1 BETWEEN -1 AND 1;
3

#
# Fundamentally "identical" numbers as far as the DECIMAL type is concerned
#

> CREATE TABLE decimal_same (f1 DECIMAL);

> INSERT INTO decimal_same VALUES (0),(-0);

# The numbers below differ only in the part that is outside of the precision of the DECIMAL type

> INSERT INTO decimal_same VALUES ('999999999999999999999999999999999999999.1');

> INSERT INTO decimal_same VALUES ('999999999999999999999999999999999999999.2');

# The number of leading or trailing zeroes should not matter

> INSERT INTO decimal_same VALUES ('1.010');

> INSERT INTO decimal_same VALUES ('1.01000');

> INSERT INTO decimal_same VALUES ('001.01');

> SELECT DISTINCT f1 FROM decimal_same;
0
1.01
999999999999999999999999999999999999999

> SELECT COUNT(DISTINCT f1) FROM decimal_same;
3

> SELECT f1 FROM decimal_same GROUP BY 1;
0
1.01
999999999999999999999999999999999999999