>>
>>obey TEST007(setup_traf);
>>set schema hive.hive;

--- SQL operation complete.
>>cqd HIVE_MAX_STRING_LENGTH_IN_BYTES '20' ;

--- SQL operation complete.
>>create schema if not exists trafodion.sch007;

--- SQL operation complete.
>>
>>obey TEST007(tests_traf);
>>-- tests for views on hive tables
>>process hive statement 'drop table thive1';

--- SQL operation complete.
>>process hive statement 'create table thive1(a int, b int)';

--- SQL operation complete.
>>sh echo "insert into thive1 values (1, 2);" > TEST007_junk;
>>sh regrhive.ksh -f TEST007_junk;
>>
>>process hive statement 'drop table thive2';

--- SQL operation complete.
>>process hive statement 'create table thive2(a int, b int)';

--- SQL operation complete.
>>sh echo "insert into thive2 values (1, 2);" > TEST007_junk;
>>sh regrhive.ksh -f TEST007_junk;
>>
>>create external table thive2 for hive.hive.thive2;

--- SQL operation complete.
>>
>>create view trafodion.sch007.vhive1 as select * from thive1;

--- SQL operation complete.
>>showddl hive.hive.thive1;

/* Hive DDL */
CREATE TABLE HIVE.HIVE.THIVE1
  (
    A                                int
  , B                                int
  )
  stored as textfile
;

/* Trafodion DDL */

REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.THIVE1;
/* ObjectUID = 8969081248987438491 */

--- SQL operation complete.
>>
>>create view trafodion.sch007.vhive11 as select * from thive2;

--- SQL operation complete.
>>create view trafodion.sch007.vhive2 as select x.a, y.b from thive1 x, thive2 y
+>  where x.a < 2 and x.b = y.b;

--- SQL operation complete.
>>create view trafodion.sch007.vhive3 as select * from trafodion.sch007.vhive2;

--- SQL operation complete.
>>
>>-- view on hive and traf tables
>>drop table if exists trafodion.sch007.thbase1;

--- SQL operation complete.
>>create table trafodion.sch007.thbase1 (aa int, bb int);

--- SQL operation complete.
>>create view trafodion.sch007.vhivehbase as select * from 
+>        trafodion.sch007.thbase1, thive2 where a = aa;

--- SQL operation complete.
>>insert into trafodion.sch007.thbase1 values (1,2);

--- 1 row(s) inserted.
>>invoke trafodion.sch007.vhivehbase;

-- Definition of Trafodion view TRAFODION.SCH007.VHIVEHBASE
-- Definition current  Sat Jun  2 18:21:30 2018

  (
    AA                               INT DEFAULT NULL
  , BB                               INT DEFAULT NULL
  , A                                INT DEFAULT NULL
  , B                                INT DEFAULT NULL
  )

--- SQL operation complete.
>>select * from trafodion.sch007.vhivehbase;

AA           BB           A            B          
-----------  -----------  -----------  -----------

          1            2            1            2

--- 1 row(s) selected.
>>
>>select * from thive1;

A            B          
-----------  -----------

          1            2

--- 1 row(s) selected.
>>insert into trafodion.sch007.vhive1 values (3,4);

--- 1 row(s) inserted.
>>select * from thive1;

A            B          
-----------  -----------

          1            2
          3            4

--- 2 row(s) selected.
>>
>>prepare s from select * from trafodion.sch007.vhive1;

--- SQL command prepared.
>>explain options 'f' s;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

1    .    2    root                                                  1.00E+002
.    .    1    hive_scan                       THIVE1                1.00E+002

--- SQL operation complete.
>>execute s;

A            B          
-----------  -----------

          1            2
          3            4

--- 2 row(s) selected.
>>
>>prepare s from select * from trafodion.sch007.vhive2;

--- SQL command prepared.
>>explain options 'f' s;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

3    .    4    root                                                  1.65E+003
2    1    3    hybrid_hash_join                                      1.65E+003
.    .    2    hive_scan                       THIVE2                1.00E+002
.    .    1    hive_scan                       THIVE1                3.29E+001

--- SQL operation complete.
>>execute s;

A            B          
-----------  -----------

          1            2

--- 1 row(s) selected.
>>
>>prepare s from select * from trafodion.sch007.vhive3;

--- SQL command prepared.
>>explain options 'f' s;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

3    .    4    root                                                  1.65E+003
2    1    3    hybrid_hash_join                                      1.65E+003
.    .    2    hive_scan                       THIVE2                1.00E+002
.    .    1    hive_scan                       THIVE1                3.29E+001

--- SQL operation complete.
>>execute s;

A            B          
-----------  -----------

          1            2

--- 1 row(s) selected.
>>
>>get tables in view trafodion.sch007.vhive1;

Tables in View SCH007.VHIVE1
============================

HIVE.HIVE.THIVE1

=======================
 1 row(s) returned

--- SQL operation complete.
>>get tables in view trafodion.sch007.vhive11;

Tables in View SCH007.VHIVE11
=============================

HIVE.HIVE.THIVE2

=======================
 1 row(s) returned

--- SQL operation complete.
>>
>>get all tables in view trafodion.sch007.vhive3;

Tables in View SCH007.VHIVE3
============================

HIVE.HIVE.THIVE1
HIVE.HIVE.THIVE2

=======================
 2 row(s) returned

--- SQL operation complete.
>>get views in view trafodion.sch007.vhive3;

Views in View SCH007.VHIVE3
===========================

TRAFODION.SCH007.VHIVE2

=======================
 1 row(s) returned

--- SQL operation complete.
>>get views on table hive.hive.thive1;

Views on Table HIVE.THIVE1
==========================

TRAFODION.SCH007.VHIVE1
TRAFODION.SCH007.VHIVE2

=======================
 2 row(s) returned

--- SQL operation complete.
>>get all views on table hive.hive.thive2;

Views on Table HIVE.THIVE2
==========================

TRAFODION.SCH007.VHIVE11
TRAFODION.SCH007.VHIVE2
TRAFODION.SCH007.VHIVE3
TRAFODION.SCH007.VHIVEHBASE

=======================
 4 row(s) returned

--- SQL operation complete.
>>
>>drop external table thive2 for hive.hive.thive2 cascade;

--- SQL operation complete.
>>showddl hive.hive.thive2;

/* Hive DDL */
CREATE TABLE HIVE.HIVE.THIVE2
  (
    A                                int
  , B                                int
  )
  stored as textfile
;

/* Trafodion DDL */

REGISTER /*INTERNAL*/ HIVE TABLE HIVE.HIVE.THIVE2;
/* ObjectUID = 8969081248987438052 */

--- SQL operation complete.
>>showddl trafodion.sch007.vhive3;

CREATE VIEW TRAFODION.SCH007.VHIVE3 AS
  SELECT TRAFODION.SCH007.VHIVE2.A, TRAFODION.SCH007.VHIVE2.B FROM
    TRAFODION.SCH007.VHIVE2 ;

--- SQL operation complete.
>>get all views on table hive.hive.thive2;

Views on Table HIVE.THIVE2
==========================

TRAFODION.SCH007.VHIVE11
TRAFODION.SCH007.VHIVE2
TRAFODION.SCH007.VHIVE3
TRAFODION.SCH007.VHIVEHBASE

=======================
 4 row(s) returned

--- SQL operation complete.
>>
>>obey TEST007(error_tests_traf);
>>
>>create view trafodion.sch007.vhive4 as select x.a, y.b from thive1 x, thive1 y;

--- SQL operation complete.
>>
>>-- insert not allowed
>>insert into trafodion.sch007.vhive4 values (3,4);

*** ERROR[4027] Table or view TRAFODION.SCH007.VHIVE4 does not permit insertions.

*** ERROR[8822] The statement was not prepared.

>>
>>-- del/upd not allowed
>>delete from trafodion.sch007.vhive1;

*** ERROR[4223] Update/Delete on Hive table is not supported in this software version or edition.

*** ERROR[8822] The statement was not prepared.

>>update trafodion.sch007.vhive1 set b = 1;

*** ERROR[4223] Update/Delete on Hive table is not supported in this software version or edition.

*** ERROR[8822] The statement was not prepared.

>>
>>--  traf view is not updatable
>>delete from trafodion.sch007.vhive4;

*** ERROR[4028] Table or view TRAFODION.SCH007.VHIVE4 is not updatable.

*** ERROR[8822] The statement was not prepared.

>>
>>create view if not exists vhive5 as select * from thive1;

--- SQL operation complete.
>>drop view vhive5;

--- SQL operation complete.
>>
>>-- if underlying hive table is dropped, an error is returned
>>prepare s from select * from trafodion.sch007.vhive1;

--- SQL command prepared.
>>execute s;

A            B          
-----------  -----------

          1            2
          3            4

--- 2 row(s) selected.
>>process hive statement 'drop table thive1';

--- SQL operation complete.
>>execute s;

