[
https://issues.apache.org/jira/browse/TRAFODION-2964?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16372090#comment-16372090
]
ASF GitHub Bot commented on TRAFODION-2964:
-------------------------------------------
Github user asfgit closed the pull request at:
https://github.com/apache/trafodion/pull/1450
> 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)
