Description
What feature or improvement would you like to see?
It looks like sqlite adbc driver currently casts decimal types into floats.. Can we support native decimal32, decimal64, decimal128 and decimal256 types?
Using the chinook.db sample. https://www.sqlitetutorial.net/sqlite-sample-database/
The Total column on the invoices table is defined as a Decimal(10,2)..
8|Total|NUMERIC(10,2)|1||0
Both ADBC's get_table_schema() and cursor.fetch_arrow_table() returns the column back as a float64.
Total: double
Trying to insert Total into a new table as a decimal64(10,2) gives this error:
adbc_driver_manager.ProgrammingError: INVALID_ARGUMENT: Failed to initialize array view: (22) Invalid argument: Error parsing schema->format: Expected decimal bitwidth of 128 or 256 but found 64
Trying to insert Total into a new table as a decimal128(10,2) gives this error:
adbc_driver_manager.NotSupportedError: NOT_IMPLEMENTED: Column 8 has unsupported type decimal128
>>> import adbc_driver_sqlite.dbapi as dbapi
>>> import pyarrow as pa
>>> import sqlite3
>>>
>>> sql_conn = sqlite3.connect("chinook.db")
>>> sql_conn.execute("PRAGMA table_info(invoices)").fetchall()
[(0, 'InvoiceId', 'INTEGER', 1, None, 1), (1, 'CustomerId', 'INTEGER', 1, None, 0), (2, 'InvoiceDate', 'DATETIME', 1, None, 0), (3, 'BillingAddress', 'NVARCHAR(70)', 0, None, 0), (4, 'BillingCity', 'NVARCHAR(40)', 0, None, 0), (5, 'BillingState', 'NVARCHAR(40)', 0, None, 0), (6, 'BillingCountry', 'NVARCHAR(40)', 0, None, 0), (7, 'BillingPostalCode', 'NVARCHAR(10)', 0, None, 0), (8, 'Total', 'NUMERIC(10,2)', 1, None, 0)]
>>>
>>> conn = dbapi.connect("chinook.db")
>>>
>>> conn.adbc_get_table_schema("invoices")
InvoiceId: int64
CustomerId: int64
InvoiceDate: string
BillingAddress: string
BillingCity: string
BillingState: string
BillingCountry: string
BillingPostalCode: string
Total: double
>>>
>>> cursor = conn.cursor()
>>>
>>> cursor.execute("select * from invoices limit 10")
>>>
>>> table = cursor.fetch_arrow_table()
>>>
>>> table.schema
InvoiceId: int64
CustomerId: int64
InvoiceDate: string
BillingAddress: string
BillingCity: string
BillingState: string
BillingCountry: string
BillingPostalCode: string
Total: double
>>>
>>> new_schema = pa.schema([("Total", pa.decimal64(10,2)) if column.name == "Total" else column for column in table.schema])
>>>
>>> new_table = table.cast(new_schema)
>>>
>>> cursor.adbc_ingest("invoices_new", new_table, mode="create")
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "C:\Users\leed\AppData\Local\miniconda3\lib\site-packages\adbc_driver_manager\dbapi.py", line 937, in adbc_ingest
return _blocking_call(self._stmt.execute_update, (), {}, self._stmt.cancel)
File "adbc_driver_manager\\_lib.pyx", line 1594, in adbc_driver_manager._lib._blocking_call
File "adbc_driver_manager\\_lib.pyx", line 1299, in adbc_driver_manager._lib.AdbcStatement.execute_update
File "adbc_driver_manager\\_lib.pyx", line 260, in adbc_driver_manager._lib.check_error
adbc_driver_manager.ProgrammingError: INVALID_ARGUMENT: Failed to initialize array view: (22) Invalid argument: Error parsing schema->format: Expected decimal bitwidth of 128 or 256 but found 64
>>>
>>> new_schema = pa.schema([("Total", pa.decimal128(10,2)) if column.name == "Total" else column for column in table.schema])
>>>
>>> new_table = table.cast(new_schema)
>>>
>>> cursor.adbc_ingest("invoices_new", new_table, mode="create")
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "C:\Users\leed\AppData\Local\miniconda3\lib\site-packages\adbc_driver_manager\dbapi.py", line 937, in adbc_ingest
return _blocking_call(self._stmt.execute_update, (), {}, self._stmt.cancel)
File "adbc_driver_manager\\_lib.pyx", line 1594, in adbc_driver_manager._lib._blocking_call
File "adbc_driver_manager\\_lib.pyx", line 1299, in adbc_driver_manager._lib.AdbcStatement.execute_update
File "adbc_driver_manager\\_lib.pyx", line 260, in adbc_driver_manager._lib.check_error
adbc_driver_manager.NotSupportedError: NOT_IMPLEMENTED: Column 8 has unsupported type decimal128