*** ERROR[4263] Object HIVE.HIVE.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup table/view' command to cleanup this object.

*** ERROR[8822] The statement was not prepared.

--- 0 row(s) selected.
>>select * from trafodion.sch007.vhive1;

*** ERROR[4263] Object HIVE.HIVE.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup table/view' command to cleanup this object.

*** ERROR[8822] The statement was not prepared.

>>unregister hive table if exists hive.hive.thive1;

*** ERROR[3251] UNREGISTER operation could not be completed. Reason: Specified object THIVE1 does not exist.

--- SQL operation failed with errors.
>>select * from hive.hive.thive1;

*** ERROR[4263] Object HIVE.HIVE.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup table/view' command to cleanup this object.

*** ERROR[8822] The statement was not prepared.

>>cleanup table hive.hive.thive1;

--- SQL operation complete.
>>select * from hive.hive.thive1;

*** ERROR[1388] Object HIVE.HIVE.THIVE1 does not exist in Hive Metadata.

*** ERROR[8822] The statement was not prepared.

>>
>>
>>obey TEST007(setup_hive);
>>create database hive.hivesch007;

--- SQL operation complete.
>>
>>create table hive.hivesch007.thive1 (a int);

--- SQL operation complete.
>>
>>insert into hive.hivesch007.thive1 values (1);

--- 1 row(s) inserted.
>>
>>create view hive.hivesch007.vhive1 as select * from hivesch007.thive1 where thive1.a > 0;

--- SQL operation complete.
>>
>>create view hive.hivesch007.vhive11 as select * from hivesch007.vhive1 where vhive1.a > 0;

--- SQL operation complete.
>>
>>create table hive.hive.thive007 (a int);

--- SQL operation complete.
>>
>>create view hive.hive.vhive007 as select * from thive007 where a > 0;

--- SQL operation complete.
>>
>>insert into hive.hive.thive007 values (1);

--- 1 row(s) inserted.
>>
>>obey TEST007(tests_hive);
>>invoke hive.hivesch007.vhive1;

-- Definition of native Hive view HIVE.HIVESCH007.VHIVE1
-- Definition current  Sat Jun  2 18:23:20 2018

  (
    A                                INT
  )

--- SQL operation complete.
>>showddl hive.hivesch007.vhive1;

Original native Hive view text:
select * from hivesch007.thive1 where thive1.a > 0

Expanded native Hive view text:
CREATE VIEW hive.hivesch007.vhive1 AS
  select thive1.a from hivesch007.thive1 where thive1.a > 0 ;

REGISTER /*INTERNAL*/ HIVE VIEW HIVE.HIVESCH007.VHIVE1;
/* ObjectUID = 8947126200801021695 */

--- SQL operation complete.
>>
>>prepare s from select * from hive.hivesch007.vhive1;

--- SQL command prepared.
>>explain options 'f' s;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

1    .    2    root                                                  3.29E+001
.    .    1    hive_scan                       THIVE1                3.29E+001

--- SQL operation complete.
>>execute s;

A          
-----------

          1

--- 1 row(s) selected.
>>
>>invoke hive.hivesch007.vhive11;

-- Definition of native Hive view HIVE.HIVESCH007.VHIVE11
-- Definition current  Sat Jun  2 18:23:25 2018

  (
    A                                INT
  )

--- SQL operation complete.
>>showddl hive.hivesch007.vhive11;

Original native Hive view text:
select * from hivesch007.vhive1 where vhive1.a > 0

Expanded native Hive view text:
CREATE VIEW hive.hivesch007.vhive11 AS
  select vhive1.a from hivesch007.vhive1 where vhive1.a > 0 ;

REGISTER /*INTERNAL*/ HIVE VIEW HIVE.HIVESCH007.VHIVE11;
/* ObjectUID = 8947126200801021864 */

--- SQL operation complete.
>>
>>prepare s from select * from hive.hivesch007.vhive11;

--- SQL command prepared.
>>explain options 'f' s;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

1    .    2    root                                                  3.29E+001
.    .    1    hive_scan                       THIVE1                3.29E+001

--- SQL operation complete.
>>execute s;

A          
-----------

          1

--- 1 row(s) selected.
>>
>>prepare s from select * from hive.hive.vhive007;

--- SQL command prepared.
>>explain options 'f' s;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

1    .    2    root                                                  3.29E+001
.    .    1    hive_scan                       THIVE007              3.29E+001

--- SQL operation complete.
>>execute s;

A          
-----------

          1

--- 1 row(s) selected.
>>
>>get tables in schema hive.hivesch007;

Tables in Schema HIVE.HIVESCH007
================================

thive1

--- SQL operation complete.
>>get views in schema hive.hivesch007;

Views in Schema HIVE.HIVESCH007
===============================

vhive1
vhive11

--- SQL operation complete.
>>
>>get tables in schema hive.hive, match '%007%';

Tables in Schema HIVE.HIVE
==========================

thive007

--- SQL operation complete.
>>get views in schema hive.hive, match '%007%';

Views in Schema HIVE.HIVE
=========================

vhive007

--- SQL operation complete.
>>
>>get tables in catalog hive, match 'hivesch007%';

Tables in Catalog HIVE
======================

hivesch007.thive1

--- SQL operation complete.
>>get views in catalog hive, match 'hivesch007%';

Views in Catalog HIVE
=====================

hivesch007.vhive1
hivesch007.vhive11

--- SQL operation complete.
>>
>>set schema hive.hive;

--- SQL operation complete.
>>prepare s from select * from vhive007 x, hive.hivesch007.vhive1 y
+>  where x.a = y.a;

--- SQL command prepared.
>>explain options 'f' s;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

3    .    4    root                                                  1.08E+003
2    1    3    hybrid_hash_join      u                               1.08E+003
.    .    2    hive_scan                       THIVE007              3.29E+001
.    .    1    hive_scan                       THIVE1                3.29E+001

--- SQL operation complete.
>>execute s;

A            A          
-----------  -----------

          1            1

--- 1 row(s) selected.
>>
>>-- create traf view on native hive view
>>create schema if not exists trafodion.sch007;

--- SQL operation complete.
>>create view trafodion.sch007.vtrafonhive as select * from hive.hivesch007.vhive11;

--- SQL operation complete.
>>showddl trafodion.sch007.vtrafonhive;

CREATE VIEW TRAFODION.SCH007.VTRAFONHIVE AS
  SELECT HIVE.HIVESCH007.VHIVE11.A FROM HIVE.HIVESCH007.VHIVE11 ;

--- SQL operation complete.
>>prepare s from select * from trafodion.sch007.vtrafonhive;

--- SQL command prepared.
>>explain options 'f' s;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

1    .    2    root                                                  3.29E+001
.    .    1    hive_scan                       THIVE1                3.29E+001

--- SQL operation complete.
>>execute s;

A          
-----------

          1

--- 1 row(s) selected.
>>get tables in view trafodion.sch007.vtrafonhive;

--- SQL operation complete.
>>get views in view trafodion.sch007.vtrafonhive;

Views in View SCH007.VTRAFONHIVE
================================

HIVE.HIVESCH007.VHIVE11

=======================
 1 row(s) returned

--- SQL operation complete.
>>get views on view hive.hivesch007.vhive11;

Views ON View HIVESCH007.VHIVE11
================================

TRAFODION.SCH007.VTRAFONHIVE

=======================
 1 row(s) returned

--- SQL operation complete.
>>
>>unregister hive view hive.hivesch007.vhive11 cascade;

--- SQL operation complete.
>>
>>get tables in view hive.hivesch007.vhive11;

--- SQL operation complete.
>>get views on table hive.hivesch007.thive1;

--- SQL operation complete.
>>
>>register hive view hive.hivesch007.vhive11 cascade;

--- SQL operation complete.
>>showddl hive.hivesch007.vhive11;

Original native Hive view text:
select * from hivesch007.vhive1 where vhive1.a > 0

Expanded native Hive view text:
CREATE VIEW hive.hivesch007.vhive11 AS
  select vhive1.a from hivesch007.vhive1 where vhive1.a > 0 ;

REGISTER HIVE VIEW HIVE.HIVESCH007.VHIVE11;
/* ObjectUID = 8947126200801027583 */

--- SQL operation complete.
>>get tables in view hive.hivesch007.vhive11;

--- SQL operation complete.
>>get views in view hive.hivesch007.vhive11;

Views in View HIVESCH007.VHIVE11
================================

HIVE.HIVESCH007.VHIVE1

=======================
 1 row(s) returned

--- SQL operation complete.
>>get views on table hive.hivesch007.thive1;

Views on Table HIVESCH007.THIVE1
================================

HIVE.HIVESCH007.VHIVE1

=======================
 1 row(s) returned

--- SQL operation complete.
>>get views on view hive.hivesch007.vhive1;

Views ON View HIVESCH007.VHIVE1
===============================

HIVE.HIVESCH007.VHIVE11

