[TRAFODION-3050] Inefficient plan when joining on VARCHAR columns

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.3
• Fix Version/s: 2.3
• Component/s: sql-cmp
• Assignee: Dave Birdsall
• Reporter: Dave Birdsall
• Created: 30/Apr/18 18:47
• Updated: 13/Aug/18 22:35
• Resolved: 11/May/18 19:39

Description

This is follow-on work to Jira TRAFODION-3031. That Jira fixed some issues with plan choice when joining on integer columns.

In this particular example, the join is on VARCHAR columns. With the fix from Jira TRAFODION-3031, we get a nice nested join plan for the subqueries, but still get a hash join at the top, which is inefficient in this example.

The attached script reproduces the problem and demonstrates a work-around. When CQD COMP_BOOL_158 is set to 'ON', we get a good plan. The attached log shows the script results.

Issue Links

• links to: GitHub Pull Request #1546