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

     1  # This tests zeros and numbers with trailing zeros.
     2  
     3  # ints
     4  
     5  statement ok
     6  CREATE TABLE i (i int, v int)
     7  
     8  statement ok
     9  INSERT INTO i VALUES
    10    (1, 0),
    11    (2, -0),
    12    (3, 0::int),
    13    (4, -0::int),
    14    (5, '0'::int),
    15    (6, '-0'::int)
    16  
    17  query II
    18  select * FROM i ORDER BY i
    19  ----
    20  1 0
    21  2 0
    22  3 0
    23  4 0
    24  5 0
    25  6 0
    26  
    27  query IIIIII
    28  SELECT 0, -0, 0::int, -0::int, '0'::int, '-0'::int
    29  ----
    30  0 0 0 0 0 0
    31  
    32  # floats
    33  
    34  statement ok
    35  CREATE TABLE f (i int, v float)
    36  
    37  statement ok
    38  INSERT INTO f VALUES
    39    (1, 0.0),
    40    (2, -0.0),
    41    (3, 0.00::float),
    42    (4, -0.00::float),
    43    (5, (-0.000)::float),
    44    (6, 0::float),
    45    (7, -0::float),
    46    (8, '0.0000'::float),
    47    (9, '-0.0000'::float),
    48    (10, 0),
    49    (11, -0)
    50  
    51  query IR
    52  select * FROM f ORDER BY i
    53  ----
    54  1 0
    55  2 -0
    56  3 0
    57  4 -0
    58  5 -0
    59  6 0
    60  7 -0
    61  8 0
    62  9 -0
    63  10 0
    64  11 -0
    65  
    66  query RRRRRIIRRRR colnames
    67  SELECT 0.0::float as a,
    68        -0.0::float as b,
    69        0.00::float as c,
    70       -0.00::float as d,
    71    (-0.000)::float as e,
    72                  0 as f,
    73                 -0 as g,
    74           0::float as h,
    75          -0::float as i,
    76    '0.0000'::float as j,
    77   '-0.0000'::float as k
    78  ----
    79  a  b   c  d   e   f  g  h  i   j  k
    80  0  -0  0  -0  -0  0  0  0  -0  0  -0
    81  
    82  # decimals
    83  
    84  query RRRRRRRRR colnames
    85  SELECT 0.0::decimal as a,
    86        -0.0::decimal as b,
    87        0.00::decimal as c,
    88       -0.00::decimal as d,
    89    (-0.000)::decimal as e,
    90           0::decimal as f,
    91          -0::decimal as g,
    92    '0.0000'::decimal as h,
    93   '-0.0000'::decimal as i
    94  ----
    95  a    b    c     d     e      f  g  h       i
    96  0.0  0.0  0.00  0.00  0.000  0  0  0.0000  0.0000
    97  
    98  statement ok
    99  CREATE TABLE d (i INT, v DECIMAL)
   100  
   101  statement ok
   102  INSERT INTO d VALUES
   103    (1, 0.0),
   104    (2, -0.0),
   105    (3, 0.00::decimal),
   106    (4, -0.00::decimal),
   107    (5, (-0.000)::decimal),
   108    (6, 0::decimal),
   109    (7, -0::decimal),
   110    (8, '0.0000'::decimal),
   111    (9, '-0.0000'::decimal),
   112    (10, 0),
   113    (11, -0)
   114  
   115  query IR
   116  select * FROM d ORDER BY i
   117  ----
   118  1 0.0
   119  2 0.0
   120  3 0.00
   121  4 0.00
   122  5 0.000
   123  6 0
   124  7 0
   125  8 0.0000
   126  9 0.0000
   127  10 0
   128  11 0
   129  
   130  statement ok
   131  CREATE TABLE didx (i INT, v DECIMAL, INDEX vidx (v))
   132  
   133  statement ok
   134  INSERT INTO didx VALUES
   135    (1, 0.0),
   136    (2, -0.0),
   137    (3, 0.00::decimal),
   138    (4, -0.00::decimal),
   139    (5, (-0.000)::decimal),
   140    (6, 0::decimal),
   141    (7, -0::decimal),
   142    (8, '0.0000'::decimal),
   143    (9, '-0.0000'::decimal),
   144    (10, 0),
   145    (11, -0)
   146  
   147  query R
   148  SELECT v FROM didx ORDER BY INDEX didx@vidx
   149  ----
   150  0.0
   151  0.0
   152  0.00
   153  0.00
   154  0.000
   155  0
   156  0
   157  0.0000
   158  0.0000
   159  0
   160  0
   161  
   162  query RRRR
   163  SELECT - -0.00::decimal, - - -0.00::decimal, - - -0.00, - -0.00
   164  ----
   165  0.00 0.00 0.00 0.00
   166  
   167  # TODO(mjibson): 
   168  #
   169  #query RRRR
   170  #SELECT -+-0.010::decimal, 0.020, 0.030::decimal, -0.0400000000000000000000000000
   171  #----
   172  #0.010 0.020 0.030 -0.0400000000000000000000000000
   173  #
   174  #query RR
   175  #SELECT -0.040 + 0, 0.040::decimal + 0
   176  #----
   177  #-0.040 0.040
   178  
   179  query R
   180  SELECT * FROM (VALUES (-0.0::DECIMAL), (-0::DECIMAL), (0::DECIMAL), (-0.00::DECIMAL)) ORDER BY 1
   181  ----
   182  0.0
   183  0
   184  0
   185  0.00