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

[ckasthuriarachchi]

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

[cjbj]

See the comments on https://stackoverflow.com/questions/72480957/nodejs-oracledb-different-result-with-query-executer-and-direct-query-from-dbms where this was also posted. We can follow up there.

[Also, use triple-back ticks when posting code - see here. I've edited your question to do this.]

[ckasthuriarachchi]

See the comments on https://stackoverflow.com/questions/72480957/nodejs-oracledb-different-result-with-query-executer-and-direct-query-from-dbms where this was also posted. We can follow up there.

[Also, use triple-back ticks when posting code - see here. I've edited your question to do this.]

Thats my question. No useful comments there. Thats why i created a issue here. This is a issue from oracledb package

[ckasthuriarachchi]

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. It's better if the contributors have a look on this matter.

[cjbj]

We take data seriously and would investigate any issue if we are given clear information. A very complex SQL statement, and a few screenshots (in the stack overflow post) aren't enough for anyone to make any conclusive statement about what is going on. Sorry.

Node.js is not Java and is tricky to get right, but a lot of people are using node-oracledb without a problem.