Does the SUM function perform a full table scan?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: sum函数会走全表?

| username: TiDBer_hW6qvmyi

【TiDB Usage Environment】Development Environment
【TiDB Version】6.5
【Reproduction Path】SQL Query
【Encountered Problem: Problem Phenomenon and Impact】
Original SQL:
IF(A.SOURCE_TYPE = 1,
(SELECT IFNULL(SUM(G.AMOUNT), 0)
FROM OIS_BILL F,
OIS_BILL_DETAILS G
WHERE F.BIZ_NO = A.ORDER_NO
AND F.STATUS = 1
AND F.BILL_ID = G.BILL_ID
AND F.OPC_ID = A.REG_ID),
(SELECT IFNULL(SUM(PRICE * NUM), 0)
FROM CIS_ORDER_FEE F
WHERE F.ORDER_NO = A.ORDER_NO
AND F.IN_VISIT_ID = A.REG_ID)) REQUEST_FEE
In the execution plan, it scans the entire table and causes memory overflow.
Modified SQL to:
IF
(A.SOURCE_TYPE = 1,
(SELECT IFNULL(SUM((select G.AMOUNT
FROM OIS_BILL F,
OIS_BILL_DETAILS G
WHERE F.BIZ_NO = A.ORDER_NO
AND F.STATUS = 1
AND F.BILL_ID = G.BILL_ID
AND F.OPC_ID = A.REG_ID)
), 0)),
(SELECT IFNULL(
(select SUM(TEMP.PRICE * TEMP.NUM)
from (select PRICE,
NUM
FROM CIS_ORDER_FEE F
WHERE F.IN_VISIT_ID = A.REG_ID
and F.ORDER_NO = A.ORDER_NO) TEMP), 0))
) REQUEST_FEE
All indexes are hit.
Tried several times, and it’s the same. The difference is that without wrapping another layer, aggregate functions like SUM cannot hit the index?
【Resource Configuration】
【Attachments: Screenshots/Logs/Monitoring】

| username: tidb菜鸟一只 | Original post link

Upload the execution plan and take a look.

| username: h5n1 | Original post link

Is this way of writing correct, even though it can be executed?

| username: TiDBer_hW6qvmyi | Original post link

Before modification:

IF(A.SOURCE_TYPE = 1,
   (SELECT IFNULL ( SUM( G.AMOUNT ), 0 ) FROM
                                     OIS_BILL F,
                                     OIS_BILL_DETAILS G
    WHERE
        F.BIZ_NO = A.ORDER_NO
      AND F.STATUS = 1
      AND F.BILL_ID = G.BILL_ID
      AND F.OPC_ID = A.REG_ID
   ),
   (SELECT IFNULL ( SUM( PRICE * NUM ), 0 ) FROM
       CIS_ORDER_FEE F
    WHERE
        F.ORDER_NO = A.ORDER_NO
      AND F.IN_VISIT_ID = A.REG_ID
   )) REQUEST_FEE

After modification:

IF
   (A.SOURCE_TYPE = 1,
    (SELECT IFNULL(SUM((select G.AMOUNT
                        FROM OIS_BILL F,
                             OIS_BILL_DETAILS G
                        WHERE F.BIZ_NO = A.ORDER_NO
                          AND F.STATUS = 1
                          AND F.BILL_ID = G.BILL_ID
                          AND F.OPC_ID = A.REG_ID)
                       ), 0)),
    (SELECT IFNULL(
                    (select SUM(TEMP.PRICE * TEMP.NUM)
                     from (select PRICE,
                                  NUM
                           FROM CIS_ORDER_FEE F
                           WHERE F.IN_VISIT_ID = A.REG_ID
                             and F.ORDER_NO = A.ORDER_NO) TEMP), 0))
   )                                                                 REQUEST_FEE
| username: TiDBer_hW6qvmyi | Original post link

Original SQL

IF(A.SOURCE_TYPE = 1,
(SELECT IFNULL(SUM(G.AMOUNT), 0)
FROM OIS_BILL F,
OIS_BILL_DETAILS G
WHERE F.BIZ_NO = A.ORDER_NO
AND F.STATUS = 1
AND F.BILL_ID = G.BILL_ID
AND F.OPC_ID = A.REG_ID),
(SELECT IFNULL(SUM(PRICE * NUM), 0)
FROM CIS_ORDER_FEE F
WHERE F.ORDER_NO = A.ORDER_NO
AND F.IN_VISIT_ID = A.REG_ID)) REQUEST_FEE

| username: Kongdom | Original post link

Using TiFlash, the speed is incredibly fast.

| username: liuis | Original post link

Why perform function operations in the database? It’s a bit confusing.