/
default
137 lines (105 loc) · 3.64 KB
/
default
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
136
137
# LogicTest: local local-opt fakedist fakedist-opt fakedist-metadata
statement error expected DEFAULT expression to have type int, but 'false' has type bool
CREATE TABLE t (a INT PRIMARY KEY DEFAULT false)
statement error variable sub-expressions are not allowed in DEFAULT
CREATE TABLE t (a INT PRIMARY KEY DEFAULT (SELECT 1))
statement error variable sub-expressions are not allowed in DEFAULT
CREATE TABLE t (a INT PRIMARY KEY DEFAULT b)
# Issue #14308: support tables with DEFAULT NULL columns.
statement ok
CREATE TABLE null_default (ts TIMESTAMP PRIMARY KEY NULL DEFAULT NULL)
# Aggregate function calls in CHECK are not ok.
statement error aggregate functions are not allowed in DEFAULT
CREATE TABLE bad (a INT DEFAULT count(1))
# Window function calls in CHECK are not ok.
statement error window functions are not allowed in DEFAULT
CREATE TABLE bad (a INT DEFAULT count(1) OVER ())
statement ok
CREATE TABLE t (
a INT PRIMARY KEY DEFAULT 42,
b TIMESTAMP DEFAULT now(),
c FLOAT DEFAULT random(),
d DATE DEFAULT now()
)
query TTBTTTB colnames
SHOW COLUMNS FROM t
----
column_name data_type is_nullable column_default generation_expression indices is_hidden
a INT8 false 42:::INT8 · {primary} false
b TIMESTAMP true now():::TIMESTAMP · {} false
c FLOAT8 true random() · {} false
d DATE true now():::DATE · {} false
statement ok
INSERT INTO t VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT)
query IBBB
SELECT a, b <= now(), c >= 0.0, d <= now() FROM t
----
42 true true true
statement ok
TRUNCATE TABLE t
statement ok
INSERT INTO t DEFAULT VALUES
query IBBB
SELECT a, b <= now(), c >= 0.0, d <= now() FROM t
----
42 true true true
statement ok
INSERT INTO t (a) VALUES (1)
query IBBB
SELECT a, b <= now(), c >= 0.0, d <= now() FROM t WHERE a = 1
----
1 true true true
statement ok
INSERT INTO t VALUES (2)
query IBBB
SELECT a, b <= now(), c >= 0.0, d <= now() FROM t WHERE a = 2
----
2 true true true
statement ok
UPDATE t SET (b, c) = ('2015-09-18 00:00:00', -1.0)
statement ok
UPDATE t SET b = DEFAULT WHERE a = 1
query IBBB
SELECT a, b <= now(), c = -1.0, d <= now() FROM t WHERE a = 1
----
1 true true true
statement ok
UPDATE t SET (b, c) = (DEFAULT, DEFAULT) WHERE a = 2
query IBBB
SELECT a, b <= now(), c >= 0.0, d <= now() FROM t WHERE a = 2
----
2 true true true
statement ok
UPDATE t SET b = DEFAULT, c = DEFAULT, d = DEFAULT
statement ok
UPDATE t SET (b) = (DEFAULT), (c) = (DEFAULT), (d) = (DEFAULT)
# Test a table without a default and with a null default
statement ok
CREATE TABLE v (
a INT PRIMARY KEY,
b TIMESTAMP NULL DEFAULT NULL,
c INT
)
statement ok
UPDATE v SET a = DEFAULT
statement ok
UPDATE v SET (a, c) = (DEFAULT, DEFAULT)
query TTBTTTB colnames
SHOW COLUMNS FROM v
----
column_name data_type is_nullable column_default generation_expression indices is_hidden
a INT8 false NULL · {primary} false
b TIMESTAMP true NULL · {} false
c INT8 true NULL · {} false
# Regression test for #34901: verify that builtins can be used in default value
# expressions without a "memory budget exceeded" error while backfilling
statement ok
CREATE TABLE t34901 (x STRING)
statement ok
INSERT INTO t34901 VALUES ('a')
statement ok
ALTER TABLE t34901 ADD COLUMN y STRING DEFAULT (concat('b', 'c'))
query TT
SELECT * FROM t34901
----
a bc