Su Tech Ennui: September 2009

Tuesday, September 1, 2009

PL/SQL integer division

I've been programming PL/SQL for a couple of years now and I only found out about this 'feature' of PL/SQL today.

Without actually running it, what would you expect this program to output?

a := 18; b := 5; c := a / b;
DBMS_Output.put_line('18 / 5 = '||c);

If like me you expected '3', then try running the program. Even though all variables are integers, PL/SQL converts them to floats in order to perform the divide, then converts the result back to an integer by rounding to the nearest int, rather than truncating, down or towards 0.

What an idiotic design for a language. And try finding this out any way other than trial or error; it's almost completely undocumented. I wonder how many PL/SQL programs have off-by-one errors that have gone undetected for years?

PL/SQL is actually a language that lends itself easily to off-by-one errors, for example the 'between' test for two dates is inclusive on both ends of the range. Standard practice in computer science for any sort of range definition is always lower-bound inclusive, upper-bound exclusive; that way a test for date between day1 and day2 can be followed by a test for date between day2 and day3, and it will always do what you want and expect. But in PL/SQL you always have to subtract a day to avoid getting a day's overlap between these two tests.