2
# This grammar performs DML operations against the DBT-3 dataset. It attempts to use
3
# realistic values and WHERE conditions by using literals that are appropriate for the particular
4
# column that is being used .
8
START TRANSACTION ; AUTOCOMMIT OFF ; SET AUTOCOMMIT = OFF ;
11
transaction_body ; commit_rollback ;
14
supplier | part | partsupp | customer | orders | lineitem | nation | region ;
17
COMMIT | COMMIT | COMMIT | COMMIT | ROLLBACK ;
21
dml ; transaction_body ;
24
select | select | select | select | select |
25
insert | insert | insert | insert | insert |
26
insert | insert | insert | insert | insert |
27
update | update | update | update | delete ;
30
SELECT * FROM supplier WHERE cond_s and_or cond_s |
31
SELECT p_partkey , p_name , p_mfgr , p_brand , p_type , p_size , p_container , p_comment FROM part WHERE cond_p and_or cond_p |
32
SELECT * FROM partsupp WHERE cond_ps and_or cond_ps |
33
SELECT * FROM customer WHERE cond_c and_or cond_c |
34
SELECT o_orderkey , o_custkey , o_orderstatus , o_orderpriority , o_clerk , o_shippriority , o_comment FROM orders WHERE cond_o and_or cond_o |
35
SELECT l_orderkey , l_partkey , l_suppkey , l_linenumber , l_quantity , l_returnflag , l_linestatus , l_shipinstruct , l_shipmode , l_comment FROM lineitem WHERE cond_l and_or cond_l |
36
SELECT * FROM nation WHERE cond_n and_or cond_n |
37
SELECT * FROM region WHERE cond_r and_or cond_r ;
40
insert_s | insert_p | insert_ps | insert_c | insert_o | insert_l | insert_n | insert_r ;
43
INSERT INTO supplier ( s_suppkey, s_name , s_address , s_nationkey , s_phone , s_acctbal , s_comment ) insert_s2 ;
46
SELECT suppkey_item , s_name , s_address , s_nationkey , s_phone , s_acctbal , s_comment FROM supplier WHERE cond_s and_or cond_s ORDER BY s_suppkey |
51
values_s , value_list_s ;
54
( suppkey_item , s_name_item , address_item , nationkey_item , phone_item , acctbal_item , comment_item ) ;
57
INSERT INTO part ( p_partkey , p_name , p_mfgr , p_brand , p_type , p_size , p_container , p_retailprice , p_comment ) insert_p2 ;
60
SELECT partkey_item , p_name , p_mfgr , p_brand , p_type , p_size , p_container , p_retailprice , p_comment FROM part WHERE cond_p and_or cond_p ORDER BY p_partkey |
65
values_p , value_list_p ;
68
( partkey_item , p_name_item , mfgr_item, brand_item , type_item , size_item , container_item , retailprice_item , comment_item ) ;
71
INSERT INTO partsupp ( ps_partkey , ps_suppkey , ps_availqty , ps_supplycost , ps_comment ) insert_ps2 ;
74
SELECT partkey_item , ps_suppkey , ps_availqty , ps_supplycost , ps_comment FROM partsupp WHERE cond_ps and_or cond_ps ORDER BY ps_partkey , ps_suppkey |
75
VALUES value_list_ps ;
79
values_ps , value_list_ps ;
82
( partkey_item , suppkey_item , availqty_item , supplycost_item , comment_item ) ;
85
INSERT INTO customer ( c_custkey , c_name , c_address , c_nationkey , c_phone , c_acctbal , c_mktsegment , c_comment ) insert_c2 ;
88
SELECT custkey_item , c_name , c_address , c_nationkey , c_phone , c_acctbal , c_mktsegment , c_comment FROM customer WHERE cond_c and_or cond_c ORDER BY c_custkey |
93
values_c , value_list_c ;
96
( custkey_item , c_name_item , address_item , nationkey_item , phone_item , acctbal_item , mktsegment_item , comment_item ) ;
99
INSERT INTO orders ( o_orderkey , o_custkey , o_orderstatus , o_totalprice , o_orderDATE , o_orderpriority , o_clerk , o_shippriority , o_comment ) insert_o2 ;
102
SELECT orderkey_item , o_custkey, o_orderstatus, o_totalprice , o_orderDATE , o_orderpriority , o_clerk , o_shippriority , o_comment FROM orders WHERE cond_o and_or cond_o ORDER BY o_orderkey |
103
VALUES value_list_o ;
107
values_o , value_list_o ;
110
( orderkey_item , custkey_item , orderstatus_item , totalprice_item , date_item , orderpriority_item , clerk_item , shippriority_item , comment_item ) ;
113
INSERT INTO lineitem ( l_orderkey , l_partkey , l_suppkey , l_linenumber , l_quantity , l_extendedprice , l_discount , l_tax , l_returnflag , l_linestatus , l_shipDATE , l_commitDATE , l_receiptDATE , l_shipinstruct , l_shipmode , l_comment ) insert_l2 ;
116
SELECT orderkey_item , l_partkey , l_suppkey , l_linenumber , l_quantity , l_extendedprice , l_discount , l_tax , l_returnflag , l_linestatus , l_shipDATE , l_commitDATE , l_receiptDATE , l_shipinstruct , l_shipmode , l_comment FROM lineitem WHERE cond_l and_or cond_l ORDER BY l_orderkey , l_linenumber |
117
VALUES value_list_l ;
121
values_l , value_list_l ;
124
( orderkey_item , partkey_item , suppkey_item , linenumber_item , quantity_item , extendedprice_item , discount_item , tax_item , returnflag_item , linestatus_item , date_item , date_item , date_item , shipinstruct_item , shipmode_item , comment_item ) ;
127
INSERT INTO nation ( n_nationkey , n_name , n_regionkey , n_comment ) insert_n2 ;
130
SELECT nationkey_item , n_name , n_regionkey , n_comment FROM nation WHERE cond_n and_or cond_n ORDER BY n_nationkey |
131
VALUES values_list_n ;
135
values_n , values_list_n ;
138
( nationkey_item , n_name_item , regionkey_item , comment_item ) ;
141
INSERT INTO region ( r_regionkey , r_name , r_comment ) insert_r2 ;
144
SELECT regionkey_item , r_name , r_comment FROM region WHERE cond_r and_or cond_r ORDER BY r_regionkey |
145
VALUES value_list_r ;
149
values_r , value_list_r ;
152
( regionkey_item , r_name_item , comment_item ) ;
155
UPDATE supplier SET set_s_list WHERE cond_s and_or cond_s |
156
UPDATE part SET set_p_list WHERE cond_p and_or cond_p |
157
UPDATE partsupp SET set_ps_list WHERE cond_ps and_or cond_ps |
158
UPDATE customer SET set_c_list WHERE cond_c and_or cond_c |
159
UPDATE orders SET set_o_list WHERE cond_o and_or cond_o |
160
UPDATE lineitem SET set_l_list WHERE cond_l and_or cond_l |
161
UPDATE nation SET set_n_list WHERE cond_n and_or cond_n |
162
UPDATE region SET set_r_list WHERE cond_r and_or cond_r
166
set_s | set_s , set_s_list ;
169
set_p | set_p , set_p_list ;
172
set_ps | set_ps , set_ps_list ;
175
set_c | set_c , set_c_list ;
178
set_o | set_o , set_o_list ;
181
set_l | set_l , set_l_list ;
184
set_n | set_n , set_n_list ;
187
set_r | set_r , set_r_list ;
190
# s_suppkey = suppkey_item |
191
s_name = s_name_item |
192
s_address = address_item |
193
s_phone = phone_item |
194
# s_acctbal = acctbal_item |
195
s_comment = comment_item |
196
s_nationkey = nationkey_item ;
199
# p_partkey = partkey_item |
200
p_name = p_name_item |
202
p_brand = brand_item |
205
p_container = container_item |
206
# p_retailprice = retailprice_item |
207
p_comment = comment_item ;
210
# ps_partkey = partkey_item |
211
ps_suppkey = suppkey_item |
212
# ps_supplycost = supplcost_item
213
ps_comment = comment_item ;
216
# c_custkey = custkey_item |
217
c_name = c_name_item |
218
c_address = address_item |
219
c_nationkey = nationkey_item |
220
c_phone = phone_item |
221
# c_acctbal = acctbal_item |
222
c_mktsegment = mktsegment_item |
223
c_comment = comment_item ;
226
# o_orderkey = orderkey_item |
227
o_custkey = custkey_item |
228
o_orderstatus = orderstatus_item |
229
# o_totalprice = totalprice_item |
230
o_orderDATE = date_item |
231
o_orderpriority = orderpriority_item |
232
o_clerk = clerk_item |
233
o_shippriority = shippriority_item |
234
o_comment = comment_item ;
237
# l_orderkey = orderkey_item |
238
l_partkey = partkey_item |
239
l_suppkey = suppkey_item |
240
l_linenumber = linenumber_item |
241
l_quantity = quantity_item |
242
# l_extendedprice = extendedprice_item |
243
# l_discount = discount_item |
245
l_returnflag = returnflag_item |
246
l_linestatus = linestatus_item |
247
l_shipDATE = date_item |
248
l_commitDATE = date_item |
249
l_receiptDATE = date_item |
250
l_shipinstruct = shipinstruct_item |
251
l_shipmode = shipmode_item |
252
l_comment = comment_item ;
255
# n_nationkey = nationkey_item |
256
n_name = n_name_item |
257
n_regionkey = regionkey_item |
258
n_comment = comment_item ;
261
# r_regionkey = regionkey_item |
262
r_name = r_name_item |
263
r_comment = comment_item ;
266
DELETE FROM supplier WHERE cond_s and_or cond_s |
267
DELETE FROM part WHERE cond_p and_or cond_p |
268
DELETE FROM partsupp WHERE cond_ps and_or cond_ps |
269
DELETE FROM customer WHERE cond_c and_or cond_c |
270
DELETE FROM orders WHERE cond_o and_or cond_o |
271
DELETE FROM lineitem WHERE cond_l and_or cond_l |
272
DELETE FROM nation WHERE cond_n and_or cond_n |
273
DELETE FROM region WHERE cond_r and_or cond_r ;
280
s_suppkey | s_nationkey ;
283
ps_partkey | ps_suppkey ;
286
l_orderkey | l_partkey | l_suppkey | l_linenumber | l_shipDATE | l_commitDATE | l_receiptDATE ;
289
o_orderkey | o_custkey ;
292
c_custkey | c_nationkey ;
304
# Multi-table WHERE conditions
308
l_extendedprice comp_op o_totalprice | lineitem_date_field comp_op o_orderdate ;
311
ps_availqty comp_op l_quantity | ps_supplycost comp_op l_extendedprice ;
314
c_nationkey comp_op s_nationkey ;
317
# Per-table WHERE conditions
321
p_partkey partkey_clause |
322
p_retailprice currency_clause ;
323
# p_comment comment_clause ;
326
s_suppkey suppkey_clause |
327
s_nationkey nationkey_clause |
328
s_acctbal currency_clause ;
330
# s_comment comment_clause ;
333
ps_partkey partkey_clause |
334
ps_suppkey suppkey_clause |
335
ps_supplycost currency_clause ;
336
# ps_comment comment_clause ;
339
l_linenumber linenumber_clause |
340
l_shipDATE shipdate_clause |
341
l_partkey partkey_clause |
342
l_suppkey suppkey_clause |
343
l_receiptDATE receiptdate_clause |
344
l_orderkey orderkey_clause |
345
l_quantity quantity_clause |
346
l_commitDATE commitdate_clause |
347
l_extendedprice currency_clause ;
348
# l_comment comment_clause ;
351
o_orderkey orderkey_clause |
352
o_custkey custkey_clause |
353
o_totalprice currency_clause ;
354
# o_comment comment_clause ;
357
c_custkey custkey_clause |
358
c_acctbal currency_clause ;
359
# c_comment comment_clause ;
362
n_nationkey nationkey_clause ;
363
# n_comment comment_clause ;
366
r_regionkey regionkey_clause ;
367
# r_comment comment_clause ;
370
# Per-column WHERE conditions
374
= | = | = | = | = | > | >= | < | <= | <> ;
377
| | | | | | | | | NOT ;
381
# not IN ( date_list ) |
385
date_item , date_item |
386
date_list , date_item ;
389
any_date | any_date | any_date | any_date | any_date |
390
any_date | any_date | any_date | any_date | any_date |
391
any_date | any_date | any_date | any_date | any_date |
392
any_date | any_date | any_date | any_date | any_date |
393
'1992-01-08' | '1998-11-27' ;
396
not BETWEEN date_item AND date_item |
397
between_two_dates_in_a_year |
398
between_two_dates_in_a_month |
405
{ sprintf("'%04d-%02d-%02d'", $prng->uint16(1992,1998), $prng->uint16(1,12), $prng->uint16(1,27)) } ;
407
between_two_dates_in_a_year:
408
{ my $year = $prng->uint16(1992,1998); return sprintf("BETWEEN '%04d-%02d-%02d' AND '%04d-%02d-%02d'", $year, $prng->uint16(1,12), $prng->uint16(1,27), $year, $prng->uint16(1,12), $prng->uint16(1,27)) } ;
410
between_two_dates_in_a_month:
411
{ 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,27), $year, $month, $prng->uint16(1,27)) } ;
414
{ my $year = $prng->uint16(1992,1998); my $month = $prng->uint16(1,12); return sprintf("BETWEEN '%04d-%02d-01' AND '%04d-%02d-27'", $year, $month, $year, $month) } ;
419
comp_op linenumber_item |
420
# not IN ( linenumber_list ) |
421
not BETWEEN linenumber_item AND linenumber_item + linenumber_range ;
424
linenumber_item , linenumber_item |
425
linenumber_item , linenumber_list ;
436
comp_op partkey_item |
437
# not IN ( partkey_list ) |
438
not BETWEEN partkey_item AND partkey_item + partkey_range ;
441
partkey_item , partkey_item |
442
partkey_item , partkey_list ;
445
_digit | _tinyint_unsigned;
448
_tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned |
449
_tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned |
450
_tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned |
451
_tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned |
457
comp_op suppkey_item |
458
# not IN ( suppkey_list ) |
459
not BETWEEN suppkey_item AND suppkey_item + _digit ;
465
suppkey_item , suppkey_item |
466
suppkey_item , suppkey_list ;
472
# not IN ( date_list ) |
479
# not IN ( date_list ) |
485
comp_op orderkey_item |
486
# not IN ( orderkey_list ) |
487
BETWEEN orderkey_item AND orderkey_item + orderkey_range ;
490
_tinyint_unsigned | { $prng->uint16(1,1500) } ;
493
orderkey_item , orderkey_item |
494
orderkey_item , orderkey_list ;
497
_digit | _tinyint_unsigned ;
502
comp_op quantity_item |
503
# not IN ( quantity_list ) |
504
BETWEEN quantity_item AND quantity_item + quantity_range ;
507
quantity_item , quantity_item |
508
quantity_item , quantity_list ;
511
_digit | { $prng->uint16(1,50) } ;
519
comp_op custkey_item |
520
# not IN ( custkey_list ) |
521
BETWEEN custkey_item AND custkey_item + custkey_range ;
524
_tinyint_unsigned | { $prng->uint16(1,150) } ;
527
custkey_item , custkey_item |
528
custkey_item , custkey_list ;
531
_digit | _tinyint_unsigned ;
536
comp_op nationkey_item |
537
# not IN ( nationkey_list ) |
538
BETWEEN nationkey_item AND nationkey_item + nationkey_range ;
541
_digit | { $prng->uint16(0,24) } ;
544
nationkey_item , nationkey_item |
545
nationkey_item , nationkey_list ;
548
_digit | _tinyint_unsigned ;
553
comp_op regionkey_item |
554
# not IN ( regionkey_list ) |
555
BETWEEN regionkey_item AND regionkey_item + regionkey_range ;
561
regionkey_item , regionkey_item |
562
regionkey_item , regionkey_list ;
570
IS NOT NULL | IS NOT NULL | IS NOT NULL |
571
comp_op _varchar(1) |
572
# comment_not LIKE CONCAT( comment_count , '%' ) |
573
BETWEEN _varchar(1) AND _varchar(1) ;
579
_varchar(1) | _varchar(1) | _varchar(1) | _varchar(1) | _varchar(2) ;
584
comp_op currency_item |
585
BETWEEN currency_item AND currency_item + currency_range ;
588
_digit | _tinyint_unsigned | _tinyint_unsigned | _tinyint_unsigned | _mediumint_unsigned ;
600
{ "'Supplier".chr(35).'00000000'.$prng->int(1,5)."'" };
608
'Manufacturer1' | 'Manufacturer2' | 'Manufacturer3' | 'Manufacturer4' | 'Manufacturer5' ;
611
{"'Brand".$prng->int(1,5).$prng->int(1,5)."'" } ;
614
'types1 types2 types3';
617
{ $prng->int(1,50) } ;
620
'container1 container2';
628
{ $prng->int(1,9999) } ;
636
{ "'Customer".chr(35).'00000000'.$prng->int(1,9)."'" } ;
653
{ "'Clerk".chr(35).'000000'.$prng->int(100,999)."'" } ;
664
{ $prng->int(1,50) } ;
670
0.0 | 0.01 | 0.02 | 0.03 | 0.04 | 0.05 | 0.06 | 0.07 | 0.08 | 0.09 | 0.10 ;
673
0.0 | 0.01 | 0.02 | 0.03 | 0.04 | 0.05 | 0.06 | 0.07 | 0.08 ;
682
'DELIVER IN PERSON' | 'COLLECT COD' | 'NONE' | 'TAKE BACK RETURN' ;
685
'REG AIR' | 'AIR' | 'RAIL' | 'SHIP' | 'TRUCK' | 'MAIL' | 'FOB' ;
704
{ "'".$prng->int(1,10).'-'.$prng->int(100,999).'-'.$prng->int(100,999).'-'.$prng->int(1000,9999)."'" } ;
707
{ $prng->int(-999,9999) } ;
714
SM | LG | MED | JUMBO | WRAP ;
717
CASE | BOX | BAG | JAR | PKG | PACK | CAN | DRUM ;
720
nouns | verbs | adjectives | adverbs | prepositions | auxiliaries ;
723
foxes | ideas | theodolites | pinto | beans |
724
instructions | dependencies | excuses | platelets |
725
asymptotes | courts | dolphins | multipliers |
726
sauternes | warthogs | frets | dinos |
727
attainments | somas | Tiresias | patterns |
728
forges | braids | hockey | players | frays |
729
warhorses | dugouts | notornis | epitaphs |
730
pearls | tithes | waters | orbits |
731
gifts | sheaves | depths | sentiments |
732
decoys | realms | pains | grouches |
736
sleep | wake | are | cajole |
737
haggle | nag | use | boost |
738
affix | detect | integrate | maintain |
739
nod | was | lose | sublate |
740
solve | thrash | promise | engage |
741
hinder | print | x-ray | breach |
742
eat | grow | impress | mold |
743
poach | serve | run | dazzle |
744
snooze | doze | unwind | kindle |
745
play | hang | believe | doubt ;
748
furious | sly | careful | blithe |
749
quick | fluffy | slow | quiet |
750
ruthless | thin | close | dogged |
751
daring | brave | stealthy | permanent |
752
enticing | idle | busy | regular |
753
final | ironic | even | bold |
757
sometimes | always | never | furiously |
758
slyly | carefully | blithely | quickly |
759
fluffily | slowly | quietly | ruthlessly |
760
thinly | closely | doggedly | daringly |
761
bravely | stealthily | permanently | enticingly |
762
idly | busily | regularly | finally |
763
ironically | evenly | boldly | silently ;
766
about | above | according to | across |
767
after | against | along | alongside of |
768
among | around | at | atop |
769
before | behind | beneath | beside |
770
besides | between | beyond | by |
771
despite | during | except | for |
772
from | in place of | inside | instead of |
773
into | near | of | on |
774
outside | over | past | since |
775
through | throughout | to | toward |
776
under | until | up | upon |
777
without | with | within ;
780
do | may | might | shall |
781
will | would | can | could |
782
should | ought to | must | will have to |
783
shall | have to | could have to | should have to | must have to |
787
almond | antique | aquamarine | azure | beige | bisque | black | blanched | blue | blush |
788
brown | burlywood | burnished | chartreuse | chiffon | chocolate | coral | cornflower |
789
cornsilk | cream | cyan | dark | deep | dim | dodger | drab | firebrick | floral | forest |
790
frosted | gainsboro | ghost | goldenrod | green | grey | honeydew | hot | indian | ivory |
791
khaki | lace | lavender | lawn | lemon | light | lime | linen | magenta | maroon | medium |
792
metallic | midnight | mint | misty | moccasin | navajo | navy | olive | orange | orchid | pale |
793
papaya | peach | peru | pink | plum | powder | puff | purple | red | rose | rosy | royal |
794
saddle | salmon | sandy | seashell | sienna | sky | slate | smoke | snow | spring | steel | tan |
795
thistle | tomato | turquoise | violet | wheat | white | yellow ;
798
'AUTOMOBILE' | 'BUILDING' | 'FURNITURE' | 'MACHINERY' | 'HOUSEHOLD' ;
801
'1-URGENT' | '2-HIGH' | '3-MEDIUM' | '4-NOT SPECIFIED' | '5-LOW' ;
804
'ALGERIA' | 'ARGENTINA' | 'BRAZIL' |
805
'CANADA' | 'EGYPT' | 'ETHIOPIA' |
806
'FRANCE' | 'GERMANY' | 'INDIA' |
807
'INDONESIA' | 'IRAN' | 'IRAQ' |
808
'JAPAN' | 'JORDAN' | 'KENYA' |
809
'MOROCCO' | 'MOZAMBIQUE' | 'PERU' |
810
'CHINA' | 'ROMANIA' | 'SAUDI ARABIA' |
811
'VIETNAM' | 'RUSSIA' | 'UNITED KINGDOM' |
815
'AFRICA' | 'AMERICA' | 'ASIA' | 'EUROPE' | 'MIDDLE EAST' ;
818
STANDARD | SMALL | MEDIUM | LARGE | ECONOMY | PROMO ;
821
ANODIZED | BURNISHED | PLATED | POLISHED | BRUSHED ;
824
TIN | NICKEL | BRASS | STEEL | COPPER ;