There is an error when executing the query interface in the code.
The persistence framework in the code uses mybatis.
Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: ` integer
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Error: Operator does not exist: `integer
Tip: There is no operator matching the given name and argument types. You may need to add an explicit type cast.
Specific error message
2023-09-19 16:25:07.093 DEBUG 873377 --- [XNIO-1 task-108] o.j.a.mapper.NoticeMapper.allCount : ==> Preparing: SELECT count(id) num FROM notice WHERE is_deleted = 0 and `status` = 0 and user_type = ? and platform_no = ? and create_time >= ? 2023-09-19 16:25:07.095 DEBUG 873377 --- [XNIO-1 task-108] o.j.a.mapper.NoticeMapper.allCount : ==> Parameters: 1(Integer), PT0002(String), 2020-06-04 16:21:06.0(Timestamp) 2023-09-19 16:25:07.098 INFO 873377 --- [XNIO-1 task-108] o.j.core.log.aspect.RequestLogAspect: 2023-09-19 16:25:07.099 ERROR 873377 --- [XNIO-1 task-108] .j.c.l.e.RestExceptionTranslator: Server exception org.springframework.jdbc.BadSqlGrammarException: ### Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: ` integer Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 96 ### The error may exist in class path resource [org/yang/account/mapper/NoticeMapper.xml] ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: SELECT count(id) num FROM notice WHERE is_deleted = 0 and `status` = 0 and user_type = ? and platform_no = ? and create_time >= ? ### Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: ` integer Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 96 ; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: ` integer Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 96 at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440) at com.sun.proxy.$Proxy194.selectOne(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:159) at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:108) at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96) at com.sun.proxy.$Proxy268.allCount(Unknown Source) at org.yang.account.service.impl.NoticeServiceImpl.isRead(NoticeServiceImpl.java:114) at org.yang.account.service.impl.NoticeServiceImpl$$FastClassBySpringCGLIB$$ef51929d.invoke(<generated>) at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:750) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:120) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689) at org.yang.account.service.impl.NoticeServiceImpl$$EnhancerBySpringCGLIB$$11f140ab.isRead(<generated>) at org.yang.account.controller.v2.NoticeV2Controller.isReadTenant(NoticeV2Controller.java:83) at org.yang.account.controller.v2.NoticeV2Controller$$FastClassBySpringCGLIB$$a9d2b025.invoke(<generated>)
View the specific executed output SQL
SELECT count(id) num FROM notice WHERE is_deleted = 0 and `status` = 0 and user_type = ? and platform_no = ? and create_time >= ?
Take this SQL replacement parameter and execute it on the client. The execution results are as follows:
SELECT count(id) mu FROM notice WHERE is_deleted = 0 and `status` = 0 and user_type = 1 and platform_no = 'PT2' and create_time >= '2020-06-04 16:21:06.0\ '
> ERROR: operator does not exist: ` integer
LINE 1: ...notice WHERE is_deleted = 0 and `status` ...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
> Query time: 0.248s
The error message is:There is no operator matching the given name and parameter type. You may need to add an explicit type cast.
I checked that the parameters are of the correct type.
Wait, I suddenly remembered
`status`
Why ” “ ” is added to status? This is because this is how I wrote it in MySQL. But I don’t use it in openGauss.
So remove it and the problem is solved.