Hans Zeller created TRAFODION-2751:
--------------------------------------
Summary: Unnecessary PROBE_CACHE in an index join
Key: TRAFODION-2751
URL: https://issues.apache.org/jira/browse/TRAFODION-2751
Project: Apache Trafodion
Issue Type: Bug
Components: sql-cmp
Affects Versions: 2.1-incubating
Reporter: Hans Zeller
Assignee: Hans Zeller
Fix For: 2.2-incubating
When we transform a scan into a nested join of an index and its base table,
this join is on the clustering key columns, which are present and unique in
both the index and the base table.
Therefore, this is a 1:1 join with unique matches on each side. There is no use
in a probe cache, since we will never probe for the same base table key twice.
Right now we see a probe cache, though. There are a few reasons and I'm not
sure whether this is a regression or an old bug.
The reason: When we create the index join, in
IndexJoinRule1::makeSubstituteFromIndexInfo() in file
core/sql/optimizer/TransRule.cpp, we don't synthesize logical properties for
the join. This is correct, in principle, because its logical properties are the
same as those of the scan. However, the synthLogProp() method also sets some
local variables in the join that are used to determine whether a probe cache is
useful: leftHasUniqueMatches_ and rightHasUniqueMatches_.
Here is a test case:
create table tdi(service_id int not null,
user_number int not null,
start_time timestamp not null,
b int,
primary key(service_id, user_number, start_time))
division by (date_trunc('day', start_time))
salt using 4 partitions on (user_number);
create index tdix on tdi(user_number, start_time) salt like table;
control query shape join(cut,cut);
prepare s from
select * from tdi where user_number = 1234 and start_time between timestamp
'2017-01-01 00:00:00' and timestamp '2017-01-15 00:00:00';
explain options 'f' s;
The explain shows a PROBE_CACHE, which is unnecessary.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
