[GitHub] [incubator-shardingsphere] zcqshine opened a new issue #2702: 【4.0.0】org.apache.shardingsphere.core.parse.old.parser.exception.SQLParsingException: SQL syntax error, expected token is 'EQ', actual token is 'IN', literals is 'IN'

[GitBox <gi...@apache.org>]

zcqshine opened a new issue #2702: 【4.0.0】org.apache.shardingsphere.core.parse.old.parser.exception.SQLParsingException: SQL syntax error, expected token is 'EQ', actual token is 'IN', literals is 'IN' 
URL: https://github.com/apache/incubator-shardingsphere/issues/2702
 
 
   ## Bug Report
   
   sharding-jdbc 4.0.0
   
   `org.apache.shardingsphere.core.parse.old.parser.exception.SQLParsingException: SQL syntax error, expected token is 'EQ', actual token is 'IN', literals is 'IN'.`
   
   ```
   org.apache.shardingsphere.core.parse.old.parser.exception.SQLParsingException: SQL syntax error, expected token is 'EQ', actual token is 'IN', literals is 'IN'.
   	at org.apache.shardingsphere.core.parse.old.lexer.LexerEngine.accept(LexerEngine.java:122)
   	at org.apache.shardingsphere.core.parse.old.parser.clause.TableReferencesClauseParser.parseJoinCondition(TableReferencesClauseParser.java:179)
   	at org.apache.shardingsphere.core.parse.old.parser.clause.TableReferencesClauseParser.parseJoinTable(TableReferencesClauseParser.java:149)
   	at org.apache.shardingsphere.core.parse.old.parser.clause.TableReferencesClauseParser.parseTableFactor(TableReferencesClauseParser.java:119)
   	at org.apache.shardingsphere.core.parse.old.parser.dialect.postgresql.clause.PostgreSQLTableReferencesClauseParser.parseTableReference(PostgreSQLTableReferencesClauseParser.java:40)
   	at org.apache.shardingsphere.core.parse.old.parser.clause.TableReferencesClauseParser.parse(TableReferencesClauseParser.java:79)
   	at org.apache.shardingsphere.core.parse.old.parser.sql.dml.select.AbstractSelectParser.parseTable(AbstractSelectParser.java:106)
   	at org.apache.shardingsphere.core.parse.old.parser.sql.dml.select.AbstractSelectParser.parseFrom(AbstractSelectParser.java:95)
   	at org.apache.shardingsphere.core.parse.old.parser.dialect.postgresql.sql.PostgreSQLSelectParser.parseInternal(PostgreSQLSelectParser.java:49)
   	at org.apache.shardingsphere.core.parse.old.parser.sql.dml.select.AbstractSelectParser.parseInternal(AbstractSelectParser.java:82)
   	at org.apache.shardingsphere.core.parse.old.parser.sql.dml.select.AbstractSelectParser.parse(AbstractSelectParser.java:69)
   	at org.apache.shardingsphere.core.parse.old.parser.sql.dml.select.AbstractSelectParser.parse(AbstractSelectParser.java:53)
   	at org.apache.shardingsphere.core.parse.SQLParsingEngine.parse(SQLParsingEngine.java:61)
   	at org.apache.shardingsphere.core.route.router.sharding.ParsingSQLRouter.parse(ParsingSQLRouter.java:78)
   	at org.apache.shardingsphere.core.route.PreparedStatementRoutingEngine.route(PreparedStatementRoutingEngine.java:64)
   	at org.apache.shardingsphere.core.PreparedQueryShardingEngine.route(PreparedQueryShardingEngine.java:60)
   	at org.apache.shardingsphere.core.BaseShardingEngine.shard(BaseShardingEngine.java:64)
   	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.shard(ShardingPreparedStatement.java:224)
   	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:170)
   ```
   
   the sql :
   ```sql
   SELECT p.gov_org_id as orgId, MAX(o.org_name) as orgName, SUM(varchar_cast_numeric(d.value)) as completeLoad
   FROM t_project p
            INNER JOIN t_sys_org o ON o.del_flag = 0 AND o.id = p.gov_org_id
            LEFT JOIN t_project_progress_relation r
                      ON r.del_flag = 0 AND r.head_template_id IN (5, 7, 9) AND r.project_id = p.id
            LEFT JOIN t_form_template_data_1 d ON d.del_flag = 0 AND d.project_id = p.id AND EXISTS(
           SELECT 1 FROM t_building_number bn WHERE bn.del_flag = 0 AND bn.id = d.building_number_id) AND EXISTS(SELECT 1
                                                                                                                 FROM t_household h
                                                                                                                 WHERE h.del_flag = 0
                                                                                                                   AND h.project_id = p.id
                                                                                                                   AND extract(year from h.signing_date) = extract(year from now())
                                                                                                                   AND h.id = d.source_id) AND
                                                d.struct_id = r.struct_id
   WHERE 1 = 1
   GROUP BY p.gov_org_id
   ```
   
   I found that if `IN` or `EXISTS` after `left join` will be throw the above exception
   

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services