Cross year aggregation failure

### Which version of ShardingSphere did you use?
`

org.apache.shardingsphere
shardingsphere-jdbc-core-spring-boot-starter
5.2.0

`
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
I have an aggregated SQL statement for cross year queries
`
SELECT
COUNT( CASE WHEN mo.order_status = 1 THEN 1 END ) AS orderCount,
IFNULL( SUM( CASE WHEN mo.order_status = 1 THEN mo.order_amount ELSE 0 END ), 0 ) AS orderAmount,
COUNT( CASE WHEN mo.refund_flag = 1 THEN 1 END ) AS refundCount,
IFNULL( SUM( CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END ), 0 ) AS refundAmount,
IFNULL( SUM( CASE WHEN mo.order_status = 1 THEN mo.pay_amount ELSE 0 END ), 0 ) AS payAmount,
IFNULL( SUM( CASE WHEN mo.order_status = 1 THEN mo.discount_amount ELSE 0 END ), 0 ) AS discountAmount,
IFNULL( SUM( CASE WHEN mo.order_status = 1 THEN mo.subsidy_amount ELSE 0 END ), 0 ) AS subsidyAmount,
IFNULL( SUM( CASE WHEN mo.order_status = 1 THEN mo.coupo_amount ELSE 0 END ), 0 ) AS coupoAmount,
IFNULL( SUM( CASE WHEN mo.order_status = 1 THEN mo.deduction_amount ELSE 0 END ), 0 ) AS deductionAmount
FROM
meal_order mo
WHERE
mo.del_flag = 0
AND mo.enterprise_id = '2088720477654859'
AND ( mo.department_ids = '1001085010986913' OR mo.department_ids IN ( SELECT department_id FROM meal_enterprise_department WHERE find_in_set('1001085010986913', ancestors )) )
AND mo.meal_date >= '2025-12-01'
AND mo.meal_date <= '2026-01-01'
`

### Actual behavior
I divided the database and shards based on the mean_date field
Mean_order is the data for 2026, for other years such as mean_order_2025 and mean_order_2024
The current issue is that my query from December 1, 2025 to January 1, 2026 theoretically includes data for December and January 1, 26. However, due to the lack of data on January 1, there is a problem with aggregation. The resulting data is 10018, 0, 0, 0, 0, 0, 0, 0, 0. Only orderCount has data and the sum seems to be invalid,
When I searched from December 1, 2025 to January 4, 2026, because there were indeed consumption records on the 4th, the query was normal again. 10467, 556500, 0, 0, 25, 1300, 300, 0, 0, 304, 900. The current problem is why the aggregation for December would be invalid even if there is no data on the 1st?
The first SQL statement is:
`
meal ::: SELECT
COUNT(CASE WHEN mo.order_status = 1
THEN 1 END) AS orderCount,
IFNULL(SUM(CASE WHEN mo.order_status = 1
THEN mo.order_amount ELSE 0 END), 0) AS orderAmount,
COUNT(CASE WHEN mo.refund_flag = 1 THEN 1 END) AS refundCount,
IFNULL(SUM(CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END), 0) AS refundAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1
THEN mo.pay_amount ELSE 0 END), 0) AS payAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1
THEN mo.discount_amount ELSE 0 END), 0) AS discountAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1
THEN mo.subsidy_amount ELSE 0 END), 0) AS subsidyAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1
THEN mo.coupo_amount ELSE 0 END), 0) AS coupoAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1
THEN mo.deduction_amount ELSE 0 END), 0) AS deductionAmount
FROM meal_order mo
where mo.del_flag = 0
AND mo.enterprise_id = ?
AND (mo.department_ids = ? OR mo.department_ids IN (SELECT department_id
FROM meal_enterprise_department
WHERE find_in_set(?, ancestors))
)
AND mo.meal_date >= ?
AND mo.meal_date <= ? ::: [2088720477654859, 1001085010986913, 1001085010986913, 2025-12-01, 2026-01-01]
`
The second SQL statement is:
`
meal ::: SELECT
COUNT(CASE WHEN mo.order_status = 1
THEN 1 END) AS orderCount,
IFNULL(SUM(CASE WHEN mo.order_status = 1
THEN mo.order_amount ELSE 0 END), 0) AS orderAmount,
COUNT(CASE WHEN mo.refund_flag = 1 THEN 1 END) AS refundCount,
IFNULL(SUM(CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END), 0) AS refundAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1
THEN mo.pay_amount ELSE 0 END), 0) AS payAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1
THEN mo.discount_amount ELSE 0 END), 0) AS discountAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1
THEN mo.subsidy_amount ELSE 0 END), 0) AS subsidyAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1
THEN mo.coupo_amount ELSE 0 END), 0) AS coupoAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1
THEN mo.deduction_amount ELSE 0 END), 0) AS deductionAmount
FROM meal_order mo
where mo.del_flag = 0
AND mo.enterprise_id = ?
AND (mo.department_ids = ? OR mo.department_ids IN (SELECT department_id
FROM meal_enterprise_department
WHERE find_in_set(?, ancestors))
)
AND mo.meal_date >= ?
AND mo.meal_date <= ? ::: [2088720477654859, 1001085010986913, 1001085010986913, 2025-12-01, 2026-01-04]
`
### Reason analyze (If you can)

### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

### Example codes for reproduce this issue (such as a github link).
The first SQL log is:
`
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5329d30b] was not registered for synchronization because synchronization is not active
JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@7aed069c] will not be managed by Spring
==> Preparing: SELECT COUNT(CASE WHEN mo.order_status = 1 THEN 1 END) AS orderCount, IFNULL(SUM(CASE WHEN mo.order_status = 1 THEN mo.order_amount ELSE 0 END), 0) AS orderAmount, COUNT(CASE WHEN mo.refund_flag = 1 THEN 1 END) AS refundCount, IFNULL(SUM(CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END), 0) AS refundAmount, IFNULL(SUM(CASE WHEN mo.order_status = 1 THEN mo.pay_amount ELSE 0 END), 0) AS payAmount, IFNULL(SUM(CASE WHEN mo.order_status = 1 THEN mo.discount_amount ELSE 0 END), 0) AS discountAmount, IFNULL(SUM(CASE WHEN mo.order_status = 1 THEN mo.subsidy_amount ELSE 0 END), 0) AS subsidyAmount, IFNULL(SUM(CASE WHEN mo.order_status = 1 THEN mo.coupo_amount ELSE 0 END), 0) AS coupoAmount, IFNULL(SUM(CASE WHEN mo.order_status = 1 THEN mo.deduction_amount ELSE 0 END), 0) AS deductionAmount FROM meal_order mo where mo.del_flag = 0 AND mo.enterprise_id = ? AND (mo.department_ids = ? OR mo.department_ids IN (SELECT department_id FROM meal_enterprise_department WHERE find_in_set(?, ancestors)) ) AND mo.meal_date >= ? AND mo.meal_date <= ?
==> Parameters: 2088720477654859(String), 1001085010986913(String), 1001085010986913(String), 2025-12-01(String), 2026-01-01(String)
14:25:29.061 [http-nio-9204-exec-9] INFO ShardingSphere-SQL - [log,74] - Logic SQL: SELECT

COUNT(CASE WHEN mo.order_status = 1

THEN 1 END) AS orderCount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.order_amount ELSE 0 END), 0) AS orderAmount,
COUNT(CASE WHEN mo.refund_flag = 1 THEN 1 END) AS refundCount,
IFNULL(SUM(CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END), 0) AS refundAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.pay_amount ELSE 0 END), 0) AS payAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.discount_amount ELSE 0 END), 0) AS discountAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.subsidy_amount ELSE 0 END), 0) AS subsidyAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.coupo_amount ELSE 0 END), 0) AS coupoAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.deduction_amount ELSE 0 END), 0) AS deductionAmount

FROM meal_order mo
where mo.del_flag = 0

AND mo.enterprise_id = ?





AND (mo.department_ids = ? OR mo.department_ids IN (SELECT department_id
FROM meal_enterprise_department
WHERE find_in_set(?, ancestors))
)



AND mo.meal_date >= ?


AND mo.meal_date <= ?
14:25:29.061 [http-nio-9204-exec-9] INFO ShardingSphere-SQL - [log,74] - SQLStatement: MySQLSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=5, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=1729, stopIndex=1729, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=1818, stopIndex=1818, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=1940, stopIndex=1940, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=2031, stopIndex=2031, parameterMarkerIndex=3, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=2085, stopIndex=2085, parameterMarkerIndex=4, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), projections=ProjectionsSegment(startIndex=25, stopIndex=1619, projections=[AggregationProjectionSegment(startIndex=25, stopIndex=157, type=COUNT, innerExpression=(CASE WHEN mo.order_status = 1

THEN 1 END), parameters=[CommonExpressionSegment(startIndex=31, stopIndex=156, text=CASE WHEN mo.order_status = 1

THEN 1 END)], alias=Optional[orderCount]), ExpressionProjectionSegment(startIndex=218, stopIndex=406, text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.order_amount ELSE 0 END), 0), expr=FunctionSegment(startIndex=218, stopIndex=380, functionName=IFNULL, parameters=[AggregationProjectionSegment(startIndex=225, stopIndex=376, type=SUM, innerExpression=(CASE WHEN mo.order_status = 1

THEN mo.order_amount ELSE 0 END), parameters=[CommonExpressionSegment(startIndex=229, stopIndex=375, text=CASE WHEN mo.order_status = 1

THEN mo.order_amount ELSE 0 END)], alias=Optional.empty), LiteralExpressionSegment(startIndex=379, stopIndex=379, literals=0)], text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.order_amount ELSE 0 END), 0), owner=null), alias=Optional[orderAmount]), AggregationProjectionSegment(startIndex=417, stopIndex=462, type=COUNT, innerExpression=(CASE WHEN mo.refund_flag = 1 THEN 1 END), parameters=[CommonExpressionSegment(startIndex=423, stopIndex=461, text=CASE WHEN mo.refund_flag = 1 THEN 1 END)], alias=Optional[refundCount]), ExpressionProjectionSegment(startIndex=522, stopIndex=617, text=IFNULL(SUM(CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END), 0), expr=FunctionSegment(startIndex=522, stopIndex=597, functionName=IFNULL, parameters=[AggregationProjectionSegment(startIndex=529, stopIndex=593, type=SUM, innerExpression=(CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END), parameters=[CommonExpressionSegment(startIndex=533, stopIndex=592, text=CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END)], alias=Optional.empty), LiteralExpressionSegment(startIndex=596, stopIndex=596, literals=0)], text=IFNULL(SUM(CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END), 0), owner=null), alias=Optional[refundAmount]), ExpressionProjectionSegment(startIndex=628, stopIndex=814, text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.pay_amount ELSE 0 END), 0), expr=FunctionSegment(startIndex=628, stopIndex=788, functionName=IFNULL, parameters=[AggregationProjectionSegment(startIndex=635, stopIndex=784, type=SUM, innerExpression=(CASE WHEN mo.order_status = 1

THEN mo.pay_amount ELSE 0 END), parameters=[CommonExpressionSegment(startIndex=639, stopIndex=783, text=CASE WHEN mo.order_status = 1

THEN mo.pay_amount ELSE 0 END)], alias=Optional.empty), LiteralExpressionSegment(startIndex=787, stopIndex=787, literals=0)], text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.pay_amount ELSE 0 END), 0), owner=null), alias=Optional[payAmount]), ExpressionProjectionSegment(startIndex=825, stopIndex=1016, text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.discount_amount ELSE 0 END), 0), expr=FunctionSegment(startIndex=825, stopIndex=990, functionName=IFNULL, parameters=[AggregationProjectionSegment(startIndex=832, stopIndex=986, type=SUM, innerExpression=(CASE WHEN mo.order_status = 1

THEN mo.discount_amount ELSE 0 END), parameters=[CommonExpressionSegment(startIndex=836, stopIndex=985, text=CASE WHEN mo.order_status = 1

THEN mo.discount_amount ELSE 0 END)], alias=Optional.empty), LiteralExpressionSegment(startIndex=989, stopIndex=989, literals=0)], text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.discount_amount ELSE 0 END), 0), owner=null), alias=Optional[discountAmount]), ExpressionProjectionSegment(startIndex=1027, stopIndex=1217, text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.subsidy_amount ELSE 0 END), 0), expr=FunctionSegment(startIndex=1027, stopIndex=1191, functionName=IFNULL, parameters=[AggregationProjectionSegment(startIndex=1034, stopIndex=1187, type=SUM, innerExpression=(CASE WHEN mo.order_status = 1

THEN mo.subsidy_amount ELSE 0 END), parameters=[CommonExpressionSegment(startIndex=1038, stopIndex=1186, text=CASE WHEN mo.order_status = 1

THEN mo.subsidy_amount ELSE 0 END)], alias=Optional.empty), LiteralExpressionSegment(startIndex=1190, stopIndex=1190, literals=0)], text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.subsidy_amount ELSE 0 END), 0), owner=null), alias=Optional[subsidyAmount]), ExpressionProjectionSegment(startIndex=1228, stopIndex=1416, text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.coupo_amount ELSE 0 END), 0), expr=FunctionSegment(startIndex=1228, stopIndex=1390, functionName=IFNULL, parameters=[AggregationProjectionSegment(startIndex=1235, stopIndex=1386, type=SUM, innerExpression=(CASE WHEN mo.order_status = 1

THEN mo.coupo_amount ELSE 0 END), parameters=[CommonExpressionSegment(startIndex=1239, stopIndex=1385, text=CASE WHEN mo.order_status = 1

THEN mo.coupo_amount ELSE 0 END)], alias=Optional.empty), LiteralExpressionSegment(startIndex=1389, stopIndex=1389, literals=0)], text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.coupo_amount ELSE 0 END), 0), owner=null), alias=Optional[coupoAmount]), ExpressionProjectionSegment(startIndex=1427, stopIndex=1619, text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.deduction_amount ELSE 0 END), 0), expr=FunctionSegment(startIndex=1427, stopIndex=1593, functionName=IFNULL, parameters=[AggregationProjectionSegment(startIndex=1434, stopIndex=1589, type=SUM, innerExpression=(CASE WHEN mo.order_status = 1

THEN mo.deduction_amount ELSE 0 END), parameters=[CommonExpressionSegment(startIndex=1438, stopIndex=1588, text=CASE WHEN mo.order_status = 1

THEN mo.deduction_amount ELSE 0 END)], alias=Optional.empty), LiteralExpressionSegment(startIndex=1592, stopIndex=1592, literals=0)], text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.deduction_amount ELSE 0 END), 0), owner=null), alias=Optional[deductionAmount])], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=1640, stopIndex=1649, identifier=IdentifierValue(value=meal_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional[mo]), where=Optional[WhereSegment(startIndex=1662, stopIndex=2085, expr=BinaryOperationExpression(startIndex=1668, stopIndex=2085, left=BinaryOperationExpression(startIndex=1668, stopIndex=2031, left=BinaryOperationExpression(startIndex=1668, stopIndex=1967, left=BinaryOperationExpression(startIndex=1668, stopIndex=1729, left=BinaryOperationExpression(startIndex=1668, stopIndex=1682, left=ColumnSegment(startIndex=1668, stopIndex=1678, identifier=IdentifierValue(value=del_flag, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=1668, stopIndex=1669, identifier=IdentifierValue(value=mo, quoteCharacter=NONE), owner=Optional.empty)]), right=LiteralExpressionSegment(startIndex=1682, stopIndex=1682, literals=0), operator==, text=mo.del_flag = 0), right=BinaryOperationExpression(startIndex=1710, stopIndex=1729, left=ColumnSegment(startIndex=1710, stopIndex=1725, identifier=IdentifierValue(value=enterprise_id, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=1710, stopIndex=1711, identifier=IdentifierValue(value=mo, quoteCharacter=NONE), owner=Optional.empty)]), right=ParameterMarkerExpressionSegment(startIndex=1729, stopIndex=1729, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), operator==, text=mo.enterprise_id = ?), operator=AND, text=mo.del_flag = 0

AND mo.enterprise_id = ?), right=BinaryOperationExpression(startIndex=1798, stopIndex=1953, left=BinaryOperationExpression(startIndex=1798, stopIndex=1818, left=ColumnSegment(startIndex=1798, stopIndex=1814, identifier=IdentifierValue(value=department_ids, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=1798, stopIndex=1799, identifier=IdentifierValue(value=mo, quoteCharacter=NONE), owner=Optional.empty)]), right=ParameterMarkerExpressionSegment(startIndex=1818, stopIndex=1818, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), operator==, text=mo.department_ids = ?), right=InExpression(startIndex=1823, stopIndex=1953, left=ColumnSegment(startIndex=1823, stopIndex=1839, identifier=IdentifierValue(value=department_ids, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=1823, stopIndex=1824, identifier=IdentifierValue(value=mo, quoteCharacter=NONE), owner=Optional.empty)]), right=SubqueryExpressionSegment(subquery=SubquerySegment(startIndex=1844, stopIndex=1953, select=MySQLSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=3, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=1729, stopIndex=1729, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=1818, stopIndex=1818, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=1940, stopIndex=1940, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), projections=ProjectionsSegment(startIndex=1852, stopIndex=1864, projections=[ColumnProjectionSegment(column=ColumnSegment(startIndex=1852, stopIndex=1864, identifier=IdentifierValue(value=department_id, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty)], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=1883, stopIndex=1908, identifier=IdentifierValue(value=meal_enterprise_department, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional[WhereSegment(startIndex=1922, stopIndex=1952, expr=FunctionSegment(startIndex=1928, stopIndex=1952, functionName=find_in_set, parameters=[ParameterMarkerExpressionSegment(startIndex=1940, stopIndex=1940, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty), ColumnSegment(startIndex=1943, stopIndex=1951, identifier=IdentifierValue(value=ancestors, quoteCharacter=NONE), owner=Optional.empty)], text=find_in_set(?, ancestors), owner=null))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combines=[]), table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty), subqueryType=PREDICATE_SUBQUERY)), not=false), operator=OR, text=mo.department_ids = ? OR mo.department_ids IN (SELECT department_id
FROM meal_enterprise_department
WHERE find_in_set(?, ancestors))), operator=AND, text=mo.del_flag = 0

AND mo.enterprise_id = ?





AND (mo.department_ids = ? OR mo.department_ids IN (SELECT department_id
FROM meal_enterprise_department
WHERE find_in_set(?, ancestors))
)), right=BinaryOperationExpression(startIndex=2015, stopIndex=2031, left=ColumnSegment(startIndex=2015, stopIndex=2026, identifier=IdentifierValue(value=meal_date, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=2015, stopIndex=2016, identifier=IdentifierValue(value=mo, quoteCharacter=NONE), owner=Optional.empty)]), right=ParameterMarkerExpressionSegment(startIndex=2031, stopIndex=2031, parameterMarkerIndex=3, parameterMarkerType=QUESTION, alias=Optional.empty), operator=>=, text=mo.meal_date >= ?), operator=AND, text=mo.del_flag = 0

AND mo.enterprise_id = ?





AND (mo.department_ids = ? OR mo.department_ids IN (SELECT department_id
FROM meal_enterprise_department
WHERE find_in_set(?, ancestors))
)



AND mo.meal_date >= ?), right=BinaryOperationExpression(startIndex=2069, stopIndex=2085, left=ColumnSegment(startIndex=2069, stopIndex=2080, identifier=IdentifierValue(value=meal_date, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=2069, stopIndex=2070, identifier=IdentifierValue(value=mo, quoteCharacter=NONE), owner=Optional.empty)]), right=ParameterMarkerExpressionSegment(startIndex=2085, stopIndex=2085, parameterMarkerIndex=4, parameterMarkerType=QUESTION, alias=Optional.empty), operator=<=, text=mo.meal_date <= ?), operator=AND, text=mo.del_flag = 0

AND mo.enterprise_id = ?





AND (mo.department_ids = ? OR mo.department_ids IN (SELECT department_id
FROM meal_enterprise_department
WHERE find_in_set(?, ancestors))
)



AND mo.meal_date >= ?


AND mo.meal_date <= ?))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combines=[]), table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
14:25:29.063 [http-nio-9204-exec-9] INFO ShardingSphere-SQL - [log,74] - Actual SQL: meal ::: SELECT

COUNT(CASE WHEN mo.order_status = 1

THEN 1 END) AS orderCount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.order_amount ELSE 0 END), 0) AS orderAmount,
COUNT(CASE WHEN mo.refund_flag = 1 THEN 1 END) AS refundCount,
IFNULL(SUM(CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END), 0) AS refundAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.pay_amount ELSE 0 END), 0) AS payAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.discount_amount ELSE 0 END), 0) AS discountAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.subsidy_amount ELSE 0 END), 0) AS subsidyAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.coupo_amount ELSE 0 END), 0) AS coupoAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.deduction_amount ELSE 0 END), 0) AS deductionAmount

FROM meal_order mo
where mo.del_flag = 0

AND mo.enterprise_id = ?





AND (mo.department_ids = ? OR mo.department_ids IN (SELECT department_id
FROM meal_enterprise_department
WHERE find_in_set(?, ancestors))
)



AND mo.meal_date >= ?


AND mo.meal_date <= ? ::: [2088720477654859, 1001085010986913, 1001085010986913, 2025-12-01, 2026-01-01]
14:25:29.063 [http-nio-9204-exec-9] INFO ShardingSphere-SQL - [log,74] - Actual SQL: meal ::: SELECT

COUNT(CASE WHEN mo.order_status = 1

THEN 1 END) AS orderCount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.order_amount ELSE 0 END), 0) AS orderAmount,
COUNT(CASE WHEN mo.refund_flag = 1 THEN 1 END) AS refundCount,
IFNULL(SUM(CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END), 0) AS refundAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.pay_amount ELSE 0 END), 0) AS payAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.discount_amount ELSE 0 END), 0) AS discountAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.subsidy_amount ELSE 0 END), 0) AS subsidyAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.coupo_amount ELSE 0 END), 0) AS coupoAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.deduction_amount ELSE 0 END), 0) AS deductionAmount

FROM meal_order_2025 mo
where mo.del_flag = 0

AND mo.enterprise_id = ?





AND (mo.department_ids = ? OR mo.department_ids IN (SELECT department_id
FROM meal_enterprise_department
WHERE find_in_set(?, ancestors))
)



AND mo.meal_date >= ?


AND mo.meal_date <= ? ::: [2088720477654859, 1001085010986913, 1001085010986913, 2025-12-01, 2026-01-01]
<== Columns: orderCount, orderAmount, refundCount, refundAmount, payAmount, discountAmount, subsidyAmount, coupoAmount, deductionAmount
<== Row: 10018, 0, 0, 0, 0, 0, 0, 0, 0
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5329d30b]
`
The second SQL log is:
`
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4e8615d8] was not registered for synchronization because synchronization is not active
JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@36340fc9] will not be managed by Spring
==> Preparing: SELECT COUNT(CASE WHEN mo.order_status = 1 THEN 1 END) AS orderCount, IFNULL(SUM(CASE WHEN mo.order_status = 1 THEN mo.order_amount ELSE 0 END), 0) AS orderAmount, COUNT(CASE WHEN mo.refund_flag = 1 THEN 1 END) AS refundCount, IFNULL(SUM(CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END), 0) AS refundAmount, IFNULL(SUM(CASE WHEN mo.order_status = 1 THEN mo.pay_amount ELSE 0 END), 0) AS payAmount, IFNULL(SUM(CASE WHEN mo.order_status = 1 THEN mo.discount_amount ELSE 0 END), 0) AS discountAmount, IFNULL(SUM(CASE WHEN mo.order_status = 1 THEN mo.subsidy_amount ELSE 0 END), 0) AS subsidyAmount, IFNULL(SUM(CASE WHEN mo.order_status = 1 THEN mo.coupo_amount ELSE 0 END), 0) AS coupoAmount, IFNULL(SUM(CASE WHEN mo.order_status = 1 THEN mo.deduction_amount ELSE 0 END), 0) AS deductionAmount FROM meal_order mo where mo.del_flag = 0 AND mo.enterprise_id = ? AND (mo.department_ids = ? OR mo.department_ids IN (SELECT department_id FROM meal_enterprise_department WHERE find_in_set(?, ancestors)) ) AND mo.meal_date >= ? AND mo.meal_date <= ?
==> Parameters: 2088720477654859(String), 1001085010986913(String), 1001085010986913(String), 2025-12-01(String), 2026-01-04(String)
14:27:18.306 [http-nio-9204-exec-6] INFO ShardingSphere-SQL - [log,74] - Logic SQL: SELECT

COUNT(CASE WHEN mo.order_status = 1

THEN 1 END) AS orderCount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.order_amount ELSE 0 END), 0) AS orderAmount,
COUNT(CASE WHEN mo.refund_flag = 1 THEN 1 END) AS refundCount,
IFNULL(SUM(CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END), 0) AS refundAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.pay_amount ELSE 0 END), 0) AS payAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.discount_amount ELSE 0 END), 0) AS discountAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.subsidy_amount ELSE 0 END), 0) AS subsidyAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.coupo_amount ELSE 0 END), 0) AS coupoAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.deduction_amount ELSE 0 END), 0) AS deductionAmount

FROM meal_order mo
where mo.del_flag = 0

AND mo.enterprise_id = ?





AND (mo.department_ids = ? OR mo.department_ids IN (SELECT department_id
FROM meal_enterprise_department
WHERE find_in_set(?, ancestors))
)



AND mo.meal_date >= ?


AND mo.meal_date <= ?
14:27:18.306 [http-nio-9204-exec-6] INFO ShardingSphere-SQL - [log,74] - SQLStatement: MySQLSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=5, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=1729, stopIndex=1729, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=1818, stopIndex=1818, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=1940, stopIndex=1940, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=2031, stopIndex=2031, parameterMarkerIndex=3, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=2085, stopIndex=2085, parameterMarkerIndex=4, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), projections=ProjectionsSegment(startIndex=25, stopIndex=1619, projections=[AggregationProjectionSegment(startIndex=25, stopIndex=157, type=COUNT, innerExpression=(CASE WHEN mo.order_status = 1

THEN 1 END), parameters=[CommonExpressionSegment(startIndex=31, stopIndex=156, text=CASE WHEN mo.order_status = 1

THEN 1 END)], alias=Optional[orderCount]), ExpressionProjectionSegment(startIndex=218, stopIndex=406, text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.order_amount ELSE 0 END), 0), expr=FunctionSegment(startIndex=218, stopIndex=380, functionName=IFNULL, parameters=[AggregationProjectionSegment(startIndex=225, stopIndex=376, type=SUM, innerExpression=(CASE WHEN mo.order_status = 1

THEN mo.order_amount ELSE 0 END), parameters=[CommonExpressionSegment(startIndex=229, stopIndex=375, text=CASE WHEN mo.order_status = 1

THEN mo.order_amount ELSE 0 END)], alias=Optional.empty), LiteralExpressionSegment(startIndex=379, stopIndex=379, literals=0)], text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.order_amount ELSE 0 END), 0), owner=null), alias=Optional[orderAmount]), AggregationProjectionSegment(startIndex=417, stopIndex=462, type=COUNT, innerExpression=(CASE WHEN mo.refund_flag = 1 THEN 1 END), parameters=[CommonExpressionSegment(startIndex=423, stopIndex=461, text=CASE WHEN mo.refund_flag = 1 THEN 1 END)], alias=Optional[refundCount]), ExpressionProjectionSegment(startIndex=522, stopIndex=617, text=IFNULL(SUM(CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END), 0), expr=FunctionSegment(startIndex=522, stopIndex=597, functionName=IFNULL, parameters=[AggregationProjectionSegment(startIndex=529, stopIndex=593, type=SUM, innerExpression=(CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END), parameters=[CommonExpressionSegment(startIndex=533, stopIndex=592, text=CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END)], alias=Optional.empty), LiteralExpressionSegment(startIndex=596, stopIndex=596, literals=0)], text=IFNULL(SUM(CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END), 0), owner=null), alias=Optional[refundAmount]), ExpressionProjectionSegment(startIndex=628, stopIndex=814, text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.pay_amount ELSE 0 END), 0), expr=FunctionSegment(startIndex=628, stopIndex=788, functionName=IFNULL, parameters=[AggregationProjectionSegment(startIndex=635, stopIndex=784, type=SUM, innerExpression=(CASE WHEN mo.order_status = 1

THEN mo.pay_amount ELSE 0 END), parameters=[CommonExpressionSegment(startIndex=639, stopIndex=783, text=CASE WHEN mo.order_status = 1

THEN mo.pay_amount ELSE 0 END)], alias=Optional.empty), LiteralExpressionSegment(startIndex=787, stopIndex=787, literals=0)], text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.pay_amount ELSE 0 END), 0), owner=null), alias=Optional[payAmount]), ExpressionProjectionSegment(startIndex=825, stopIndex=1016, text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.discount_amount ELSE 0 END), 0), expr=FunctionSegment(startIndex=825, stopIndex=990, functionName=IFNULL, parameters=[AggregationProjectionSegment(startIndex=832, stopIndex=986, type=SUM, innerExpression=(CASE WHEN mo.order_status = 1

THEN mo.discount_amount ELSE 0 END), parameters=[CommonExpressionSegment(startIndex=836, stopIndex=985, text=CASE WHEN mo.order_status = 1

THEN mo.discount_amount ELSE 0 END)], alias=Optional.empty), LiteralExpressionSegment(startIndex=989, stopIndex=989, literals=0)], text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.discount_amount ELSE 0 END), 0), owner=null), alias=Optional[discountAmount]), ExpressionProjectionSegment(startIndex=1027, stopIndex=1217, text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.subsidy_amount ELSE 0 END), 0), expr=FunctionSegment(startIndex=1027, stopIndex=1191, functionName=IFNULL, parameters=[AggregationProjectionSegment(startIndex=1034, stopIndex=1187, type=SUM, innerExpression=(CASE WHEN mo.order_status = 1

THEN mo.subsidy_amount ELSE 0 END), parameters=[CommonExpressionSegment(startIndex=1038, stopIndex=1186, text=CASE WHEN mo.order_status = 1

THEN mo.subsidy_amount ELSE 0 END)], alias=Optional.empty), LiteralExpressionSegment(startIndex=1190, stopIndex=1190, literals=0)], text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.subsidy_amount ELSE 0 END), 0), owner=null), alias=Optional[subsidyAmount]), ExpressionProjectionSegment(startIndex=1228, stopIndex=1416, text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.coupo_amount ELSE 0 END), 0), expr=FunctionSegment(startIndex=1228, stopIndex=1390, functionName=IFNULL, parameters=[AggregationProjectionSegment(startIndex=1235, stopIndex=1386, type=SUM, innerExpression=(CASE WHEN mo.order_status = 1

THEN mo.coupo_amount ELSE 0 END), parameters=[CommonExpressionSegment(startIndex=1239, stopIndex=1385, text=CASE WHEN mo.order_status = 1

THEN mo.coupo_amount ELSE 0 END)], alias=Optional.empty), LiteralExpressionSegment(startIndex=1389, stopIndex=1389, literals=0)], text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.coupo_amount ELSE 0 END), 0), owner=null), alias=Optional[coupoAmount]), ExpressionProjectionSegment(startIndex=1427, stopIndex=1619, text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.deduction_amount ELSE 0 END), 0), expr=FunctionSegment(startIndex=1427, stopIndex=1593, functionName=IFNULL, parameters=[AggregationProjectionSegment(startIndex=1434, stopIndex=1589, type=SUM, innerExpression=(CASE WHEN mo.order_status = 1

THEN mo.deduction_amount ELSE 0 END), parameters=[CommonExpressionSegment(startIndex=1438, stopIndex=1588, text=CASE WHEN mo.order_status = 1

THEN mo.deduction_amount ELSE 0 END)], alias=Optional.empty), LiteralExpressionSegment(startIndex=1592, stopIndex=1592, literals=0)], text=IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.deduction_amount ELSE 0 END), 0), owner=null), alias=Optional[deductionAmount])], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=1640, stopIndex=1649, identifier=IdentifierValue(value=meal_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional[mo]), where=Optional[WhereSegment(startIndex=1662, stopIndex=2085, expr=BinaryOperationExpression(startIndex=1668, stopIndex=2085, left=BinaryOperationExpression(startIndex=1668, stopIndex=2031, left=BinaryOperationExpression(startIndex=1668, stopIndex=1967, left=BinaryOperationExpression(startIndex=1668, stopIndex=1729, left=BinaryOperationExpression(startIndex=1668, stopIndex=1682, left=ColumnSegment(startIndex=1668, stopIndex=1678, identifier=IdentifierValue(value=del_flag, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=1668, stopIndex=1669, identifier=IdentifierValue(value=mo, quoteCharacter=NONE), owner=Optional.empty)]), right=LiteralExpressionSegment(startIndex=1682, stopIndex=1682, literals=0), operator==, text=mo.del_flag = 0), right=BinaryOperationExpression(startIndex=1710, stopIndex=1729, left=ColumnSegment(startIndex=1710, stopIndex=1725, identifier=IdentifierValue(value=enterprise_id, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=1710, stopIndex=1711, identifier=IdentifierValue(value=mo, quoteCharacter=NONE), owner=Optional.empty)]), right=ParameterMarkerExpressionSegment(startIndex=1729, stopIndex=1729, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), operator==, text=mo.enterprise_id = ?), operator=AND, text=mo.del_flag = 0

AND mo.enterprise_id = ?), right=BinaryOperationExpression(startIndex=1798, stopIndex=1953, left=BinaryOperationExpression(startIndex=1798, stopIndex=1818, left=ColumnSegment(startIndex=1798, stopIndex=1814, identifier=IdentifierValue(value=department_ids, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=1798, stopIndex=1799, identifier=IdentifierValue(value=mo, quoteCharacter=NONE), owner=Optional.empty)]), right=ParameterMarkerExpressionSegment(startIndex=1818, stopIndex=1818, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), operator==, text=mo.department_ids = ?), right=InExpression(startIndex=1823, stopIndex=1953, left=ColumnSegment(startIndex=1823, stopIndex=1839, identifier=IdentifierValue(value=department_ids, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=1823, stopIndex=1824, identifier=IdentifierValue(value=mo, quoteCharacter=NONE), owner=Optional.empty)]), right=SubqueryExpressionSegment(subquery=SubquerySegment(startIndex=1844, stopIndex=1953, select=MySQLSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=3, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=1729, stopIndex=1729, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=1818, stopIndex=1818, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=1940, stopIndex=1940, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), projections=ProjectionsSegment(startIndex=1852, stopIndex=1864, projections=[ColumnProjectionSegment(column=ColumnSegment(startIndex=1852, stopIndex=1864, identifier=IdentifierValue(value=department_id, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty)], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=1883, stopIndex=1908, identifier=IdentifierValue(value=meal_enterprise_department, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional[WhereSegment(startIndex=1922, stopIndex=1952, expr=FunctionSegment(startIndex=1928, stopIndex=1952, functionName=find_in_set, parameters=[ParameterMarkerExpressionSegment(startIndex=1940, stopIndex=1940, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty), ColumnSegment(startIndex=1943, stopIndex=1951, identifier=IdentifierValue(value=ancestors, quoteCharacter=NONE), owner=Optional.empty)], text=find_in_set(?, ancestors), owner=null))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combines=[]), table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty), subqueryType=PREDICATE_SUBQUERY)), not=false), operator=OR, text=mo.department_ids = ? OR mo.department_ids IN (SELECT department_id
FROM meal_enterprise_department
WHERE find_in_set(?, ancestors))), operator=AND, text=mo.del_flag = 0

AND mo.enterprise_id = ?





AND (mo.department_ids = ? OR mo.department_ids IN (SELECT department_id
FROM meal_enterprise_department
WHERE find_in_set(?, ancestors))
)), right=BinaryOperationExpression(startIndex=2015, stopIndex=2031, left=ColumnSegment(startIndex=2015, stopIndex=2026, identifier=IdentifierValue(value=meal_date, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=2015, stopIndex=2016, identifier=IdentifierValue(value=mo, quoteCharacter=NONE), owner=Optional.empty)]), right=ParameterMarkerExpressionSegment(startIndex=2031, stopIndex=2031, parameterMarkerIndex=3, parameterMarkerType=QUESTION, alias=Optional.empty), operator=>=, text=mo.meal_date >= ?), operator=AND, text=mo.del_flag = 0

AND mo.enterprise_id = ?





AND (mo.department_ids = ? OR mo.department_ids IN (SELECT department_id
FROM meal_enterprise_department
WHERE find_in_set(?, ancestors))
)



AND mo.meal_date >= ?), right=BinaryOperationExpression(startIndex=2069, stopIndex=2085, left=ColumnSegment(startIndex=2069, stopIndex=2080, identifier=IdentifierValue(value=meal_date, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=2069, stopIndex=2070, identifier=IdentifierValue(value=mo, quoteCharacter=NONE), owner=Optional.empty)]), right=ParameterMarkerExpressionSegment(startIndex=2085, stopIndex=2085, parameterMarkerIndex=4, parameterMarkerType=QUESTION, alias=Optional.empty), operator=<=, text=mo.meal_date <= ?), operator=AND, text=mo.del_flag = 0

AND mo.enterprise_id = ?





AND (mo.department_ids = ? OR mo.department_ids IN (SELECT department_id
FROM meal_enterprise_department
WHERE find_in_set(?, ancestors))
)



AND mo.meal_date >= ?


AND mo.meal_date <= ?))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combines=[]), table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
14:27:18.307 [http-nio-9204-exec-6] INFO ShardingSphere-SQL - [log,74] - Actual SQL: meal ::: SELECT

COUNT(CASE WHEN mo.order_status = 1

THEN 1 END) AS orderCount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.order_amount ELSE 0 END), 0) AS orderAmount,
COUNT(CASE WHEN mo.refund_flag = 1 THEN 1 END) AS refundCount,
IFNULL(SUM(CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END), 0) AS refundAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.pay_amount ELSE 0 END), 0) AS payAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.discount_amount ELSE 0 END), 0) AS discountAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.subsidy_amount ELSE 0 END), 0) AS subsidyAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.coupo_amount ELSE 0 END), 0) AS coupoAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.deduction_amount ELSE 0 END), 0) AS deductionAmount

FROM meal_order mo
where mo.del_flag = 0

AND mo.enterprise_id = ?





AND (mo.department_ids = ? OR mo.department_ids IN (SELECT department_id
FROM meal_enterprise_department
WHERE find_in_set(?, ancestors))
)



AND mo.meal_date >= ?


AND mo.meal_date <= ? ::: [2088720477654859, 1001085010986913, 1001085010986913, 2025-12-01, 2026-01-04]
14:27:18.307 [http-nio-9204-exec-6] INFO ShardingSphere-SQL - [log,74] - Actual SQL: meal ::: SELECT

COUNT(CASE WHEN mo.order_status = 1

THEN 1 END) AS orderCount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.order_amount ELSE 0 END), 0) AS orderAmount,
COUNT(CASE WHEN mo.refund_flag = 1 THEN 1 END) AS refundCount,
IFNULL(SUM(CASE WHEN mo.refund_flag = 1 THEN mo.order_amount ELSE 0 END), 0) AS refundAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.pay_amount ELSE 0 END), 0) AS payAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.discount_amount ELSE 0 END), 0) AS discountAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.subsidy_amount ELSE 0 END), 0) AS subsidyAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.coupo_amount ELSE 0 END), 0) AS coupoAmount,
IFNULL(SUM(CASE WHEN mo.order_status = 1

THEN mo.deduction_amount ELSE 0 END), 0) AS deductionAmount

FROM meal_order_2025 mo
where mo.del_flag = 0

AND mo.enterprise_id = ?





AND (mo.department_ids = ? OR mo.department_ids IN (SELECT department_id
FROM meal_enterprise_department
WHERE find_in_set(?, ancestors))
)



AND mo.meal_date >= ?


AND mo.meal_date <= ? ::: [2088720477654859, 1001085010986913, 1001085010986913, 2025-12-01, 2026-01-04]
<== Columns: orderCount, orderAmount, refundCount, refundAmount, payAmount, discountAmount, subsidyAmount, coupoAmount, deductionAmount
<== Row: 10467, 556500, 0, 0, 251300, 300, 0, 0, 304900
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4e8615d8]

`

Start with the supplied ShardingSphere-JDBC 5.2.0 SQL and ShardingSphere-SQL logs, then reproduce the cross-year aggregation with the two date ranges shown. Compare results when one shard has no matching rows; done means the aggregate values remain correct for the same query range.

Written by the indexing model from the issue text.