[jira] [Created] (TRAFODION-1215) LP Bug: 1450193 - User was granted select privilege, but query plans generated without using histogram stats

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

Alice Chen created TRAFODION-1215:
-------------------------------------

             Summary: LP Bug: 1450193 - User was granted select privilege, but query plans generated without using histogram stats
                 Key: TRAFODION-1215
                 URL: https://issues.apache.org/jira/browse/TRAFODION-1215
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-security
            Reporter: Julie Thai
            Assignee: Roberta Marton
            Priority: Critical
             Fix For: 2.0-incubating


Schema & table owned and update-stats issued, by user 'trafodion'. SELECT privilege on table granted to user 'qauser_cmp'. Subsequent, queries prepared by user 'qauser_cmp' return "WARNING[6008] Statistics for column...were not available. As a result, the access path chosen might not be the best possible."

Workaround: grant select on sb_histograms, sb_histogram_intervals to public.

Cardinality/query plans generated by 'qauser_cmp' versus 'trafodion' differ. 

As 'qauser_cmp':
SQL>prepare XX from select * from f00 where colnum = 649991.789;

*** WARNING[6008] Statistics for column (COLKEY) from table TRAFODION.F00SCH.F00 were not available. As a result, the access path chosen might not be the best possible. [2015-04-29 13:10:04]
*** WARNING[6008] Statistics for column (COLNUM) from table TRAFODION.F00SCH.F00 were not available. As a result, the access path chosen might not be the best possible. [2015-04-29 13:10:04]
--- SQL command prepared.

SQL>explain options 'f' XX;


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

1    .    2    root                                                  1.00E+003
.    .    1    trafodion_scan                  F00                   1.00E+003

--- SQL operation complete.

Whereas, as 'trafodion':
SQL>prepare XX from select * from f00 where colnum = 649991.789;

--- SQL command prepared.

SQL>explain options 'f' XX;


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

1    .    2    root                                                  1.00E+001
.    .    1    trafodion_scan                  F00                   1.00E+001

--- SQL operation complete.

To reproduce:

1.  In trafci, user 'trafodion':
cqd TRAF_LOAD_PREP_TMP_LOCATION '/bulkload/';
cqd TRAF_LOAD_TAKE_SNAPSHOT 'OFF';
drop schema f00sch cascade;
create schema f00sch;
set schema f00sch;
drop table f00;
create table f00(
   colkey int not null primary key,
   colnum numeric(11,3));
load into f00 select
c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000, --colkey
cast((c1+c2*10+c3*100+c4*1000+c5*10000+c6*100000) as numeric(11,3)) --colnum
from (values(1)) t
transpose 0,1,2,3,4,5,6,7,8,9 as c1
transpose 0,1,2,3,4,5,6,7,8,9 as c2
transpose 0,1,2,3,4,5,6,7,8,9 as c3
transpose 0,1,2,3,4,5,6,7,8,9 as c4
transpose 0,1,2,3,4,5,6,7,8,9 as c5
transpose 0,1,2,3,4,5,6,7,8,9 as c6;
update statistics for table f00 on every column;
showstats for table f00 on colnum detail;
grant select on table f00 to public;

2. In trafci, user 'qauser_cmp':
set schema f00sch;
prepare XX from select * from f00 where colnum = 649991.789;
explain options 'f' XX;
prepare XX from select * from f00
where colnum between 333333.123 and 333353.789;
explain options 'f' XX;
prepare XX from select count(*) from f00
where colnum > 878787;
explain options 'f' XX;
showstats for table f00 on colnum detail;



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