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

WM_TRIGGER trigger creation failed with type cast from Integer to Boolean

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 3.0.0
    • Component/s: Metastore
    • Labels:
      None

      Description

      During testing of the new WM feature and the Hive metastore is created using Postgresql, I've discovered a bug when creating a new trigger. For example

      CREATE RESOURCE PLAN plan_1 WITH QUERY_PARALLELISM=4;
      CREATE POOL plan_1.slow WITH
         ALLOC_FRACTION=0.5, QUERY_PARALLELISM=2, SCHEDULING_POLICY='fair';
      ALTER POOL plan_1.default SET
         ALLOC_FRACTION=0.5, QUERY_PARALLELISM=2, SCHEDULING_POLICY='fifo';
      CREATE TRIGGER plan_1.trigger_1 WHEN S3A_BYTES_READ > 268435456 DO MOVE TO slow;
      

      Right at the CREATE TRIGGER statement, an error will occur

      Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Insert of object "org.apache.hadoop.hive.metastore.model.MWMTrigger@5c5ae5d8" using statement "INSERT INTO "WM_TRIGGER" ("TRIGGER_ID","ACTION_EXPRESSION","IS_IN_UNMANAGED","NAME","RP_ID","TRIGGER_EXPRESSION") VALUES (?,?,?,?,?,?)" failed : ERROR: column "IS_IN_UNMANAGED" is of type boolean but expression is of type integer Hint: You will need to rewrite or cast the expression. Position: 129)
              at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:543) ~[datanucleus-api-jdo-4.2.4.jar:?]
              at org.datanucleus.api.jdo.JDOPersistenceManager.jdoMakePersistent(JDOPersistenceManager.java:729) ~[datanucleus-api-jdo-4.2.4.jar:?]
              at org.datanucleus.api.jdo.JDOPersistenceManager.makePersistent(JDOPersistenceManager.java:749) ~[datanucleus-api-jdo-4.2.4.jar:?]
              at org.apache.hadoop.hive.metastore.ObjectStore.createWMTrigger(ObjectStore.java:11218) ~[hive-exec-3.1.0-SNAPSHOT.jar:3.1.0-SNAPSHOT]
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_151]
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_151]
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_151]
              at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_151]
              at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:97) ~[hive-exec-3.1.0-SNAPSHOT.jar:3.1.0-SNAPSHOT]
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_151]
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_151]
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_151]
              at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_151]
              at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:97) ~[hive-exec-3.1.0-SNAPSHOT.jar:3.1.0-SNAPSHOT]
              at com.sun.proxy.$Proxy37.createWMTrigger(Unknown Source) ~[?:?]
              at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_wm_trigger(HiveMetaStore.java:7846) ~[hive-exec-3.1.0-SNAPSHOT.jar:3.1.0-SNAPSHOT]
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_151]
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_151]
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_151]
              at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_151]
              at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:147) ~[hive-exec-3.1.0-SNAPSHOT.jar:3.1.0-SNAPSHOT]
              at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:108) ~[hive-exec-3.1.0-SNAPSHOT.jar:3.1.0-SNAPSHOT]
              at com.sun.proxy.$Proxy39.create_wm_trigger(Unknown Source) ~[?:?]
              at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createWMTrigger(HiveMetaStoreClient.java:3062) ~[hive-exec-3.1.0-SNAPSHOT.jar:3.1.0-SNAPSHOT]
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_151]
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_151]
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_151]
              at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_151]
              at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:212) ~[hive-exec-3.1.0-SNAPSHOT.jar:3.1.0-SNAPSHOT]
              at com.sun.proxy.$Proxy40.createWMTrigger(Unknown Source) ~[?:?]
              at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createWMTrigger(HiveMetaStoreClient.java:3062) ~[hive-exec-3.1.0-SNAPSHOT.jar:3.1.0-SNAPSHOT]
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_151]
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_151]
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_151]
              at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_151]
              at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:212) ~[hive-exec-3.1.0-SNAPSHOT.jar:3.1.0-SNAPSHOT]
              at com.sun.proxy.$Proxy40.createWMTrigger(Unknown Source) ~[?:?]
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_151]
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_151]
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_151]
              at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_151]
              at org.apache.hadoop.hive.metastore.HiveMetaStoreClient$SynchronizedHandler.invoke(HiveMetaStoreClient.java:2722) ~[hive-exec-3.1.0-SNAPSHOT.jar:3.1.0-SNAPSHOT]
              at com.sun.proxy.$Proxy40.createWMTrigger(Unknown Source) ~[?:?]
              at org.apache.hadoop.hive.ql.metadata.Hive.createWMTrigger(Hive.java:5048) ~[hive-exec-3.1.0-SNAPSHOT.jar:3.1.0-SNAPSHOT]
              ... 22 more
      

      Apparently, Postgres doesn't automatically cast int to boolean.

      hive=# create table example (active BOOLEAN);
      CREATE TABLE
      hive=# \d+ example;
                             Table "public.example"
       Column |  Type   | Modifiers | Storage | Stats target | Description
      --------+---------+-----------+---------+--------------+-------------
       active | boolean |           | plain   |              |
      
      hive=# insert into example (active) values (0);
      ERROR:  column "active" is of type boolean but expression is of type integer
      LINE 1: insert into example (active) values (0);
                                                   ^
      HINT:  You will need to rewrite or cast the expression.
      

      Adding a ' quote and the insert statement will be okay

      hive=# insert into example (active) values ('0');
      INSERT 0 1
      hive=# select * from example;
       active
      --------
       f
      (1 row)
      

      The fix is to change the IS_IN_UNMANAGED field in Postgres from boolean to integer (smallint) since that is what it's being done in derby schema.

        Attachments

        1. HIVE-19394.patch
          0.7 kB
          Thai Bui
        2. HIVE-19394.patch
          0.7 kB
          Thai Bui

          Activity

            People

            • Assignee:
              thai.bui Thai Bui
              Reporter:
              thai.bui Thai Bui
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: