Nodejs oracledb different result with query executer and direct query from DBMS

Question

When I execute my oracle query from the DBMS it returns correct data. But when i run the same query from from the nodejs oracledb code, results are wrong. I've searched this everywhere. but didn't get any solution. please help me. oracledb version is 5.3.0. I've also mentioned my query below.

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 ) )

[UPDATE] Filter 4400037844 PONUMBER in the nodejs oracledb output (4400037844 is there but its cannot be)

Filter 4400037844 PONUMBER in the direct query in the DBMS (4400037844 is not there. results are correct)

[COUNTS are also different using the same identical query]. oracledb nodejs

DBMS Direct Query

[UPDATE 2] I cannot found any solution for this. So I had to make a separate java spring boot project to run this query. that resulted perfectly as same as the DBMS direct query.

So A lot of tests proved me that there is a bug in the node oracledb. But i cannot say whats that exactly. I think there is a query interpretation problem. So the conclusion is i cannot recommend nodejs oracledb to run complex queries since the results are unexpected until they fix it.

Answer 1

Your mistake is using to_date(sysdate, ".....") In function to_date first paremeter is varchar2, but sysdate is date. Oracle automatically convert date to varchar2 using session nls parameters. Result of conversation in node-oracledb and DBMS is different with different default session nls parameters in this apps. And result of to_date is different.

I've had this mistake in my practice :)