feat: functions blocks, parenthesed JSON Expressions

- fixes #1792, the very complex example - fixes #1477
JSQLParser · manticore-projects · Jun 15, 2023 · Dec 11, 2022 · Dec 22, 2022 · Jan 8, 2023
commit e19dc0e081f741df2c3f2a1aa909486f95c79c19
diff --git a/src/main/java/net/sf/jsqlparser/parser/CCJSqlParserUtil.java b/src/main/java/net/sf/jsqlparser/parser/CCJSqlParserUtil.java
@@ -104,6 +104,7 @@ public static Statement parse(String sql, ExecutorService executorService,
             LOGGER.info("Trying SIMPLE parsing " + (allowComplex ? "first" : "only"));
             statement = parseStatement(parser.withAllowComplexParsing(false), executorService);
         } catch (JSQLParserException ex) {
+            LOGGER.info("Nesting Depth" + getNestingDepth(sql));
             if (allowComplex && getNestingDepth(sql) <= ALLOWED_NESTING_DEPTH) {
                 LOGGER.info("Trying COMPLEX parsing when SIMPLE parsing failed");
                 // beware: the parser must not be reused, but needs to be re-initiated

diff --git a/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt b/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt
@@ -554,7 +554,7 @@ TOKEN:
             input_stream.backup(image.length() - matchedToken.image.length() );
         }
     }
