>>
>>
>>obey TEST017(setup);
>>--------------
>>create  table T017TTB ( a int not null primary key, b int, c int);

--- SQL operation complete.
>>
>>create  table T017TTA ( a int , b int, c int);

--- SQL operation complete.
>>
>>create  table T017TTC ( a int not null primary key, b int, c int) SALT using 2 partitions on (a);

--- SQL operation complete.
>>
>>create  table T017TTD ( a int not null not droppable, b int, c int) store by (a);

--- SQL operation complete.
>>
>>create  table T017TTE ( a int , b int, c int);

--- SQL operation complete.
>>
>>create table "T017_Delimit" ( a int not null primary key, b int, c int);

--- SQL operation complete.
>>
>>load with no output, no recovery into T017TTA
+>  select
+>    0 +   (10 * x10) + x1,
+>    1000 +   (10 * x10) + x1,
+>    10000 +   (10 * x10) + x1
+>  from (values(1)) as starter
+>    transpose 0,1,2,3,4,5,6,7,8,9 as x10
+>    transpose 0,1,2,3,4,5,6,7,8,9 as x1 ;

--- 100 row(s) loaded.
>>
>>
>>create table a5table1
+> ( ref_num largeint
+> , z_text char(3)
+> , emp_num smallint
+> ) no partition
+> ;

--- SQL operation complete.
>>
>>insert into a5table1 values
+> (100,'abc',99), (100,'abd',99), (100,'abe',99), (100,'abc',100),
+> (100,'abd',100), (100,'abe',100), (100,'abc',101), (100,'abd',101),
+> (100,'abe',101), (100,'abb',null), (100,'abc',null), (100,'abd',null),
+> (100,'abe',null), (100,null,99), (100,null,100), (100,null,101),
+> (200,'abc',200), (200,'abd',200), (200,'abe',200);

--- 19 row(s) inserted.
>>
>>
>>
>>
>>obey TEST017(tests_simple);
>>----------------------
>>create index T017TTBIDXb on T017TTB(b) no populate;

--- SQL operation complete.
>>create unique index T017TTBIDXc on T017TTB(c) no populate;

--- SQL operation complete.
>>
>>create index T017TTCIDXb on T017TTC(b) no populate;

--- SQL operation complete.
>>create unique index T017TTCIDXc on T017TTC(c) no populate;

--- SQL operation complete.
>>
>>create index T017TTDIDXb on T017TTD(b) no populate;

--- SQL operation complete.
>>create unique index T017TTDIDXc on T017TTD(c) no populate;

--- SQL operation complete.
>>
>>create index T017TTEIDXb on T017TTE(b) no populate;

--- SQL operation complete.
>>
>>execute indexinfo using 'T017TTB';

(EXPR)                                                                            OBJECT_TYPE  INDEX_VALID_DEF  TABLE_VALID_DEF
--------------------------------------------------------------------------------  -----------  ---------------  ---------------

T017TTBIDXB                                                                       IX           N                Y              
T017TTBIDXC                                                                       IX           N                Y              

--- 2 row(s) selected.
>>execute indexinfo using 'T017TTC';

(EXPR)                                                                            OBJECT_TYPE  INDEX_VALID_DEF  TABLE_VALID_DEF
--------------------------------------------------------------------------------  -----------  ---------------  ---------------

T017TTCIDXB                                                                       IX           N                Y              
T017TTCIDXC                                                                       IX           N                Y              

--- 2 row(s) selected.
>>execute indexinfo using 'T017TTD';

(EXPR)                                                                            OBJECT_TYPE  INDEX_VALID_DEF  TABLE_VALID_DEF
--------------------------------------------------------------------------------  -----------  ---------------  ---------------

T017TTDIDXB                                                                       IX           N                Y              
T017TTDIDXC                                                                       IX           N                Y              

--- 2 row(s) selected.
>>execute indexinfo using 'T017TTE';

(EXPR)                                                                            OBJECT_TYPE  INDEX_VALID_DEF  TABLE_VALID_DEF
--------------------------------------------------------------------------------  -----------  ---------------  ---------------

