Materialize/test/sqllogictest/numeric.slt

# 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.

# numeric is for refactoring decimal/numeric in parallel development to the main
# branch

mode cockroach

query T
SELECT pg_typeof(0.2)
----
numeric

# Parsing

query R
SELECT '0.2'::numeric
----
0.2

query R
SELECT '-0.2'::numeric
----
-0.2

query R
SELECT -'0.2'::numeric
----
-0.2

query R
SELECT '2'::numeric
----
2

query R
SELECT '-2'::numeric
----
-2

query R
SELECT -'2'::numeric
----
-2

query R
SELECT '20'::numeric
----
20

query R
SELECT '-20'::numeric
----
-20

query R
SELECT '-1.2e20'::numeric
----
-120000000000000000000

query R
SELECT '1.2e-20'::numeric
----
0.000000000000000000012

query R
SELECT '-0.0000001'::numeric(10,2)
----
0

query R
SELECT '    1.2'::numeric
----
1.2

query R
SELECT '1.2    '::numeric
----
1.2

query error invalid input syntax for type numeric: "1.   2"
SELECT '1.   2'::numeric

query error invalid input syntax for type numeric: "-123abc456"
SELECT '-123abc456'::numeric;

query error invalid input syntax for type numeric: "1.   2"
SELECT '1.   2'::numeric

query error invalid input syntax for type numeric: "-123abc456"
SELECT '-123abc456'::numeric

query error invalid input syntax for type numeric: "e25"
SELECT 'e25'::numeric

query error invalid input syntax for type numeric: "1e"
SELECT '1e'::numeric

query error invalid input syntax for type numeric: "1x25"
SELECT '1x25'::numeric

query error invalid input syntax for type numeric: "!1"
SELECT '!1'::numeric

query error invalid input syntax for type numeric: "--1"
SELECT '--1'::numeric

query error invalid input syntax for type numeric: "-1-"
SELECT '-1-'::numeric

query error invalid input syntax for type numeric: "1-"
SELECT '1-'::numeric

query error invalid input syntax for type numeric: "1..1"
SELECT '1..1'::numeric

query error invalid input syntax for type numeric: "..1"
SELECT '..1'::numeric

query error invalid input syntax for type numeric: "1.1.1"
SELECT '1.1.1'::numeric

query R
SELECT '-0.0'::numeric
----
0

# Max precision is 39
query R
SELECT '-123456789012345678.901234567890123456789'::numeric
----
-123456789012345678.901234567890123456789

query R
SELECT '-0.000000000000000000000000000000000000001'::numeric
----
-0.000000000000000000000000000000000000001

# However, we'll round fractional component to not exceed 39 digits of precision
query R
SELECT '-123456789012345678.901234567890123456789123'::numeric
----
-123456789012345678.901234567890123456789

# We won't round non-decimal places, i.e. overflow
query error "-123456789012345678901234567890123456789123" is out of range for type numeric: exceeds maximum precision 39
SELECT '-123456789012345678901234567890123456789123'::numeric

query error "-1.2e40" is out of range for type numeric: exceeds maximum precision 39
SELECT '-1.2e40'::numeric

# If most significant digit is outside of precision bounds, error, i.e. underflow
query error "1.2e-40" is out of range for type numeric: exceeds maximum precision 39
SELECT '1.2e-40'::numeric

# Literals

query R
SELECT 98754321098754321098754321098754321
----
98754321098754321098754321098754321

query RRRR
SELECT 9.1e10, -9.1e10, 9.1e-10, -9.1e-10
----
91000000000  -91000000000  0.00000000091  -0.00000000091

query RRRRR
SELECT 0.0, 0.00, 0.000, 0.0000, 0.00000
----
0 0 0 0 0

query T
SELECT pg_typeof(1e38)
----
numeric

query error "1E39" is out of range for type numeric: exceeds maximum precision 39
SELECT pg_typeof(1e39)

query error "-1E39" is out of range for type numeric: exceeds maximum precision 39
SELECT pg_typeof(-1e39)

query error "1E-40" is out of range for type numeric: exceeds maximum precision 39
SELECT pg_typeof(1e-40)

query error "-1E-40" is out of range for type numeric: exceeds maximum precision 39
SELECT pg_typeof(-1e-40)

query error "9876543210987654321098765432109876543210" is out of range for type numeric: exceeds maximum precision 39
SELECT 9876543210987654321098765432109876543210

# Special values

query R
SELECT 'NaN'::numeric
----
NaN

query error invalid input syntax for type numeric: "-NaN"
SELECT '-NaN'::numeric

query error invalid input syntax for type numeric: "Infinity"
SELECT 'Infinity'::numeric

query error invalid input syntax for type numeric: "-Infinity"
SELECT '-Infinity'::numeric

query error invalid input syntax for type numeric: "sNaN"
SELECT 'sNaN'::numeric

# Casts

## int4 to numeric
query RRR
SELECT 7::numeric, -7::numeric, 0::numeric
----
7  -7  0

query RRR
SELECT 7::numeric(39,3), -7::numeric(39,3), 0::numeric(39,3)
----
7  -7  0

