Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-11375

Broken processing of queries containing NOT (x IS NOT NULL and x <> 0)

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.0
    • Fix Version/s: 2.0.0
    • Component/s: Logical Optimizer
    • Labels:
      None
    • Target Version/s:

      Description

      When running query like this:

      explain select * from test where (val is not null and val <> 0);

      hive will simplify expression in parenthesis and omit is not null check:

                Filter Operator
                  predicate: (val <> 0) (type: boolean)
      

      which is fine.

      but if we negate condition using NOT operator:

      explain select * from test where not (val is not null and val <> 0);

      hive will also simplify thing, but now it will break stuff:

                Filter Operator
                  predicate: (not (val <> 0)) (type: boolean)
      

      because valid predicate should be val == 0 or val is null, while above row is equivalent to val == 0 only, filtering away rows where val is null

      simple example:

      CREATE TABLE example (
          val bigint
      );
      INSERT INTO example VALUES (1), (NULL), (0);
      
      -- returns 2 rows - NULL and 0
      select * from example where (val is null or val == 0);
      -- returns 1 row - 0
      select * from example where not (val is not null and val <> 0);
      

        Attachments

        1. HIVE-11375.branch-1.patch
          67 kB
          Aihua Xu
        2. HIVE-11375.4.patch
          72 kB
          Aihua Xu
        3. HIVE-11375.3.patch
          67 kB
          Aihua Xu
        4. HIVE-11375.2.patch
          28 kB
          Aihua Xu
        5. HIVE-11375.patch
          16 kB
          Aihua Xu

          Issue Links

            Activity

              People

              • Assignee:
                aihuaxu Aihua Xu
                Reporter:
                mariusz.sakowski Mariusz Sakowski
              • Votes:
                0 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: