2
# At the top of this grammar, we define the data types, the literals and the functions that will
3
# be used in the test. Some combinations of functions, data types and literals may produce
4
# false positives when used in a comparison test
8
_digit | regular_field ;
9
# _letter | _varchar(4) ;
13
#| CHAR(255) BINARY | DATETIME ;
17
( comparison_operator ) | ( logical_operator ) | ( logical_operator ) |
18
( control_flow_function ) |
19
# ( string_function ) |
20
# ( string_comparison_function ) |
21
( arithmetic_function ) |
22
# ( date_and_time_function ) |
23
( mathematical_function ) ;
26
# We use multiple CREATE TABLE so that if a given table definition is invalid
27
# (such as a constant expression in a virtual column), subsequent CREATEs will
28
# create a valid table
33
drop_table ; create_table ; create_table ; create_table ;
36
drop_table ; create_table ; create_table ; create_table | create_table |
37
select_dml | select_dml | select_dml | select_dml | select_dml |
38
select_dml | select_dml | select_dml | select_dml | select_dml |
39
select_dml | select_dml | select_dml | select_dml | select_dml |
40
select_dml | select_dml | select_dml | select_dml | select_dml |
41
select_dml | select_dml | select_dml | select_dml | select_dml |
42
select_dml | select_dml | select_dml | select_dml | select_dml ;
45
select | dml | dml | dml | dml | dml ;
48
DROP TABLE IF EXISTS X ;
51
# Using the /*executor1 */ and /*executor2 */ syntax, we make sure that, in a comparison test PERSISTENT virtual columns
52
# are created on the first server while non-persistent ones are created on the second server
53
# We use the same syntax for KEY and FORCE|IGNORE KEY because only PERSISTENT virtual columns can have keys
57
CREATE TABLE IF NOT EXISTS X (
58
f1 field_type null default,
59
f2 field_type null default,
60
f3 field_type null default,
61
f4 field_type null default,
62
v1 field_type AS ( virtual_expr ) /*executor1 PERSISTENT */ /*executor2 VIRTUAL */ ,
63
v2 field_type AS ( virtual_expr ) /*executor1 PERSISTENT */ /*executor2 VIRTUAL */ ,
64
v3 field_type AS ( virtual_expr ) /*executor1 PERSISTENT */ /*executor2 VIRTUAL */ ,
65
v4 field_type AS ( virtual_expr ) /*executor1 PERSISTENT */ /*executor2 VIRTUAL */
66
/*executor1 , KEY (v3), KEY (v4) */
80
SELECT any_field AS a1 , any_field AS a2 FROM virtual_table_name force_key WHERE where_condition order_by_limit;
83
SELECT v3 , aggregate_item FROM virtual_table_name force_key WHERE where_condition GROUP BY v3 order_by |
84
SELECT v4 , aggregate_item FROM virtual_table_name force_key WHERE where_condition GROUP BY v4 order_by;
87
| /*executor1 force_ignore KEY (v3, v4) */ ;
93
select_list, any_field | any_field ;
96
SUM(any_field) | COUNT(*) | COUNT(any_field) | MIN(any_field) | MAX(any_field) ;
100
ORDER BY any_field_list |
101
ORDER BY any_field_list , complete_field_list LIMIT _digit;
105
ORDER BY any_field_list ;
108
any_field_list , any_field | any_field ;
111
insert_replace | insert_replace |
115
i_s INTO virtual_table_name ( regular_field , regular_field ) VALUES ( constant , constant ) |
116
i_s INTO virtual_table_name ( f1 , f2 , f3 , f4 ) VALUES ( constant , constant , constant , constant ) |
117
i_s INTO virtual_table_name ( f1 , f2 , f3 , f4 ) SELECT any_field , any_field , any_field , any_field FROM virtual_table_name WHERE where_condition ORDER BY complete_field_list LIMIT _digit ;
124
UPDATE virtual_table_name SET regular_field = constant WHERE where_condition |
125
UPDATE virtual_table_name SET regular_field = virtual_field WHERE where_condition ;
128
DELETE FROM virtual_table_name WHERE where_condition ORDER BY complete_field_list LIMIT _digit ;
131
f1,f2,f3,f4,v1,v2,v3,v4;
134
where_condition AND any_field op constant |
135
where_condition OR any_field op constant |
136
any_field op constant | any_field op constant |
137
any_field op constant | any_field op constant |
138
any_field op constant | any_field op constant |
139
any_field op any_field | any_field op any_field |
140
any_field not BETWEEN _digit AND _digit ;
146
< | > | = | <> | <=> | != ;
152
regular_field | virtual_field ;
168
regular_field | regular_field | regular_field | nested_expr | nested_expr;
173
expr <> expr | expr != expr |
178
expr IS not boolean_value |
180
expr not BETWEEN ( expr ) AND ( expr ) |
181
COALESCE( expr_list ) |
182
GREATEST( expr , expr_list ) |
183
expr not IN ( expr_list ) |
185
INTERVAL( expr , expr_list ) |
186
LEAST( expr, expr_list ) ;
190
( expr ) AND ( expr ) |
191
( expr ) OR ( expr ) ;
192
# ( expr ) XOR ( expr ) ; # MySQL bug #55365
194
string_comparison_function:
195
expr not LIKE pattern |
199
expr not REGEXP pattern ;
204
control_flow_function:
205
CASE expr WHEN expr THEN expr WHEN expr THEN expr ELSE expr END |
206
IF( expr , expr , expr ) |
207
IFNULL( expr , expr ) |
208
NULLIF( expr , expr ) ;
215
CHAR( expr USING _charset ) |
216
CHAR_LENGTH( expr ) |
218
CONCAT( expr_list ) |
219
CONCAT_WS( expr , expr_list ) |
220
ELT( expr , expr_list ) |
221
EXPORT_SET( expr , expr , expr , expr , expr ) |
222
FIELD( expr , expr_list ) |
223
FIND_IN_SET( expr , expr_list ) |
224
FORMAT( expr , expr ) |
226
INSERT( expr , expr , expr , expr ) |
227
INSTR( expr , expr ) |
229
LEFT( expr , expr ) |
232
LOCATE( expr , expr ) |
233
LOCATE( expr , expr , expr) |
235
LPAD( expr , expr , expr ) |
237
MAKE_SET( expr , expr_list ) |
238
MID( expr , pos , len) |
240
OCTET_LENGTH( expr ) |
242
POSITION( expr IN expr ) |
244
REPEAT( expr , expr ) |
245
REPLACE( expr , expr , expr ) |
247
RIGHT( expr , expr ) |
248
RPAD( expr , expr , expr ) |
251
( expr SOUNDS LIKE expr ) |
253
SUBSTR( expr , expr ) |
254
SUBSTR( expr , expr , expr ) |
255
SUBSTRING_INDEX( expr , expr , expr ) |
256
TRIM( both_leading_trailing expr FROM expr ) |
270
mathematical_function:
275
ATAN( expr , expr ) |
278
CONV( expr , expr , expr ) |
285
FORMAT( expr , expr ) |
295
POWER( expr , expr ) |
299
ROUND( expr , expr ) |
304
# TRUNCATE( expr , expr ) ; # MySQL bug #55365
306
date_and_time_function:
307
ADDDATE( expr , INTERVAL expr unit) |
308
ADDDATE( expr , expr ) |
309
ADDTIME( expr , expr ) |
310
CONVERT_TZ( expr , tz , tz ) |
313
CURRENT_TIMESTAMP() |
315
DATEDIFF( expr , expr ) |
316
DATE_ADD( expr INTERVAL expr unit ) |
317
DATE_FORMAT( expr , expr ) |
318
DATE_SUB( expr INTERVAL expr unit ) |
324
EXTRACT( unit FROM expr ) |
326
FROM_UNIXTIME( expr ) |
327
FROM_UNIXTIME( expr , expr ) |
328
GET_FORMAT( date_time_datetime, date_format ) |
332
MAKEDATE( expr , expr ) |
333
MAKETIME( expr , expr , expr ) |
334
MICROSECOND( expr ) |
339
PERIOD_ADD( expr , expr ) |
340
PERIOD_DIFF( expr , expr ) |
343
SEC_TO_TIME( expr ) |
344
STR_TO_DATE( expr , expr ) |
345
SUBDATE( expr INTERVAL expr unit ) |
346
SUBDATE( expr , expr ) |
347
SUBTIME( expr , expr ) |
350
TIMEDIFF( expr , expr ) |
352
TIMESTAMP( expr , expr ) |
353
TIMESTAMPADD( unit , expr , expr ) |
354
TIMESTAMPDIFF( unit , expr , expr ) |
355
TIME_FORMAT( expr , expr ) |
356
TIME_TO_SEC( expr ) |
359
UNIX_TIMESTAMP( expr ) |
363
WEEK( expr , expr ) |
368
YEARWEEK( expr , expr ) ;
371
DATE | TIME | DATETIME ;
374
'EUR' | 'USA' | 'JIS' | 'ISO' | 'INTERNAL' ;
377
MICROSECOND | SECOND | MINUTE | HOUR | DAY | WEEK | MONTH | QUARTER | YEAR |
379
MINUTE_MICROSECOND | MINUTE_SECOND |
380
HOUR_MICRSECOND | HOUR_SECOND | HOUR_MINUTE |
381
DAY_MICROSECOND | DAY_SECOND | DAY_MINUTE | DAY_HOUR |
387
both_leading_trailing:
388
| BOTH | LEADING | TRAILING ;
391
TRUE | FALSE | UNKNOWN ;
394
expr_list , expr | expr , expr | expr | expr | expr ;