query RR
SELECT 2147483647::numeric, -2147483648::numeric
----
2147483647  -2147483648

query error numeric field overflow
SELECT 12::numeric(39,38)

## numeric to int4
query III
SELECT 7::numeric::int, 7.3::int, 7.5::int
----
7  7  8

query III
SELECT -7::numeric::int, -7.3::int, -7.5::int
----
-7  -7  -8

query II
SELECT 0::numeric::int, '-0'::numeric::int
----
0  0

query error "2147483648" integer out of range
SELECT '2147483648'::numeric::int4

query error "-2147483649" integer out of range
SELECT '-2147483649'::numeric::int4

## int8 to numeric
query RRR
SELECT 7::int8::numeric, -7::int8::numeric, 0::int8::numeric
----
7  -7  0

query RRR
SELECT 7::int8::numeric(39,3), -7::int8::numeric(39,3), 0::int8::numeric(39,3)
----
7  -7  0

query RR
SELECT 9223372036854775807::numeric, -9223372036854775808::numeric
----
9223372036854775807  -9223372036854775808

query error numeric field overflow
SELECT 12::int8::numeric(39,38)

## numeric to int8
query III
SELECT 7::numeric::int8, 7.3::int8, 7.5::int8
----
7  7  8

query III
SELECT -7::numeric::int8, -7.3::int8, -7.5::int8
----
-7  -7  -8

query II
SELECT 0::numeric::int8, -0::numeric::int8
----
0  0

query error "9223372036854775808" integer out of range
SELECT '9223372036854775808'::numeric::int4

query error "-9223372036854775809" integer out of range
SELECT '-9223372036854775809'::numeric::int4

# numeric to float4
query RRRR
SELECT 1.234::float4, 1234.567891234567::float4, 0.000000000000000123456789123456789012345::float4, -0.000000000000000123456789123456789012345::float4
----
1.234  1234.5679  0.00000000000000012345679  -0.00000000000000012345679

query RRR
SELECT 0.000::float4, 3.40282347E+38::float4, -3.40282347E+38::float4
----
0  340282350000000000000000000000000000000  -340282350000000000000000000000000000000

query R
SELECT 'NaN'::numeric::float4
----
NaN

query R
SELECT 9E-39::float4;
----
0.000000000000000000000000000000000000009

query error real out of range
SELECT 9E+38::float4

# float4 to numeric
query RRR
SELECT 1.23::float4::numeric, -1.23::float4::numeric, 1.23::float4::numeric(38,1)
----
1.23  -1.23  1.2

query RR
SELECT 'NaN'::float4::numeric, '-NaN'::float4::numeric;
----
NaN NaN

query RR
SELECT '3.40282347E+38'::float4::numeric, '-3.40282347E+38'::float4::numeric
----
340282350000000000000000000000000000000  -340282350000000000000000000000000000000

query error function casting real to numeric is only defined for finite arguments
SELECT 'infinity'::float4::numeric

query error numeric field overflow
SELECT '12.34'::float4::numeric(39,38);

# numeric to float8
query RRRR
SELECT 1.234::float8, 1234.567891234567::float8, 0.000000000000000123456789123456789012345::float8, -0.000000000000000123456789123456789012345::float8
----
1.234  1234.567891234567  0.00000000000000012345678912345679  -0.00000000000000012345678912345679

query RRR
SELECT 0.000::float8, 3.40282347E+38::float8, -3.40282347E+38::float8
----
0  340282347000000000000000000000000000000  -340282347000000000000000000000000000000

query R
SELECT 'NaN'::numeric::float8
----
NaN

query RR
SELECT 9E-39::float8, 9E+38::float8;
----
0.000000000000000000000000000000000000009  900000000000000000000000000000000000000

# float8 to numeric
query RRR
SELECT 1.23::float8::numeric, -1.23::float8::numeric, 1.23::float8::numeric(38,1)
----
1.23  -1.23  1.2

query RR
SELECT 'NaN'::float8::numeric, '-NaN'::float8::numeric;
----
NaN NaN

query RR
SELECT '3.40282347E+38'::float8::numeric, '-3.40282347E+38'::float8::numeric
----
340282347000000000000000000000000000000  -340282347000000000000000000000000000000

query error numeric field overflow
SELECT '1.7976931348623157E+308'::float8::numeric

query error numeric field overflow
SELECT '-1.7976931348623157E+308'::float8::numeric

query error function casting double precision to numeric is only defined for finite arguments
SELECT 'infinity'::float8::numeric

query error numeric field overflow
SELECT '12.34'::float8::numeric(39,38);

# jsonb to numeric
# - note that these just defer to other casts, so don't need as-extensive of testing

query R
SELECT ('1'::jsonb)::numeric;
----
1

query R
SELECT ('1.2'::jsonb)::numeric;
----
1.2

query error cannot cast jsonb string to type numeric
SELECT ('"Infinity"'::jsonb)::numeric;

query error cannot cast jsonb string to type numeric
SELECT ('"-Infinity"'::jsonb)::numeric;

