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

     1  statement ok
     2  CREATE TABLE test (
     3    t CHAR(4),
     4    UNIQUE INDEX a (t)
     5  )
     6  
     7  statement ok
     8  INSERT INTO test VALUES ('a')
     9  
    10  statement ok
    11  INSERT INTO test VALUES ('ab')
    12  
    13  statement ok
    14  INSERT INTO test VALUES ('abcd')
    15  
    16  statement error value too long for type CHAR\(4\) \(column "t"\)
    17  INSERT INTO test VALUES ('abcdef')
    18  
    19  statement ok
    20  INSERT INTO test VALUES ('áááá')
    21  
    22  statement error value too long
    23  INSERT INTO test VALUES ('ááááß')
    24  
    25  statement ok
    26  UPDATE test SET t = 'b' WHERE t = 'abcde'
    27  
    28  statement error value too long
    29  UPDATE test SET t = 'cdefg' WHERE t = 'ab'
    30  
    31  statement ok
    32  CREATE TABLE tb (
    33    b BIT(3),
    34    UNIQUE INDEX a (b)
    35  )
    36  
    37  statement ok
    38  INSERT INTO tb VALUES (B'001')
    39  
    40  statement ok
    41  INSERT INTO tb VALUES (B'011')
    42  
    43  statement ok
    44  INSERT INTO tb VALUES (B'111')
    45  
    46  statement error bit string length 4 does not match type BIT\(3\)
    47  INSERT INTO tb VALUES (B'1111')
    48  
    49  statement ok
    50  UPDATE tb SET b = B'010' WHERE b = B'111'
    51  
    52  statement error bit string length 5 does not match type BIT\(3\)
    53  UPDATE tb SET b = B'10000' WHERE b = B'010'
    54  
    55  statement ok
    56  CREATE TABLE tc (
    57    b INT2,
    58    UNIQUE INDEX a (b)
    59  )
    60  
    61  statement ok
    62  INSERT INTO tc VALUES (50)
    63  
    64  statement ok
    65  INSERT INTO tc VALUES (-32768)
    66  
    67  statement ok
    68  INSERT INTO tc VALUES (32767)
    69  
    70  # Note that neither of these value are INT2, but we only check
    71  # on insert and update, not mathematical operations
    72  statement ok
    73  INSERT INTO tc VALUES (60000-59999)
    74  
    75  statement error integer out of range for type int2 \(column "b"\)
    76  INSERT INTO tc VALUES (-32769)
    77  
    78  statement error integer out of range for type int2 \(column "b"\)
    79  INSERT INTO tc VALUES (32768)
    80  
    81  statement ok
    82  UPDATE tc SET b = 80 WHERE b = 50
    83  
    84  statement error integer out of range for type int2 \(column "b"\)
    85  UPDATE tc SET b = 32768 WHERE b = 32767
    86  
    87  statement ok
    88  CREATE TABLE tc1 (
    89    b INT4,
    90    UNIQUE INDEX a (b)
    91  )
    92  
    93  statement ok
    94  INSERT INTO tc1 VALUES (50)
    95  
    96  statement ok
    97  INSERT INTO tc1 VALUES (-2147483648)
    98  
    99  statement ok
   100  INSERT INTO tc1 VALUES (2147483647)
   101  
   102  statement error integer out of range for type int4 \(column "b"\)
   103  INSERT INTO tc1 VALUES (-2147483649)
   104  
   105  statement error integer out of range for type int4 \(column "b"\)
   106  INSERT INTO tc1 VALUES (2147483648)
   107  
   108  statement ok
   109  UPDATE tc1 SET b = 80 WHERE b = 50
   110  
   111  statement error integer out of range for type int4 \(column "b"\)
   112  UPDATE tc1 SET b = 2147483648 WHERE b = 2147483647
   113  
   114  statement ok
   115  CREATE TABLE td (
   116    d DECIMAL(3, 2),
   117    UNIQUE INDEX b (d)
   118  )
   119  
   120  statement ok
   121  INSERT INTO td VALUES (DECIMAL '3.1')
   122  
   123  statement ok
   124  INSERT INTO td VALUES (DECIMAL '3.14')
   125  
   126  statement error duplicate
   127  INSERT INTO td VALUES (DECIMAL '3.1415')
   128  
   129  statement error type DECIMAL\(3,2\) \(column "d"\): value with precision 3, scale 2 must round to an absolute value less than 10\^1
   130  INSERT INTO td VALUES (DECIMAL '13.1415')
   131  
   132  query R rowsort
   133  SELECT d FROM td
   134  ----
   135  3.10
   136  3.14
   137  
   138  statement error must round
   139  UPDATE td SET d = DECIMAL '101.414' WHERE d = DECIMAL '3.14'
   140  
   141  statement ok
   142  UPDATE td SET d = DECIMAL '1.414' WHERE d = DECIMAL '3.14'
   143  
   144  statement error duplicate
   145  UPDATE td SET d = DECIMAL '1.41' WHERE d = DECIMAL '3.1'
   146  
   147  query R rowsort
   148  SELECT d FROM td
   149  ----
   150  3.10
   151  1.41
   152  
   153  statement ok
   154  CREATE TABLE td2 (x DECIMAL(3), y DECIMAL)
   155  
   156  statement ok
   157  INSERT INTO td2 VALUES (DECIMAL '123.1415', DECIMAL '123.1415')
   158  
   159  query RR
   160  select x, y FROM td2
   161  ----
   162  123 123.1415
   163  
   164  
   165  # Ensure decimal columns greater than 16 precision are supported.
   166  
   167  statement ok
   168  CREATE TABLE td3 (a decimal, b decimal(3, 1), c decimal(20, 10))
   169  
   170  statement ok
   171  INSERT INTO td3 VALUES (123456789012.123456789012, 12.3, 1234567890.1234567890)
   172  
   173  query RRR
   174  select * from td3
   175  ----
   176  123456789012.123456789012 12.3 1234567890.1234567890
   177  
   178  statement error must round
   179  INSERT INTO td3 (c) VALUES (12345678901)