DEV Community

刘亳
刘亳

Posted on

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

Heroku

Simplify your DevOps and maximize your time.

Since 2007, Heroku has been the go-to platform for developers as it monitors uptime, performance, and infrastructure concerns, allowing you to focus on writing code.

Learn More

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more