Materialize/test/sqllogictest/postgres/float4.slt

# Copyright 1994, Regents of the University of California.
# Copyright 1996-2019 PostgreSQL Global Development Group.
# 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 regression test suite in PostgreSQL.
# The original file was retrieved on February 10, 2021 from:
#
#     https://github.com/postgres/postgres/blob/64990081504661ff5c04dbf20cc4252be66ab149/src/test/regress/expected/float4.out
#
# The original source code is subject to the terms of the PostgreSQL
# license, a copy of which can be found in the LICENSE file at the
# root of this repository.

mode cockroach

statement ok
CREATE TABLE float4_tbl (f1 float4)

statement ok
INSERT INTO float4_tbl (f1) VALUES ('    0.0'), ('1004.30   '), ('     -34.84    '), ('1.2345678901234e+20'), ('1.2345678901234e-20')

# test for over and under flow
query error "10e70" is out of range for type real
SELECT '10e70'::float4

query error "-10e70" is out of range for type real
SELECT '-10e70'::float4

query error "10e-70" is out of range for type real
SELECT '10e-70'::float4

query error "-10e-70" is out of range for type real
SELECT '-10e-70'::float4

query error value out of range: overflow
SELECT '10e70'::float8::float4

query error value out of range: overflow
SELECT '-10e70'::float8::float4

query error value out of range: underflow
SELECT '10e-70'::float8::float4

query error value out of range: underflow
SELECT '-10e-70'::float8::float4

query error "10e400" is out of range for type real
SELECT '10e400'::float4

query error "-10e400" is out of range for type real
SELECT '-10e400'::float4

query error "10e-400" is out of range for type real
SELECT '10e-400'::float4

query error "-10e-400" is out of range for type real
SELECT '-10e-400'::float4

query error invalid input syntax for type real: ""
SELECT ''::float4

query error invalid input syntax for type real: "       "
SELECT '       '::float4

query error invalid input syntax for type real: "xyz"
SELECT 'xyz'::float4

query error invalid input syntax for type real: "5.0.0"
SELECT '5.0.0'::float4

query error invalid input syntax for type real: "5 . 0"
SELECT '5 . 0'::float4

query error invalid input syntax for type real: "5.   0"
SELECT '5.   0'::float4

query error invalid input syntax for type real: "     - 3.0"
SELECT '     - 3.0'::float4

query error invalid input syntax for type real: "123            5"
SELECT '123            5'::float4

query T
SELECT 'NaN'::float4::text
----
NaN

query T
SELECT 'nan'::float4::text
----
NaN

query T
SELECT '   NAN  '::float4::text
----
NaN

query T
SELECT 'infinity'::float4::text
----
Infinity

query T
SELECT '          -INFINiTY   '::float4::text
----
-Infinity

query error invalid input syntax for type real: "N A N"
SELECT 'N A N'::float4

query error invalid input syntax for type real: "NaN x"
SELECT 'NaN x'::float4;

query error invalid input syntax for type real: " INFINITY    x"
SELECT ' INFINITY    x'::float4

query T
SELECT ('Infinity'::float4 + 100.0)::text
----
Infinity

query T
SELECT ('Infinity'::float4 / 'Infinity'::float4)::text
----
NaN

query T
SELECT ('42'::float4 / 'Infinity'::float4)::text
----
0

query T
SELECT ('nan'::float4 / 'nan'::float4)::text
----
NaN

query T
SELECT ('nan'::float4 / '0'::float4)::text
----
NaN

# TODO(benesch): re-enable when the numeric type supports NaN.
#
# query R
# SELECT 'nan'::numeric::float4;
# ----
# NaN

query T rowsort
SELECT f1::text FROM float4_tbl
----
0
1004.3
-34.84
1.2345679e+20
1.2345679e-20

query T rowsort
SELECT f1::text FROM float4_tbl WHERE f1 <> '1004.3'
----
0
-34.84
1.2345679e+20
1.2345679e-20

query T
SELECT f1::text FROM float4_tbl WHERE f1 = '1004.3'
----
1004.3

query T rowsort
SELECT f1::text FROM float4_tbl WHERE '1004.3' > f1
----
0
-34.84
1.2345679e-20

query T rowsort
SELECT f1::text FROM float4_tbl WHERE  f1 < '1004.3'
----
0
-34.84
1.2345679e-20

