Hash Indexes and NULL Comparisons

3868714

    Hi,

     

    We are currently introducing more indexes into our databases as instructed by the index advisor, but have noticed a change in behaviour when comparing null values.

     

    Prior to the index the following query would return 0 results:

     

    select * from network_set where priority3_network_element not in (select id from network_element);
    0 rows found.
    

    (I know this should be enforced by a foreign key, but we currently have incompatible data types which we're trying to resolve first)

     

    Adding the following index results in a change of behaviour where the above query will then return any entries where priority3_network_element is null - adding a is not null check first achieves the correct result:

    create hash index HASH_NETWORK_ELEMENT_ID on NETWORK_ELEMENT(ID);
    select * from network_set where priority3_network_element not in (select id from network_element);
    122 rows found.
    ...
    select * from network_set where priority3_network_element is not null and priority3_network_element not in (select id from network_element);
    0 rows found.
    

     

    What is the explanation for the change in query behaviour here? Is there any other potential cases that we should be aware of that also changes the behaviour of queries when adding indexes?

     

    Many Thanks

    Curtis

      • 1. Re: Hash Indexes and NULL Comparisons
        ChrisJenkins-Oracle

        This looks like a bug to me. Can you please provide:

         

        1.   The definition of the table NETWORK_ELEMENT.

         

        2.  The definition of the column NETWORK_SET.PRIORITY3_NETWORK_ELEMENT.

         

        3.  The exact version of TimesTen being used (the full output of the ttVersion command).

         

        Thanks,

         

        Chris

        • 2. Re: Hash Indexes and NULL Comparisons
          3868714

          Hi Chris.

           

          Here are the details you asked for:

          [timesten@noas-timesten-vm ~]$ ttVersion

          TimesTen Release 11.2.2.8.22 (64 bit Linux/x86_64) (tt1122:53396) 2017-06-20T21:06:23Z

            Instance admin: timesten

            Instance home directory: /opt/timesten/TimesTen/tt1122

            Group owner: timesten

            Daemon home directory: /opt/timesten/data

            PL/SQL enabled.

           

          Please note that PRIORITY3_NETWORK_ELEMENT maps to PRIORITY3_ASBC in the below data model:

           

          Table TEST.NETWORK_ELEMENT:
            Columns:
             *ID                              NUMBER (32) NOT NULL
              OSS_ID                          VARCHAR2 (32) INLINE NOT NULL
              TYPE                            NUMBER (16) NOT NULL
              NAME                            VARCHAR2 (32) INLINE NOT NULL
              IP                              VARCHAR2 (32) INLINE
              PORT                            VARCHAR2 (8) INLINE
              LAST_UPDATED_BY                 VARCHAR2 (32) INLINE
              LAST_UPDATED_DTIME              DATE
          
          Table TEST.NETWORK_SET:
            Columns:
             *ID                              NUMBER (8) NOT NULL
              OSS_ID                          VARCHAR2 (32) INLINE NOT NULL
              PRIORITY1_SLEE                  NUMBER (8) NOT NULL
              PRIORITY2_SLEE                  NUMBER (8)
              PRIORITY1_ASBC                  NUMBER (8) NOT NULL
              PRIORITY2_ASBC                  NUMBER (8)
              PRIORITY3_ASBC                  NUMBER (8)
              PRIORITY4_ASBC                  NUMBER (8)
              LAST_UPDATED_BY                 VARCHAR2 (32) INLINE
              LAST_UPDATED_DTIME              DATE
          

           

          The reason why we added this query is that PRIORITYX_YYYY should be a foreign key mapped to NETWORK_ELEMENT.ID but the incompatible data types (NUMBER(8) vs NUMBER(32)) prevent the foreign key from being created. We plan on fixing this but there are a few constraints which prevent us from doing this immediately.

           

          Coming back to the original queries, which result set is the expected result?

           

          Curtis

          • 3. Re: Hash Indexes and NULL Comparisons
            ChrisJenkins-Oracle

            The correct result set is of course the one with 0 rows found. Rows where PRIORITY3_NETWORK_ELEMENT is NULl should not appear in the result set.

             

            This is a serious bug which is still present in the most recent patch (11.2.2.8.35) but does not reproduce in the new 18.1 release.

             

            I will raise a bug on this to get it addressed in 11.2.2 but in the meantime please use the 'is not null' workaround.

             

            Sorry for the inconvenience.

             

            Chris

             

            • 4. Re: Hash Indexes and NULL Comparisons
              3868714

              Chris,

               

              Thank you for getting back to me so quickly and confirming the correct result. I will keep an eye out for any future patch releases.

               

              Curtis