I do understand the "pro-double" arguments, however I think that exact
representation would give us at least some hope (for the future, I know now
it does not work) to answer certain SQL queries efficiently using index
only without the need to read the record.
select name order by name, age where name like 'A%' and age > 10
can be answered just using index on (name,age) (I deliberately ignore MVCC
and transaction visibility rules for a moment)
Also, I suspect inexact representation of integer/decimal breaks "unique" on
some probably rare cases. If 2 different very big int64 numbers are mapped
to a single double, only one of these numbers can be stored in a unique
index, isn't it so?
> -----Original Message-----
> From: Ann.Harrison@stripped [mailto:Ann.Harrison@stripped] On Behalf Of
> Ann W. Harrison
> Sent: Wednesday, February 18, 2009 8:16 PM
> To: Vladislav Vaintroub
> Cc: Kevin.Lewis@stripped; 'Lars-Erik Bjørk'; 'Jim Starkey'; 'FalconDev'
> Subject: Re: Patch for bug#42208
> Vladislav Vaintroub wrote:
> > And while we're on it why not to fix integer representation;) Doubles
> > strange creation by mathematician, exact longlongs would be really
> > not?
> Not, I think. Yes, we could have a separate index representation
> for numbers declared as integer, but I don't think that buys us
> much. Double precision float is precise to 15 digits, so only
> numbers in the upper range of int64 lose precision when converted.
> The conversion guarantees that if x > y when represented as
> int64, x >= y when represented as double. That's good enough
> for our indexes - we have to look at the value anyway.
> But just being good enough and giving a single representation
> for numbers isn't the best argument for using double precision
> as opposed to int64 keys. Nor is the fact that I happen to
> know how to mangle a double to byte comparable form, and don't
> have an algorithm at hand to do the same for int64. No doubt
> there is one.
> Double precision allows us to make on-line changes to the
> length, scale, and type of numbers without rebuilding indexes.
> Falcon represents decimal/numeric values as binary with a
> decimal scale factor - meaning that the value is multiplied
> by 10^<scale> before being stored and divided by 10^<scale>
> for comparisons with values with different scales.
> If someone decides after storing lots of GPS data in meters
> that the actual precision is centimeters, Falcon would allow
> the field to change from int64 to decimal [20,2] on line
> I think that's good.