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

Incorrect results when filtering data from UNION ALL sub-query

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Critical
    • Resolution: Unresolved
    • 3.1.0
    • None
    • Database/Schema
    • None
    • Azure HDInsight 4.1.7.5

      Hive 3.1.0

    Description

      With CBO enabled querying from a view or CTE with a UNION ALL clause produces wrong results, such as the following script shows.

      CREATE TABLE n1 (c1 STRING);
      
      INSERT OVERWRITE TABLE n1 VALUES('needn');
      
      CREATE VIEW v1 
      AS
      SELECT 'maggie'  AS c1 FROM n1
      UNION ALL
      SELECT c1 FROM n1;
      

      Return the incorrect result when using "=" or "IN" with single element.

      For example, the following 2 querys return nothing.

      SELECT * FROM v1 WHERE c1 = 'maggie';
      SELECT * FROM v1 WHERE c1 IN ('maggie');

       

      However, I can get correct result when using "LIKE" or "IN" with multiple element.

      For example, the following 2 querys return expected result.

      SELECT * FROM v1 WHERE c1 IN ('maggie','This is a bug');
      SELECT * FROM v1 WHERE c1 LIKE 'maggie%';
      

       

      Attachments

        1. hive.png
          64 kB
          Needn Yu

        Activity

          People

            Unassigned Unassigned
            Needn Needn Yu
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated: