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)