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

[Dhruv-22]

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

[MuthuLakshmi · Accepted solution]

@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

[Dhruv-22]

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

1. 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.
2. Also, when ansi mode is disabled, which set of rules apply?

[MuthuLakshmi]

@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

[Dhruv-22]

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.