query error cannot cast jsonb string to type numeric
SELECT ('"NaN"'::jsonb)::numeric;

# not a number
query error cannot cast jsonb array to type numeric
SELECT ('[1]'::jsonb)::numeric;

# not a number
query error cannot cast jsonb string to type numeric
SELECT ('"1"'::jsonb)::numeric;

# Addition
query R
SELECT 1::numeric + 2::numeric
----
3

query R
SELECT 1.23 + 2.34
----
3.57

query R
SELECT 1.23 + -2.34
----
-1.11

query R
SELECT 1.23 + -2.34
----
-1.11

query R
SELECT 1.23 + -1.23
----
0

query R
SELECT 3402823669209384634633746074317682 + 3402823669209384634633746074317682::numeric
----
6805647338418769269267492148635364

query R
SELECT 3402823669209384.634633746074317682 + 3402823669209384.634633746074317682::numeric
----
6805647338418769.269267492148635364

# Values rounded to fit precision
query R
SELECT 1e38 + 1e-39
----
100000000000000000000000000000000000000

# Limited precision means losing commutativity
query R
SELECT 1e38 + 1e-39 + -1e38
----
0

query R
SELECT 1e38 + -1e38 + 1e-39
----
0.000000000000000000000000000000000000001

query error value out of range: overflow
SELECT 999999999999999999999999999999999999999 + 1::numeric

query error value out of range: overflow
SELECT 790123449679012344967901234496790123392 + 790123449679012344967901234496790123392::numeric

query R
SELECT 'NaN'::numeric + 2::numeric
----
NaN

# Subtraction
query R
SELECT 1::numeric - 2::numeric
----
-1

query R
SELECT 1.23 - 2.34
----
-1.11

query R
SELECT 1.23 - -2.34
----
3.57

query R
SELECT -1.23 - -2.34
----
1.11

query R
SELECT -(1.23 - 2.34)
----
1.11

query R
SELECT 1.23 - 1.23
----
0

query R
SELECT -3402823669209384634633746074317682 - 3402823669209384634633746074317682::numeric
----
-6805647338418769269267492148635364

query error value out of range: overflow
SELECT -790123449679012344967901234496790123392 - 790123449679012344967901234496790123392::numeric

query R
SELECT 'NaN'::numeric - 2::numeric
----
NaN

# Limited precision means losing commutativity
query R
SELECT 1e38 - 1e-39 - 1e38
----
0

query R
SELECT 1e38 - 1e38 - 1e-39
----
-0.000000000000000000000000000000000000001

# Multiplication
query R
SELECT 1.1 * 2.2
----
2.42

query R
SELECT 1.1 * -2.2
----
-2.42

query R
SELECT -1.1 * 2.2
----
-2.42

query R
SELECT -1.1 * -2.2
----
2.42

query R
SELECT -1.1 * .2
----
-0.22

query R
SELECT .1 * -2.2
----
-0.22

query R
SELECT -(.1 * 2.2)
----
-0.22

query error value out of range: overflow
SELECT 123456789012345678901234567890123456789 * 10::numeric

query error value out of range: underflow
SELECT 1E-39 * .1

# Results are rounded to 39 digits of precision
query R
SELECT .123456789012345678901234567890123456789 * .1
----
0.012345678901234567890123456789012345679

query R
SELECT 3402823669209384.634633746074317682 * 3402823669209384.634633746074317682
----
11579208923731619542357098500868.7900057

# known bad behavior in old i128 implementation
query R
SELECT 1.50000000 * 1.50000000
----
2.25

query R
SELECT 'NaN'::numeric * 2::numeric
----
NaN

query R
SELECT 0::numeric * -1::numeric
----
0

# Division
query R
SELECT 1::numeric / 2::numeric;
----
0.5

query R
SELECT 2.0 / 1;
----
2

query R
SELECT 1 / 0.5;
----
2

query R
SELECT 2.0 / 1.1;
----
1.81818181818181818181818181818181818182

query R
SELECT 1::numeric / 11;
----
0.090909090909090909090909090909090909091

query error value out of range: overflow
SELECT 1::numeric / 0.000000000000000000000000000000000000001;

query error value out of range: overflow
SELECT 123456789012345678901234567890123456789 / .1

query error value out of range: underflow
SELECT 1E-39 / 10::numeric

# Round results to 39 digits of precision
query R
SELECT .123456789012345678901234567890123456789 / 10::numeric
----
0.012345678901234567890123456789012345679

query R
SELECT 11579208923731619542357098500868.7900057 / 3402823669209384.634633746074317682
----
3402823669209384.63463374607431768200001

query error division by zero
SELECT 1::numeric / 0::numeric

query error division by zero
SELECT 1::numeric / 0.0;

query error division by zero
SELECT 1::numeric / (-1::numeric + 1.0);

query error division by zero
SELECT 0::numeric / 0::numeric;

query R
SELECT 1 / 1.21035
----
0.826207295410418473995125376957078531003

query R
SELECT 'NaN'::numeric / 2::numeric
----
NaN

