Expectations of SessionContext::parse_sql_expr (it can't parse a lot of things)

[adriangb]

I'm wondering what is and what isn't valid to feed into this function and what the expectations are of what it can and can't parse.

use std::sync::Arc;

use arrow_schema::{DataType, Field, Schema};
use datafusion::{common::DFSchema, datasource::empty::EmptyTable, prelude::*};

#[tokio::main]
async fn main() {
    let ctx = SessionContext::new();
    let schema = Schema::new(
        vec![
            Field::new("a", DataType::Int32, false),
        ]
    );
    let provider = Arc::new(
        EmptyTable::new(
            Arc::new(schema.clone())
        )
    );
    let df_schema = DFSchema::try_from(schema).unwrap();

    ctx.register_table("test", provider).unwrap();
    // parsing a simple binary op works
    ctx.parse_sql_expr("a = 123", &df_schema).unwrap();
    // parsing a full select statement does not work
    // Error: Err(SchemaError(FieldNotFound { field: Column { relation: None, name: "select" }, valid_fields: [Column { relation: None, name: "a" }] }, Some("")))
    println!("Error: {:?}", ctx.parse_sql_expr("select * from test", &df_schema));
    // parsing anything with a reference to the table does not work
    // Error: Err(Plan("table 'datafusion.public.test' not found"))
    println!("Error: {:?}", ctx.parse_sql_expr("a in (select a from test)", &df_schema));
}

[jonahgao]

// parsing a full select statement does not work
// Error: Err(SchemaError(FieldNotFound { field: Column { relation: None, name: "select" }, valid_fields: [Column { relation: None, name: "a" }] }, Some("")))
println!("Error: {:?}", ctx.parse_sql_expr("select * from test", &df_schema));

This result seems reasonable. The ident select appearing in an expression was recognized as a column name. sqlparser-rs does not prohibit the keyword select from being used as a column name. For example

DataFusion CLI v43.0.0
> select select from (select 1 as select);
+--------+
| select |
+--------+
| 1      |
+--------+
1 row(s) fetched.

// parsing anything with a reference to the table does not work
// Error: Err(Plan("table 'datafusion.public.test' not found"))
println!("Error: {:?}", ctx.parse_sql_expr("a in (select a from test)", &df_schema));

This one seems to be a bug or a missing feature. parse_sql_expr does not add any TableSource in its ContextProvider, resulting in an inability to resolve relations in the FROM clause.
Maybe we need to visit the expression to find table references, as statement_to_plan does. We should retrieve them from the catalog and add them to the context provider.