github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/serial (about) 1 # LogicTest: !3node-tenant 2 subtest serial_rowid 3 4 statement ok 5 CREATE TABLE serial ( 6 a SERIAL PRIMARY KEY, 7 b INT DEFAULT 7, 8 c SERIAL, 9 UNIQUE INDEX (c), 10 FAMILY "primary" (a, b, c) 11 ) 12 13 query TT 14 SHOW CREATE TABLE serial 15 ---- 16 serial CREATE TABLE serial ( 17 a INT8 NOT NULL DEFAULT unique_rowid(), 18 b INT8 NULL DEFAULT 7:::INT8, 19 c INT8 NOT NULL DEFAULT unique_rowid(), 20 CONSTRAINT "primary" PRIMARY KEY (a ASC), 21 UNIQUE INDEX serial_c_key (c ASC), 22 FAMILY "primary" (a, b, c) 23 ) 24 25 statement ok 26 INSERT INTO serial (a, b) VALUES (1, 2), (DEFAULT, DEFAULT), (DEFAULT, 3) 27 28 statement ok 29 INSERT INTO serial (b) VALUES (2) 30 31 query III 32 SELECT count(DISTINCT a), count(DISTINCT b), count(DISTINCT c) FROM serial 33 ---- 34 4 3 4 35 36 statement error multiple default values specified for column "a" of table "s1" 37 CREATE TABLE s1 (a SERIAL DEFAULT 7) 38 39 statement error conflicting NULL/NOT NULL declarations for column "a" of table "s1" 40 CREATE TABLE s1 (a SERIAL NULL) 41 42 statement ok 43 CREATE TABLE smallbig ( 44 a SMALLSERIAL, b BIGSERIAL, c INT, 45 FAMILY "primary" (a, b, c, rowid) 46 ) 47 48 statement ok 49 INSERT INTO smallbig (c) VALUES (7), (7) 50 51 query TT 52 SHOW CREATE TABLE smallbig 53 ---- 54 smallbig CREATE TABLE smallbig ( 55 a INT8 NOT NULL DEFAULT unique_rowid(), 56 b INT8 NOT NULL DEFAULT unique_rowid(), 57 c INT8 NULL, 58 FAMILY "primary" (a, b, c, rowid) 59 ) 60 61 query III 62 SELECT count(DISTINCT a), count(DISTINCT b), count(DISTINCT c) FROM smallbig 63 ---- 64 2 2 1 65 66 statement ok 67 CREATE TABLE serials ( 68 a SERIAL2, b SERIAL4, c SERIAL8, d INT, 69 FAMILY "primary" (a, b, c, d, rowid) 70 ) 71 72 query TT 73 SHOW CREATE TABLE serials 74 ---- 75 serials CREATE TABLE serials ( 76 a INT8 NOT NULL DEFAULT unique_rowid(), 77 b INT8 NOT NULL DEFAULT unique_rowid(), 78 c INT8 NOT NULL DEFAULT unique_rowid(), 79 d INT8 NULL, 80 FAMILY "primary" (a, b, c, d, rowid) 81 ) 82 83 statement ok 84 INSERT INTO serials (d) VALUES (9), (9) 85 86 query III 87 SELECT count(DISTINCT a), count(DISTINCT b), count(DISTINCT c) FROM serials 88 ---- 89 2 2 2 90 91 statement ok 92 DROP TABLE serials, smallbig, serial 93 94 95 subtest serial_virtual_sequence 96 97 statement ok 98 SET serial_normalization = virtual_sequence 99 100 # Force the sequence for column "c" to bump to 2. 101 statement ok 102 CREATE SEQUENCE serial_c_seq; CREATE SEQUENCE serial_c_seq1 103 104 statement ok 105 CREATE TABLE serial ( 106 a SERIAL PRIMARY KEY, 107 b INT DEFAULT 7, 108 c SERIAL, 109 UNIQUE INDEX (c), 110 FAMILY "primary" (a, b, c) 111 ) 112 113 query TT 114 SHOW CREATE TABLE serial 115 ---- 116 serial CREATE TABLE serial ( 117 a INT8 NOT NULL DEFAULT nextval('serial_a_seq':::STRING), 118 b INT8 NULL DEFAULT 7:::INT8, 119 c INT8 NOT NULL DEFAULT nextval('serial_c_seq2':::STRING), 120 CONSTRAINT "primary" PRIMARY KEY (a ASC), 121 UNIQUE INDEX serial_c_key (c ASC), 122 FAMILY "primary" (a, b, c) 123 ) 124 125 query TT 126 SHOW CREATE SEQUENCE serial_a_seq 127 ---- 128 serial_a_seq CREATE SEQUENCE serial_a_seq MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 1 START 1 VIRTUAL 129 130 statement ok 131 INSERT INTO serial (a, b) VALUES (1, 2), (DEFAULT, DEFAULT), (DEFAULT, 3) 132 133 statement ok 134 INSERT INTO serial (b) VALUES (2) 135 136 query III 137 SELECT count(DISTINCT a), count(DISTINCT b), count(DISTINCT c) FROM serial 138 ---- 139 4 3 4 140 141 statement error multiple default values specified for column "a" of table "s1" 142 CREATE TABLE s1 (a SERIAL DEFAULT 7) 143 144 statement error conflicting NULL/NOT NULL declarations for column "a" of table "s1" 145 CREATE TABLE s1 (a SERIAL NULL) 146 147 statement ok 148 CREATE TABLE smallbig ( 149 a SMALLSERIAL, b BIGSERIAL, c INT, 150 FAMILY "primary" (a, b, c, rowid) 151 ) 152 153 statement ok 154 INSERT INTO smallbig (c) VALUES (7), (7) 155 156 query TT 157 SHOW CREATE TABLE smallbig 158 ---- 159 smallbig CREATE TABLE smallbig ( 160 a INT8 NOT NULL DEFAULT nextval('smallbig_a_seq':::STRING), 161 b INT8 NOT NULL DEFAULT nextval('smallbig_b_seq':::STRING), 162 c INT8 NULL, 163 FAMILY "primary" (a, b, c, rowid) 164 ) 165 166 query III 167 SELECT count(DISTINCT a), count(DISTINCT b), count(DISTINCT c) FROM smallbig 168 ---- 169 2 2 1 170 171 statement ok 172 CREATE TABLE serials ( 173 a SERIAL2, b SERIAL4, c SERIAL8, d INT, 174 FAMILY "primary" (a, b, c, d, rowid) 175 ) 176 177 query TT 178 SHOW CREATE TABLE serials 179 ---- 180 serials CREATE TABLE serials ( 181 a INT8 NOT NULL DEFAULT nextval('serials_a_seq':::STRING), 182 b INT8 NOT NULL DEFAULT nextval('serials_b_seq':::STRING), 183 c INT8 NOT NULL DEFAULT nextval('serials_c_seq':::STRING), 184 d INT8 NULL, 185 FAMILY "primary" (a, b, c, d, rowid) 186 ) 187 188 statement ok 189 INSERT INTO serials (d) VALUES (9), (9) 190 191 query III 192 SELECT count(DISTINCT a), count(DISTINCT b), count(DISTINCT c) FROM serials 193 ---- 194 2 2 2 195 196 statement ok 197 DROP TABLE serials, smallbig, serial 198 199 200 subtest serial_sql_sequence 201 202 statement ok 203 SET serial_normalization = sql_sequence 204 205 statement ok 206 CREATE TABLE serial ( 207 a SERIAL PRIMARY KEY, 208 b INT DEFAULT 7, 209 c SERIAL, 210 UNIQUE INDEX (c), 211 FAMILY "primary" (a, b, c) 212 ) 213 214 query TT 215 SHOW CREATE TABLE serial 216 ---- 217 serial CREATE TABLE serial ( 218 a INT8 NOT NULL DEFAULT nextval('serial_a_seq1':::STRING), 219 b INT8 NULL DEFAULT 7:::INT8, 220 c INT8 NOT NULL DEFAULT nextval('serial_c_seq3':::STRING), 221 CONSTRAINT "primary" PRIMARY KEY (a ASC), 222 UNIQUE INDEX serial_c_key (c ASC), 223 FAMILY "primary" (a, b, c) 224 ) 225 226 query TT 227 SHOW CREATE SEQUENCE serial_a_seq1 228 ---- 229 serial_a_seq1 CREATE SEQUENCE serial_a_seq1 MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 1 START 1 230 231 statement ok 232 INSERT INTO serial (a, b) VALUES (0, 2), (DEFAULT, DEFAULT), (DEFAULT, 3) 233 234 statement ok 235 INSERT INTO serial (b) VALUES (2) 236 237 query III 238 SELECT count(DISTINCT a), count(DISTINCT b), count(DISTINCT c) FROM serial 239 ---- 240 4 3 4 241 242 statement ok 243 CREATE TABLE "serial_MixedCase" ( 244 a SERIAL PRIMARY KEY, 245 b INT DEFAULT 7, 246 c SERIAL, 247 UNIQUE INDEX (c), 248 FAMILY "primary" (a, b, c) 249 ) 250 251 query TT 252 SHOW CREATE TABLE "serial_MixedCase" 253 ---- 254 "serial_MixedCase" CREATE TABLE "serial_MixedCase" ( 255 a INT8 NOT NULL DEFAULT nextval('"serial_MixedCase_a_seq"':::STRING), 256 b INT8 NULL DEFAULT 7:::INT8, 257 c INT8 NOT NULL DEFAULT nextval('"serial_MixedCase_c_seq"':::STRING), 258 CONSTRAINT "primary" PRIMARY KEY (a ASC), 259 UNIQUE INDEX "serial_MixedCase_c_key" (c ASC), 260 FAMILY "primary" (a, b, c) 261 ) 262 263 statement error multiple default values specified for column "a" of table "s1" 264 CREATE TABLE s1 (a SERIAL DEFAULT 7) 265 266 statement error conflicting NULL/NOT NULL declarations for column "a" of table "s1" 267 CREATE TABLE s1 (a SERIAL NULL) 268 269 statement ok 270 CREATE TABLE smallbig ( 271 a SMALLSERIAL, b BIGSERIAL, c INT, 272 FAMILY "primary" (a, b, c, rowid) 273 ) 274 275 statement ok 276 INSERT INTO smallbig (c) VALUES (7), (7) 277 278 query TT 279 SHOW CREATE TABLE smallbig 280 ---- 281 smallbig CREATE TABLE smallbig ( 282 a INT2 NOT NULL DEFAULT nextval('smallbig_a_seq1':::STRING), 283 b INT8 NOT NULL DEFAULT nextval('smallbig_b_seq1':::STRING), 284 c INT8 NULL, 285 FAMILY "primary" (a, b, c, rowid) 286 ) 287 288 query III 289 SELECT count(DISTINCT a), count(DISTINCT b), count(DISTINCT c) FROM smallbig 290 ---- 291 2 2 1 292 293 statement ok 294 CREATE TABLE serials ( 295 a SERIAL2, b SERIAL4, c SERIAL8, d INT, 296 FAMILY "primary" (a, b, c, d, rowid) 297 ) 298 299 query TT 300 SHOW CREATE TABLE serials 301 ---- 302 serials CREATE TABLE serials ( 303 a INT2 NOT NULL DEFAULT nextval('serials_a_seq1':::STRING), 304 b INT4 NOT NULL DEFAULT nextval('serials_b_seq1':::STRING), 305 c INT8 NOT NULL DEFAULT nextval('serials_c_seq1':::STRING), 306 d INT8 NULL, 307 FAMILY "primary" (a, b, c, d, rowid) 308 ) 309 310 statement ok 311 INSERT INTO serials (d) VALUES (9), (9) 312 313 query III 314 SELECT count(DISTINCT a), count(DISTINCT b), count(DISTINCT c) FROM serials 315 ---- 316 2 2 2 317 318 statement ok 319 DROP TABLE serials, smallbig, serial