[Bug] [web] When dinky uses postgresql data, the document search reports an error

[chenlb]

Search before asking

• I had searched in the issues and found no similar issues.

What happened

我的环境，docker 部署的 dinky-1.2.0， flink-1.20，jdk11 。postgresql 版本 PostgreSQL 17.2 (Debian 17.2-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

报错如下：

2024-12-29 21:12:37 [dinky] 2024-12-29 21:12:37.814  ERROR    59 --- [  XNIO-1 task-3] org.dinky.aop.exception.UnKnownExceptionHandler: 
2024-12-29 21:12:37 ### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1
2024-12-29 21:12:37 ### The error may exist in class path resource [mapper/DocumentMapper.xml]
2024-12-29 21:12:37 ### The error may involve defaultParameterMap
2024-12-29 21:12:37 ### The error occurred while setting parameters
2024-12-29 21:12:37 ### SQL: select count(*) as total from dinky_flink_document a where 1 = 1 and a.name like concat('%', ?, '%')
2024-12-29 21:12:37 ### Cause: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1
2024-12-29 21:12:37 ; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1
2024-12-29 21:12:37 org.springframework.jdbc.BadSqlGrammarException: 
2024-12-29 21:12:37 ### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1
2024-12-29 21:12:37 ### The error may exist in class path resource [mapper/DocumentMapper.xml]
2024-12-29 21:12:37 ### The error may involve defaultParameterMap
2024-12-29 21:12:37 ### The error occurred while setting parameters
2024-12-29 21:12:37 ### SQL: select count(*) as total from dinky_flink_document a where 1 = 1 and a.name like concat('%', ?, '%')
2024-12-29 21:12:37 ### Cause: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1
2024-12-29 21:12:37 ; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1
2024-12-29 21:12:37     at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101) ~[spring-jdbc-5.3.31.jar:5.3.31]

What you expected to happen

不报错，能找到。

How to reproduce

按界面操作可以复现。

Anything else

No response

Version

1.2.0

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[github-actions]

Hello @chenlb, this issue is about web, so I assign it to @Zzm0809. If you have any questions, you can comment and reply.

你好 @chenlb, 这个 issue 是关于 web 的，所以我把它分配给了 @Zzm0809。如有任何问题，可以评论回复。

[aiwenmo]

@Zzm0809 CC

[MactavishCui]

Hi~ @aiwenmo @Zzm0809
When using postgreSql and method selectForProTable is called, this bug will occurred. That means this bug exits in every search tables, not only document table in register center but also role table, user table in auth center and so on.
I think this bug can be fixed by changing the code of [dinky-admin]org.dinky.interceptor.PostgreSQLPrepareInterceptor line 47 as follows:

before:

field.set(boundSql, boundSql.getSql().replace("`", "\"").toLowerCase());

after:

field.set(boundSql, boundSql.getSql().replace("`", "\"").replace("concat('%', '?', '%')", "concat('%', '?::text', '%')"").toLowerCase());

I have tested that this bug can be fixed after this change and willing to submit a pr for this issue, my solution is shown in https://github.com/DataLinkDC/dinky/pull/4101
What is your opinion?
Looking forward to your reply.