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

HPL/SQL does not allow variables in update statements

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • None
    • None
    • hpl/sql

    Description

      HPL/SQL does not allow variables in update statements

      Works in Oracle:

      DECLARE
      val_to_update varchar(10);
      BEGIN
      val_to_update := 'one';
      FOR REC in (select a,b from test1 where a = val_to_update) LOOP
      dbms_output.put_line (rec.a);
      dbms_output.put_line (rec.b);
      END LOOP;
      update test1 set b = 'another'
      where a = val_to_update;
      end;

      Doesn't work in Hive:

      DECLARE
      val_to_update STRING;
      BEGIN
      val_to_update := 'one';
      FOR REC in (select a,b from test where a = val_to_update) LOOP
      print (rec.a);
      print (rec.b);
      END LOOP;
      update test set b = 'another test'
      where a = val_to_update;
      end;
      /
      ERROR : FAILED: SemanticException [Error 10004]: Line 2:14 Invalid table alias or column reference 'val_to_update': (possible column names are: a, b)
      org.apache.hadoop.hive.ql.parse.SemanticException: Line 2:14 Invalid table alias or column reference 'val_to_update': (possible column names are: a, b)
      at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genAllExprNodeDesc(SemanticAnalyzer.java:13636)
      at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:13575)
      ...
      

       

      Select (not update) does work in hive:

      DECLARE
      val_to_update STRING;
      BEGIN
      val_to_update := 'one';
      FOR REC in (select a,b from test where a = val_to_update) LOOP
      print (rec.a);
      print (rec.b);
      END LOOP;
      select * from test
      where a = val_to_update;
      end;
      /

      Attachments

        Issue Links

          Activity

            People

              Dayakar Dayakar M
              Dayakar Dayakar M
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: