forked from MaterializeInc/materialize
-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathsqlsmith.slt
164 lines (148 loc) · 7.03 KB
/
sqlsmith.slt
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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
# https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/sqlsmith
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.
mode cockroach
# This file contains regression tests discovered by sqlsmith.
# Regression: #28836 (nulls in string_agg)
statement ok
SELECT subq_0.c3 AS c0, subq_0.c6 AS c1, subq_0.c4 AS c2, CASE WHEN (SELECT start_key FROM crdb_internal.ranges LIMIT 1 OFFSET 6) < CAST(NULLIF(pg_catalog.string_agg(CAST((SELECT start_key FROM crdb_internal.ranges LIMIT 1 OFFSET 7) AS BYTEA), CAST((SELECT pg_catalog.xor_agg(tgargs) FROM pg_catalog.pg_trigger) AS BYTEA)) OVER (PARTITION BY subq_0.c0 ORDER BY subq_0.c0, subq_0.c5, subq_0.c2), CAST(NULL AS BYTEA)) AS BYTEA) THEN subq_0.c6 ELSE subq_0.c6 END AS c3, subq_0.c2 AS c4, subq_0.c7 AS c5, CAST(COALESCE(subq_0.c7, subq_0.c7) AS INT8) AS c6 FROM (SELECT ref_0.table_name AS c0, ref_0.table_catalog AS c1, ref_0.table_type AS c2, (SELECT rolcreatedb FROM pg_catalog.pg_roles LIMIT 1 OFFSET 79) AS c3, ref_0.table_name AS c4, ref_0.version AS c5, ref_0.version AS c6, ref_0.version AS c7 FROM information_schema.tables AS ref_0 WHERE (ref_0.version IS NOT NULL) OR (pg_catalog.set_masklen(CAST(CAST(NULL AS INET) AS INET), CAST(ref_0.version AS INT8)) != (SELECT pg_catalog.max(client_addr) FROM pg_catalog.pg_stat_activity)) LIMIT 101) AS subq_0 WHERE subq_0.c7 IS NOT NULL
# Regression: make sure lookup join planNode propagates its close signal. This
# query could panic otherwise with a failure to empty all memory accounts.
statement ok
CREATE TABLE a (a INT PRIMARY KEY);
statement ok
SELECT true FROM (SELECT ref_1.a AS c0 FROM crdb_internal.cluster_queries AS ref_0 JOIN a AS ref_1 ON (ref_0.node_id = ref_1.a) WHERE (SELECT a from a limit 1 offset 1) is null);
# Regression: #34437 (union all could produce panic in distsql planning)
statement ok
CREATE TABLE table8 (col1 TIME, col2 BYTEA, col4 OID, col6 NAME, col9 TIMESTAMP, PRIMARY KEY (col1));
statement ok
CREATE TABLE table5 (col0 TIME NULL, col1 OID, col3 INET, PRIMARY KEY (col1 ASC));
statement ok
INSERT INTO table8 (col1, col2, col4, col6)
VALUES ('19:06:18.321589', NULL, NULL, NULL)
UNION ALL (SELECT NULL, NULL, NULL, NULL FROM table5 AS tab_8);
# Regression: #36441 (raw indexed var can't be type checked)
query TO
WITH
with_20394 (col_162526)
AS (
SELECT
*
FROM
(
VALUES
(
'd2d225e2-e9be-4420-a645-d1b8f577511c':::UUID
)
)
AS tab_25520 (col_162526)
UNION ALL
SELECT
*
FROM
(
VALUES
(
'1d6eaf81-8a2c-43c5-a495-a3b102917ab1':::UUID
)
)
AS tab_25521 (col_162527)
)
SELECT
max(with_20394.col_162526::UUID)::UUID AS col_162534,
3697877132:::OID AS col_162541
FROM
with_20394
GROUP BY
with_20394.col_162526
ORDER BY
with_20394.col_162526 ASC
----
1d6eaf81-8a2c-43c5-a495-a3b102917ab1 3697877132
d2d225e2-e9be-4420-a645-d1b8f577511c 3697877132
# Regression: #36830 (can't run wrapped window node)
statement ok
CREATE TABLE table9 (a INT8);
statement ok
INSERT INTO table9 SELECT lag(a) OVER (PARTITION BY a) FROM table9;
# Regression: #36607 (can't serialize or type-check arrays of NULL properly)
query TTTT
WITH
with_194015 (col_1548014)
AS (
SELECT
*
FROM
(
VALUES
(('-28 years -2 mons -677 days -11:53:30.528699':::INTERVAL::INTERVAL + '11:55:41.419498':::TIME::TIME)::TIME + '1973-01-24':::DATE::DATE),
('1970-01-11 01:38:09.000155+00:00':::TIMESTAMP),
('1970-01-09 07:04:13.000247+00:00':::TIMESTAMP),
('1970-01-07 14:19:52.000951+00:00':::TIMESTAMP),
(NULL)
)
AS tab_240443 (col_1548014)
),
with_194016 (col_1548015, col_1548016, col_1548017)
AS (
SELECT
*
FROM
(
VALUES
(
'160.182.25.199/22':::INET::INET << 'c2af:30cb:5db8:bb79:4d11:2d0:1de8:bcea/59':::INET::INET,
'09:14:05.761109':::TIME::TIME + '4 years 7 mons 345 days 23:43:13.325036':::INTERVAL::INTERVAL,
B'0101010110101011101001111010100011001111001110001000101100011001101'
),
(false, '14:36:41.282187':::TIME, B'011111111011001100000001101101011111110110010011110100110111100')
)
AS tab_240444 (col_1548015, col_1548016, col_1548017)
),
with_194017 (col_1548018)
AS (SELECT * FROM (VALUES ('43a30bc5-e412-426d-b99a-65783a7ed445':::UUID), (NULL), (crdb_internal.cluster_id()::UUID)) AS tab_240445 (col_1548018))
SELECT
CASE
WHEN false THEN age('1970-01-09 08:48:24.000568+00:00':::TIMESTAMPTZ::TIMESTAMPTZ, '1970-01-07 08:40:45.000483+00:00':::TIMESTAMPTZ::TIMESTAMPTZ)::INTERVAL
ELSE (
(
(-0.02805450661234963150):::DECIMAL::DECIMAL
* array_position(
(gen_random_uuid()::UUID::UUID || (NULL::UUID || NULL::UUID[])::UUID[])::UUID[],
'5f29920d-7db1-4efc-b1cc-d1a7d0bcf145':::UUID::UUID
)::INT8::INT8
)::DECIMAL
* age('1970-01-04 07:17:45.000268+00:00':::TIMESTAMPTZ::TIMESTAMPTZ, NULL::TIMESTAMPTZ)::INTERVAL::INTERVAL
)
END::INTERVAL
+ '-21 years -10 mons -289 days -13:27:05.205069':::INTERVAL::INTERVAL
AS col_1548019,
'1984-01-07':::DATE AS col_1548020,
NULL AS col_1548021,
'f96fd19a-d2a9-4d98-81dd-97e3fc2a45d2':::UUID AS col_1548022
FROM
with_194015
ORDER BY
with_194015.col_1548014 DESC
LIMIT
4:::INT8;
----
NULL 1984-01-07 00:00:00 +0000 +0000 NULL f96fd19a-d2a9-4d98-81dd-97e3fc2a45d2
NULL 1984-01-07 00:00:00 +0000 +0000 NULL f96fd19a-d2a9-4d98-81dd-97e3fc2a45d2
NULL 1984-01-07 00:00:00 +0000 +0000 NULL f96fd19a-d2a9-4d98-81dd-97e3fc2a45d2
NULL 1984-01-07 00:00:00 +0000 +0000 NULL f96fd19a-d2a9-4d98-81dd-97e3fc2a45d2