search

spiceai / spiceai

A self-hostable CDN for databases. Spice provides a unified SQL query interface and portable runtime to locally materialize, accelerate, and query datasets across databases, data warehouses, and data lakes.
https://docs.spiceai.org
Apache License 2.0
1.83k stars 72 forks source link

SQLite accelerator doesn't support `date_part` #2351

Closed peasee closed 2 weeks ago

peasee commented 2 weeks ago

Queries that use e.g. extract(year), like some of those from TPCH (Q9) fail on SQLite accelerated datasets:

Query Error Unable to query arrow: ConnectionError Rusqlite("no such function: date_part in SELECT profit.nation, profit.o_year, sum(profit.amount) AS sum_profit FROM (SELECT nation.n_name AS nation, date_part('YEAR', orders.o_orderdate) AS o_year, ((lineitem.l_extendedprice * (CAST(1 AS DECIMAL(20,0)) - lineitem.l_discount)) - (partsupp.ps_supplycost * lineitem.l_quantity)) AS amount FROM part JOIN supplier ON true JOIN lineitem ON true JOIN partsupp ON true JOIN orders ON true JOIN nation ON true WHERE (((((((supplier.s_suppkey = lineitem.l_suppkey) AND (partsupp.ps_suppkey = lineitem.l_suppkey)) AND (partsupp.ps_partkey = lineitem.l_partkey)) AND (part.p_partkey = lineitem.l_partkey)) AND (orders.o_orderkey = lineitem.l_orderkey)) AND (supplier.s_nationkey = nation.n_nationkey)) AND part.p_name LIKE '%green%')) AS profit GROUP BY profit.nation, profit.o_year ORDER BY profit.nation ASC NULLS LAST, profit.o_year DESC NULLS FIRST at offset 108")
peasee commented 2 weeks ago

SQLite accelerator most likely needs a dialect set, similar to the fix for MySQL in #1951

peasee commented 2 weeks ago

Upstream datafusion change: https://github.com/apache/datafusion/pull/12161