2
# This grammar attempts to create realistic queries against the DBT-3 data set. The following rules apply:
4
# * standard DBT-3 prefixes are used for stuff ,e.g. ps = partsupp , r = region , etc.
6
# * each join is one of several plausible join chains allowed by the dataset
8
# * each WHERE condition is realistic for the column being queried and only uses table that are known to participate in the particular join chain
10
# * More AND is used as opposed to OR to keep with the spirit of the original queries from the benchmark
12
# * MariaDB's table elimination will remove unnecessary tables that have been joined but for which no WHERE conditions apply
14
# * The joinable fields are indexed in both tables and most WHERE conditions also involve indexes. To provide some non-indexed clauses
15
# * we include some WHERE conditions on the comment field that appears in each table
17
# * In order to have realistic HAVING, for the HAVING queries we only use fields that hold currency ammounts
21
select_r_n_s_ps_l_o_c | select_p_ps_s_n_r | select_p_ps_l_o_c_r_n_s | currency_select_p_ps_s_l_o_c;
23
# region -> nation -> supplier -> partsupp -> lineitem -> orders -> customer
25
select_r_n_s_ps_l_o_c:
26
SELECT select_list_r_n_s_ps_l_o_c join_r_n_s_ps_l_o_c WHERE where_r_n_s_ps_l_o_c order_by_1_2 |
27
SELECT aggregate field_r_n_s_ps_l_o_c ) join_r_n_s_ps_l_o_c WHERE where_r_n_s_ps_l_o_c |
28
SELECT field_r_n_s_ps_l_o_c , aggregate field_r_n_s_ps_l_o_c ) join_r_n_s_ps_l_o_c WHERE where_r_n_s_ps_l_o_c GROUP BY 1 asc_desc order_by_1 |
29
SELECT field_r_n_s_ps_l_o_c , field_r_n_s_ps_l_o_c , aggregate field_r_n_s_ps_l_o_c ) join_r_n_s_ps_l_o_c WHERE where_r_n_s_ps_l_o_c GROUP BY 1 asc_desc , 2 asc_desc order_by_1_2 ;
31
# part -> partsupp -> supplier -> nation -> region
34
SELECT select_list_p_ps_s_n_r join_p_ps_s_n_r WHERE where_p_ps_s_n_r order_by_1_2 |
35
SELECT aggregate field_p_ps_s_n_r ) join_p_ps_s_n_r WHERE where_p_ps_s_n_r |
36
SELECT field_p_ps_s_n_r , aggregate field_p_ps_s_n_r ) join_p_ps_s_n_r WHERE where_p_ps_s_n_r GROUP BY 1 asc_desc order_by_1 |
37
SELECT field_p_ps_s_n_r , field_p_ps_s_n_r , aggregate field_p_ps_s_n_r ) join_p_ps_s_n_r WHERE where_p_ps_s_n_r GROUP BY 1 asc_desc , 2 asc_desc order_by_1_2 ;
39
# part -> partsupp -> lineitem -> orders -> customer -> region -> nation -> supplier
41
select_p_ps_l_o_c_r_n_s:
42
SELECT select_list_p_ps_l_o_c_r_n_s join_p_ps_l_o_c_r_n_s WHERE where_p_ps_l_o_c_r_n_s order_by_1_2 |
43
SELECT aggregate field_p_ps_l_o_c_r_n_s ) join_p_ps_l_o_c_r_n_s WHERE where_p_ps_l_o_c_r_n_s |
44
SELECT field_p_ps_l_o_c_r_n_s , aggregate field_p_ps_l_o_c_r_n_s ) join_p_ps_l_o_c_r_n_s WHERE where_p_ps_l_o_c_r_n_s GROUP BY 1 asc_desc order_by_1 |
45
SELECT field_p_ps_l_o_c_r_n_s , field_p_ps_l_o_c_r_n_s , aggregate field_p_ps_l_o_c_r_n_s ) join_p_ps_l_o_c_r_n_s WHERE where_p_ps_l_o_c_r_n_s GROUP BY 1 asc_desc , 2 asc_desc order_by_1_2 ;
47
# part -> partsupp -> lineitem -> orders -> customer with currency fields only
48
# This allows for a meaningful HAVING condition because the type and the spirit of values in the SELECT list will be known
50
currency_select_p_ps_s_l_o_c:
51
SELECT currency_field_p_ps_s_l_o_c AS currency1 , currency_field_p_ps_s_l_o_c AS currency2 join_p_ps_s_l_o_c WHERE where_p_ps_s_l_o_c HAVING currency_having order_by_1_2 |
52
SELECT field_p_ps_s_l_o_c, currency_field_p_ps_s_l_o_c AS currency1 , aggregate currency_field_p_ps_s_l_o_c ) AS currency2 join_p_ps_s_l_o_c WHERE where_p_ps_s_l_o_c GROUP BY 1 , 2 HAVING currency_having order_by_1_2 ;
55
| | | | | | ASC | DESC ;
58
| | ORDER BY 1 ; # 30% of queries have ORDER BY on a single column
61
| | | | | | ORDER BY 1 | ORDER BY 2 | ORDER BY 1 , 2 ; # 30% of queries have ORDER BY on two columns
64
FROM region join_type nation ON ( r_regionkey = n_regionkey ) join_type supplier ON ( s_nationkey = n_nationkey ) join_type partsupp ON ( s_suppkey = ps_suppkey ) join_type lineitem ON ( partsupp_lineitem_join_cond ) join_type orders ON ( l_orderkey = o_orderkey ) join_type customer ON ( o_custkey = c_custkey ) ;
67
FROM part join_type partsupp ON ( p_partkey = ps_partkey ) join_type supplier ON ( ps_suppkey = s_suppkey ) join_type nation ON ( s_nationkey = n_nationkey ) join_type region ON ( n_regionkey = r_regionkey ) ;
69
join_p_ps_l_o_c_r_n_s:
70
FROM part join_type partsupp ON ( p_partkey = ps_partkey ) join_type lineitem ON ( partsupp_lineitem_join_cond ) join_type orders ON ( l_orderkey = o_orderkey ) join_type customer ON ( o_custkey = c_custkey ) join_type nation ON ( c_nationkey = n_nationkey ) join_type supplier ON ( s_nationkey = n_nationkey ) join_type region ON ( n_regionkey = r_regionkey ) ;
73
FROM part join_type partsupp ON ( p_partkey = ps_partkey ) join_type supplier ON (s_suppkey = ps_suppkey) join_type lineitem ON ( partsupp_lineitem_join_cond ) join_type orders ON ( l_orderkey = o_orderkey ) join_type customer ON ( o_custkey = c_custkey ) ;
76
JOIN | LEFT JOIN | RIGHT JOIN ;
78
partsupp_lineitem_join_cond:
79
ps_partkey = l_partkey AND ps_suppkey = l_suppkey |
80
ps_partkey = l_partkey AND ps_suppkey = l_suppkey |
81
ps_partkey = l_partkey | ps_suppkey = l_suppkey ;
83
lineitem_orders_join_cond:
84
l_orderkey = o_orderkey | lineitem_date_field = o_orderdate ;
87
l_shipDATE | l_commitDATE | l_receiptDATE ;
89
select_list_r_n_s_ps_l_o_c:
90
field_r_n_s_ps_l_o_c , field_r_n_s_ps_l_o_c | field_r_n_s_ps_l_o_c , select_list_r_n_s_ps_l_o_c ;
93
field_r | field_n | field_s | field_ps | field_l | field_o | field_c ;
95
select_list_p_ps_s_n_r:
96
field_p_ps_s_n_r , field_p_ps_s_n_r | field_p_ps_s_n_r , select_list_p_ps_s_n_r ;
99
field_p | field_ps | field_s | field_n | field_r;
101
select_list_p_ps_l_o_c_r_n_s:
102
field_p_ps_l_o_c_r_n_s , field_p_ps_l_o_c_r_n_s | field_p_ps_l_o_c_r_n_s , select_list_p_ps_l_o_c_r_n_s ;
104
field_p_ps_l_o_c_r_n_s:
105
field_p | field_ps | field_l | field_o | field_c | field_r | field_n | field_s ;
108
field_p | field_ps | field_s | field_l | field_o | field_c |;
110
currency_field_p_ps_s_l_o_c:
111
p_retailprice | ps_supplycost | l_extendedprice | o_totalprice | s_acctbal | c_acctbal ;
117
s_suppkey | s_nationkey ;
120
ps_partkey | ps_suppkey ;
123
l_orderkey | l_partkey | l_suppkey | l_linenumber | l_shipDATE | l_commitDATE | l_receiptDATE ;
126
o_orderkey | o_custkey ;
129
c_custkey | c_nationkey ;
138
COUNT( distinct | SUM( distinct | MIN( | MAX( ;
143
where_r_n_s_ps_l_o_c:
144
cond_r_n_s_ps_l_o_c and_or cond_r_n_s_ps_l_o_c and_or cond_r_n_s_ps_l_o_c | where_r_n_s_ps_l_o_c and_or cond_r_n_s_ps_l_o_c ;
146
cond_r | cond_n | cond_s | cond_ps | cond_l | cond_o | cond_c | cond_l_o | cond_l_o | cond_s_c | cond_ps_l ;
149
cond_p_ps_s_n_r and_or cond_p_ps_s_n_r and_or cond_p_ps_s_n_r | where_p_ps_s_n_r and_or cond_p_ps_s_n_r ;
151
cond_p | cond_ps | cond_s | cond_n | cond_r ;
154
where_p_ps_l_o_c_r_n_s:
155
cond_p_ps_l_o_c_r_n_s and_or cond_p_ps_l_o_c_r_n_s and_or cond_p_ps_l_o_c_r_n_s | where_p_ps_l_o_c_r_n_s and_or cond_p_ps_l_o_c_r_n_s ;
156
cond_p_ps_l_o_c_r_n_s:
157
cond_p | cond_ps | cond_l | cond_o | cond_c | cond_r | cond_n | cond_s ;
160
cond_p_ps_s_l_o_c and_or cond_p_ps_s_l_o_c and_or cond_p_ps_s_l_o_c | where_p_ps_s_l_o_c and_or cond_p_ps_s_l_o_c ;
163
cond_p | cond_ps | cond_s | cond_l | cond_o | cond_c ;
166
currency_having_item |
167
currency_having_item and_or currency_having_item ;
169
currency_having_item:
170
currency_having_field currency_clause ;
172
currency_having_field:
173
currency1 | currency2 ;
176
AND | AND | AND | AND | OR ;
179
# Multi-table WHERE conditions
183
l_extendedprice comp_op o_totalprice | lineitem_date_field comp_op o_orderdate ;
186
ps_availqty comp_op l_quantity | ps_supplycost comp_op l_extendedprice ;
189
c_nationkey comp_op s_nationkey ;
192
# Per-table WHERE conditions
196
p_partkey partkey_clause |
197
p_retailprice currency_clause |
198
p_comment comment_clause ;
201
s_suppkey suppkey_clause |
202
s_nationkey nationkey_clause |
203
s_acctbal currency_clause |
204
s_comment comment_clause ;
207
ps_partkey partkey_clause |
208
ps_suppkey suppkey_clause |
209
ps_supplycost currency_clause |
210
ps_comment comment_clause ;
213
l_linenumber linenumber_clause |
214
l_shipDATE shipdate_clause |
215
l_partkey partkey_clause |
216
l_suppkey suppkey_clause |
217
l_receiptDATE receiptdate_clause |
218
l_orderkey orderkey_clause |
219
l_quantity quantity_clause |
220
l_commitDATE commitdate_clause |
221
l_extendedprice currency_clause |
222
l_comment comment_clause ;
225
o_orderkey orderkey_clause |
226
o_custkey custkey_clause |
227
o_totalprice currency_clause |
228
o_comment comment_clause ;
231
c_custkey custkey_clause |
232
c_acctbal currency_clause |
233
c_comment comment_clause ;
236
n_nationkey nationkey_clause |
237
n_comment comment_clause ;
240
r_regionkey regionkey_clause |
241
r_comment comment_clause ;
244
# Per-column WHERE conditions
248
= | = | = | = | != | > | >= | < | <= | <> ;
251
| | | | | | | | | NOT ;
255
not IN ( date_list ) |
259
date_item , date_item |
260
date_list , date_item ;
263
any_date | any_date | any_date | any_date | any_date |
264
any_date | any_date | any_date | any_date | any_date |
265
any_date | any_date | any_date | any_date | any_date |
266
any_date | any_date | any_date | any_date | any_date |
267
'1992-01-08' | '1998-11-27' ;
270
BETWEEN date_item AND date_item |
271
between_two_dates_in_a_year |
272
between_two_dates_in_a_month |
279
{ sprintf("'%04d-%02d-%02d'", $prng->uint16(1992,1998), $prng->uint16(1,12), $prng->uint16(1,28)) } ;
281
between_two_dates_in_a_year:
282
{ my $year = $prng->uint16(1992,1998); return sprintf("BETWEEN '%04d-%02d-%02d' AND '%04d-%02d-%02d'", $year, $prng->uint16(1,12), $prng->uint16(1,28), $year, $prng->uint16(1,12), $prng->uint16(1,28)) } ;
284
between_two_dates_in_a_month:
285
{ my $year = $prng->uint16(1992,1998); my $month = $prng->uint16(1,12); return sprintf("BETWEEN '%04d-%02d-%02d' AND '%04d-%02d-%02d'", $year, $month, $prng->uint16(1,28), $year, $month, $prng->uint16(1,28)) } ;
288
{ my $year = $prng->uint16(1992,1998); my $month = $prng->uint16(1,12); return sprintf("BETWEEN '%04d-%02d-01' AND '%04d-%02d-29'", $year, $month, $year, $month) } ;
293
comp_op linenumber_item |
294
not IN ( linenumber_list ) |
295
BETWEEN linenumber_item AND linenumber_item + linenumber_range ;
298
linenumber_item , linenumber_item |
299
linenumber_item , linenumber_list ;
310
comp_op partkey_item |
311
not IN ( partkey_list ) |
312
BETWEEN partkey_item AND partkey_item + partkey_range ;
315
partkey_item , partkey_item |
316
partkey_item , partkey_list ;
319
_digit | _tinyint_unsigned;
322
_tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned |
323
_tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned |
324
_tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned |
325
_tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned |
331
comp_op suppkey_item |
332
not IN ( suppkey_list ) |
333
BETWEEN suppkey_item AND suppkey_item + _digit ;
339
suppkey_item , suppkey_item |
340
suppkey_item , suppkey_list ;
346
not IN ( date_list ) |
353
not IN ( date_list ) |
359
comp_op orderkey_item |
360
not IN ( orderkey_list ) |
361
BETWEEN orderkey_item AND orderkey_item + orderkey_range ;
364
_tinyint_unsigned | { $prng->uint16(1,1500) } ;
367
orderkey_item , orderkey_item |
368
orderkey_item , orderkey_list ;
371
_digit | _tinyint_unsigned ;
376
comp_op quantity_item |
377
not IN ( quantity_list ) |
378
BETWEEN quantity_item AND quantity_item + quantity_range ;
381
quantity_item , quantity_item |
382
quantity_item , quantity_list ;
385
_digit | { $prng->uint16(1,50) } ;
393
comp_op custkey_item |
394
not IN ( custkey_list ) |
395
BETWEEN custkey_item AND custkey_item + custkey_range ;
398
_tinyint_unsigned | { $prng->uint16(1,150) } ;
401
custkey_item , custkey_item |
402
custkey_item , custkey_list ;
405
_digit | _tinyint_unsigned ;
410
comp_op nationkey_item |
411
not IN ( nationkey_list ) |
412
BETWEEN nationkey_item AND nationkey_item + nationkey_range ;
415
_digit | { $prng->uint16(0,24) } ;
418
nationkey_item , nationkey_item |
419
nationkey_item , nationkey_list ;
422
_digit | _tinyint_unsigned ;
427
comp_op regionkey_item |
428
not IN ( regionkey_list ) |
429
BETWEEN regionkey_item AND regionkey_item + regionkey_range ;
435
regionkey_item , regionkey_item |
436
regionkey_item , regionkey_list ;
444
IS NOT NULL | IS NOT NULL | IS NOT NULL |
445
comp_op _varchar(1) |
446
comment_not LIKE CONCAT( comment_count , '%' ) |
447
BETWEEN _varchar(1) AND _varchar(1) ;
453
_varchar(1) | _varchar(1) | _varchar(1) | _varchar(1) | _varchar(2) ;
458
comp_op currency_item |
459
BETWEEN currency_item AND currency_item + currency_range ;
462
_digit | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _mediumint_unsigned ;