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

# -0 and 0 should not be possible in a unique index.

statement ok
CREATE TABLE p (f float null, unique index (f))

statement ok
INSERT INTO p VALUES (NULL), ('NaN'::float), ('Inf'::float), ('-Inf'::float), ('0'::float), (1), (-1)

# -0 and 0 should both equate to zero with or without an index.

statement error duplicate key value
INSERT INTO p VALUES ('-0'::float)

query R
SELECT * FROM p WHERE f = 'NaN'
----
NaN

query RBBB
SELECT f, f IS NaN, f = 'NaN', isnan(f) FROM p@{FORCE_INDEX=primary} ORDER BY 1
----
NULL  NULL   NULL   NULL
NaN   true   true   true
-Inf  false  false  false
-1    false  false  false
0     false  false  false
1     false  false  false
+Inf  false  false  false

query RBBB
SELECT f, f IS NaN, f = 'NaN', isnan(f) FROM p@{FORCE_INDEX=p_f_key} ORDER BY 1
----
NULL  NULL   NULL   NULL
NaN   true   true   true
-Inf  false  false  false
-1    false  false  false
0     false  false  false
1     false  false  false
+Inf  false  false  false

query RB
select f, f > 'NaN' from p@{FORCE_INDEX=primary} where f > 'NaN' ORDER BY f
----
-Inf  true
-1    true
0     true
1     true
+Inf  true

query RB
select f, f > 'NaN' from p@{FORCE_INDEX=p_f_key} where f > 'NaN' ORDER BY f
----
-Inf  true
-1    true
0     true
1     true
+Inf  true

statement ok
CREATE TABLE i (f float)

statement ok
INSERT INTO i VALUES (0), ('-0'::float)

query R rowsort
SELECT * FROM i WHERE f = 0
----
-0
0

statement ok
CREATE INDEX ON i (f)

query R rowsort
SELECT * FROM i WHERE f = 0
----
-0
0

statement error violates unique constraint
CREATE UNIQUE INDEX ON i (f)

subtest extra_float_digits

statement ok
CREATE TABLE vals(f FLOAT);
  INSERT INTO vals VALUES (0.0), (123.4567890123456789), (12345678901234567890000), (0.0001234567890123456789)

query RT rowsort
SELECT f, f::string FROM vals
----
0                     0
123.456789012346      123.456789012346
1.23456789012346e+22  1.23456789012346e+22
0.000123456789012346  0.000123456789012346

statement ok
SET extra_float_digits = 3

query RT rowsort
SELECT f, f::string FROM vals
----
0                       0
123.45678901234568      123.45678901234568
1.2345678901234568e+22  1.2345678901234568e+22
0.00012345678901234567  0.00012345678901234567

statement ok
SET extra_float_digits = -8

query RT rowsort
SELECT f, f::string FROM vals
----
0             0
123.4568      123.4568
1.234568e+22  1.234568e+22
0.0001234568  0.0001234568

statement ok
SET extra_float_digits = -15

query RT rowsort
SELECT f, f::string FROM vals
----
0       0
100     1e+02
1e+22   1e+22
0.0001  0.0001

statement ok
DROP TABLE vals

statement ok
RESET extra_float_digits