-| < S_QUOTED_IDENTIFIER: "\"" (~["\n","\r","\""])* "\"" | "$$" (~["\n","\r","\""])* "$$" | ("`" (~["\n","\r","`"])+ "`") | ( "[" (~["\n","\r","]"])* "]" ) >
+| < S_QUOTED_IDENTIFIER: "\"" (~["\n","\r","\""])* "\"" | "$$" (~["$"])* "$$" | ("`" (~["\n","\r","`"])+ "`") | ( "[" (~["\n","\r","]"])* "]" ) >
     {
         if ( !configuration.getAsBoolean(Feature.allowSquareBracketQuotation) && matchedToken.image.charAt(0) == '[' ) {
             matchedToken.image = "[";
@@ -4048,6 +4048,17 @@ ArrayConstructor ArrayConstructor(boolean arrayKeyword) : {
     { return array; }
 }
 
+Expression ParenthesedExpression():
+{
+    Expression expression;
+}
+{
+    "(" expression = PrimaryExpression()  ")"
+    {
+        return new Parenthesis(expression);
+    }
+}
+
 JsonExpression JsonExpression() : {
   JsonExpression result = new JsonExpression();
   Expression expr;
@@ -4056,6 +4067,7 @@ JsonExpression JsonExpression() : {
   CastExpression castExpr = null;
 }
 {
+    { System.out.println("Complex:" + getAsBoolean(Feature.allowComplexParsing));}
     (
         LOOKAHEAD(3, {!interrupted}) expr=CaseWhenExpression()
         |
@@ -4071,13 +4083,16 @@ JsonExpression JsonExpression() : {
         |
         LOOKAHEAD(FullTextSearch(), {getAsBoolean(Feature.allowComplexParsing) && !interrupted}) expr = FullTextSearch()
         |
-        LOOKAHEAD( 3 , {getAsBoolean(Feature.allowComplexParsing) && !interrupted} ) expr=Function()
+        LOOKAHEAD( Function() , {getAsBoolean(Feature.allowComplexParsing) && !interrupted} ) expr=Function()
         |
         LOOKAHEAD( 2, {!interrupted} ) expr=Column()
         |
         token=<S_CHAR_LITERAL> { expr = new StringValue(token.image); }
         |
-        LOOKAHEAD( {!interrupted} ) "(" expr=ParenthesedSelect() ")"
+        LOOKAHEAD(ParenthesedExpression(), {getAsBoolean(Feature.allowComplexParsing)} ) expr = ParenthesedExpression()
+        |
+        LOOKAHEAD( 3, {getAsBoolean(Feature.allowComplexParsing) && !interrupted}) expr=ParenthesedSelect()
+
     )
 
     (
@@ -6607,7 +6622,7 @@ CreateFunctionalStatement CreateFunctionStatement(boolean isUsingOrReplace):
    |
    <K_PROCEDURE> { statementType = "PROCEDURE"; }
   )
-  tokens=captureRest()
+  tokens=captureFunctionBody()
   {
     if(statementType.equals("FUNCTION")) {
       type = new CreateFunction(isUsingOrReplace, tokens);
@@ -6683,6 +6698,36 @@ List<String> captureRest() {
   return tokens;
 }
 
+/**
+* Reads the tokens of a function or procedure body.
+* A function body can end in 2 ways:
+* 1) BEGIN...END;
+* 2) Postgres: $$...$$...;
+*/
+
+JAVACODE
+List<String> captureFunctionBody() {
+  List<String> tokens = new LinkedList<String>();
+  Token tok;
+  boolean foundEnd = false;
+  while(true) {
+    tok = getToken(1);
+    int l = tokens.size();
+    if( tok.kind == EOF || ( foundEnd && tok.kind == ST_SEMICOLON) ) {
+      break;
+    } else if ( l>0 && ( tok.image.equals(".") ||  tokens.get(l-1).endsWith(".")) ) {
+        tokens.set(l-1, tokens.get(l-1) + tok.image);
+    } else {
+        tokens.add(tok.image);
+    }
+    foundEnd |= (tok.kind == K_END)
+        || ( tok.image.trim().startsWith("$$") && tok.image.trim().endsWith("$$")) ;
+
+    tok = getNextToken();
+  }
+  return tokens;
+}
+
 JAVACODE
 List<String> captureUnsupportedStatementDeclaration() {
   List<String> tokens = new LinkedList<String>();

diff --git a/src/test/java/net/sf/jsqlparser/expression/JsonExpressionTest.java b/src/test/java/net/sf/jsqlparser/expression/JsonExpressionTest.java
@@ -13,6 +13,8 @@
 import net.sf.jsqlparser.test.TestUtils;
 import org.junit.jupiter.api.Test;
 
+import static net.sf.jsqlparser.test.TestUtils.assertSqlCanBeParsedAndDeparsed;
+
 class JsonExpressionTest {
 
     @Test
@@ -33,4 +35,78 @@ void testIssue1792() throws JSQLParserException, InterruptedException {
                         + " END";
         TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
     }
+
+    @Test
+    void testParenthesedJsonExpressionsIssue1792() throws JSQLParserException {
+        String sqlStr =
+                "SELECT table_a.b_e_t,\n"
+                        + "       CASE\n"
+                        + "           WHEN table_a.g_o_a_c IS NULL THEN 'a'\n"
+                        + "           ELSE table_a.g_o_a_c\n"
+                        + "           END                                        AS e_cd,\n"
+                        + "       CASE\n"
+                        + "           WHEN table_a.a_f_t IS NULL THEN 'b'\n"
+                        + "           ELSE table_a.a_f_t\n"
+                        + "           END                                        AS a_f_t,\n"
+                        + "       COUNT(1)                                       AS count,\n"
+                        + "       ROUND(ABS(SUM(table_a.gb_eq))::NUMERIC, 2) AS total_x\n"
+                        + "FROM (SELECT table_x.b_e_t,\n"
+                        + "             table_x.b_e_a,\n"
+                        + "             table_y.g_o_a_c,\n"
+                        + "             table_z.a_f_t,\n"
+                        + "             CASE\n"
+                        + "                 WHEN table_x.b_e_a IS NOT NULL THEN table_x.b_e_a::DOUBLE PRECISION /\n"
+                        + "                                                                 schema_z.g_c_r(table_x.c_c,\n"
+                        + "                                                                                  'x'::CHARACTER VARYING,\n"
+                        + "                                                                                  table_x.r_ts::DATE)\n"
+                        + "                 ELSE\n"
+                        + "                     CASE\n"
+                        + "                         WHEN table_x.b_e_t::TEXT = 'p_e'::TEXT THEN (SELECT ((\n"
+                        + "                                 (table_x.pld::JSON -> 'p_d'::TEXT) ->>\n"
+                        + "                                 's_a'::TEXT)::DOUBLE PRECISION) / schema_z.g_c_r(fba.s_c_c,\n"
+                        + "                                                                                             'x'::CHARACTER VARYING,\n"
+                        + "                                                                                             table_x.r_ts::DATE)\n"
+                        + "                                                                                                   FROM schema_z.f_b_a fba\n"
+                        + "                                                                                                            JOIN schema_z.t_b_a_n_i table_y\n"
+                        + "                                                                                                                 ON fba.b_a_i = table_y.f_b_a_id\n"
+                        + "                                                                                                   WHERE table_y.t_ngn_id =\n"
+                        + "                                                                                                         (((table_x.pld::JSON -> 'p_d'::TEXT) ->>\n"
+                        + "                                                                                                           's_a_i'::TEXT)::BIGINT))\n"
+                        + "                         WHEN table_x.b_e_t::TEXT = 'i_e'::TEXT\n"
+                        + "                             THEN (SELECT (((table_x.pld::JSON -> 'i_d'::TEXT) ->> 'a'::TEXT)::DOUBLE PRECISION) /\n"
+                        + "                                          schema_z.g_c_r(fba.s_c_c, 'x'::CHARACTER VARYING,\n"
+                        + "                                                           table_x.r_ts::DATE)\n"
+                        + "                                   FROM schema_z.f_b_a fba\n"
+                        + "                                            JOIN schema_z.t_b_a_n_i table_y\n"
+                        + "                                                 ON fba.b_a_i = table_y.f_b_a_id\n"
+                        + "                                   WHERE table_y.t_ngn_id = (((table_x.pld::JSON -> 'i_d'::TEXT) ->>\n"
+                        + "                                                             's_a_i'::TEXT)::BIGINT))\n"
+                        + "                         WHEN table_x.b_e_t::TEXT = 'i_e_2'::TEXT\n"
+                        + "                             THEN (SELECT (((table_x.pld::JSON -> 'i_d'::TEXT) ->> 'a'::TEXT)::DOUBLE PRECISION) /\n"
+                        + "                                          schema_z.g_c_r(fba.s_c_c, 'x'::CHARACTER VARYING,\n"
+                        + "                                                           table_x.r_ts::DATE)\n"
+                        + "                                   FROM schema_z.f_b_a fba\n"
+                        + "                                            JOIN schema_z.t_b_a_n_i table_y\n"
+                        + "                                                 ON fba.b_a_i = table_y.f_b_a_id\n"
+                        + "                                   WHERE table_y.t_ngn_id = (((table_x.pld::JSON -> 'id'::TEXT) ->>\n"
+                        + "                                                             'd_i'::TEXT)::BIGINT))\n"
+                        + "                         WHEN table_x.b_e_t::TEXT = 'm_e'::TEXT\n"
+                        + "                             THEN (SELECT (((table_x.pld::JSON -> 'o'::TEXT) ->> 'eda'::TEXT)::DOUBLE PRECISION) /\n"
+                        + "                                          schema_z.g_c_r(\n"
+                        + "                                                  ((table_x.pld::JSON -> 'o'::TEXT) ->> 'dc'::TEXT)::CHARACTER VARYING,\n"
+                        + "                                                  'x'::CHARACTER VARYING, table_x.r_ts::DATE))\n"
+                        + "                         ELSE NULL::DOUBLE PRECISION\n"
+                        + "                         END\n"
+                        + "                 END AS gb_eq\n"
+                        + "      FROM schema_z.baz\n"
+                        + "               LEFT JOIN f_ctl.g_o_f_e_t_a_m table_y\n"
+                        + "                         ON table_x.p_e_m LIKE table_y.f_e_m_p\n"
+                        + "               LEFT JOIN f_ctl.g_o_c_a_t table_z\n"
+                        + "                         ON table_z.c_a_t_c = table_y.g_o_a_c\n"
+                        + "      WHERE table_x.p_st = 'E'\n"
+                        + "     ) table_a\n"
+                        + "GROUP BY 1, 2, 3";
+
+        assertSqlCanBeParsedAndDeparsed(sqlStr, true);
+    }
 }
diff --git a/src/test/java/net/sf/jsqlparser/statement/UnsupportedStatementTest.java b/src/test/java/net/sf/jsqlparser/statement/UnsupportedStatementTest.java
@@ -17,6 +17,7 @@
 import org.junit.jupiter.api.Test;
 import org.junit.jupiter.api.function.Executable;
 
+import static org.junit.jupiter.api.Assertions.assertEquals;
 import static org.junit.jupiter.api.Assertions.assertTrue;
 
 public class UnsupportedStatementTest {
@@ -99,4 +100,25 @@ void testCreate() throws JSQLParserException {
         statement = TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
         assertTrue(statement instanceof UnsupportedStatement);
     }
+
+    @Test
+    void testFunctions() throws JSQLParserException {
+        String sqlStr =
+                "CREATE OR REPLACE FUNCTION func_example(foo integer)\n"
+                        + "RETURNS integer AS $$\n"
+                        + "BEGIN\n"
+                        + "  RETURN foo + 1;\n"
+                        + "END\n"
+                        + "$$ LANGUAGE plpgsql;\n"
+                        + "\n"
+                        + "CREATE OR REPLACE FUNCTION func_example2(IN foo integer, OUT bar integer)\n"
+                        + "AS $$\n"
+                        + "BEGIN\n"
+                        + "    SELECT foo + 1 INTO bar;\n"
+                        + "END\n"
+                        + "$$ LANGUAGE plpgsql;";
+
+        Statements statements = CCJSqlParserUtil.parseStatements(sqlStr);
+        assertEquals(2, statements.size());
+    }
 }
diff --git a/src/test/java/net/sf/jsqlparser/statement/select/PostgresTest.java b/src/test/java/net/sf/jsqlparser/statement/select/PostgresTest.java
@@ -32,14 +32,17 @@ public void testExtractFunction() throws JSQLParserException {
 
     @Test
     public void testExtractFunctionIssue1582() throws JSQLParserException {
-        String sqlStr = "" + "select\n" + "              t0.operatienr\n" + "            , case\n"
-                + "                when\n"
-                + "                    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"
-                + "                         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"
-                + "                end = 0 then null\n"
-                + "                    else '25. Meer dan 4 uur'\n"
-                + "                end                                                                                                                                                  \n"
-                + "              as snijtijd_interval";
+        String sqlStr = ""
+                + "select\n"
+                + "  t0.operatienr\n"
+                + "  , case\n"
+                + "    when\n"
+                + "        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"
+                + "             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"
+                + "    end = 0 then null\n"
+                + "        else '25. Meer dan 4 uur'\n"
+                + "    end\n"
+                + "  as snijtijd_interval";
         TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr, true);
     }
 

diff --git a/src/test/java/net/sf/jsqlparser/statement/select/SelectTest.java b/src/test/java/net/sf/jsqlparser/statement/select/SelectTest.java
@@ -61,6 +61,7 @@
 import java.nio.charset.StandardCharsets;
 import java.util.ArrayList;
 import java.util.List;
+import java.util.logging.Level;
 
 import static net.sf.jsqlparser.test.TestUtils.assertDeparse;
 import static net.sf.jsqlparser.test.TestUtils.assertExpressionCanBeDeparsedAs;