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

[zhangconan]

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

Failing SQL Feature:

• not support on duplicate key do nothing / where

SQL Example:

• Simplified Insert Example, focusing on the failing feature

  INSERT INTO users (id, name) VALUES (1, 'Alice') ON DUPLICATE KEY UPDATE  do nothing

Software Information:

• JSqlParser 5.3
• Database opengauss

Tips:

I hope to support do nothing and where syntax。Now I made some modifications to support the no thing 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); }
}

[zhangconan]

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

[zhangconan]

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  do nothing";
        statement = CCJSqlParserUtil.parse(sql);
        assert sql.equalsIgnoreCase(statement.toString());
    }