At 11:07 AM -0600 9/29/05, Dennis Cote wrote:
As you can see, the result of exact (integer) division is also exact (integer) with implementation defined precision and scale. The result of an expression containing approximate (floating point) values is approximate (floating point). So SQLite is conforming to the SQL standard.
You mis-understand what 'exact' means; 'exact' != 'integer', but rather 'integer is conceptually a sub-set of exact. An exact value can be fractional, such as '1.32', and yet not be floating point.
Therefore, if the columns were defined as integers, then it is reasonable for the result to be an integer; however, if the columns were defined as exact fractionals, then the result should be an exact fractional, '2.5'.
When it comes to concept and storage, there are 3 distinct types of numbers, which the SQL standard gives distinct names:
1. INTEGER - and big/little and sign/unsign variations - An exactly remembered whole number that can be stored and manipulated compactly in base-2 binary as is native for computers. Any value that will always be whole is optimally stored this way.
2. DECIMAL(p,s) - An exactly remembered fractional number that is typically stored in a form akin to text, such as one byte per base-10 digit. These can effectively store arbitrarily large numbers of any length and precision without loss of detail, although doing math with them may be slower. For example, if you store '2.5' in one, then '2.5' is actually stored.
3. FLOAT(p) - and double variation - An approximately remembered number that is stored and manipulated compactly in base-2 floating point. Increasing the precision will only better approximate a value, but the exact value is lost, though math with these is fast. For example, if you store '2.5' in one, then either '2.499999999' or '2.500000001' is actually stored.
SQLite should recognize the above 3 numerical types as being distinct, and do the correct actions with math involving any of them.
-- Darren Duncan
