SQL execute error: SELECT count(*) FROM postgres.public.companies

[mmooyyii]

run the postgres.rs in example;

// Query Example 1: Query the renamed table through default catalog
    let df = ctx
        .sql("SELECT count(*) FROM postgres.public.companies")
        .await
        .expect("select failed");
    df.show().await.expect("show failed");

/Users/yimo/.cargo/bin/cargo run --color=always --example postgres --profile dev --manifest-path /Users/yimo/PycharmProjects/datafusion-table-providers/core/Cargo.toml
    Finished `dev` profile [unoptimized + debuginfo] target(s) in 0.29s
     Running `target/debug/examples/postgres`

thread 'main' panicked at core/examples/postgres.rs:106:21:
show failed: Internal("Physical input schema should be the same as the one converted from logical input schema. Differences: \n\t- Different number of fields: (physical) 1 vs (logical) 0")
stack backtrace:
   0: __rustc::rust_begin_unwind
             at /rustc/d7ea436a02d5de4033fcf7fd4eb8ed965d0f574c/library/std/src/panicking.rs:697:5
   1: core::panicking::panic_fmt
             at /rustc/d7ea436a02d5de4033fcf7fd4eb8ed965d0f574c/library/core/src/panicking.rs:75:14
   2: core::result::unwrap_failed
             at /rustc/d7ea436a02d5de4033fcf7fd4eb8ed965d0f574c/library/core/src/result.rs:1704:5
   3: core::result::Result<T,E>::expect
             at /Users/yimo/.rustup/toolchains/nightly-aarch64-apple-darwin/lib/rustlib/src/rust/library/core/src/result.rs:1061:23
   4: postgres::main::{{closure}}
             at ./core/examples/postgres.rs:106:5
   5: <core::pin::Pin<P> as core::future::future::Future>::poll
             at /Users/yimo/.rustup/toolchains/nightly-aarch64-apple-darwin/lib/rustlib/src/rust/library/core/src/future/future.rs:124:9
   6: tokio::runtime::park::CachedParkThread::block_on::{{closure}}
             at /Users/yimo/.cargo/registry/src/mirrors.ustc.edu.cn-5857e57f01837ef8/tokio-1.45.0/src/runtime/park.rs:284:60
   7: tokio::task::coop::with_budget
             at /Users/yimo/.cargo/registry/src/mirrors.ustc.edu.cn-5857e57f01837ef8/tokio-1.45.0/src/task/coop/mod.rs:167:5
   8: tokio::task::coop::budget
             at /Users/yimo/.cargo/registry/src/mirrors.ustc.edu.cn-5857e57f01837ef8/tokio-1.45.0/src/task/coop/mod.rs:133:5
   9: tokio::runtime::park::CachedParkThread::block_on
             at /Users/yimo/.cargo/registry/src/mirrors.ustc.edu.cn-5857e57f01837ef8/tokio-1.45.0/src/runtime/park.rs:284:31
  10: tokio::runtime::context::blocking::BlockingRegionGuard::block_on
             at /Users/yimo/.cargo/registry/src/mirrors.ustc.edu.cn-5857e57f01837ef8/tokio-1.45.0/src/runtime/context/blocking.rs:66:9
  11: tokio::runtime::scheduler::multi_thread::MultiThread::block_on::{{closure}}
             at /Users/yimo/.cargo/registry/src/mirrors.ustc.edu.cn-5857e57f01837ef8/tokio-1.45.0/src/runtime/scheduler/multi_thread/mod.rs:87:13
  12: tokio::runtime::context::runtime::enter_runtime
             at /Users/yimo/.cargo/registry/src/mirrors.ustc.edu.cn-5857e57f01837ef8/tokio-1.45.0/src/runtime/context/runtime.rs:65:16
  13: tokio::runtime::scheduler::multi_thread::MultiThread::block_on
             at /Users/yimo/.cargo/registry/src/mirrors.ustc.edu.cn-5857e57f01837ef8/tokio-1.45.0/src/runtime/scheduler/multi_thread/mod.rs:86:9
  14: tokio::runtime::runtime::Runtime::block_on_inner
             at /Users/yimo/.cargo/registry/src/mirrors.ustc.edu.cn-5857e57f01837ef8/tokio-1.45.0/src/runtime/runtime.rs:358:45
  15: tokio::runtime::runtime::Runtime::block_on
             at /Users/yimo/.cargo/registry/src/mirrors.ustc.edu.cn-5857e57f01837ef8/tokio-1.45.0/src/runtime/runtime.rs:328:13
  16: postgres::main
             at ./core/examples/postgres.rs:120:5
  17: core::ops::function::FnOnce::call_once
             at /Users/yimo/.rustup/toolchains/nightly-aarch64-apple-darwin/lib/rustlib/src/rust/library/core/src/ops/function.rs:250:5
note: Some details are omitted, run with `RUST_BACKTRACE=full` for a verbose backtrace.

Process finished with exit code 101

function project_schema_safe at datafusion-table-providers/core/src/sql/sql_provider_datafusion/mod.rs : 235
should impl as following

pub fn project_schema_safe(
    schema: &SchemaRef,
    projection: Option<&Vec<usize>>,
) -> DataFusionResult<SchemaRef> {
    let schema = match projection {
        Some(columns) => {
            Arc::new(schema.project(columns)?)
        }
        None => Arc::clone(schema),
    };
    Ok(schema)
}

[trueleo]

Thanks for the report.

datafusion-table-providers/core/src/sql/sql_provider_datafusion/mod.rs : 235 should impl as following

I don't think that will be enough to fix the issue here. schema.project will give out an empty schema.

There is a note there

If the projection is Some([]) then it gets unparsed as `SELECT 1`, so return a schema with a single Int64 
See: <https://github.com/apache/datafusion/blob/83ce79c39412a4f150167d00e40ea05948c4870f/datafusion/sql/src/unparser/plan.rs#L998>

Will have to check if that is actually the case still. Otherwise will have to handle empty schema manually in SqlExec with query SELECT 1 from table_name till that unparser logic is solved.

[mmooyyii]

Thanks for the report.

datafusion-table-providers/core/src/sql/sql_provider_datafusion/mod.rs : 235 should impl as following

I don't think that will be enough to fix the issue here. schema.project will give out an empty schema.

There is a note there

If the projection is Some([]) then it gets unparsed as `SELECT 1`, so return a schema with a single Int64 
See: <https://github.com/apache/datafusion/blob/83ce79c39412a4f150167d00e40ea05948c4870f/datafusion/sql/src/unparser/plan.rs#L998>

Will have to check if that is actually the case still. Otherwise will have to handle empty schema manually in SqlExec with query SELECT 1 from table_name till that unparser logic is solved.

I try it, and it solved the bug

[trueleo]

I try it, and it solved the bug

👍 Can you open a PR then? Thanks

[phillipleblanc]

We might need to change the examples to use the federated tables by default - the existing logic works when federation is added, which is the most useful way to use the postgres table provider. Alternatively, we might need a way to detect if the table is being federated or not and change the schema being returned.