11 Replies Latest reply on Jul 31, 2020 1:09 AM by chris227

    Oracle 19c: connect by level

    user8780285

      HI All - Thanks in advance for your help. We are seeing incorrect result the first time the connect by level is used in the function in Oracle 19c version(19.7 April 2020 RU). When the query is executed the second time, it is giving correct result.  We don't see this issue in Oracle 12.2.0.1 version.

      The function description is listed in the function definition.

       

      Thanks.

       

      1) create table

      CREATE TABLE HOLIDAY

      ( HOLIDAY_DT    DATE                            NOT NULL,

        HOLIDAY_DESC  VARCHAR2(250 BYTE)

      )

      ;

      2) Insert data into HOLIDAY table

      SET DEFINE OFF;

      Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('1/1/2020', 'MM/DD/YYYY'), 'New Year''s Day');

      Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('1/20/2020', 'MM/DD/YYYY'), 'Martin Luther King, Jr. Day');

      Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('2/17/2020', 'MM/DD/YYYY'), 'Presidents'' Day');

      Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('5/25/2020', 'MM/DD/YYYY'), 'Memorial Day ');

      Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('7/3/2020', 'MM/DD/YYYY'), 'Independence Day');

      Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('9/7/2020', 'MM/DD/YYYY'), 'Labor Day');

      Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('10/12/2020', 'MM/DD/YYYY'), 'Columbus Day');

      Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('11/11/2020', 'MM/DD/YYYY'), 'Veterans'' Day');

      Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('11/26/2020', 'MM/DD/YYYY'), 'Thanksgiving Day');

      Insert into HOLIDAY (HOLIDAY_DT, HOLIDAY_DESC) Values(TO_DATE('12/25/2020', 'MM/DD/YYYY'), 'Christmas Day');

      COMMIT;

       

      3) Create function

      CREATE OR REPLACE FUNCTION "TESTHOLIDAYSWKENDS" (v_start_dt in varchar2, v_end_dt in varchar2)

         RETURN date

      IS

          v_new_end_dt date;

       

      /*

      Description: This  function receives a start date and end date as input and a new end date is returned.  It will

      calculate for a two business day lag and if a holiday or weekend falls within the two-day lag, it will

      search for the previous business day and then return the new date to the calling program.

      */

       

      BEGIN

        WITH date_tab AS

          (SELECT TO_DATE (v_start_dt, 'mm/dd/yyyy') + LEVEL - 1 business_date

          FROM DUAL

          CONNECT BY LEVEL <=

                              TO_DATE (v_end_dt, 'mm/dd/yyyy')

                              - TO_DATE (v_start_dt, 'mm/dd/yyyy')

                              + 1)

      ,

      business_date AS

          (SELECT business_date

             FROM date_tab

            WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN')

              AND business_date not in (select holiday_dt from HOLIDAY)

            ORDER BY 1 DESC

          )

       

      SELECT business_date into v_new_end_dt FROM

      (

         SELECT business_date FROM business_date

          WHERE rownum <= 3

          minus

         SELECT business_date FROM business_date

          WHERE rownum <= 2

      );

       

      RETURN(v_new_end_dt);

      END;

      /

       

      4) Run the SELECT statement

       

      sql>SELECT to_char(nvl(TESTHOLIDAYSWKENDS('07/16/2020','07/29/2020'), TO_DATE('12/31/9999','mm/dd/yyyy')), 'mm/dd/yyyy')  as End_Dt FROM DUAL;

       

      END_DT

      ----------

      07/24/2020 <-------(incorrect result when run the first time)

       

      sql> /

       

      END_DT

      ----------

      07/27/2020 <-------(correct result when re-run immediately)

       

      sql>  alter system flush shared_pool;

       

      System altered.

       

      sql>SELECT to_char(nvl(TESTHOLIDAYSWKENDS('07/16/2020','07/29/2020'), TO_DATE('12/31/9999','mm/dd/yyyy')), 'mm/dd/yyyy')  as End_Dt FROM DUAL;

       

      END_DT

      ----------

      07/24/2020 <-------(incorrect result when run the first time)

       

      sql> /

       

      END_DT

      ----------

      07/27/2020 <-------(correct result when re-run immediately)

       

       

       

      Thanks.

        • 1. Re: Oracle 19c: connect by level
          mathguy

          1. I haven't the faintest idea why the "first" run in version 19.7 would return the wrong value. It looks pretty certain to be a bug. Did you try Oracle support? (Although as I understand it, that is hit or miss.)

           

          2. It would seem more natural to demonstrate the same by selecting the function return by itself from DUAL. Why are you wrapping that within NVL?  If you get the same odd behavior without the NVL wrapper, then showing your test with NVL simply hides the problem. (Not too deeply, but still - why do that?)  If in fact the problem ONLY arises when you use the additional NVL wrapper, that would be information worth knowing.

           

          3. In any case, the function is unnecessarily complicated. Perhaps if you use a simpler one, which does not manifest the same odd behavior, you can adopt it and not worry about answering your original question. Is that something you are open to?

          • 2. Re: Oracle 19c: connect by level
            chris227

            I can confirm the behaviour.

            SInce the behaviour is reproducable also when you drop and recreate the holiday table and some result caching is kicked in according to the execution plans i guess that is probably an optimizier bug.

            But i dont want to exclude the possibility yet that the way your query is written contains some random factor.

            Perhaps both together leads to this result, that is without doubt not correct.

            • 3. Re: Oracle 19c: connect by level
              chris227

              I dont like this minus construct.

              if you work around it like below the error disappears:

               

              CREATE OR REPLACE FUNCTION "TESTHOLIDAYSWKENDS" (v_start_dt in varchar2, v_end_dt in varchar2)
                 RETURN date
              IS
                  v_new_end_dt date;
              
              
              
              BEGIN
                WITH date_tab AS
                  (SELECT  TO_DATE (v_start_dt, 'mm/dd/yyyy') + LEVEL - 1 business_date
                  FROM DUAL
                  CONNECT BY LEVEL <=
                                      TO_DATE (v_end_dt, 'mm/dd/yyyy')
                                      - TO_DATE (v_start_dt, 'mm/dd/yyyy')
                                      + 1)
              ,
              business_date AS
                  (SELECT  business_date
              , row_number() over (ORDER BY business_date DESC) rn
                     FROM date_tab
                    WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN')
                      AND business_date not in (select holiday_dt from HOLIDAY)
                   
                  )
              
              SELECT business_date into v_new_end_dt FROM
              (
              --   SELECT business_date FROM business_date
              --    WHERE rownum <= 3
              --    minus
                 SELECT business_date FROM business_date
                  where rn = 3
              );
              
              RETURN(v_new_end_dt);
              END;
              /
              
              • 4. Re: Oracle 19c: connect by level
                RogerT

                The problem is rather the minus with the two rownum < filters (and no order by .... yes i know the source is ordered), than the connect by.

                 

                I have rewritten the function like this:

                 

                create or replace FUNCTION "TESTHOLIDAYSWKENDS" (v_start_dt in varchar2, v_end_dt in varchar2)

                   RETURN date

                IS

                    v_new_end_dt date;

                BEGIN

                  WITH date_tab AS

                    (SELECT TO_DATE (v_start_dt, 'mm/dd/yyyy') + LEVEL - 1 business_date

                    FROM DUAL

                    CONNECT BY LEVEL <=

                                        TO_DATE (v_end_dt, 'mm/dd/yyyy')

                                        - TO_DATE (v_start_dt, 'mm/dd/yyyy')

                                        + 1)

                ,business_date AS

                    (SELECT business_date

                       FROM date_tab

                      WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN')

                        AND business_date not in (select holiday_dt from HOLIDAY)

                      ORDER BY 1 DESC

                    )

                SELECT business_date into v_new_end_dt

                  from business_date

                  order by business_date desc

                  offset 2 rows fetch first row only;

                 

                RETURN(v_new_end_dt);

                END;

                 

                and always get the same date...(27.07).

                 

                hth

                • 5. Re: Oracle 19c: connect by level
                  chris227

                  RogerT wrote:

                   

                  The problem is rather the minus with the two rownum < filters (and no order by .... yes i know the source is ordered),

                   

                  But this is the way (rownum not minus) we did pagination in the old days, isnt it?

                  I should work however.

                  But oviously it doesnt.

                   

                  I tried to get it correct by applying OPTIMIZER_FEATURE_ENABLE and tricks like that, but nothing worked out.

                   

                  There are some hint applied by the 0ptimizer IGNORE_WHERE_CLAUSE RESULT_CACHE.

                   

                  But i didnt get yet an idea what role result cache might play here, if at all.

                  • 6. Re: Oracle 19c: connect by level
                    Stefan Jager

                    chris227 wrote:

                    I dont like this minus construct.

                    That minus construct is just plain weird....

                     

                    Also: the phrase "business_date" is being used as table name and column name, without any aliases. Oracle is clever enough to distinguish between the two I think, but will the next developer be? Not how I would write code....

                    • 7. Re: Oracle 19c: connect by level
                      RogerT

                      The rownum (or row_number) thing is not the problem ... as you already showd ... the problem is the minus and the two queries without order by clause.

                       

                      even this here:

                       

                        WITH date_tab AS

                          (SELECT TO_DATE ('07/16/2020', 'mm/dd/yyyy') + LEVEL - 1 business_date

                          FROM DUAL

                          CONNECT BY LEVEL <=

                                              TO_DATE ('07/29/2020', 'mm/dd/yyyy')

                                              - TO_DATE ('07/16/2020', 'mm/dd/yyyy')

                                              + 1)

                      ,business_date AS

                          (SELECT business_date

                             FROM date_tab

                            WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN')

                              AND business_date not in (select holiday_dt from HOLIDAY)

                            ORDER BY 1 DESC

                          )

                      SELECT business_date

                      FROM

                      (

                         select * from (SELECT business_date FROM business_date order by business_date desc)

                          WHERE rownum <= 3

                          minus

                         SELECT * from (SELECT business_date FROM business_date order by business_date desc)

                          WHERE rownum <= 2

                      );

                       

                       

                      would return always the same result

                       

                      hth

                      • 8. Re: Oracle 19c: connect by level
                        chris227

                        Ok, yes. The hints just came from the dynamic sampling. Nothing to do with this problem probably.

                        So when comparing the two plans

                         

                        WITH DATE_TAB AS (SELECT TO_DATE (:B1 , 'mm/dd/yyyy') + LEVEL - 1
                        BUSINESS_DATE FROM DUAL CONNECT BY LEVEL <= TO_DATE (:B2 ,
                        'mm/dd/yyyy') - TO_DATE (:B1 , 'mm/dd/yyyy') + 1) , BUSINESS_DATE AS
                        (SELECT BUSINESS_DATE FROM DATE_TAB WHERE TO_CHAR (BUSINESS_DATE, 'DY')
                        NOT IN ('SAT', 'SUN') AND BUSINESS_DATE NOT IN (SELECT HOLIDAY_DT FROM
                        HOLIDAY) ORDER BY 1 DESC ) SELECT BUSINESS_DATE FROM ( SELECT
                        BUSINESS_DATE FROM BUSINESS_DATE WHERE ROWNUM <= 3 MINUS SELECT
                        BUSINESS_DATE FROM BUSINESS_DATE WHERE ROWNUM <= 2 )
                        
                        Plan hash value: 71285176
                        
                        ------------------------------------------------------------------------------------------------------------------------
                        | Id  | Operation                                | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
                        ------------------------------------------------------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT                         |                             |       |       |    13 (100)|          |
                        |   1 |  TEMP TABLE TRANSFORMATION               |                             |       |       |            |          |
                        |   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D688B_97852AA1 |       |       |            |          |
                        |   3 |    SORT ORDER BY                         |                             |     1 |    15 |     7  (15)| 00:00:01 |
                        |*  4 |     HASH JOIN ANTI                       |                             |     1 |    15 |     6   (0)| 00:00:01 |
                        |*  5 |      VIEW                                |                             |     1 |     6 |     2   (0)| 00:00:01 |
                        |   6 |       CONNECT BY WITHOUT FILTERING       |                             |       |       |            |          |
                        |   7 |        FAST DUAL                         |                             |     1 |       |     2   (0)| 00:00:01 |
                        |   8 |      TABLE ACCESS FULL                   | HOLIDAY                     |    10 |    90 |     4   (0)| 00:00:01 |
                        |   9 |   VIEW                                   |                             |     1 |     9 |     6  (34)| 00:00:01 |
                        |  10 |    MINUS                                 |                             |       |       |            |          |
                        |  11 |     SORT UNIQUE                          |                             |     1 |     9 |     3  (34)| 00:00:01 |
                        |* 12 |      COUNT STOPKEY                       |                             |       |       |            |          |
                        |  13 |       VIEW                               |                             |     1 |     9 |     2   (0)| 00:00:01 |
                        |  14 |        TABLE ACCESS FULL                 | SYS_TEMP_0FD9D688B_97852AA1 |     1 |     9 |     2   (0)| 00:00:01 |
                        |  15 |     SORT UNIQUE                          |                             |     1 |     9 |     3  (34)| 00:00:01 |
                        |* 16 |      COUNT STOPKEY                       |                             |       |       |            |          |
                        |  17 |       VIEW                               |                             |     1 |     9 |     2   (0)| 00:00:01 |
                        |  18 |        TABLE ACCESS FULL                 | SYS_TEMP_0FD9D688B_97852AA1 |     1 |     9 |     2   (0)| 00:00:01 |
                        ------------------------------------------------------------------------------------------------------------------------
                        
                        Predicate Information (identified by operation id):
                        ---------------------------------------------------
                        
                           4 - access("HOLIDAY_DT"=INTERNAL_FUNCTION("BUSINESS_DATE"))
                           5 - filter((TO_CHAR("BUSINESS_DATE",'DY')<>'SAT' AND TO_CHAR("BUSINESS_DATE",'DY')<>'SUN'))
                          12 - filter(ROWNUM<=3)
                          16 - filter(ROWNUM<=2)
                        
                        WITH DATE_TAB AS (SELECT TO_DATE (:B1 , 'mm/dd/yyyy') + LEVEL - 1
                        BUSINESS_DATE FROM DUAL CONNECT BY LEVEL <= TO_DATE (:B2 ,
                        'mm/dd/yyyy') - TO_DATE (:B1 , 'mm/dd/yyyy') + 1) , BUSINESS_DATE AS
                        (SELECT BUSINESS_DATE FROM DATE_TAB WHERE TO_CHAR (BUSINESS_DATE, 'DY')
                        NOT IN ('SAT', 'SUN') AND BUSINESS_DATE NOT IN (SELECT HOLIDAY_DT FROM
                        HOLIDAY) ORDER BY 1 DESC ) SELECT BUSINESS_DATE FROM ( SELECT * FROM
                        (SELECT BUSINESS_DATE FROM BUSINESS_DATE ORDER BY 1 DESC) WHERE ROWNUM
                        <= 3 MINUS SELECT * FROM (SELECT BUSINESS_DATE FROM BUSINESS_DATE ORDER
                        BY 1 DESC) WHERE ROWNUM <= 2 )
                        
                        Plan hash value: 2661260176
                        
                        ------------------------------------------------------------------------------------------------------------------------
                        | Id  | Operation                                | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
                        ------------------------------------------------------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT                         |                             |       |       |    15 (100)|          |
                        |   1 |  TEMP TABLE TRANSFORMATION               |                             |       |       |            |          |
                        |   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D689A_97852AA1 |       |       |            |          |
                        |   3 |    SORT ORDER BY                         |                             |     1 |    15 |     7  (15)| 00:00:01 |
                        |*  4 |     HASH JOIN ANTI                       |                             |     1 |    15 |     6   (0)| 00:00:01 |
                        |*  5 |      VIEW                                |                             |     1 |     6 |     2   (0)| 00:00:01 |
                        |   6 |       CONNECT BY WITHOUT FILTERING       |                             |       |       |            |          |
                        |   7 |        FAST DUAL                         |                             |     1 |       |     2   (0)| 00:00:01 |
                        |   8 |      TABLE ACCESS FULL                   | HOLIDAY                     |    10 |    90 |     4   (0)| 00:00:01 |
                        |   9 |   VIEW                                   |                             |     1 |     9 |     8  (50)| 00:00:01 |
                        |  10 |    MINUS                                 |                             |       |       |            |          |
                        |  11 |     SORT UNIQUE                          |                             |     1 |     9 |     4  (50)| 00:00:01 |
                        |* 12 |      COUNT STOPKEY                       |                             |       |       |            |          |
                        |  13 |       VIEW                               |                             |     1 |     9 |     3  (34)| 00:00:01 |
                        |* 14 |        SORT ORDER BY STOPKEY             |                             |     1 |     9 |     3  (34)| 00:00:01 |
                        |  15 |         VIEW                             |                             |     1 |     9 |     2   (0)| 00:00:01 |
                        |  16 |          TABLE ACCESS FULL               | SYS_TEMP_0FD9D689A_97852AA1 |     1 |     9 |     2   (0)| 00:00:01 |
                        |  17 |     SORT UNIQUE                          |                             |     1 |     9 |     4  (50)| 00:00:01 |
                        |* 18 |      COUNT STOPKEY                       |                             |       |       |            |          |
                        |  19 |       VIEW                               |                             |     1 |     9 |     3  (34)| 00:00:01 |
                        |* 20 |        SORT ORDER BY STOPKEY             |                             |     1 |     9 |     3  (34)| 00:00:01 |
                        |  21 |         VIEW                             |                             |     1 |     9 |     2   (0)| 00:00:01 |
                        |  22 |          TABLE ACCESS FULL               | SYS_TEMP_0FD9D689A_97852AA1 |     1 |     9 |     2   (0)| 00:00:01 |
                        ------------------------------------------------------------------------------------------------------------------------
                        
                        Predicate Information (identified by operation id):
                        ---------------------------------------------------
                        
                           4 - access("HOLIDAY_DT"=INTERNAL_FUNCTION("BUSINESS_DATE"))
                           5 - filter((TO_CHAR("BUSINESS_DATE",'DY')<>'SAT' AND TO_CHAR("BUSINESS_DATE",'DY')<>'SUN'))
                          12 - filter(ROWNUM<=3)
                          14 - filter(ROWNUM<=3)
                          18 - filter(ROWNUM<=2)
                          20 - filter(ROWNUM<=2)
                        

                        we see that the sort order by stopkey is not applied in the origin version.

                         

                        Lesson learned: we cant rely on the rownum from an order by produced by an subquery factory.

                         

                        But is this a bug or per design?

                        • 9. Re: Oracle 19c: connect by level
                          Paulzip

                          The bug can be alleviated with /*+ NO_QUERY_TRANSFORMATION */ hint,

                           

                          SELECT /*+ NO_QUERY_TRANSFORMATION */ business_date into v_new_end_dt

                          FROM ...

                           

                          I'm no CBO expert (Jonathan Lewis will no doubt have a much better explanation than my rudimentary one), but I'd guess it's doing a view merge and pushing predicates into the business_date section on the first run during hard parse, then on subsequent runs it "fixes" because of bind peeking / adaptive cursor sharing.

                          • 10. Re: Oracle 19c: connect by level
                            user8780285

                            Thank you all for your kind replies and suggestions. I will talk to the development teams about updating the code and in the meantime will use the suggested hint as a workaround.

                             

                            Thanks again.