Look at:
SELECT SUM(-1 + CAST(1 AS UNSIGNED INT) * 0) FROM <a table of your choice> LIMIT 1;
What result do you anticipate?
Now replace "<a table of your choice>" and run the query...what result did you get?
Explain why...
Obviously, my actual case was not that stupid...I had a sum following this pattern:
SUM(<a field of type INT> + <a field of type UNSIGNED INT> * <a field of type INT>)
Mystery type conversion in MySQL: Can you figure it out?
Moderator: General Moderators
My version of MySQL returns 73786976294838206460. It's MySQL version 5.0.22. Apparently, results vary from version to version and none are correct.feyd wrote:Generally I would expect that the signed integers would be upcast to unsigned as I recall C would do it. However, I see that at least my local version of MySQL (5.0.18) returns NULL.
Upon consideration, I agree with you. That CAST AS SIGNED must be used to keep every member of the expression SIGNED and to prevent MySQL from concluding the result should be of type UNSIGNED.feyd wrote:Well, that would be correct if the logic is to upcast signed to unsigned. That result indicates that your install uses 64bit integers.SorryDev wrote:My version of MySQL returns 73786976294838206460. It's MySQL version 5.0.22. Apparently, results vary from version to version and none are correct.