>>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 THIVE1
  (
    A                                int
  , B                                int
  )
  stored as textfile
;

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

--- 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  Wed May  3 05:10:02 2017

  (
    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

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

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

HIVE.HIVE.THIVE2

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

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

HIVE.HIVE.THIVE1
HIVE.HIVE.THIVE2

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

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

TRAFODION.SCH007.VHIVE2

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

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

TRAFODION.SCH007.VHIVE1
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

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

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

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

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

--- 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

--- 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.

*** 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.

*** 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.

>>
>>-- traf view must be in traf cat/sch
>>create view vhive5 as select * from thive1;

*** ERROR[3242] This statement is not supported. Reason: DDL views can only be created or dropped in trafodion schema.

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

>>
>>-- 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 hive 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 hive 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 hive table/view' command to cleanup this object.

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

>>cleanup hive 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);
>>process hive statement 'create database hivesch007';

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

--- SQL operation complete.
>>
>>sh echo "insert into hivesch007.thive1 values (1);" > TEST007_junk;
>>sh regrhive.ksh -f TEST007_junk;
>>
>>process hive statement 'create view hivesch007.vhive1 as select * from hivesch007.thive1 where thive1.a > 0';

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

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

--- SQL operation complete.
>>
>>process hive statement 'create view 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 VHIVE1
-- Definition current  Wed May  3 05:11:22 2017

  (
    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 vhive1 AS
  select thive1.a from hivesch007.thive1 where thive1.a > 0 ;

--- 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 VHIVE11
-- Definition current  Wed May  3 05:11:24 2017

  (
    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 vhive11 AS
  select vhive1.a from hivesch007.vhive1 where vhive1.a > 0 ;

--- 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

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

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

TRAFODION.SCH007.VTRAFONHIVE

--- 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 vhive11 AS
  select vhive1.a from hivesch007.vhive1 where vhive1.a > 0 ;

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

--- 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

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

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

HIVE.HIVESCH007.VHIVE1

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

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

HIVE.HIVESCH007.VHIVE11

--- 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 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

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

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

HIVE.HIVESCH0071.VH1

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

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

HIVE.HIVESCH0071.H1
HIVE.HIVESCH0071.VH1

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

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

hive.hivesch0071.h1

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

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

hive.hivesch0071.vh1
hive.hivesch0071.vh11

--- 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

--- 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 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 database hivesch0078;


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

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

/* Hive DDL */
create database 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

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

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

/* Hive DDL */
create database 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 hive 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 hive 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 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

--- 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: 7463718898080914461

   Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
 770517434      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 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 THIVE9
  (
    A                                int
  )
  stored as textfile
;

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

/* 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: 7463718898080914461

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


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

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

/* Hive DDL */
CREATE TABLE 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: 7463718898080914461

   Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
 770517434      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 THIVE9
  (
    A                                int
  )
  stored as textfile
;

--- 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.
>>
>>
>>
>>log;
