Major Description
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';
Attachments
Issue Links
- links to
