Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

[BUG] JSQLParser Version 5.3 : RDBMS opengauss: not support on duplicate key update nothing / where #2292

Closed
@zhangconan

Description

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

Failing SQL Feature:

  • not support on duplicate key update nothing / where
Image

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); }
}

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions

        AltStyle によって変換されたページ (->オリジナル) /