Node.js oracledb 5.3.0执行复杂Oracle查询结果与DBMS不一致

问题说明 #

直接在DBMS客户端中执行目标Oracle查询可返回符合预期的正确结果，但使用Node.js oracledb 5.3.0驱动运行完全相同的查询时，返回结果存在错误。检索各类公开技术渠道均未找到对应解决方案，涉及的查询语句如下：

SELECT
    ordersl3.*
FROM
    (
        SELECT
            ordersl2.*,
            (
                SELECT
                    COUNT(*)
                FROM
                    (
                        SELECT
                            to_date(ordersl2.orderrelentrydate, 'YYYY-MM-DD HH24:MI:SS') + ROWNUM - 1 AS cal_date
                        FROM
                            all_objects
                        WHERE
                            ROWNUM <= to_date(ordersl2.orderconfirmdate, 'YYYY-MM-DD HH24:MI:SS') - to_date(ordersl2.orderrelentrydate,
                            'YYYY-MM-DD HH24:MI:SS') + 1
                    )
                WHERE
                    to_char(cal_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') IN ( 'SAT', 'SUN' )
            ) AS order_confirmation_week_end_count
        FROM
            (
                SELECT
                    ordersl1.obrowid,
                    MAX(ordersl1.ponumber)                      AS ponumber,
                    MAX(ordersl1.customerpartnum)               AS customerpartnum,
                    MAX(ordersl1.sourcepartnum)                 AS sourcepartnum,
                    MAX(ordersl1.poreceiveddate)                AS poreceiveddate,
                    MAX(ordersl1.poactioneddate)                AS poactioneddate,
                    MAX(ordersl1.reviseddeliverydate)           AS deliverydate,
                    MAX(ordersl1.orderqunatity)                 AS orderqunatity,
                    MAX(ordersl1.shadedescription)              AS shadedescription,
                    MAX(ordersl1.shiptodesc)                    AS shiptodesc,
                    MAX(ordersl1.globalcustomercode)            AS globalcustomercode,
                    MAX(ordersl1.globalsubbrandcode)            AS globalsubbrandcode,
                    MAX(ordersl1.orderstatus)                   AS orderstatus,
                    MAX(ordersl1.companynum)                    AS companynum,
                    MAX(ordersl1.companyid)                     AS companyid,
                    MAX(ordersl1.companyname)                   AS companyname,
                    MAX(ordersl1.ordernum)                      AS ordernum,
                    MAX(ordersl1.orderlinenum)                  AS orderlinenum,
                    MAX(ordersl1.orderrelnum)                   AS orderrelnum,
                    MAX(ordersl1.ordervalue)                    AS ordervalue,
                    MAX(ordersl1.sellingunitprice)              AS sellingunitprice,
                    MAX(ordersl1.expecteddeliverydate)          AS expecteddeliverydate,
                    MAX(ordersl1.acknowledgedeliverydate)       AS acknowledgedeliverydate,
                    MAX(ordersl1.orderrelentrydate)             AS orderrelentrydate,
                    MAX(ordersl1.orderconfirmdate)              AS orderconfirmdate,
                    MAX(mc.custname)                            AS custname,
                    MAX(ms.subbrandname)                        AS subbrandname,
                    MAX(ordersl1.order_confirmation_diff)       AS order_confirmation_diff,
                    MAX(ordersl1.order_confirmation_diff_hours) AS order_confirmation_diff_hours,
                    MAX(ordersl1.po_actioned_hours_diff)        AS po_actioned_hours_diff,
                    MAX(order_confirmation_holidays_count)      AS order_confirmation_holidays_count,
                    MAX(wsf.dispatcheddate)                     AS dispatcheddate,
                    MAX(wsf.invoicedate)                        AS invoicedate,
                    MAX(wsf.customeracknowdate)                 AS customeracknowdate
                FROM
                    (
                        SELECT
                            raworders.*,
                            ( raworders.order_confirmation_diff * 24 ) AS order_confirmation_diff_hours,
                            ( raworders.po_actioned_diff * 24 )        AS po_actioned_hours_diff
                        FROM
                            (
                                SELECT
                                    wof.*,
                                    (
                                        SELECT
                                            to_date(wof.orderconfirmdate, 'YYYY-MM-DD HH24:MI:SS') - to_date(wof.orderrelentrydate, 'YYYY-MM-DD HH24:MI:SS')
                                        FROM
                                            dual
                                    ) AS order_confirmation_diff,
                                    (
                                        SELECT
                                            COUNT(*)
                                        FROM
                                            blabs.t_holiday th
                                        WHERE
                                                th.globalplantcode = wof.globalplantcode
                                            AND th.active = 1
                                            AND th.holiday BETWEEN to_date(wof.orderrelentrydate, 'YYYY-MM-DD HH24:MI:SS') AND to_date(
                                            wof.orderconfirmdate, 'YYYY-MM-DD HH24:MI:SS')
                                    ) AS order_confirmation_holidays_count,
                                    (
                                        SELECT
                                            to_date(wof.poactioneddate, 'YYYY-MM-DD HH24:MI:SS') - to_date(wof.poreceiveddate, 'YYYY-MM-DD HH24:MI:SS')
                                        FROM
                                            dual
                                    ) AS po_actioned_diff
                                FROM
                                    sales.w_orderbook_f wof
                                WHERE
                                    wof.orderrelentrydate != TO_DATE('1901-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
                            ) raworders
                    )                       ordersl1
                    LEFT JOIN masterdata.mst_customer mc ON ordersl1.globalcustomercode = mc.custid
                    LEFT JOIN masterdata.mst_subbrand ms ON ordersl1.globalsubbrandcode = ms.subbrandcode
                    LEFT JOIN sales.w_salesinvoice_f  wsf ON ordersl1.ordernum = wsf.ordernum
                                                            AND ordersl1.orderlinenum = wsf.orderline
                                                            AND ordersl1.orderrelnum = wsf.orderrelnum
                WHERE
                    ordersl1.poactioneddate BETWEEN TO_DATE('2022-03-04 18:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2022-06-04 23:59:59',
                    'YYYY-MM-DD HH24:MI:SS')
                    AND ordersl1.orderstatus = 0
                    AND mc.custid = 'B00019-T'
                    AND upper(concat(concat(TRIM(ordersl1.ponumber), ' '), concat(ordersl1.shiptodesc, concat(to_char(ordersl1.poreceiveddate),
                    concat(ordersl1.customerpartnum, concat(ordersl1.sourcepartnum, concat(ordersl1.subbrandname, concat(mc.custname,
                    ' ')))))))) LIKE '%%'
                    AND ordersl1.obrowid IS NOT NULL
                GROUP BY
                    ordersl1.obrowid
                ORDER BY
                    dispatcheddate
            ) ordersl2
    ) ordersl3
WHERE
    ( ordersl3.order_confirmation_diff >= 0
      OR ordersl3.order_confirmation_diff < 0 )
    AND ( ( ordersl3.orderconfirmdate IS NOT NULL
            AND ( ordersl3.order_confirmation_diff - ( ordersl3.order_confirmation_holidays_count + ordersl3.order_confirmation_week_end_count ) *
            24 ) <= 48 )
          OR ( ordersl3.orderconfirmdate IS NULL
               AND ( ( ( to_date(sysdate, 'YYYY-MM-DD HH24:MI:SS') - ordersl3.orderrelentrydate ) * 24 ) - ( ( ordersl3.order_confirmation_holidays_count +
               ordersl3.order_confirmation_week_end_count ) * 24 ) ) <= 48 ) )
    AND ( ( to_date(ordersl3.poactioneddate, 'YYYY-MM-DD HH24:MI:SS') - to_date(ordersl3.poreceiveddate, 'YYYY-MM-DD HH24:MI:SS') ) *
    24 <= 24 )
    AND ( ( ordersl3.dispatcheddate IS NOT NULL
            AND ordersl3.dispatcheddate <= ordersl3.deliverydate )
          OR ( ordersl3.dispatcheddate IS NULL
               AND to_date(sysdate, 'YYYY-MM-DD HH24:MI:SS') <= ordersl3.deliverydate ) )

异常现象验证 #

• 筛选PONUMBER为4400037844的记录时，Node.js oracledb返回结果中存在该记录，但该记录按查询逻辑不应出现在结果集中：
  
• 在DBMS中直接执行同一查询后筛选PONUMBER为4400037844的记录，无对应记录返回，结果符合预期：
  
• 相同查询两种执行方式返回的总记录数存在明显差异：
  • Node.js oracledb执行返回总记录数：
    
  • DBMS直接查询返回总记录数：
    

交叉测试结论 #

因始终未定位到Node.js侧的配置或代码问题，单独搭建Java Spring Boot项目执行相同查询，返回结果与DBMS直接查询结果完全一致。
经多轮重复测试验证，判断Node.js oracledb 5.3.0版本存在查询解析类Bug。最终结论：在官方修复该问题前，不推荐使用Node.js oracledb 5.3.0执行多层嵌套的复杂查询，否则可能出现非预期的错误返回结果。

内容的提问来源于stack exchange，提问作者Chathuranga Kasthuriarachchi