1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
|
# name: test/sql/create/create_as.test
# description: Test CREATE TABLE AS SELECT (CTAS) statements
# group: [create]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE tbl1 AS SELECT 1;
query I
SELECT * FROM tbl1;
----
1
statement ok
CREATE TABLE tbl2 AS SELECT 2 AS f;
query I
SELECT * FROM tbl2;
----
2
statement ok
CREATE OR REPLACE TABLE tbl3 AS SELECT 3;
query I
SELECT * FROM tbl3;
----
3
statement error
CREATE TABLE tbl1 AS SELECT 3;
----
statement ok
CREATE OR REPLACE TABLE tbl1 AS SELECT 4;
query I
SELECT * FROM tbl1;
----
4
statement ok
CREATE OR REPLACE TABLE tbl1 AS SELECT 'hello' UNION ALL SELECT 'world';
query I
SELECT * FROM tbl1;
----
hello
world
statement ok
CREATE OR REPLACE TABLE tbl1 AS SELECT 5 WHERE false;
query I
SELECT * FROM tbl1;
----
statement error
CREATE TABLE tbl4 IF NOT EXISTS AS SELECT 4;
----
Parser Error: syntax error at or near "IF"
statement error
CREATE OR REPLACE TABLE tbl4 IF NOT EXISTS AS SELECT 4;
----
Parser Error: syntax error at or near "IF"
### CREATE TABLE t(col1, col2) AS SELECT ...
statement ok
CREATE TABLE tbl4(col1, col2) AS SELECT 1, 'hello';
query II
SELECT * FROM tbl4;
----
1 hello
statement ok
CREATE OR REPLACE TABLE tbl4(col1, col2) AS SELECT 2, 'duck';
query II
SELECT * FROM tbl4;
----
2 duck
statement ok
CREATE TABLE IF NOT EXISTS tbl5(col1, col2) AS SELECT 3, 'database';
query II
SELECT * FROM tbl5;
----
3 database
# define a column name need quote
statement ok
CREATE OR REPLACE TABLE tbl5(col1, "col need ' quote") AS SELECT 3.5, 'quote';
query II
SELECT * FROM tbl5;
----
3.5 quote
#colname and query mismatch
statement ok
CREATE TABLE tbl6(col1) AS SELECT 4 ,'mismatch';
query II
SELECT * FROM tbl6;
----
4 mismatch
statement error
CREATE TABLE tbl7(col1, col2) AS SELECT 5;
----
Binder Error: Target table has more colum names than query result.
# WITH NO DATA / WITH DATA
statement ok
CREATE TABLE tbl8 AS SELECT 42 WITH NO DATA
query I
SELECT COUNT(*) FROM tbl8;
----
0
statement ok
CREATE TABLE tbl9 AS SELECT 42 WITH DATA
query I
SELECT COUNT(*) FROM tbl9;
----
1
|
