github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/float (about)

     1  # -0 and 0 should not be possible in a unique index.
     2  
     3  statement ok
     4  CREATE TABLE p (f float null, unique index (f))
     5  
     6  statement ok
     7  INSERT INTO p VALUES (NULL), ('NaN'::float), ('Inf'::float), ('-Inf'::float), ('0'::float), (1), (-1)
     8  
     9  # -0 and 0 should both equate to zero with or without an index.
    10  
    11  statement error duplicate key value
    12  INSERT INTO p VALUES ('-0'::float)
    13  
    14  query R
    15  SELECT * FROM p WHERE f = 'NaN'
    16  ----
    17  NaN
    18  
    19  query RBBB
    20  SELECT f, f IS NaN, f = 'NaN', isnan(f) FROM p@{FORCE_INDEX=primary} ORDER BY 1
    21  ----
    22  NULL  NULL   NULL   NULL
    23  NaN   true   true   true
    24  -Inf  false  false  false
    25  -1    false  false  false
    26  0     false  false  false
    27  1     false  false  false
    28  +Inf  false  false  false
    29  
    30  query RBBB
    31  SELECT f, f IS NaN, f = 'NaN', isnan(f) FROM p@{FORCE_INDEX=p_f_key} ORDER BY 1
    32  ----
    33  NULL  NULL   NULL   NULL
    34  NaN   true   true   true
    35  -Inf  false  false  false
    36  -1    false  false  false
    37  0     false  false  false
    38  1     false  false  false
    39  +Inf  false  false  false
    40  
    41  query RB
    42  select f, f > 'NaN' from p@{FORCE_INDEX=primary} where f > 'NaN' ORDER BY f
    43  ----
    44  -Inf  true
    45  -1    true
    46  0     true
    47  1     true
    48  +Inf  true
    49  
    50  query RB
    51  select f, f > 'NaN' from p@{FORCE_INDEX=p_f_key} where f > 'NaN' ORDER BY f
    52  ----
    53  -Inf  true
    54  -1    true
    55  0     true
    56  1     true
    57  +Inf  true
    58  
    59  statement ok
    60  CREATE TABLE i (f float)
    61  
    62  statement ok
    63  INSERT INTO i VALUES (0), ('-0'::float)
    64  
    65  query R rowsort
    66  SELECT * FROM i WHERE f = 0
    67  ----
    68  -0
    69  0
    70  
    71  statement ok
    72  CREATE INDEX ON i (f)
    73  
    74  query R rowsort
    75  SELECT * FROM i WHERE f = 0
    76  ----
    77  -0
    78  0
    79  
    80  statement error violates unique constraint
    81  CREATE UNIQUE INDEX ON i (f)
    82  
    83  subtest extra_float_digits
    84  
    85  statement ok
    86  CREATE TABLE vals(f FLOAT);
    87    INSERT INTO vals VALUES (0.0), (123.4567890123456789), (12345678901234567890000), (0.0001234567890123456789)
    88  
    89  query RT rowsort
    90  SELECT f, f::string FROM vals
    91  ----
    92  0                     0
    93  123.456789012346      123.456789012346
    94  1.23456789012346e+22  1.23456789012346e+22
    95  0.000123456789012346  0.000123456789012346
    96  
    97  statement ok
    98  SET extra_float_digits = 3
    99  
   100  query RT rowsort
   101  SELECT f, f::string FROM vals
   102  ----
   103  0                       0
   104  123.45678901234568      123.45678901234568
   105  1.2345678901234568e+22  1.2345678901234568e+22
   106  0.00012345678901234567  0.00012345678901234567
   107  
   108  statement ok
   109  SET extra_float_digits = -8
   110  
   111  query RT rowsort
   112  SELECT f, f::string FROM vals
   113  ----
   114  0             0
   115  123.4568      123.4568
   116  1.234568e+22  1.234568e+22
   117  0.0001234568  0.0001234568
   118  
   119  statement ok
   120  SET extra_float_digits = -15
   121  
   122  query RT rowsort
   123  SELECT f, f::string FROM vals
   124  ----
   125  0       0
   126  100     1e+02
   127  1e+22   1e+22
   128  0.0001  0.0001
   129  
   130  statement ok
   131  DROP TABLE vals
   132  
   133  statement ok
   134  RESET extra_float_digits