[jira] [Created] (TRAFODION-741) LP Bug: 1389797 - Select count(*) shows wrong result for upsert after purgedata

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

Alice Chen created TRAFODION-741:
------------------------------------

             Summary: LP Bug: 1389797 - Select count(*) shows wrong result for upsert after purgedata
                 Key: TRAFODION-741
                 URL: https://issues.apache.org/jira/browse/TRAFODION-741
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
            Reporter: Weishiun Tsai
            Assignee: justin.du@hp.com
            Priority: Critical


Select count(*) would often show a wrong result after the data in a table is purged using purgedata, and repopulated using upsert.  This is seen on the v1104_0830 build installed both on a HortonWorks cluster and a Cloudera cluster.   The problem existed even prior to the v1104_0830 build.

As shown in the execution output here, the first set of the upsert and select count(*) showed the correct 10500 rows.  After the purgedata, the second set of upsert and select count(*) showed the wrong result as 7485 rows.  As a matter of fact, the two consecutive select count(*) statements showed 2 different results (7485 rows and 9682 rows), both of them are wrong.

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

Here is the entire script to reproduce it, but it will require the QA global table g_cmureg.dat10500

set schema mytest;

create table pd33210
(
unique1         int            not null not droppable,
unique2         int            not null not droppable
primary key not droppable,
two             int            not null not droppable,
four            int            not null not droppable,
ten             int            not null not droppable,
twenty          int            not null not droppable,
onePercent      int            not null not droppable,
tenPercent      int            not null not droppable,
twentyPercent   int            not null not droppable,
fiftyPercent    int            not null not droppable,
unique3         int            not null not droppable,
evenOnePercent  int             not null not droppable,
oddOnePercent   int             not null not droppable,
stringu1        char(52)        not null not droppable,
stringu2        char(52)        not null not droppable,
string4         char(52)        not null not droppable
);

create view pd33210v1 (v_unique3, v_ten) as select unique3, ten from pd33210;

create index pd33210a1 on pd33210 (two , four);

upsert using load into trafodion.mytest.pd33210 select * from trafodion.g_cmureg.dat10500;

select count(*) from trafodion.mytest.pd33210;

PURGEDATA trafodion.mytest.pd33210;

select * from trafodion.mytest.pd33210;

select * from trafodion.mytest.pd33210v1;

upsert using load into trafodion.mytest.pd33210 select * from trafodion.g_cmureg.dat10500;

select count(*) from trafodion.mytest.pd33210;

prepare xx from select count(*) from trafodion.mytest.pd33210;
explain options 'f' xx;
execute xx;

drop schema mytest cascade;

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

>>set schema mytest;

--- SQL operation complete.
>>
>>create table pd33210
+>(
+>unique1         int            not null not droppable,
+>unique2         int            not null not droppable
+>primary key not droppable,
+>two             int            not null not droppable,
+>four            int            not null not droppable,
+>ten             int            not null not droppable,
+>twenty          int            not null not droppable,
+>onePercent      int            not null not droppable,
+>tenPercent      int            not null not droppable,
+>twentyPercent   int            not null not droppable,
+>fiftyPercent    int            not null not droppable,
+>unique3         int            not null not droppable,
+>evenOnePercent  int             not null not droppable,
+>oddOnePercent   int             not null not droppable,
+>stringu1        char(52)        not null not droppable,
+>stringu2        char(52)        not null not droppable,
+>string4         char(52)        not null not droppable
+>);

--- SQL operation complete.
>>
>>create view pd33210v1 (v_unique3, v_ten) as select unique3, ten from pd33210;

--- SQL operation complete.
>>
>>create index pd33210a1 on pd33210 (two , four);

--- SQL operation complete.
>>
>>upsert using load into trafodion.mytest.pd33210 select * from trafodion.g_cmureg.dat10500;

--- 10500 row(s) inserted.
>>
>>select count(*) from trafodion.mytest.pd33210;

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

               10500

--- 1 row(s) selected.
>>
>>PURGEDATA trafodion.mytest.pd33210;

--- SQL operation complete.
>>
>>select * from trafodion.mytest.pd33210;

--- 0 row(s) selected.
>>
>>select * from trafodion.mytest.pd33210v1;

--- 0 row(s) selected.
>>
>>upsert using load into trafodion.mytest.pd33210 select * from trafodion.g_cmureg.dat10500;

--- 10500 row(s) inserted.
>>
>>select count(*) from trafodion.mytest.pd33210;

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

                7485

--- 1 row(s) selected.
>>
>>prepare xx from select count(*) from trafodion.mytest.pd33210;

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

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

1    .    2    root                                                  1.00E+000
.    .    1    hbase_coproc_aggr                                     1.00E+000

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

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

                9682

--- 1 row(s) selected.
>>
>>drop schema mytest cascade;

--- SQL operation complete.



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