=======================
 1 row(s) returned

--- SQL operation complete.
>>
>>-- tests for recursive hive view/table register/unregister operations
>>process hive statement 'create database hivesch0071';

--- SQL operation complete.
>>process hive statement 'create table hivesch0071.h1 (a int)';

--- SQL operation complete.
>>process hive statement 'create view hivesch0071.vh1 as select * from hivesch0071.h1';

--- SQL operation complete.
>>process hive statement 'create view hivesch0071.vh11 as select vh1.a x, h1.a y from hivesch0071.vh1, hivesch0071.h1';

--- SQL operation complete.
>>
>>set schema hive.hivesch0071;

--- SQL operation complete.
>>showddl hive.hivesch0071.vh11;

Original native Hive view text:
select vh1.a x, h1.a y from hivesch0071.vh1, hivesch0071.h1

Expanded native Hive view text:
CREATE VIEW hive.hivesch0071.vh11 AS
  select vh1.a x, h1.a y from hivesch0071.vh1, hivesch0071.h1 ;

--- SQL operation complete.
>>get tables in view vh11;

--- SQL operation complete.
>>get views in view vh11;

--- SQL operation complete.
>>get all objects in view vh11;

--- SQL operation complete.
>>
>>register hive view vh11 cascade;

--- SQL operation complete.
>>get tables in view vh11;

Tables in View HIVESCH0071.VH11
===============================

HIVE.HIVESCH0071.H1

=======================
 1 row(s) returned

--- SQL operation complete.
>>get views in view vh11;

Views in View HIVESCH0071.VH11
==============================

HIVE.HIVESCH0071.VH1

=======================
 1 row(s) returned

--- SQL operation complete.
>>get all objects in view vh11;

Objects in View HIVESCH0071.VH11
================================

HIVE.HIVESCH0071.H1
HIVE.HIVESCH0071.VH1

=======================
 2 row(s) returned

--- SQL operation complete.
>>
>>get hive registered tables in catalog trafodion, match '%hivesch0071%';

Hive Registered Tables in Catalog TRAFODION
===========================================

hive.hivesch0071.h1

=======================
 1 row(s) returned

--- SQL operation complete.
>>get hive registered views in catalog trafodion, match '%hivesch0071%';

Hive Registered Views in Catalog TRAFODION
==========================================

hive.hivesch0071.vh1
hive.hivesch0071.vh11

=======================
 2 row(s) returned

--- SQL operation complete.
>>get hive registered objects in catalog trafodion, match '%hivesch0071%';

Hive Registered Objects in Catalog TRAFODION
============================================

hive.hivesch0071.h1
hive.hivesch0071.vh1
hive.hivesch0071.vh11

=======================
 3 row(s) returned

--- SQL operation complete.
>>
>>select object_name from trafodion."_MD_".objects where schema_name = 'HIVESCH0071';

OBJECT_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

VH11                                                                                                                                                                                                                                                            
VH1                                                                                                                                                                                                                                                             
H1                                                                                                                                                                                                                                                              

--- 3 row(s) selected.
>>
>>unregister hive view vh11 cascade;

--- SQL operation complete.
>>get hive registered objects in catalog trafodion, match '%hivesch0071%';

--- SQL operation complete.
>>
>>showddl vh11;

Original native Hive view text:
select vh1.a x, h1.a y from hivesch0071.vh1, hivesch0071.h1

Expanded native Hive view text:
CREATE VIEW hive.hivesch0071.vh11 AS
  select vh1.a x, h1.a y from hivesch0071.vh1, hivesch0071.h1 ;

--- SQL operation complete.
>>
>>select object_name from trafodion."_MD_".objects where schema_name = 'HIVESCH0071';

--- 0 row(s) selected.
>>
>>-- register hive schemas
>>get schemas in catalog hive, match '%hivesch0078%';

--- SQL operation complete.
>>get hive registered schemas in catalog trafodion, match '%hivesch0078%';

--- SQL operation complete.
>>showddl schema hive.hivesch0078;

*** ERROR[1003] Schema HIVE.HIVESCH0078 does not exist.

--- SQL operation failed with errors.
>>get tables in schema hive.hivesch0078;

*** ERROR[1003] Schema hive.HIVESCH0078 does not exist.

--- SQL operation failed with errors.
>>process hive statement 'create database hivesch0078';

--- SQL operation complete.
>>showddl schema hive.hivesch0078;

/* Hive DDL */
CREATE SCHEMA HIVE.HIVESCH0078;


--- SQL operation complete.
>>register hive schema hive.hivesch0078;

