[jira] [Created] (TRAFODION-1028) LP Bug: 1427414 - Update stats should invalidate the cached histogram of the table

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

Alice Chen created TRAFODION-1028:
-------------------------------------

             Summary: LP Bug: 1427414 - Update stats should invalidate the cached histogram of the table
                 Key: TRAFODION-1028
                 URL: https://issues.apache.org/jira/browse/TRAFODION-1028
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmp
            Reporter: Hans Zeller
            Priority: Critical


Anoop found this test case, a piece from seabase/TEST016. What happens here is that we create a table and insert data, then update stats, then do a select. The select statement uses a fake histogram for a column (UEC is 2). When we exit sqlci and do the same select only in a new session, we get the good histogram with a UEC of 1.

drop table mcStatPart7;

create table mcStatPart7
(a smallint not null not droppable,
b largeint not null not droppable,
f int, txt char(100),
primary key (a,b))
salt using 8 partitions ;

insert into mcStatPart7 values (1,100000,1,'xyz'),(1,200000,1,'xyz'),(1,600000,1,'xyz'),(2,300000,1,'xyz'),(2,400000,1,'xyz'),(2,500000,1,'xyz'),(2,600000,1,'xyz'),
                           (3,100000,1,'xyz'),(3,200000,1,'xyz'),(3,600000,1,'xyz'),(4,300000,1,'xyz'),(4,400000,1,'xyz'),(4,500000,1,'xyz'),(4,600000,1,'xyz');

update statistics for table mcStatPart7 on every column, (b,a,"_SALT_");


prepare st1 from select f,count(f) from mcStatPart7 <<+ cardinality 10e8 >> group by f;

explain options 'f' st1;

-- Result cardinality (UEC of column F) is 2. GUI display shows a fake histogram with UEC of 2.

-- Now exit sqlci and explain the same query again. This time the cardinality is 1. GUI display shows a real histogram with UEC of 1.



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