Skip to content

Postgresql insert parser error #955

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
slieer opened this issue Mar 5, 2020 · 5 comments · Fixed by #1745
Closed

Postgresql insert parser error #955

slieer opened this issue Mar 5, 2020 · 5 comments · Fixed by #1745

Comments

@slieer
Copy link

slieer commented Mar 5, 2020

Describe the bug
A clear and concise description of what the bug is.

To Reproduce
Steps to reproduce the behavior:

  1. Example SQL
    INSERT INTO tableName (id,xxx0,xxx1,xxx2,is_deleted,create_time,update_time) VALUES (?, ?, ?, ?, ?, ?, ?) on conflict(xxx0, xxx1) do update set xxx1=?, update_time=?

  2. Parsing this SQL using JSqlParser with this statements

  3. Exception
    Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "conflict" <S_IDENTIFIER>
    at line 3, column 12.

Was expecting:

"DUPLICATE"

at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:22439)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:22286)
at net.sf.jsqlparser.parser.CCJSqlParser.Insert(CCJSqlParser.java:1792)
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:137)
at net.sf.jsqlparser.parser.CCJSqlParser.Statements(CCJSqlParser.java:479)
at DqmTest.parseStatements(DqmTest.java:51)
... 40 more

Caused by:
net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "conflict" <S_IDENTIFIER>
at line 3, column 12.

Was expecting:

"DUPLICATE"

at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:22439)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:22286)
at net.sf.jsqlparser.parser.CCJSqlParser.Insert(CCJSqlParser.java:1792)
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:137)
at net.sf.jsqlparser.parser.CCJSqlParser.Statements(CCJSqlParser.java:479)
at DqmTest.parseStatements(DqmTest.java:51)

Expected behavior
A clear and concise description of what you expected to happen.

System

  • Database you are using
  • Java Version
    java 11
  • JSqlParser version
    3.1
@WenSteven
Copy link

It also appears in v4.1

@manticore-projects
Copy link
Contributor

Greetings!

I do not think that the PostgreSQL specific ON CONFLICT ... DO ... clause is supported.
You would need to amend the INSERT production and send a PR please.

@manticore-projects
Copy link
Contributor

Technically this is an UPSERT, which is actually supported by JSQLParser: http://217.160.215.75:8080/jsqlformatter/_static/railroad_diagram.xhtml#Upsert

However, PostgreSQL does not seem to support UPSERT explicitly, but having these INSERT ... ON CONFLICT ... syntax instead, which is not supported by JSQLParser. Please implement and send a PR.

@manticore-projects
Copy link
Contributor

INSERT ... ON DUPLICATE ... is supported as well: http://217.160.215.75:8080/jsqlformatter/_static/railroad_diagram.xhtml#Insert

So it all boils down to ammending the INSERT production and to allow the ON CONFLICT instead.

@WenSteven
Copy link

INSERT ... ON DUPLICATE ... is supported as well: http://217.160.215.75:8080/jsqlformatter/_static/railroad_diagram.xhtml#Insert

So it all boils down to ammending the INSERT production and to allow the ON CONFLICT instead.

Yeap,but the code not designed with design-patterns,I think parse sql with different db dialect should be rather than knead the code together

