corgy-w opened a new issue, #33220:
URL: https://github.com/apache/shardingsphere/issues/33220
Sql1:
```
SELECT DISTINCT a.id
FROM hl_claim a
JOIN hl_policy b ON a.policy_id = b.id
LEFT JOIN hl_policy_customer_org c ON b.id = c.policy_id
LEFT JOIN hl_policy_customer_org d ON b.id = d.policy_id
LEFT JOIN hl_policy_channel e ON b.id = e.policy_id
LEFT JOIN hl_claim_timeliness f
ON a.id = f.claim_id
AND f.is_deleted = 'N'
LEFT JOIN (
SELECT NULL AS claim_id, NULL AS employees_name
) g
ON a.id = g.claim_id
LEFT JOIN hl_claim_history ch
ON a.id = ch.claim_id
AND ch.status <> 10
AND ch.is_deleted = 'N'
LEFT JOIN hl_claim_extra hce ON hce.claim_id = a.id
LEFT JOIN (
SELECT SUM(hangupClaim.hangupClaimSeconds) AS
hangupClaimSeconds, hangupClaim.claimId, hangupClaim.objectType
FROM (
SELECT CASE LOCATE('"finishDate":', `note`)
WHEN 0 THEN UNIX_TIMESTAMP(NOW())
ELSE
CASE
LOCATE('"finishDate":null', `note`)
WHEN 0 THEN
SUBSTRING(`note`, LOCATE('"finishDate":', `note`) + 13, 10)
ELSE
UNIX_TIMESTAMP(NOW())
END
END - CASE LOCATE('"applyDate":', `note`)
WHEN 0 THEN 0
ELSE SUBSTRING(`note`,
LOCATE('"applyDate":', `note`) + 12, 10)
END AS hangupClaimSeconds, hn1.object_id AS
claimId, hn1.object_type AS objectType
FROM hl_note hn1
WHERE hn1.note_type = 13
AND hn1.object_type = 7
AND (LOCATE('"reasonCode":6', `note`) > 0
OR LOCATE('"reasonCode":3', `note`) > 0)
) hangupClaim
GROUP BY hangupClaim.claimId
) hangupClaim_
ON a.id = hangupClaim_.claimId
LEFT JOIN (
SELECT SUM(hangupClaim.hangupClaimSeconds) AS
hangupClaimSeconds, hangupClaim.claimId, hangupClaim.objectType
FROM (
SELECT CASE LOCATE('"finishDate":', `note`)
WHEN 0 THEN UNIX_TIMESTAMP(NOW())
ELSE
CASE
LOCATE('"finishDate":null', `note`)
WHEN 0 THEN
SUBSTRING(`note`, LOCATE('"finishDate":', `note`) + 13, 10)
ELSE
UNIX_TIMESTAMP(NOW())
END
END - CASE LOCATE('"applyDate":', `note`)
WHEN 0 THEN 0
ELSE SUBSTRING(`note`,
LOCATE('"applyDate":', `note`) + 12, 10)
END AS hangupClaimSeconds, hn1.object_id AS
claimId, hn1.object_type AS objectType
FROM hl_note hn1
WHERE hn1.note_type = 13
AND hn1.object_type = 20
AND (LOCATE('"reasonCode":6', `note`) > 0
OR LOCATE('"reasonCode":3', `note`) > 0)
) hangupClaim
LEFT JOIN hl_claim_history hch
ON hangupClaim.claimId = hch.id
AND hch.history_type <> 1
GROUP BY hangupClaim.claimId
) hangupHistory_
ON ch.id = hangupHistory_.claimId
WHERE 1 = 1
AND a.is_deleted = 'N'
AND a.report_no = ?
AND c.customer_insure_role = 1
AND d.customer_insure_role = 2
ORDER BY CASE
WHEN hce.loss_cause_ext IS NULL THEN 0
ELSE
CASE
WHEN LOCATE('3', hce.loss_cause_ext) > 0 THEN 1
ELSE 0
END
END DESC, TimeStampDiff(SECOND, a.gmt_created, now()) -
(ifnull(hangupClaim_.hangupClaimSeconds, 0) +
ifnull(hangupHistory_.hangupClaimSeconds, 0)) DESC, a.gmt_modified DESC
LIMIT 0, 10
```
error1:
```
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment
cannot be cast to
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.LiteralExpressionSegment
at
org.apache.shardingsphere.sql.parser.mysql.visitor.statement.impl.MySQLStatementSQLVisitor.visitPositionFunction(MySQLStatementSQLVisitor.java:913)
at
org.apache.shardingsphere.sql.parser.mysql.visitor.statement.impl.MySQLStatementSQLVisitor.visitPositionFunction(MySQLStatementSQLVisitor.java:226)
at
org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$PositionFunctionContext.accept(MySQLStatementParser.java:41834)
```
Sql2:
```
SELECT a.endorsement_detail_type, a.name, a.hire_date, a.effective_time,
a.expiry_time
, a.cert_type, a.cert_no, a.plan_name, a.plan_id, a.age
, a.occupation_name, a.occupation_type, a.occupation_Code,
a.cert_verify_res, d.change_proportion
, d.difference, d.effective_date, d.id AS endorsementDetailId,
c.rule_codes, c.success
, a.id, d.manual_reason, a.medical_staff_position, a.surrender_time
FROM hl_endorsement_person a
JOIN hl_endorsement_detail d ON a.endorsement_detail_id = d.id
LEFT JOIN (
SELECT b.object_id, b.rule_execute_result_id, GROUP_CONCAT(CASE
WHEN b.is_success = 'N' THEN b.rule_code
END) AS rule_codes
, CASE
WHEN LOCATE('N', GROUP_CONCAT(b.is_success)) >
0 THEN 'N'
ELSE 'Y'
END AS success
FROM hl_rule_execute_result_details b
WHERE b.rule_execute_result_id = (
SELECT id
FROM hl_rule_execute_result
WHERE object_type = 3
AND object_id = 64365002
ORDER BY gmt_created DESC
LIMIT 1
)
AND b.object_type = 2
GROUP BY object_id
) c
ON c.object_id = a.id
WHERE a.is_deleted = 'N'
AND a.endorsement_id = 64365002
```
error2:
```
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.FunctionSegment
cannot be cast to
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.simple.LiteralExpressionSegment
at
org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:96)
```
Can anyone help me take a look?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
