Repository navigation
[BUG] JSQLParser Version 5.3 : RDBMS opengauss: not support on duplicate key update nothing / where #2292
Copy link
Copy link
Closed
Description
Activity
Similarly, the same changes are made in Upsert。
Upsert class.private InsertDuplicateAction duplicateAction; public List<UpdateSet> getDuplicateUpdateSets() { if (duplicateAction != null) { return duplicateAction.getUpdateSets(); } return duplicateUpdateSets; } public Upsert setDuplicateUpdateSets(List<UpdateSet> duplicateUpdateSets) { if (duplicateAction != null) { duplicateAction.setConflictActionType(ConflictActionType.DO_UPDATE); duplicateAction.setUpdateSets(duplicateUpdateSets); } else { duplicateAction = new InsertDuplicateAction(ConflictActionType.DO_UPDATE); duplicateAction.setUpdateSets(duplicateUpdateSets); } return this; } public String toString() { StringBuilder sb = new StringBuilder(); switch (upsertType) { case REPLACE: case REPLACE_SET: sb.append("REPLACE "); break; case INSERT_OR_ABORT: sb.append("INSERT OR ABORT "); break; case INSERT_OR_FAIL: sb.append("INSERT OR FAIL "); break; case INSERT_OR_IGNORE: sb.append("INSERT OR IGNORE "); break; case INSERT_OR_REPLACE: sb.append("INSERT OR REPLACE "); break; case INSERT_OR_ROLLBACK: sb.append("INSERT OR ROLLBACK "); break; case UPSERT: default: sb.append("UPSERT "); } if (isUsingInto) { sb.append("INTO "); } sb.append(table).append(" "); if (updateSets != null) { sb.append("SET "); UpdateSet.appendUpdateSetsTo(sb, updateSets); } else { if (columns != null) { sb.append(columns).append(" "); } if (select != null) { sb.append(select); } } if (duplicateAction != null) { sb.append(" ON DUPLICATE KEY UPDATE "); duplicateAction.appendTo(sb); } return sb.toString(); } public InsertDuplicateAction getDuplicateAction() { return duplicateAction; } public void setDuplicateAction(InsertDuplicateAction duplicateAction) { this.duplicateAction = duplicateAction; }
JSqlParserCC.jjt
Upsert Upsert(): { ..... InsertDuplicateAction duplicateAction = null; } { [ <K_ON> <K_DUPLICATE> <K_KEY> <K_UPDATE> duplicateAction = InsertDuplicateAction() { upsert.setDuplicateAction(duplicateAction); } ] }Add Test.
@Test public void test009() { String sql = "INSERT /*+ plan_hint(USE_MERGE) */ INTO teablea (id, start_time, url) select id, start_time, url from teableb ON DUPLICATE KEY UPDATE url = values(url) where id = EXCLUDED.id"; Statement statement = CCJSqlParserUtil.parse(sql); assert sql.equalsIgnoreCase(statement.toString()); sql = "INSERT INTO t_score (student_id, course_id, score) VALUES (1, 101, 95) ON DUPLICATE KEY UPDATE score = GREATEST(score, VALUES(score))"; statement = CCJSqlParserUtil.parse(sql); assert sql.equalsIgnoreCase(statement.toString()); sql = "INSERT INTO t_score (student_id, course_id, score) VALUES (1, 101, 95) ON CONFLICT ON CONSTRAINT uniq_student_course DO UPDATE SET score = EXCLUDED.score WHERE t_score.score < EXCLUDED.score"; statement = CCJSqlParserUtil.parse(sql); assert sql.equalsIgnoreCase(statement.toString()); sql = "INSERT INTO users (id, name) VALUES (1, 'Alice') ON DUPLICATE KEY UPDATE name = EXCLUDED.name WHERE users.name IS DISTINCT FROM EXCLUDED.name"; statement = CCJSqlParserUtil.parse(sql); assert sql.equalsIgnoreCase(statement.toString()); sql = "INSERT INTO users (id, name) VALUES (1, 'Alice') ON DUPLICATE KEY UPDATE nothing"; statement = CCJSqlParserUtil.parse(sql); assert sql.equalsIgnoreCase(statement.toString()); }
manticore-projects commented
on Aug 24, 2025 ContributorMore actionsGreetings.
Please submit a PR.
Greetings.
Please submit a PR.
OK! I will submit a PR.
- changed the title
[-][BUG] JSQLParser Version 5.3 : RDBMS opengauss: not support on duplicate key do nothing / where[/-][+][BUG] JSQLParser Version 5.3 : RDBMS opengauss: not support on duplicate key nothing / where[/+]on Aug 28, 2025 - changed the title
[-][BUG] JSQLParser Version 5.3 : RDBMS opengauss: not support on duplicate key nothing / where[/-][+][BUG] JSQLParser Version 5.3 : RDBMS opengauss: not support on duplicate key update nothing / where[/+]on Aug 28, 2025 Greetings.
Please submit a PR.
I have submitted a PR.
support opengauss "on duplicate key update nothing" grammarThank you very much and kudos!
Metadata
Metadata
Assignees
Labels
No labels
Always check against the Latest SNAPSHOT of JSQLParser and the Syntax Diagram
Failing SQL Feature:
SQL Example:
Software Information:
Tips:
I hope to support nothing and where syntax。Now I made some modifications to support the nothing and where syntax。
New Add InsertDuplicateAction class,the content is as follows:
Modify Insert class,add InsertDuplicateAction field related content。
And modify JSqlParserCC.jjt, content is as follows: