Repository navigation
[BUG] JSQLParser Version 5.3 : RDBMS opengauss: not support on duplicate key update nothing / where #2292
Copy link
Copy link
Closed
Description
Always check against the Latest SNAPSHOT of JSQLParser and the Syntax Diagram
Failing SQL Feature:
- not support on duplicate key update nothing / where
SQL Example:
- Simplified Insert Example, focusing on the failing feature
INSERT INTO users (id, name) VALUES (1, 'Alice') ON DUPLICATE KEY UPDATE nothing
Software Information:
- JSqlParser 5.3
- Database opengauss
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:
public class InsertDuplicateAction implements Serializable {
ConflictActionType conflictActionType;
Expression whereExpression;
private List<UpdateSet> updateSets;
public InsertDuplicateAction(ConflictActionType conflictActionType) {
this.conflictActionType = Objects.requireNonNull(conflictActionType, "The Conflict Action Type is mandatory and must not be Null.");
}
public List<UpdateSet> getUpdateSets() {
return updateSets;
}
public void setUpdateSets(List<UpdateSet> updateSets) {
this.updateSets = updateSets;
}
public InsertDuplicateAction withUpdateSets(List<UpdateSet> updateSets) {
this.setUpdateSets(updateSets);
return this;
}
public ConflictActionType getConflictActionType() {
return conflictActionType;
}
public void setConflictActionType(ConflictActionType conflictActionType) {
this.conflictActionType = Objects.requireNonNull(conflictActionType, "The Conflict Action Type is mandatory and must not be Null.");
}
public InsertDuplicateAction withConflictActionType(ConflictActionType conflictActionType) {
setConflictActionType(conflictActionType);
return this;
}
public InsertDuplicateAction addUpdateSet(Column column, Expression expression) {
return this.addUpdateSet(new UpdateSet());
}
public InsertDuplicateAction addUpdateSet(UpdateSet updateSet) {
if (updateSets == null) {
updateSets = new ArrayList<>();
}
this.updateSets.add(updateSet);
return this;
}
public InsertDuplicateAction withUpdateSets(Collection<UpdateSet> updateSets) {
this.setUpdateSets(new ArrayList<>(updateSets));
return this;
}
public Expression getWhereExpression() {
return whereExpression;
}
public void setWhereExpression(Expression whereExpression) {
this.whereExpression = whereExpression;
}
public InsertDuplicateAction withWhereExpression(Expression whereExpression) {
setWhereExpression(whereExpression);
return this;
}
@SuppressWarnings("PMD.SwitchStmtsShouldHaveDefault")
public StringBuilder appendTo(StringBuilder builder) {
switch (conflictActionType) {
case DO_NOTHING:
builder.append(" DO NOTHING");
break;
default:
UpdateSet.appendUpdateSetsTo(builder, updateSets);
if (whereExpression != null) {
builder.append(" WHERE ").append(whereExpression);
}
break;
}
return builder;
}
@Override
public String toString() {
return appendTo(new StringBuilder()).toString();
}
}Modify Insert class,add InsertDuplicateAction field related content。
private InsertDuplicateAction duplicateAction;
public List<UpdateSet> getDuplicateUpdateSets() {
if (duplicateAction != null) {
return duplicateAction.getUpdateSets();
}
return duplicateUpdateSets;
}
public Insert withDuplicateUpdateSets(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;
}
@Override
@SuppressWarnings({"PMD.CyclomaticComplexity", "PMD.NPathComplexity"})
public String toString() {
StringBuilder sql = new StringBuilder();
if (withItemsList != null && !withItemsList.isEmpty()) {
sql.append("WITH ");
for (Iterator<WithItem<?>> iter = withItemsList.iterator(); iter.hasNext(); ) {
WithItem<?> withItem = iter.next();
sql.append(withItem);
if (iter.hasNext()) {
sql.append(",");
}
sql.append(" ");
}
}
sql.append("INSERT ");
if (oracleHint != null) {
sql.append(oracleHint).append(" ");
}
if (modifierPriority != null) {
sql.append(modifierPriority.name()).append(" ");
}
if (modifierIgnore) {
sql.append("IGNORE ");
}
if (overwrite) {
sql.append("OVERWRITE ");
} else {
sql.append("INTO ");
}
if (tableKeyword) {
sql.append("TABLE ");
}
sql.append(table).append(" ");
if (onlyDefaultValues) {
sql.append("DEFAULT VALUES");
}
if (columns != null) {
sql.append("(");
for (int i = 0; i < columns.size(); i++) {
if (i > 0) {
sql.append(", ");
}
// only plain names, but not fully qualified names allowed
sql.append(columns.get(i).getColumnName());
}
sql.append(") ");
}
if (overriding) {
sql.append("OVERRIDING SYSTEM VALUE ");
}
if (partitions != null) {
sql.append(" PARTITION (");
Partition.appendPartitionsTo(sql, partitions);
sql.append(") ");
}
if (outputClause != null) {
sql.append(outputClause);
}
if (select != null) {
sql.append(select);
}
if (setUpdateSets != null && !setUpdateSets.isEmpty()) {
sql.append("SET ");
sql = UpdateSet.appendUpdateSetsTo(sql, setUpdateSets);
}
if (duplicateAction != null) {
sql.append(" ON DUPLICATE KEY UPDATE ");
duplicateAction.appendTo(sql);
}
if (conflictAction != null) {
sql.append(" ON CONFLICT");
if (conflictTarget != null) {
conflictTarget.appendTo(sql);
}
conflictAction.appendTo(sql);
}
if (returningClause != null) {
returningClause.appendTo(sql);
}
return sql.toString();
}
public InsertDuplicateAction getDuplicateAction() {
return duplicateAction;
}
public void setDuplicateAction(InsertDuplicateAction duplicateAction) {
this.duplicateAction = duplicateAction;
}And modify JSqlParserCC.jjt, content is as follows:
Insert Insert():
{
....
InsertDuplicateAction duplicateAction = null;
}
{
[ LOOKAHEAD(2) <K_ON> <K_DUPLICATE> <K_KEY> <K_UPDATE>
duplicateAction = InsertDuplicateAction() { insert.setDuplicateAction(duplicateAction); }
]
}
InsertDuplicateAction InsertDuplicateAction():
{
InsertDuplicateAction duplicateAction;
Expression whereExpression = null;
List<UpdateSet> updateSets;
}
{
(
LOOKAHEAD(2) (
<K_DO> <K_NOTHING> { duplicateAction = new InsertDuplicateAction( ConflictActionType.DO_NOTHING ); }
)
|
(
{ duplicateAction = new InsertDuplicateAction( ConflictActionType.DO_UPDATE ); }
updateSets = UpdateSets() { duplicateAction.setUpdateSets(updateSets); }
[ whereExpression = WhereClause() ]
)
)
{ return duplicateAction
.withWhereExpression(whereExpression); }
}
Reactions are currently unavailable
Metadata
Metadata
Assignees
Labels
No labels
Activity
zhangconan commentedon Aug 7, 2025
Similarly, the same changes are made in Upsert。
Upsert class.
JSqlParserCC.jjt
zhangconan commentedon Aug 7, 2025
Add Test.
manticore-projects commentedon Aug 24, 2025
Greetings.
Please submit a PR.
zhangconan commentedon Aug 27, 2025
OK! I will submit a PR.
[-][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[/+][-][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[/+]zhangconan commentedon Aug 28, 2025
I have submitted a PR.
support opengauss "on duplicate key update nothing" grammar
manticore-projects commentedon Sep 1, 2025
Thank you very much and kudos!