topic Re: Least Common Type is different in Serverless and All Purpose Cluster. in Data Engineering

Least Common Type is different in Serverless and All Purpose Cluster.

Dhruv-22 — Tue, 21 Oct 2025 05:41:56 GMT

The following statement gives different outputs in different computes.

In Databricks, 15.4 LTS

%sql
SELECT typeof(coalesce(5, '6'));

-- Output
string

In Serverless, environment version 4

%sql
SELECT typeof(coalesce(5, '6'));

-- Output
bigint

There are other cases as well.

%sql
SELECT '1.00' = 1;

-- Databricks, 15.4 LTS
true

-- Serverless, version 4
[CAST_INVALID_INPUT] The value '1.00' of the type "STRING" cannot be cast to "BIGINT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. SQLSTATE: 22018 == SQL (line 1, position 😎 == SELECT '1.00' = 1

Re: Least Common Type is different in Serverless and All Purpose Cluster.

MuthuLakshmi — Tue, 21 Oct 2025 06:31:37 GMT

@Dhruv-22
The difference is because of ANSI mode is enabled by default in Serverless where-as in classic compute it's disabled.

Enabling ANSI mode is a recommended for data correctness.
More details: https://kb.databricks.com/en_US/dbsql/databricks-sql-ansi-enablement-guide

Re: Least Common Type is different in Serverless and All Purpose Cluster.

Dhruv-22 — Tue, 21 Oct 2025 07:22:48 GMT

Hey @MuthuLakshmi , thanks for the reply. I have two doubts

Does this also mean that the SQL Type Rules here, are based on ansi mode being enabled? Because, in one of the examples on the rules page, it is written that the least common type of string and int is bigint, which is applicable only when ansi mode is enabled.
Also, when ansi mode is disabled, which set of rules apply?

Re: Least Common Type is different in Serverless and All Purpose Cluster.

MuthuLakshmi — Tue, 21 Oct 2025 07:55:32 GMT

@Dhruv-22
Regarding your 1st question, I'm not sure
You can refer to https://docs.databricks.com/aws/en/sql/language-manual/parameters/ansi_mode#system-default to understand what happens when ansi mode is disabled

Re: Least Common Type is different in Serverless and All Purpose Cluster.

Dhruv-22 — Tue, 21 Oct 2025 08:17:43 GMT

I think for my first question, my intuition is correct. As, in the link you shared, it completely says that type promotion, downcasting and crosscasting are used when ansi is enabled. And the examples for when ansi is disabled completely explain the behaviour in my question regarding 15.4 LTS.