Parser is dropping Inner Joins when constructing the SQL from AST

[kasvith]

Problem

A SQL statement with an Inner Join is given to the parser, once converted to AST and getting the result back as a SQL string parser loose the inner join altering original SQL statement.

I know INNER JOIN is represented as JOIN in many dialects. But since parser alters original query when converted back to AST is a problem as it is loosing information.

Example:

fn main() {
    let sql_select = "SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID";

    let dialect = MySqlDialect {}; // or AnsiDialect, or your own dialect ...
    let mut ast = Parser::parse_sql(&dialect, sql_select).unwrap();

    assert_eq!(ast[0].to_string(), sql_select);
}

Output:

thread 'main' panicked at 'assertion failed: `(left == right)`
  left: `"SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders JOIN Customers ON Orders.CustomerID = Customers.CustomerID"`,
 right: `"SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID"`', src/main.rs:111:5

[kasvith]

Same is happening with OUTER JOIN

[alamb]

Hi @kasvith -- thank you for the report

This is a similar issue to #918

Basically what needs to happen is that the SQL AST needs to be extended to track if the INNER keyword was seen and then use that to faithfully replicate the output

Anyone who has time to offer a PR that would be most helpful

[kasvith]

hey @alamb is this resolved?

[alamb]

hey @alamb is this resolved?

I am not sure -- perhaps you can contribute a PR with a test case demonstrating if it is fixed or not

[kasvith]

I checked one more time and seems its failing... if you can give me some hints i would like to work on this PR

[alamb]

I checked one more time and seems its failing... if you can give me some hints i would like to work on this PR

Perhaps you could write a test in https://github.com/sqlparser-rs/sqlparser-rs/blob/main/tests/sqlparser_common.rs that uses verified_query_with_canonical to show that the resulting parse tree loses the results

Here is an example
https://github.com/sqlparser-rs/sqlparser-rs/blob/4875dadbf5e691dd1e8ef179145829d8ca3ef4d8/tests/sqlparser_databricks.rs#L154-L166

Or perhaps you can just put the example in this PR into test form

[kasvith]

Hi @alamb sure i will work on this, please assign me to the task :)