Skip to content

Propose a way to store BigDecimal in SQL tables #17

Closed

Description

@Degot

Frequently it is required to store BigDecimal in database with ability to use it in matching and ORDER BY clauses. I've implemented next approach in my project:

I split BigDecimal into Whole, Fractional parts with total size 32 bytes:

  • 2 bytes: 1 bit - (1 if sign > 0) and 15 bits - biased exponent
  • 14 bytes: hi -> lo, aligned to right (not() if sign < 0)
  • 1 byte: qty of fraction leading decimal zeroes (not() if sign > 0)..ie.. 11.000123 -> 3 leading zeroes;
  • 13 bytes: hi -> lo, aligned to left (not() if sign < 0)
  • 1 byte: qty of used bytes in whole part
  • 1 byte: qty of used bytes in fraction part

example:

11,123 -> 1100000000000000 ...00000001011 11111111 01111011000... 00000001 00000001
11,125 -> 1100000000000000 ...00000001011 11111111 01111101000... 00000001 00000001
12,123 -> 1100000000000000 ...00000001100 11111111 01111011000... 00000001 00000001
12,125 -> 1100000000000000 ...00000001100 11111111 01111101000... 00000001 00000001
-11,123 -> 0100000000000000 ...11111110100 00000000 10000100111... 00000001 00000001
-11,125 -> 0100000000000000 ...11111110100 00000000 10000010111... 00000001 00000001
-12,123 -> 0100000000000000 ...11111110011 00000000 10000100111... 00000001 00000001
-12,125 -> 0100000000000000 ...11111110011 00000000 10000010111... 00000001 00000001

This approach allows to store BigDecimal with 34 digits for whole part and 32 digits for fractional.

int conversionBytesForSqlBlob = 32;
int conversionBytesForMainPart = conversionBytesForSqlBlob / 2;
int conversionBytesForExponent = 2;
int conversionBytesForInteger = conversionBytesForMainPart - conversionBytesForExponent;
int conversionBytesForFractionPart = conversionBytesForSqlBlob - conversionBytesForMainPart - 2;
int conversionBytesForLeadingZeroes = 1;
int conversionBytesForFraction = conversionBytesForFractionPart - conversionBytesForLeadingZeroes;
int conversionBitsForExponent = conversionBytesForExponent * 8 - 1;
int conversionBiasedMaxValue = 32768; //(int)Math.Pow(2, conversionBitsForExponent);
int conversionBiasedMidPoint = conversionBiasedMaxValue / 2;
int conversionMaxExponent = conversionBiasedMaxValue - conversionBiasedMidPoint;
int conversionMinExponent = -conversionBiasedMidPoint;
int conversionMaxDigitsInteger = 34;// (int)(conversionBytesForInteger * 8 * Math.Log10(2)) + 1;
int conversionMaxDigitsFraction = 32;// (int)(conversionBytesForFraction * 8 * Math.Log10(2)) + 1;

Activity

  1. Protiguous commented on Dec 20, 2023

    @Protiguous
    Contributor

    Being able to ORDER BY on the server would have many advantages.

  2. Degot commented on Dec 29, 2023

    @Degot
    Author

    Being able to ORDER BY on the server would have many advantages.

    With suggested approach , binary-based ORDER BY works perfectly.

  3. AdamWhiteHat commented on Jan 2, 2024

    @AdamWhiteHat
    Owner

    Hi!

    Hmm, turning a floating-point number into a sortable representation sure is an interesting problem. Not so easy!

    I'm presuming the intent here is to be able to transform the BigInteger into a representation such that the result of performing the ORDER BY command on it be the same as if ordered by the usual less than (<) or greater than (>) relation.

    I tried my hand at this, and I came up with an approach, which I will detail in a separate comment following this one, but it has some caveats and tradeoffs, so it's likely your approach is still superior.

    I wanted to test your approach, but I had a few questions/clarifications about implementing it. My questions below are highlighted in bold.

    First things first, in your numbers above, for example

    11,123

    The comma (,) character here indicates the decimal place (NumberFormatInfo.NumberDecimalSeparator in your current culture), right?

    I didn't catch on to that at first, but after trying to follow your example, I conclude this is probably the case. I just wanted to confirm that ambiguity. Can you confirm?

    Moving on...

    • 2 bytes: 1 bit - (1 if sign > 0) and 15 bits - biased exponent

    1). When you say sign here, you me sign of the number, right? Not sign of the exponent? (Because the exponent can be negative, indicating a shift of the decimal point to the left, while the number is still a positive value, e.g. 0.00001).

    2). I'm not sure what you mean by biased exponent here. I mean, I know that a exponent bias is a value usually added to the exponent to make it non-negative. But part of my confusion is that the exponent is a 32 bit signed integer (4 bytes), not 2 bytes that you are calling for here. What are we adding (or subtracting or multiplying) to the exponent here, and what are we doing with the other 2 bytes? I guess we are retaining only the 2 least significant bytes?

    • 14 bytes: hi -> lo, aligned to right (not() if sign < 0)

    - 14 bytes (112 bits) holds ≈ 34 digits, so this must be the "34 digits for whole part" that you mention just below the example?

    • 13 bytes: hi -> lo, aligned to left (not() if sign < 0)

    - 13 byes (104 bits) holds ≈ 32 digits, so this must be the "32 digits for fractional" part that you mention just below the example?

    Finally,
    What is the ask here?

    Are we wanting a helper method that puts a BigDecimal instance into this format for easy insertion in an SQL table?
    Or maybe just a mention of this in the documentation, saying "Hey if you're looking to shove a BigDecimal into SQL, we recommend using this format"?

    I recently seen on another project that it's possible to have a community discussion board for a project. That would probably a good place for information and discussions like this to live.
    After I finish my other comment, I'm going to post about my attempt at this problem, I'll try creating a community discussion board, and if it works, I'll probably replicate this information over there.

  4. Repository owner locked and limited conversation to collaborators on Jan 3, 2024
  5. converted this issue into a discussion #19 on Jan 3, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions