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

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/TRAFODION-2964?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

David Wayne Birdsall resolved TRAFODION-2964.
---------------------------------------------
       Resolution: Fixed
    Fix Version/s: 2.3

> 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
>             Fix For: 2.3
>
>
> 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)