Encountered unexpected token: "CASE"

[dracenliu]

To Reproduce Steps to reproduce the behavior:

1. Example SQL

   SELECT av1.app_name, av1.version_no, av1.file_size, av1.version_name, av1.update_desc, av1.detail_desc, av1.gmt_modified
       , av1.icon_url ,av1.app_id ,av1.id
       FROM app_version av1,
   (
   SELECT av.app_id,MAX(av.version_no) AS version_no
   FROM
       app_version av
       join app_version_policy avp on av.id = avp.app_version_id
       WHERE
    av.`status` = 1
     AND av.app_id IN
        (  
           ?
        , 
           ?
        , 
           ?
        , 
           ?
        ) 
   
           AND CASE
           WHEN
               avp.device_sn IS NOT NULL AND length(avp.device_sn) > 0
           THEN
               avp.device_sn LIKE CONCAT('%',?,'%')
           ELSE 1 = 1
           END
   
           AND CASE
           WHEN
           avp.area IS NOT NULL AND length(avp.area) > 0
           THEN
           (avp.area LIKE CONCAT('%,',?,',%') or avp.area LIKE CONCAT('%,',?,',%'))
           ELSE 1 = 1
           END
   
   GROUP BY 
     av.app_id
   ) t 
   WHERE av1.app_id = t.app_id AND av1.version_no = t.version_no

2. Exception

   
   Caused by: net.sf.jsqlparser.JSQLParserException: null
   at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:51)
   at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:40)
   at com.agtech.ailot.common.dal.mybatis.MybatisInterceptor.intercept(MybatisInterceptor.java:62)
   at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
   ... 139 common frames omitted
   Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "CASE" "CASE"
   at line 31, column 8.

Was expecting one of:

"!"
"("
"NOT"

at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:22439)
at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:22286)
at net.sf.jsqlparser.parser.CCJSqlParser.AndExpression(CCJSqlParser.java:7606)
at net.sf.jsqlparser.parser.CCJSqlParser.OrExpression(CCJSqlParser.java:7475)
at net.sf.jsqlparser.parser.CCJSqlParser.Expression(CCJSqlParser.java:7446)
at net.sf.jsqlparser.parser.CCJSqlParser.WhereClause(CCJSqlParser.java:6425)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:3794)
at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:3973)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:3649)
at net.sf.jsqlparser.parser.CCJSqlParser.SubSelect(CCJSqlParser.java:11689)
at net.sf.jsqlparser.parser.CCJSqlParser.FromItem(CCJSqlParser.java:5423)
at net.sf.jsqlparser.parser.CCJSqlParser.JoinerExpression(CCJSqlParser.java:6247)
at net.sf.jsqlparser.parser.CCJSqlParser.JoinsList(CCJSqlParser.java:6073)
at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:3775)
at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:3973)
at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:3649)
at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:3642)
at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:124)
at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:75)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:49)
... 142 common frames omitted

**Expected behavior**
A clear and concise description of what you expected to happen.

**System**
 - Database version mysql 8.0
- Java Version  jdk8
- JSqlParser version 3.1

[wumpz]

Could you build a smaller example?

[dracenliu]

SELECT av.app_id, MAX(av.version_no) AS version_no
FROM app_version av
    JOIN app_version_policy avp ON av.id = avp.app_version_id
WHERE av.`status` = 1
    AND CASE 
        WHEN avp.area IS NOT NULL
        AND length(avp.area) > 0 THEN avp.area LIKE CONCAT('%,', '12', ',%')
        OR avp.area LIKE CONCAT('%,', '13', ',%')
        ELSE 1 = 1
    END
GROUP BY av.app_id

[wumpz]

Ok. JSqlParser does not yet support and / or expression for its then part.