[mysql] Mysql kan niet rekenen?

[argi]

Hallo iedereen.

Ik ben bezig met een applicatie in PHP en MySQL. Ik heb de volgende tabel:

id	quantity	price
659	25.0000	57.1000

Daarover heb ik de volgende query gemaakt:

SELECT `transactions`.`id` ,

SUM( `transactions`.`quantity` ) AS `total_quantity`,

SUM(`transactions`.`buy_price`) as sum_price, SUM( `transactions`.`quantity` * `transactions`.`buy_price` ) / SUM(`transactions`.`quantity`) as `avg_rate`

FROM transactions

WHERE ( `transactions`.`id_stocks` = 659 )

GROUP BY `transactions`.`id_stocks`

Hieruit verwacht ik het volgende resultaat:

id	total_quantity	sum_price	avg_rate
659	25.0000	57.1000	57.1000

Ik krijg echter het volgende resultaat:

id	total_quantity	sum_price	avg_rate
659	25.0000	57.1000	57.099998

Hoe kan dit? avg_rate zou dezelfde waarde moeten hebben als de waarde in 'price', aangezien er maar één transactie is. Er is echter een (zeer klein) verschil.

Ik heb de query zowel met behulp van PHP als rechtstreeks op de MySQL server uitgevoerd. Mijn MySQL versie is 3.23.52 op RedHat 8.0

Mijn velden zijn van de volgende types:
id --> int(11)
quantity --> float(7,4)
price --> float(7,4)

Ik heb nog een aantal andere waarden in de tabel staan waar wel de goede 'avg_rate' word berekend...

Weet iemand hoe dit kan? Heb ik een verkeerde query gemaakt? Kan MySQL niet rekenen? Is er een probleem met afrondingen? Is dit een bekende bug(?) in MySQL?

[.oisyn]

Het lijkt me gewoon een afrondingsprobleem

The FLOAT type is used to represent approximate numeric datatypes. The SQL-92 standard allows an optional specification of the precision (but not the range of the exponent) in bits following the keyword FLOAT in parentheses. The MySQL implementation also supports this optional precision specification. When the keyword FLOAT is used for a column type without a precision specification, MySQL uses four bytes to store the values. A variant syntax is also supported, with two numbers given in parentheses following the FLOAT keyword. With this option, the first number continues to represent the storage requirements for the value in bytes, and the second number specifies the number of digits to be stored and displayed following the decimal point (as with DECIMAL and NUMERIC). When MySQL is asked to store a number for such a column with more decimal digits following the decimal point than specified for the column, the value is rounded to eliminate the extra digits when the value is stored.

Ik vind deze tekst een beetje onduidelijk. De SQL-92 standaard zegt volgens dit stukje dat je met het getal tussen de haakjes het aantal bits van de mantissa specificeert (bij een standaard IEEE float is dat 23, wat een 24 bits precisie geeft). Alleen je gebruikt de alternatieve syntax met 2 getallen tussen de haakjes, wat volgens dit stukje tekst het aantal bytes van de float is

Best raar, misschien bedoelen ze wel gewoon bits, in dat geval is je fout logisch, aangezien een getal als 57.1 zich niet laat noteren in slechts 7 bits

De reden dat sum_price wel goed wordt weergegeven is omdat het op dat moment nog een float(7,4) is, waardoor de daadwerkelijke 57.099998 wordt afgerond naar 57.1000. Maar na de berekening is het gewoon een volledige float, en wordt die afronding dus niet toegepast

[Robtimus]

Berekeningen met floats gaan vaker fout, omdat het niet 100% als binair decimaal is op te slaan.

Een simpel voorbeeld in Java:

Java:

for (double i = 0.0; i <= 1.0; i += 0.1)
    System.out.println(i);

Resultaat:

0.0
0.1
0.2
0.30000000000000004
0.4
0.5
0.6
0.7
0.7999999999999999
0.8999999999999999
0.9999999999999999

Ligt dus denk ik niet aan MySQL.

[JHS]

Je zou het ook op kunnen slaan als een integer, en aan het eind door 100 kunnen delen, daarmee haal je iig het probleem eruit. (En dan bij het invoeren met 100 vermenigvuldigen, anders worden de decimalen verwijderd, in is je getal een stuk kleiner )

[.oisyn]

57.1 weergegeven als float is overigens in werkelijkheid 57.09999847412109
(of 14968422 * 2^-18 om het even exact weer te geven )

Je zou het kunnen casten naar double (voor je de bereking doet natuurlijk), dan krijg je wel een exactere benadering
Dan wordt het 57.09999999999855 (15695528486502 * 2^-38)

[ Voor 11% gewijzigd door .oisyn op 21-02-2004 21:22 ]

[argi]

Bedankt voor de reacties.

Als ik het aantal decimalen in het prijs veld naar 14 zet krijg ik inderdaad ook 57.09999847412109 te zien ipv 57.1 en krijg ik met geen mogelijkheid 57.1 in de tabel gezet.

Ik heb nu het veld type omgezet naar DECIMAL(10,4). Hierbij worden wel de 'juiste' getallen weergegeven. Wat ik echter raar vind is dat er in een boek dat ik heb gekocht er juist stond dat je float (7,2) moest gebruiken...

[ Voor 35% gewijzigd door argi op 21-02-2004 21:53 ]

[Henk007]

Er was recentelijk een verwante thread over afrondfouten in PHP.

[ Voor 16% gewijzigd door Henk007 op 21-02-2004 22:02 ]

[Verwijderd]

PHP:

round($avg_rate, 2);

[Korben]

IceManX schreef op 21 februari 2004 @ 20:48:
Berekeningen met floats gaan vaker fout, omdat het niet 100% als binair decimaal is op te slaan.

Een simpel voorbeeld in Java:

Java:

for (double i = 0.0; i <= 1.0; i += 0.1)
    System.out.println(i);

Resultaat:
[...]
Ligt dus denk ik niet aan MySQL.

Tja, Java kan dat misschien idd niet...

Visual Basic .NET:

        Dim I As Double

        For I = 0.0 To 1.0 Step 0.1
            Console.WriteLine(I)
        Next

Output:

code:

0
0,1
0,2
0,3
0,4
0,5
0,6
0,7
0,8
0,9
1

SQL Server maakt ook afrondingsfouten, maar dan de andere kant op, ik krijg 57.100000000000001.