Cody Taylor

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

Today I created a simple shell script and I was getting a few odd errors:

cody@taylor:/var/some_folder/server$ ./process_xml.sh
-bash: ./process_xml.sh: /bin/sh^M: bad interpreter: No such file or directory

I figured it was probably a permissions error or an issue with the shebang (#!/bin/sh) line. I tried removing the shebang line, changing it to use dash or bash explicitly, chmoding to 777 and still no luck and another odd error.

cody@taylor:/var/some_folder/server$ sh process_xml.sh
: not found.sh: 4:

I then checked the log file that the commands were supposed to be writing to and it was filled with ‘^M’ on every line break and the log name itself was followed by a ‘?’. Took a minute or two but I finally clued in that I wrote that script on a windows machine and then exported it to an ubuntu linux server via subversion. It was just a basic text format issue.

Under DOS (Windows/PC) the end of a line of text is signalled using the ASCII code sequence CarriageReturn,LineFeed. Alternately written as CR,LF or the bytes 0x0D,0x0A. On the Macintosh platform, only the CR character is used. Under UNIX, the opposite is true and only the LF character is used.

After a quick :

cody@taylor:/var/some_folder/server$ apt-get install tofrodos
cody@taylor:/var/some_folder/server$ dos2unix process_xml.sh

Everything worked fine.

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.