[jira] [Created] (TRAFODION-564) LP Bug: 1359872 - 2nd create index hangs

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-564:
------------------------------------

             Summary: LP Bug: 1359872 - 2nd create index hangs
                 Key: TRAFODION-564
                 URL: https://issues.apache.org/jira/browse/TRAFODION-564
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
            Reporter: Weishiun Tsai
            Assignee: Apache Trafodion
            Priority: Blocker
             Fix For: 0.9 (pre-incubation)


The following sequence of statements hangs at the 2nd create index.  The table is very small and has only 19 rows.  It may have something to do with its being the 2nd create index statement in the sequence.  If the first create index statement is commented out, the statement that hanged previously completes right away.

This is a regression.  On both a Cloudera cluster and a HortonWorks cluster, this statement had hanged for 15+ hours before we had to kill it.  The problem can be seen on a workstation installation as well.

--------------------------------------------------------
Here is the entire script to reproduce this problem:

drop schema mytest2 cascade;
set schema mytest2;

create table a5table1
( ref_num     largeint
, z_text      char(3)
, emp_num     smallint
) no partition
;

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

select count(*) from a5table1;
create index a5iraea on a5table1 (ref_num asc, z_text, emp_num asc);
create index a5iraed on a5table1 (ref_num asc, z_text, emp_num desc);

--------------------------------------------------------

Here is the execution output when it hangs:

>>drop schema mytest2 cascade;

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

--- SQL operation complete.
>>
>>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.
>>
>>select count(*) from a5table1;

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

                  19

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

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

--------------------------------------------------------

Here is the execution output to show that the same create index statement runs fine if the first create index is commented out:

>>drop schema mytest2 cascade;

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

--- SQL operation complete.
>>
>>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.
>>
>>select count(*) from a5table1;

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

                  19

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

--- SQL operation complete.
Assigned to LaunchPad User khaled Bouaziz



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)