Today, a generation task that we run monthly failed with the following error:
The conversion of the varchar value '31685772700' overflowed an int column. Maximum integer value exceeded.
Since the highest Integer number you can have is 2147483647 I knew what the error meant. When I looked into the code I found the column of the table where it was getting this value from. This column had a data type of varchar(50). Everything looked fine in the table so then I moved onto the query.
When I broke down the query line by line I finally found the culprit. There was an inner join that had that following clause:
columnwithhugenumber > 0
At this point I had my suspicions that the code was converting the column value into an integer before it compared the two, but why not convert the zero to varchar and compare? Well after some digging I found a very interesting answer. Did you know that some data types have a precedence over others?
So I used my favorite search engine and this is what I came up with. A full chart of Data Type Precedence. If you have two different data types to compare, whichever one is lower on this list will be converted to the data type that is higher on the list.
So with Int being 16 on the list and varchar being 27, that explains why it was erroring.
I tried a couple of different things, first I tried to cast the varchar value as a bigint as well as cast the zero as a bitint, but I received an error in doing this. Then, I tried to cast the zero as a varchar, which after I did it I realized it makes no sense and also errors.
So, finally to work around this I put single quotes around the '0' and it worked.
It makes sense that data types have precedence, but it never occurred to me until now that they did.