--- SQL operation complete.
>>showddl schema hive.hivesch0078;

/* Hive DDL */
CREATE SCHEMA HIVE.HIVESCH0078;

REGISTER /*INTERNAL*/ HIVE SCHEMA HIVE.HIVESCH0078;

--- SQL operation complete.
>>get hive registered schemas in catalog trafodion, match '%hivesch0078%';

Hive Registered Schemas in Catalog TRAFODION
============================================

hive.hivesch0078

=======================
 1 row(s) returned

--- SQL operation complete.
>>unregister hive schema hive.hivesch0078;

--- SQL operation complete.
>>showddl schema hive.hivesch0078;

/* Hive DDL */
CREATE SCHEMA HIVE.HIVESCH0078;


--- SQL operation complete.
>>get hive registered schemas in catalog trafodion, match '%hivesch0078%';

--- SQL operation complete.
>>process hive statement 'drop database hivesch0078';

--- SQL operation complete.
>>showddl schema hive.hivesch0078;

*** ERROR[1003] Schema HIVE.HIVESCH0078 does not exist.

--- SQL operation failed with errors.
>>
>>obey TEST007(error_tests_hive);
>>insert into hive.hivesch007.vhive1 values (1);

*** ERROR[4027] Table or view HIVE.HIVESCH007.VHIVE1 does not permit insertions.

*** ERROR[8822] The statement was not prepared.

>>delete from hive.hivesch007.vhive1;

*** ERROR[4028] Table or view HIVE.HIVESCH007.VHIVE1 is not updatable.

*** ERROR[8822] The statement was not prepared.

>>
>>-- drop underlying hive table
>>cqd auto_query_retry_warnings 'ON';

--- SQL operation complete.
>>
>>process hive statement 'drop table hivesch007.thive1';

--- SQL operation complete.
>>
>>prepare s from select * from hive.hivesch007.vhive11;

*** ERROR[4263] Object HIVE.HIVESCH007.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup table/view' command to cleanup this object.

*** ERROR[8822] The statement was not prepared.

>>execute s;

*** ERROR[15017] Statement S was not found.

>>
>>process hive statement 'create table hivesch007.thive1 (a int)';

--- SQL operation complete.
>>
>>prepare s from select * from hive.hivesch007.vhive11;

--- SQL command prepared.
>>execute s;

--- 0 row(s) selected.
>>
>>process hive statement 'drop table hivesch007.thive1';

--- SQL operation complete.
>>
>>execute s;

*** ERROR[4263] Object HIVE.HIVESCH007.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup table/view' command to cleanup this object.

*** ERROR[8822] The statement was not prepared.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[8577] Table, index, or view HIVE.HIVESCH007.THIVE1 was not found.

--- 0 row(s) selected.
>>
>>-- test compatibility with hive tables created prior to registration change)
>>set schema hive.hivesch007;

--- SQL operation complete.
>>process hive statement 'drop table hivesch007.thive9';

--- SQL operation complete.
>>process hive statement 'create table hivesch007.thive9 (a int)';

--- SQL operation complete.
>>unregister hive table if exists hive.hivesch007.thive9;

--- SQL operation complete.
>>cqd hive_no_register_objects 'ON';

--- SQL operation complete.
>>drop external table if exists thive9 for hive.hivesch007.thive9;

--- SQL operation complete.
>>create external table thive9 for hive.hivesch007.thive9;

--- SQL operation complete.
>>showddl thive9;

/* Hive DDL */
CREATE TABLE HIVE.HIVESCH007.THIVE9
  (
    A                                int
  )
  stored as textfile
;

/* Trafodion DDL */

CREATE EXTERNAL TABLE THIVE9
  FOR HIVE.HIVESCH007.THIVE9
;

--- SQL operation complete.
>>grant select on hive.hivesch007.thive9 to sql_user3;

--- SQL operation complete.
>>get privileges for user sql_user3, match '%HIVESCH007%';

Privileges for User SQL_USER3
=============================

S------    HIVE.HIVESCH007.THIVE9

=======================
 1 row(s) returned

--- SQL operation complete.
>>insert into hive.hivesch007.thive9 values (1), (2), (3), (4), (5), (6);

--- 6 row(s) inserted.
>>update statistics for table hive.hivesch007.thive9 on every column;

--- SQL operation complete.
>>showstats for table hive.hivesch007.thive9 on every column;

Histogram data for Table HIVE.HIVESCH007.THIVE9
Table ID: 8947126200801036437

   Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
