JSqlParser: [BUG] JSQLParser 4.6 : PostgreSQL : CPU burn when parsing certain queries
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
About this issue
- Original URL
- State: closed
- Created a year ago
- Comments: 29 (19 by maintainers)
Commits related to this issue
- fix: Complex Parsing Approach - optionally provide a global Executor, instead spawning one for each parse - run into Complex Parsing only, when Complex Parsing was allowed - provide a Logger - fixes ... — committed to manticore-projects/JSqlParser by manticore-projects a year ago
- feat: chaining JSON Expressions - supports chains like '{"obj":{"field": "value"}}'::JSON -> 'obj'::TEXT ->> 'field'::TEXT - fixes #1792 — committed to manticore-projects/JSqlParser by manticore-projects a year ago
- feat: functions blocks, parenthesed JSON Expressions - fixes #1792, the very complex example - fixes #1477 — committed to manticore-projects/JSqlParser by manticore-projects a year ago
- feat: functions blocks, parenthesed JSON Expressions - fixes #1792, the very complex example - fixes #1477 - cosmetics — committed to manticore-projects/JSqlParser by manticore-projects a year ago
- Assorted Fixes #8 (#1807) * Fixes #1684: Support CREATE MATERIALIZED VIEW with AUTO REFRESH Support parsing create view statements in Redshift with AUTO REFRESH option. * Reduce cyclomatic com... — committed to JSQLParser/JSqlParser by manticore-projects a year ago
- Backslash Quotation of Single-Quote `'\\''` (#1813) * Fixes #1684: Support CREATE MATERIALIZED VIEW with AUTO REFRESH Support parsing create view statements in Redshift with AUTO REFRESH option. ... — committed to JSQLParser/JSqlParser by manticore-projects a year ago
Now you can also pretty print and format your statement.
Or print the AST:
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.
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.