On 9/29/05, Darren Duncan <[EMAIL PROTECTED]> wrote: > > 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.
Actually I do understand that, but no offence is taken. I was simply restricting the discussion to the two numeric types thet SQLite implements, integer and real (or 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. This can also be done with fixed point fractions. This type of scaled integer with a fractional component are often used by integer DSPs. They store a value in an integer with a fixed binary point (rather than a floating binary point as implied by the name floating point). For example, a 32 bit integer can be viewed as a signed 16 bit integer bit and 16 fraction bits. It has a range of signed values from 32767.999984741 to -32768 with a resolution of 0.000015259. These values can also be stored as arbitrary precision integers, which store multiple integers words to represent the value which can again be interpreted as a fixed point fraction, or as BCD (binary coded decimal) values which store 2 digits per byte. 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. Actually, 2.5 can be represented exactly in binary floating point, so your example is incorrect, but other values cannot, so the principal is valid. SQLite should recognize the above 3 numerical types as being > distinct, and do the correct actions with math involving any of them. > > -- Darren Duncan >
