7

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)

9

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.

  • I was about to say you must have my issue backwards, until I read your answer a couple more times. So presumably, the len function needs to store it's result in a temp variable while it calculates - for which it needs to "assume" a datatype - which it's guessing correctly for the bigint, but it's not expecting an nchar(600) to exceed int's signed max of 4.3billion, so I need to be explicit with that one... (or that's how I will remember it even if it's not quite right!) Good answer, short and sweet, thanks! – ashleedawg Jul 12 '18 at 21:53
  • I guess this also explains something semi-related was wondering... correct me if I'm wrong, but T-SQL integers are signed (always) while MySQL integers are unsigned by default unless declared as SIGNED ... so, in SEDE anything larger than ~+2.1 Billion needs to be an bigint. Right? – ashleedawg Jul 12 '18 at 22:00
  • Yes, they are always signed: docs.microsoft.com/en-us/sql/t-sql/data-types/… (except for tinyint, which ranges from 0 to 255). See also stackoverflow.com/q/4451967/4751173 – Glorfindel Jul 13 '18 at 5:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .