[BUG] JSQLParser 4.6 : PostgreSQL : CPU burn when parsing certain queries

[llehtinen]

Always check against the Latest SNAPSHOT of JSQLParser and the Syntax Diagram

Failing SQL Feature:

• Parenthesis in a query lead to CPU burn - my example has ((( ... ))), add more parenthesis to see significant increase in the time it takes to fail

SQL Example:

• Valid PostgreSQL query (heavily reduced from a large query with many nested cases):

  -- Simple query which fails fast
  SELECT ('{"obj":{"field": "value"}}'::JSON -> 'obj'::TEXT ->> 'field'::TEXT)
  
  -- Same query nested in a case statement, takes the CPU for a spin
  SELECT
   CASE
      WHEN true
      THEN (SELECT ((('{"obj":{"field": "value"}}'::JSON -> 'obj'::TEXT ->> 'field'::TEXT))))
   END

My notes:

I was trying to narrow down the issue by testing the case statement query like this:

CCJSqlParserUtil.newParser(sql).withAllowComplexParsing(allowComplexParsing).statements();

JSql	allowComplexParsing	outcome
4.5	false	Fails fast
4.5	true	Fails after ~10 seconds of 100% CPU
4.6	false	Fails after ~20 seconds of 100% CPU
4.6	true	Doesn't finish in 3 minutes, CPU stays at 100%

Software Information:

• JSqlParser 4.5, 4.6
• Database PostgreSQL
  • https://www.postgresql.org/docs/9.3/functions-json.html

[manticore-projects]

Greetings!

Thanks for reporting, we are always very interested in such performance related corner cases.
Good news is: With PR #1778 your complex query times out as expected (within 20 seconds):

    // Supposed to time out
    @Test
    void testComplexIssue1792() throws JSQLParserException {
        String sqlStr =
                "SELECT\n"
                + " CASE\n"
                + "    WHEN true\n"
                + "    THEN (SELECT ((('{\"obj\":{\"field\": \"value\"}}'::JSON -> 'obj'::TEXT ->> 'field'::TEXT))))\n"
                + " END";
        Assertions.assertThrows(TimeoutException.class, new Executable() {
            @Override
            public void execute() throws Throwable {
                try {
                    assertSqlCanBeParsedAndDeparsed(sqlStr, true);
                } catch (JSQLParserException ex) {
                    throw ex.getCause();
                }
            }
        });
    }

Now, the more interesting question is: Is this query valid? (I am not good with this JSON crap.)
If it is valid on the RDBMS, then I would like to look for ways to speeding it up.

[manticore-projects]

You can make it time out faster when you don't want to wait 20 seconds:

    // Supposed to time out
    @Test
    void testComplexIssue1792() throws JSQLParserException {
        String sqlStr =
                "SELECT\n"
                + " CASE\n"
                + "    WHEN true\n"
                + "    THEN (SELECT ((('{\"obj\":{\"field\": \"value\"}}'::JSON -> 'obj'::TEXT ->> 'field'::TEXT))))\n"
                + " END";
        Assertions.assertThrows(TimeoutException.class, new Executable() {
            @Override
            public void execute() throws Throwable {
                try {
                    CCJSqlParserUtil.parse(sqlStr, parser -> {
                        parser.withTimeOut(1000);
                        parser.withAllowComplexParsing(true);
                    });
                } catch (JSQLParserException ex) {
                    throw ex.getCause();
                }
            }
        });
    }

[llehtinen]

Hey, and thanks for having a look.

The query is valid (whether it's a good query, different question).

I got into troubleshooting this because we had to roll back a deployment due to the CPU burn this was causing.

As for the other suggestion, we are intentionally staying away from the CCJSqlParserUtil.parse due to the single use executors created (and thrown away) by it.

[manticore-projects]

Thanks for quick round turn.

The query is valid (whether it's a good query, different question).
Great, so ideally it should parse and I would aim for that.

I got into troubleshooting this because we had to roll back a deployment due to the CPU burn this was causing.

Sorry for that but I still wonder how exactly you have achieved that. The Executor is there since 4.6 and should catch such timeouts reliably. How exactly are you parsing, please show me the boiler plate code.

As for the other suggestion, we are intentionally staying away from the CCJSqlParserUtil.parse due to the single use executors created (and thrown away) by it.

This executor my friend would have saved you! Because it observes if a query stalls and kills it softly before your CPU burns. Do you have a better/alternative suggestion replacing the "single use executor thrown away"? E. g. have a single instance of this executor which will be used for all queries and then only get destroyed on demand at the end?

[llehtinen]

Sorry for that but I still wonder how exactly you have achieved that. The Executor is there since 4.6 and should catch such timeouts reliably. How exactly are you parsing, please show me the boiler plate code.

Here is the code:

    /*
      Don't use `CCJSqlParserUtil.parse`, this has an overhead of launching a new executor service and parsing the sql there.
     */
    CCJSqlParser parser = CCJSqlParserUtil.newParser(sql).withAllowComplexParsing(false);

    return parser.Statements();

In the application in question, the number of requests is such that timing out after 20 seconds is not much better than never finishing.

One option you might consider is letting the client provide the executor. But the parsing sometimes going AWOL like this, is a bigger concern in my opinion.

[manticore-projects]

I am sorry for asking a dumb question: Why is the simplified select also failing if the query was valid?

    // Supposed to fail, but not to hang or time out
    @Test
    @Timeout(2000)
    void testIssue1792() throws JSQLParserException {
        String sqlStr =
                "SELECT ('{\"obj\":{\"field\": \"value\"}}'::JSON -> 'obj'::TEXT ->> 'field'::TEXT)";
        Assertions.assertThrows(JSQLParserException.class, new Executable() {
            @Override
            public void execute() throws Throwable {
               assertSqlCanBeParsedAndDeparsed(sqlStr, true);
            }
        });
    }

[llehtinen]

Why JSqlParser is failing to parse it? I don't know 😄

[llehtinen]

I believe this has not been an issue for us with 4.5 because it fails fast.

We're using this library to collect statistics on table usage, and those are recorded after the query has been executed. If it fails, it's not the end of the world. But if it hangs, it sort of is. As it turned out.

[manticore-projects]

Ok, so as far as I understand it, we have 2 problems here:

1. We need to provide a facility, where a large number of queries can be parsed under the super-vision of one single time-out monitor (instead of spawning one for each query).

2. We need to figure out, why your query can't be parsed.

Unfortunately you will always be able to craft a statement which will trip the Parser.
In 4.5 it has not been an issue because many other things have been an issue. We have added support for many many syntax -- but this also made parsing certain statements much more complex.

[manticore-projects]

Why JSqlParser is failing to parse it? I don't know smile

Can you provide an illustration of the Postgres AST for this please? JSON in RDBMS is my arch nemesis and I need to understand first, what this thing is supposed to mean. Thanks for helping!

[llehtinen]

Maybe this helps. The first -> just extracts the value of obj, i.e. {"field": "value"} from the original json.

Then, in the original query, the subsequent ->> gets the value of field.

As for the differences between -> and ->> operators: https://www.postgresql.org/docs/9.3/functions-json.html.

As mentioned in the issue, this silly query is

heavily reduced from a large query with many nested cases

I just tried to boil it down to something that caused the behavior we observed.

[manticore-projects]

As far as I understand it, this kind of chaining of JSON Expressions has not been considered/implemented.
I would try to get it working tomorrow.

[llehtinen]

I played a bit with your suggestion and noticed that it took twice as long as the timeout value I provided.

On closer look, somewhat unexpectedly, the parsing was attempted twice (each time in its own single-use executor).

        // first, try to parse fast and simple
        try {
            CCJSqlParser parser = newParser(sql).withAllowComplexParsing(false);
            if (consumer != null) {
                consumer.accept(parser);
            }
            statement = parseStatement(parser);
        } catch (JSQLParserException ex) {
            if (getNestingDepth(sql)<=ALLOWED_NESTING_DEPTH) {
                CCJSqlParser parser = newParser(sql).withAllowComplexParsing(true);
                if (consumer != null) {
                    consumer.accept(parser);
                }
                statement = parseStatement(parser);
            }
        }

[manticore-projects]

Yes, but you can avoid this by dis-allowing COMPLEX parsing:

CCJSqlParserUtil.parse(sqlStr, parser -> {
                        parser.withTimeOut(1000);
                        parser.withAllowComplexParsing(false);
                    });

[llehtinen]

With 4.6, the example you provided, there are two attempts to parse the statement that time out.

On the second attempt, complex parsing setting is overridden to false, but there's still an attempt, and it still times out.

In total, it takes a little over 2 seconds.

[manticore-projects]

Oopps, that has been an oversight. The second attempt shall only happen when Complex Parsing was allowed.
I will correct that tomorrow. Thank you for pointing it out.

[manticore-projects]

So task list for tomorrow, by priority:

1. fix running into complex parsing, when complex parsing not set
2. provide parse methods, which will accept an Executor Service as parameter (to be created and destroyed outside the parser, globally)

--> this should remedy the pressing issue and allow prompt timeout with failure

3. implement chaining of JSON expressions

--> this may cure the root cause (parser goes lost visiting the AST nodes)

[manticore-projects]

Good Morning.

Please checkout commit 4f0488c from PR #1778 (precompiled JAR is attached).

It supports:
a) provision of a global ExecutorService (to be initiated and shutdown outside of the parser)
b) complex parsing only, when complex parsing was allowed (default) and complex parsing skipped, when not allowed

Please do let me know, if this suits your requirements or what changes you would like to request.
When sufficient, I would back port it to 4.6 (since upcoming 5.0 features a lot of good changes).

Illustration

    @Test
    void testComplexIssue1792() throws JSQLParserException {

        String sqlStr =
                "SELECT\n"
                        + " CASE\n"
                        + "    WHEN true\n"
                        + "    THEN (SELECT ((('{\"obj\":{\"field\": \"value\"}}'::JSON -> 'obj'::TEXT ->> 'field'::TEXT))))\n"
                        + " END";

        CCJSqlParserUtil.LOGGER.setLevel(Level.ALL);
        ExecutorService executorService = Executors.newCachedThreadPool();

        // Expect to fail fast with SIMPLE Parsing only when COMPLEX is not allowed
        // No TIMEOUT Exception shall be thrown
        // CCJSqlParserUtil.LOGGER will report:
        // 1) Allowed Complex Parsing: false
        // 2) Trying SIMPLE parsing only
        try {
            CCJSqlParserUtil.parse(sqlStr, executorService, parser -> {
                parser.withTimeOut(6000);
                parser.withAllowComplexParsing(false);
            });
        } catch (JSQLParserException ex) {
            assertFalse(ex.getCause() instanceof TimeoutException);
        }

        // Expect to time-out with COMPLEX Parsing allowed
        // CCJSqlParserUtil.LOGGER will report:
        // 1) Allowed Complex Parsing: true
        // 2) Trying SIMPLE parsing first
        // 3) Trying COMPLEX parsing when SIMPLE parsing failed
        try {
            CCJSqlParserUtil.parse(sqlStr, executorService, parser -> {
                parser.withTimeOut(6000);
                parser.withAllowComplexParsing(true);
            });
        } catch (JSQLParserException ex) {
            assertTrue(ex.getCause() instanceof TimeoutException);
        }
        executorService.shutdown();
    }

JSQLParser-4.7-SNAPSHOT.zip

[manticore-projects]

There is a small inconvenience now: I have also fixed the "Chained JSON Expressions" (at least for your particular example) and so the parser won't time-out or fail anymore making the test above obsolete.

So please:

