-
-
Notifications
You must be signed in to change notification settings - Fork 1.3k
/
CCJSqlParserUtilTest.java
332 lines (291 loc) · 15 KB
/
CCJSqlParserUtilTest.java
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
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
/*-
* #%L
* JSQLParser library
* %%
* Copyright (C) 2004 - 2019 JSQLParser
* %%
* Dual licensed under GNU LGPL 2.1 or Apache License 2.0
* #L%
*/
package net.sf.jsqlparser.parser;
import java.io.ByteArrayInputStream;
import java.io.StringReader;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.TimeoutException;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.Parenthesis;
import net.sf.jsqlparser.expression.operators.arithmetic.Addition;
import net.sf.jsqlparser.expression.operators.arithmetic.Multiplication;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.Statements;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.junit.jupiter.api.Assertions.assertTrue;
import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.function.Executable;
public class CCJSqlParserUtilTest {
@Test
public void testParseExpression() throws Exception {
Expression result = CCJSqlParserUtil.parseExpression("a+b");
assertEquals("a + b", result.toString());
assertTrue(result instanceof Addition);
Addition add = (Addition) result;
assertTrue(add.getLeftExpression() instanceof Column);
assertTrue(add.getRightExpression() instanceof Column);
}
@Test
public void testParseExpression2() throws Exception {
Expression result = CCJSqlParserUtil.parseExpression("2*(a+6.0)");
assertEquals("2 * (a + 6.0)", result.toString());
assertTrue(result instanceof Multiplication);
Multiplication mult = (Multiplication) result;
assertTrue(mult.getLeftExpression() instanceof LongValue);
assertTrue(mult.getRightExpression() instanceof Parenthesis);
}
@Test
public void testParseExpressionNonPartial() throws Exception {
assertThrows(JSQLParserException.class, () -> CCJSqlParserUtil.parseExpression("a+", false));
}
@Test
public void testParseExpressionFromStringFail() throws Exception {
assertThrows(JSQLParserException.class, () -> CCJSqlParserUtil.parse("whatever$"));
}
@Test
public void testParseExpressionFromRaderFail() throws Exception {
assertThrows(JSQLParserException.class, () -> CCJSqlParserUtil.parse(new StringReader("whatever$")));
}
@Test
public void testParseExpressionNonPartial2() throws Exception {
Expression result = CCJSqlParserUtil.parseExpression("a+", true);
assertEquals("a", result.toString());
}
@Test
public void testParseCondExpression() throws Exception {
Expression result = CCJSqlParserUtil.parseCondExpression("a+b>5 and c<3");
assertEquals("a + b > 5 AND c < 3", result.toString());
}
@Test
public void testParseCondExpressionFail() throws Exception {
assertThrows(JSQLParserException.class, () -> CCJSqlParserUtil.parseCondExpression(";"));
}
@Test
public void testParseFromStreamFail() throws Exception {
assertThrows(JSQLParserException.class,
() -> CCJSqlParserUtil.parse(new ByteArrayInputStream("BLA".getBytes(StandardCharsets.UTF_8))));
}
@Test
public void testParseFromStreamWithEncodingFail() throws Exception {
assertThrows(JSQLParserException.class,
() -> CCJSqlParserUtil.parse(new ByteArrayInputStream("BLA".getBytes(StandardCharsets.UTF_8)), StandardCharsets.UTF_8.name()));
}
@Test
public void testParseCondExpressionNonPartial() throws Exception {
Expression result = CCJSqlParserUtil.parseCondExpression("x=92 and y=29", false);
assertEquals("x = 92 AND y = 29", result.toString());
}
@Test
public void testParseCondExpressionNonPartial2() throws Exception {
assertThrows(JSQLParserException.class, () -> CCJSqlParserUtil.parseCondExpression("x=92 lasd y=29", false));
}
@Test
public void testParseCondExpressionPartial2() throws Exception {
Expression result = CCJSqlParserUtil.parseCondExpression("x=92 lasd y=29", true);
assertEquals("x = 92", result.toString());
}
@Test
public void testParseCondExpressionIssue471() throws Exception {
Expression result = CCJSqlParserUtil.parseCondExpression("(SSN,SSM) IN ('11111111111111', '22222222222222')");
assertEquals("(SSN, SSM) IN ('11111111111111', '22222222222222')", result.toString());
}
@Test
public void testParseStatementsIssue691() throws Exception {
Statements result = CCJSqlParserUtil.parseStatements(
"select * from dual;\n"
+ "\n"
+ "select\n"
+ "*\n"
+ "from\n"
+ "dual;\n"
+ "\n"
+ "select *\n"
+ "from dual;");
assertEquals("SELECT * FROM dual;\n"
+ "SELECT * FROM dual;\n"
+ "SELECT * FROM dual;\n", result.toString());
}
@Test
public void testStreamStatementsIssue777() throws Exception {
final List<Statement> list = new ArrayList<>();
CCJSqlParserUtil.streamStatements(new StatementListener() {
@Override
public void accept(Statement statement) {
list.add(statement);
}
}, new ByteArrayInputStream(("select * from dual;\n"
+ "select\n"
+ "*\n"
+ "from\n"
+ "dual;\n"
+ "\n"
+ "-- some comment\n"
+ "select *\n"
+ "from dual;").getBytes(StandardCharsets.UTF_8)), "UTF-8");
assertEquals(list.size(), 3);
}
@Test
@Disabled
public void testParseStatementsFail() throws Exception {
// This will not fail, but always return the Unsupported Statements
// Since we can't LOOKAHEAD in the Statements() production
assertThrows(JSQLParserException.class, () -> CCJSqlParserUtil.parseStatements("select * from dual;WHATEVER!!"));
}
@Test
public void testParseASTFail() throws Exception {
assertThrows(JSQLParserException.class, () -> CCJSqlParserUtil.parseAST("select * from dual;WHATEVER!!"));
}
@Test
public void testParseStatementsIssue691_2() throws Exception {
Statements result = CCJSqlParserUtil.parseStatements(
"select * from dual;\n"
+ "---test");
assertEquals("SELECT * FROM dual;\n", result.toString());
}
@Test
public void testParseStatementIssue742() throws Exception {
Statements result = CCJSqlParserUtil.parseStatements("CREATE TABLE `table_name` (\n"
+ " `id` bigint(20) NOT NULL AUTO_INCREMENT,\n"
+ " `another_column_id` bigint(20) NOT NULL COMMENT 'column id as sent by SYSTEM',\n"
+ " PRIMARY KEY (`id`),\n"
+ " UNIQUE KEY `uk_another_column_id` (`another_column_id`)\n"
+ ")");
assertEquals("CREATE TABLE `table_name` (`id` bigint (20) NOT NULL AUTO_INCREMENT, `another_column_id` "
+ "bigint (20) NOT NULL COMMENT 'column id as sent by SYSTEM', PRIMARY KEY (`id`), UNIQUE KEY `uk_another_column_id` "
+ "(`another_column_id`));\n", result.toString());
}
@Test
public void testParseExpressionIssue982() throws Exception {
Expression result = CCJSqlParserUtil.parseExpression("tab.col");
assertEquals("tab.col", result.toString());
}
@Test
public void testParseExpressionWithBracketsIssue1159() throws Exception {
Expression result = CCJSqlParserUtil.parseExpression("[travel_data].[travel_id]", false,
parser -> parser.withSquareBracketQuotation(true));
assertEquals("[travel_data].[travel_id]", result.toString());
}
@Test
public void testParseExpressionWithBracketsIssue1159_2() throws Exception {
Expression result = CCJSqlParserUtil.parseCondExpression("[travel_data].[travel_id]", false,
parser -> parser.withSquareBracketQuotation(true));
assertEquals("[travel_data].[travel_id]", result.toString());
}
@Test
public void testNestingDepth() throws Exception {
assertEquals(2,
CCJSqlParserUtil.getNestingDepth("SELECT concat(concat('A','B'),'B') FROM mytbl"));
assertEquals(20, CCJSqlParserUtil.getNestingDepth(
"concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat('A','B'),'B'),'B'),'B'),'B'),'B'),'B'),'B'),'B'),'B'),'B'),'B'),'B'),'B'),'B'),'B'),'B'),'B'),'B'),'B') FROM mytbl"));
assertEquals(4, CCJSqlParserUtil.getNestingDepth(""
+ "-- MERGE 1\n"
+ "MERGE INTO cfe.impairment imp\n" + " USING ( WITH x AS (\n"
+ " SELECT a.id_instrument\n"
+ " , a.id_currency\n"
+ " , a.id_instrument_type\n"
+ " , b.id_portfolio\n"
+ " , c.attribute_value product_code\n"
+ " , t.valid_date\n" + " , t.ccf\n"
+ " FROM cfe.instrument a\n"
+ " INNER JOIN cfe.impairment b\n"
+ " ON a.id_instrument = b.id_instrument\n"
+ " LEFT JOIN cfe.instrument_attribute c\n"
+ " ON a.id_instrument = c.id_instrument\n"
+ " AND c.id_attribute = 'product'\n"
+ " INNER JOIN cfe.ext_ccf t\n"
+ " ON ( a.id_currency LIKE t.id_currency )\n"
+ " AND ( a.id_instrument_type LIKE t.id_instrument_type )\n"
+ " AND ( b.id_portfolio LIKE t.id_portfolio\n"
+ " OR ( b.id_portfolio IS NULL\n"
+ " AND t.id_portfolio = '%' ) )\n"
+ " AND ( c.attribute_value LIKE t.product_code\n"
+ " OR ( c.attribute_value IS NULL\n"
+ " AND t.product_code = '%' ) ) )\n"
+ "SELECT /*+ PARALLEL */ *\n" + " FROM x x1\n"
+ " WHERE x1.valid_date = ( SELECT max\n"
+ " FROM x\n"
+ " WHERE id_instrument = x1.id_instrument ) ) s\n"
+ " ON ( imp.id_instrument = s.id_instrument )\n" + "WHEN MATCHED THEN\n"
+ " UPDATE SET imp.ccf = s.ccf\n" + ";"));
}
@Test
public void testParseStatementIssue1250() throws Exception {
Statement result = CCJSqlParserUtil.parse("Select test.* from (Select * from sch.PERSON_TABLE // root test\n) as test");
assertEquals("SELECT test.* FROM (SELECT * FROM sch.PERSON_TABLE) AS test", result.toString());
}
@Test
public void testCondExpressionIssue1482() throws JSQLParserException {
Expression expr = CCJSqlParserUtil.parseCondExpression("test_table_enum.f1_enum IN ('TEST2'::test.test_enum)", false);
assertEquals("test_table_enum.f1_enum IN ('TEST2'::test.test_enum)", expr.toString());
}
@Test
public void testCondExpressionIssue1482_2() throws JSQLParserException {
Expression expr = CCJSqlParserUtil.parseCondExpression("test_table_enum.f1_enum IN ('TEST2'::test.\"test_enum\")", false);
assertEquals("test_table_enum.f1_enum IN ('TEST2'::test.\"test_enum\")", expr.toString());
}
@Test
public void testTimeOutIssue1582() throws InterruptedException {
// This statement is INVALID on purpose
// There are crafted INTO keywords in order to make it fail but only after a long time (40 seconds plus)
String sqlStr = "" +
"select\n" +
" t0.operatienr\n" +
" , case\n" +
" when\n" +
" case when (t0.vc_begintijd_operatie is null or lpad((extract('hours' into t0.vc_begintijd_operatie::timestamp))::text,2,'0') ||':'|| lpad(extract('minutes' from t0.vc_begintijd_operatie::timestamp)::text,2,'0') = '00:00') then null\n" +
" else (greatest(((extract('hours' into (t0.vc_eindtijd_operatie::timestamp-t0.vc_begintijd_operatie::timestamp))*60 + extract('minutes' from (t0.vc_eindtijd_operatie::timestamp-t0.vc_begintijd_operatie::timestamp)))/60)::numeric(12,2),0))*60\n" +
" end = 0 then null\n" +
" else '25. Meer dan 4 uur'\n" +
" end \n" +
" as snijtijd_interval";
// With DEFAULT TIMEOUT 6 Seconds, we expect the statement to timeout normally
// A TimeoutException wrapped into a Parser Exception should be thrown
assertThrows(TimeoutException.class, new Executable() {
@Override
public void execute() throws Throwable {
try {
CCJSqlParserUtil.parse(sqlStr);
} catch (JSQLParserException ex) {
Throwable cause = ((JSQLParserException) ex).getCause();
if (cause!=null) {
throw cause;
} else {
throw ex;
}
}
}
});
// With custom TIMEOUT 60 Seconds, we expect the statement to not timeout but to fail instead
// No TimeoutException wrapped into a Parser Exception must be thrown
// Instead we expect a Parser Exception only
assertThrows(JSQLParserException.class, new Executable() {
@Override
public void execute() throws Throwable {
try {
CCJSqlParserUtil.parse(sqlStr, parser -> parser.withTimeOut(60000));
} catch (JSQLParserException ex) {
Throwable cause = ((JSQLParserException) ex).getCause();
if (cause instanceof TimeoutException) {
throw cause;
} else {
throw ex;
}
}
}
});
}
}