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

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

David Wayne Birdsall created TRAFODION-2964:
-----------------------------------------------

             Summary: 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


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)