Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

join missing the 'on' filter #1229

Closed
zhang245606 opened this issue Jun 16, 2021 · 4 comments · Fixed by #1303
Closed

join missing the 'on' filter #1229

zhang245606 opened this issue Jun 16, 2021 · 4 comments · Fixed by #1303
Assignees
Labels
Projects

Comments

@zhang245606
Copy link

original SQL : select t1.column1,t1.column2,t2.field1,t2.field2 from T_DT_ytb_01 t1 , T_DT_ytb_02 t2 on t1.column1 = t2.field1
after parse SQL: SELECT t1.column1, t1.column2, t2.field1, t2.field2 FROM T_DT_ytb_01 t1, T_DT_ytb_02 t2

What is simple join?

@manticore-projects
Copy link
Contributor

manticore-projects commented Jun 16, 2021

Greetings.

  1. I can not confirm your observation. Statement is parsed and deparsed correctly:
SELECT  t1.column1
        , t1.column2
        , t2.field1
        , t2.field2
FROM t_dt_ytb_01 t1
    , t_dt_ytb_02 t2
        ON t1.column1 = t2.field1
;

You can also test it online here

  1. Your statement should be invalid though because you should be able to use ON only in conjunction with JOIN, otherwise you would need to use WHERE (at least this is my understanding).

Please double check and close this issue, when confirmed.

@wumpz
Copy link
Member

wumpz commented Jun 16, 2021

It could be, like using parseExpression, that JSqlParser parses as far as it is able to interpret your sql and stops then. How to you parse this statement actually?

If you do not use CCJSqlParserUtil.parse but directly use the parses SingleStatement method, this one could happen.

However this seems like a bug to me, as @manticore-projects already mentioned, this on should only be valid with a corresponding join.

@wumpz wumpz added the bug label Jun 16, 2021
@zhang245606
Copy link
Author

Thank you for your reply!

I try to parse it as follows :
String sql = "select t1.column1,t1.column2,t2.field1,t2.field2 from T_DT_ytb_01 t1 , T_DT_ytb_02 t2 on t1.column1 = t2.field1";
Statement parse = CCJSqlParserUtil.parse(sql);
return parse.toString();

This sql is equivalent to “select t1.column1,t1.column2,t2.field1,t2.field2 from T_DT_ytb_01 t1 inner join T_DT_ytb_02 t2 on t1.column1 = t2.field1”

I don't understand what simple join means .
The debug screenshot is as follows:
image

@zhang245606 zhang245606 reopened this Jun 16, 2021
@wumpz
Copy link
Member

wumpz commented Jun 16, 2021

The problem is in production JoinerExpression. Simple means a comma list of table names like

select * from tab1, tab2

but in the grammar is no check when this on part is parsed, if there was a non simple join

( <K_ON> onExpression=Expression()  { join.setOnExpression(onExpression); })

So the solution should be a semantic Lookahead here with the check !join.isSimple().

@wumpz wumpz self-assigned this Jun 16, 2021
@wumpz wumpz added this to To do in release 4.2 via automation Jun 16, 2021
manticore-projects added a commit to manticore-projects/JSqlParser that referenced this issue Aug 14, 2021
release 4.2 automation moved this from To do to Done Sep 6, 2021
wumpz pushed a commit that referenced this issue Sep 6, 2021
* Implement Joins with multiple trailing ON Expressions

Fixes #1302
Fixes SpecialOracleTest JOIN17, now 190/273 tests pass

* Fixes #1229

* Merge MASTER
Refactor the appendTo() method in favour of the traditional toString()

* Remove unused imports
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
No open projects
Development

Successfully merging a pull request may close this issue.

3 participants