github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/create_table (about) 1 # LogicTest: !3node-tenant 2 subtest regression_42858 3 4 statement ok 5 CREATE TABLE TEST2 (COL1 SERIAL PRIMARY KEY, COL2 INT8) 6 7 statement error pq: duplicate constraint name: "duplicate_name" 8 CREATE TABLE TEST1 (COL1 SERIAL PRIMARY KEY, COL2 INT8, COL3 INT8, CONSTRAINT duplicate_name FOREIGN KEY (col2) REFERENCES TEST2(COL1), CONSTRAINT duplicate_name FOREIGN KEY (col3) REFERENCES TEST2(COL1)) 9 10 statement ok 11 DROP TABLE TEST2 12 13 # Regression for #43894 14 subtest regression_43894 15 statement ok 16 CREATE TABLE IF NOT EXISTS t43894 (PRIMARY KEY (a), a UUID NOT NULL, b JSONB NOT NULL DEFAULT '5') 17 18 subtest telemetry_tests 19 20 statement ok 21 CREATE TABLE new_table (a timetz(3)) 22 23 statement ok 24 ALTER TABLE new_table ADD COLUMN c timetz(4) 25 26 # Cannot really get more exact than this (i.e. looking at usage_count), as it increments on each run. 27 query T 28 SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name IN ('sql.schema.new_column_type.timetz_3_', 'sql.schema.new_column_type.timetz_4_') AND usage_count > 0 ORDER BY feature_name 29 ---- 30 sql.schema.new_column_type.timetz_3_ 31 sql.schema.new_column_type.timetz_4_ 32 33 statement ok 34 CREATE TABLE sec_col_fam(x INT, y INT, z INT, FAMILY (x), FAMILY (y), FAMILY (z), INDEX (x) STORING (y, z)); 35 CREATE INDEX ON sec_col_fam (x) STORING (y, z) 36 37 query T 38 SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name = 'sql.schema.secondary_index_column_families' AND usage_count >= 2 39 ---- 40 sql.schema.secondary_index_column_families 41 42 statement ok 43 set require_explicit_primary_keys=true 44 45 statement error pq: no primary key specified for table t \(require_explicit_primary_keys = true\) 46 CREATE TABLE t (x INT, y INT) 47 48 # Regression for #45496. 49 statement ok 50 reset require_explicit_primary_keys; 51 DROP TABLE IF EXISTS t; 52 CREATE TABLE t (rowid INT, rowid_1 INT, FAMILY (rowid, rowid_1)) 53 54 query T rowsort 55 SELECT column_name FROM [SHOW COLUMNS FROM t] 56 ---- 57 rowid 58 rowid_1 59 rowid_2 60 61 query TT 62 SELECT index_name, column_name FROM [SHOW INDEXES FROM t] 63 ---- 64 primary rowid_2 65 66 query TT 67 SHOW CREATE t 68 ---- 69 t CREATE TABLE t ( 70 rowid INT8 NULL, 71 rowid_1 INT8 NULL, 72 FAMILY fam_0_rowid_rowid_1_rowid_2 (rowid, rowid_1, rowid_2) 73 ) 74 75 subtest regression_qualification_feature_counts 76 77 statement ok 78 CREATE TABLE telemetry_test (a int DEFAULT 1, b int UNIQUE CHECK(b > 1), c int AS (a + b) STORED) 79 80 query T rowsort 81 SELECT feature_name FROM crdb_internal.feature_usage 82 WHERE feature_name IN ( 83 'sql.schema.new_column.qualification.computed', 84 'sql.schema.new_column.qualification.default_expr', 85 'sql.schema.new_column.qualification.unique' 86 ) 87 ---- 88 sql.schema.new_column.qualification.unique 89 sql.schema.new_column.qualification.computed 90 sql.schema.new_column.qualification.default_expr 91 92 statement ok 93 DROP TABLE telemetry_test 94 95 statement ok 96 SET experimental_enable_hash_sharded_indexes = true; 97 CREATE TABLE telemetry ( 98 x INT PRIMARY KEY, 99 y INT, 100 z JSONB, 101 INVERTED INDEX (z), 102 INDEX (y) USING HASH WITH BUCKET_COUNT = 4 103 ) 104 105 query T rowsort 106 SELECT feature_name FROM crdb_internal.feature_usage 107 WHERE feature_name IN ( 108 'sql.schema.inverted_index', 109 'sql.schema.hash_sharded_index' 110 ) 111 ---- 112 sql.schema.inverted_index 113 sql.schema.hash_sharded_index 114 115 subtest like_table 116 117 # Test the CREATE TABLE LIKE functionality. 118 119 statement ok 120 CREATE TABLE like_table ( 121 a INT CHECK (a > 3), 122 b STRING DEFAULT 'foo' NOT NULL, 123 c DECIMAL AS (a+3) STORED, 124 h INT, 125 j JSON, 126 PRIMARY KEY (a, b), 127 UNIQUE INDEX foo (b DESC, c), 128 INDEX (c) STORING (j), 129 INVERTED INDEX (j) 130 ) 131 132 statement ok 133 CREATE TABLE like_none (LIKE like_table) 134 135 query TT 136 SHOW CREATE TABLE like_none 137 ---- 138 like_none CREATE TABLE like_none ( 139 a INT8 NOT NULL, 140 b STRING NOT NULL, 141 c DECIMAL NULL, 142 h INT8 NULL, 143 j JSONB NULL, 144 FAMILY "primary" (a, b, c, h, j, rowid) 145 ) 146 147 statement ok 148 CREATE TABLE like_constraints (LIKE like_table INCLUDING CONSTRAINTS) 149 150 query TT 151 SHOW CREATE TABLE like_constraints 152 ---- 153 like_constraints CREATE TABLE like_constraints ( 154 a INT8 NOT NULL, 155 b STRING NOT NULL, 156 c DECIMAL NULL, 157 h INT8 NULL, 158 j JSONB NULL, 159 FAMILY "primary" (a, b, c, h, j, rowid), 160 CONSTRAINT check_a CHECK (a > 3:::INT8) 161 ) 162 163 statement ok 164 CREATE TABLE like_indexes (LIKE like_table INCLUDING INDEXES) 165 166 query TT 167 SHOW CREATE TABLE like_indexes 168 ---- 169 like_indexes CREATE TABLE like_indexes ( 170 a INT8 NOT NULL, 171 b STRING NOT NULL, 172 c DECIMAL NULL, 173 h INT8 NULL, 174 j JSONB NULL, 175 CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC), 176 UNIQUE INDEX foo (b DESC, c ASC), 177 INDEX like_table_c_idx (c ASC) STORING (j), 178 INVERTED INDEX like_table_j_idx (j), 179 FAMILY "primary" (a, b, c, h, j) 180 ) 181 182 # INCLUDING GENERATED adds "generated columns", aka stored columns. 183 statement ok 184 CREATE TABLE like_generated (LIKE like_table INCLUDING GENERATED) 185 186 query TT 187 SHOW CREATE TABLE like_generated 188 ---- 189 like_generated CREATE TABLE like_generated ( 190 a INT8 NOT NULL, 191 b STRING NOT NULL, 192 c DECIMAL NULL AS (a + 3:::DECIMAL) STORED, 193 h INT8 NULL, 194 j JSONB NULL, 195 FAMILY "primary" (a, b, c, h, j, rowid) 196 ) 197 198 statement ok 199 CREATE TABLE like_defaults (LIKE like_table INCLUDING DEFAULTS) 200 201 query TT 202 SHOW CREATE TABLE like_defaults 203 ---- 204 like_defaults CREATE TABLE like_defaults ( 205 a INT8 NOT NULL, 206 b STRING NOT NULL DEFAULT 'foo':::STRING, 207 c DECIMAL NULL, 208 h INT8 NULL, 209 j JSONB NULL, 210 FAMILY "primary" (a, b, c, h, j, rowid) 211 ) 212 213 statement ok 214 CREATE TABLE like_all (LIKE like_table INCLUDING ALL) 215 216 query TT 217 SHOW CREATE TABLE like_all 218 ---- 219 like_all CREATE TABLE like_all ( 220 a INT8 NOT NULL, 221 b STRING NOT NULL DEFAULT 'foo':::STRING, 222 c DECIMAL NULL AS (a + 3:::DECIMAL) STORED, 223 h INT8 NULL, 224 j JSONB NULL, 225 CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC), 226 UNIQUE INDEX foo (b DESC, c ASC), 227 INDEX like_table_c_idx (c ASC) STORING (j), 228 INVERTED INDEX like_table_j_idx (j), 229 FAMILY "primary" (a, b, c, h, j), 230 CONSTRAINT check_a CHECK (a > 3:::INT8) 231 ) 232 233 statement ok 234 CREATE TABLE like_mixed (LIKE like_table INCLUDING ALL EXCLUDING GENERATED EXCLUDING CONSTRAINTS INCLUDING GENERATED) 235 236 # We expect that this table will be missing the check constraint from the first 237 # table, but will include everything else. 238 query TT 239 SHOW CREATE TABLE like_mixed 240 ---- 241 like_mixed CREATE TABLE like_mixed ( 242 a INT8 NOT NULL, 243 b STRING NOT NULL DEFAULT 'foo':::STRING, 244 c DECIMAL NULL AS (a + 3:::DECIMAL) STORED, 245 h INT8 NULL, 246 j JSONB NULL, 247 CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC), 248 UNIQUE INDEX foo (b DESC, c ASC), 249 INDEX like_table_c_idx (c ASC) STORING (j), 250 INVERTED INDEX like_table_j_idx (j), 251 FAMILY "primary" (a, b, c, h, j) 252 ) 253 254 statement ok 255 CREATE TABLE like_no_pk_table ( 256 a INT, b INT 257 ) 258 259 statement ok 260 CREATE TABLE like_no_pk_rowid_hidden (LIKE like_no_pk_table INCLUDING INDEXES) 261 262 query TT 263 SHOW CREATE TABLE like_no_pk_rowid_hidden 264 ---- 265 like_no_pk_rowid_hidden CREATE TABLE like_no_pk_rowid_hidden ( 266 a INT8 NULL, 267 b INT8 NULL, 268 FAMILY "primary" (a, b, rowid) 269 ) 270 271 statement error duplicate column name 272 CREATE TABLE duplicate_column (LIKE like_table, c DECIMAL) 273 274 statement ok 275 CREATE TABLE other_table (blah INT) 276 277 # Test that mixing normal specifiers and LIKE specifiers works as expected. We 278 # expect that the column ordering depends on the order of the LIKE specifiers. 279 statement ok 280 CREATE TABLE like_more_specifiers (LIKE like_table, z DECIMAL, INDEX(a,blah,z), LIKE other_table) 281 282 query TT 283 SHOW CREATE TABLE like_more_specifiers 284 ---- 285 like_more_specifiers CREATE TABLE like_more_specifiers ( 286 a INT8 NOT NULL, 287 b STRING NOT NULL, 288 c DECIMAL NULL, 289 h INT8 NULL, 290 j JSONB NULL, 291 z DECIMAL NULL, 292 blah INT8 NULL, 293 INDEX like_more_specifiers_a_blah_z_idx (a ASC, blah ASC, z ASC), 294 FAMILY "primary" (a, b, c, h, j, z, blah, rowid) 295 ) 296 297 statement ok 298 CREATE TABLE like_hash_base (a INT, INDEX (a) USING HASH WITH BUCKET_COUNT=4) 299 300 statement ok 301 CREATE TABLE like_hash (LIKE like_hash_base INCLUDING INDEXES) 302 303 query TT 304 SHOW CREATE TABLE like_hash 305 ---- 306 like_hash CREATE TABLE like_hash ( 307 a INT8 NULL, 308 INDEX like_hash_base_crdb_internal_a_shard_4_a_idx (a ASC) USING HASH WITH BUCKET_COUNT = 4, 309 FAMILY "primary" (a, crdb_internal_a_shard_4, rowid) 310 ) 311 312 statement error unimplemented 313 CREATE TABLE error (LIKE like_hash_base INCLUDING COMMENTS) 314 315 statement error unimplemented 316 CREATE TABLE error (LIKE like_hash_base INCLUDING STATISTICS) 317 318 statement error unimplemented 319 CREATE TABLE error (LIKE like_hash_base INCLUDING STORAGE)