SQL parsing error

[corgy-w]

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)

version

5.2.1

type

ShardingSphere-JDBC

Can anyone help me take a look?

[terrymanu]

Can you follow the template to complete the missed information?

[corgy-w]

Can you follow the template to complete the missed information?

I'm using the question template so it's a little off.
Add version already type used，thk !

[strongduanmu]

Hi @FlyingZC, can you help check this issue?