Query statement character parsing issues

[umll]

When I execute the query, I need to use Chinese, but the following fuzzy query with Chinese is successful. SELECT * FROM topicset_info ti WHERE ti.topicset_name LIKE CONCAT('%','测试','%') But I use a more complicated query statement, as follows SELECT COUNT(*) AS total FROM (SELECT DISTINCT rtu.topicset_id FROM relate_topicset_usergroup rtu JOIN relate_usergroup_user ruu ON ruu.usergroup_id = rtu.usergroup_id WHERE ruu.account_id = 1183367845602394287) u LEFT JOIN topicset_info ti ON u.topicset_id = ti.topicset_id LEFT JOIN relate_label_topicset tl ON ti.topicset_id = tl.topicset_id LEFT JOIN teacher_info ti2 ON ti.account_id = ti2.account_id LEFT JOIN unit_first_info ufi ON ufi.unit_first_id = ti.unit_first_id WHERE (ti.topicset_recycled = 1 OR ti.topicset_recycled = 3 OR topicset_recycled = 0) AND ti.topicset_name LIKE CONCAT('%', '测试', '%') AND ti.topicset_typeid = 4 The execution result will be an error,as follows. Caused by: java.lang.RuntimeException: while converting CONCAT('%', u&'\6d4b\8bd5', '%') at org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerOpTypeMethod$3(ReflectiveConvertletTable.java:145) at org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:62) at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5269) at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4495) at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161) at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5097) at org.apache.calcite.sql2rel.StandardConvertletTable.convertOperands(StandardConvertletTable.java:960) at org.apache.calcite.sql2rel.StandardConvertletTable.convertOperands(StandardConvertletTable.java:952) at org.apache.calcite.sql2rel.StandardConvertletTable.convertCall(StandardConvertletTable.java:915) ... 59 common frames omitted Caused by: java.lang.reflect.InvocationTargetException: null at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.calcite.sql2rel.ReflectiveConvertletTable.lambda$registerOpTypeMethod$3(ReflectiveConvertletTable.java:140) ... 67 common frames omitted Caused by: org.apache.calcite.runtime.CalciteException: Failed to encode '测试' in character set 'ISO-8859-1' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:506) at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:600) at org.apache.calcite.util.NlsString.<init>(NlsString.java:147) at org.apache.calcite.util.NlsString.<init>(NlsString.java:116) at org.apache.calcite.rex.RexBuilder.makeLiteral(RexBuilder.java:964) at org.apache.calcite.rex.RexBuilder.makeCharLiteral(RexBuilder.java:1183) at org.apache.calcite.sql2rel.SqlNodeToRexConverterImpl.convertLiteral(SqlNodeToRexConverterImpl.java:112) at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:5254) at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4495) at org.apache.calcite.sql.SqlLiteral.accept(SqlLiteral.java:570) at org.apache.calcite.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:5097) at org.apache.calcite.sql2rel.StandardConvertletTable.convertOperands(StandardConvertletTable.java:960) at org.apache.calcite.sql2rel.StandardConvertletTable.convertOperands(StandardConvertletTable.java:952) at org.apache.calcite.sql2rel.StandardConvertletTable.convertFunction(StandardConvertletTable.java:727) ... 72 common frames omitted Please tell me what is the reason for this problem and how to solve it?

[strongduanmu]

Hi @umll, can you modify the issue according to template?

[umll]

你好@umll，可以按照模板修改问题吗？

Bug Report

Which version of ShardingSphere did you use?

ShardingSphere5.1

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

Execute SELECT COUNT(*) AS total FROM (SELECT DISTINCT rtu.topicset_id FROM relate_topicset_usergroup rtu JOIN relate_usergroup_user ruu ON ruu.usergroup_id = rtu.usergroup_id WHERE ruu.account_id = 1183367845602394287) u LEFT JOIN topicset_info ti ON u.topicset_id = ti.topicset_id LEFT JOIN relate_label_topicset tl ON ti.topicset_id = tl.topicset_id LEFT JOIN teacher_info ti2 ON ti.account_id = ti2.account_id LEFT JOIN unit_first_info ufi ON ufi.unit_first_id = ti.unit_first_id WHERE (ti.topicset_recycled = 1 OR ti.topicset_recycled = 3 OR topicset_recycled = 0) AND ti.topicset_name LIKE CONCAT('%', '测试', '%') AND ti.topicset_typeid = 4successfully.

Actual behavior

[ERROR] 2024-04-24 22:37:49.860 [ShardingSphere-Command-9] o.a.s.p.f.c.CommandExecutorTask - Exception occur: java.sql.SQLException: Error while preparing statement [SELECT COUNT(*) AS total FROM ^M (SELECT DISTINCT rtu.topicset_id FROM relate_topicset_usergroup rtu ^M JOIN relate_usergroup_user ruu ON ruu.usergroup_id = rtu.usergroup_id ^M WHERE ruu.account_id = 1183367845602394287) u LEFT JOIN topicset_info ti ON u.topicset_id = ti.topicset_id ^M LEFT JOIN relate_label_topicset tl ON ti.topicset_id = tl.topicset_id ^M LEFT JOIN teacher_info ti2 ON ti.account_id = ti2.account_id ^M LEFT JOIN unit_first_info ufi ON ufi.unit_first_id = ti.unit_first_id ^M WHERE (ti.topicset_recycled = 1 OR ti.topicset_recycled = 3 OR topicset_recycled = 0) ^M AND ti.topicset_name LIKE CONCAT('%', '测试', '%') AND ti.topicset_typeid = 4 LIMIT 0, 1000]

Caused by: org.apache.calcite.runtime.CalciteException: Failed to encode '测试' in character set 'ISO-8859-1' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:506)

Reason analyze (If you can)

It may be that ShardingSphere-Proxy cannot use 'ISO-8859-1' to encode Chinese and needs to change the encoding format.

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.