GV

GeezerVison

The World Wide Web, restored to 800 pixels of glory.

Home|About|Gaming cabinet|Archives|How it works|Starter sites

On this site

  • Sign in
  • ActionsCI/CD and automation
  • CodespacesCloud development environments
  • All Features
  • Documentation
  • Training
  • Certifications
  • Build with Copilot
  • Customize Copilot
  • Integrations
  • Trending repos
  • GitHub Sponsors
  • Developer articles
  • Enterprises

This view

  • Original page
  • New address

GeezerVison is showing github.com — [BUG] JSQLParser Version : 4.0 : ON condition not parsed correctly when modifying SQL with natural join · Issue #2016 · JSQLParser/JSqlParser

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

New issue
Copy link
New issue
Copy link
Closed as not planned
Closed as not planned
[BUG] JSQLParser Version : 4.0 : ON condition not parsed correctly when modifying SQL with natural join#2016
Copy link

Description

@Createsequence
Createsequence
opened on Jun 7, 2024
Issue body actions

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.

Activity

  1. manticore-projects commented on Jun 7, 2024

    @manticore-projects
    manticore-projects
    on Jun 7, 2024
    Contributor
    More actions

    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.

  2. manticore-projects
    closed this as not plannedon Jun 7, 2024
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
      You are visitor 01402503 — GeezerVison does not store the pages you look up.
      Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.