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)