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)
