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
|
# name: test/sql/function/autocomplete/tpch.test
# description: Test sql_auto_complete
# group: [autocomplete]
require autocomplete
require tpch
# column names
statement ok
CALL dbgen(sf=0);
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete('SELECT l_ord') LIMIT 1;
----
l_orderkey 7
# arithmetic
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete('SELECT 1 + l_ord') LIMIT 1;
----
l_orderkey 11
# function calls
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete('SELECT min(l_ord') LIMIT 1;
----
l_orderkey 11
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete('SELECT min(42, l_ord') LIMIT 1;
----
l_orderkey 15
# like
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete('SELECT ''test_string'' LIKE l_c') LIMIT 1;
----
l_comment 26
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete('SELECT l_orderkey FROM lin') LIMIT 1;
----
lineitem 23
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete('SELECT l_orderkey FROM lineitem, ord') LIMIT 1;
----
orders 33
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete('SELECT l_orderkey FROM lineitem JOIN ord') LIMIT 1;
----
orders 37
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete('SELECT (SELECT SUM(l_orderkey) FROM lineit') LIMIT 1;
----
lineitem 36
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete('SELECT * FROM (FROM lineit') LIMIT 1;
----
lineitem 20
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete('INSERT INTO lin') LIMIT 1;
----
lineitem 12
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete('DROP TABLE lin') LIMIT 1;
----
lineitem 11
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete('COPY lin') LIMIT 1;
----
lineitem 5
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete('DROP TABLE na') LIMIT 1;
----
nation 11
query III
SELECT suggestion, suggestion_start, suggestion_type FROM sql_auto_complete('SELECT * FROM partsupp JOIN supplier USING (ps_su') LIMIT 1;
----
ps_suppkey 44 column
# more complex queries
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete($$
SELECT
nation,
o_year,
sum(amount) AS sum_profit
FROM (
SELECT
n_name AS nation,
extract(year FROM o_orderdate) AS o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
FROM
part,
supplier,
lineitem,
partsupp,
orders,
nation
WHERE
s_suppkey = l_suppkey
AND ps_suppkey = l_suppkey
AND ps_partkey = l_partkey
AND p_partkey = l_partkey
AND o_orderkey = l_orderkey
AND s_nationkey = n_nat$$) LIMIT 1;
----
n_nationkey 552
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete($$
SELECT
l_returnflag,
l_linestatus,
sum(l_quantity) AS sum_qty,
sum(l_extendedprice) AS sum_base_price,
sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
avg(l_quantity) AS avg_qty,
avg(l_extendedprice) AS avg_price,
avg(l_discount) AS avg_disc,
count(*) AS count_order
FROM
lineitem
WHERE
l_shipdate <= CAST('1998-09-02' AS date)
GROUP BY
l_ret$$) LIMIT 1;
----
l_returnflag 470
query II
SELECT suggestion, suggestion_start FROM sql_auto_complete($$
SELECT
nation,
o_year,
sum(amount) AS sum_profit
FROM (
SELECT
n_name AS nation,
extract(year FROM o_orderdate) AS o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
FROM
part,
supplier,
line$$) LIMIT 1;
----
lineitem 287
|
