Materialize/test/sqllogictest/float.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.

mode cockroach

query T
SELECT '-0'::float::text
----
-0

query T
SELECT '+0'::float::text
----
0

query T
SELECT '000000.00000'::float::text
----
0

query T
SELECT '0.0e10'::float::text
----
0

query T
SELECT '0.'::float::text
----
0

query T
SELECT '.0'::float::text
----
0

query T
SELECT '-.0'::float::text
----
-0

query T
SELECT '+.0'::float::text
----
0

query T
SELECT '+0.'::float::text
----
0

query T
SELECT '-0.'::float::text
----
-0

query error invalid input syntax
SELECT '++0'::float::text

query error invalid input syntax
SELECT '--0'::float::text

query TTTTT
SELECT 'Inf'::float::text, 'Infinity'::float::text, 'inFinIty'::float::text, '+inf'::float::text, '+infinity'::float::text
----
Infinity  Infinity  Infinity  Infinity  Infinity

query TTT
SELECT '-Inf'::float::text, '-Infinity'::float::text, '-inFinIty'::float::text
----
-Infinity  -Infinity  -Infinity

query TTT
SELECT 'NaN'::float::text, 'nan'::float::text, 'nAN'::float::text
----
NaN  NaN  NaN

query TTTTT
SELECT 'Inf'::double precision::text, 'Infinity'::double precision::text, 'inFinIty'::double precision::text, '+inf'::double precision::text, '+infinity'::double precision::text
----
Infinity  Infinity  Infinity  Infinity  Infinity

query TTT
SELECT '-Inf'::double precision::text, '-Infinity'::double precision::text, '-inFinIty'::double precision::text
----
-Infinity  -Infinity  -Infinity

query TTT
SELECT 'NaN'::double precision::text, 'nan'::double precision::text, 'nAN'::double precision::text
----
NaN  NaN  NaN

query R
SELECT 1.2::float8::float4
----
1.2

query R
SELECT '2e40'::float8
----
20000000000000000000000000000000000000000

query error value out of range: overflow
SELECT '2e40'::float8::float4

query R
SELECT '2147483583'::float4::int;
----
2147483520

query error "2147483600" integer out of range
SELECT '2147483648'::float4::int

query R
SELECT '-2147483648'::float4::int
----
-2147483648

query error "-2147484000" integer out of range
SELECT '-2147483777'::float4::int;

# Invalid type mods

query error precision for type float must be within \(\[1-53\]\)
SELECT 1::float(0);

query error precision for type float must be within \(\[1-53\]\)
SELECT 1::float(55);


# Test SUM() with floats

statement ok
create table t1 (f1 double, f2 double)

statement ok
insert into t1 values (1e31, 1e31), (1e31, 1e31)

query T
SELECT f1+f2 FROM t1
----
19999999999999999271792589930496.000
19999999999999999271792589930496.000

query T
SELECT SUM(f1) FROM t1
----
-282409603651671152154661355520.000

# This *should* be zero, known issue https://github.com/MaterializeInc/database-issues/issues/4341
query T
SELECT MIN(f1+f2)-SUM(f1) FROM t1
----
20282409603651670423947251286016.000

query error invalid input syntax
SELECT ''::float::text

query error invalid input syntax
SELECT 'e'::float::text

query error invalid input syntax
SELECT 'e10'::float::text