github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/create_as (about) 1 # LogicTest: !3node-tenant 2 statement ok 3 CREATE TABLE stock (item, quantity) AS VALUES ('cups', 10), ('plates', 15), ('forks', 30) 4 5 statement count 3 6 SELECT * FROM stock 7 8 statement ok 9 CREATE TABLE runningOut AS SELECT * FROM stock WHERE quantity < 12 10 11 statement count 1 12 SELECT * FROM runningOut 13 14 query TI 15 SELECT * FROM runningOut 16 ---- 17 cups 10 18 19 statement ok 20 CREATE TABLE itemColors (color) AS VALUES ('blue'), ('red'), ('green') 21 22 statement count 3 23 SELECT * FROM itemColors 24 25 statement ok 26 CREATE TABLE itemTypes AS (SELECT item, color FROM stock, itemColors) 27 28 statement count 9 29 SELECT * FROM itemTypes 30 31 query TT rowsort 32 SELECT * FROM itemTypes 33 ---- 34 cups blue 35 cups red 36 cups green 37 plates blue 38 plates red 39 plates green 40 forks blue 41 forks red 42 forks green 43 44 statement error pq: AS OF SYSTEM TIME must be provided on a top-level statement 45 CREATE TABLE t AS SELECT * FROM stock AS OF SYSTEM TIME '2016-01-01' 46 47 statement error pgcode 42601 CREATE TABLE specifies 3 column names, but data source has 2 columns 48 CREATE TABLE t2 (col1, col2, col3) AS SELECT * FROM stock 49 50 statement error pgcode 42601 CREATE TABLE specifies 1 column name, but data source has 2 columns 51 CREATE TABLE t2 (col1) AS SELECT * FROM stock 52 53 statement ok 54 CREATE TABLE unionstock AS SELECT * FROM stock UNION VALUES ('spoons', 25), ('knives', 50) 55 56 statement count 5 57 SELECT * FROM unionstock 58 59 query TI 60 SELECT * FROM unionstock ORDER BY quantity 61 ---- 62 cups 10 63 plates 15 64 spoons 25 65 forks 30 66 knives 50 67 68 statement ok 69 CREATE TABLE IF NOT EXISTS unionstock AS VALUES ('foo', 'bar') 70 71 query TI 72 SELECT * FROM unionstock ORDER BY quantity LIMIT 1 73 ---- 74 cups 10 75 76 statement ok 77 CREATE DATABASE smtng 78 79 statement ok 80 CREATE TABLE smtng.something AS SELECT * FROM stock 81 82 statement count 3 83 SELECT * FROM smtng.something; 84 85 statement ok 86 CREATE TABLE IF NOT EXISTS smtng.something AS SELECT * FROM stock 87 88 query TI 89 SELECT * FROM smtng.something ORDER BY 1 LIMIT 1 90 ---- 91 cups 10 92 93 statement error pgcode 42P01 relation "something" does not exist 94 SELECT * FROM something LIMIT 1 95 96 # Check for memory leak (#10466) 97 statement ok 98 CREATE TABLE foo (x, y, z) AS SELECT catalog_name, schema_name, sql_path FROM information_schema.schemata 99 100 statement error pq: relation "foo" already exists 101 CREATE TABLE foo (x, y, z) AS SELECT catalog_name, schema_name, sql_path FROM information_schema.schemata 102 103 statement error pq: value type tuple cannot be used for table columns 104 CREATE TABLE foo2 (x) AS (VALUES(ROW())) 105 106 statement error pq: nested array unsupported as column type: int\[\]\[\] 107 CREATE TABLE foo2 (x) AS (VALUES(ARRAY[ARRAY[1]])) 108 109 statement error generator functions are not allowed in VALUES 110 CREATE TABLE foo2 (x) AS (VALUES(generate_series(1,3))) 111 112 statement error pq: value type unknown cannot be used for table columns 113 CREATE TABLE foo2 (x) AS (VALUES(NULL)) 114 115 # Check nulls are handled properly (#13921) 116 query I 117 CREATE TABLE foo3 (x) AS VALUES (1), (NULL); SELECT * FROM foo3 ORDER BY x 118 ---- 119 NULL 120 1 121 122 # Check that CREATE TABLE AS can use subqueries (#23002) 123 query B 124 CREATE TABLE foo4 (x) AS SELECT EXISTS(SELECT * FROM foo3 WHERE x IS NULL); SELECT * FROM foo4 125 ---- 126 true 127 128 # Regression test for #36930. 129 statement ok 130 CREATE TABLE bar AS SELECT 1 AS a, 2 AS b, count(*) AS c FROM foo 131 132 query III colnames 133 SELECT * FROM bar 134 ---- 135 a b c 136 1 2 5 137 138 statement ok 139 CREATE TABLE baz (a, b, c) AS SELECT 1, 2, count(*) FROM foo 140 141 query III colnames 142 SELECT * FROM baz 143 ---- 144 a b c 145 1 2 5 146 147 # Check that CREATE TABLE AS allows users to specify primary key (#20940) 148 statement ok 149 CREATE TABLE foo5 ( 150 a , b PRIMARY KEY, c, 151 FAMILY "primary" (a, b, c) 152 ) AS 153 SELECT * FROM baz 154 155 query TT 156 SHOW CREATE TABLE foo5 157 ---- 158 foo5 CREATE TABLE foo5 ( 159 a INT8 NULL, 160 b INT8 NOT NULL, 161 c INT8 NULL, 162 CONSTRAINT "primary" PRIMARY KEY (b ASC), 163 FAMILY "primary" (a, b, c) 164 ) 165 166 statement ok 167 CREATE TABLE foo6 ( 168 a PRIMARY KEY, b , c, 169 FAMILY "primary" (a, b, c) 170 ) AS 171 SELECT * FROM baz 172 173 query TT 174 SHOW CREATE TABLE foo6 175 ---- 176 foo6 CREATE TABLE foo6 ( 177 a INT8 NOT NULL, 178 b INT8 NULL, 179 c INT8 NULL, 180 CONSTRAINT "primary" PRIMARY KEY (a ASC), 181 FAMILY "primary" (a, b, c) 182 ) 183 184 statement error generate insert row: null value in column "x" violates not-null constraint 185 CREATE TABLE foo7 (x PRIMARY KEY) AS VALUES (1), (NULL); 186 187 statement ok 188 BEGIN; CREATE TABLE foo8 (item PRIMARY KEY, qty, FAMILY "primary" (item, qty)) AS SELECT * FROM stock UNION VALUES ('spoons', 25), ('knives', 50); END 189 190 query TT 191 SHOW CREATE TABLE foo8 192 ---- 193 foo8 CREATE TABLE foo8 ( 194 item STRING NOT NULL, 195 qty INT8 NULL, 196 CONSTRAINT "primary" PRIMARY KEY (item ASC), 197 FAMILY "primary" (item, qty) 198 ) 199 200 # Allow CREATE TABLE AS to specify composite primary keys. 201 statement ok 202 CREATE TABLE foo9 ( 203 a , b , c, 204 PRIMARY KEY (a, c), 205 FAMILY "primary" (a, b, c) 206 ) AS 207 SELECT * FROM baz 208 209 query TT 210 SHOW CREATE TABLE foo9 211 ---- 212 foo9 CREATE TABLE foo9 ( 213 a INT8 NOT NULL, 214 b INT8 NULL, 215 c INT8 NOT NULL, 216 CONSTRAINT "primary" PRIMARY KEY (a ASC, c ASC), 217 FAMILY "primary" (a, b, c) 218 ) 219 220 statement ok 221 CREATE TABLE foo10 (a, PRIMARY KEY (c, b, a), b, c, FAMILY "primary" (a, b, c)) AS SELECT * FROM foo9 222 223 query TT 224 SHOW CREATE TABLE foo10 225 ---- 226 foo10 CREATE TABLE foo10 ( 227 a INT8 NOT NULL, 228 b INT8 NOT NULL, 229 c INT8 NOT NULL, 230 CONSTRAINT "primary" PRIMARY KEY (c ASC, b ASC, a ASC), 231 FAMILY "primary" (a, b, c) 232 ) 233 234 statement ok 235 CREATE TABLE foo11 ( 236 x , y , z, 237 PRIMARY KEY (x, z), 238 FAMILY "primary" (x, y, z) 239 ) AS 240 VALUES (1, 3, 4), (10, 20, 40); 241 242 query TT 243 SHOW CREATE TABLE foo11 244 ---- 245 foo11 CREATE TABLE foo11 ( 246 x INT8 NOT NULL, 247 y INT8 NULL, 248 z INT8 NOT NULL, 249 CONSTRAINT "primary" PRIMARY KEY (x ASC, z ASC), 250 FAMILY "primary" (x, y, z) 251 ) 252 253 statement error pq: multiple primary keys for table "foo12" are not allowed 254 CREATE TABLE foo12 (x PRIMARY KEY, y, PRIMARY KEY(y)) AS VALUES (1, 2), (3, 4); 255 256 # Check that CREATE TABLE AS allows users to specify column families. 257 statement ok 258 CREATE TABLE abcd( 259 a INT PRIMARY KEY, 260 b INT, 261 c INT, 262 d INT 263 ); 264 265 # Test column qualifiers to define column families. 266 statement ok 267 CREATE TABLE foo12 (a PRIMARY KEY FAMILY f1, b, c FAMILY fam_1_c, d) AS SELECT * FROM abcd; 268 269 query TT 270 SHOW CREATE TABLE foo12 271 ---- 272 foo12 CREATE TABLE foo12 ( 273 a INT8 NOT NULL, 274 b INT8 NULL, 275 c INT8 NULL, 276 d INT8 NULL, 277 CONSTRAINT "primary" PRIMARY KEY (a ASC), 278 FAMILY f1 (a, b, d), 279 FAMILY fam_1_c (c) 280 ) 281 282 # Test constraint style definition of column families. 283 statement ok 284 CREATE TABLE foo13 (a, b, c, d, PRIMARY KEY(a, b), FAMILY pk (a, b), FAMILY (c, d)) AS SELECT * FROM abcd; 285 286 query TT 287 SHOW CREATE TABLE foo13 288 ---- 289 foo13 CREATE TABLE foo13 ( 290 a INT8 NOT NULL, 291 b INT8 NOT NULL, 292 c INT8 NULL, 293 d INT8 NULL, 294 CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC), 295 FAMILY pk (a, b), 296 FAMILY fam_1_c_d (c, d) 297 ) 298 299 # Test renaming columns still preserves the column families. 300 statement ok 301 ALTER TABLE foo13 RENAME d TO z 302 303 statement ok 304 ALTER TABLE foo13 RENAME c TO e 305 306 query TT 307 SHOW CREATE TABLE foo13 308 ---- 309 foo13 CREATE TABLE foo13 ( 310 a INT8 NOT NULL, 311 b INT8 NOT NULL, 312 e INT8 NULL, 313 z INT8 NULL, 314 CONSTRAINT "primary" PRIMARY KEY (a ASC, b ASC), 315 FAMILY pk (a, b), 316 FAMILY fam_1_c_d (e, z) 317 ) 318 319 # Regression test for #41004 320 statement ok 321 CREATE TABLE foo41004 (x, y, z, FAMILY (y), FAMILY (x), FAMILY (z)) AS 322 VALUES (1, 2, NULL::INT) 323 324 query III 325 SELECT * FROM foo41004 326 ---- 327 1 2 NULL 328 329 # Test CREATE TABLE AS with a correlated subquery. 330 statement ok 331 CREATE TABLE ab (a INT PRIMARY KEY, b INT) 332 333 statement ok 334 CREATE TABLE cd (c INT PRIMARY KEY, b INT) 335 336 statement ok 337 INSERT INTO ab VALUES (1, 1), (2, 2), (3, 3) 338 339 statement ok 340 INSERT INTO cd VALUES (2, 2), (3, 3), (4, 4) 341 342 statement ok 343 CREATE TABLE t AS SELECT a, b, EXISTS(SELECT c FROM cd WHERE cd.c=ab.a) FROM ab; 344 345 query IIB rowsort 346 SELECT * FROM t 347 ---- 348 1 1 false 349 2 2 true 350 3 3 true 351 352 # Test CREATE TABLE AS with a mutation. 353 statement ok 354 CREATE TABLE t2 AS SELECT * FROM [DELETE FROM t WHERE b>2 RETURNING a,b] 355 356 # TODO(radu): this should contain (3,3); bug tracked by #39197. 357 query II 358 SELECT * FROM t2 359 ---- 360 361 query IIB rowsort 362 SELECT * FROM t 363 ---- 364 1 1 false 365 2 2 true