Materialize/test/sqllogictest/cockroach/decimal.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# 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.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/decimal
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

mode cockroach

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_enable_table_keys = true
----
COMPLETE 0

# The following tests have results equivalent to Postgres (differences
# in string representation and number of decimals returned, but otherwise
# the same). These do not pass using the inf package. The inf package
# (http://gopkg.in/inf.v0) is what we used to use, but it had various problems
# (for example, all the test cases below), and was replaced with apd.

# inf returns 0
query R
SELECT (1.4238790346995263e-40::DECIMAL / 6.011482313728436e+41::DECIMAL)
----
2.3685988919035999994E-82

# inf returns -108.4851126682386588
query R
SELECT ln(7.682705743584112e-48::DECIMAL)
----
-108.48511266823882051

# inf returns 0
query R
SELECT sqrt(9.789765531128956e-34::DECIMAL)
----
3.1288601009199749773E-17

# inf returns 0.1547300000000000
query R
SELECT pow(4.727998800941528e-14::DECIMAL, 0.06081860494226844::DECIMAL)
----
0.15472926640705911955

# inf returns 0, 0
query RR
SELECT pow(sqrt(1e-10::DECIMAL), 2), sqrt(pow(1e-5::DECIMAL, 2))
----
1E-10  0.00001

# inf returns 1e-16, 0, 2e-16
query RRR
SELECT 1e-16::DECIMAL / 2, 1e-16::DECIMAL / 3, 1e-16::DECIMAL / 2 * 2
----
5E-17  3.3333333333333333333E-17  1.0E-16

# inf returns 1e-8, 0, 0, 0
query RRRR
SELECT pow(1e-4::DECIMAL, 2), pow(1e-5::DECIMAL, 2), pow(1e-8::DECIMAL, 2), pow(1e-9::DECIMAL, 2)
----
1E-8  1E-10  1E-16  1E-18

# inf returns argument too large
query R
SELECT pow(1e-10::DECIMAL, 2)
----
1E-20

# inf panics (materialize#13051)
query RR
SELECT 'NaN'::FLOAT::DECIMAL, 'NaN'::DECIMAL
----
NaN NaN

# Ensure trailing zeros are kept for decimal types with no listed scale,
# and enforced when the scale is listed.

statement ok
CREATE TABLE t (d decimal, v decimal(3, 1))

statement ok
INSERT INTO t VALUES (0.000::decimal, 0.00::decimal), (1.00::decimal, 1.00::decimal), (2.0::decimal, 2.0::decimal), (3::decimal, 3::decimal)

query RR
SELECT * FROM t ORDER BY d
----
0.000  0.0
1.00   1.0
2.0    2.0
3      3.0

# Ensure trailing zeros are kept in an index.

statement ok
CREATE TABLE t2 (d decimal, v decimal(3, 1), primary key (d, v))

statement ok
INSERT INTO t2 VALUES
  (1.00::decimal, 1.00::decimal),
  (2.0::decimal, 2.0::decimal),
  (3::decimal, 3::decimal),
  ('NaN'::decimal, 'NaN'::decimal),
  ('Inf'::decimal, 'Inf'::decimal),
  ('-Inf'::decimal, '-Inf'::decimal),
  ('-0.0000'::decimal, '-0.0000'::decimal)

query RR
SELECT * FROM t2 ORDER BY d
----
NaN        NaN
-Infinity  -Infinity
0.0000     0.0
1.00       1.0
2.0        2.0
3          3.0
Infinity   Infinity

# Ensure uniqueness in PK columns with +/- NaN and 0.

statement error duplicate key value
INSERT INTO t2 VALUES ('-NaN'::decimal, '-NaN'::decimal)

statement error duplicate key value
INSERT INTO t2 VALUES (0, 0)

# Ensure NaN cannot be signaling or negative.

query RRRR
SELECT 'NaN'::decimal, '-NaN'::decimal, 'sNaN'::decimal, '-sNaN'::decimal
----
NaN NaN NaN NaN

query RR
SELECT * FROM t2 WHERE d IS NaN and v IS NaN
----
NaN NaN

query RR
SELECT * FROM t2 WHERE d = 'Infinity' and v = 'Infinity'
----
Infinity Infinity

query RR
SELECT * FROM t2 WHERE d = '-Infinity' and v = '-Infinity'
----
-Infinity -Infinity

# Ensure special values are handled correctly.

statement ok
CREATE TABLE s (d decimal null, index (d))

statement ok
INSERT INTO s VALUES
  (null),
  ('NaN'::decimal),
  ('-NaN'::decimal),
  ('Inf'::decimal),
  ('-Inf'::decimal),
  ('0'::decimal),
  (1),
  (-1)

