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

StorageHandler with select-limit query is returning 0 rows

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 4.0.0
    • Component/s: None

      Description

       

      CREATE EXTERNAL TABLE dbs(db_id bigint, db_location_uri string, name string, owner_name string, owner_type string) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ('hive.sql.database.type'='METASTORE', 'hive.sql.query'='SELECT `DB_ID`, `DB_LOCATION_URI`, `NAME`, `OWNER_NAME`, `OWNER_TYPE` FROM `DBS`'); 
      

      ==> Wrong Result <==

      set hive.limit.optimize.enable=true;
      select * from dbs limit 1;
      ----------------------------------------------------------------------------------------------
       VERTICES MODE STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
      ----------------------------------------------------------------------------------------------
      Map 1 .......... container SUCCEEDED 0 0 0 0 0 0
      ----------------------------------------------------------------------------------------------
      VERTICES: 01/01 [==========================>>] 100% ELAPSED TIME: 0.91 s
      ----------------------------------------------------------------------------------------------
      +------------+----------------------+-----------+-----------------+-----------------+
      | dbs.db_id | dbs.db_location_uri | dbs.name | dbs.owner_name | dbs.owner_type |
      +------------+----------------------+-----------+-----------------+-----------------+
      +------------+----------------------+-----------+-----------------+-----------------+
      

      ==> Correct Result <==

      set hive.limit.optimize.enable=false;
      select * from dbs limit 1;
      ----------------------------------------------------------------------------------------------
              VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED
      ----------------------------------------------------------------------------------------------
      Map 1 .......... container     SUCCEEDED      1          1        0        0       0       0
      ----------------------------------------------------------------------------------------------
      VERTICES: 01/01  [==========================>>] 100%  ELAPSED TIME: 4.11 s
      ----------------------------------------------------------------------------------------------+------------+----------------------------------------------------+-----------+-----------------+-----------------+
      | dbs.db_id  |                dbs.db_location_uri                 | dbs.name  | dbs.owner_name  | dbs.owner_type  |
      +------------+----------------------------------------------------+-----------+-----------------+-----------------+
      | 1          | hdfs://abcd:8020/warehouse/tablespace/managed/hive | default   | public          | ROLE            |
      +------------+----------------------------------------------------+-----------+-----------------+-----------------+

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                nareshpr Naresh P R
                Reporter:
                nareshpr Naresh P R
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 40m
                  40m