Page 1 of 1

Mystery type conversion in MySQL: Can you figure it out?

Posted: Sat Sep 09, 2006 8:34 pm
by SorryDev
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>)

Posted: Sat Sep 09, 2006 8:41 pm
by feyd
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.

Posted: Sat Sep 09, 2006 8:48 pm
by SorryDev
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.
My version of MySQL returns 73786976294838206460. It's MySQL version 5.0.22. Apparently, results vary from version to version and none are correct. 8O

Posted: Sat Sep 09, 2006 9:09 pm
by feyd
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. 8O
Well, that would be correct if the logic is to upcast signed to unsigned. That result indicates that your install uses 64bit integers. :)

Posted: Sat Sep 09, 2006 9:48 pm
by SorryDev
feyd wrote:
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. 8O
Well, that would be correct if the logic is to upcast signed to unsigned. That result indicates that your install uses 64bit integers. :)
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.