T017TTEIDXB                                                                       IX           N                Y              

--- 1 row(s) selected.
>>
>>set parserflags 1;

--- SQL operation complete.
>>select count(*) from table(index_table T017TTBIDXb);

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>select count(*) from table(index_table T017TTBIDXc);

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>select count(*) from table(index_table T017TTCIDXb);

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>select count(*) from table(index_table T017TTCIDXc);

(EXPR)              
--------------------

                   0

--- 1 row(s) selected.
>>
>>alter table T017TTB enable all indexes;

--- SQL operation complete.
>>alter table T017TTC enable all indexes;

--- SQL operation complete.
>>alter table T017TTD enable all indexes;

--- SQL operation complete.
>>
>>execute indexinfo using 'T017TTB';

(EXPR)                                                                            OBJECT_TYPE  INDEX_VALID_DEF  TABLE_VALID_DEF
--------------------------------------------------------------------------------  -----------  ---------------  ---------------

T017TTBIDXB                                                                       IX           Y                Y              
T017TTBIDXC                                                                       IX           Y                Y              

--- 2 row(s) selected.
>>execute indexinfo using 'T017TTC';

(EXPR)                                                                            OBJECT_TYPE  INDEX_VALID_DEF  TABLE_VALID_DEF
--------------------------------------------------------------------------------  -----------  ---------------  ---------------

T017TTCIDXB                                                                       IX           Y                Y              
T017TTCIDXC                                                                       IX           Y                Y              

--- 2 row(s) selected.
>>execute indexinfo using 'T017TTD';

(EXPR)                                                                            OBJECT_TYPE  INDEX_VALID_DEF  TABLE_VALID_DEF
--------------------------------------------------------------------------------  -----------  ---------------  ---------------

T017TTDIDXB                                                                       IX           Y                Y              
T017TTDIDXC                                                                       IX           Y                Y              

--- 2 row(s) selected.
>>
>>alter table T017TTB disable all indexes;

--- SQL operation complete.
>>alter table T017TTC disable all indexes;

--- SQL operation complete.
>>alter table T017TTD disable all indexes;

--- SQL operation complete.
>>
>>execute indexinfo using 'T017TTB';

(EXPR)                                                                            OBJECT_TYPE  INDEX_VALID_DEF  TABLE_VALID_DEF
--------------------------------------------------------------------------------  -----------  ---------------  ---------------

T017TTBIDXB                                                                       IX           N                Y              
T017TTBIDXC                                                                       IX           N                Y              

--- 2 row(s) selected.
>>execute indexinfo using 'T017TTC';

(EXPR)                                                                            OBJECT_TYPE  INDEX_VALID_DEF  TABLE_VALID_DEF
--------------------------------------------------------------------------------  -----------  ---------------  ---------------

T017TTCIDXB                                                                       IX           N                Y              
T017TTCIDXC                                                                       IX           N                Y              

--- 2 row(s) selected.
>>execute indexinfo using 'T017TTD';

(EXPR)                                                                            OBJECT_TYPE  INDEX_VALID_DEF  TABLE_VALID_DEF
--------------------------------------------------------------------------------  -----------  ---------------  ---------------

T017TTDIDXB                                                                       IX           N                Y              
T017TTDIDXC                                                                       IX           N                Y              

--- 2 row(s) selected.
>>
>>load with no output, no recovery into T017TTB select * from T017TTA;

--- 100 row(s) loaded.
>>load with no output, no recovery into T017TTC select * from T017TTA;

--- 100 row(s) loaded.
>>load with no output, no recovery into T017TTD select * from T017TTA;

--- 100 row(s) loaded.
>>load with no output, no recovery into T017TTE select * from T017TTA;

--- 100 row(s) loaded.
>>---------------------------------------------------
>>populate all indexes on T017TTB;

--- SQL operation complete.
>>execute indexinfo using 'T017TTB';

