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.