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:
(
expression = Expression()
<K_USING> transcodingName=IdentifierChain()
)
)
")"
There are two solutions to this problem:
-
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 -
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(" )");