[
https://issues.apache.org/jira/browse/TRAFODION-231?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Atanu Mishra closed TRAFODION-231.
----------------------------------
Resolution: Fixed
Assignee: (was: kai-hua.xu)
Fix Version/s: 0.8 (pre-incubation)
> LP Bug: 1309121 - select count(*) does not reflect upsert result immediately
> ----------------------------------------------------------------------------
>
> Key: TRAFODION-231
> URL: https://issues.apache.org/jira/browse/TRAFODION-231
> Project: Apache Trafodion
> Issue Type: Bug
> Components: dtm
> Reporter: Weishiun Tsai
> Priority: Critical
> Fix For: 0.8 (pre-incubation)
>
>
> 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>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
