You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "David Wayne Birdsall (JIRA)" <ji...@apache.org> on 2015/10/07 01:34:26 UTC
[jira] [Commented] (TRAFODION-457) LP Bug: 1343455 - running update
statistics and select statements from the same session results in card
mismatch
[ https://issues.apache.org/jira/browse/TRAFODION-457?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14946002#comment-14946002 ]
David Wayne Birdsall commented on TRAFODION-457:
------------------------------------------------
I reproduced this issue. If I add "cqd CACHE_HISTOGRAMS 'OFF' at the beginning, the problem does not occur. We get a correct cardinality of 1. This seems to be the same problem as JIRA TRAFODION-882.
> 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: David Wayne Birdsall
> 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)