Skip to content

Aggregations with parametrized FILTER WHERE clauses don't work #12065

Closed

Description

@karlovnv

Describe the bug

Queries with FILTER WHERE don't work when parameters are provided.

For instance, SELECT SUM (amount) FILTER (WHERE year = $1) FROM payments will fail on physical planning.

Query like COUNT (CASE WHEN year = $1 then amount ELSE NULL END) fails as well.

To Reproduce

async fn main() -> Result<()> {
    //  generate test memtable with 30 u64 columns and some data
    let table1 = generate_memtable(30)?;

    let cfg = SessionConfig::new()
        .set("datafusion.sql_parser.dialect", "PostgreSQL".into());

    let ctx = SessionContext::new_with_config(cfg);

    ctx.register_table("table1", Arc::new(table1))?;

    let dataframe = ctx.sql(
        "select count (f_0) filter (where f_1 > $1) from table1"
        ).await?
        .with_param_values(ParamValues::List(vec![ScalarValue::from(10u64)]));

    // fails here on physical planning:
    let result = dataframe?.collect().await.unwrap();

    let record_batch = result.first().unwrap();

    dbg!(record_batch.columns());

    Ok(())
}

This fails with error:

thread 'main' panicked at filter-where-repro.rs:46:45:
called `Result::unwrap()` on an `Err` value: 
Context("type_coercion", SchemaError(FieldNotFound { field: Column { relation: None, name: "count(table1.f_0) FILTER (WHERE table1.f_1 > $1)" }, valid_fields: [Column { relation: None, name: "count(table1.f_0) FILTER (WHERE table1.f_1 > UInt64(10))" }] }, Some("")))

Expected behavior

No response

Additional context

No response

Activity

  1. self-assigned this
    on Aug 21, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

bugSomething isn't working

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions