search

JSQLParser / JSqlParser

JSqlParser parses an SQL statement and translate it into a hierarchy of Java classes. The generated hierarchy can be navigated using the Visitor Pattern
https://github.com/JSQLParser/JSqlParser/wiki
Apache License 2.0
5.33k stars 1.34k forks source link

multiple columns IN operation is not support #737

Closed yozen17 closed 4 years ago

yozen17 commented 5 years ago

Hi : i used JSqlParser - version 1.4 for example: // mysql select * from a where (c1,c2) IN ((1,2),(3,4))

wumpz commented 5 years ago

My first guess is, that this multivalue statement is not supported. (c1, c2) in (select c1, c2 from ...) should work.

yozen17 commented 5 years ago

I think this is standard syntax and should be supported

wumpz commented 5 years ago

Do you want to contribute? PRs are welcome.

jerryleooo commented 5 years ago

@wumpz thanks for your great project first! I also encounter this issue and I tried define a function contains to solve it but seems also not working. The SQL I tried are select a, b, c, d from table where contains(((1, 2, 3, 4), (5, 6, 7, 8)), (a, b, c, d)) and select a, b, c, d from table where contains(VALUES((1, 2, 3, 4), (5, 6, 7, 8)), (a, b, c, d)) The version is 1.3

We have our own AST so there are spaces for us to perform. Do you have any advices to solve this? For example a specific syntax and so on.

jerryleooo commented 5 years ago

I have solved this by define a tuple function, so the SQL looks like select a, b, c, d from table where contains(tuple(tuple(1, 2, 3, 4), tuple(5, 6, 7, 8)), tuple(a, b, c, d))

yidu0331 commented 4 years ago

we also encountered

AnEmortalKid commented 4 years ago

I think @wumpz 's guess was correct, the problem starts at the where (:

CCJSqlParserUtil.parse("select a,b from foo where (a,b) in ((1,2),(3,4))");
Call:   Statement
  Call: SingleStatement
    Call:   Select
      Call: SelectBody
        Call:   SetOperationList
          Call: PlainSelect
            Consumed token: <<K_SELECT>: "select" at line 1 column 1>
            Call:   getOracleHint
            Return: getOracleHint
            Call:   SelectItemsList
              Call: SelectItem
                Call:   SelectExpressionItem
                  Call: SimpleExpression
                    Call:   ConcatExpression
                      Call: BitwiseAndOr
                        Call:   AdditiveExpression
                          Call: MultiplicativeExpression
                            Call:   BitwiseXor
                              Call: PrimaryExpression
                                Call:   Column
                                  Call: RelObjectNameList
                                    Call:   RelObjectNameExt
                                      Call: RelObjectName
                                        Call:   RelObjectNameWithoutValue
                                          Consumed token: <<S_IDENTIFIER>: "a" at line 1 column 8>
                                        Return: RelObjectNameWithoutValue
                                      Return: RelObjectName
                                    Return: RelObjectNameExt
                                  Return: RelObjectNameList
                                Return: Column
                              Return: PrimaryExpression
                            Return: BitwiseXor
                          Return: MultiplicativeExpression
                        Return: AdditiveExpression
                      Return: BitwiseAndOr
                    Return: ConcatExpression
                  Return: SimpleExpression
                Return: SelectExpressionItem
              Return: SelectItem
              Consumed token: <"," at line 1 column 9>
              Call: SelectItem
                Call:   SelectExpressionItem
                  Call: SimpleExpression
                    Call:   ConcatExpression
                      Call: BitwiseAndOr
                        Call:   AdditiveExpression
                          Call: MultiplicativeExpression
                            Call:   BitwiseXor
                              Call: PrimaryExpression
                                Call:   Column
                                  Call: RelObjectNameList
                                    Call:   RelObjectNameExt
                                      Call: RelObjectName
                                        Call:   RelObjectNameWithoutValue
                                          Consumed token: <<S_IDENTIFIER>: "b" at line 1 column 10>
                                        Return: RelObjectNameWithoutValue
                                      Return: RelObjectName
                                    Return: RelObjectNameExt
                                  Return: RelObjectNameList
                                Return: Column
                              Return: PrimaryExpression
                            Return: BitwiseXor
                          Return: MultiplicativeExpression
                        Return: AdditiveExpression
                      Return: BitwiseAndOr
                    Return: ConcatExpression
                  Return: SimpleExpression
                Return: SelectExpressionItem
              Return: SelectItem
            Return: SelectItemsList
            Consumed token: <"FROM": "from" at line 1 column 12>
            Call:   FromItem
              Call: Table
                Call:   RelObjectNameList
                  Call: RelObjectNameExt
                    Call:   RelObjectName
                      Call: RelObjectNameWithoutValue
                        Consumed token: <<S_IDENTIFIER>: "foo" at line 1 column 17>
                      Return: RelObjectNameWithoutValue
                    Return: RelObjectName
                  Return: RelObjectNameExt
                Return: RelObjectNameList
              Return: Table
            Return: FromItem
            Call:   JoinsList
            Return: JoinsList
            Call:   WhereClause
              Consumed token: <"WHERE": "where" at line 1 column 21>
              Call: Expression
                Call:   OrExpression
                  Call: AndExpression
                    Consumed token: <"(" at line 1 column 27>
                    Call:   OrExpression
                      Call: AndExpression
                        Call:   Condition
                          Call: SimpleExpression
                            Call:   ConcatExpression
                              Call: BitwiseAndOr
                                Call:   AdditiveExpression
                                  Call: MultiplicativeExpression
                                    Call:   BitwiseXor
                                      Call: PrimaryExpression
                                        Call:   Column
                                          Call: RelObjectNameList
                                            Call:   RelObjectNameExt
                                              Call: RelObjectName
                                                Call:   RelObjectNameWithoutValue
                                                  Consumed token: <<S_IDENTIFIER>: "a" at line 1 column 28>
                                                Return: RelObjectNameWithoutValue
                                              Return: RelObjectName
                                            Return: RelObjectNameExt
                                          Return: RelObjectNameList
                                        Return: Column
                                      Return: PrimaryExpression
                                    Return: BitwiseXor
                                  Return: MultiplicativeExpression
                                Return: AdditiveExpression
                              Return: BitwiseAndOr
                            Return: ConcatExpression
                          Return: SimpleExpression
                        Return: Condition
                      Return: AndExpression
                    Return: OrExpression
                  Return: AndExpression
                Return: OrExpression
              Return: Expression
            Return: WhereClause
          Return: PlainSelect
        Return: SetOperationList
      Return: SelectBody
    Return: Select
  Return: SingleStatement