query T rowsort
SELECT f1::text FROM float4_tbl WHERE '1004.3' >= f1
----
0
1004.3
-34.84
1.2345679e-20

query T rowsort
SELECT f1::text FROM float4_tbl WHERE  f1 <= '1004.3'
----
0
1004.3
-34.84
1.2345679e-20

query TT rowsort
SELECT f.f1::text, (f.f1 * '-10')::text AS x FROM float4_tbl f
WHERE f.f1 > '0.0'
----
1004.3         -10043
1.2345679e+20  -1.2345678e+21
1.2345679e-20  -1.2345678e-19

query TT rowsort
SELECT f.f1::text, (f.f1 + '-10')::text AS x FROM float4_tbl f
WHERE f.f1 > '0.0'
----
1004.3         994.3
1.2345679e+20  1.2345679e+20
1.2345679e-20  -10

query TT rowsort
SELECT f.f1::text, (f.f1 / '-10')::text AS x FROM float4_tbl f
WHERE f.f1 > '0.0'
----
1004.3         -100.43
1.2345679e+20  -1.2345679e+19
1.2345679e-20  -1.2345679e-21

query TT rowsort
SELECT f.f1::text, (f.f1 - '-10')::text AS x FROM float4_tbl f
WHERE f.f1 > '0.0'
----
1004.3         1014.3
1.2345679e+20  1.2345679e+20
1.2345679e-20  10

# test divide by zero
query error division by zero
SELECT f.f1 / '0.0' from float4_tbl f;

query T rowsort
SELECT f1::text FROM float4_tbl
----
0
1004.3
-34.84
1.2345679e+20
1.2345679e-20

# -- test the unary float4abs operator
# SELECT f.f1, @f.f1 AS abs_f1 FROM float4_tbl f;
#       f1       |    abs_f1
# ---------------+---------------
#              0 |             0
#         1004.3 |        1004.3
#         -34.84 |         34.84
#  1.2345679e+20 | 1.2345679e+20
#  1.2345679e-20 | 1.2345679e-20
# (5 rows)

statement ok
UPDATE float4_tbl SET f1 = float4_tbl.f1 * '-1' WHERE float4_tbl.f1 > '0.0'

query T rowsort
SELECT f1::text FROM float4_tbl
----
0
-34.84
-1004.3
-1.2345679e+20
-1.2345679e-20

# test edge-case coercions to integer

query I
SELECT '32767.4'::float4::int2;
----
32767

query error smallint out of range
SELECT '32767.6'::float4::int2;

query I
SELECT '-32768.4'::float4::int2;
----
-32768

query error smallint out of range
SELECT '-32768.6'::float4::int2;

query I
SELECT '2147483520'::float4::int4;
----
2147483520

query error "2147483600" integer out of range
SELECT '2147483647'::float4::int4

query I
SELECT '-2147483648.5'::float4::int4
----
-2147483648

query error "-2147484000" integer out of range
SELECT '-2147483900'::float4::int4

query I
SELECT '9223369837831520256'::float4::int8
----
9223369837831520256

query error "9223372000000000000" bigint out of range
SELECT '9223372036854775807'::float4::int8;

query I
SELECT '-9223372036854775808.5'::float4::int8;
----
-9223372036854775808

query error "-9223380000000000000" bigint out of range
SELECT '-9223380000000000000'::float4::int8

query T
SELECT '36854775807.0'::float4::int8::text
----
36854775808

query RI rowsort
SELECT x, x::int4 AS int4_value
FROM (VALUES (-2.5::float4),
             (-1.5::float4),
             (-0.5::float4),
             (0.0::float4),
             (0.5::float4),
             (1.5::float4),
             (2.5::float4)) t(x);
----
-2.5  -2
-1.5  -2
-0.5  0
0     0
0.5   0
1.5   2
2.5   2

query RI rowsort
SELECT x, x::int8 AS int8_value
FROM (VALUES (-2.5::float4),
             (-1.5::float4),
             (-0.5::float4),
             (0.0::float4),
             (0.5::float4),
             (1.5::float4),
             (2.5::float4)) t(x);
----
-2.5  -2
-1.5  -2
-0.5  0
0     0
0.5   0
1.5   2
2.5   2