[jira] [Commented] (TRAFODION-2964) New MDAM costing code incorrectly assumes key column is always on the left

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-2964?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16370429#comment-16370429 ] 

ASF GitHub Bot commented on TRAFODION-2964:
-------------------------------------------

GitHub user DaveBirdsall opened a pull request:

    https://github.com/apache/trafodion/pull/1450

    [TRAFODION-2964] Fix bug in new MDAM costing code concerning comparisons

    This change fixes a bug in the new MDAM costing code. The code checks for a mix of less than and greater than predicates and if so detected uses the square of the HIST_DEFAULT_SEL_FOR_PRED_RANGE when calculating the UEC for the range. But the code was assuming the key column was always on the left. It turns out this assumption is not true. With this change, we no longer make this assumption but instead check to see which side of the comparison operator has the key column.

You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/DaveBirdsall/trafodion Trafodion2964

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/trafodion/pull/1450.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 #1450
    
----
commit 55211ac16c395a6e65205ec4f50fcce6b6fef28b
Author: Dave Birdsall <db...@...>
Date:   2018-02-20T18:34:40Z

    [TRAFODION-2964] Fix bug in new MDAM costing code concerning comparisons

----


> New MDAM costing code incorrectly assumes key column is always on the left
> --------------------------------------------------------------------------
>
>                 Key: TRAFODION-2964
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-2964
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>    Affects Versions: 2.3
>            Reporter: David Wayne Birdsall
>            Assignee: David Wayne Birdsall
>            Priority: Major
>
> With Jira TRAFODION-2645, the MDAM costing code has been rewritten. The new code is turned off by default at the moment until it can undergo large-scale testing.
> But a bug has been noticed in this code. Method NewMDAMOptimalDisjunctPrefixWA::calculateMetricsFromKeyPred assumes in its predicate analysis that the key column is always on the left. (This is significant only for <, <=, > and >= predicates.) But it turns out this is not always true.
> For example, in the following theta-join query, when MDAM is considered on the inner table of a nested loop join, and FACT1 is the inner table, the key column is on the right of the join predicate:
> select  * from district d join fact1 f  on d.district_number > f.district_number where f.serial_number > 100;
> The DDL used for this example is:
> ```
> >>showddl fact1;
> CREATE TABLE TRAFODION.SCH.FACT1
>  (
>  DISTRICT_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
>  SERIALIZED
>  , SERIAL_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
>  SERIALIZED
>  , STUFF INT DEFAULT NULL NOT SERIALIZED
>  , PRIMARY KEY (DISTRICT_NUMBER ASC, SERIAL_NUMBER ASC)
>  )
>  SALT USING 4 PARTITIONS
>  ATTRIBUTES ALIGNED FORMAT
> ;
> -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SCH.FACT1 TO DB__ROOT WITH GRANT OPTION;
> --- SQL operation complete.
> >>showddl district;
> CREATE TABLE TRAFODION.SCH.DISTRICT
>  (
>  DISTRICT_NAME CHAR(20) CHARACTER SET ISO88591 COLLATE
>  DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
>  , DISTRICT_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
>  SERIALIZED
>  , PRIMARY KEY (DISTRICT_NAME ASC)
>  )
>  ATTRIBUTES ALIGNED FORMAT
> ;
> -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SCH.DISTRICT TO DB__ROOT WITH GRANT OPTION;
> --- SQL operation complete.
> >>
> ```
> Here, DISTRICT was populated with 10 rows, FACT1 with 2 million rows. UPDATE STATISTICS was done on both. Notice that FACT1 is salted but DISTRICT is not.
> CQD MDAM_COSTING_REWRITE 'ON' was in force to get the new costing code. The following CQDs also were in force (these force a horrible plan. by the way, but it illustrates the bug):
> cqd keyless_nested_joins 'ON';
> cqd MDAM_UNDER_NJ_PROBES_THRESHOLD '10000000';
> cqd hash_joins 'OFF';
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)