1235981905      6           6           6 A


--- SQL operation complete.
>>create view trafodion.sch007.vhive9 as select * from hive.hivesch007.thive9;

--- SQL operation complete.
>>showddl hive.hivesch007.thive9;

/* Hive DDL */
CREATE TABLE HIVE.HIVESCH007.THIVE9
  (
    A                                int
  )
  stored as textfile
;

/* Trafodion DDL */

CREATE EXTERNAL TABLE THIVE9
  FOR HIVE.HIVESCH007.THIVE9
;

--- SQL operation complete.
>>
>>register hive table hive.hivesch007.thive9;

--- SQL operation complete.
>>showddl thive9;

/* Hive DDL */
CREATE TABLE HIVE.HIVESCH007.THIVE9
  (
    A                                int
  )
  stored as textfile
;

/* Trafodion DDL */

REGISTER HIVE TABLE HIVE.HIVESCH007.THIVE9;
/* ObjectUID = 8947126200801036437 */

CREATE EXTERNAL TABLE THIVE9
  FOR HIVE.HIVESCH007.THIVE9
;

--- SQL operation complete.
>>showstats for table hive.hivesch007.thive9 on every column;

Histogram data for Table HIVE.HIVESCH007.THIVE9
Table ID: 8947126200801036437

   Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
1235981905      6           6           6 A


--- SQL operation complete.
>>unregister hive table hive.hivesch007.thive9;

--- SQL operation complete.
>>showddl thive9;

/* Hive DDL */
CREATE TABLE HIVE.HIVESCH007.THIVE9
  (
    A                                int
  )
  stored as textfile
;

/* Trafodion DDL */

CREATE EXTERNAL TABLE THIVE9
  FOR HIVE.HIVESCH007.THIVE9
;

--- SQL operation complete.
>>showstats for table hive.hivesch007.thive9 on every column;

Histogram data for Table HIVE.HIVESCH007.THIVE9
Table ID: 8947126200801036437

   Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
1235981905      6           6           6 A


--- SQL operation complete.
>>drop view trafodion.sch007.vhive9;

--- SQL operation complete.
>>drop external table thive9 for hive.hivesch007.thive9;

--- SQL operation complete.
>>
>>showddl thive9;

/* Hive DDL */
CREATE TABLE HIVE.HIVESCH007.THIVE9
  (
    A                                int
  )
  stored as textfile
;

/* Trafodion DDL */

--- SQL operation complete.
>>showstats for table hive.hivesch007.thive9 on every column;

Histogram data for Table HIVE.HIVESCH007.THIVE9
Table ID: 0

   Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================

No Histograms exist for the requested columns or groups


--- SQL operation complete.
>>
>>
>>
>>obey TEST007(clean_up_traf);
>>drop view trafodion.sch007.vhive1 cascade;

*** ERROR[1389] Object TRAFODION.SCH007.VHIVE1 does not exist in Trafodion.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[1389] Object TRAFODION.SCH007.VHIVE1 does not exist in Trafodion.

--- SQL operation failed with errors.
>>drop view trafodion.sch007.vhive11 cascade;

--- SQL operation complete.
>>drop view trafodion.sch007.vhive2 cascade;

*** ERROR[1389] Object TRAFODION.SCH007.VHIVE2 does not exist in Trafodion.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[1389] Object TRAFODION.SCH007.VHIVE2 does not exist in Trafodion.

--- SQL operation failed with errors.
>>drop view trafodion.sch007.vhive3 cascade;

*** ERROR[1389] Object TRAFODION.SCH007.VHIVE3 does not exist in Trafodion.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[1389] Object TRAFODION.SCH007.VHIVE3 does not exist in Trafodion.

--- SQL operation failed with errors.
>>drop view trafodion.sch007.vhive4 cascade;

*** ERROR[1389] Object TRAFODION.SCH007.VHIVE4 does not exist in Trafodion.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[1389] Object TRAFODION.SCH007.VHIVE4 does not exist in Trafodion.

--- SQL operation failed with errors.
>>drop view trafodion.sch007.vhive5 cascade;

*** ERROR[1389] Object TRAFODION.SCH007.VHIVE5 does not exist in Trafodion.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[1389] Object TRAFODION.SCH007.VHIVE5 does not exist in Trafodion.

--- SQL operation failed with errors.
>>drop view trafodion.sch007.vhive6 cascade;

*** ERROR[1389] Object TRAFODION.SCH007.VHIVE6 does not exist in Trafodion.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[1389] Object TRAFODION.SCH007.VHIVE6 does not exist in Trafodion.