query R
SELECT 2::numeric / 'NaN'::numeric
----
NaN

query R
SELECT 0::numeric / -1::numeric
----
0

# mod

query R
SELECT 3::numeric % 2::numeric
----
1

query R
SELECT 23 % 4::numeric
----
3

query R
SELECT 4::numeric % 2::numeric
----
0

query R
SELECT 17.8 % 4::numeric
----
1.8

query R
SELECT 20.1 % 4.1
----
3.7

query R
SELECT -16.3 % 4.1
----
-4

query R
SELECT 20.1 % -4.1
----
3.7

query R
SELECT -20.1 % -4.1
----
-3.7

query R
SELECT 'NaN'::numeric % -4.1
----
NaN

query R
SELECT -20.1 % 'NaN'::numeric
----
NaN

query error division by zero
SELECT 2::numeric % 0::numeric

# Specify scale
query R
SELECT 0.2::numeric(39,1);
----
0.2

query R
SELECT 0.2::numeric(39,3);
----
0.2

query R
SELECT 0.002::numeric(39,1);
----
0

query R
SELECT 0.12::numeric(39,2) + 0.2::numeric(39,1);
----
0.32

query R
SELECT 0.12::numeric(39,1) + 0.2::numeric(39,2);
----
0.3

query R
SELECT (0.12 + 0.2)::numeric(39,3);
----
0.32

query R
SELECT (0.12 + 0.2)::numeric(39,1);
----
0.3

query R
SELECT 0.12::numeric(39,2) * 0.2::numeric(39,1);
----
0.024

query R
SELECT 0.12::numeric(39,1) * 0.2::numeric(39,2);
----
0.02

query R
SELECT (0.12 * 0.2)::numeric(39,3);
----
0.024

query R
SELECT (0.14 * 0.2)::numeric(39,2);
----
0.03

query R
SELECT (0.12 * 0.2)::numeric(39,1);
----
0

query R
SELECT 'NaN'::numeric(39,1);
----
NaN

query error precision for type numeric must be between 1 and 39
SELECT 0.2::numeric(40,1);

query error scale for type numeric must be between 0 and precision 39
SELECT 0.2::numeric(39,40);

query error scale for type numeric must be between 0 and precision 1
SELECT 0.2::numeric(1,39);

query error numeric field overflow
SELECT (12345678901234567890::numeric(39,3) * 12345678901234567890::numeric(39,3))::numeric(39,3);

# Adding a scale "prevents" rounding
## This value is within range, but requires 37 digits of precision left of the decimal
query R
SELECT 98765432109876543210987654321098765432.109
----
98765432109876543210987654321098765432.1

## When expressly requesting scale of 3, only have 36 digits of precision left of the decimal
query error numeric field overflow
SELECT 98765432109876543210987654321098765432.109::numeric(39,3)

query error numeric field overflow
SELECT 1::numeric(39,39)

## Multiplication w/ numeric field overflow
query R
SELECT 1234567890123456789.012345 * 1234567890123456789.012345;
----
1524157875323883675049533479957338669.12

query error numeric field overflow
SELECT (1234567890123456789.012345 * 1234567890123456789.012345)::numeric(39,3);

## Addition w/ numeric field overflow
query R
SELECT 999999999999999999999999999999999999.123 + 1::numeric
----
1000000000000000000000000000000000000.12

query error numeric field overflow
SELECT (999999999999999999999999999999999999.123 + 1::numeric)::numeric(39,3);

# Successive operations

## regression test for database-issues#2137
query R
SELECT -0.0 - 1::numeric  * '-0.0 '::numeric;
----
0

# Negate is nop on NaN and 0
query R
SELECT -'NaN'::numeric;
----
NaN

query R
SELECT -0::numeric;
----
0

query R
SELECT -0.00;
----
0

query R
SELECT -(0.1 - 0.10);
----
0

# Abs
query R
SELECT abs(-1::numeric);
----
1

query R
SELECT abs(-1::numeric);
----
1

query R
SELECT abs(-7e-38);
----
0.00000000000000000000000000000000000007

query R
SELECT abs(1::numeric - 1e38);
----
99999999999999999999999999999999999999

query R
SELECT abs(-0.00);
----
0

query R
SELECT abs('NaN'::numeric);
----
NaN

# round

query RR
SELECT round(1.4), round(1.5)
----
1  2

query RR
SELECT round(-1.4), round(-1.5)
----
-1  -2

query R
SELECT round('NaN'::numeric)
----
NaN

query R
SELECT round(1.29, 1)
----
1.3

query R
SELECT round(1.23, 4)
----
1.23

query R
SELECT round(.1234567890123456789012345678901234567890, 1)
----
0.1

query R
SELECT round(.1234567890123456789012345678901234567890, 39)
----
0.123456789012345678901234567890123456789

query R
SELECT round(.1234567890123456789012345678901234567890, 41)
----
0.123456789012345678901234567890123456789

query R
SELECT round(.1234567890123456789012345678901234567, 37)
----
0.1234567890123456789012345678901234567

query R
SELECT round(.1234567890123456789012345678901234567, 39)
----
0.1234567890123456789012345678901234567

query R
SELECT round(6e38, 39)
----
600000000000000000000000000000000000000

query R
SELECT round(19.87, -1)
----
20

query R
SELECT round(99.9, -1)
----
100

query R
SELECT round(123456789012345.6789012345678901234567890, -7)
----
123456790000000

query R
SELECT round(123456789012345.6789012345678901234567890, -16)
----
0

query R
SELECT round('5.6e10'::numeric, -10);
----
60000000000

query R
SELECT round('5.6e10'::numeric, 10);
----
56000000000

query R
SELECT round('9.876e38'::numeric, -37);
----
990000000000000000000000000000000000000

# properly rounded value exceeds max precision
query error value out of range: overflow
SELECT round('9.876e38'::numeric, -38);

query error value out of range: overflow
SELECT round(9e38, -39);

# however, if the "place" argument exceeds the number of digits, the result is
# zero
query R
SELECT round(9e38, -40);
----
0

query R
SELECT round (-0.10212864, -900)
----
0

# ceil

query RRR
SELECT ceil(1.234), ceil(-1.234), ceil('NaN'::numeric)
----
2  -1  NaN

query R
SELECT ceil(-0.6);
----
0

# floor

query RRR
SELECT floor(1.234), floor(-1.234), floor('NaN'::numeric)
----
1  -2  NaN

#trunc

query RRR
SELECT trunc(1.234), trunc(-1.234), trunc('NaN'::numeric)
----
1  -1  NaN

query RR
SELECT trunc(0.6), trunc(-0.6)
----
0  0

# Exponential calculations

## Pow
query RR
SELECT pow(2::numeric, 3::numeric), pow(2.5, -3.5)
----
8 0.040477154050155255449585837368738797232

query RR
SELECT pow(1::numeric, 9e38), pow(9e38, 1::numeric)
----
1  900000000000000000000000000000000000000

query RR
SELECT pow(0::numeric, 0::numeric), pow(0.00, -0.000)
----
1  1

query RR
SELECT pow(1::numeric, 9e-39), pow(9e-39, 1::numeric)
----
1  0.000000000000000000000000000000000000009

query RR
SELECT pow('NaN'::numeric, -2.5), pow(-2.5, 'NaN'::numeric);
----
NaN NaN

query error value out of range: overflow
SELECT pow(999::numeric, 9999::numeric);

query error value out of range: overflow
SELECT pow(-2::numeric, 2111176704::numeric)::text;

query error value out of range: underflow
SELECT pow(999::numeric, -9999::numeric);

query error value out of range: underflow
SELECT pow(-2::numeric, -2111176704::numeric)::text;

query error zero raised to a negative power is undefined
SELECT pow(0::numeric, -1::numeric)

query error function pow cannot return complex numbers
SELECT pow(-1::numeric, '-.1'::numeric)

# Square root
query RRR
SELECT sqrt(2::numeric), sqrt(3::numeric), sqrt(0::numeric)
----
1.41421356237309504880168872420969807857  1.73205080756887729352744634150587236694  0

query R
SELECT sqrt('.98765432109876543210'::numeric)
----
0.993807990055808231173954156543476014651

query RR
SELECT sqrt(9e38), sqrt(9e-39)
----
30000000000000000000  0.00000000000000000009486832980505137996

query error cannot take square root of a negative number
SELECT sqrt(-2::numeric)

## Exp, i.e. e^x
query R
SELECT exp(1::numeric)
----
2.71828182845904523536028747135266249776

query R
SELECT exp(2::numeric)
----
7.38905609893065022723042746057500781318

query R
SELECT exp(2.0)
----
7.38905609893065022723042746057500781318

query R
SELECT exp(-1::numeric)
----
0.367879441171442321595523770161460867446

query R
SELECT exp(-1.0)
----
0.367879441171442321595523770161460867446

query R
SELECT exp(1.5)
----
4.48168907033806482260205546011927581901

query R
SELECT exp('.5'::numeric)
----
1.64872127070012814684865078781416357165

query R
SELECT exp('NaN'::numeric)
----
NaN

query error value out of range: overflow
SELECT exp(50000::numeric)

query error value out of range: underflow
SELECT exp(-50000::numeric)

# Logarithmic calculations

query RRR
SELECT ln(2.0), ln(2.5), ln(5000::numeric)
----
0.693147180559945309417232121458176568076  0.91629073187415506518352721176801107145  8.51719319141623742665473369727928026233

query error function ln is not defined for negative numbers
SELECT ln(-100.000)

query error function ln is not defined for zero
SELECT ln(0::numeric)

query RRR
SELECT log(10.0), log(100.000), log(5000::numeric)
----
1  2  3.69897000433601880478626110527550697323

query R
SELECT ln(exp(2::numeric))
----
2

query error function log10 is not defined for negative numbers
SELECT log(-100.000)

query error function log10 is not defined for zero
SELECT log(0::numeric)

# These results used to end up with some errant digits in the least significant digit.
query R
SELECT log(2::numeric, 64)
----
6

query R
SELECT log(4::numeric, 4096);
----
6

query R
SELECT log(6::numeric, 279936);
----
7

query R
SELECT log(6::numeric, 1679616);
----
8

query R
SELECT log(8::numeric, 16777216);
----
8

query R
SELECT log(8::numeric, 134217728);
----
9

# This result has never been incorrect
query R
SELECT log(2::numeric, 128)
----
7

query R
SELECT log(128, 2::numeric)
----
0.142857142857142857142857142857142857143

query R
SELECT log(9e38, 1e-39)
----
-1.00117464716582763094927800807557636035

query R
SELECT log(1e-39, 9e38)
----
-0.99882673101126474037410399503872386201

query error function log is not defined for zero
SELECT log(0::numeric, 2::numeric)

query error function log is not defined for negative numbers
SELECT log(-1::numeric, 2::numeric)

query error function log is not defined for zero
SELECT log(128, 0::numeric)

query error function log is not defined for negative numbers
SELECT log(128, -1::numeric)

query error division by zero
select log(1::numeric, 2::numeric);

# Equality
# Tests binary combination of values from set {0, 0.00, 1, 1.00, 1.2, -1.00, NaN}
# including self-combinations using each equality operator

query T
SELECT 0::numeric = 0::numeric;
----
true

query T
SELECT 0::numeric < 0::numeric;
----
false

query T
SELECT 0::numeric <= 0::numeric;
----
true

query T
SELECT 0::numeric > 0::numeric;
----
false

query T
SELECT 0::numeric >= 0::numeric;
----
true

query T
SELECT 0::numeric <> 0::numeric;
----
false

query T
SELECT 0::numeric = 0.00;
----
true

query T
SELECT 0::numeric < 0.00;
----
false

query T
SELECT 0::numeric <= 0.00;
----
true

query T
SELECT 0::numeric > 0.00;
----
false

query T
SELECT 0::numeric >= 0.00;
----
true

query T
SELECT 0::numeric <> 0.00;
----
false

query T
SELECT 0::numeric = 1::numeric;
----
false

query T
SELECT 0::numeric < 1::numeric;
----
true

query T
SELECT 0::numeric <= 1::numeric;
----
true

query T
SELECT 0::numeric > 1::numeric;
----
false

query T
SELECT 0::numeric >= 1::numeric;
----
false

query T
SELECT 0::numeric <> 1::numeric;
----
true

query T
SELECT 0::numeric = 1.00;
----
false

query T
SELECT 0::numeric < 1.00;
----
true

query T
SELECT 0::numeric <= 1.00;
----
true

query T
SELECT 0::numeric > 1.00;
----
false

query T
SELECT 0::numeric >= 1.00;
----
false

query T
SELECT 0::numeric <> 1.00;
----
true

query T
SELECT 0::numeric = 1.2;
----
false

query T
SELECT 0::numeric < 1.2;
----
true

query T
SELECT 0::numeric <= 1.2;
----
true

query T
SELECT 0::numeric > 1.2;
----
false

query T
SELECT 0::numeric >= 1.2;
----
false

query T
SELECT 0::numeric <> 1.2;
----
true

query T
SELECT 0::numeric = -1.00;
----
false

query T
SELECT 0::numeric < -1.00;
----
false

query T
SELECT 0::numeric <= -1.00;
----
false

query T
SELECT 0::numeric > -1.00;
----
true

query T
SELECT 0::numeric >= -1.00;
----
true

query T
SELECT 0::numeric <> -1.00;
----
true

query T
SELECT 0::numeric = 'NaN'::numeric;
----
false

query T
SELECT 0::numeric < 'NaN'::numeric;
----
true

query T
SELECT 0::numeric <= 'NaN'::numeric;
----
true

query T
SELECT 0::numeric > 'NaN'::numeric;
----
false

query T
SELECT 0::numeric >= 'NaN'::numeric;
----
false

query T
SELECT 0::numeric <> 'NaN'::numeric;
----
true

query T
SELECT 0.00 = 0.00;
----
true

query T
SELECT 0.00 < 0.00;
----
false

query T
SELECT 0.00 <= 0.00;
----
true

query T
SELECT 0.00 > 0.00;
----
false

query T
SELECT 0.00 >= 0.00;
----
true

query T
SELECT 0.00 <> 0.00;
----
false

query T
SELECT 0.00 = 1::numeric;
----
false

query T
SELECT 0.00 < 1::numeric;
----
true

query T
SELECT 0.00 <= 1::numeric;
----
true

query T
SELECT 0.00 > 1::numeric;
----
false

query T
SELECT 0.00 >= 1::numeric;
----
false

query T
SELECT 0.00 <> 1::numeric;
----
true

query T
SELECT 0.00 = 1.00;
----
false

query T
SELECT 0.00 < 1.00;
----
true

query T
SELECT 0.00 <= 1.00;
----
true

query T
SELECT 0.00 > 1.00;
----
false

query T
SELECT 0.00 >= 1.00;
----
false

query T
SELECT 0.00 <> 1.00;
----
true

query T
SELECT 0.00 = 1.2;
----
false

query T
SELECT 0.00 < 1.2;
----
true

query T
SELECT 0.00 <= 1.2;
----
true

query T
SELECT 0.00 > 1.2;
----
false

query T
SELECT 0.00 >= 1.2;
----
false

query T
SELECT 0.00 <> 1.2;
----
true

query T
SELECT 0.00 = -1.00;
----
false

query T
SELECT 0.00 < -1.00;
----
false

query T
SELECT 0.00 <= -1.00;
----
false

query T
SELECT 0.00 > -1.00;
----
true

query T
SELECT 0.00 >= -1.00;
----
true

query T
SELECT 0.00 <> -1.00;
----
true

query T
SELECT 0.00 = 'NaN'::numeric;
----
false

query T
SELECT 0.00 < 'NaN'::numeric;
----
true

query T
SELECT 0.00 <= 'NaN'::numeric;
----
true

query T
SELECT 0.00 > 'NaN'::numeric;
----
false

query T
SELECT 0.00 >= 'NaN'::numeric;
----
false

query T
SELECT 0.00 <> 'NaN'::numeric;
----
true

query T
SELECT 1::numeric = 1::numeric;
----
true

query T
SELECT 1::numeric < 1::numeric;
----
false

query T
SELECT 1::numeric <= 1::numeric;
----
true

query T
SELECT 1::numeric > 1::numeric;
----
false

query T
SELECT 1::numeric >= 1::numeric;
----
true

query T
SELECT 1::numeric <> 1::numeric;
----
false

query T
SELECT 1::numeric = 1.00;
----
true

query T
SELECT 1::numeric < 1.00;
----
false

query T
SELECT 1::numeric <= 1.00;
----
true

query T
SELECT 1::numeric > 1.00;
----
false

query T
SELECT 1::numeric >= 1.00;
----
true

query T
SELECT 1::numeric <> 1.00;
----
false

query T
SELECT 1::numeric = 1.2;
----
false

query T
SELECT 1::numeric < 1.2;
----
true

query T
SELECT 1::numeric <= 1.2;
----
true

query T
SELECT 1::numeric > 1.2;
----
false

query T
SELECT 1::numeric >= 1.2;
----
false

query T
SELECT 1::numeric <> 1.2;
----
true

query T
SELECT 1::numeric = -1.00;
----
false

query T
SELECT 1::numeric < -1.00;
----
false

query T
SELECT 1::numeric <= -1.00;
----
false

query T
SELECT 1::numeric > -1.00;
----
true

query T
SELECT 1::numeric >= -1.00;
----
true

query T
SELECT 1::numeric <> -1.00;
----
true

query T
SELECT 1::numeric = 'NaN'::numeric;
----
false

query T
SELECT 1::numeric < 'NaN'::numeric;
----
true

query T
SELECT 1::numeric <= 'NaN'::numeric;
----
true

query T
SELECT 1::numeric > 'NaN'::numeric;
----
false

query T
SELECT 1::numeric >= 'NaN'::numeric;
----
false

query T
SELECT 1::numeric <> 'NaN'::numeric;
----
true

query T
SELECT 1.00 = 1.00;
----
true

query T
SELECT 1.00 < 1.00;
----
false

query T
SELECT 1.00 <= 1.00;
----
true

query T
SELECT 1.00 > 1.00;
----
false

query T
SELECT 1.00 >= 1.00;
----
true

query T
SELECT 1.00 <> 1.00;
----
false

query T
SELECT 1.00 = 1.2;
----
false

query T
SELECT 1.00 < 1.2;
----
true

query T
SELECT 1.00 <= 1.2;
----
true

query T
SELECT 1.00 > 1.2;
----
false

query T
SELECT 1.00 >= 1.2;
----
false

query T
SELECT 1.00 <> 1.2;
----
true

query T
SELECT 1.00 = -1.00;
----
false

query T
SELECT 1.00 < -1.00;
----
false

query T
SELECT 1.00 <= -1.00;
----
false

query T
SELECT 1.00 > -1.00;
----
true

query T
SELECT 1.00 >= -1.00;
----
true

query T
SELECT 1.00 <> -1.00;
----
true

query T
SELECT 1.00 = 'NaN'::numeric;
----
false

query T
SELECT 1.00 < 'NaN'::numeric;
----
true

query T
SELECT 1.00 <= 'NaN'::numeric;
----
true

query T
SELECT 1.00 > 'NaN'::numeric;
----
false

query T
SELECT 1.00 >= 'NaN'::numeric;
----
false

query T
SELECT 1.00 <> 'NaN'::numeric;
----
true

query T
SELECT 1.2 = 1.2;
----
true

query T
SELECT 1.2 < 1.2;
----
false

query T
SELECT 1.2 <= 1.2;
----
true

query T
SELECT 1.2 > 1.2;
----
false

query T
SELECT 1.2 >= 1.2;
----
true

query T
SELECT 1.2 <> 1.2;
----
false

query T
SELECT 1.2 = -1.00;
----
false

