Yang Jie created SPARK-61871:
--------------------------------

             Summary: CBO estimates an equi-join between CHAR/VARCHAR keys of 
different types as a cartesian product
                 Key: SPARK-61871
                 URL: https://issues.apache.org/jira/browse/SPARK-61871
             Project: Spark
          Issue Type: Improvement
          Components: SQL
    Affects Versions: 5.0.0
            Reporter: Yang Jie


With {{spark.sql.cbo.enabled=true}}, an equi-join between CHAR or VARCHAR 
columns of different types gets no estimate from the key statistics. Type 
coercion widens the shorter key, and 
{{JoinEstimation.extractJoinKeysWithColStats}} only takes a pair of attributes:
* In the default mode, {{CHAR(5) = CHAR(10)}} compares {{rpad(c, 10, ' ')}}.
* When {{spark.sql.charVarchar.standardSemantics.enabled}} or 
{{spark.sql.preserveCharVarcharTypeInfo}} is true, {{VARCHAR(5) = VARCHAR(10)}} 
compares {{cast(c as varchar(10))}}, and {{CHAR(5) = VARCHAR(5)}} compares 
{{cast(c as varchar(5))}}.

Measured on master after ANALYZE, with 1000 rows and 10 distinct values on each 
side:

||join keys||default mode||either conf||
|CHAR(5) = CHAR(10)|1000000|1000000|
|VARCHAR(5) = VARCHAR(10)|100000|1000000|
|CHAR(5) = VARCHAR(5)|100000|1000000|
|STRING = STRING|100000|100000|

The widening takes the longer length, so it never truncates a value. A cast to 
VARCHAR(m) returns a value of at most m characters unchanged, and padding or a 
cast to CHAR(m) adds trailing spaces up to m. The key's distinct count 
therefore carries over, except for stored values that differ only in trailing 
spaces, which is the caveat SPARK-60810 already accepts for read-side padding. 
SPARK-60830 leaves these joins as they are, and the 4.4 migration guide entry 
says so.



--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to