Mysql Arithmetic..

section31

hey guys...I need some clarification on something.

Ok...i'm not clear on mysql float datatypes. I mean, my script is working but I would like to know exactly what its doing.

I'm running a particular query...

$sql = "UPDATE table SET votes=votes+1, votecount=votecount+$rating, rating=votecount/votes WHERE linkid=$id";

mysql datatypes are as follows
votes (int)
votecount (int)
rating (float)

the part i'm unclear about is the division i used here "rating=votecount/votes"
I read up a little at mysql.com and it discusses some arithmitic operations, but not division... so i'm dividing 2 integers and I want to know what type of division mysql does (percision wise).

This is what i'm getting pushed into the database..Ex. (29/6) = 4.83333

You would think since its a repeating decimal, it would push the maximum length of numbers, but that isn't the case.

Thanks,
Dave

Sxooter

The problem is that you're assuming that by the time the rating is figured out that the row is already updated. On many databases, the rating may well get set to the old row's votecount/votes when updating. You can either update the other two values first, then update the rating, or do it the easy way and just put the math in your select statement.

If MySQL supported triggers, an after trigger would be perfect here. but it doesn't, so no joy there. But doing it on the select is probably just fine.

section31

sorry..but i don't think u understood me.

tekky

some mysql.com info

I'm sure it doesnt max out the column size to save space and avoid excessive process time due to possible LARGE column sizes or long decimal values...

Sxooter

I understand you all too well. here's the problem. When you run an update, you have the old values, and the new values. The two are like different rows to the databasel engine.

So, we can pretend that your query really looks like this:

UPDATE table SET new.votes=old.votes+1, new.votecount=old.votecount+$rating, new.rating=old.votecount/old.votes WHERE old.linkid=$id

Do you see it yet? The third element in your update will be setting the new rating to the old count divided by the old votes. I.e. the things on the right of the = sigh are the old values. What you need is to either fire two updatesin sequence, one to update the votecount and votes first, and the next to update the rating, or you need to get the rating when you do the select by creating it then.

Oh, and MySQL is notorious for getting the math wrong, so don't be surprised by the behaviour you're seeing with the 4.838888 type numbers. If you want your math done correctly you'd be advised to look at firebirdSQL or PostgreSQL.

section31

omg guys...my question had nothing to do with updating, that was just a hypothetical scenario. I appreciate the replies and all, but let me try one more time.

My question was dealing with how mysql handles division and floats...thats all.

I wanted some clarification on what kind of percision would dividing 2 integers via a query..I read on mysql.com that * are 64bit operations and are always handled as big ints..but what about dividing...

Read above post to see the result i'm getting dividing 2 integers.

Sxooter

OK the, I'll repeat the last paragraph of my previous post:

Oh, and MySQL is notorious for getting the math wrong, so don't be surprised by the behaviour you're seeing with the 4.838888 type numbers. If you want your math done correctly you'd be advised to look at firebirdSQL or PostgreSQL.

I.e. they're not real good at following the SQL spec, and it's not uncommon for MySQL to use the wrong precision.

section31

Originally posted by Sxooter
OK the, I'll repeat the last paragraph of my previous post:

I.e. they're not real good at following the SQL spec, and it's not uncommon for MySQL to use the wrong precision.

I see...Thanks for the info... I really haven't tried out PostgreSQL, is it much different than mysql. I just chose mysql b/c of the popularity, saw books for it everywhere.

Lars_Berg

Actually the old/new analogy does not hold for Mysql.

quote from http://www.mysql.com/doc/en/UPDATE.html

UPDATE assignments are evaluated from left to right. For example, the following statement doubles the age column, then increments it:

mysql> UPDATE persondata SET age=age*2, age=age+1;

According to the standard, an Update statement should work the way Sxooter describes.

I don't see why you should need two update statements though (with standard behaviour) as you could do

UPDATE table SET votes=votes+1, votecount=votecount+$rating, rating=(votecount+$rating)/(votes+1) WHERE linkid=$id

I agree about the bad arithmetics of Mysql. 29/6 should give the result 4 Since the operands are both integer, the result should be integer.

Sxooter

yeah, the weird thing is that a proper answer should be an int, and if you cast to a float, you should get more precision. It's like MySQL is returning a half-float / half-int response.

As for PostgreSQL, yes it is quite a bit different from MySQL. For typical users with more windows experience, and little database experience, I'd recommend FirebirdSQL first, as it runs natively on Windows, has a smaller footprint, and requires no regular maintenance like Postgresql does (i.e. vacuuming.)

While the autovacuum daemon works well in postgresql, it is still a higher amount of initial footwork for the beginner.

However, it generally is considered the best "heavy lifting vehicle" of the Open Source realm, which is why the OSDL tests the linux kernel with it.

Both FirebirdSQL and PostgreSQL strive for SQL-92/99/2003 compliance, within reason, and for correct operation above all. This may lead to some queries that work in MySQL causing errors in either of the other databases. An example is:

select a,b,c from table group by a;

which will run on MySQL but generates an error on Postgresql or Firebirdsql, since there may be multiple b or c entries for each corresponding a entry, and the database isn't sure which one you want, so the results are theoretically capable of changing from one run to the next, which is considered a bad thing for data consistency.

Sxooter

Originally posted by Lars Berg

UPDATE table SET votes=votes+1, votecount=votecount+$rating, rating=(votecount+$rating)/(votes+1) WHERE linkid=$id

[/B]

Note that that query would give you the proper answer on Postgresql or FirebirdSQL, but would give you the wrong answer on MySQL, since votecount would have just gotten updated when the rating was fired, so the third term would be functionally equivalent to:

rating = (votecount+(votecount+$rating))/(votes+1+1)

wouldn't it?