[BUG] 4.6 : PG15 : Encountered unexpected token: "\n\n\n"

[ilxqx]

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

Failing SQL Feature:

Default.

SQL Example:

• A very simple and ordinary SQL, just a bit ugly in terms of formatting:

  
  INSERT INTO mrqc_hospitalization_medical_record  ( id,

medical_record_number, created_at, updated_at ) VALUES ( ?,

?, ?, ? )

The code for calling the SQL parsing is as follows(I am using Groovy console in IDEA.):
```java
import net.sf.jsqlparser.parser.CCJSqlParserUtil

def sql = """
INSERT INTO mrqc_hospitalization_medical_record  ( id,

medical_record_number,
created_at,
updated_at )  VALUES  ( ?,

?,
?,
? )"""

CCJSqlParserUtil.parse(sql)

The following error will occur when running:

net.sf.jsqlparser.JSQLParserException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "\n\n\n" <ST_SEMICOLON>
    at line 2, column 55.

Was expecting one of:

    "ACTION"
    "ACTIVE"
    "ADD"
    "ADVANCE"
    "ADVISE"
    "AGAINST"
    "ALGORITHM"
    "ALL"
    "ALTER"
    "ANALYZE"
    "ANY"
    "APPLY"
    "ARCHIVE"
    "ARRAY"
    "ASC"
    "AT"
    "AUTHORIZATION"
    "AUTO"
    "BEGIN"
    "BINARY"
    "BIT"
    "BUFFERS"
    "BY"
    "BYTE"
    "BYTES"
    "CACHE"
    "CALL"
    "CASCADE"
    "CASE"
    "CASEWHEN"
    "CAST"
    "CHANGE"
    "CHANGES"
    "CHAR"
    "CHARACTER"
    "CHECKPOINT"
    "CLOSE"
    "COLLATE"
    "COLUMN"
    "COLUMNS"
    "COMMENT"
    "COMMIT"
    "CONFLICT"
    "CONNECT"
    "COSTS"
    "CREATE"
    "CS"
    "CYCLE"
    "DATABASE"
    "DDL"
    "DECLARE"
    "DEFAULT"
    "DEFERRABLE"
    "DELAYED"
    "DELETE"
    "DESC"
    "DESCRIBE"
    "DISABLE"
    "DISCONNECT"
    "DIV"
    "DML"
    "DO"
    "DOUBLE"
    "DROP"
    "DUMP"
    "DUPLICATE"
    "EMIT"
    "ENABLE"
    "END"
    "ESCAPE"
    "EXCLUDE"
    "EXEC"
    "EXECUTE"
    "EXPLAIN"
    "EXTENDED"
    "EXTRACT"
    "FALSE"
    "FILTER"
    "FIRST"
    "FLUSH"
    "FN"
    "FOLLOWING"
    "FORMAT"
    "FULLTEXT"
    "FUNCTION"
    "GLOBAL"
    "GRANT"
    "GROUP"
    "GROUPING"
    "GUARD"
    "HISTORY"
    "HOPPING"
    "IF"
    "IGNORE"
    "IIF"
    "IN"
    "INCLUDE"
    "INCREMENT"
    "INDEX"
    "INSERT"
    "INTERLEAVE"
    "INTERVAL"
    "ISNULL"
    "JSON"
    "KEEP"
    "KEY"
    "KEYS"
    "LAST"
    "LEADING"
    "LEFT"
    "LIMIT"
    "LINK"
    "LOCAL"
    "LOCKED"
    "LOG"
    "MATCH"
    "MATCHED"
    "MATERIALIZED"
    "MAXVALUE"
    "MERGE"
    "MINVALUE"
    "MODIFY"
    "MOVEMENT"
    "NEXT"
    "NO"
    "NOCACHE"
    "NOKEEP"
    "NOLOCK"
    "NOMAXVALUE"
    "NOMINVALUE"
    "NOORDER"
    "NOTHING"
    "NOVALIDATE"
    "NOWAIT"
    "NULLS"
    "OF"
    "OFF"
    "OFFSET"
    "ON"
    "OPEN"
    "OPTIMIZE"
    "ORDER"
    "OVER"
    "OVERLAPS"
    "PARALLEL"
    "PARENT"
    "PARTITION"
    "PATH"
    "PERCENT"
    "PLACING"
    "PRECEDING"
    "PRECISION"
    "PRIMARY"
    "PRIOR"
    "PROCEDURE"
    "PUBLIC"
    "PURGE"
    "QUERY"
    "QUICK"
    "QUIESCE"
    "RANGE"
    "READ"
    "RECYCLEBIN"
    "REFERENCES"
    "REFRESH"
    "REGISTER"
    "RENAME"
    "REPLACE"
    "RESET"
    "RESTART"
    "RESTRICT"
    "RESTRICTED"
    "RESUMABLE"
    "RESUME"
    "RIGHT"
    "RLIKE"
    "ROLLBACK"
    "ROW"
    "ROWS"
    "RR"
    "RS"
    "SAVEPOINT"
    "SCHEMA"
    "SEPARATOR"
    "SEQUENCE"
    "SESSION"
    "SET"
    "SETS"
    "SHOW"
    "SHUTDOWN"
    "SIBLINGS"
    "SIGNED"
    "SIMILAR"
    "SIZE"
    "SKIP"
    "SOME"
    "START"
    "STORED"
    "STRING"
    "SUSPEND"
    "SWITCH"
    "SYNONYM"
    "SYSTEM"
    "TABLE"
    "TABLES"
    "TABLESPACE"
    "TEMP"
    "TEMPORARY"
    "THEN"
    "TIMEOUT"
    "TIMESTAMPTZ"
    "TO"
    "TOP"
    "TRUE"
    "TRUNCATE"
    "TUMBLING"
    "TYPE"
    "UNLOGGED"
    "UNQIESCE"
    "UNSIGNED"
    "UPDATE"
    "UPSERT"
    "UR"
    "USER"
    "VALIDATE"
    "VALUE"
    "VALUES"
    "VERBOSE"
    "VIEW"
    "WAIT"
    "WITHIN"
    "WITHOUT"
    "WORK"
    "XML"
    "XMLAGG"
    "XMLTEXT"
    "YAML"
    "YES"
    "ZONE"
    <K_DATETIMELITERAL>
    <K_DATE_LITERAL>
    <K_ISOLATION>
    <K_STRING_FUNCTION_NAME>
    <K_TIME_KEY_EXPR>
    <S_IDENTIFIER>
    <S_QUOTED_IDENTIFIER>

    at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:263)
    at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:81)
    at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:47)
    at org.codehaus.groovy.vmplugin.v8.IndyInterface.fromCache(IndyInterface.java:321)
    at ideaGroovyConsole.run(ideaGroovyConsole.groovy:16)
    at groovy.lang.GroovyShell.runScriptOrMainOrTestOrRunnable(GroovyShell.java:287)
    at groovy.lang.GroovyShell.run(GroovyShell.java:393)
    at groovy.lang.GroovyShell.run(GroovyShell.java:372)
    at org.codehaus.groovy.vmplugin.v8.IndyInterface.fromCache(IndyInterface.java:321)
    at console.run(console.groovy:11)
    at groovy.ui.GroovyMain.processReader(GroovyMain.java:628)
    at groovy.ui.GroovyMain.processFiles(GroovyMain.java:549)
    at groovy.ui.GroovyMain.run(GroovyMain.java:387)
    at groovy.ui.GroovyMain.access$1400(GroovyMain.java:67)
    at groovy.ui.GroovyMain$GroovyCommand.process(GroovyMain.java:313)
    at groovy.ui.GroovyMain.processArgs(GroovyMain.java:141)
    at groovy.ui.GroovyMain.main(GroovyMain.java:114)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.codehaus.groovy.tools.GroovyStarter.rootLoader(GroovyStarter.java:115)
    at org.codehaus.groovy.tools.GroovyStarter.main(GroovyStarter.java:37)
Caused by: java.util.concurrent.ExecutionException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "\n\n\n" <ST_SEMICOLON>
    at line 2, column 55.

Software Information:

• JSqlParser version: 4.6
• Database: PostgreSQL15 When I downgraded the version of JSqlParser to 4.5, the SQL parsing above was normal and did not report any error, indicating that this problem does not exist in version 4.5.

At the same time, if I process it like this before parsing SQL: sql.replaceAll("\\s+", " ").trim(), the parsing will be completed normally.

Can't version 4.6 handle extra spaces and line breaks in SQL correctly?

[manticore-projects]

Greetings.

2 empty lines (e.g. \n\n\n) terminate a statement and I do not see any good reason why SQL statements should contains 2 empty lines.

Remove the 2 empty lines and it will parse well.

[ilxqx]

First of all, thank you for your reply. There are multiple blank lines here, which is actually SQL automatically generated by a framework called MybatisPlus. There may be additional blank lines when the SQL is automatically concatenated internally.

Yes, removing two blank lines can parse it normally. But why could version 4.5 parse it normally? Was it intentional or was the logic not rigorous and fixed in version 4.6?

[manticore-projects]

Greetings.

I have lodged a case about this at MyBatisPlus already, they should fix their generator. The change was indeed introduced in JSQLParser 4.6 (together with other possible statement terminators like \ or GO).

[manticore-projects]

Here is this My Batis Issue: https://github.com/baomidou/mybatis-plus/issues Maybe you can intervene there since its all in Chinese.

[ilxqx]

Okay, thank you.