# 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