Return: Statement
Exception in thread "main" net.sf.jsqlparser.JSQLParserException
    at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:65)
    at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:40)
    at net.sf.jsqlparser.Visualizer.main(Visualizer.java:9)
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "," ","
    at line 1, column 29.

Comparing it with a standard a in (1,2), I noticed the path goes through a SimpleExpression:

                          Return: SimpleExpressionList
                          Consumed token: <")" at line 1 column 36>
                        Return: InExpression

I noticed that there's a PIVOT option that supports Multiple IN items, so we'd probably have to extend at least the IN to support multiple expression list items?

        String sql = "SELECT * FROM ( select a,b from foo) PIVOT ( func(column2) for column2 in ( (1,2), (3,4)) )";
        Statement statement = CCJSqlParserUtil.parse(sql);

image

The grammar for IN (in oracle at least) if it helps any to understand what portion we're missing: https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions013.htm

wumpz commented 4 years ago

@AnEmortalKid 👍

AnEmortalKid commented 4 years ago

Took a preliminary stab at this on the weekend:

CCJSqlParserUtil.parse("select a,b from foo where (a,b) in (1,2)");
CCJSqlParserUtil.parse("select a,b from foo where (a,b) = (1,2)");

Parses as a SimpleExpressionList (on the right).

The tricky bit is with the right part, at least:

((1,2),(3,4))

I think for that we need to have at least a (3) look ahead and look for something like:

"(" 
  "(" , SimpleExpressionList() ")" ("," "(" SimpleExpressionList() ")")*
")"

Got some conflict productions while trying that but i might be on the right track.

AnEmortalKid commented 4 years ago

I have a little bit more working over here: https://github.com/AnEmortalKid/JSqlParser/blob/multi-ins/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt#L2563

    @Test
    public void foo() throws Exception {
        Statement parsed = CCJSqlParserUtil.parse("select a,b from foo where (a,b) in ((1,2),(3,4),(5,6),(7,8))");
        System.out.println(parsed);
        Select select = (Select) parsed;
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();

        Expression whereExpression = plainSelect.getWhere();
        InExpression inExpression = (InExpression) whereExpression;

        System.out.println(inExpression.getMultiExpressionList());
    }

Can print out:

SELECT a, b FROM foo WHERE (a, b) IN null
(1, 2), (3, 4), (5, 6), (7, 8)

I might need some of @wumpz 's knowledge on javacc since some of the SelectTests now seem to be failing, even though I thought my "((" production part would make them not go through that path.