I have cast a number to varchar as below,

SELECT (266::VARCHAR)/2

This query is returning result as 133.000000..

I was expecting an error. But got above result. Why?

CodePudding user response：

In my Snowflake account, I am not getting the decimal places via the webui

But auto-casting to trying "make things just work" is the reason.

SELECT 266 as an_int
    ,an_int::text as as_text
    ,SYSTEM$TYPEOF(an_int) as type_an_int
    ,SYSTEM$TYPEOF(as_text) as type_an_int
    ,an_int/2 as int_div_2
    ,as_text/2 as text_div_2
    ,(266::VARCHAR)/2 as magic;

But via SnowSQL:

>SELECT 266 as an_int
    ,an_int::text as as_text
    ,SYSTEM$TYPEOF(an_int) as type_an_int
    ,SYSTEM$TYPEOF(as_text) as type_an_int
    ,an_int/2 as int_div_2
    ,as_text/2 as text_div_2
    ,(266::VARCHAR)/2 as magic;
 -------- --------- ------------------ ------------------------ ------------ ----------------- ----------------- 
| AN_INT | AS_TEXT | TYPE_AN_INT      | TYPE_AN_INT            |  INT_DIV_2 |      TEXT_DIV_2 |           MAGIC |
|-------- --------- ------------------ ------------------------ ------------ ----------------- -----------------|
|    266 | 266     | NUMBER(3,0)[SB2] | VARCHAR(16777216)[LOB] | 133.000000 | 133.00000000000 | 133.00000000000 |
 -------- --------- ------------------ ------------------------ ------------ ----------------- ----------------- 
1 Row(s) produced. Time Elapsed: 0.281s

CodePudding user response：

I was expecting an error. But got above result. Why?

When data types varies the mechanism called implicit casting/type coercion takes places.