Description

kevin2000

opened on Apr 14, 2023

Issue body actions

the target sql is "select * from table2 where id in (SELECT id FROM table1 WHERE find_in_set(100, ancestors))"

the follow code will throw exception that Statement cant convert to Expression
InExpression expression = new InExpression();
expression.setLeftExpression(new Column(deptColumn));
Statement subSelect = CCJSqlParserUtil.parse("SELECT id FROM table1 WHERE find_in_set(100, ancestors)");
expression.setRightExpression((Expression) subSelect);

Activity

1. manticore-projects commented on Apr 14, 2023

   

   manticore-projects

   on Apr 14, 2023

   Contributor

   More actions

   Greetings.

   Yes, your observation is correct because SubSelect is an Expression, but not a Statement.
   So you have to options:

   1. Build a new SubSelect using the Java API.
   2. Parse the SubSelect as an Expression:

   SubSelect subSelect = (SubSelect) CCJSqlParserUtil.parseExpression("SELECT id FROM table1 WHERE find_in_set(100, ancestors)");
   InExpression expression = new InExpression();
   expression.setLeftExpression(new Column(deptColumn));
   expression.setRightExpression( subSelect );
2. manticore-projects commented on Apr 14, 2023

   

   manticore-projects

   on Apr 14, 2023

   Contributor

   More actions

   It's just pure coincidence, that I have provided PR #1754 which changes exactly that behaviour and makes the former SubSelect a normal Statement (and Expression).

   So when you checkout this PR #1754, the following will work:

       @Test
       void testSubJoinParsing() throws JSQLParserException {
           String sqlStr = "(SELECT id FROM table1 WHERE find_in_set(100, ancestors))";
           Select select = (Select)  CCJSqlParserUtil.parse(sqlStr);
   
           InExpression inExpression = new InExpression();
           inExpression.setLeftExpression(new Column("id"));
           inExpression.setRightExpression( select );
   
           Assertions.assertEquals("id IN (SELECT id FROM table1 WHERE find_in_set(100, ancestors))", inExpression.toString());
       }
3. kevin2000

   closed this as completedon Apr 17, 2023

Issue actions