(EXPR)                                                                            OBJECT_TYPE  INDEX_VALID_DEF  TABLE_VALID_DEF
--------------------------------------------------------------------------------  -----------  ---------------  ---------------

T017TTBIDXB                                                                       IX           Y                Y              
T017TTBIDXC                                                                       IX           Y                Y              

--- 2 row(s) selected.
>>set parserflags 1;

--- SQL operation complete.
>>select count(*) from table(index_table T017TTBIDXb);

(EXPR)              
--------------------

                 100

--- 1 row(s) selected.
>>select count(*) from table(index_table T017TTBIDXc);

(EXPR)              
--------------------

                 100

--- 1 row(s) selected.
>>
>>
>>populate all indexes on T017TTC;

--- SQL operation complete.
>>execute indexinfo using 'T017TTC';

(EXPR)                                                                            OBJECT_TYPE  INDEX_VALID_DEF  TABLE_VALID_DEF
--------------------------------------------------------------------------------  -----------  ---------------  ---------------

T017TTCIDXB                                                                       IX           Y                Y              
T017TTCIDXC                                                                       IX           Y                Y              

--- 2 row(s) selected.
>>set parserflags 1;

--- SQL operation complete.
>>select count(*) from table(index_table T017TTCIDXb);

(EXPR)              
--------------------

                 100

--- 1 row(s) selected.
>>select count(*) from table(index_table T017TTCIDXc);

(EXPR)              
--------------------

                 100

--- 1 row(s) selected.
>>--verify contents of index table defined on table with Salt column 
>>showddl table(index_table T017TTcIDXb);

CREATE TABLE TRAFODION.HBASE.T017TTCIDXB
  (
    "B@"                             INT NO DEFAULT
  , "_SALT_"                         INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  , A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY ("B@" ASC, "_SALT_" ASC, A ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>select  "B@" , I.A ,t.b,t.c ,case when I."_SALT_"<>T."_SALT_" THEN 1 else 0 end  from table(index_table T017TTcIDXb) I join T017TTc t on I.A=t.A order by "B@";

B@           A            B            C            (EXPR)
-----------  -----------  -----------  -----------  ------

       1000            0         1000        10000       0
       1001            1         1001        10001       0
       1002            2         1002        10002       0
       1003            3         1003        10003       0
       1004            4         1004        10004       0
       1005            5         1005        10005       0
       1006            6         1006        10006       0
       1007            7         1007        10007       0
       1008            8         1008        10008       0
       1009            9         1009        10009       0
       1010           10         1010        10010       0
       1011           11         1011        10011       0
       1012           12         1012        10012       0
       1013           13         1013        10013       0
       1014           14         1014        10014       0
       1015           15         1015        10015       0
       1016           16         1016        10016       0
       1017           17         1017        10017       0
       1018           18         1018        10018       0
       1019           19         1019        10019       0
       1020           20         1020        10020       0
       1021           21         1021        10021       0
       1022           22         1022        10022       0
       1023           23         1023        10023       0
       1024           24         1024        10024       0
       1025           25         1025        10025       0
       1026           26         1026        10026       0
       1027           27         1027        10027       0
       1028           28         1028        10028       0
       1029           29         1029        10029       0
       1030           30         1030        10030       0
       1031           31         1031        10031       0
       1032           32         1032        10032       0
       1033           33         1033        10033       0
       1034           34         1034        10034       0
       1035           35         1035        10035       0
       1036           36         1036        10036       0
       1037           37         1037        10037       0
       1038           38         1038        10038       0
       1039           39         1039        10039       0
       1040           40         1040        10040       0
       1041           41         1041        10041       0
       1042           42         1042        10042       0
       1043           43         1043        10043       0
       1044           44         1044        10044       0
       1045           45         1045        10045       0
       1046           46         1046        10046       0
       1047           47         1047        10047       0
       1048           48         1048        10048       0
       1049           49         1049        10049       0
       1050           50         1050        10050       0
       1051           51         1051        10051       0
       1052           52         1052        10052       0
       1053           53         1053        10053       0
       1054           54         1054        10054       0
       1055           55         1055        10055       0
       1056           56         1056        10056       0
       1057           57         1057        10057       0
       1058           58         1058        10058       0
       1059           59         1059        10059       0
       1060           60         1060        10060       0
       1061           61         1061        10061       0
       1062           62         1062        10062       0
       1063           63         1063        10063       0
       1064           64         1064        10064       0
       1065           65         1065        10065       0
       1066           66         1066        10066       0
       1067           67         1067        10067       0
       1068           68         1068        10068       0
       1069           69         1069        10069       0
       1070           70         1070        10070       0
       1071           71         1071        10071       0
       1072           72         1072        10072       0
       1073           73         1073        10073       0
       1074           74         1074        10074       0
       1075           75         1075        10075       0
       1076           76         1076        10076       0
       1077           77         1077        10077       0
       1078           78         1078        10078       0
       1079           79         1079        10079       0
       1080           80         1080        10080       0
       1081           81         1081        10081       0
       1082           82         1082        10082       0
       1083           83         1083        10083       0
       1084           84         1084        10084       0
       1085           85         1085        10085       0
       1086           86         1086        10086       0
       1087           87         1087        10087       0
       1088           88         1088        10088       0
       1089           89         1089        10089       0
       1090           90         1090        10090       0
       1091           91         1091        10091       0
       1092           92         1092        10092       0
       1093           93         1093        10093       0
       1094           94         1094        10094       0
       1095           95         1095        10095       0
       1096           96         1096        10096       0
       1097           97         1097        10097       0
       1098           98         1098        10098       0
       1099           99         1099        10099       0

--- 100 row(s) selected.
>>
>>populate all indexes on T017TTD;

--- SQL operation complete.
>>execute indexinfo using 'T017TTD';

(EXPR)                                                                            OBJECT_TYPE  INDEX_VALID_DEF  TABLE_VALID_DEF
--------------------------------------------------------------------------------  -----------  ---------------  ---------------

T017TTDIDXB                                                                       IX           Y                Y              
T017TTDIDXC                                                                       IX           Y                Y              

--- 2 row(s) selected.
>>set parserflags 1;

--- SQL operation complete.
>>select count(*) from table(index_table T017TTDIDXb);

(EXPR)              
--------------------

                 100

--- 1 row(s) selected.
>>select count(*) from table(index_table T017TTDIDXc);

(EXPR)              
--------------------

                 100

--- 1 row(s) selected.
>>--verify contents of index table defined on table with syskey and store by clause
>>showddl table(index_table T017TTdIDXb);

CREATE TABLE TRAFODION.HBASE.T017TTDIDXB
  (
    "B@"                             INT NO DEFAULT
  , A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY ("B@" ASC, A ASC, SYSKEY ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>select  "B@" , I.A ,t.b,t.c ,case when I.SYSKEY<>T.SYSKEY THEN 1 else 0 end  from table(index_table T017TTdIDXb) I join T017TTd t on I.A=t.A order by "B@";

B@           A            B            C            (EXPR)
-----------  -----------  -----------  -----------  ------

       1000            0         1000        10000       0
       1001            1         1001        10001       0
       1002            2         1002        10002       0
       1003            3         1003        10003       0
       1004            4         1004        10004       0
       1005            5         1005        10005       0
       1006            6         1006        10006       0
       1007            7         1007        10007       0
       1008            8         1008        10008       0
       1009            9         1009        10009       0
       1010           10         1010        10010       0
       1011           11         1011        10011       0
       1012           12         1012        10012       0
       1013           13         1013        10013       0
       1014           14         1014        10014       0
       1015           15         1015        10015       0
       1016           16         1016        10016       0
       1017           17         1017        10017       0
       1018           18         1018        10018       0
       1019           19         1019        10019       0
       1020           20         1020        10020       0
       1021           21         1021        10021       0
       1022           22         1022        10022       0
       1023           23         1023        10023       0
       1024           24         1024        10024       0
       1025           25         1025        10025       0
       1026           26         1026        10026       0
       1027           27         1027        10027       0
       1028           28         1028        10028       0
       1029           29         1029        10029       0
       1030           30         1030        10030       0
       1031           31         1031        10031       0
       1032           32         1032        10032       0
       1033           33         1033        10033       0
       1034           34         1034        10034       0
       1035           35         1035        10035       0
       1036           36         1036        10036       0
       1037           37         1037        10037       0
       1038           38         1038        10038       0
       1039           39         1039        10039       0
       1040           40         1040        10040       0
       1041           41         1041        10041       0
       1042           42         1042        10042       0
       1043           43         1043        10043       0
       1044           44         1044        10044       0
       1045           45         1045        10045       0
       1046           46         1046        10046       0
       1047           47         1047        10047       0
       1048           48         1048        10048       0
       1049           49         1049        10049       0
       1050           50         1050        10050       0
       1051           51         1051        10051       0
       1052           52         1052        10052       0
       1053           53         1053        10053       0
       1054           54         1054        10054       0
       1055           55         1055        10055       0
       1056           56         1056        10056       0
       1057           57         1057        10057       0
       1058           58         1058        10058       0
       1059           59         1059        10059       0
       1060           60         1060        10060       0
       1061           61         1061        10061       0
       1062           62         1062        10062       0
       1063           63         1063        10063       0
       1064           64         1064        10064       0
       1065           65         1065        10065       0
       1066           66         1066        10066       0
       1067           67         1067        10067       0
       1068           68         1068        10068       0
       1069           69         1069        10069       0
       1070           70         1070        10070       0
       1071           71         1071        10071       0
       1072           72         1072        10072       0
       1073           73         1073        10073       0
       1074           74         1074        10074       0
       1075           75         1075        10075       0
       1076           76         1076        10076       0
       1077           77         1077        10077       0
       1078           78         1078        10078       0
       1079           79         1079        10079       0
       1080           80         1080        10080       0
       1081           81         1081        10081       0
       1082           82         1082        10082       0
       1083           83         1083        10083       0
       1084           84         1084        10084       0
       1085           85         1085        10085       0
       1086           86         1086        10086       0
       1087           87         1087        10087       0
       1088           88         1088        10088       0
       1089           89         1089        10089       0
       1090           90         1090        10090       0
       1091           91         1091        10091       0
       1092           92         1092        10092       0
       1093           93         1093        10093       0
       1094           94         1094        10094       0
       1095           95         1095        10095       0
       1096           96         1096        10096       0
       1097           97         1097        10097       0
       1098           98         1098        10098       0
       1099           99         1099        10099       0

--- 100 row(s) selected.
>>
>>populate all indexes on T017TTE;

--- SQL operation complete.
>>execute indexinfo using 'T017TTE';

(EXPR)                                                                            OBJECT_TYPE  INDEX_VALID_DEF  TABLE_VALID_DEF
--------------------------------------------------------------------------------  -----------  ---------------  ---------------

T017TTEIDXB                                                                       IX           Y                Y              

--- 1 row(s) selected.
>>set parserflags 1;

--- SQL operation complete.
>>select count(*) from table(index_table T017TTEIDXb);

(EXPR)              
--------------------

                 100

--- 1 row(s) selected.
>>--verify contents of index table defined on table with syskey 
>>showddl table(index_table T017TTeIDXb);

CREATE TABLE TRAFODION.HBASE.T017TTEIDXB
  (
    "B@"                             INT NO DEFAULT
  , SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY ("B@" ASC, SYSKEY ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>select  "B@" , t.b,t.c ,case when I.SYSKEY<>T.SYSKEY THEN 1 else 0 end  from table(index_table T017TTeIDXb) I join T017TTe t on I."B@"=t.B order by "B@";

B@           B            C            (EXPR)
-----------  -----------  -----------  ------

       1000         1000        10000       0
       1001         1001        10001       0
       1002         1002        10002       0
       1003         1003        10003       0
       1004         1004        10004       0
       1005         1005        10005       0
       1006         1006        10006       0
       1007         1007        10007       0
       1008         1008        10008       0
       1009         1009        10009       0
       1010         1010        10010       0
       1011         1011        10011       0
       1012         1012        10012       0
       1013         1013        10013       0
       1014         1014        10014       0
       1015         1015        10015       0
       1016         1016        10016       0
       1017         1017        10017       0
       1018         1018        10018       0
       1019         1019        10019       0
       1020         1020        10020       0
       1021         1021        10021       0
       1022         1022        10022       0
       1023         1023        10023       0
       1024         1024        10024       0
       1025         1025        10025       0
       1026         1026        10026       0
       1027         1027        10027       0
       1028         1028        10028       0
       1029         1029        10029       0
       1030         1030        10030       0
       1031         1031        10031       0
       1032         1032        10032       0
       1033         1033        10033       0
       1034         1034        10034       0
       1035         1035        10035       0
       1036         1036        10036       0
       1037         1037        10037       0
       1038         1038        10038       0
       1039         1039        10039       0
       1040         1040        10040       0
       1041         1041        10041       0
       1042         1042        10042       0
       1043         1043        10043       0
       1044         1044        10044       0
       1045         1045        10045       0
       1046         1046        10046       0
       1047         1047        10047       0
       1048         1048        10048       0
       1049         1049        10049       0
       1050         1050        10050       0
       1051         1051        10051       0
       1052         1052        10052       0
       1053         1053        10053       0
       1054         1054        10054       0
       1055         1055        10055       0
       1056         1056        10056       0
       1057         1057        10057       0
       1058         1058        10058       0
       1059         1059        10059       0
       1060         1060        10060       0
       1061         1061        10061       0
       1062         1062        10062       0
       1063         1063        10063       0
       1064         1064        10064       0
       1065         1065        10065       0
       1066         1066        10066       0
       1067         1067        10067       0
       1068         1068        10068       0
       1069         1069        10069       0
       1070         1070        10070       0
       1071         1071        10071       0
       1072         1072        10072       0
       1073         1073        10073       0
       1074         1074        10074       0
       1075         1075        10075       0
       1076         1076        10076       0
       1077         1077        10077       0
       1078         1078        10078       0
       1079         1079        10079       0
       1080         1080        10080       0
       1081         1081        10081       0
       1082         1082        10082       0
       1083         1083        10083       0
       1084         1084        10084       0
       1085         1085        10085       0
       1086         1086        10086       0
       1087         1087        10087       0
       1088         1088        10088       0
       1089         1089        10089       0
       1090         1090        10090       0
       1091         1091        10091       0
       1092         1092        10092       0
       1093         1093        10093       0
       1094         1094        10094       0
       1095         1095        10095       0
       1096         1096        10096       0
       1097         1097        10097       0
       1098         1098        10098       0
       1099         1099        10099       0

--- 100 row(s) selected.
>>
>>--case where no indexes
>>drop index T017TTBIDXb;

--- SQL operation complete.
>>drop index T017TTBIDXc;

--- SQL operation complete.
>>drop table T017TTB;

--- SQL operation complete.
>>create  table T017TTB ( a int not null primary key, b int, c int);

--- SQL operation complete.
>>
>>load with no output, no recovery into T017TTB select * from T017TTA;

--- 100 row(s) loaded.
>>
>>populate all indexes on T017TTB;

--- SQL operation complete.
>>alter table T017TTB enable all indexes;

--- SQL operation complete.
>>
>>--shoud give error
>>update T017TTB set c = 1 ;

--- 100 row(s) updated.
>>create unique index T017TTBIDXc on T017TTB(c) no populate;

--- SQL operation complete.
>>populate all indexes on T017TTB;

*** ERROR[8110] Duplicate rows detected.

*** ERROR[1053] Unique index TRAFODION.HBASE.T017TTBIDXC could not be created because the specified column(s) contain duplicate data.

--- SQL operation failed with errors.
>>
>>--delimited table name
>>
>>insert into "T017_Delimit" values (1,1,1),(2,2,2);

--- 2 row(s) inserted.
>>
>>create index idxdelim on "T017_Delimit"(b) no populate;

--- SQL operation complete.
>>populate all indexes on "T017_Delimit";

--- SQL operation complete.
>>-------
>>
>>--verifying fix for Bug 1359872
>>--
>>select count(*) from a5table1;

(EXPR)              
--------------------

                  19

--- 1 row(s) selected.
>> create index a5iraea10 on a5table1 (ref_num asc, z_text, emp_num asc) ;

--- SQL operation complete.
>>
>> create index a5iraey10 on a5table1 (ref_num asc, z_text, emp_num desc) ;

--- SQL operation complete.
>>
>>select count(*) from table(index_table a5iraea10);

(EXPR)              
--------------------

                  19

--- 1 row(s) selected.
>>select count(*) from table(index_table a5iraey10);

(EXPR)              
--------------------

                  19

--- 1 row(s) selected.
>>
>>drop  index T017TTCIDXb;

--- SQL operation complete.
>>
>>create index T017TTCIDXb on T017TTC (b) HBASE_OPTIONS (DATA_BLOCK_ENCODING = 'FAST_DIFF', COMPRESSION = 'GZ') SALT LIKE TABLE;

--- SQL operation complete.
>>
>>select * from table (index_table T017TTCIDXb) order by "_SALT_@","B@","A";

_SALT_@     B@           A          
----------  -----------  -----------

         0         1000            0
         0         1001            1
         0         1002            2
         0         1004            4
         0         1009            9
         0         1010           10
         0         1012           12
         0         1013           13
         0         1014           14
         0         1017           17
         0         1019           19
         0         1023           23
         0         1027           27
         0         1030           30
         0         1034           34
         0         1036           36
         0         1037           37
         0         1041           41
         0         1043           43
         0         1044           44
         0         1045           45
         0         1047           47
         0         1049           49
         0         1050           50
         0         1051           51
         0         1052           52
         0         1053           53
         0         1054           54
         0         1055           55
         0         1056           56
         0         1058           58
         0         1059           59
         0         1061           61
         0         1064           64
         0         1067           67
         0         1069           69
         0         1072           72
         0         1073           73
         0         1076           76
         0         1077           77
         0         1078           78
         0         1079           79
         0         1080           80
         0         1082           82
         0         1084           84
         0         1085           85
         0         1086           86
         0         1087           87
         0         1088           88
         0         1090           90
         0         1093           93
         0         1097           97
         0         1098           98
         0         1099           99
         1         1003            3
         1         1005            5
         1         1006            6
         1         1007            7
         1         1008            8
         1         1011           11
         1         1015           15
         1         1016           16
         1         1018           18
         1         1020           20
         1         1021           21
         1         1022           22
         1         1024           24
         1         1025           25
         1         1026           26
         1         1028           28
         1         1029           29
         1         1031           31
         1         1032           32
         1         1033           33
         1         1035           35
         1         1038           38
         1         1039           39
         1         1040           40
         1         1042           42
         1         1046           46
         1         1048           48
         1         1057           57
         1         1060           60
         1         1062           62
         1         1063           63
         1         1065           65
         1         1066           66
         1         1068           68
         1         1070           70
         1         1071           71
         1         1074           74
         1         1075           75
         1         1081           81
         1         1083           83
         1         1089           89
         1         1091           91
         1         1092           92
         1         1094           94
         1         1095           95
         1         1096           96

--- 100 row(s) selected.
>>
>>explain options 'f' Load transform into table(index_table  T017TTCIDXb ) select "_SALT_","B","A" from T017TTC for read uncommitted access;

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

4    .    5    root                                                  1.00E+002
3    .    4    esp_exchange                    1:2(range)            1.00E+002
1    2    3    tuple_flow                                            1.00E+002
.    .    2    trafodion_load_prepa            T017TTCIDXB           1.00E+000
.    .    1    trafodion_index_scan            T017TTCIDXB           1.00E+002

--- SQL operation complete.
>>
>>explain options 'f' Load transform into table(index_table  T017TTCIDXb ) select "_SALT_","B","A" from T017TTC <<+ cardinality 10e1 >> for read uncommitted access;

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

4    .    5    root                                                  1.00E+002
3    .    4    esp_exchange                    1:2(range)            1.00E+002
1    2    3    tuple_flow                                            1.00E+002
.    .    2    trafodion_load_prepa            T017TTCIDXB           1.00E+000
.    .    1    trafodion_index_scan            T017TTCIDXB           1.00E+002

--- SQL operation complete.
>>
>>
>>drop  index T017TTCIDXb;

--- SQL operation complete.
>>
>>create index T017TTCIDXb on T017TTC (b);

--- SQL operation complete.
>>
>>select * from table (index_table T017TTCIDXb) order by "B@","_SALT_","A";

B@           _SALT_      A          
-----------  ----------  -----------

       1000           0            0
       1001           0            1
       1002           0            2
       1003           1            3
       1004           0            4
       1005           1            5
       1006           1            6
       1007           1            7
       1008           1            8
       1009           0            9
       1010           0           10
       1011           1           11
       1012           0           12
       1013           0           13
       1014           0           14
       1015           1           15
       1016           1           16
       1017           0           17
       1018           1           18
       1019           0           19
       1020           1           20
       1021           1           21
       1022           1           22
       1023           0           23
       1024           1           24
       1025           1           25
       1026           1           26
       1027           0           27
       1028           1           28
       1029           1           29
       1030           0           30
       1031           1           31
       1032           1           32
       1033           1           33
       1034           0           34
       1035           1           35
       1036           0           36
       1037           0           37
       1038           1           38
       1039           1           39
       1040           1           40
       1041           0           41
       1042           1           42
       1043           0           43
       1044           0           44
       1045           0           45
       1046           1           46
       1047           0           47
       1048           1           48
       1049           0           49
       1050           0           50
       1051           0           51
       1052           0           52
       1053           0           53
       1054           0           54
       1055           0           55
       1056           0           56
       1057           1           57
       1058           0           58
       1059           0           59
       1060           1           60
       1061           0           61
       1062           1           62
       1063           1           63
       1064           0           64
       1065           1           65
       1066           1           66
       1067           0           67
       1068           1           68
       1069           0           69
       1070           1           70
       1071           1           71
       1072           0           72
       1073           0           73
       1074           1           74
       1075           1           75
       1076           0           76
       1077           0           77
       1078           0           78
       1079           0           79
       1080           0           80
       1081           1           81
       1082           0           82
       1083           1           83
       1084           0           84
       1085           0           85
       1086           0           86
       1087           0           87
       1088           0           88
       1089           1           89
       1090           0           90
       1091           1           91
       1092           1           92
       1093           0           93
       1094           1           94
       1095           1           95
       1096           1           96
       1097           0           97
       1098           0           98
       1099           0           99

--- 100 row(s) selected.
>>
>>explain options 'f' Load transform into table(index_table  T017TTCIDXb ) select "B","_SALT_","A" from T017TTC for read uncommitted access;

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

3    .    4    root                                                  1.00E+002
1    2    3    tuple_flow                                            1.00E+002
.    .    2    trafodion_load_prepa            T017TTCIDXB           1.00E+000
.    .    1    trafodion_index_scan            T017TTCIDXB           1.00E+002

--- SQL operation complete.
>>
>>explain options 'f' Load transform into table(index_table  T017TTCIDXb ) select "B","_SALT_","A" from T017TTC <<+ cardinality 10e1 >> for read uncommitted access;

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

3    .    4    root                                                  1.00E+002
1    2    3    tuple_flow                                            1.00E+002
.    .    2    trafodion_load_prepa            T017TTCIDXB           1.00E+000
.    .    1    trafodion_index_scan            T017TTCIDXB           1.00E+002

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