Type coercion error: The type of Boolean AND Decimal128(10, 2) of binary physical should be same

[andygrove]

Describe the bug
Postgres:

postgres=# create table test (a decimal(10,2), b decimal(10,2));
CREATE TABLE
postgres=# select * from test;l
 a | b 
---+---
(0 rows)

postgres=# insert into test values (1,2);
INSERT 0 1

postgres=# select * from test;
  a   |  b   
------+------
 1.00 | 2.00
(1 row)

postgres=# select * from test where a != b and case when b > a then a/b else null end >= case when a > b then a/b else null end;
 a | b 
---+---
(0 rows)

DataFusion:

DataFusion CLI v12.0.0
❯ create external table test (a decimal(10,2), b decimal(10,2)) stored as csv location 'test.csv';
0 rows in set. Query took 0.001 seconds.

❯ select * from test where a != b and case when b > a then a/b else null end >= case when a > b then a/b else null end;
Internal("The type of Boolean AND Decimal128(10, 2) of binary physical should be same")

To Reproduce
See above

Expected behavior
Should work

Additional context
None

[comphead]

@andygrove please attach the test.csv file?

I tried 2 tests with your query in pure sql, those works.

#[tokio::test]
async fn test_decimal_optimizer() -> Result<()> {
    let ctx = SessionContext::new();

    let sql = "select * from (SELECT cast(1 as decimal(10,2)) a, cast(2 as decimal(10,2)) b) test where a != b and case when b > a then a/b else null end >= case when a > b then a/b else null end;";
    let actual = execute_to_batches(&ctx, sql).await;


    Ok(())
}

#[tokio::test]
async fn test_decimal_optimizer1() -> Result<()> {
    let schema = Arc::new(Schema::new(vec![
        Field::new("a", DataType::Decimal128(10,2), true),
        Field::new("b", DataType::Decimal128(10,2), true),
    ]));

    let data = RecordBatch::try_new(
        schema.clone(),
        vec![
            Arc::new(
                Decimal128Array::from_iter_values([1])
                    .with_precision_and_scale(10, 2)
                    .unwrap(),
            ),
            Arc::new(
                Decimal128Array::from_iter_values([2])
                    .with_precision_and_scale(10, 2)
                    .unwrap(),
            ),
        ],
    )?;

    let table = MemTable::try_new(schema, vec![vec![data]])?;

    let ctx = SessionContext::new();
    ctx.register_table("test", Arc::new(table))?;


    let sql = "select * from test where a != b and case when b > a then a/b else null end >= case when a > b then a/b else null end;";
    let actual = execute_to_batches(&ctx, sql).await;


    Ok(())
}

[andygrove]

Thanks for looking at this @comphead. The CSV file was just 1,2, but it looks like this issue was fixed yesterday in 29b8bbd (thanks @liukun4515!) so I will go ahead and close this.