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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
SorryDev
Forum Newbie
Posts: 7
Joined: Fri Sep 08, 2006 3:49 pm

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

Post 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>)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
SorryDev
Forum Newbie
Posts: 7
Joined: Fri Sep 08, 2006 3:49 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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. :)
SorryDev
Forum Newbie
Posts: 7
Joined: Fri Sep 08, 2006 3:49 pm

Post 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.
Post Reply