What's causing this error in SEDE?

Question

In the Stack Exchange Data Explorer, how come:

• select sum(len(body)) from posts works fine, but,
• select sum(len(text)) from comments throws an error?

Arithmetic overflow error converting expression to data type int.

I experimented and discovered if I CAST..as BigInt it will work (on either of the above queries), and that comparable field in the same table select sum(len(userdisplayname)) works fine with out conversion.

Posts.body                nvarchar(max)    43,016,689,192 characters   Works
Comments.Text             nvarchar(600)     9,711,438,180 characters   Errors
Comments.UserDisplayName  nvarchar(30)          7,616,503 characters   Works

                                T-SQL Int: ±4,294,967,295
                 T-SQL BigInt: ±9,223,372,036,854,775,807 

Post.Body is much larger, and Comments.UserDisplayName is declared similarly as Comments.Text, so I don't understand the difference.

Is this a SQL-thing or an SEDE-thing?

(example query)

Answer 1

It's a T-SQL thingy, see the documentation about len:

Return Types

bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int.

So the Posts.Body query knows it needs to sum len(varchar(max)), a bigint and doesn't overflow (43 billion is not enough for that), while the Comments.Text query tries to sum len(varchar(600)) which is an int variable and does overflow (9.7 billion is larger than 2.1 billion, the maximum for an int). The sum on Comments.UserDisplayName isn't simply large enough to overflow an int variable.