--- SQL operation failed with errors.
>>drop external table thive1 for hive.hive.thive1 cascade;

*** ERROR[1389] Object TRAFODION."_HV_HIVE_".THIVE1 does not exist in Trafodion.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[1389] Object TRAFODION."_HV_HIVE_".THIVE1 does not exist in Trafodion.

--- SQL operation failed with errors.
>>cleanup table trafodion."_HV_HIVE_".thive1;

*** WARNING[4251] Object UID could not be retrieved from metadata based on the provided object name. Metadata cleanup will not be performed but the underlying hbase object will be removed, if it exists.

--- SQL operation complete.
>>drop external table thive2 for hive.hive.thive2 cascade;

*** ERROR[1389] Object TRAFODION."_HV_HIVE_".THIVE2 does not exist in Trafodion.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[1389] Object TRAFODION."_HV_HIVE_".THIVE2 does not exist in Trafodion.

--- SQL operation failed with errors.
>>process hive statement 'drop table thive1';

--- SQL operation complete.
>>process hive statement 'drop table thive2';

--- SQL operation complete.
>>drop external table thive3 for hive.hive.thive3;

*** ERROR[1389] Object TRAFODION."_HV_HIVE_".THIVE3 does not exist in Trafodion.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[1389] Object TRAFODION."_HV_HIVE_".THIVE3 does not exist in Trafodion.

--- SQL operation failed with errors.
>>process hive statement 'drop table thive3';

--- SQL operation complete.
>>drop table if exists trafodion.sch007.thbase1 cascade;

--- SQL operation complete.
>>
>>obey TEST007(clean_up_hive);
>>
>>set schema hive.hive;

--- SQL operation complete.
>>unregister hive table hive.hive.thive1 cleanup;

*** WARNING[1389] Object THIVE1 does not exist in Trafodion.

--- SQL operation complete.
>>
>>unregister hive view hive.hivesch007.vhive1 cascade cleanup;

*** ERROR[4263] Object HIVE.HIVESCH007.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup table/view' command to cleanup this object.

--- SQL operation failed with errors.
>>unregister hive view hive.hivesch007.vhive11 cascade cleanup;

*** ERROR[4263] Object HIVE.HIVESCH007.THIVE1 has invalid state and cannot be accessed. It is registered in trafodion metadata but the correponding hive object does not exist. Use 'cleanup table/view' command to cleanup this object.

--- SQL operation failed with errors.
>>
>>drop table hivesch007.thive1;

*** ERROR[1388] Table hivesch007.thive1 does not exist in Hive Metadata.

--- SQL operation failed with errors.
>>drop view hivesch007.vhive11;

--- SQL operation complete.
>>drop view hivesch007.vhive1;

--- SQL operation complete.
>>drop database hivesch007 cascade;

--- SQL operation complete.
>>drop table hive.hive.thive007;

--- SQL operation complete.
>>drop view vhive007;

--- SQL operation complete.
>>
>>cleanup view hive.hivesch007.vhive11;

*** ERROR[1389] Object VHIVE11 does not exist in Trafodion.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[1389] Object VHIVE11 does not exist in Trafodion.

--- SQL operation failed with errors.
>>
>>drop view trafodion.sch007.vtrafonhive;

--- SQL operation complete.
>>
>>drop schema trafodion.sch007 cascade;

--- SQL operation complete.
>>
>>drop table hivesch0071.h1;

--- SQL operation complete.
>>drop view hivesch0071.vh1;

--- SQL operation complete.
>>drop view hivesch0071.vh11;

--- SQL operation complete.
>>
>>drop table hivesch007.thive9;

*** ERROR[1388] Table hivesch007.thive9 does not exist in Hive Metadata.

--- SQL operation failed with errors.
>>drop view trafodion.sch007.vhive9;

*** ERROR[1389] Object TRAFODION.SCH007.VHIVE9 does not exist in Trafodion.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[1389] Object TRAFODION.SCH007.VHIVE9 does not exist in Trafodion.

--- SQL operation failed with errors.
>>drop external table thive9 for hive.hivesch007.thive9;

*** ERROR[1389] Object TRAFODION."_HV_HIVESCH007_".THIVE9 does not exist in Trafodion.

*** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry.

*** WARNING[1389] Object TRAFODION."_HV_HIVESCH007_".THIVE9 does not exist in Trafodion.

--- SQL operation failed with errors.
>>
>>drop database hivesch0071 cascade;

--- SQL operation complete.
>>
>>
>>
>>log;
