JSqlParser-5.0 problem with MySQL Convert(expr,type) function

We upgrade our base framework to springboot 3.3.5 and other related libraries recently.

A bug was reported to me during the testing. Some query would fail with JSqlParser 5.0.

Here is a simple demonstration:

public class Test {
  public static void main(String[] args) throws JSQLParserException {
    String sql =
        "SELECT  CONVERT(IF(bill_type = 2, id, ''), char) from dual";
    Statement statement2 = CCJSqlParserUtil.parse(sql);
    System.out.println(statement2.toString());
  }
}

Enter fullscreen mode Exit fullscreen mode

The issue is that JSqlParser 5.0 doesn’t support MySQL-style CONVERT syntax CONVERT(expr, type). It only supports:

SQL Server style: CONVERT(type, expr)
Character set conversion: CONVERT(expr USING charset)
This affects queries using MySQL’s CONVERT function, especially when the expression is complex (like using IF statements).

Current grammar in JSqlParser:

{
    <K_CONVERT> "("
    (
        LOOKAHEAD(ColDataType() ",") (
            colDataType = ColDataType()
            "," expression = Expression()
            [ "," style = <S_LONG> ]
        )
        |
        (
            expression = Expression()
            <K_USING> transcodingName=IdentifierChain()
        )
    )
    ")"
}

Enter fullscreen mode Exit fullscreen mode

There are two solutions to this problem:

1. Use CAST instead of CONVERT
   Simply replace CONVERT(expr, type) with CAST(expr AS type). This is the simplest solution if you’re using MySQL

2. Modify JSqlParser grammar to support CONVERT(expr, type)
   Need to modify the grammar rules to support MySQL syntax. However, be careful with TranscodingFunction.appendTo method. Current implementation generates CONVERT(type, expr) which is legal in SQL Server, but illegal in MySQL

    public StringBuilder appendTo(StringBuilder builder) {
        if (isTranscodeStyle) {
            return builder
                    .append("CONVERT( ")
                    .append(expression)
                    .append(" USING ")
                    .append(transcodingName)
                    .append(" )");
        } else {
            return builder
                    .append("CONVERT( ")
                    .append(colDataType)
                    .append(", ")
                    .append(expression)
                    .append(transcodingName != null && !transcodingName.isEmpty()
                            ? ", " + transcodingName
                            : "")
                    .append(" )");
        }
    }

Enter fullscreen mode Exit fullscreen mode

原文链接：JSqlParser-5.0 problem with MySQL Convert(expr,type) function