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:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
