[BUG] JSQLParser Version : 4.0 : ON condition not parsed correctly when modifying SQL with natural join

[Createsequence]

Description

Hi, I am trying to dynamically add condition statements to SQL using JSqlparser. However, it seems there is an issue when the SQL contains a natural join:

public static void main(String[] args) throws JSQLParserException {
    String sql = "SELECT * FROM A a, B b where a.id = b.id";
    Statement statement = CCJSqlParserUtil.parse(sql, null);
    PlainSelect plainSelect = (PlainSelect) ((Select) statement).getSelectBody();
    Join join = CollUtil.getFirst(plainSelect.getJoins());
    // append a condition "a.id = '100'"
    join.setOnExpression(new EqualsTo(new Column("a.id"), new StringValue("100")));

    // = "SELECT * FROM A a, B b WHERE a.id = b.id"
    System.out.println(statement.toString());
}

I am not sure how JSqlparser handles this situation, but SELECT * FROM A a, B b WHERE a.id = b.id is clearly not the expected result.

Analysis

I tried debugging to find the root cause of this issue. I noticed that in the above code, the query of table B is parsed into a net.sf.jsqlparser.statement.select.Join object, and at this point, Join.simple is true.

After setting the ON condition with join.setOnExpression(new EqualsTo(new Column("a.id"), new StringValue("100"))); and attempting to convert it back to SQL, the ON condition is not correctly parsed into SQL because Join.simple is true:

@Override
public String toString() {
    if (isSimple() && isOuter()) {
        return "OUTER " + rightItem;
    } else if (isSimple()) {
        return "" + rightItem;
    } else {

        // isSimple() returns true, so this branch is ignored

        String type = "";

        if (isRight()) {
            type += "RIGHT ";
        } else if (isNatural()) {
            type += "NATURAL ";
        } else if (isFull()) {
            type += "FULL ";
        } else if (isLeft()) {
            type += "LEFT ";
        } else if (isCross()) {
            type += "CROSS ";
        }

        if (isOuter()) {
            type += "OUTER ";
        } else if (isInner()) {
            type += "INNER ";
        } else if (isSemi()) {
            type += "SEMI ";
        }

        if (isStraight()) {
            type = "STRAIGHT_JOIN ";
        } else if (isApply()) {
            type += "APPLY ";
        } else {
            type += "JOIN ";
        }

        return type + rightItem + ((joinWindow != null) ? " WITHIN " + joinWindow : "")
                + ((onExpression != null) ? " ON " + onExpression + "" : "")
                + PlainSelect.getFormatedList(usingColumns, "USING", true, true);
    }
}

Fix

Calling join.setSimple(false) after setOnExpression fixes the issue:

public static void main(String[] args) throws JSQLParserException {
    String sql = "SELECT * FROM A a, B b where a.id = b.id";
    Statement statement = CCJSqlParserUtil.parse(sql, null);
    PlainSelect plainSelect = (PlainSelect) ((Select) statement).getSelectBody();
    Join join = CollUtil.getFirst(plainSelect.getJoins());
    join.setOnExpression(new EqualsTo(new Column("a.id"), new StringValue("100")));
    join.setSimple(false); // set Join.simple to false

    // SELECT * FROM A a JOIN B b ON a.id = '100' WHERE a.id = b.id
    System.out.println(statement.toString());
}

In fact, if the ON condition is not null, isSimple should return false. Should we make the following changes to fix this issue?

• When calling the setOnExpression method, if the parameter is not null, set simple to false.
• When calling the isSimple method, check if onExpression is null.

[manticore-projects]

Greetings.

You describe exactly the expected behavior:

1. you parse a statement with a simple join
2. you add an ON condition without setting simple=false

Nothing to do here.