Understanding least common type in databricks

Dhruv-22 — Wed, 03 Apr 2024 10:04:55 GMT

I was reading the data type rules and found about least common type.

I have a doubt. What is the least common type of STRING and INT? The referred link gives the following example saying the least common type is BIGINT.

-- The least common type between an INT and STRING is BIGINT
> SELECT typeof(coalesce(5, '6'));
  BIGINT

However, running this on Databricks 13.3, Spark 3.4.1 and Scala 2.12 configurations gives the following output.

> SELECT typeof(coalesce(5, '6'));
  STRING

If I run explain on the query, then I get the following output.

> EXPLAIN EXTENDED SELECT typeof(coalesce(5, '6'));
== Analyzed Logical Plan ==
typeof(coalesce(5, 6)): string
Project [typeof(coalesce(cast(5 as string), 6)) AS typeof(coalesce(5, 6))#239637]
+- OneRowRelation

It shows that INT is cast to STRING so the least common type should be STRING.

However, if I run an equal to operation between STRING and INT, then the explain query gives different output.

> EXPLAIN EXTENDED SELECT '1.00' = 1;
== Analyzed Logical Plan ==
(1.00 = 1): boolean
Project [(cast(1.00 as int) = 1) AS (1.00 = 1)#239661]
+- OneRowRelation

Here STRING is cast to INT.

Both coalesce and = rely on the least common type and give different results. So, what is exactly the least common type of STRING and INT? Also, which is more narrower STRING or INT? Any simple data type can be converted to STRING without any data loss, like an INT can be converted to STRING without any loss. However, the reverse is not true. If a STRING containing a decimal value is converted to INT, there would be a value loss. So shouldn't STRING be the wider type?

Re: Understanding least common type in databricks

Dhruv-22 — Fri, 17 May 2024 11:22:23 GMT

Hey, if STRING is the wider type then why did you say that the least common type between STRING and INT is STRING?

Re: Understanding least common type in databricks

Dhruv-22 — Tue, 21 Oct 2025 08:19:51 GMT

The question is solved here - link

topic Re: Understanding least common type in databricks in Data Engineering

Understanding least common type in databricks

Re: Understanding least common type in databricks

Re: Understanding least common type in databricks