Skip to content

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

Description

@zhangconan

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

Activity

  1. zhangconan commented on Aug 7, 2025

    @zhangconan
    ContributorAuthor

    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); }
        ]
    }
    
  2. zhangconan commented on Aug 7, 2025

    @zhangconan
    ContributorAuthor

    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());
        }
  3. manticore-projects commented on Aug 24, 2025

    @manticore-projects
    Contributor

    Greetings.

    Please submit a PR.

  4. zhangconan commented on Aug 27, 2025

    @zhangconan
    ContributorAuthor

    Greetings.

    Please submit a PR.

    OK! I will submit a PR.

  5. 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
  6. 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
  7. zhangconan commented on Aug 28, 2025

    @zhangconan
    ContributorAuthor

    Greetings.

    Please submit a PR.

    I have submitted a PR.
    support opengauss "on duplicate key update nothing" grammar

  8. manticore-projects commented on Sep 1, 2025

    @manticore-projects
    Contributor

    Thank you very much and kudos!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

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