You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@arrow.apache.org by "lidavidm (via GitHub)" <gi...@apache.org> on 2023/06/12 19:53:08 UTC

[GitHub] [arrow-adbc] lidavidm opened a new issue, #767: c/driver/postgresql: type of `SELECT SUM(bigints) FROM ...` is binary, not int64 as expected

lidavidm opened a new issue, #767:
URL: https://github.com/apache/arrow-adbc/issues/767

   (no comment)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@arrow.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [arrow-adbc] lidavidm commented on issue #767: c/driver/postgresql: support NUMERIC type

Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #767:
URL: https://github.com/apache/arrow-adbc/issues/767#issuecomment-1590239521

   Some research is needed here:
   - What does JDBC do with a NaN/Infinity?
   - What does Dremio do?
   - What does Turbodbc do?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [arrow-adbc] paleolimbot closed issue #767: c/driver/postgresql: support NUMERIC type

Posted by "paleolimbot (via GitHub)" <gi...@apache.org>.
paleolimbot closed issue #767: c/driver/postgresql: support NUMERIC type
URL: https://github.com/apache/arrow-adbc/issues/767


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [arrow-adbc] lidavidm commented on issue #767: c/driver/postgresql: support NUMERIC type

Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #767:
URL: https://github.com/apache/arrow-adbc/issues/767#issuecomment-1591517280

   A quick test with Turbodbc shows that a column with a NaN will give you the string representation of the numbers, otherwise it switches between integers and floats.
   
   Seems this is the ODBC driver's doing.
   
   > Numeric As: Specify the map from numeric items without precision to SQL data types. numeric(default), varchar, double or memo(SQL_LONGVARCHAR) can be specified.
   
   https://odbc.postgresql.org/docs/config.html


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [arrow-adbc] lidavidm commented on issue #767: c/driver/postgresql: support NUMERIC type

Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #767:
URL: https://github.com/apache/arrow-adbc/issues/767#issuecomment-1624153624

   Nice finds.
   
   I think that's reasonable: string/double for now (or even just string to start with), and we could add conversion to Decimal128/256 eventually if needed. (We could provide options there, like a fixed precision/scale, inferring it if provided in the Postgres schema, or inferring it by reading a fixed amount of data, much like SQLite.) 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [arrow-adbc] lidavidm commented on issue #767: c/driver/postgresql: type of `SELECT SUM(bigints) FROM ...` is binary, not int64 as expected

Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #767:
URL: https://github.com/apache/arrow-adbc/issues/767#issuecomment-1588139953

   Ah, it's because it ends up as `numeric`.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [arrow-adbc] lidavidm commented on issue #767: c/driver/postgresql: support NUMERIC type

Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #767:
URL: https://github.com/apache/arrow-adbc/issues/767#issuecomment-1591518879

   The easiest for us might be to allow these options:
   
   - String representation (slow, but exact)
   - Double (inexact)
   - Decimal256 (error if NaN/inf/out-of-range encountered)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [arrow-adbc] paleolimbot commented on issue #767: c/driver/postgresql: support NUMERIC type

Posted by "paleolimbot (via GitHub)" <gi...@apache.org>.
paleolimbot commented on issue #767:
URL: https://github.com/apache/arrow-adbc/issues/767#issuecomment-1622785377

   Summarizing what I found while investigating this in #871:
   
   - An additional complication with Decimal256/128 is that the precision and scale aren't necessarily constant, so if you allowed specifying that option it would also require specifying the precision + scale.
   - The binary format is implemented here: https://github.com/postgres/postgres/blob/4f4d73466d71976b58f29121bab9d9fef6f3436e/src/backend/utils/adt/numeric.c#L1068-L1146 .
   - The "convert to string" logic seems sufficiently compact that we could implement it in the driver without too much work: https://github.com/postgres/postgres/blob/4f4d73466d71976b58f29121bab9d9fef6f3436e/src/backend/utils/adt/numeric.c#L7416-L7552
   - In Postgres, "numeric cast to double" is just `strtod()` on the string value. We might be able to improve on that by using fastfloat (+ avoid locale issues with strtod).
   
   I can probably take a stab at the first two options (perhaps selected as an option on the statement like `"adbc.postgres.decimal"` of `"string"`, or `"double"`, defaulting to string).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [arrow-adbc] lidavidm commented on issue #767: c/driver/postgresql: support NUMERIC type

Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #767:
URL: https://github.com/apache/arrow-adbc/issues/767#issuecomment-1589292383

   References:
   - https://www.postgresql.org/docs/current/datatype-numeric.html
   - The binary format is described at https://www.postgresql.org/message-id/16572.1091489720@sss.pgh.pa.us but does not describe how NaN or infinities are encoded.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [arrow-adbc] lidavidm commented on issue #767: c/driver/postgresql: type of `SELECT SUM(bigints) FROM ...` is binary, not int64 as expected

Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #767:
URL: https://github.com/apache/arrow-adbc/issues/767#issuecomment-1589289969

   I'm tempted to punt on this because there is not a clear Arrow type to map to. 
   
   PostgreSQL supports a precision and scale far beyond what Arrow supports: 131072 + 16383 digits. It also supports NaN.
   
   Starting in PostgreSQL v14, it also supports +Infinity and -Infinity.
   
   The Arrow Decimal type does not support NaN or infinities, and only goes up to ~80 digits of precision.
   
   What we might want is:
   - Try our best to fit into a Decimal256 by default, erroring or emitting NULL for values outside the range, or unrepresentable values.
   - Allow emitting the raw binary values if desired.
   - Possibly support an encoding where we have `Union[Decimal, Bool, Null]` where decimal values go in the decimal column, null values go in the null column, and infinities are represented in the boolean column.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [arrow-adbc] lidavidm commented on issue #767: c/driver/postgresql: support NUMERIC type

Posted by "lidavidm (via GitHub)" <gi...@apache.org>.
lidavidm commented on issue #767:
URL: https://github.com/apache/arrow-adbc/issues/767#issuecomment-1591109429

   `https://github.com/pgjdbc/pgjdbc/pull/1304`
   
   JDBC returns Double.NAN or BigDecimal if you use getObject, else raises an exception on NaN if you request a BigDecimal (fun...)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org