[BUG] JSQLParser Version 5.3: LIMIT with subquery fails: Was expecting: "BY"

[johaasen]

Failing SQL Feature:

• LIMIT with subquery cannot be parsed, although valid PostgreSQL
• Error discovered during upgrade from JSQLParser 5.2 to 5.3 within a spring-boot / spring-data application
• Online example
• Seems to recognize a limit-by expression, although plain-limit

Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token:<EOF>
    at line X, column Y.

Was expecting:

    "BY"

	at app//net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:53111)
	at app//net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:52929)
	at app//net.sf.jsqlparser.parser.CCJSqlParser.LimitBy(CCJSqlParser.java:16031)
	at app//net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:11805)
	at app//net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:10144)
	at app//net.sf.jsqlparser.parser.CCJSqlParser.SelectWithWithItems(CCJSqlParser.java:10108)
	at app//net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:503)
	at app//net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:413)

SQL Example:

-- self contained / anonymized example
WITH some_table AS (
    SELECT 1 AS some_column, 2 AS another_column
), another_table AS (
    SELECT 'some_value' AS condition_column
)
SELECT some_column
FROM some_table
ORDER BY some_column
-- this kind of limit expression is not supported
LIMIT (SELECT COUNT(*) FROM another_table WHERE condition_column = 'some_value')
-- real world-like rather more complex, should also be supported
-- LIMIT GREATEST(0, 100 - (SELECT COUNT(*) FROM another_table WHERE condition_column = 'some_value'))

Software Information:

• JSqlParser version 5.3 / 5.4
• PostgreSQL

[manticore-projects]

Thank you for reporting. Somewhere a LOOKAHEAD is too tight, because it works in general:

select *
from dual
LIMIT (SELECT 1)

/*
SQL Text
 └─Statements: statement.select.PlainSelect
    ├─selectItems: statement.select.SelectItem
    │  └─AllColumns: *
    ├─Table: dual
    └─limit: statement.select.Limit
       └─rowCount: statement.select.ParenthesedSelect
          └─select: statement.select.PlainSelect
             └─selectItems: statement.select.SelectItem
                └─LongValue: 1

*/