[jira] [Created] (TRAFODION-457) LP Bug: 1343455 - running update statistics and select statements from the same session results in card mismatch

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

Alice Chen created TRAFODION-457:
------------------------------------

             Summary: LP Bug: 1343455 - running update statistics and select statements from the same session results in card mismatch
                 Key: TRAFODION-457
                 URL: https://issues.apache.org/jira/browse/TRAFODION-457
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmp
            Reporter: Ravisha Neelakanthappa
            Assignee: Apache Trafodion
            Priority: Minor


This problem is probably related to query caching and should be fixed otherwise we can't really test cardinality related probelms in our regression suites.

How to reproduce:
>>create table T011T2 (a int not null, b char(500),
+>                     c int not null, primary key(a))
+>salt using 4 partitions;

--- SQL operation complete.
>>
>>insert into T011T2 values (1, 'a', 11), (2, 'b', 22), (3, 'c', 33);

--- 3 row(s) inserted.
>>insert into T011T2 values (10, 'aa', 110), (20, 'bb', 220), (30, 'cc', 330);

--- 3 row(s) inserted.
>>insert into T011T2 values (11, 'aaa', 111), (22, 'bbb', 222), (33, 'ccc', 333);

--- 3 row(s) inserted.
>>update statistics for table T011T2 on every column;

--- SQL operation complete.
>>
>>-- should get serial plans
>>explain options 'f'
+>select b, c
+>from T011T2
+>where a = ?;

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

1    .    2    root                  o                               5.00E+001
.    .    1    trafodion_scan                  T011T2                5.00E+001

--- SQL operation complete.
>>

The correct cardinality estimation for T011T2 should be 1.

if you run the same select from a new sqlci session, you would see correct cardinality of 1 row as shown below:

>>set schema trafodion.sch;

--- SQL operation complete.
>>explain options 'f'
+>select b, c from T011T2
+>where a = ?;

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

1    .    2    root                  o                               1.00E+000
.    .    1    trafodion_scan                  T011T2                1.00E+000

--- SQL operation complete.
>>

Our regressions tests run both update stat and queries in the same sqlci session.
Assigned to LaunchPad User taoufik ben abdellatif



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