I had to write some reports for some legacy software today and I was unpleasantly surprised with the results of my SQL queries. I was selecting dollar values and summing them to for the monthly spending of certain individuals. Easy enough right? I wrote a query something like this :

SELECT SUM(t.money_spent) as sum_of_spent,
c.customer_name
from transactions t
join customers c on t.customer_id=c.customer_id
group by customer_name order by c.customer_name asc

I ended up getting numerical values that were 10 decimal places long with seemingly random numbers. After checking to make sure the database didn’t have any odd entries I stumbled on this bug report.
The ‘money_spent’ column had a data type of float, which is a waste, but I still don’t think that it should sum up incorrectly. When I select individual values I get proper two decimal results.
Apparently floats and doubles use floating point math, which deals with approximate values for numbers and can thus result in confusion like this. It seems that it isn’t really possible to store 0.1 in a column of type float. You can only store 0.00999999977648258. This behavior is a little silly but easily fixed by using the ROUND() function :

SELECT SUM(ROUND(t.money_spent)) as sum_of_money_spent,
c.customer_name from transactions t
join customers c on t.customer_id=c.customer_id
group by customer_name order by c.customer_name asc

If you have root access to a linux server and you don’t have the root mysql password, but need it, then you can easily reset the root mysql password in just a few commands. These commands probably differ depending on what linux distro you use. I was using Ubuntu 9.04 (Jaunty Jackalope) when I wrote this.

Now we restart the mysql server with the ‘skip-grant-tables’ option which basically allows anyone to do whatever they like. It’s usually preferable to include the ‘skip-networking’ option so that only localhost (you) have access to the naked database.

Uniform resource locators (URL) are starting to get very long and I’m getting sick of typing ridiculously long strings into safari on my iphone. I don’t really care about the extra bandwidth, It’s just annoying when you’re on the phone telling someone to go check out a 67 character long url and they mistype it three times.

I’ve just started using the tiny url service and so far it’s been useful. On one of my sites I wanted to generate a tiny url for each of my pages to make them quicker to type in and also so people don’t know what get variables I’ve set until they get there. Here is the PHP function that I used to generate a tiny url for every page on the site.

There doesn’t seem to be any documentation at all on the Tiny URL website about this so I’m not sure if it’ll change in the future.
This function passes your desired url to the api-create.php script on the tinyurl domain which returns a nice short url that isn’t a pain to put into your iphone.

I tried a ‘tail settings.pyc’ in a new django project and I guess pyc means compiled?
Kinda neat what it did to my terminal though. Those lines ending in the dollar symbol are actually me hitting enter, that is my prompt.