1. verify the timeout/failing behaviour exactly with the Commit a7dfb94 or use the provided pre-compiled JAR; any later commit has those tests disabled (because they don't fail anymore)
2. send me more of your JSON statements (maybe the original, UN-simplified ones) for assurance that my fix was comprehensive (I am not good with this JSON stuff)

[llehtinen]

Hey, I tried with the latest. Simple query works indeed, original still won't parse. Here's a manually obfuscated version of it (I hope I didn't break it).

SELECT table_a.b_e_t,
       CASE
           WHEN table_a.g_o_a_c IS NULL THEN 'a'
           ELSE table_a.g_o_a_c
           END                                        AS e_cd,
       CASE
           WHEN table_a.a_f_t IS NULL THEN 'b'
           ELSE table_a.a_f_t
           END                                        AS a_f_t,
       COUNT(1)                                       AS count,
       ROUND(ABS(SUM(table_a.gb_eq))::NUMERIC, 2) AS total_x
FROM (SELECT table_x.b_e_t,
             table_x.b_e_a,
             table_y.g_o_a_c,
             table_z.a_f_t,
             CASE
                 WHEN table_x.b_e_a IS NOT NULL THEN table_x.b_e_a::DOUBLE PRECISION /
                                                                 schema_z.g_c_r(table_x.c_c,
                                                                                  'x'::CHARACTER VARYING,
                                                                                  table_x.r_ts::DATE)
                 ELSE
                     CASE
                         WHEN table_x.b_e_t::TEXT = 'p_e'::TEXT THEN (SELECT ((
                                 (table_x.pld::JSON -> 'p_d'::TEXT) ->>
                                 's_a'::TEXT)::DOUBLE PRECISION) / schema_z.g_c_r(fba.s_c_c,
                                                                                             'x'::CHARACTER VARYING,
                                                                                             table_x.r_ts::DATE)
                                                                                                   FROM schema_z.f_b_a fba
                                                                                                            JOIN schema_z.t_b_a_n_i table_y
                                                                                                                 ON fba.b_a_i = table_y.f_b_a_id
                                                                                                   WHERE table_y.t_ngn_id =
                                                                                                         (((table_x.pld::JSON -> 'p_d'::TEXT) ->>
                                                                                                           's_a_i'::TEXT)::BIGINT))
                         WHEN table_x.b_e_t::TEXT = 'i_e'::TEXT
                             THEN (SELECT (((table_x.pld::JSON -> 'i_d'::TEXT) ->> 'a'::TEXT)::DOUBLE PRECISION) /
                                          schema_z.g_c_r(fba.s_c_c, 'x'::CHARACTER VARYING,
                                                           table_x.r_ts::DATE)
                                   FROM schema_z.f_b_a fba
                                            JOIN schema_z.t_b_a_n_i table_y
                                                 ON fba.b_a_i = table_y.f_b_a_id
                                   WHERE table_y.t_ngn_id = (((table_x.pld::JSON -> 'i_d'::TEXT) ->>
                                                             's_a_i'::TEXT)::BIGINT))
                         WHEN table_x.b_e_t::TEXT = 'i_e_2'::TEXT
                             THEN (SELECT (((table_x.pld::JSON -> 'i_d'::TEXT) ->> 'a'::TEXT)::DOUBLE PRECISION) /
                                          schema_z.g_c_r(fba.s_c_c, 'x'::CHARACTER VARYING,
                                                           table_x.r_ts::DATE)
                                   FROM schema_z.f_b_a fba
                                            JOIN schema_z.t_b_a_n_i table_y
                                                 ON fba.b_a_i = table_y.f_b_a_id
                                   WHERE table_y.t_ngn_id = (((table_x.pld::JSON -> 'id'::TEXT) ->>
                                                             'd_i'::TEXT)::BIGINT))
                         WHEN table_x.b_e_t::TEXT = 'm_e'::TEXT
                             THEN (SELECT (((table_x.pld::JSON -> 'o'::TEXT) ->> 'eda'::TEXT)::DOUBLE PRECISION) /
                                          schema_z.g_c_r(
                                                  ((table_x.pld::JSON -> 'o'::TEXT) ->> 'dc'::TEXT)::CHARACTER VARYING,
                                                  'x'::CHARACTER VARYING, table_x.r_ts::DATE))
                         ELSE NULL::DOUBLE PRECISION
                         END
                 END AS gb_eq
      FROM schema_z.baz
               LEFT JOIN f_ctl.g_o_f_e_t_a_m table_y
                         ON table_x.p_e_m LIKE table_y.f_e_m_p
               LEFT JOIN f_ctl.g_o_c_a_t table_z
                         ON table_z.c_a_t_c = table_y.g_o_a_c
      WHERE table_x.p_st = 'E'
     ) table_a
GROUP BY 1, 2, 3

[manticore-projects]

Thank you, I appreciate.

I spotted this one: table_x.b_e_a::DOUBLE PRECISION, do they really allow White space for the column types?

[llehtinen]

Yes, that seems to be legitimate (https://www.postgresql.org/docs/current/datatype-numeric.html)

[manticore-projects]

Well, let me try your statement with proper Column Types (no white space) and if this works, I will remove Postgres from the list of supported RDBMs.

Remember, when SQL was invented for enabling any personal assistant to query data without the need IT knowledge?
:-D

[manticore-projects]

In the mean time, how do you feel about the new TimeOut behaviour and the handling of the Executor? How does it match your needs?

[manticore-projects]

Again, thank you for your example.
I believe there are 2 challenges:

1) the Column Types with White Space, like DOUBLE PRECISION (that's a minor issue I will fix) This was considered already!
2) the additional brackets in your JSON Expression

-- Fails
SELECT (((table_x.pld::JSON -> 'p_d'::TEXT) ->> 's_a'::TEXT)::DOUBLE) 
        / schema_z.g_c_r(fba.s_c_c,'x'::CHARACTER, table_x.r_ts::DATE)

-- Works
SELECT (table_x.pld::JSON -> 'p_d'::TEXT ->> 's_a'::TEXT::DOUBLE) 
        / schema_z.g_c_r(fba.s_c_c,'x'::CHARACTER, table_x.r_ts::DATE)

I should be able to fix that as well, but it will take some time, sorry.

[llehtinen]

I think providing a way for the client to control the thread pool is a good thing. Whether or not we'll use it, early to say. I think we'll need to have a think about the potential risk of a runaway parser, and whether we want to handle it somehow differently.

[manticore-projects]

I think providing a way for the client to control the thread pool is a good thing. Whether or not we'll use it, early to say. I think we'll need to have a think about the potential risk of a runaway parser, and whether we want to handle it somehow differently.

I understand the challenge, but unfortunately I came to the conclusion that for SQL based on JavaCC with a RDBMS agnostic scope, we won't be able to prevent runaway situations. At least, I am not capable of achieving this and I have fixed many and most of such runaway conditions.

So mitigating via a Time Out Observer seems the most robust approach to me, although it's brute force. Anyone with better ideas is certainly welcome.

[manticore-projects]

Fixed the complex statement via commit 5263b91.
As suggested, the extra brackets around the JSon Expressions caused the problem, rest of the query including Case .. When ... and PostgreSQL Cast expressions ran normally.

Complex parsing mode is needed though (because of those extra brackets), Simple will timeout.

[manticore-projects]

Now you can also pretty print and format your statement.

Or print the AST:

SQL Text
 └─Statements: statement.select.PlainSelect
    ├─selectItems: statement.select.SelectItem
    │  └─Column: table_a.b_e_t
    ├─selectItems: statement.select.SelectItem
    │  ├─expression: expression.CaseExpression
    │  │  ├─whenClauses: expression.WhenClause
    │  │  │  ├─whenExpression: expression.operators.relational.IsNullExpression
    │  │  │  │  └─Column: table_a.g_o_a_c
    │  │  │  └─StringValue: 'a'
    │  │  └─Column: table_a.g_o_a_c
    │  └─Alias:  AS e_cd
    ├─selectItems: statement.select.SelectItem
    │  ├─expression: expression.CaseExpression
    │  │  ├─whenClauses: expression.WhenClause
    │  │  │  ├─whenExpression: expression.operators.relational.IsNullExpression
    │  │  │  │  └─Column: table_a.a_f_t
    │  │  │  └─StringValue: 'b'
    │  │  └─Column: table_a.a_f_t
    │  └─Alias:  AS a_f_t
    ├─selectItems: statement.select.SelectItem
    │  ├─expression: expression.Function
    │  │  └─ExpressionList: 1
    │  └─Alias:  AS count
    ├─selectItems: statement.select.SelectItem
    │  ├─expression: expression.Function
    │  │  └─ExpressionList: ABS(SUM(table_a.gb_eq))::NUMERIC, 2
    │  └─Alias:  AS total_x
    ├─fromItem: statement.select.ParenthesedSelect
    │  ├─Alias:  table_a
    │  └─select: statement.select.PlainSelect
    │     ├─selectItems: statement.select.SelectItem
    │     │  └─Column: table_x.b_e_t
    │     ├─selectItems: statement.select.SelectItem
    │     │  └─Column: table_x.b_e_a
    │     ├─selectItems: statement.select.SelectItem
    │     │  └─Column: table_y.g_o_a_c
    │     ├─selectItems: statement.select.SelectItem
    │     │  └─Column: table_z.a_f_t
    │     ├─selectItems: statement.select.SelectItem
    │     │  ├─expression: expression.CaseExpression
    │     │  │  ├─whenClauses: expression.WhenClause
    │     │  │  │  ├─whenExpression: expression.operators.relational.IsNullExpression
    │     │  │  │  │  └─Column: table_x.b_e_a
    │     │  │  │  └─thenExpression: expression.operators.arithmetic.Division
    │     │  │  │     ├─leftExpression: expression.CastExpression
    │     │  │  │     │  ├─Column: table_x.b_e_a
    │     │  │  │     │  └─ColDataType: DOUBLE PRECISION
    │     │  │  │     └─rightExpression: expression.Function
    │     │  │  │        └─ExpressionList: table_x.c_c, 'x'::CHARACTER VARYING, table_x.r_ts::DATE
    │     │  │  └─elseExpression: expression.CaseExpression
    │     │  │     ├─whenClauses: expression.WhenClause
    │     │  │     │  ├─whenExpression: expression.operators.relational.EqualsTo
    │     │  │     │  │  ├─leftExpression: expression.CastExpression
    │     │  │     │  │  │  ├─Column: table_x.b_e_t
    │     │  │     │  │  │  └─ColDataType: TEXT
    │     │  │     │  │  └─rightExpression: expression.CastExpression
    │     │  │     │  │     ├─StringValue: 'p_e'
    │     │  │     │  │     └─ColDataType: TEXT
    │     │  │     │  └─thenExpression: statement.select.ParenthesedSelect
    │     │  │     │     └─select: statement.select.PlainSelect
    │     │  │     │        ├─selectItems: statement.select.SelectItem
    │     │  │     │        │  └─expression: expression.operators.arithmetic.Division
    │     │  │     │        │     ├─leftExpression: expression.Parenthesis
    │     │  │     │        │     │  └─expression: expression.CastExpression
    │     │  │     │        │     │     ├─leftExpression: expression.Parenthesis
    │     │  │     │        │     │     │  └─expression: expression.JsonExpression
    │     │  │     │        │     │     │     └─expr: expression.CastExpression
    │     │  │     │        │     │     │        ├─leftExpression: expression.JsonExpression
    │     │  │     │        │     │     │        │  └─expr: expression.Parenthesis
    │     │  │     │        │     │     │        │     └─expression: expression.JsonExpression
    │     │  │     │        │     │     │        │        └─expr: expression.CastExpression
    │     │  │     │        │     │     │        │           ├─leftExpression: expression.JsonExpression
    │     │  │     │        │     │     │        │           │  └─expr: expression.CastExpression
    │     │  │     │        │     │     │        │           │     ├─Column: table_x.pld
    │     │  │     │        │     │     │        │           │     └─ColDataType: JSON
    │     │  │     │        │     │     │        │           └─ColDataType: TEXT
    │     │  │     │        │     │     │        └─ColDataType: TEXT
    │     │  │     │        │     │     └─ColDataType: DOUBLE PRECISION
    │     │  │     │        │     └─rightExpression: expression.Function
    │     │  │     │        │        └─ExpressionList: fba.s_c_c, 'x'::CHARACTER VARYING, table_x.r_ts::DATE
    │     │  │     │        ├─Table: schema_z.f_b_a fba
    │     │  │     │        │  └─Alias:  fba
    │     │  │     │        ├─joins: statement.select.Join
    │     │  │     │        │  ├─Table: schema_z.t_b_a_n_i table_y
    │     │  │     │        │  │  └─Alias:  table_y
    │     │  │     │        │  └─onExpressions: expression.operators.relational.EqualsTo
    │     │  │     │        │     ├─Column: fba.b_a_i
    │     │  │     │        │     └─Column: table_y.f_b_a_id
    │     │  │     │        └─where: expression.operators.relational.EqualsTo
    │     │  │     │           ├─Column: table_y.t_ngn_id
    │     │  │     │           └─rightExpression: expression.Parenthesis
    │     │  │     │              └─expression: expression.CastExpression
    │     │  │     │                 ├─leftExpression: expression.Parenthesis
    │     │  │     │                 │  └─expression: expression.JsonExpression
    │     │  │     │                 │     └─expr: expression.CastExpression
    │     │  │     │                 │        ├─leftExpression: expression.JsonExpression
    │     │  │     │                 │        │  └─expr: expression.Parenthesis
    │     │  │     │                 │        │     └─expression: expression.JsonExpression
    │     │  │     │                 │        │        └─expr: expression.CastExpression
    │     │  │     │                 │        │           ├─leftExpression: expression.JsonExpression
    │     │  │     │                 │        │           │  └─expr: expression.CastExpression
    │     │  │     │                 │        │           │     ├─Column: table_x.pld
    │     │  │     │                 │        │           │     └─ColDataType: JSON
    │     │  │     │                 │        │           └─ColDataType: TEXT
    │     │  │     │                 │        └─ColDataType: TEXT
    │     │  │     │                 └─ColDataType: BIGINT
    │     │  │     ├─whenClauses: expression.WhenClause
    │     │  │     │  ├─whenExpression: expression.operators.relational.EqualsTo
    │     │  │     │  │  ├─leftExpression: expression.CastExpression
    │     │  │     │  │  │  ├─Column: table_x.b_e_t
    │     │  │     │  │  │  └─ColDataType: TEXT
    │     │  │     │  │  └─rightExpression: expression.CastExpression
    │     │  │     │  │     ├─StringValue: 'i_e'
    │     │  │     │  │     └─ColDataType: TEXT
    │     │  │     │  └─thenExpression: statement.select.ParenthesedSelect
    │     │  │     │     └─select: statement.select.PlainSelect
    │     │  │     │        ├─selectItems: statement.select.SelectItem
    │     │  │     │        │  └─expression: expression.operators.arithmetic.Division
    │     │  │     │        │     ├─leftExpression: expression.Parenthesis
    │     │  │     │        │     │  └─expression: expression.CastExpression
    │     │  │     │        │     │     ├─leftExpression: expression.Parenthesis
    │     │  │     │        │     │     │  └─expression: expression.JsonExpression
    │     │  │     │        │     │     │     └─expr: expression.CastExpression
    │     │  │     │        │     │     │        ├─leftExpression: expression.JsonExpression
    │     │  │     │        │     │     │        │  └─expr: expression.Parenthesis
    │     │  │     │        │     │     │        │     └─expression: expression.JsonExpression
    │     │  │     │        │     │     │        │        └─expr: expression.CastExpression
    │     │  │     │        │     │     │        │           ├─leftExpression: expression.JsonExpression
    │     │  │     │        │     │     │        │           │  └─expr: expression.CastExpression
    │     │  │     │        │     │     │        │           │     ├─Column: table_x.pld
    │     │  │     │        │     │     │        │           │     └─ColDataType: JSON
    │     │  │     │        │     │     │        │           └─ColDataType: TEXT
    │     │  │     │        │     │     │        └─ColDataType: TEXT
    │     │  │     │        │     │     └─ColDataType: DOUBLE PRECISION
    │     │  │     │        │     └─rightExpression: expression.Function
    │     │  │     │        │        └─ExpressionList: fba.s_c_c, 'x'::CHARACTER VARYING, table_x.r_ts::DATE
    │     │  │     │        ├─Table: schema_z.f_b_a fba
    │     │  │     │        │  └─Alias:  fba
    │     │  │     │        ├─joins: statement.select.Join
    │     │  │     │        │  ├─Table: schema_z.t_b_a_n_i table_y
    │     │  │     │        │  │  └─Alias:  table_y
    │     │  │     │        │  └─onExpressions: expression.operators.relational.EqualsTo
    │     │  │     │        │     ├─Column: fba.b_a_i
    │     │  │     │        │     └─Column: table_y.f_b_a_id
    │     │  │     │        └─where: expression.operators.relational.EqualsTo
    │     │  │     │           ├─Column: table_y.t_ngn_id
    │     │  │     │           └─rightExpression: expression.Parenthesis
    │     │  │     │              └─expression: expression.CastExpression
    │     │  │     │                 ├─leftExpression: expression.Parenthesis
    │     │  │     │                 │  └─expression: expression.JsonExpression
    │     │  │     │                 │     └─expr: expression.CastExpression
    │     │  │     │                 │        ├─leftExpression: expression.JsonExpression
    │     │  │     │                 │        │  └─expr: expression.Parenthesis
    │     │  │     │                 │        │     └─expression: expression.JsonExpression
    │     │  │     │                 │        │        └─expr: expression.CastExpression
    │     │  │     │                 │        │           ├─leftExpression: expression.JsonExpression
    │     │  │     │                 │        │           │  └─expr: expression.CastExpression
    │     │  │     │                 │        │           │     ├─Column: table_x.pld
    │     │  │     │                 │        │           │     └─ColDataType: JSON
    │     │  │     │                 │        │           └─ColDataType: TEXT
    │     │  │     │                 │        └─ColDataType: TEXT
    │     │  │     │                 └─ColDataType: BIGINT
    │     │  │     ├─whenClauses: expression.WhenClause
    │     │  │     │  ├─whenExpression: expression.operators.relational.EqualsTo
    │     │  │     │  │  ├─leftExpression: expression.CastExpression
    │     │  │     │  │  │  ├─Column: table_x.b_e_t
    │     │  │     │  │  │  └─ColDataType: TEXT
    │     │  │     │  │  └─rightExpression: expression.CastExpression
    │     │  │     │  │     ├─StringValue: 'i_e_2'
    │     │  │     │  │     └─ColDataType: TEXT
    │     │  │     │  └─thenExpression: statement.select.ParenthesedSelect
    │     │  │     │     └─select: statement.select.PlainSelect
    │     │  │     │        ├─selectItems: statement.select.SelectItem
    │     │  │     │        │  └─expression: expression.operators.arithmetic.Division
    │     │  │     │        │     ├─leftExpression: expression.Parenthesis
    │     │  │     │        │     │  └─expression: expression.CastExpression
    │     │  │     │        │     │     ├─leftExpression: expression.Parenthesis
    │     │  │     │        │     │     │  └─expression: expression.JsonExpression
    │     │  │     │        │     │     │     └─expr: expression.CastExpression
    │     │  │     │        │     │     │        ├─leftExpression: expression.JsonExpression
    │     │  │     │        │     │     │        │  └─expr: expression.Parenthesis
    │     │  │     │        │     │     │        │     └─expression: expression.JsonExpression
    │     │  │     │        │     │     │        │        └─expr: expression.CastExpression
    │     │  │     │        │     │     │        │           ├─leftExpression: expression.JsonExpression
    │     │  │     │        │     │     │        │           │  └─expr: expression.CastExpression
    │     │  │     │        │     │     │        │           │     ├─Column: table_x.pld
    │     │  │     │        │     │     │        │           │     └─ColDataType: JSON
    │     │  │     │        │     │     │        │           └─ColDataType: TEXT
    │     │  │     │        │     │     │        └─ColDataType: TEXT
    │     │  │     │        │     │     └─ColDataType: DOUBLE PRECISION
    │     │  │     │        │     └─rightExpression: expression.Function
    │     │  │     │        │        └─ExpressionList: fba.s_c_c, 'x'::CHARACTER VARYING, table_x.r_ts::DATE
    │     │  │     │        ├─Table: schema_z.f_b_a fba
    │     │  │     │        │  └─Alias:  fba
    │     │  │     │        ├─joins: statement.select.Join
    │     │  │     │        │  ├─Table: schema_z.t_b_a_n_i table_y
    │     │  │     │        │  │  └─Alias:  table_y
    │     │  │     │        │  └─onExpressions: expression.operators.relational.EqualsTo
    │     │  │     │        │     ├─Column: fba.b_a_i
    │     │  │     │        │     └─Column: table_y.f_b_a_id
    │     │  │     │        └─where: expression.operators.relational.EqualsTo
    │     │  │     │           ├─Column: table_y.t_ngn_id
    │     │  │     │           └─rightExpression: expression.Parenthesis
    │     │  │     │              └─expression: expression.CastExpression
    │     │  │     │                 ├─leftExpression: expression.Parenthesis
    │     │  │     │                 │  └─expression: expression.JsonExpression
    │     │  │     │                 │     └─expr: expression.CastExpression
    │     │  │     │                 │        ├─leftExpression: expression.JsonExpression
    │     │  │     │                 │        │  └─expr: expression.Parenthesis
    │     │  │     │                 │        │     └─expression: expression.JsonExpression
    │     │  │     │                 │        │        └─expr: expression.CastExpression
    │     │  │     │                 │        │           ├─leftExpression: expression.JsonExpression
    │     │  │     │                 │        │           │  └─expr: expression.CastExpression
    │     │  │     │                 │        │           │     ├─Column: table_x.pld
    │     │  │     │                 │        │           │     └─ColDataType: JSON
    │     │  │     │                 │        │           └─ColDataType: TEXT
    │     │  │     │                 │        └─ColDataType: TEXT
    │     │  │     │                 └─ColDataType: BIGINT
    │     │  │     ├─whenClauses: expression.WhenClause
    │     │  │     │  ├─whenExpression: expression.operators.relational.EqualsTo
    │     │  │     │  │  ├─leftExpression: expression.CastExpression
    │     │  │     │  │  │  ├─Column: table_x.b_e_t
    │     │  │     │  │  │  └─ColDataType: TEXT
    │     │  │     │  │  └─rightExpression: expression.CastExpression
    │     │  │     │  │     ├─StringValue: 'm_e'
    │     │  │     │  │     └─ColDataType: TEXT
    │     │  │     │  └─thenExpression: statement.select.ParenthesedSelect
    │     │  │     │     └─select: statement.select.PlainSelect
    │     │  │     │        └─selectItems: statement.select.SelectItem
    │     │  │     │           └─expression: expression.operators.arithmetic.Division
    │     │  │     │              ├─leftExpression: expression.Parenthesis
    │     │  │     │              │  └─expression: expression.CastExpression
    │     │  │     │              │     ├─leftExpression: expression.Parenthesis
    │     │  │     │              │     │  └─expression: expression.JsonExpression
    │     │  │     │              │     │     └─expr: expression.CastExpression
    │     │  │     │              │     │        ├─leftExpression: expression.JsonExpression
    │     │  │     │              │     │        │  └─expr: expression.Parenthesis
    │     │  │     │              │     │        │     └─expression: expression.JsonExpression
    │     │  │     │              │     │        │        └─expr: expression.CastExpression
    │     │  │     │              │     │        │           ├─leftExpression: expression.JsonExpression
    │     │  │     │              │     │        │           │  └─expr: expression.CastExpression
    │     │  │     │              │     │        │           │     ├─Column: table_x.pld
    │     │  │     │              │     │        │           │     └─ColDataType: JSON
    │     │  │     │              │     │        │           └─ColDataType: TEXT
    │     │  │     │              │     │        └─ColDataType: TEXT
    │     │  │     │              │     └─ColDataType: DOUBLE PRECISION
    │     │  │     │              └─rightExpression: expression.Function
    │     │  │     │                 └─ExpressionList: ((table_x.pld::JSON->'o'::TEXT)->>'dc'::TEXT)::CHARACTER VARYING, 'x'::CHARACTER VARYING, table_x.r_ts::DATE
    │     │  │     └─elseExpression: expression.CastExpression
    │     │  │        ├─NullValue: NULL
    │     │  │        └─ColDataType: DOUBLE PRECISION
    │     │  └─Alias:  AS gb_eq
    │     ├─Table: schema_z.baz
    │     ├─joins: statement.select.Join
    │     │  ├─Table: f_ctl.g_o_f_e_t_a_m table_y
    │     │  │  └─Alias:  table_y
    │     │  └─onExpressions: expression.operators.relational.LikeExpression
    │     │     ├─Column: table_x.p_e_m
    │     │     └─Column: table_y.f_e_m_p
    │     ├─joins: statement.select.Join
    │     │  ├─Table: f_ctl.g_o_c_a_t table_z
    │     │  │  └─Alias:  table_z
    │     │  └─onExpressions: expression.operators.relational.EqualsTo
    │     │     ├─Column: table_z.c_a_t_c
    │     │     └─Column: table_y.g_o_a_c
    │     └─where: expression.operators.relational.EqualsTo
    │        ├─Column: table_x.p_st
    │        └─StringValue: 'E'
    └─groupBy: statement.select.GroupByElement
       └─ExpressionList: 1, 2, 3