wbeardall opened a new issue, #4143:
URL: https://github.com/apache/arrow-adbc/issues/4143
### What happened?
The SQLite binaries shipped with `adbc-driver-sqlite` use a thinner set of
compile options as compared to common system standard SQLite builds. For
example, ENABLE_MATH_FUNCTIONS is not set, so common row-level mathematics
functions are missing in ADBC's SQLite. This results in divergent behaviour
between Python standard SQLite and ADBC's SQLite driver on the same machine.
## Expected behaviour
Official PyPI adbc-driver-sqlite binaries on a given platform should either:
- Match common system/Python SQLite capabilities for core math / hash
builtins that many apps rely on (MOD, COS, etc.), or
- Document clearly that wheels use a minimal SQLite and list unsupported
functions / required workarounds.
Preference is (1) for least surprise.
### Stack Trace
_No response_
### How can we reproduce the bug?
The behaviour is investigated as follows:
```
from __future__ import annotations
import importlib.resources
import importlib.util
import sqlite3
import sys
from collections.abc import Callable, Sequence
from pathlib import Path
import adbc_driver_sqlite
import adbc_driver_sqlite.dbapi as adb
DbConnect = Callable[[str], object]
def _sqlite3_module_location() -> str:
import _sqlite3
path = getattr(_sqlite3, "__file__", None)
if isinstance(path, str) and path:
return str(Path(path).resolve())
spec = importlib.util.find_spec("_sqlite3")
if spec is not None and spec.origin:
return str(Path(spec.origin).resolve())
return "<unknown>"
def _adbc_driver_library_path() -> str | None:
root = importlib.resources.files("adbc_driver_sqlite")
try:
for entry in root.iterdir():
if entry.is_file() and
entry.name.startswith("libadbc_driver_sqlite"):
return str(entry)
except (OSError, TypeError):
pass
return None
def _fetchall(cursor, sql: str) -> tuple[str, object]:
try:
cursor.execute(sql)
return "OK", cursor.fetchall()
except Exception as e: # noqa: BLE001 — diagnostic script
return "FAIL", f"{type(e).__name__}: {e}"
def _compile_options(cursor) -> list[str]:
status, rows = _fetchall(cursor, "PRAGMA compile_options")
if status != "OK" or not isinstance(rows, Sequence):
return [f"<error: {rows}>"]
return sorted(str(r[0]) for r in rows)
def _investigate(label: str, connect: DbConnect, db: str) -> frozenset[str]:
print("=" * 72)
print(label)
print("=" * 72)
cx = connect(db)
cur = cx.cursor() # pyright: ignore[reportAttributeAccessIssue]
for sql in (
"SELECT sqlite_version()",
"SELECT sqlite_source_id()",
):
status, out = _fetchall(cur, sql)
print(f" {sql}")
print(f" -> {status}: {out}")
opts = _compile_options(cur)
opt_set = (
frozenset(opts)
if opts and not str(opts[0]).startswith("<error")
else frozenset()
)
print(f" PRAGMA compile_options ({len(opts)} lines)")
for line in opts:
print(f" {line}")
print(" Modulo / remainder probes")
for sql in (
"SELECT MOD(3, 2)",
"SELECT mod(3, 2)",
"SELECT 3 % 2",
):
status, out = _fetchall(cur, sql)
print(f" {sql!r} -> {status}: {out}")
print(" pragma_function_list (name contains 'mod')")
status, out = _fetchall(
cur,
"""
SELECT name, builtin, type, narg
FROM pragma_function_list
WHERE lower(name) LIKE '%mod%'
ORDER BY name, narg
""",
)
if status == "OK" and isinstance(out, Sequence) and out:
for row in out:
print(f" {row}")
else:
print(f" -> {status}: {out}")
print(" pragma_function_list (first 20 names, sorted)")
status, out = _fetchall(
cur,
"""
SELECT name, builtin, type, narg
FROM pragma_function_list
ORDER BY name, narg
LIMIT 20
""",
)
if status == "OK" and isinstance(out, Sequence):
for row in out:
print(f" {row}")
else:
print(f" -> {status}: {out}")
close = getattr(cx, "close", None)
if callable(close):
close()
print()
return opt_set
def main() -> None:
db = "/tmp/t_adbc_compare.db"
sqlite3.connect(db).execute("CREATE TABLE IF NOT EXISTS t(id INTEGER);")
print("Python:", sys.version.split()[0])
print("Executable:", sys.executable)
print("sqlite3.sqlite_version:", sqlite3.sqlite_version)
print("adbc_driver_sqlite package:", adbc_driver_sqlite.__version__) #
pyright: ignore[reportPrivateImportUsage]
print("stdlib _sqlite3:", _sqlite3_module_location())
print(
"adbc driver library:", _adbc_driver_library_path() or "<not found
in package>"
)
print()
opts_std = _investigate("sqlite3.connect", sqlite3.connect, db)
opts_adb = _investigate("adbc_driver_sqlite.dbapi.connect", adb.connect,
db)
if opts_std and opts_adb:
only_std = sorted(opts_std - opts_adb)
only_adb = sorted(opts_adb - opts_std)
print("=" * 72)
print("PRAGMA compile_options — symmetric difference")
print("=" * 72)
print(f" Only in stdlib ({len(only_std)}):")
for x in only_std:
print(f" + {x}")
print(f" Only in ADBC ({len(only_adb)}):")
for x in only_adb:
print(f" + {x}")
if __name__ == "__main__":
main()
```
With sample trace
```
Python: 3.13.11
Executable: /path/to/.venv/bin/python3
sqlite3.sqlite_version: 3.50.4
adbc_driver_sqlite package: 1.10.0
stdlib _sqlite3: /path/to/built-in
adbc driver library:
/path/to/site-packages/adbc_driver_sqlite/libadbc_driver_sqlite.so
========================================================================
sqlite3.connect
========================================================================
SELECT sqlite_version()
-> OK: [('3.50.4',)]
SELECT sqlite_source_id()
-> OK: [('2025-07-30 19:33:53
4d8adfb30e03f9cf27f800a2c1ba3c48fb4ca1b08b0f5ed59a4d5ecbf45e20a3',)]
PRAGMA compile_options (45 lines)
ATOMIC_INTRINSICS=1
COMPILER=clang-21.1.4
DEFAULT_AUTOVACUUM
DEFAULT_CACHE_SIZE=-2000
DEFAULT_FILE_FORMAT=4
DEFAULT_JOURNAL_SIZE_LIMIT=-1
DEFAULT_MMAP_SIZE=0
DEFAULT_PAGE_SIZE=4096
DEFAULT_PCACHE_INITSZ=20
DEFAULT_RECURSIVE_TRIGGERS
DEFAULT_SECTOR_SIZE=4096
DEFAULT_SYNCHRONOUS=2
DEFAULT_WAL_AUTOCHECKPOINT=1000
DEFAULT_WAL_SYNCHRONOUS=2
DEFAULT_WORKER_THREADS=0
DIRECT_OVERFLOW_READ
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_FTS4
ENABLE_FTS5
ENABLE_GEOPOLY
ENABLE_MATH_FUNCTIONS
ENABLE_RTREE
MALLOC_SOFT_LIMIT=1024
MAX_ATTACHED=10
MAX_COLUMN=2000
MAX_COMPOUND_SELECT=500
MAX_DEFAULT_PAGE_SIZE=8192
MAX_EXPR_DEPTH=1000
MAX_FUNCTION_ARG=1000
MAX_LENGTH=1000000000
MAX_LIKE_PATTERN_LENGTH=50000
MAX_MMAP_SIZE=0x7fff0000
MAX_PAGE_COUNT=0xfffffffe
MAX_PAGE_SIZE=65536
MAX_SQL_LENGTH=1000000000
MAX_TRIGGER_DEPTH=1000
MAX_VARIABLE_NUMBER=32766
MAX_VDBE_OP=250000000
MAX_WORKER_THREADS=8
MUTEX_PTHREADS
SYSTEM_MALLOC
TEMP_STORE=1
THREADSAFE=1
Modulo / remainder probes
'SELECT MOD(3, 2)' -> OK: [(1.0,)]
'SELECT mod(3, 2)' -> OK: [(1.0,)]
'SELECT 3 % 2' -> OK: [(1,)]
pragma_function_list (name contains 'mod')
('mod', 1, 's', 2)
pragma_function_list (first 20 names, sorted)
('->', 1, 's', 2)
('->>', 1, 's', 2)
('abs', 1, 's', 1)
('acos', 1, 's', 1)
('acosh', 1, 's', 1)
('asin', 1, 's', 1)
('asinh', 1, 's', 1)
('atan', 1, 's', 1)
('atan2', 1, 's', 2)
('atanh', 1, 's', 1)
('avg', 1, 'w', 1)
('bm25', 0, 's', -1)
('ceil', 1, 's', 1)
('ceiling', 1, 's', 1)
('changes', 1, 's', 0)
('char', 1, 's', -1)
('coalesce', 1, 's', -4)
('concat', 1, 's', -3)
('concat_ws', 1, 's', -4)
('cos', 1, 's', 1)
========================================================================
adbc_driver_sqlite.dbapi.connect
========================================================================
SELECT sqlite_version()
-> OK: [('3.50.4',)]
SELECT sqlite_source_id()
-> OK: [('2025-07-30 19:33:53
4d8adfb30e03f9cf27f800a2c1ba3c48fb4ca1b08b0f5ed59a4d5ecbf45e20a3',)]
PRAGMA compile_options (39 lines)
ATOMIC_INTRINSICS=1
COMPILER=clang-16.0.0
DEFAULT_AUTOVACUUM
DEFAULT_CACHE_SIZE=-2000
DEFAULT_FILE_FORMAT=4
DEFAULT_JOURNAL_SIZE_LIMIT=-1
DEFAULT_MMAP_SIZE=0
DEFAULT_PAGE_SIZE=4096
DEFAULT_PCACHE_INITSZ=20
DEFAULT_RECURSIVE_TRIGGERS
DEFAULT_SECTOR_SIZE=4096
DEFAULT_SYNCHRONOUS=2
DEFAULT_WAL_AUTOCHECKPOINT=1000
DEFAULT_WAL_SYNCHRONOUS=2
DEFAULT_WORKER_THREADS=0
DIRECT_OVERFLOW_READ
ENABLE_COLUMN_METADATA
ENABLE_UNLOCK_NOTIFY
MALLOC_SOFT_LIMIT=1024
MAX_ATTACHED=10
MAX_COLUMN=2000
MAX_COMPOUND_SELECT=500
MAX_DEFAULT_PAGE_SIZE=8192
MAX_EXPR_DEPTH=1000
MAX_FUNCTION_ARG=1000
MAX_LENGTH=1000000000
MAX_LIKE_PATTERN_LENGTH=50000
MAX_MMAP_SIZE=0x7fff0000
MAX_PAGE_COUNT=0xfffffffe
MAX_PAGE_SIZE=65536
MAX_SQL_LENGTH=1000000000
MAX_TRIGGER_DEPTH=1000
MAX_VARIABLE_NUMBER=32766
MAX_VDBE_OP=250000000
MAX_WORKER_THREADS=8
MUTEX_PTHREADS
SYSTEM_MALLOC
TEMP_STORE=1
THREADSAFE=1
Modulo / remainder probes
'SELECT MOD(3, 2)' -> FAIL: ProgrammingError: INVALID_ARGUMENT: [SQLite]
Failed to prepare query: no such function: MOD
query: SELECT MOD(3, 2)
'SELECT mod(3, 2)' -> FAIL: ProgrammingError: INVALID_ARGUMENT: [SQLite]
Failed to prepare query: no such function: mod
query: SELECT mod(3, 2)
'SELECT 3 % 2' -> OK: [(1,)]
pragma_function_list (name contains 'mod')
-> OK: []
pragma_function_list (first 20 names, sorted)
('->', 1, 's', 2)
('->>', 1, 's', 2)
('abs', 1, 's', 1)
('avg', 1, 'w', 1)
('changes', 1, 's', 0)
('char', 1, 's', -1)
('coalesce', 1, 's', -4)
('concat', 1, 's', -3)
('concat_ws', 1, 's', -4)
('count', 1, 'w', 0)
('count', 1, 'w', 1)
('cume_dist', 1, 'w', 0)
('current_date', 1, 's', 0)
('current_time', 1, 's', 0)
('current_timestamp', 1, 's', 0)
('date', 1, 's', -1)
('datetime', 1, 's', -1)
('dense_rank', 1, 'w', 0)
('first_value', 1, 'w', 1)
('format', 1, 's', -1)
========================================================================
PRAGMA compile_options — symmetric difference
========================================================================
Only in stdlib (9):
+ COMPILER=clang-21.1.4
+ ENABLE_DBSTAT_VTAB
+ ENABLE_FTS3
+ ENABLE_FTS3_PARENTHESIS
+ ENABLE_FTS4
+ ENABLE_FTS5
+ ENABLE_GEOPOLY
+ ENABLE_MATH_FUNCTIONS
+ ENABLE_RTREE
Only in ADBC (3):
+ COMPILER=clang-16.0.0
+ ENABLE_COLUMN_METADATA
+ ENABLE_UNLOCK_NOTIFY
```
### Environment/Setup
- MacOS 26.3.1 (M1)
- ADBC installed from Pip wheels
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
