trafodion-codereview mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From DaveBirdsall <...@git.apache.org>
Subject [GitHub] incubator-trafodion pull request #913: TRAFODION-2439 add support for traf v...
Date Wed, 18 Jan 2017 18:27:53 GMT
Github user DaveBirdsall commented on a diff in the pull request:

    https://github.com/apache/incubator-trafodion/pull/913#discussion_r96701030
  
    --- Diff: core/sql/regress/hive/EXPECTED007 ---
    @@ -0,0 +1,370 @@
    +>>obey TEST007(setup);
    +>>set schema hive.hive;
    +
    +--- SQL operation complete.
    +>>cqd HIVE_MAX_STRING_LENGTH '20' ;
    +
    +--- SQL operation complete.
    +>>
    +>>obey TEST007(tests);
    +>>-- tests for views on hive tables
    +>>sh echo "drop table thive1;" > TEST007_junk;
    +>>sh regrhive.ksh -f TEST007_junk;
    +>>sh echo "create table thive1(a int, b int);" > TEST007_junk;
    +>>sh regrhive.ksh -f TEST007_junk;
    +>>sh echo "insert into thive1 values (1, 2);" > TEST007_junk;
    +>>sh regrhive.ksh -f TEST007_junk;
    +>>
    +>>sh echo "drop table thive2;" > TEST007_junk;
    +>>sh regrhive.ksh -f TEST007_junk;
    +>>sh echo "create table thive2(a int, b int);" > TEST007_junk;
    +>>sh regrhive.ksh -f TEST007_junk;
    +>>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.sch.vhive1 as select * from thive1;
    +
    +--- SQL operation complete.
    +>>showddl hive.hive.thive1;
    +
    +/* Hive DDL */
    +CREATE TABLE THIVE1
    +  (
    +    A                                int
    +  , B                                int
    +  )
    +  stored as textfile
    +;
    +
    +/* Trafodion DDL */
    +
    +CREATE EXTERNAL TABLE THIVE1
    +  FOR HIVE.HIVE.THIVE1
    +;
    +
    +--- SQL operation complete.
    +>>
    +>>create view trafodion.sch.vhive11 as select * from thive2;
    +
    +--- SQL operation complete.
    +>>create view trafodion.sch.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.sch.vhive3 as select * from trafodion.sch.vhive2;
    +
    +--- SQL operation complete.
    +>>
    +>>-- view on hive and traf tables
    +>>drop table if exists trafodion.sch.thbase1;
    +
    +--- SQL operation complete.
    +>>create table trafodion.sch.thbase1 (aa int, bb int);
    +
    +--- SQL operation complete.
    +>>create view trafodion.sch.vhivehbase as select * from 
    ++>        trafodion.sch.thbase1, thive2 where a = aa;
    +
    +--- SQL operation complete.
    +>>insert into trafodion.sch.thbase1 values (1,2);
    +
    +--- 1 row(s) inserted.
    +>>invoke trafodion.sch.vhivehbase;
    +
    +-- Definition of Trafodion view TRAFODION.SCH.VHIVEHBASE
    +-- Definition current  Mon Jan 16 18:43:12 2017
    +
    +  (
    +    AA                               INT DEFAULT NULL
    +  , BB                               INT DEFAULT NULL
    +  , A                                INT DEFAULT NULL
    +  , B                                INT DEFAULT NULL
    +  )
    +
    +--- SQL operation complete.
    +>>select * from trafodion.sch.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.sch.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.sch.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.sch.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.sch.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.sch.vhive1;
    +
    +Tables in View SCH.VHIVE1
    +=========================
    +
    +HIVE.HIVE.THIVE1
    +
    +--- SQL operation complete.
    +>>get tables in view trafodion.sch.vhive11;
    +
    +Tables in View SCH.VHIVE11
    +==========================
    +
    +HIVE.HIVE.THIVE2
    +
    +--- SQL operation complete.
    +>>
    +>>get all tables in view trafodion.sch.vhive3;
    +
    +Tables in View SCH.VHIVE3
    +=========================
    +
    +HIVE.HIVE.THIVE1
    +HIVE.HIVE.THIVE2
    +
    +--- SQL operation complete.
    +>>get views in view trafodion.sch.vhive3;
    +
    +Views in View SCH.VHIVE3
    +========================
    +
    +TRAFODION.SCH.VHIVE2
    +
    +--- SQL operation complete.
    +>>get views on table hive.hive.thive1;
    +
    +Views on Table _HV_HIVE_.THIVE1
    +===============================
    +
    +SCH.VHIVE1
    +SCH.VHIVE2
    +
    +--- SQL operation complete.
    +>>get all views on table hive.hive.thive2;
    +
    +Views on Table _HV_HIVE_.THIVE2
    +===============================
    +
    +SCH.VHIVE11
    +SCH.VHIVE2
    +SCH.VHIVE3
    +SCH.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
    +;
    +
    +--- SQL operation complete.
    +>>showddl trafodion.sch.vhive3;
    +
    +*** ERROR[4082] Object TRAFODION.SCH.VHIVE3 does not exist or is inaccessible.
    +
    +--- SQL operation failed with errors.
    +>>get all views on table hive.hive.thive2;
    +
    +--- SQL operation complete.
    +>>
    +>>obey TEST007(error_tests);
    +>>
    +>>create view trafodion.sch.vhive4 as select x.a, y.b from thive1 x, thive1 y;
    +
    +--- SQL operation complete.
    +>>
    +>>-- insert not allowed
    +>>insert into trafodion.sch.vhive4 values (3,4);
    +
    +*** ERROR[4027] Table or view TRAFODION.SCH.VHIVE4 does not permit insertions.
    +
    +*** ERROR[8822] The statement was not prepared.
    +
    +>>
    +>>-- del/upd not allowed
    +>>delete from trafodion.sch.vhive1;
    +
    +*** ERROR[4223] Update/Delete on Hive table is not supported in this software version.
    +
    +*** ERROR[8822] The statement was not prepared.
    +
    +>>update trafodion.sch.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.sch.vhive4;
    +
    +*** ERROR[4028] Table or view TRAFODION.SCH.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 operations can only be done
on trafodion or external tables.
    --- End diff --
    
    Might be a little clearer to say that views can only be created in a trafodion schema.
(The DDL operation is on two objects, thive1 and vhive5, but thive1 is not limited to being
a trafodion or external table, and vhive5 doesn't exist yet.)


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

Mime
View raw message