BOOLEAN to NUMBER cast: Result type follows the target NUMBER type (Pending)¶

When you cast a BOOLEAN value to a NUMBER type, Snowflake now resolves the result type the same way it does for other source types, such as VARCHAR and NUMBER. If you specify an explicit precision and scale, the result is typed as NUMBER(p, s). If you cast to NUMBER without a precision and scale, the result is typed as the default NUMBER(38, 0). This applies whether you use the CAST function, the :: operator, or TO_NUMBER.

Before the change:

Casting a BOOLEAN to NUMBER was always typed as NUMBER(2,0), whether or not you specified a precision and scale:

Copy code

SELECT SYSTEM$TYPEOF(TRUE::NUMBER(24,10));
SELECT SYSTEM$TYPEOF(TRUE::NUMBER);

NUMBER(2,0)[SB1]
NUMBER(2,0)[SB1]

The stored value was correct (0 or 1), but the column type did not reflect the requested (or default) precision and scale.

After the change:

When the 2026_07 behavior change bundle is enabled in your account, the result type follows the target NUMBER type. An explicit precision and scale produce NUMBER(p, s), and a bare NUMBER produces the default NUMBER(38, 0):

Copy code

SELECT SYSTEM$TYPEOF(TRUE::NUMBER(24,10));
SELECT SYSTEM$TYPEOF(TRUE::NUMBER);

NUMBER(24,10)[SB16]
NUMBER(38,0)[SB16]

Customer impact¶

The stored value of a BOOLEAN to NUMBER cast is always 0 or 1, and that value does not change. This behavior change affects only the result type of the expression. The impact is most visible where the column type metadata is consumed, such as:

• Column types created by CREATE TABLE … AS SELECT (CTAS) when a projected column casts a BOOLEAN to NUMBER.
• The output of DESCRIBE TABLE and similar metadata commands.
• Schema validation in downstream tools or pipelines that expect the previous NUMBER(2,0) type.

Because both NUMBER(p, s) with an explicit precision and scale and the default NUMBER(38, 0) can always represent the values 0 and 1, Snowflake doesn’t expect this change to cause new query failures or different result values for the affected casts.

New overflow error possibility¶

Because the requested precision and scale are now applied, casting TRUE to a NUMBER type that can’t represent the value 1 now returns an overflow error. For example, NUMBER(1,1) can’t represent 1:

SELECT CAST(TRUE AS NUMBER(1,1));

100046 (22003): Number out of representable range: type NUMBER(1,1){nullable}, value 1

This error is correct and expected. Previously, this statement returned 1 typed as NUMBER(2,0).

How to update your code¶

Before the bundle is enabled, review any code that casts a BOOLEAN to NUMBER, especially CTAS statements and pipelines that depend on the resulting column type. Expect a bare NUMBER target to resolve to NUMBER(38, 0) and an explicit type to resolve to NUMBER(p, s). If a target type can’t represent the value 1 (for example, NUMBER(p, p)), update the precision and scale to a type that can.

Ref: 2383