Bug: Cannot coerce arithmetic expression Date32 + Int64 to valid types

[peasee]

Describe the bug

• Running the following TPCDS query in Postgres accelerator gives the error:
  72
  When running some TPCDS Q72 in with DuckDB in-memory acceleration, observe the following errors:

Status { code: Internal, message: "Failed to execute query: type_coercion\ncaused by\nError during planning: Cannot coerce arithmetic expression Date32 + Int64 to valid types", metadata: MetadataMap { headers: {"content-type": "application/grpc", "date": "Wed, 11 Sep 2024 06:37:42 GMT", "content-length": "0"} }, source: None }

TPCDS queries as seen in datafusion-benchmarks

To Reproduce

Steps to reproduce the behavior:

1. Configure a connector with a TPCDS data source using DuckDB in-memory acceleration
2. Run TPCDS Q72
3. See error

Expected behavior

The query should complete successfully.

Testing Validation

• Q72 in-memory DuckDB

Spicepod

version: v1beta1
kind: Spicepod
name: tpcds1.duckdb
datasets:
  - from: duckdb:call_center
    name: call_center
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:catalog_page
    name: catalog_page
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:catalog_returns
    name: catalog_returns
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:catalog_sales
    name: catalog_sales
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:customer
    name: customer
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:customer_address
    name: customer_address
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:customer_demographics
    name: customer_demographics
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:date_dim
    name: date_dim
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:household_demographics
    name: household_demographics
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:income_band
    name: income_band
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:inventory
    name: inventory
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:item
    name: item
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:promotion
    name: promotion
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:reason
    name: reason
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:ship_mode
    name: ship_mode
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:store
    name: store
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:store_returns
    name: store_returns
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:store_sales
    name: store_sales
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:time_dim
    name: time_dim
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:warehouse
    name: warehouse
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:web_page
    name: web_page
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:web_returns
    name: web_returns
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:web_sales
    name: web_sales
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory
  - from: duckdb:web_site
    name: web_site
    params:
      duckdb_open: tpcds1.duckdb
    acceleration:
      enabled: true
      engine: duckdb
      mode: memory

postgres spicepod config

version: v1beta1
kind: Spicepod
name: tpcds-sf0.1.db
datasets:
  - from: duckdb:call_center
    name: call_center
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:catalog_page
    name: catalog_page
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:catalog_returns
    name: catalog_returns
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:catalog_sales
    name: catalog_sales
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:customer
    name: customer
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:customer_address
    name: customer_address
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:customer_demographics
    name: customer_demographics
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:date_dim
    name: date_dim
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:household_demographics
    name: household_demographics
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:income_band
    name: income_band
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:inventory
    name: inventory
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:item
    name: item
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:promotion
    name: promotion
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:reason
    name: reason
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:ship_mode
    name: ship_mode
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:store
    name: store
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:store_returns
    name: store_returns
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:store_sales
    name: store_sales
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:time_dim
    name: time_dim
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:warehouse
    name: warehouse
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:web_page
    name: web_page
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:web_returns
    name: web_returns
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:web_sales
    name: web_sales
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds
  - from: duckdb:web_site
    name: web_site
    params:
      duckdb_open: tpcds-sf0.1.db
    acceleration:
      enabled: true
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5432
        pg_user: postgres
        pg_pass: postgres
        pg_sslmode: disable
        pg_db: tpcds

[bgjackma]

Possibly caused by apache/datafusion#12342

[Sevenannn]

• Closed by: Document DataFusion limitation: The context only support single SQL Statement, Date Arithmetic like date + 3 not supported #2970