JSqlParser- 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());
  }
}

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()
        )
    )
    ")"
}

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(" )");
        }
    }

The above is the detailed content of JSqlParser- problem with MySQL Convert(expr,type) function. For more information, please follow other related articles on the PHP Chinese website!