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]