Skip to content

Commit e19dc0e

Browse files
manticore-projectsmanticore-projects
committed
feat: functions blocks, parenthesed JSON Expressions
- fixes #1792, the very complex example - fixes #1477
1 parent 64b0331 commit e19dc0e

6 files changed

Lines changed: 160 additions & 12 deletions

File tree

‎src/main/java/net/sf/jsqlparser/parser/CCJSqlParserUtil.java‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -104,6 +104,7 @@ public static Statement parse(String sql, ExecutorService executorService,
104104
LOGGER.info("Trying SIMPLE parsing " + (allowComplex ? "first" : "only"));
105105
statement = parseStatement(parser.withAllowComplexParsing(false), executorService);
106106
} catch (JSQLParserException ex) {
107+
LOGGER.info("Nesting Depth" + getNestingDepth(sql));
107108
if (allowComplex && getNestingDepth(sql) <= ALLOWED_NESTING_DEPTH) {
108109
LOGGER.info("Trying COMPLEX parsing when SIMPLE parsing failed");
109110
// beware: the parser must not be reused, but needs to be re-initiated

‎src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt‎

Lines changed: 49 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -554,7 +554,7 @@ TOKEN:
554554
input_stream.backup(image.length() - matchedToken.image.length() );
555555
}
556556
}
557-
| < S_QUOTED_IDENTIFIER: "\"" (~["\n","\r","\""])* "\"" | "$$" (~["\n","\r","\""])* "$$" | ("`" (~["\n","\r","`"])+ "`") | ( "[" (~["\n","\r","]"])* "]" ) >
557+
| < S_QUOTED_IDENTIFIER: "\"" (~["\n","\r","\""])* "\"" | "$$" (~["$"])* "$$" | ("`" (~["\n","\r","`"])+ "`") | ( "[" (~["\n","\r","]"])* "]" ) >
558558
{
559559
if ( !configuration.getAsBoolean(Feature.allowSquareBracketQuotation) && matchedToken.image.charAt(0) == '[' ) {
560560
matchedToken.image = "[";
@@ -4048,6 +4048,17 @@ ArrayConstructor ArrayConstructor(boolean arrayKeyword) : {
40484048
{ return array; }
40494049
}
40504050

4051+
Expression ParenthesedExpression():
4052+
{
4053+
Expression expression;
4054+
}
4055+
{
4056+
"(" expression = PrimaryExpression() ")"
4057+
{
4058+
return new Parenthesis(expression);
4059+
}
4060+
}
4061+
40514062
JsonExpression JsonExpression() : {
40524063
JsonExpression result = new JsonExpression();
40534064
Expression expr;
@@ -4056,6 +4067,7 @@ JsonExpression JsonExpression() : {
40564067
CastExpression castExpr = null;
40574068
}
40584069
{
4070+
{ System.out.println("Complex:" + getAsBoolean(Feature.allowComplexParsing));}
40594071
(
40604072
LOOKAHEAD(3, {!interrupted}) expr=CaseWhenExpression()
40614073
|
@@ -4071,13 +4083,16 @@ JsonExpression JsonExpression() : {
40714083
|
40724084
LOOKAHEAD(FullTextSearch(), {getAsBoolean(Feature.allowComplexParsing) && !interrupted}) expr = FullTextSearch()
40734085
|
4074-
LOOKAHEAD( 3 , {getAsBoolean(Feature.allowComplexParsing) && !interrupted} ) expr=Function()
4086+
LOOKAHEAD( Function() , {getAsBoolean(Feature.allowComplexParsing) && !interrupted} ) expr=Function()
40754087
|
40764088
LOOKAHEAD( 2, {!interrupted} ) expr=Column()
40774089
|
40784090
token=<S_CHAR_LITERAL> { expr = new StringValue(token.image); }
40794091
|
4080-
LOOKAHEAD( {!interrupted} ) "(" expr=ParenthesedSelect() ")"
4092+
LOOKAHEAD(ParenthesedExpression(), {getAsBoolean(Feature.allowComplexParsing)} ) expr = ParenthesedExpression()
4093+
|
4094+
LOOKAHEAD( 3, {getAsBoolean(Feature.allowComplexParsing) && !interrupted}) expr=ParenthesedSelect()
4095+
40814096
)
40824097

40834098
(
@@ -6607,7 +6622,7 @@ CreateFunctionalStatement CreateFunctionStatement(boolean isUsingOrReplace):
66076622
|
66086623
<K_PROCEDURE> { statementType = "PROCEDURE"; }
66096624
)
6610-
tokens=captureRest()
6625+
tokens=captureFunctionBody()
66116626
{
66126627
if(statementType.equals("FUNCTION")) {
66136628
type = new CreateFunction(isUsingOrReplace, tokens);
@@ -6683,6 +6698,36 @@ List<String> captureRest() {
66836698
return tokens;
66846699
}
66856700

6701+
/**
6702+
* Reads the tokens of a function or procedure body.
6703+
* A function body can end in 2 ways:
6704+
* 1) BEGIN...END;
6705+
* 2) Postgres: $$...$$...;
6706+
*/
6707+
6708+
JAVACODE
6709+
List<String> captureFunctionBody() {
6710+
List<String> tokens = new LinkedList<String>();
6711+
Token tok;
6712+
boolean foundEnd = false;
6713+
while(true) {
6714+
tok = getToken(1);
6715+
int l = tokens.size();
6716+
if( tok.kind == EOF || ( foundEnd && tok.kind == ST_SEMICOLON) ) {
6717+
break;
6718+
} else if ( l>0 && ( tok.image.equals(".") || tokens.get(l-1).endsWith(".")) ) {
6719+
tokens.set(l-1, tokens.get(l-1) + tok.image);
6720+
} else {
6721+
tokens.add(tok.image);
6722+
}
6723+
foundEnd |= (tok.kind == K_END)
6724+
|| ( tok.image.trim().startsWith("$$") && tok.image.trim().endsWith("$$")) ;
6725+
6726+
tok = getNextToken();
6727+
}
6728+
return tokens;
6729+
}
6730+
66866731
JAVACODE
66876732
List<String> captureUnsupportedStatementDeclaration() {
66886733
List<String> tokens = new LinkedList<String>();

‎src/test/java/net/sf/jsqlparser/expression/JsonExpressionTest.java‎

Lines changed: 76 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,8 @@
1313
import net.sf.jsqlparser.test.TestUtils;
1414
import org.junit.jupiter.api.Test;
1515

16+
import static net.sf.jsqlparser.test.TestUtils.assertSqlCanBeParsedAndDeparsed;
17+
1618
class JsonExpressionTest {
1719

1820
@Test
@@ -33,4 +35,78 @@ void testIssue1792() throws JSQLParserException, InterruptedException {
3335
+ " END";
3436
TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
3537
}
38+
39+
@Test
40+
void testParenthesedJsonExpressionsIssue1792() throws JSQLParserException {
41+
String sqlStr =
42+
"SELECT table_a.b_e_t,\n"
43+
+ " CASE\n"
44+
+ " WHEN table_a.g_o_a_c IS NULL THEN 'a'\n"
45+
+ " ELSE table_a.g_o_a_c\n"
46+
+ " END AS e_cd,\n"
47+
+ " CASE\n"
48+
+ " WHEN table_a.a_f_t IS NULL THEN 'b'\n"
49+
+ " ELSE table_a.a_f_t\n"
50+
+ " END AS a_f_t,\n"
51+
+ " COUNT(1) AS count,\n"
52+
+ " ROUND(ABS(SUM(table_a.gb_eq))::NUMERIC, 2) AS total_x\n"
53+
+ "FROM (SELECT table_x.b_e_t,\n"
54+
+ " table_x.b_e_a,\n"
55+
+ " table_y.g_o_a_c,\n"
56+
+ " table_z.a_f_t,\n"
57+
+ " CASE\n"
58+
+ " WHEN table_x.b_e_a IS NOT NULL THEN table_x.b_e_a::DOUBLE PRECISION /\n"
59+
+ " schema_z.g_c_r(table_x.c_c,\n"
60+
+ " 'x'::CHARACTER VARYING,\n"
61+
+ " table_x.r_ts::DATE)\n"
62+
+ " ELSE\n"
63+
+ " CASE\n"
64+
+ " WHEN table_x.b_e_t::TEXT = 'p_e'::TEXT THEN (SELECT ((\n"
65+
+ " (table_x.pld::JSON -> 'p_d'::TEXT) ->>\n"
66+
+ " 's_a'::TEXT)::DOUBLE PRECISION) / schema_z.g_c_r(fba.s_c_c,\n"
67+
+ " 'x'::CHARACTER VARYING,\n"
68+
+ " table_x.r_ts::DATE)\n"
69+
+ " FROM schema_z.f_b_a fba\n"
70+
+ " JOIN schema_z.t_b_a_n_i table_y\n"
71+
+ " ON fba.b_a_i = table_y.f_b_a_id\n"
72+
+ " WHERE table_y.t_ngn_id =\n"
73+
+ " (((table_x.pld::JSON -> 'p_d'::TEXT) ->>\n"
74+
+ " 's_a_i'::TEXT)::BIGINT))\n"
75+
+ " WHEN table_x.b_e_t::TEXT = 'i_e'::TEXT\n"
76+
+ " THEN (SELECT (((table_x.pld::JSON -> 'i_d'::TEXT) ->> 'a'::TEXT)::DOUBLE PRECISION) /\n"
77+
+ " schema_z.g_c_r(fba.s_c_c, 'x'::CHARACTER VARYING,\n"
78+
+ " table_x.r_ts::DATE)\n"
79+
+ " FROM schema_z.f_b_a fba\n"
80+
+ " JOIN schema_z.t_b_a_n_i table_y\n"
81+
+ " ON fba.b_a_i = table_y.f_b_a_id\n"
82+
+ " WHERE table_y.t_ngn_id = (((table_x.pld::JSON -> 'i_d'::TEXT) ->>\n"
83+
+ " 's_a_i'::TEXT)::BIGINT))\n"
84+
+ " WHEN table_x.b_e_t::TEXT = 'i_e_2'::TEXT\n"
85+
+ " THEN (SELECT (((table_x.pld::JSON -> 'i_d'::TEXT) ->> 'a'::TEXT)::DOUBLE PRECISION) /\n"
86+
+ " schema_z.g_c_r(fba.s_c_c, 'x'::CHARACTER VARYING,\n"
87+
+ " table_x.r_ts::DATE)\n"
88+
+ " FROM schema_z.f_b_a fba\n"
89+
+ " JOIN schema_z.t_b_a_n_i table_y\n"
90+
+ " ON fba.b_a_i = table_y.f_b_a_id\n"
91+
+ " WHERE table_y.t_ngn_id = (((table_x.pld::JSON -> 'id'::TEXT) ->>\n"
92+
+ " 'd_i'::TEXT)::BIGINT))\n"
93+
+ " WHEN table_x.b_e_t::TEXT = 'm_e'::TEXT\n"
94+
+ " THEN (SELECT (((table_x.pld::JSON -> 'o'::TEXT) ->> 'eda'::TEXT)::DOUBLE PRECISION) /\n"
95+
+ " schema_z.g_c_r(\n"
96+
+ " ((table_x.pld::JSON -> 'o'::TEXT) ->> 'dc'::TEXT)::CHARACTER VARYING,\n"
97+
+ " 'x'::CHARACTER VARYING, table_x.r_ts::DATE))\n"
98+
+ " ELSE NULL::DOUBLE PRECISION\n"
99+
+ " END\n"
100+
+ " END AS gb_eq\n"
101+
+ " FROM schema_z.baz\n"
102+
+ " LEFT JOIN f_ctl.g_o_f_e_t_a_m table_y\n"
103+
+ " ON table_x.p_e_m LIKE table_y.f_e_m_p\n"
104+
+ " LEFT JOIN f_ctl.g_o_c_a_t table_z\n"
105+
+ " ON table_z.c_a_t_c = table_y.g_o_a_c\n"
106+
+ " WHERE table_x.p_st = 'E'\n"
107+
+ " ) table_a\n"
108+
+ "GROUP BY 1, 2, 3";
109+
110+
assertSqlCanBeParsedAndDeparsed(sqlStr, true);
111+
}
36112
}

‎src/test/java/net/sf/jsqlparser/statement/UnsupportedStatementTest.java‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,7 @@
1717
import org.junit.jupiter.api.Test;
1818
import org.junit.jupiter.api.function.Executable;
1919

20+
import static org.junit.jupiter.api.Assertions.assertEquals;
2021
import static org.junit.jupiter.api.Assertions.assertTrue;
2122

2223
public class UnsupportedStatementTest {
@@ -99,4 +100,25 @@ void testCreate() throws JSQLParserException {
99100
statement = TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
100101
assertTrue(statement instanceof UnsupportedStatement);
101102
}
103+
104+
@Test
105+
void testFunctions() throws JSQLParserException {
106+
String sqlStr =
107+
"CREATE OR REPLACE FUNCTION func_example(foo integer)\n"
108+
+ "RETURNS integer AS $$\n"
109+
+ "BEGIN\n"
110+
+ " RETURN foo + 1;\n"
111+
+ "END\n"
112+
+ "$$ LANGUAGE plpgsql;\n"
113+
+ "\n"
114+
+ "CREATE OR REPLACE FUNCTION func_example2(IN foo integer, OUT bar integer)\n"
115+
+ "AS $$\n"
116+
+ "BEGIN\n"
117+
+ " SELECT foo + 1 INTO bar;\n"
118+
+ "END\n"
119+
+ "$$ LANGUAGE plpgsql;";
120+
121+
Statements statements = CCJSqlParserUtil.parseStatements(sqlStr);
122+
assertEquals(2, statements.size());
123+
}
102124
}

‎src/test/java/net/sf/jsqlparser/statement/select/PostgresTest.java‎

Lines changed: 11 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -32,14 +32,17 @@ public void testExtractFunction() throws JSQLParserException {
3232

3333
@Test
3434
public void testExtractFunctionIssue1582() throws JSQLParserException {
35-
String sqlStr = "" + "select\n" + " t0.operatienr\n" + " , case\n"
36-
+ " when\n"
37-
+ " case when (t0.vc_begintijd_operatie is null or lpad((extract('hours' from 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"
38-
+ " else (greatest(((extract('hours' from (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"
39-
+ " end = 0 then null\n"
40-
+ " else '25. Meer dan 4 uur'\n"
41-
+ " end \n"
42-
+ " as snijtijd_interval";
35+
String sqlStr = ""
36+
+ "select\n"
37+
+ " t0.operatienr\n"
38+
+ " , case\n"
39+
+ " when\n"
40+
+ " case when (t0.vc_begintijd_operatie is null or lpad((extract('hours' from 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"
41+
+ " else (greatest(((extract('hours' from (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"
42+
+ " end = 0 then null\n"
43+
+ " else '25. Meer dan 4 uur'\n"
44+
+ " end\n"
45+
+ " as snijtijd_interval";
4346
TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
4447
}
4548

‎src/test/java/net/sf/jsqlparser/statement/select/SelectTest.java‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -61,6 +61,7 @@
6161
import java.nio.charset.StandardCharsets;
6262
import java.util.ArrayList;
6363
import java.util.List;
64+
import java.util.logging.Level;
6465

6566
import static net.sf.jsqlparser.test.TestUtils.assertDeparse;
6667
import static net.sf.jsqlparser.test.TestUtils.assertExpressionCanBeDeparsedAs;

0 commit comments

Comments
 (0)