Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Better handling of unconstrained numerics #875

Open
olsen232 opened this issue Jun 20, 2023 · 1 comment
Open

Better handling of unconstrained numerics #875

olsen232 opened this issue Jun 20, 2023 · 1 comment
Labels
enhancement New feature or request

Comments

@olsen232
Copy link
Collaborator

olsen232 commented Jun 20, 2023

Right now, numerics are imported / committed as numerics along with any extra precision / scale information from the DB, and without that information if it is not present. Here is the kart schema for two numerics, the first is "unconstrained":

   {
     "id": "af675322-60aa-0d4c-a8ac-01fc31010876",
     "name": "unconstrained",
     "dataType": "numeric"
   },
   {
     "id": "04250903-55c7-b426-4eb4-1183f5f3f022",
     "name": "constrained",
     "dataType": "numeric",
     "precision": 10,
     "scale": 5
   }

And within Kart, we don't truncate numeric values at all - we just commit whatever we get from the DB as text in base10.
The issue is that unconstrained numerics behave differently in different DBs - eg NUMERIC in PG has arbitrary scale and precision, whereas in SqlServer it has a scale of zero, ie, no decimal places.

A fix with a good degree of backwards compatibility would be to import unconstrained numerics with the scale and precision they actually have from the DB they are in. Omitting either attribute would indicate that this attribute has been given the maximum value possible. So, here is what would happen if you imported an unconstrained NUMERIC from PG and then checked it out as SqlServer:
PG unconstrained NUMERIC
Kart:

   {
     "id": "af675322-60aa-0d4c-a8ac-01fc31010876",
     "name": "unconstrained",
     "dataType": "numeric"
   }

SqlServer: unconstrained NUMERIC(38, 19)
ie, NUMERIC(max, max) - that's as big a numeric as SqlServer supports

And if you import an unconstrained NUMERIC from SqlServer and check it out as PG:
SqlServer: unconstrained NUMERIC
Kart:

   {
     "id": "04250903-55c7-b426-4eb4-1183f5f3f022",
     "name": "constrained",
     "dataType": "numeric",
     "scale": 0
   }

PG: unconstrained NUMERIC(1000)
ie, NUMERIC(max, 0)

This change would not affect PG users. It would affect SqlServer users, or users who have been using both PG and SqlServer.

@olsen232 olsen232 added the enhancement New feature or request label Jun 20, 2023
@craigds
Copy link
Member

craigds commented Jun 21, 2023

postgres unconstrained numeric supports infinities too

We should ensure infinities and other uncheckoutable values raise errors at checkout time, or reject them at import/commit time

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

No branches or pull requests

2 participants