User was granted select privilege, but query plans generated without using histogram stats

[Julie Thai]

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;

[Roberta Marton (roberta-marton)]

The issue is because the person compiling the query does not have select privilege on the HISTOGRAMS table so statistics are not retrieved.

Histograms are being retrieved on behalf of the compilation. We support a special interface to allow metadata tables to be accessed without privs for operations performed during compilations. However, this interface is not being used for HISTOGRAMS tables during query compilation.

[Roberta Marton (roberta-marton)]

Fixed code so that histograms statistics can be used during plan generation.