[TRAFODION-2736] Missing predicates on salt columns when using an index join

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0-incubating
• Fix Version/s: 2.3
• Component/s: sql-cmp
• Environment: Any
• Assignee: Hans Zeller
• Reporter: Hans Zeller
• Created: 12/Sep/17 19:28
• Updated: 04/Oct/17 01:27
• Resolved: 04/Oct/17 01:27

Description

When we have salted tables, the compiler is supposed to generate predicates on the salt column automatically, based on predicates on the columns used for salting.

Suresh and Benny found a case where we don't do that, related to index joins (a join between an index and its base table). Here is an example:

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 s;

In the explain, we would expect a predicate on the salt column but there is none.