I am currently writing a query on Hibernate 5.2.10.Final and keep on encountering Unexpected AST Node error, the query is here
SELECT
DISTINCT cs
FROM
CashbackSetting cs
LEFT JOIN CashbackRegisterSetting crs ON
cs.id = crs.cashbackSetting.id
LEFT JOIN crs.affiliates af
LEFT JOIN CashbackDepositSetting cds ON
cs.id = cds.cashbackSetting.id
LEFT JOIN cds.memberGroups mg
LEFT JOIN CashbackPaymentMethod cpm ON
cds.id = cpm.cashbackDepositSetting.id
LEFT JOIN cpm.banks b
WHERE
cs.merchant = :merchant
AND (cs.currency = :currency)
AND (:id IS NULL
OR cs.id = :id)
AND (coalesce(:cashbackTypes) IS NULL
OR cs.cashbackType IN (:cashbackTypes))
AND (cs.startDate >= :startDate
AND cs.endDate <= :endDate)
AND (:createdBy IS NULL
OR cs.createdBy = :createdBy)
AND (:modifiedBy IS NULL
OR cs.lastModifiedBy = :modifiedBy)
AND (:status IS NULL
OR cs.status = :status)
AND ((CASE
WHEN (cs.cashbackType = 'REGISTER') THEN ((CASE
WHEN (:applyType IS NULL) THEN 1
WHEN (:applyType = 'CODE_LIST') THEN ((CASE
WHEN (:useAllAffCode IS NOT NULL) THEN (crs.useAll = :useAllAffCode)
ELSE (:affId IS NULL
OR af.id = :affId)
END) = 1)
ELSE (crs.affCodeApplyType = :applyType)
END) = 1) <!-- error in this line '=' -->
ELSE (((CASE
WHEN (:useAllPaymentMethods IS NULL OR :useAllPaymentMethods = FALSE) THEN (
(:paymentMethod IS NULL OR cpm.paymentMethod = :paymentMethod)
AND ((CASE
WHEN (:useAllChannelId IS NULL
OR :useAllChannelId = FALSE) THEN (:channelId IS NULL
OR b.id = :channelId)
ELSE (cpm.useAll = TRUE)
END) = 1))
ELSE (cds.useAllPaymentMethods = TRUE)
END) = 1)
AND ((CASE <!-- error in this line 'AND' -->
WHEN (:useAllMemberGroups IS NULL
OR :useAllMemberGroups = FALSE) THEN (:groupId IS NULL
OR mg.id = :groupId)
ELSE (cds.useAllMemberGroups = TRUE)
END) = 1))
END) = 1)
the error:
2023-11-09 15:27:09.496 ERROR [CashMarket,,,] 23208 --- [ restartedMain] o.h.hql.internal.ast.ErrorCounter : <AST>:50:25: unexpected AST node: AND
2023-11-09 15:27:09.500 ERROR [CashMarket,,,] 23208 --- [ restartedMain] o.h.hql.internal.ast.ErrorCounter : <AST>:50:25: unexpected AST node: AND
antlr.NoViableAltException: unexpected AST node: AND
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.expressionOrSubQuery(HqlSqlBaseWalker.java:5358)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.elseClause(HqlSqlBaseWalker.java:5431)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.searchedCaseExpression(HqlSqlBaseWalker.java:5267)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.caseExpr(HqlSqlBaseWalker.java:5127)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.arithmeticExpr(HqlSqlBaseWalker.java:3355)
2023-11-09 15:27:07.218 WARN [CashMarket,,,] 23208 --- [onMonitorThread] c.h.s.i.o.impl.InvocationMonitor : [127.0.0.1]:13782 [dev] [3.7.8] BroadcastOperationHeartbeatsTask delayed 278531 ms
2023-11-09 15:27:07.220 ERROR [CashMarket,,,] 23208 --- [ restartedMain] o.h.hql.internal.ast.ErrorCounter : <AST>:38:30: unexpected AST node: =
antlr.NoViableAltException: unexpected AST node: =
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.expressionOrSubQuery(HqlSqlBaseWalker.java:5358)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.searchedCaseWhenClause(HqlSqlBaseWalker.java:5471)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.searchedCaseExpression(HqlSqlBaseWalker.java:5251)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.caseExpr(HqlSqlBaseWalker.java:5127)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.arithmeticExpr(HqlSqlBaseWalker.java:3355)
so, according to the log and function expressionOrSubQuery()'s source, the '=' in line 38 and 'AND in line 50 should not be there, can anyone suggest me a change?
I was expecting it to be able to be built successfully as I was able to run the sql version correctly on workbench. Apparently HQL has many rules under the flow that simply parsing sql with hql format wouldn't cut it.