Help with MySQL 5 Math problem

Hi,

I have a query that returned correct results in MySQL 4, but incorrect
results in MySQL 5.

Here is the query:

select (((stYY+rtYY)*(stNN+rtNN))-((stYN+rtYN)*(stNY+rtNY)))/(((stYY
+rtYY)*(stNN+rtNN))+((stYN+rtYN)*(stNY+rtNY))) from ...

With the following values (all of which are int(10) unsigned):

+------+------+------+------+------+------+------+------+
| stYY | rtYY | stNN | rtNN | stYN | rtYN | stNY | rtNY |
+------+------+------+------+------+------+------+------+
| 0 | 0 | 16 | 13 | 13 | 0 | 0 | 16 |
+------+------+------+------+------+------+------+------+

The correct answer is -1.0, which MySQL 4 provides. MySQL 5 returns
88686269585142074.0769. When I replace the numerator in the original
query with the evaluated expression (0-(16*13)), MySQL 5 returns -1.

However, for some sets of values, Both MySQL 4 and MySQL 5 return the
same (correct) value. For example, for the set which evaluates to
0.9632:

+------+------+------+------+------+------+------+------+
| stYY | rtYY | stNN | rtNN | stYN | rtYN | stNY | rtNY |
+------+------+------+------+------+------+------+------+
| 0 | 4 | 24 | 16 | 0 | 1 | 0 | 3 |
+------+------+------+------+------+------+------+------+

Specifics are:

MySQL 4 version (running on Red Hat Enterprise Linux 3):
mysql Ver 14.7 Distrib 4.1.15, for pc-linux-gnu (i686) using readline
4.3

MySQL 5 version (running on Fedora Core 6):
mysql Ver 14.12 Distrib 5.0.27, for redhat-linux-gnu (i686) using
readline 5.0

Thanks for any help you can provide!

Regards,
Eric

All,

For future seekers, the solution to my problem is in the way MySQL 5
handles subtraction where one of the types is unsigned. If the result
is negative, it will be cast to an unsigned. From the manual:

When you use subtraction between integer values where one is of type
UNSIGNED, the result is unsigned unless the NO_UNSIGNED_SUBTRACTION
SQL mode is enabled. See Section 12.9, "Cast Functions and Operators".

Regards,
Eric


On Jun 3, 5:36 pm, Ace Gopher <e...@intellovations.com> wrote:
> Hi,
>
> I have a query that returned correct results in MySQL 4, but incorrect
> results in MySQL 5.
>
> Here is the query:
>
> select (((stYY+rtYY)*(stNN+rtNN))-((stYN+rtYN)*(stNY+rtNY)))/(((stYY
> +rtYY)*(stNN+rtNN))+((stYN+rtYN)*(stNY+rtNY))) from ...
>
> With the following values (all of which are int(10) unsigned):
>
> +------+------+------+------+------+------+------+------+
> | stYY | rtYY | stNN | rtNN | stYN | rtYN | stNY | rtNY |
> +------+------+------+------+------+------+------+------+
> | 0 | 0 | 16 | 13 | 13 | 0 | 0 | 16 |
> +------+------+------+------+------+------+------+------+
>
> The correct answer is -1.0, which MySQL 4 provides. MySQL 5 returns
> 88686269585142074.0769. When I replace the numerator in the original
> query with the evaluated expression (0-(16*13)), MySQL 5 returns -1.
>
> However, for some sets of values, Both MySQL 4 and MySQL 5 return the
> same (correct) value. For example, for the set which evaluates to
> 0.9632:
>
> +------+------+------+------+------+------+------+------+
> | stYY | rtYY | stNN | rtNN | stYN | rtYN | stNY | rtNY |
> +------+------+------+------+------+------+------+------+
> | 0 | 4 | 24 | 16 | 0 | 1 | 0 | 3 |
> +------+------+------+------+------+------+------+------+
>
> Specifics are:
>
> MySQL 4 version (running on Red Hat Enterprise Linux 3):
> mysql Ver 14.7 Distrib 4.1.15, for pc-linux-gnu (i686) using readline
> 4.3
>
> MySQL 5 version (running on Fedora Core 6):
> mysql Ver 14.12 Distrib 5.0.27, for redhat-linux-gnu (i686) using
> readline 5.0
>
> Thanks for any help you can provide!
>
> Regards,
> Eric