double(3,2) behaving oddly on WinXP?

Hi,
I develop on WinXP Mysql 4.1.xx and upload to 4.0.25 on *nix server,

Th *nix server works just fine

on winXP trying to store 45.00 into double(3,2) gave the result 9.99.

I got round this by upping to double(5,2)


This was tested directly from a mysql gui as well (ie no scripting involved)

Wierd

--
zzapper
Success for Techies and Vim,Zsh tips
http://SuccessTheory.com/


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: double(3,2) behaving oddly on WinXP?

--=_alternative 00042F9D852570A1_=
Content-Type: text/plain; charset="US-ASCII"

news wrote on 10/20/2005 05:28:24 PM:

> Hi,
> I develop on WinXP Mysql 4.1.xx and upload to 4.0.25 on *nix server,
>
> Th *nix server works just fine
>
> on winXP trying to store 45.00 into double(3,2) gave the result 9.99.
>
> I got round this by upping to double(5,2)
>
>
> This was tested directly from a mysql gui as well (ie no scripting
involved)
>
> Wierd
>
> --
> zzapper
> Success for Techies and Vim,Zsh tips
> http://SuccessTheory.com/
>
>

That is correct behavior. The definition double(3,2) means you can only
have 3 digits, two of which are behind the decimal. The numbers must meet
the pattern #.##. The definition double(5,2) has 5 digits two of which
are behind the decimal point. That means that all numbers must fit the
pattern ###.##. If you try to insert a number into a field that exceeds
the limits of the field, MySQL substitutes with the largest value that
fits the field or raises an error (depending on which SQL_MODE you are
using).

Trying to add the number 1000 to a field defined as (5,3) would result in
999.99 because 1000 would need four digits in front of the decimal.

Make sense?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

--=_alternative 00042F9D852570A1_=--

Re: double(3,2) behaving oddly on WinXP?

On Thu, 20 Oct 2005 20:45:51 -0400, wrote:

Shawn,
I am bewildered, because this is on a website that I designed and has been working for a year
selling £000's of product every day! In there is no product worth less that £10.00 ??!!??

But I only occasionally noticed the problem!?!

> from MySQL doc
MySQL allows a non-standard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, “(M,D)”
means than values are displayed with up to M digits in total, of which D digits may be after the
decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed.
MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column,
the approximate result is 999.0001.

MySQL treats DOUBLE as a synonym for DOUBLE PRECISION (a non-standard extension). MySQL also treats
REAL as a synonym for DOUBLE PRECISION (a non-standard variation), unless the server SQL mode
includes the REAL_AS_FLOAT option.

For maximum portability, code requiring storage of approximate numeric data values should use FLOAT
or DOUBLE PRECISION with no specification of precision or number of digits.

--
zzapper
Success for Techies and Vim,Zsh tips
http://SuccessTheory.com/


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org