[TRAFODION-231] LP Bug: 1309121 - select count(*) does not reflect upsert result immediately

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: 0.8 (pre-incubation)
• Component/s: dtm
• External issue URL: https://bugs.launchpad.net/bugs/1309121
• Assignee: Unassigned
• Reporter: Weishiun Tsai
• Created: 22/Jul/15 18:14
• Updated: 21/Aug/15 06:49
• Resolved: 21/Aug/15 06:49

Description

We often see this inconsistent row count returned by select count right after upsert. The select statement would show a lower row count. The correct value is not returned until the user waits for a while. As shown here, the table fourmillion should really have 4000000 rows, but the select count issued immediately after the upsert shows only 1842367 rows. The user has to wait for a while and reissue the select statement again to see the correct 4000000 rows returned:

SQL>set schema trafodion.seaquest_fixes_m6;
— SQL operation complete.

SQL>create table ten (a int not null) store by (a);
— SQL operation complete.

SQL>create table hundred (a int not null) store by (a);
— SQL operation complete.

SQL>create table tenK (a int not null) store by (a);
— SQL operation complete.

SQL>create table fourmillion (a largeint not null, b largeint default 0 )
no partition
--store by (a)
;
— SQL operation complete.

-------------------------------------------------------------------------------
== TEST: tcase.test001
-------------------------------------------------------------------------------
SQL>insert into ten values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
— 10 row(s) inserted.

SQL>upsert using load into hundred
select t1.a + t2.a from ten t1, ten t2;
— SQL operation complete.

SQL>select count from hundred;
(EXPR)
--------------------
100

— 1 row(s) selected.

SQL>upsert using load into tenK
select t1.a + t2.a from hundred t1, hundred t2;
— SQL operation complete.

SQL>select count from tenK;
(EXPR)
--------------------
10000

— 1 row(s) selected.

SQL>upsert using load into fourmillion
select t1.a + t2.a +t3.a, 0 from hundred t1, tenK t2, ten t3
where t3.a in (3,5,7,9);
— SQL operation complete.

SQL>select count from fourmillion;
(EXPR)
--------------------
1842367

— 1 row(s) selected.
=================================================

After waiting for a while and reissue the same select statement again:

-bash-4.1$ trafci.sh -h centos-mapr1.hpl.hp.com:37800 -u dontcare -p dontcare
Welcome to Trafodion Command Interface
Copyright(C) 2013-2014 Hewlett-Packard Development Company, L.P.

Host Name/IP Address: centos-mapr1.hpl.hp.com:37800
User Name: dontcare

Connected to Trafodion

SQL>select count from trafodion.seaquest_fixes_m6.fourmillion;

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

— 1 row(s) selected.

SQL>