SQL MAX 函数奇怪的行为

Question

Stackoverflow，你好。
有以下问题。
假设有一个返回以下内容的查询:

当我尝试“选择 max(col1), max(col2)”时，它返回给我:



当我尝试“选择 max(col2),max(col1)”时，它返回给我:



你对这里发生的事情有什么想法吗？

给出问题的查询是

select max(col2),max(col1)
from (

SELECT 
        NVL(IS_IN_OPEN_BR, 0)                             AS col1,
        NVL(IS_IN_OPEN_ISR, 0)                            AS col2
      FROM
        (
        SELECT DISTINCT PRD_DEV_CODE AS DEV_CODE_ISR,
          PRD_GRP_CODE               AS GRP_CODE_ISR,
          1                          AS IS_IN_OPEN_ISR
        FROM ATL_ADM.PRODUCT PRD
        INNER JOIN ATL_ADM.SB_REQ SR
        ON PRD.PRD_ID = SR.SBR_PRD_ID
        INNER JOIN ATL_ADM.LIB_REPLENISH_REQ LSR
        ON LSR.LSR_ID         = SR.SBR_LSR_ID
        WHERE LSR.LSR_STATUS <> 'CLOSED'
        GROUP BY PRD_DEV_CODE,
          PRD_GRP_CODE
        ) ISR
      FULL OUTER JOIN
        (
        SELECT DISTINCT PRD.PRD_DEV_CODE DEV_CODE_SHIP,
          PRD.PRD_GRP_CODE AS GRP_CODE_SHIP,
          NVL( MAX (SHP.SHIP_SHIPDATE), MAX (SHP.SHIP_UPDDATET)) LAST_SHIP_DATE
        FROM ATL_ADM.SB_REQ SR
        JOIN ATL_ADM.SHIPMENT SHP
        ON SR.SBR_ID = SHP.SHIP_SBR_ID
        JOIN ATL_ADM.PRODUCT PRD
        ON PRD.PRD_ID        = SR.SBR_PRD_ID
        WHERE SR.SBR_TYPE    = 'FLA'
        AND SHP.SHIP_STATUS IN ('SHIPPE', 'MANDEV')
        GROUP BY PRD.PRD_DEV_CODE ,
          PRD.PRD_GRP_CODE
        ) SHIPS ON DEV_CODE_ISR = DEV_CODE_SHIP
      FULL OUTER JOIN
        (
            SELECT DISTINCT 
                PRODUCT.PRD_DEV_CODE  DEV_CODE_BR,
                PRODUCT.PRD_GRP_CODE GRP_CODE_BR,
                1 IS_IN_OPEN_BR
            FROM ATL_ADM.PRODUCT
            JOIN ATL_ADM.SB_REQ
            ON PRODUCT.PRD_ID = SB_REQ.SBR_PRD_ID
            JOIN ATL_ADM.REQT_SET
            ON REQT_SET.REQTS_ID = SB_REQ.SBR_RS_ID
            JOIN ATL_ADM.BRIEF
            ON BRIEF.BR_ID = REQT_SET.REQTS_BR_ID
            WHERE BRIEF.BR_STATUS_CODE NOT IN ('CLOSED', 'CANCEL')
            GROUP BY PRODUCT.PRD_GRP_CODE,
            PRODUCT.PRD_DEV_CODE,
            BRIEF.BR_STATUS_CODE
        ) BRS ON DEV_CODE_BR = DEV_CODE_SHIP)

Answer 1

使用这个提示:

select /*+ no_query_transformation */
          max(col1), max(col2)
from  ( ....

如果没有它，Oracle 优化器将尝试猜测您的意图并重新编译 sql 以获得更好的性能。

对于使用像这样的内联性能 View 的复杂查询，它通常会做出错误的猜测。