statement ok
INSERT INTO s VALUES
  ('-0'::decimal),
  ('-0.0'::decimal),
  ('-0.00'::decimal),
  ('-0.00E-1'::decimal),
  ('-0.0E-3'::decimal)

query R rowsort
SELECT * FROM s WHERE d = 0
----
0
0
0.0
0.00
0.000
0.0000

query R
SELECT * FROM s WHERE d IS NAN
----
NaN
NaN

query R
SELECT * FROM s WHERE d = 'inf'::decimal
----
Infinity

query R
SELECT * FROM s WHERE d = 'NaN'
----
NaN
NaN

# In the following tests, the various zero values all compare equal to
# each other so we must use two ORDER BY clauses to obtain a stable result.

# Check the ordering of decimal values.
query R
SELECT d FROM s ORDER BY d, d::TEXT
----
NULL
NaN
NaN
-Infinity
-1
0
0
0.0
0.00
0.000
0.0000
1
Infinity

# Just test the NaN-ness of the values.
query RBBB
SELECT d, d IS NaN, d = 'NaN', isnan(d) FROM s@{FORCE_INDEX=primary} ORDER BY d, d::TEXT
----
NULL       NULL   NULL   NULL
NaN        true   true   true
NaN        true   true   true
-Infinity  false  false  false
-1         false  false  false
0          false  false  false
0          false  false  false
0.0        false  false  false
0.00       false  false  false
0.000      false  false  false
0.0000     false  false  false
1          false  false  false
Infinity   false  false  false

# Just test the NaN-ness of the values in secondary index
query RBBB
SELECT d, d IS NaN, d = 'NaN', isnan(d) FROM s@{FORCE_INDEX=s_d_idx} ORDER BY d, d::TEXT
----
NULL       NULL   NULL   NULL
NaN        true   true   true
NaN        true   true   true
-Infinity  false  false  false
-1         false  false  false
0          false  false  false
0          false  false  false
0.0        false  false  false
0.00       false  false  false
0.000      false  false  false
0.0000     false  false  false
1          false  false  false
Infinity   false  false  false

query RB
select d, d > 'NaN' from s@{FORCE_INDEX=primary} where d > 'NaN' ORDER BY d, d::TEXT
----
-Infinity  true
-1         true
0          true
0          true
0.0        true
0.00       true
0.000      true
0.0000     true
1          true
Infinity   true

query RB
select d, d > 'NaN' from s@{FORCE_INDEX=s_d_idx} where d > 'NaN' ORDER BY d, d::TEXT
----
-Infinity  true
-1         true
0          true
0          true
0.0        true
0.00       true
0.000      true
0.0000     true
1          true
Infinity   true

# Verify that decimals don't lose trailing 0s even when used for an index.
statement ok
CREATE INDEX idx ON s (d)

query R rowsort
SELECT * FROM s@idx WHERE d = 0
----
0
0
0.0
0.00
0.000
0.0000

statement ok
INSERT INTO s VALUES
  ('10'::decimal),
  ('10.0'::decimal),
  ('10.00'::decimal),
  ('10.000'::decimal),
  ('100000E-4'::decimal),
  ('1000000E-5'::decimal),
  ('1.0000000E+1'::decimal)

query R rowsort
SELECT * FROM s@primary WHERE d = 10
----
10
10.0
10.00
10.000
10.0000
10.00000
10.000000

query R rowsort
SELECT * FROM s@idx WHERE d = 10
----
10
10.0
10.00
10.000
10.0000
10.00000
10.000000

query R
SELECT 1.00::decimal(6,4)
----
1.0000

statement error value with precision 6, scale 4 must round to an absolute value less than 10\^2
SELECT 101.00::decimal(6,4)

statement error scale \(6\) must be between 0 and precision \(4\)
SELECT 101.00::decimal(4,6)

statement error value with precision 2, scale 2 must round to an absolute value less than 1
SELECT 1::decimal(2, 2)

# Regression test for materialize#16081

statement ok
CREATE TABLE a (b DECIMAL)

statement ok
INSERT INTO a VALUES (142378208485490985369999605144727062141206925976498256305323716858805588894693616552055968571135475510700810219028167653516982373238641332965927953273383572708760984694356069974208844865675206339235758647159337463780100273189720943242182911961627806424621091859596571173867825568394327041453823674373002756096)

query R
SELECT * FROM a
----
142378208485490985369999605144727062141206925976498256305323716858805588894693616552055968571135475510700810219028167653516982373238641332965927953273383572708760984694356069974208844865675206339235758647159337463780100273189720943242182911961627806424621091859596571173867825568394327041453823674373002756096

# Verify that NaNs are returned instead of invalid operation.
query R
SELECT 'inf'::decimal + '-inf'::decimal
----
NaN