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?

[iamhucong]

@corgy-w The first issue has already been fixed in #27299. Please provide the full stack trace for the second exception, or try again with the latest version in the master branch.

[corgy-w]

@corgy-w The first issue has already been fixed in #27299. Please provide the full stack trace for the second exception, or try again with the latest version in the master branch.

thk! i try