On Wednesday, 13 December, 2017 13:35, Lifepillar <lifepil...@lifepillar.me> wrote:
>I am implementing an extension for manipulating IEEE754 decimal >numbers. Numbers are stored as blobs using a standard encoding. >Numbers that are mathematically equal may have different >representations, (e.g., 1.0 may have mantissa 10 and exponent -1 >while 1.00 may have mantissa 100 and exponent -2). You have stated something that is impossible, or at least self-contradictory. Unless, of course, you are talking about the "decimal" formats of IEEE754-2008 and not the standard (far more common) "binary" formats. You cannot have an IEEE754 (binary) number stored in denormalized format *except* in the circumstance where the exponent indicates that it is a denormalized number. There are only two valid exponents to indicate that the number is denormalized, and all denormalized numbers are only used to represent numbers between +/- (0 and epsilon). All other numbers stored in IEEE754 floating point format are required, by the standard, to be normalized. (That is, where the MSB is 1 and that 1 is not stored as part of the significand). So in order for the numbers to be IEEE754 floating point, the number "1.0" (no matter the number of trailing 0's you choose to display) must always be stored with a mantissa of 0.5 and an exponent of 1. Although a mantissa of 0.25 with exponent 2 evaluates also to the number 1.0, in IEEE754 format it must always have a mantissa of 0.5 and exponent of 1. Note the above two paragraphs only apply to binary IEE754-2008 numbers. These are the only kind of "floating point" presently understood by SQLite3. However, if you are talking about the "decimal" IEEE754 then you can indeed have different representations of the same "value". Some "values" can have about 800 different representations of the same value. (Note that the solution below would work even if the blobs were arbitrary precision IBM GDAS floating point numbers, or any other kind of floating point number, pretty much). To answer your question however, I would recommend that you consider writing a function that return the "value" in a supported format. My personal recommendation would be IEEE754-2008 binary64 (that is, the standard double precision floating point format supported by SQLite3). You would tag this function as being CONSTANT/DETERMINISTIC. You could then create an index on the result. CREATE INDEX decimal64blob_to_binary64 ON MyTable (ConvertDecimal64toBinary64(binary64blob_field)); and when you search the index ala: SELECT * FROM MyTable where ConvertDecimal64toBinary64(binary64blob_field) between 47.0 and 47.1; you will use the index (I believe). And you need not specify conversion to string format nor deal with the vagaries of strings. You just have to deal with the standard binary floating point limitations. On the other hand however if you do NOT need binary64 at all, then there was a minor change discussed a while back by someone else where you can "change" the default floating-point number format from binary64 to decimal64 and then compile your own custom version of SQLite3 ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