@manticore-projects manticore-projects mentioned this issue Nov 22, 2022
Closed
manticore-projects added a commit to manticore-projects/JSqlParser that referenced this issue Mar 18, 2023
@manticore-projects
feat: ConflictTarget allows multiple IndexColumnNames
043b71a 
fixes JSQLParser#1749
fixes JSQLParser#1633
fixes JSQLParser#955
@manticore-projects manticore-projects mentioned this issue Mar 20, 2023
Merged
@wumpz wumpz closed this as completed in 31ef1aa Mar 21, 2023
wumpz pushed a commit that referenced this issue Apr 27, 2023
@manticore-projects @zaza
Refactor Parenthesed SelectBody and FromItem (#1754)
a312dcd 
* Fixes #1684: Support CREATE MATERIALIZED VIEW with AUTO REFRESH

Support parsing create view statements in Redshift with AUTO REFRESH
option.

* Reduce cyclomatic complexity in CreateView.toString

Extract adding the force option into a dedicated method resulting in the
cyclomatic complexity reduction of the CreateView.toString method.

* Enhanced Keywords

Add Keywords and document, which keywords are allowed for what purpose

* Fix incorrect tests

* Define Reserved Keywords explicitly
Derive All Keywords from Grammar directly
Generate production for Object Names (semi-) automatically
Add parametrized Keyword Tests

* Fix test resources

* Adjust Gradle to JUnit 5

Parallel Test execution
Gradle Caching
Explicitly request for latest JavaCC 7.0.10

* Do not mark SpeedTest for concurrent execution

* Remove unused imports

* Adjust Gradle to JUnit 5

Parallel Test execution
Gradle Caching
Explicitly request for latest JavaCC 7.0.10

* Do not mark SpeedTest for concurrent execution

* Remove unused imports

* Sphinx Documentation

Update the MANTICORE Sphinx Theme, but ignore it in GIT
Add the content to the Sphinx sites
Add a Gradle function to derive Stable and Snapshot version from GIT Tags
Add a Gradle GIT change task
Add a Gradle sphinx task
Add a special Test case for illustrating the use of JSQLParser

* doc: request for `Conventional Commit` messages

* feat: make important Classes Serializable

Implement Serializable for persisting via ObjectOutputStream

* chore: Make Serializable

* doc: Better integration of the RR diagrams

- apply neutral Sphinx theme
- insert the RR diagrams into the sphinx sources
- better documentation on Gradle dependencies
- link GitHub repository

* Merge

* feat: Oracle Alternative Quoting

- add support for Oracle Alternative Quoting e.g. `q'(...)'`
- fixes #1718
- add a Logo and FavIcon to the Website
- document recent changes on Quoting/Escaping
- add an example on building SQL from Java
- rework the README.md, promote the Website
- add Spotless Formatter, using Google Java Style (with Tab=4 Spaces)

* style: Appease PMD/Codacy

* doc: fix the issue template

- fix the issue template
- fix the -SNAPSHOT version number

* Update issue templates

* Update issue templates

* feat: Support more Statement Separators

- `GO`
- Slash `/`
- Two empty lines

* feat: FETCH uses EXPRESSION

- `FETCH` uses `EXPRESSION` instead of SimpleJDBCParameter only
- Visit/Accept `FETCH` `EXPRESSION` instead of `append` to String
- Visit/Accept `OFFSET` `EXPRESSION` instead of `append` to String
- Gradle: remove obsolete/incompatible `jvmArgs` from Test()

* style: apply Spotless

* test: commit missing test

* fix: JSon Operator can use Simple Function

Supports `Function() ->> Literal` (although `Function()` would not allow Nested Expression Parameters)

fixes #1571

* style: Reformat changed files and headers

* style: Remove unused variable

* feat: Add support for Hangul "\uAC00"-"\uD7A3"

fixes #1747

* style: expose `SetStatement` key-value list

fixes #1746

* style: Appease PMD/Codacy

* feat: `ConflictTarget` allows multiple `IndexColumnNames`

fixes #1749
fixes #1633
fixes #955

* doc: fix reference in the Java Doc

* build: better Upload Groovy Task

* feat: ParenthesedSelectBody and ParenthesedFromItem

- First properly working version
- Work in progress, 13 tests failing

* feat: ParenthesedSelectBody and ParenthesedFromItem

- delete unneeded ParenthesedJoin
- rename ParenthesisFromItem into ParenthesedFromItem

* feat: ParenthesedSelectBody and ParenthesedFromItem

- fix `NULLS FIRST` and `NULLS LAST`

* feat: ParenthesedSelectBody and ParenthesedFromItem

- fix Oracle Hints

* feat: ParenthesedSelectBody and ParenthesedFromItem

- parse `SetOperation` only after a (first plain) SelectBody has found, this fixes the performance issue
- one more special Oracle Test succeeds
- 5 remaining test failures

* feat: ParenthesedSelectBody and ParenthesedFromItem

- extract `OrderByElements` into `SelectBody`
- one more special Oracle Test succeeds
- all tests succeed

* style: Appease PMD/Codacy

* style: Appease PMD/Codacy

* feat: Refactor SelectBody implementations

- `SelectBody` implements `FromItem`
- get rid of `SubSelect` and `SpecialSubSelect`
- `Merge` can use `FromItem` instead of `SubSelect` or `Table`
- `LateralSubSelect` extends `ParenthesedSelectBody` directly
- Simplify the `Select` statement, although it is still redundant since     `SelectBody` also could implement `Statement` directly
- `WithItem` can use `SelectBody` directly, which allows for nested `WithItems`

BREAKING-CHANGE: Lots of redundant methods and intermediate removed

* feat: Refactor SelectBody implementations

- `SelectBody` implements `Statement` and so makes `Select` redundant
- get rid of `ValuesList`
- refactor `ValuesStatement` into `Values` which just implements `SelectBody` (and becomes a `Statement` and a `FromItem`), move to `select` package

BREAKING-CHANGE: Lots of redundant methods and intermediate removed

* style: Code cleanup

- remove 3 unused/obsolete productions
- appease PMD/Codacy

* feat: Merge `SelectBody` into `Select` Statement

- former `SelectBody` implements `Statement` and so becomes `Select`
- this reduces the AST by 1 hierarchy level

* style: Remove unused import

* test: @disabled invalid Test

* style: Appease PMD/Codacy

* test: Add a SubSelect Parsing Test

---------

Co-authored-by: zaza <tzarna@gmail.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Assorted Fixes #7 manticore-projects/JSqlParser
3 participants
@slieer @WenSteven @manticore-projects