You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "weixu120801 (via GitHub)" <gi...@apache.org> on 2026/01/05 06:27:54 UTC
[I] Cross year aggregation failure [shardingsphere]
weixu120801 opened a new issue, #37644:
URL: https://github.com/apache/shardingsphere/issues/37644
### Which version of ShardingSphere did you use?
`
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.2.0</version>
</dependency>
`
### 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 WHER
E 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=1
619, 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(start
Index=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=Subquery
Segment(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, identifie
r=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 WHER
E 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=1
619, 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(start
Index=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=Subquery
Segment(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, identifie
r=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]
`
--
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Cross year aggregation failure [shardingsphere]
Posted by "nirupam-06 (via GitHub)" <gi...@apache.org>.
nirupam-06 commented on issue #37644:
URL: https://github.com/apache/shardingsphere/issues/37644#issuecomment-5206423499
Hi! I'm interested in working on this issue. Could you please assign it to me? Thanks!
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Cross year aggregation failure [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #37644:
URL: https://github.com/apache/shardingsphere/issues/37644#issuecomment-3710032239
### Problem Understanding
Cross-shard aggregation where SUM is wrapped by IFNULL(SUM(...),0) returns only the first shard’s value (0 when that shard is empty), while a top-level COUNT still accumulates across shards.
### Root Cause
The current result-merge pipeline only performs cross-shard aggregation on projections recognized as top-level aggregations. When an aggregation is nested inside an expression (e.g., IFNULL), the entire expression is treated as a normal column, no AggregationUnit is built for the inner SUM, so it is not merged across shards and the first shard’s value “wins.” This aligns with current implementation boundaries rather than user misuse.
### Problem Analysis
- Because only top-level aggregations are merged, nested aggregations fall outside the supported shape and get returned as-is from the first shard. This is a capability gap that can surprise users writing conventional SQL with wrappers like IFNULL/COALESCE.
- To support this SQL shape, the engine would need to detect and merge aggregations even when they are embedded inside expression projections.
### Problem Conclusion
This is a missing capability (feature to be enhanced), not a violation of usage rules by the user. It should be addressed either by (a) enhancing result merge to recognize and merge aggregations inside expressions, and/or (b) clarifying the limitation in the docs until the enhancement lands.
### Design-Level Enhancement Proposal (welcome PRs!)
1. Parsing/context: When handling ExpressionProjectionSegment, recursively extract any AggregationProjectionSegment, assign deterministic aliases, and register them in the projections/merge context (similar to how AVG spawns SUM/COUNT derived columns).
2. Result merge: For extracted aggregations, create AggregationUnits and accumulate per-shard values; after merge, evaluate the original expression (e.g., IFNULL) on the merged row.
3. Column alignment/rewrite: Ensure derived aggregation aliases are available in the result set or via rewrite so merge can fetch them reliably.
4. Tests: Add unit tests for projection extraction and result-merge accumulation; add integration tests with two shards (one empty, one with data) to verify IFNULL(SUM(...),0) aggregates correctly while COUNT remains correct.
5. Documentation: Update the merge section to state the current limitation and, once implemented, document support for aggregations nested inside expressions.
We’d love to see a community PR implementing the above and adding the tests/docs; we’re happy to help review and iterate.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Cross year aggregation failure [shardingsphere]
Posted by "drghdtjr (via GitHub)" <gi...@apache.org>.
drghdtjr commented on issue #37644:
URL: https://github.com/apache/shardingsphere/issues/37644#issuecomment-3739953919
Hi @terrymanu, I have submitted a PR. Please take a look when you have time!
https://github.com/apache/shardingsphere/pull/37722
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org