query T
SELECT 1.2 < -1.00;
----
false

query T
SELECT 1.2 <= -1.00;
----
false

query T
SELECT 1.2 > -1.00;
----
true

query T
SELECT 1.2 >= -1.00;
----
true

query T
SELECT 1.2 <> -1.00;
----
true

query T
SELECT 1.2 = 'NaN'::numeric;
----
false

query T
SELECT 1.2 < 'NaN'::numeric;
----
true

query T
SELECT 1.2 <= 'NaN'::numeric;
----
true

query T
SELECT 1.2 > 'NaN'::numeric;
----
false

query T
SELECT 1.2 >= 'NaN'::numeric;
----
false

query T
SELECT 1.2 <> 'NaN'::numeric;
----
true

query T
SELECT -1.00 = -1.00;
----
true

query T
SELECT -1.00 < -1.00;
----
false

query T
SELECT -1.00 <= -1.00;
----
true

query T
SELECT -1.00 > -1.00;
----
false

query T
SELECT -1.00 >= -1.00;
----
true

query T
SELECT -1.00 <> -1.00;
----
false

query T
SELECT -1.00 = 'NaN'::numeric;
----
false

query T
SELECT -1.00 < 'NaN'::numeric;
----
true

query T
SELECT -1.00 <= 'NaN'::numeric;
----
true

query T
SELECT -1.00 > 'NaN'::numeric;
----
false

query T
SELECT -1.00 >= 'NaN'::numeric;
----
false

query T
SELECT -1.00 <> 'NaN'::numeric;
----
true

query T
SELECT 'NaN'::numeric = 'NaN'::numeric;
----
true

query T
SELECT 'NaN'::numeric < 'NaN'::numeric;
----
false

query T
SELECT 'NaN'::numeric <= 'NaN'::numeric;
----
true

query T
SELECT 'NaN'::numeric > 'NaN'::numeric;
----
false

query T
SELECT 'NaN'::numeric >= 'NaN'::numeric;
----
true

query T
SELECT 'NaN'::numeric <> 'NaN'::numeric;
----
false

# NaN is larger than large numbers
query T
SELECT 'NaN'::numeric > 9e38;
----
true

# Scale doesn't affect equality
query T
SELECT 9::numeric(39,5) = 9::numeric;
----
true

query T
SELECT 9::numeric(39,5) = 9::numeric(39,10);
----
true

# Values are rounded before comparison
query T
SELECT 123456789012345678901234567890123456789.0 = 123456789012345678901234567890123456789.4999;
----
true

query T
SELECT 1.00123::numeric(39,2) = 1::numeric;
----
true

query T
SELECT 1.23456789::numeric(39,2) < 1.23456789::numeric(39,3);
----
true

query T
SELECT 1.23456789::numeric(39,5) < 1.23456789::numeric(39,4);
----
true

query R
select max(a) from (select a from unnest(ARRAY[1.4::numeric(39,5), 1.8::numeric(39,4)]) a);
----
1.8

query R
select min(a) from (select a from unnest(ARRAY[1.4::numeric(39,5), 1.8::numeric(39,4)]) a);
----
1.4

query R
select max(a) from (select a from unnest(ARRAY['NaN'::numeric(39,5), 1.4::numeric(39,5), 1.8::numeric(39,4)]) a);
----
NaN

query R
select min(a) from (select a from unnest(ARRAY['NaN'::numeric(39,5), 1.4::numeric(39,5), 1.8::numeric(39,4)]) a);
----
1.4

query R
select max(a) from (select a from unnest(ARRAY[]::numeric[]) a);
----
NULL

query R
select min(a) from (select a from unnest(ARRAY[]::numeric[]) a);
----
NULL

# Distinct, except
# regression tests for database-issues#2145 database-issues#2152

query R
SELECT DISTINCT column1 FROM (
    VALUES
        (0.1),
        (0.10),
        (0.100),
        (0.1000),
        (0.10000)
);
----
0.1

query R
SELECT column1 FROM (
    SELECT * FROM (
        VALUES (0.1)
    ) EXCEPT ALL
        SELECT * FROM (
            VALUES (0.1000)
        )
)
----

# regression test for https://github.com/MaterializeInc/database-issues/issues/2298
statement ok
CREATE TABLE A (f1 DECIMAL);

statement ok
INSERT INTO A VALUES ('-999999999999999999999999999999999999999'), ('-999999999999999999999999999999999999999');

query R
SELECT sum(f1) FROM A;
----
-Infinity

# sqllogictest rounding/truncation behavior.
#
# Note: this behavior is called out in the developer docs, so if it ever changes be sure to update
# those docs.

query I
SELECT '1.9'::numeric
----
2

query I
SELECT '1.5'::numeric
----
2

query I
SELECT '1.1'::numeric
----
1

mode standard

# For SQLite compatibility, we truncate Numerics when interpreting as an Int.

query I
SELECT '1.9'::numeric
----
1

query I
SELECT '1.5'::numeric
----
1

query I
SELECT '1.1'::numeric
----
1