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
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
|
query:
SELECT select_option grandparent_select_items
FROM grandparent_from
grandparent_where
grandparent_group_having_order_limit;
grandparent_select_items:
grandparent_select_item;
grandparent_select_item:
GRANDPARENT1 . field_name AS G1;
grandparent_from:
table_name AS GRANDPARENT1 |
table_name AS GRANDPARENT1 LEFT JOIN table_name AS GRANDPARENT2 USING ( field_name ) |
table_name AS GRANDPARENT1 LEFT JOIN table_name AS GRANDPARENT2 ON ( grandparent_join_condition );
grandparent_join_condition:
GRANDPARENT2 . int_field_name arithmetic_operator GRANDPARENT1 . int_field_name |
GRANDPARENT2 . date_field_name arithmetic_operator GRANDPARENT1 . date_field_name |
GRANDPARENT2 . char_field_name arithmetic_operator GRANDPARENT1 . char_field_name ;
grandparent_where:
WHERE grandparent_subquery_expr AND grandparent_condition ;
grandparent_subquery_expr:
GRANDPARENT1 . int_field_name IN ( SELECT select_option PARENT1 . int_field_name AS P1 parent_select_body ) |
GRANDPARENT1 . char_field_name IN ( SELECT select_option PARENT1 . char_field_name AS P1 parent_select_body ) |
( GRANDPARENT1 . int_field_name , GRANDPARENT1 . int_field_name ) IN ( SELECT select_option PARENT1 . int_field_name AS P1 , PARENT1 . int_field_name AS P2 parent_select_body ) |
( GRANDPARENT1 . char_field_name , GRANDPARENT1 . char_field_name ) IN ( SELECT select_option PARENT1 . char_field_name AS P1 , PARENT1 . char_field_name AS P2 parent_select_body ) ;
grandparent_group_having_order_limit:
grandparent_group_by grandparent_having grandparent_order_by grandparent_limit |
grandparent_group_by grandparent_having grandparent_limit |
grandparent_having grandparent_order_by grandparent_limit |
grandparent_having ;
grandparent_group_by:
GROUP BY GRANDPARENT1 . field_name ;
grandparent_order_by:
ORDER BY GRANDPARENT1 . field_name ;
grandparent_having:
| HAVING G1 arithmetic_operator value;
grandparent_limit:
| LIMIT digit ;
parent_select_body:
FROM parent_from
parent_where
parent_order_by;
parent_from:
table_name AS PARENT1 |
table_name AS PARENT1 LEFT JOIN table_name AS PARENT2 USING ( field_name ) |
table_name AS PARENT1 LEFT JOIN table_name AS PARENT2 ON ( parent_join_condition ) ;
parent_join_condition:
PARENT1 . int_field_name arithmetic_operator PARENT2 . int_field_name |
PARENT1 . char_field_name arithmetic_operator PARENT2 . char_field_name ;
parent_where:
| WHERE parent_subquery_expr AND parent_condition
| WHERE parent_condition ;
parent_order_by:
| ORDER BY PARENT1 . field_name ;
parent_subquery_expr:
PARENT1 . int_field_name IN ( SELECT select_option CHILD1 . int_field_name AS C1 child_select_body ) |
GRANDPARENT1 . int_field_name IN ( SELECT select_option CHILD1 . int_field_name AS C1 child_select_body ) |
PARENT1 . char_field_name IN ( SELECT select_option CHILD1 . char_field_name AS C1 child_select_body ) |
GRANDPARENT1 . char_field_name IN ( SELECT select_option CHILD1 . char_field_name AS C1 child_select_body ) |
( PARENT1 . int_field_name , PARENT1 . int_field_name ) IN ( SELECT select_option CHILD1 . int_field_name AS C1 , CHILD1 . int_field_name AS C2 child_select_body ) |
( PARENT1 . int_field_name , GRANDPARENT1 . int_field_name ) IN ( SELECT select_option CHILD1 . int_field_name AS C1 , CHILD1 . int_field_name AS C2 child_select_body ) |
( PARENT1 . char_field_name , PARENT1 . char_field_name ) IN ( SELECT select_option CHILD1 . char_field_name AS C1 , CHILD1 . char_field_name AS C2 child_select_body ) ;
( PARENT1 . char_field_name , GRANDPARENT1 . char_field_name ) IN ( SELECT select_option CHILD1 . char_field_name AS C1 , CHILD1 . char_field_name AS C2 child_select_body ) ;
( PARENT1 . char_field_name , PARENT1 . char_field_name ) IN ( SELECT select_option CHILD1 . char_field_name AS C1 , CHILD1 . char_field_name AS C2 child_select_body ) ;
( PARENT1 . int_field_name , PARENT1 . int_field_name ) IN ( SELECT select_option CHILD1 . int_field_name AS C1 , CHILD1 . int_field_name AS C2 child_select_body ) ;
child_select_body:
FROM child_from
child_where
child_order_by;
child_from:
table_name AS CHILD1 |
table_name AS CHILD1 LEFT JOIN table_name AS CHILD2 USING ( field_name ) |
table_name AS CHILD1 LEFT JOIN table_name AS CHILD2 ON ( child_join_condition ) ;
child_join_condition:
CHILD1 . int_field_name arithmetic_operator CHILD2 . int_field_name |
CHILD1 . char_field_name arithmetic_operator CHILD2 . char_field_name ;
child_where:
| WHERE child_condition ;
child_order_by:
| ORDER BY CHILD1 . field_name ;
child_condition:
( GRANDPARENT1 . expression ) |
( PARENT1 . expression ) |
( CHILD1 . expression ) |
( child_condition_bottom logical_operator child_condition ) |
( child_condition_bottom logical_operator parent_condition ) |
( child_condition_bottom logical_operator grandparent_condition ) ;
child_condition_bottom:
( CHILD1 . expression ) |
( CHILD1 . int_field_name arithmetic_operator CHILD1 . int_field_name ) |
( CHILD1 . date_field_name arithmetic_operator CHILD1 . date_field_name ) |
( CHILD1 . char_field_name arithmetic_operator CHILD1 . char_field_name ) |
( CHILD1 . int_field_name arithmetic_operator PARENT1 . int_field_name ) |
( CHILD1 . date_field_name arithmetic_operator PARENT1 . date_field_name ) |
( CHILD1 . char_field_name arithmetic_operator PARENT1 . char_field_name ) |
( CHILD1 . int_field_name arithmetic_operator GRANDPARENT1 . int_field_name ) |
( CHILD1 . date_field_name arithmetic_operator GRANDPARENT1 . date_field_name ) |
( CHILD1 . char_field_name arithmetic_operator GRANDPARENT1 . char_field_name ) ;
grandparent_condition:
grandparent_condition_bottom |
( grandparent_condition logical_operator grandparent_condition_bottom ) ;
grandparent_condition_bottom:
GRANDPARENT1 . expression ;
expression:
field_name null_operator |
int_field_name int_expression |
date_field_name date_expression |
char_field_name char_expression ;
int_expression:
arithmetic_operator digit ;
date_expression:
arithmetic_operator date | BETWEEN date AND date;
char_expression:
arithmetic_operator _varchar(1);
parent_condition:
( GRANDPARENT1 . expression ) |
( PARENT1 . expression ) |
( parent_condition_bottom logical_operator parent_condition ) |
( parent_condition_bottom logical_operator grandparent_condition ) ;
parent_condition_bottom:
( PARENT1 . expression ) |
( PARENT1 . int_field_name arithmetic_operator PARENT1 . int_field_name ) |
( PARENT1 . date_field_name arithmetic_operator PARENT1 . date_field_name ) |
( PARENT1 . char_field_name arithmetic_operator PARENT1 . char_field_name ) |
( PARENT1 . int_field_name arithmetic_operator GRANDPARENT1 . int_field_name ) |
( PARENT1 . date_field_name arithmetic_operator GRANDPARENT1 . date_field_name ) |
( PARENT1 . char_field_name arithmetic_operator GRANDPARENT1 . char_field_name ) ;
null_operator: IS NULL | IS NOT NULL | IS UNKNOWN ;
logical_operator:
AND | OR | OR NOT;
arithmetic_operator: = | > | < | <> | >= | <= ;
field_name:
int_field_name | char_field_name | date_field_name;
int_field_name:
`pk` | `int_key` | `int_nokey` ;
date_field_name:
`date_key` | `date_nokey` | `datetime_key` | `datetime_nokey` | `time_key` | `time_nokey` ;
char_field_name:
`varchar_key` | `varchar_nokey` ;
table_name:
B | C | BB | CC;
value: _digit | _date | _time | _datetime | _varchar(1) | NULL ;
select_option:
| DISTINCT ;
|