[
https://issues.apache.org/jira/browse/TRAFODION-3050?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16459065#comment-16459065
]
ASF GitHub Bot commented on TRAFODION-3050:
-------------------------------------------
GitHub user DaveBirdsall opened a pull request:
https://github.com/apache/trafodion/pull/1546
[TRAFODION-3050] Change default for CQD COMP_BOOL_158 to 'ON'
This pull request changes the default for CQD COMP_BOOL_158 to 'ON'.
This causes equi-join predicates on VARCHAR/VARCHAR and CHAR/VARCHAR pairs
of columns to be rewritten as VEGPreds, enabling transitive closure on them. It
also makes the predicate available to the scan index elimination logic
(Scan::addIndexInfo) so that certain queries that formerly did not make use of
certain indexes will now consider them. This can lead to very efficient nested
join plans in some cases.
There is a side effect to turning this CQD on, however. By allowing free
use of VEGPreds for such equi-join predicates, the compiler may substitute
references to one column with another, if the two columns compare as equal in a
join predicate.
This side effect is thought to be less undesirable at this time than
getting bad query plans when such equi-join predicates are present.
Nevertheless, a separate JIRA, TRAFODION-3051, has been written to address
this side effect. When that one is fixed, we will be able to have our cake and
eat it too.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/DaveBirdsall/trafodion Trafodion3050
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/trafodion/pull/1546.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1546
----
commit cd1fde8a263ab66295692309e6b80e1af3ec8ab9
Author: Dave Birdsall <dbirdsall@...>
Date: 2018-04-30T20:56:09Z
[TRAFODION-3050] Change default for CQD COMP_BOOL_158 to 'ON'
----
> Inefficient plan when joining on VARCHAR columns
> ------------------------------------------------
>
> Key: TRAFODION-3050
> URL: https://issues.apache.org/jira/browse/TRAFODION-3050
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.3
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
> Priority: Major
> Attachments: JiraVarchar.log, JiraVarchar.sql
>
>
> 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.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
