You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by al...@apache.org on 2025/05/22 18:30:18 UTC
(datafusion) branch main updated: Move prepare/parameter handling tests into `params.rs` (#16141)
This is an automated email from the ASF dual-hosted git repository.
alamb pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new dc8161e54f Move prepare/parameter handling tests into `params.rs` (#16141)
dc8161e54f is described below
commit dc8161e54f28c426749286e7d26f22b8ed502f08
Author: Liam Bao <li...@gmail.com>
AuthorDate: Thu May 22 14:30:14 2025 -0400
Move prepare/parameter handling tests into `params.rs` (#16141)
* Move prepare/parameter handling tests into `params.rs`
* Resolve conflicts
---
datafusion/expr/src/logical_plan/statement.rs | 2 +-
datafusion/sql/tests/cases/mod.rs | 1 +
datafusion/sql/tests/cases/params.rs | 964 ++++++++++++++++++++++++++
datafusion/sql/tests/sql_integration.rs | 954 +------------------------
4 files changed, 970 insertions(+), 951 deletions(-)
diff --git a/datafusion/expr/src/logical_plan/statement.rs b/datafusion/expr/src/logical_plan/statement.rs
index 9491143f37..72eb6b39bb 100644
--- a/datafusion/expr/src/logical_plan/statement.rs
+++ b/datafusion/expr/src/logical_plan/statement.rs
@@ -110,7 +110,7 @@ impl Statement {
Statement::Prepare(Prepare {
name, data_types, ..
}) => {
- write!(f, "Prepare: {name:?} {data_types:?} ")
+ write!(f, "Prepare: {name:?} {data_types:?}")
}
Statement::Execute(Execute {
name, parameters, ..
diff --git a/datafusion/sql/tests/cases/mod.rs b/datafusion/sql/tests/cases/mod.rs
index b3eedcdc41..426d188f63 100644
--- a/datafusion/sql/tests/cases/mod.rs
+++ b/datafusion/sql/tests/cases/mod.rs
@@ -17,4 +17,5 @@
mod collection;
mod diagnostic;
+mod params;
mod plan_to_sql;
diff --git a/datafusion/sql/tests/cases/params.rs b/datafusion/sql/tests/cases/params.rs
new file mode 100644
index 0000000000..7a4fbba147
--- /dev/null
+++ b/datafusion/sql/tests/cases/params.rs
@@ -0,0 +1,964 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+use crate::logical_plan;
+use arrow::datatypes::DataType;
+use datafusion_common::{assert_contains, ParamValues, ScalarValue};
+use datafusion_expr::{LogicalPlan, Prepare, Statement};
+use insta::assert_snapshot;
+use std::collections::HashMap;
+
+fn generate_prepare_stmt_and_data_types(sql: &str) -> (LogicalPlan, String) {
+ let plan = logical_plan(sql).unwrap();
+ let data_types = match &plan {
+ LogicalPlan::Statement(Statement::Prepare(Prepare { data_types, .. })) => {
+ format!("{data_types:?}")
+ }
+ _ => panic!("Expected a Prepare statement"),
+ };
+ (plan, data_types)
+}
+
+#[test]
+fn test_prepare_statement_to_plan_panic_param_format() {
+ // param is not number following the $ sign
+ // panic due to error returned from the parser
+ let sql = "PREPARE my_plan(INT) AS SELECT id, age FROM person WHERE age = $foo";
+
+ assert_snapshot!(
+ logical_plan(sql).unwrap_err().strip_backtrace(),
+ @r###"
+ Error during planning: Invalid placeholder, not a number: $foo
+ "###
+ );
+}
+
+#[test]
+fn test_prepare_statement_to_plan_panic_param_zero() {
+ // param is zero following the $ sign
+ // panic due to error returned from the parser
+ let sql = "PREPARE my_plan(INT) AS SELECT id, age FROM person WHERE age = $0";
+
+ assert_snapshot!(
+ logical_plan(sql).unwrap_err().strip_backtrace(),
+ @r###"
+ Error during planning: Invalid placeholder, zero is not a valid index: $0
+ "###
+ );
+}
+
+#[test]
+fn test_prepare_statement_to_plan_panic_prepare_wrong_syntax() {
+ // param is not number following the $ sign
+ // panic due to error returned from the parser
+ let sql = "PREPARE AS SELECT id, age FROM person WHERE age = $foo";
+ assert!(logical_plan(sql)
+ .unwrap_err()
+ .strip_backtrace()
+ .contains("Expected: AS, found: SELECT"))
+}
+
+#[test]
+fn test_prepare_statement_to_plan_panic_no_relation_and_constant_param() {
+ let sql = "PREPARE my_plan(INT) AS SELECT id + $1";
+
+ let plan = logical_plan(sql).unwrap_err().strip_backtrace();
+ assert_snapshot!(
+ plan,
+ @r"Schema error: No field named id."
+ );
+}
+
+#[test]
+fn test_prepare_statement_should_infer_types() {
+ // only provide 1 data type while using 2 params
+ let sql = "PREPARE my_plan(INT) AS SELECT 1 + $1 + $2";
+ let plan = logical_plan(sql).unwrap();
+ let actual_types = plan.get_parameter_types().unwrap();
+ let expected_types = HashMap::from([
+ ("$1".to_string(), Some(DataType::Int32)),
+ ("$2".to_string(), Some(DataType::Int64)),
+ ]);
+ assert_eq!(actual_types, expected_types);
+}
+
+#[test]
+fn test_non_prepare_statement_should_infer_types() {
+ // Non prepared statements (like SELECT) should also have their parameter types inferred
+ let sql = "SELECT 1 + $1";
+ let plan = logical_plan(sql).unwrap();
+ let actual_types = plan.get_parameter_types().unwrap();
+ let expected_types = HashMap::from([
+ // constant 1 is inferred to be int64
+ ("$1".to_string(), Some(DataType::Int64)),
+ ]);
+ assert_eq!(actual_types, expected_types);
+}
+
+#[test]
+#[should_panic(
+ expected = "Expected: [NOT] NULL | TRUE | FALSE | DISTINCT | [form] NORMALIZED FROM after IS, found: $1"
+)]
+fn test_prepare_statement_to_plan_panic_is_param() {
+ let sql = "PREPARE my_plan(INT) AS SELECT id, age FROM person WHERE age is $1";
+ logical_plan(sql).unwrap();
+}
+
+#[test]
+fn test_prepare_statement_to_plan_no_param() {
+ // no embedded parameter but still declare it
+ let sql = "PREPARE my_plan(INT) AS SELECT id, age FROM person WHERE age = 10";
+ let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
+ assert_snapshot!(
+ plan,
+ @r#"
+ Prepare: "my_plan" [Int32]
+ Projection: person.id, person.age
+ Filter: person.age = Int64(10)
+ TableScan: person
+ "#
+ );
+ assert_snapshot!(dt, @r#"[Int32]"#);
+
+ ///////////////////
+ // replace params with values
+ let param_values = vec![ScalarValue::Int32(Some(10))];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Projection: person.id, person.age
+ Filter: person.age = Int64(10)
+ TableScan: person
+ "
+ );
+
+ //////////////////////////////////////////
+ // no embedded parameter and no declare it
+ let sql = "PREPARE my_plan AS SELECT id, age FROM person WHERE age = 10";
+ let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
+ assert_snapshot!(
+ plan,
+ @r#"
+ Prepare: "my_plan" []
+ Projection: person.id, person.age
+ Filter: person.age = Int64(10)
+ TableScan: person
+ "#
+ );
+ assert_snapshot!(dt, @r#"[]"#);
+
+ ///////////////////
+ // replace params with values
+ let param_values: Vec<ScalarValue> = vec![];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Projection: person.id, person.age
+ Filter: person.age = Int64(10)
+ TableScan: person
+ "
+ );
+}
+
+#[test]
+fn test_prepare_statement_to_plan_one_param_no_value_panic() {
+ // no embedded parameter but still declare it
+ let sql = "PREPARE my_plan(INT) AS SELECT id, age FROM person WHERE age = 10";
+ let plan = logical_plan(sql).unwrap();
+ // declare 1 param but provide 0
+ let param_values: Vec<ScalarValue> = vec![];
+
+ assert_snapshot!(
+ plan.with_param_values(param_values)
+ .unwrap_err()
+ .strip_backtrace(),
+ @r###"
+ Error during planning: Expected 1 parameters, got 0
+ "###);
+}
+
+#[test]
+fn test_prepare_statement_to_plan_one_param_one_value_different_type_panic() {
+ // no embedded parameter but still declare it
+ let sql = "PREPARE my_plan(INT) AS SELECT id, age FROM person WHERE age = 10";
+ let plan = logical_plan(sql).unwrap();
+ // declare 1 param but provide 0
+ let param_values = vec![ScalarValue::Float64(Some(20.0))];
+
+ assert_snapshot!(
+ plan.with_param_values(param_values)
+ .unwrap_err()
+ .strip_backtrace(),
+ @r###"
+ Error during planning: Expected parameter of type Int32, got Float64 at index 0
+ "###
+ );
+}
+
+#[test]
+fn test_prepare_statement_to_plan_no_param_on_value_panic() {
+ // no embedded parameter but still declare it
+ let sql = "PREPARE my_plan AS SELECT id, age FROM person WHERE age = 10";
+ let plan = logical_plan(sql).unwrap();
+ // declare 1 param but provide 0
+ let param_values = vec![ScalarValue::Int32(Some(10))];
+
+ assert_snapshot!(
+ plan.with_param_values(param_values)
+ .unwrap_err()
+ .strip_backtrace(),
+ @r###"
+ Error during planning: Expected 0 parameters, got 1
+ "###
+ );
+}
+
+#[test]
+fn test_prepare_statement_to_plan_params_as_constants() {
+ let sql = "PREPARE my_plan(INT) AS SELECT $1";
+ let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
+ assert_snapshot!(
+ plan,
+ @r#"
+ Prepare: "my_plan" [Int32]
+ Projection: $1
+ EmptyRelation
+ "#
+ );
+ assert_snapshot!(dt, @r#"[Int32]"#);
+
+ ///////////////////
+ // replace params with values
+ let param_values = vec![ScalarValue::Int32(Some(10))];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Projection: Int32(10) AS $1
+ EmptyRelation
+ "
+ );
+
+ ///////////////////////////////////////
+ let sql = "PREPARE my_plan(INT) AS SELECT 1 + $1";
+ let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
+ assert_snapshot!(
+ plan,
+ @r#"
+ Prepare: "my_plan" [Int32]
+ Projection: Int64(1) + $1
+ EmptyRelation
+ "#
+ );
+ assert_snapshot!(dt, @r#"[Int32]"#);
+
+ ///////////////////
+ // replace params with values
+ let param_values = vec![ScalarValue::Int32(Some(10))];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Projection: Int64(1) + Int32(10) AS Int64(1) + $1
+ EmptyRelation
+ "
+ );
+
+ ///////////////////////////////////////
+ let sql = "PREPARE my_plan(INT, DOUBLE) AS SELECT 1 + $1 + $2";
+ let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
+ assert_snapshot!(
+ plan,
+ @r#"
+ Prepare: "my_plan" [Int32, Float64]
+ Projection: Int64(1) + $1 + $2
+ EmptyRelation
+ "#
+ );
+ assert_snapshot!(dt, @r#"[Int32, Float64]"#);
+
+ ///////////////////
+ // replace params with values
+ let param_values = vec![
+ ScalarValue::Int32(Some(10)),
+ ScalarValue::Float64(Some(10.0)),
+ ];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Projection: Int64(1) + Int32(10) + Float64(10) AS Int64(1) + $1 + $2
+ EmptyRelation
+ "
+ );
+}
+
+#[test]
+fn test_infer_types_from_join() {
+ let sql =
+ "SELECT id, order_id FROM person JOIN orders ON id = customer_id and age = $1";
+
+ let plan = logical_plan(sql).unwrap();
+ assert_snapshot!(
+ plan,
+ @r#"
+ Projection: person.id, orders.order_id
+ Inner Join: Filter: person.id = orders.customer_id AND person.age = $1
+ TableScan: person
+ TableScan: orders
+ "#
+ );
+
+ let actual_types = plan.get_parameter_types().unwrap();
+ let expected_types = HashMap::from([("$1".to_string(), Some(DataType::Int32))]);
+ assert_eq!(actual_types, expected_types);
+
+ // replace params with values
+ let param_values = vec![ScalarValue::Int32(Some(10))];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Projection: person.id, orders.order_id
+ Inner Join: Filter: person.id = orders.customer_id AND person.age = Int32(10)
+ TableScan: person
+ TableScan: orders
+ "
+ );
+}
+
+#[test]
+fn test_prepare_statement_infer_types_from_join() {
+ let sql =
+ "PREPARE my_plan AS SELECT id, order_id FROM person JOIN orders ON id = customer_id and age = $1";
+
+ let plan = logical_plan(sql).unwrap();
+ assert_snapshot!(
+ plan,
+ @r#"
+ Prepare: "my_plan" [Int32]
+ Projection: person.id, orders.order_id
+ Inner Join: Filter: person.id = orders.customer_id AND person.age = $1
+ TableScan: person
+ TableScan: orders
+ "#
+ );
+
+ let actual_types = plan.get_parameter_types().unwrap();
+ let expected_types = HashMap::from([("$1".to_string(), Some(DataType::Int32))]);
+ assert_eq!(actual_types, expected_types);
+
+ // replace params with values
+ let param_values = vec![ScalarValue::Int32(Some(10))];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Projection: person.id, orders.order_id
+ Inner Join: Filter: person.id = orders.customer_id AND person.age = Int32(10)
+ TableScan: person
+ TableScan: orders
+ "
+ );
+}
+
+#[test]
+fn test_infer_types_from_predicate() {
+ let sql = "SELECT id, age FROM person WHERE age = $1";
+ let plan = logical_plan(sql).unwrap();
+ assert_snapshot!(
+ plan,
+ @r#"
+ Projection: person.id, person.age
+ Filter: person.age = $1
+ TableScan: person
+ "#
+ );
+
+ let actual_types = plan.get_parameter_types().unwrap();
+ let expected_types = HashMap::from([("$1".to_string(), Some(DataType::Int32))]);
+ assert_eq!(actual_types, expected_types);
+
+ // replace params with values
+ let param_values = vec![ScalarValue::Int32(Some(10))];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Projection: person.id, person.age
+ Filter: person.age = Int32(10)
+ TableScan: person
+ "
+ );
+}
+
+#[test]
+fn test_prepare_statement_infer_types_from_predicate() {
+ let sql = "PREPARE my_plan AS SELECT id, age FROM person WHERE age = $1";
+ let plan = logical_plan(sql).unwrap();
+ assert_snapshot!(
+ plan,
+ @r#"
+ Prepare: "my_plan" [Int32]
+ Projection: person.id, person.age
+ Filter: person.age = $1
+ TableScan: person
+ "#
+ );
+
+ let actual_types = plan.get_parameter_types().unwrap();
+ let expected_types = HashMap::from([("$1".to_string(), Some(DataType::Int32))]);
+ assert_eq!(actual_types, expected_types);
+
+ // replace params with values
+ let param_values = vec![ScalarValue::Int32(Some(10))];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Projection: person.id, person.age
+ Filter: person.age = Int32(10)
+ TableScan: person
+ "
+ );
+}
+
+#[test]
+fn test_infer_types_from_between_predicate() {
+ let sql = "SELECT id, age FROM person WHERE age BETWEEN $1 AND $2";
+
+ let plan = logical_plan(sql).unwrap();
+ assert_snapshot!(
+ plan,
+ @r#"
+ Projection: person.id, person.age
+ Filter: person.age BETWEEN $1 AND $2
+ TableScan: person
+ "#
+ );
+
+ let actual_types = plan.get_parameter_types().unwrap();
+ let expected_types = HashMap::from([
+ ("$1".to_string(), Some(DataType::Int32)),
+ ("$2".to_string(), Some(DataType::Int32)),
+ ]);
+ assert_eq!(actual_types, expected_types);
+
+ // replace params with values
+ let param_values = vec![ScalarValue::Int32(Some(10)), ScalarValue::Int32(Some(30))];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Projection: person.id, person.age
+ Filter: person.age BETWEEN Int32(10) AND Int32(30)
+ TableScan: person
+ "
+ );
+}
+
+#[test]
+fn test_prepare_statement_infer_types_from_between_predicate() {
+ let sql = "PREPARE my_plan AS SELECT id, age FROM person WHERE age BETWEEN $1 AND $2";
+
+ let plan = logical_plan(sql).unwrap();
+ assert_snapshot!(
+ plan,
+ @r#"
+ Prepare: "my_plan" [Int32, Int32]
+ Projection: person.id, person.age
+ Filter: person.age BETWEEN $1 AND $2
+ TableScan: person
+ "#
+ );
+
+ let actual_types = plan.get_parameter_types().unwrap();
+ let expected_types = HashMap::from([
+ ("$1".to_string(), Some(DataType::Int32)),
+ ("$2".to_string(), Some(DataType::Int32)),
+ ]);
+ assert_eq!(actual_types, expected_types);
+
+ // replace params with values
+ let param_values = vec![ScalarValue::Int32(Some(10)), ScalarValue::Int32(Some(30))];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Projection: person.id, person.age
+ Filter: person.age BETWEEN Int32(10) AND Int32(30)
+ TableScan: person
+ "
+ );
+}
+
+#[test]
+fn test_infer_types_subquery() {
+ let sql = "SELECT id, age FROM person WHERE age = (select max(age) from person where id = $1)";
+
+ let plan = logical_plan(sql).unwrap();
+ assert_snapshot!(
+ plan,
+ @r#"
+ Projection: person.id, person.age
+ Filter: person.age = (<subquery>)
+ Subquery:
+ Projection: max(person.age)
+ Aggregate: groupBy=[[]], aggr=[[max(person.age)]]
+ Filter: person.id = $1
+ TableScan: person
+ TableScan: person
+ "#
+ );
+
+ let actual_types = plan.get_parameter_types().unwrap();
+ let expected_types = HashMap::from([("$1".to_string(), Some(DataType::UInt32))]);
+ assert_eq!(actual_types, expected_types);
+
+ // replace params with values
+ let param_values = vec![ScalarValue::UInt32(Some(10))];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Projection: person.id, person.age
+ Filter: person.age = (<subquery>)
+ Subquery:
+ Projection: max(person.age)
+ Aggregate: groupBy=[[]], aggr=[[max(person.age)]]
+ Filter: person.id = UInt32(10)
+ TableScan: person
+ TableScan: person
+ "
+ );
+}
+
+#[test]
+fn test_prepare_statement_infer_types_subquery() {
+ let sql = "PREPARE my_plan AS SELECT id, age FROM person WHERE age = (select max(age) from person where id = $1)";
+
+ let plan = logical_plan(sql).unwrap();
+ assert_snapshot!(
+ plan,
+ @r#"
+ Prepare: "my_plan" [UInt32]
+ Projection: person.id, person.age
+ Filter: person.age = (<subquery>)
+ Subquery:
+ Projection: max(person.age)
+ Aggregate: groupBy=[[]], aggr=[[max(person.age)]]
+ Filter: person.id = $1
+ TableScan: person
+ TableScan: person
+ "#
+ );
+
+ let actual_types = plan.get_parameter_types().unwrap();
+ let expected_types = HashMap::from([("$1".to_string(), Some(DataType::UInt32))]);
+ assert_eq!(actual_types, expected_types);
+
+ // replace params with values
+ let param_values = vec![ScalarValue::UInt32(Some(10))];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Projection: person.id, person.age
+ Filter: person.age = (<subquery>)
+ Subquery:
+ Projection: max(person.age)
+ Aggregate: groupBy=[[]], aggr=[[max(person.age)]]
+ Filter: person.id = UInt32(10)
+ TableScan: person
+ TableScan: person
+ "
+ );
+}
+
+#[test]
+fn test_update_infer() {
+ let sql = "update person set age=$1 where id=$2";
+
+ let plan = logical_plan(sql).unwrap();
+ assert_snapshot!(
+ plan,
+ @r#"
+ Dml: op=[Update] table=[person]
+ Projection: person.id AS id, person.first_name AS first_name, person.last_name AS last_name, $1 AS age, person.state AS state, person.salary AS salary, person.birth_date AS birth_date, person.😀 AS 😀
+ Filter: person.id = $2
+ TableScan: person
+ "#
+ );
+
+ let actual_types = plan.get_parameter_types().unwrap();
+ let expected_types = HashMap::from([
+ ("$1".to_string(), Some(DataType::Int32)),
+ ("$2".to_string(), Some(DataType::UInt32)),
+ ]);
+ assert_eq!(actual_types, expected_types);
+
+ // replace params with values
+ let param_values = vec![ScalarValue::Int32(Some(42)), ScalarValue::UInt32(Some(1))];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Dml: op=[Update] table=[person]
+ Projection: person.id AS id, person.first_name AS first_name, person.last_name AS last_name, Int32(42) AS age, person.state AS state, person.salary AS salary, person.birth_date AS birth_date, person.😀 AS 😀
+ Filter: person.id = UInt32(1)
+ TableScan: person
+ "
+ );
+}
+
+#[test]
+fn test_prepare_statement_update_infer() {
+ let sql = "PREPARE my_plan AS update person set age=$1 where id=$2";
+
+ let plan = logical_plan(sql).unwrap();
+ assert_snapshot!(
+ plan,
+ @r#"
+ Prepare: "my_plan" [Int32, UInt32]
+ Dml: op=[Update] table=[person]
+ Projection: person.id AS id, person.first_name AS first_name, person.last_name AS last_name, $1 AS age, person.state AS state, person.salary AS salary, person.birth_date AS birth_date, person.😀 AS 😀
+ Filter: person.id = $2
+ TableScan: person
+ "#
+ );
+
+ let actual_types = plan.get_parameter_types().unwrap();
+ let expected_types = HashMap::from([
+ ("$1".to_string(), Some(DataType::Int32)),
+ ("$2".to_string(), Some(DataType::UInt32)),
+ ]);
+ assert_eq!(actual_types, expected_types);
+
+ // replace params with values
+ let param_values = vec![ScalarValue::Int32(Some(42)), ScalarValue::UInt32(Some(1))];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Dml: op=[Update] table=[person]
+ Projection: person.id AS id, person.first_name AS first_name, person.last_name AS last_name, Int32(42) AS age, person.state AS state, person.salary AS salary, person.birth_date AS birth_date, person.😀 AS 😀
+ Filter: person.id = UInt32(1)
+ TableScan: person
+ "
+ );
+}
+
+#[test]
+fn test_insert_infer() {
+ let sql = "insert into person (id, first_name, last_name) values ($1, $2, $3)";
+ let plan = logical_plan(sql).unwrap();
+ assert_snapshot!(
+ plan,
+ @r#"
+ Dml: op=[Insert Into] table=[person]
+ Projection: column1 AS id, column2 AS first_name, column3 AS last_name, CAST(NULL AS Int32) AS age, CAST(NULL AS Utf8) AS state, CAST(NULL AS Float64) AS salary, CAST(NULL AS Timestamp(Nanosecond, None)) AS birth_date, CAST(NULL AS Int32) AS 😀
+ Values: ($1, $2, $3)
+ "#
+ );
+
+ let actual_types = plan.get_parameter_types().unwrap();
+ let expected_types = HashMap::from([
+ ("$1".to_string(), Some(DataType::UInt32)),
+ ("$2".to_string(), Some(DataType::Utf8)),
+ ("$3".to_string(), Some(DataType::Utf8)),
+ ]);
+ assert_eq!(actual_types, expected_types);
+
+ // replace params with values
+ let param_values = vec![
+ ScalarValue::UInt32(Some(1)),
+ ScalarValue::from("Alan"),
+ ScalarValue::from("Turing"),
+ ];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+ assert_snapshot!(
+ plan_with_params,
+ @r#"
+ Dml: op=[Insert Into] table=[person]
+ Projection: column1 AS id, column2 AS first_name, column3 AS last_name, CAST(NULL AS Int32) AS age, CAST(NULL AS Utf8) AS state, CAST(NULL AS Float64) AS salary, CAST(NULL AS Timestamp(Nanosecond, None)) AS birth_date, CAST(NULL AS Int32) AS 😀
+ Values: (UInt32(1) AS $1, Utf8("Alan") AS $2, Utf8("Turing") AS $3)
+ "#
+ );
+}
+
+#[test]
+fn test_prepare_statement_insert_infer() {
+ let sql = "PREPARE my_plan AS insert into person (id, first_name, last_name) values ($1, $2, $3)";
+ let plan = logical_plan(sql).unwrap();
+ assert_snapshot!(
+ plan,
+ @r#"
+ Prepare: "my_plan" [UInt32, Utf8, Utf8]
+ Dml: op=[Insert Into] table=[person]
+ Projection: column1 AS id, column2 AS first_name, column3 AS last_name, CAST(NULL AS Int32) AS age, CAST(NULL AS Utf8) AS state, CAST(NULL AS Float64) AS salary, CAST(NULL AS Timestamp(Nanosecond, None)) AS birth_date, CAST(NULL AS Int32) AS 😀
+ Values: ($1, $2, $3)
+ "#
+ );
+
+ let actual_types = plan.get_parameter_types().unwrap();
+ let expected_types = HashMap::from([
+ ("$1".to_string(), Some(DataType::UInt32)),
+ ("$2".to_string(), Some(DataType::Utf8)),
+ ("$3".to_string(), Some(DataType::Utf8)),
+ ]);
+ assert_eq!(actual_types, expected_types);
+
+ // replace params with values
+ let param_values = vec![
+ ScalarValue::UInt32(Some(1)),
+ ScalarValue::from("Alan"),
+ ScalarValue::from("Turing"),
+ ];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+ assert_snapshot!(
+ plan_with_params,
+ @r#"
+ Dml: op=[Insert Into] table=[person]
+ Projection: column1 AS id, column2 AS first_name, column3 AS last_name, CAST(NULL AS Int32) AS age, CAST(NULL AS Utf8) AS state, CAST(NULL AS Float64) AS salary, CAST(NULL AS Timestamp(Nanosecond, None)) AS birth_date, CAST(NULL AS Int32) AS 😀
+ Values: (UInt32(1) AS $1, Utf8("Alan") AS $2, Utf8("Turing") AS $3)
+ "#
+ );
+}
+
+#[test]
+fn test_prepare_statement_to_plan_one_param() {
+ let sql = "PREPARE my_plan(INT) AS SELECT id, age FROM person WHERE age = $1";
+ let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
+ assert_snapshot!(
+ plan,
+ @r#"
+ Prepare: "my_plan" [Int32]
+ Projection: person.id, person.age
+ Filter: person.age = $1
+ TableScan: person
+ "#
+ );
+ assert_snapshot!(dt, @r#"[Int32]"#);
+
+ ///////////////////
+ // replace params with values
+ let param_values = vec![ScalarValue::Int32(Some(10))];
+
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Projection: person.id, person.age
+ Filter: person.age = Int32(10)
+ TableScan: person
+ "
+ );
+}
+
+#[test]
+fn test_prepare_statement_to_plan_data_type() {
+ let sql = "PREPARE my_plan(DOUBLE) AS SELECT id, age FROM person WHERE age = $1";
+
+ let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
+ assert_snapshot!(
+ plan,
+ // age is defined as Int32 but prepare statement declares it as DOUBLE/Float64
+ // Prepare statement and its logical plan should be created successfully
+ @r#"
+ Prepare: "my_plan" [Float64]
+ Projection: person.id, person.age
+ Filter: person.age = $1
+ TableScan: person
+ "#
+ );
+ assert_snapshot!(dt, @r#"[Float64]"#);
+
+ ///////////////////
+ // replace params with values still succeed and use Float64
+ let param_values = vec![ScalarValue::Float64(Some(10.0))];
+
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+ assert_snapshot!(
+ plan_with_params,
+ @r"
+ Projection: person.id, person.age
+ Filter: person.age = Float64(10)
+ TableScan: person
+ "
+ );
+}
+
+#[test]
+fn test_prepare_statement_to_plan_multi_params() {
+ let sql = "PREPARE my_plan(INT, STRING, DOUBLE, INT, DOUBLE, STRING) AS
+ SELECT id, age, $6
+ FROM person
+ WHERE age IN ($1, $4) AND salary > $3 and salary < $5 OR first_name < $2";
+ let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
+ assert_snapshot!(
+ plan,
+ @r#"
+ Prepare: "my_plan" [Int32, Utf8, Float64, Int32, Float64, Utf8]
+ Projection: person.id, person.age, $6
+ Filter: person.age IN ([$1, $4]) AND person.salary > $3 AND person.salary < $5 OR person.first_name < $2
+ TableScan: person
+ "#
+ );
+ assert_snapshot!(dt, @r#"[Int32, Utf8, Float64, Int32, Float64, Utf8]"#);
+
+ ///////////////////
+ // replace params with values
+ let param_values = vec![
+ ScalarValue::Int32(Some(10)),
+ ScalarValue::from("abc"),
+ ScalarValue::Float64(Some(100.0)),
+ ScalarValue::Int32(Some(20)),
+ ScalarValue::Float64(Some(200.0)),
+ ScalarValue::from("xyz"),
+ ];
+
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+ assert_snapshot!(
+ plan_with_params,
+ @r#"
+ Projection: person.id, person.age, Utf8("xyz") AS $6
+ Filter: person.age IN ([Int32(10), Int32(20)]) AND person.salary > Float64(100) AND person.salary < Float64(200) OR person.first_name < Utf8("abc")
+ TableScan: person
+ "#
+ );
+}
+
+#[test]
+fn test_prepare_statement_to_plan_having() {
+ let sql = "PREPARE my_plan(INT, DOUBLE, DOUBLE, DOUBLE) AS
+ SELECT id, sum(age)
+ FROM person \
+ WHERE salary > $2
+ GROUP BY id
+ HAVING sum(age) < $1 AND sum(age) > 10 OR sum(age) in ($3, $4)\
+ ";
+ let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
+ assert_snapshot!(
+ plan,
+ @r#"
+ Prepare: "my_plan" [Int32, Float64, Float64, Float64]
+ Projection: person.id, sum(person.age)
+ Filter: sum(person.age) < $1 AND sum(person.age) > Int64(10) OR sum(person.age) IN ([$3, $4])
+ Aggregate: groupBy=[[person.id]], aggr=[[sum(person.age)]]
+ Filter: person.salary > $2
+ TableScan: person
+ "#
+ );
+ assert_snapshot!(dt, @r#"[Int32, Float64, Float64, Float64]"#);
+
+ ///////////////////
+ // replace params with values
+ let param_values = vec![
+ ScalarValue::Int32(Some(10)),
+ ScalarValue::Float64(Some(100.0)),
+ ScalarValue::Float64(Some(200.0)),
+ ScalarValue::Float64(Some(300.0)),
+ ];
+
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+ assert_snapshot!(
+ plan_with_params,
+ @r#"
+ Projection: person.id, sum(person.age)
+ Filter: sum(person.age) < Int32(10) AND sum(person.age) > Int64(10) OR sum(person.age) IN ([Float64(200), Float64(300)])
+ Aggregate: groupBy=[[person.id]], aggr=[[sum(person.age)]]
+ Filter: person.salary > Float64(100)
+ TableScan: person
+ "#
+ );
+}
+
+#[test]
+fn test_prepare_statement_to_plan_limit() {
+ let sql = "PREPARE my_plan(BIGINT, BIGINT) AS
+ SELECT id FROM person \
+ OFFSET $1 LIMIT $2";
+ let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
+ assert_snapshot!(
+ plan,
+ @r#"
+ Prepare: "my_plan" [Int64, Int64]
+ Limit: skip=$1, fetch=$2
+ Projection: person.id
+ TableScan: person
+ "#
+ );
+ assert_snapshot!(dt, @r#"[Int64, Int64]"#);
+
+ // replace params with values
+ let param_values = vec![ScalarValue::Int64(Some(10)), ScalarValue::Int64(Some(200))];
+ let plan_with_params = plan.with_param_values(param_values).unwrap();
+ assert_snapshot!(
+ plan_with_params,
+ @r#"
+ Limit: skip=10, fetch=200
+ Projection: person.id
+ TableScan: person
+ "#
+ );
+}
+
+#[test]
+fn test_prepare_statement_unknown_list_param() {
+ let sql = "SELECT id from person where id = $2";
+ let plan = logical_plan(sql).unwrap();
+ let param_values = ParamValues::List(vec![]);
+ let err = plan.replace_params_with_values(¶m_values).unwrap_err();
+ assert_contains!(
+ err.to_string(),
+ "Error during planning: No value found for placeholder with id $2"
+ );
+}
+
+#[test]
+fn test_prepare_statement_unknown_hash_param() {
+ let sql = "SELECT id from person where id = $bar";
+ let plan = logical_plan(sql).unwrap();
+ let param_values = ParamValues::Map(HashMap::new());
+ let err = plan.replace_params_with_values(¶m_values).unwrap_err();
+ assert_contains!(
+ err.to_string(),
+ "Error during planning: No value found for placeholder with name $bar"
+ );
+}
+
+#[test]
+fn test_prepare_statement_bad_list_idx() {
+ let sql = "SELECT id from person where id = $foo";
+ let plan = logical_plan(sql).unwrap();
+ let param_values = ParamValues::List(vec![]);
+
+ let err = plan.replace_params_with_values(¶m_values).unwrap_err();
+ assert_contains!(err.to_string(), "Error during planning: Failed to parse placeholder id: invalid digit found in string");
+}
diff --git a/datafusion/sql/tests/sql_integration.rs b/datafusion/sql/tests/sql_integration.rs
index 8e058d350a..365012b7f6 100644
--- a/datafusion/sql/tests/sql_integration.rs
+++ b/datafusion/sql/tests/sql_integration.rs
@@ -17,21 +17,16 @@
use std::any::Any;
#[cfg(test)]
-use std::collections::HashMap;
use std::sync::Arc;
use std::vec;
use arrow::datatypes::{TimeUnit::Nanosecond, *};
use common::MockContextProvider;
-use datafusion_common::{
- assert_contains, DataFusionError, ParamValues, Result, ScalarValue,
-};
+use datafusion_common::{assert_contains, DataFusionError, Result};
use datafusion_expr::{
- col,
- logical_plan::{LogicalPlan, Prepare},
- test::function_stub::sum_udaf,
- ColumnarValue, CreateIndex, DdlStatement, ScalarFunctionArgs, ScalarUDF,
- ScalarUDFImpl, Signature, Statement, Volatility,
+ col, logical_plan::LogicalPlan, test::function_stub::sum_udaf, ColumnarValue,
+ CreateIndex, DdlStatement, ScalarFunctionArgs, ScalarUDF, ScalarUDFImpl, Signature,
+ Volatility,
};
use datafusion_functions::{string, unicode};
use datafusion_sql::{
@@ -3388,17 +3383,6 @@ fn ident_normalization_parser_options_ident_normalization() -> ParserOptions {
}
}
-fn generate_prepare_stmt_and_data_types(sql: &str) -> (LogicalPlan, String) {
- let plan = logical_plan(sql).unwrap();
- let data_types = match &plan {
- LogicalPlan::Statement(Statement::Prepare(Prepare { data_types, .. })) => {
- format!("{data_types:?}")
- }
- _ => panic!("Expected a Prepare statement"),
- };
- (plan, data_types)
-}
-
#[test]
fn select_partially_qualified_column() {
let sql = "SELECT person.first_name FROM public.person";
@@ -4330,936 +4314,6 @@ Projection: p1.id, p1.age, p2.id
);
}
-#[test]
-fn test_prepare_statement_to_plan_panic_param_format() {
- // param is not number following the $ sign
- // panic due to error returned from the parser
- let sql = "PREPARE my_plan(INT) AS SELECT id, age FROM person WHERE age = $foo";
-
- assert_snapshot!(
- logical_plan(sql).unwrap_err().strip_backtrace(),
- @r###"
- Error during planning: Invalid placeholder, not a number: $foo
- "###
- );
-}
-
-#[test]
-fn test_prepare_statement_to_plan_panic_param_zero() {
- // param is zero following the $ sign
- // panic due to error returned from the parser
- let sql = "PREPARE my_plan(INT) AS SELECT id, age FROM person WHERE age = $0";
-
- assert_snapshot!(
- logical_plan(sql).unwrap_err().strip_backtrace(),
- @r###"
- Error during planning: Invalid placeholder, zero is not a valid index: $0
- "###
- );
-}
-
-#[test]
-fn test_prepare_statement_to_plan_panic_prepare_wrong_syntax() {
- // param is not number following the $ sign
- // panic due to error returned from the parser
- let sql = "PREPARE AS SELECT id, age FROM person WHERE age = $foo";
- assert!(logical_plan(sql)
- .unwrap_err()
- .strip_backtrace()
- .contains("Expected: AS, found: SELECT"))
-}
-
-#[test]
-fn test_prepare_statement_to_plan_panic_no_relation_and_constant_param() {
- let sql = "PREPARE my_plan(INT) AS SELECT id + $1";
-
- let plan = logical_plan(sql).unwrap_err().strip_backtrace();
- assert_snapshot!(
- plan,
- @r"Schema error: No field named id."
- );
-}
-
-#[test]
-fn test_prepare_statement_should_infer_types() {
- // only provide 1 data type while using 2 params
- let sql = "PREPARE my_plan(INT) AS SELECT 1 + $1 + $2";
- let plan = logical_plan(sql).unwrap();
- let actual_types = plan.get_parameter_types().unwrap();
- let expected_types = HashMap::from([
- ("$1".to_string(), Some(DataType::Int32)),
- ("$2".to_string(), Some(DataType::Int64)),
- ]);
- assert_eq!(actual_types, expected_types);
-}
-
-#[test]
-fn test_non_prepare_statement_should_infer_types() {
- // Non prepared statements (like SELECT) should also have their parameter types inferred
- let sql = "SELECT 1 + $1";
- let plan = logical_plan(sql).unwrap();
- let actual_types = plan.get_parameter_types().unwrap();
- let expected_types = HashMap::from([
- // constant 1 is inferred to be int64
- ("$1".to_string(), Some(DataType::Int64)),
- ]);
- assert_eq!(actual_types, expected_types);
-}
-
-#[test]
-#[should_panic(
- expected = "Expected: [NOT] NULL | TRUE | FALSE | DISTINCT | [form] NORMALIZED FROM after IS, found: $1"
-)]
-fn test_prepare_statement_to_plan_panic_is_param() {
- let sql = "PREPARE my_plan(INT) AS SELECT id, age FROM person WHERE age is $1";
- logical_plan(sql).unwrap();
-}
-
-#[test]
-fn test_prepare_statement_to_plan_no_param() {
- // no embedded parameter but still declare it
- let sql = "PREPARE my_plan(INT) AS SELECT id, age FROM person WHERE age = 10";
- let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
- assert_snapshot!(
- plan,
- @r#"
- Prepare: "my_plan" [Int32]
- Projection: person.id, person.age
- Filter: person.age = Int64(10)
- TableScan: person
- "#
- );
- assert_snapshot!(dt, @r#"[Int32]"#);
-
- ///////////////////
- // replace params with values
- let param_values = vec![ScalarValue::Int32(Some(10))];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
- assert_snapshot!(
- plan_with_params,
- @r"
- Projection: person.id, person.age
- Filter: person.age = Int64(10)
- TableScan: person
- "
- );
-
- //////////////////////////////////////////
- // no embedded parameter and no declare it
- let sql = "PREPARE my_plan AS SELECT id, age FROM person WHERE age = 10";
- let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
- assert_snapshot!(
- plan,
- @r#"
- Prepare: "my_plan" []
- Projection: person.id, person.age
- Filter: person.age = Int64(10)
- TableScan: person
- "#
- );
- assert_snapshot!(dt, @r#"[]"#);
-
- ///////////////////
- // replace params with values
- let param_values: Vec<ScalarValue> = vec![];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
- assert_snapshot!(
- plan_with_params,
- @r"
- Projection: person.id, person.age
- Filter: person.age = Int64(10)
- TableScan: person
- "
- );
-}
-
-#[test]
-fn test_prepare_statement_to_plan_one_param_no_value_panic() {
- // no embedded parameter but still declare it
- let sql = "PREPARE my_plan(INT) AS SELECT id, age FROM person WHERE age = 10";
- let plan = logical_plan(sql).unwrap();
- // declare 1 param but provide 0
- let param_values: Vec<ScalarValue> = vec![];
-
- assert_snapshot!(
- plan.with_param_values(param_values)
- .unwrap_err()
- .strip_backtrace(),
- @r###"
- Error during planning: Expected 1 parameters, got 0
- "###);
-}
-
-#[test]
-fn test_prepare_statement_to_plan_one_param_one_value_different_type_panic() {
- // no embedded parameter but still declare it
- let sql = "PREPARE my_plan(INT) AS SELECT id, age FROM person WHERE age = 10";
- let plan = logical_plan(sql).unwrap();
- // declare 1 param but provide 0
- let param_values = vec![ScalarValue::Float64(Some(20.0))];
-
- assert_snapshot!(
- plan.with_param_values(param_values)
- .unwrap_err()
- .strip_backtrace(),
- @r###"
- Error during planning: Expected parameter of type Int32, got Float64 at index 0
- "###
- );
-}
-
-#[test]
-fn test_prepare_statement_to_plan_no_param_on_value_panic() {
- // no embedded parameter but still declare it
- let sql = "PREPARE my_plan AS SELECT id, age FROM person WHERE age = 10";
- let plan = logical_plan(sql).unwrap();
- // declare 1 param but provide 0
- let param_values = vec![ScalarValue::Int32(Some(10))];
-
- assert_snapshot!(
- plan.with_param_values(param_values)
- .unwrap_err()
- .strip_backtrace(),
- @r###"
- Error during planning: Expected 0 parameters, got 1
- "###
- );
-}
-
-#[test]
-fn test_prepare_statement_to_plan_params_as_constants() {
- let sql = "PREPARE my_plan(INT) AS SELECT $1";
- let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
- assert_snapshot!(
- plan,
- @r#"
- Prepare: "my_plan" [Int32]
- Projection: $1
- EmptyRelation
- "#
- );
- assert_snapshot!(dt, @r#"[Int32]"#);
-
- ///////////////////
- // replace params with values
- let param_values = vec![ScalarValue::Int32(Some(10))];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
- assert_snapshot!(
- plan_with_params,
- @r"
- Projection: Int32(10) AS $1
- EmptyRelation
- "
- );
-
- ///////////////////////////////////////
- let sql = "PREPARE my_plan(INT) AS SELECT 1 + $1";
- let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
- assert_snapshot!(
- plan,
- @r#"
- Prepare: "my_plan" [Int32]
- Projection: Int64(1) + $1
- EmptyRelation
- "#
- );
- assert_snapshot!(dt, @r#"[Int32]"#);
-
- ///////////////////
- // replace params with values
- let param_values = vec![ScalarValue::Int32(Some(10))];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
- assert_snapshot!(
- plan_with_params,
- @r"
- Projection: Int64(1) + Int32(10) AS Int64(1) + $1
- EmptyRelation
- "
- );
-
- ///////////////////////////////////////
- let sql = "PREPARE my_plan(INT, DOUBLE) AS SELECT 1 + $1 + $2";
- let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
- assert_snapshot!(
- plan,
- @r#"
- Prepare: "my_plan" [Int32, Float64]
- Projection: Int64(1) + $1 + $2
- EmptyRelation
- "#
- );
- assert_snapshot!(dt, @r#"[Int32, Float64]"#);
-
- ///////////////////
- // replace params with values
- let param_values = vec![
- ScalarValue::Int32(Some(10)),
- ScalarValue::Float64(Some(10.0)),
- ];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
- assert_snapshot!(
- plan_with_params,
- @r"
- Projection: Int64(1) + Int32(10) + Float64(10) AS Int64(1) + $1 + $2
- EmptyRelation
- "
- );
-}
-
-#[test]
-fn test_infer_types_from_join() {
- let sql =
- "SELECT id, order_id FROM person JOIN orders ON id = customer_id and age = $1";
-
- let plan = logical_plan(sql).unwrap();
- assert_snapshot!(
- plan,
- @r#"
- Projection: person.id, orders.order_id
- Inner Join: Filter: person.id = orders.customer_id AND person.age = $1
- TableScan: person
- TableScan: orders
- "#
- );
-
- let actual_types = plan.get_parameter_types().unwrap();
- let expected_types = HashMap::from([("$1".to_string(), Some(DataType::Int32))]);
- assert_eq!(actual_types, expected_types);
-
- // replace params with values
- let param_values = vec![ScalarValue::Int32(Some(10))];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
-
- assert_snapshot!(
- plan_with_params,
- @r"
- Projection: person.id, orders.order_id
- Inner Join: Filter: person.id = orders.customer_id AND person.age = Int32(10)
- TableScan: person
- TableScan: orders
- "
- );
-}
-
-#[test]
-fn test_prepare_statement_infer_types_from_join() {
- let sql =
- "PREPARE my_plan AS SELECT id, order_id FROM person JOIN orders ON id = customer_id and age = $1";
-
- let plan = logical_plan(sql).unwrap();
- assert_snapshot!(
- plan,
- @r#"
- Prepare: "my_plan" [Int32]
- Projection: person.id, orders.order_id
- Inner Join: Filter: person.id = orders.customer_id AND person.age = $1
- TableScan: person
- TableScan: orders
- "#
- );
-
- let actual_types = plan.get_parameter_types().unwrap();
- let expected_types = HashMap::from([("$1".to_string(), Some(DataType::Int32))]);
- assert_eq!(actual_types, expected_types);
-
- // replace params with values
- let param_values = vec![ScalarValue::Int32(Some(10))];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
-
- assert_snapshot!(
- plan_with_params,
- @r"
- Projection: person.id, orders.order_id
- Inner Join: Filter: person.id = orders.customer_id AND person.age = Int32(10)
- TableScan: person
- TableScan: orders
- "
- );
-}
-
-#[test]
-fn test_infer_types_from_predicate() {
- let sql = "SELECT id, age FROM person WHERE age = $1";
- let plan = logical_plan(sql).unwrap();
- assert_snapshot!(
- plan,
- @r#"
- Projection: person.id, person.age
- Filter: person.age = $1
- TableScan: person
- "#
- );
-
- let actual_types = plan.get_parameter_types().unwrap();
- let expected_types = HashMap::from([("$1".to_string(), Some(DataType::Int32))]);
- assert_eq!(actual_types, expected_types);
-
- // replace params with values
- let param_values = vec![ScalarValue::Int32(Some(10))];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
-
- assert_snapshot!(
- plan_with_params,
- @r"
- Projection: person.id, person.age
- Filter: person.age = Int32(10)
- TableScan: person
- "
- );
-}
-
-#[test]
-fn test_prepare_statement_infer_types_from_predicate() {
- let sql = "PREPARE my_plan AS SELECT id, age FROM person WHERE age = $1";
- let plan = logical_plan(sql).unwrap();
- assert_snapshot!(
- plan,
- @r#"
- Prepare: "my_plan" [Int32]
- Projection: person.id, person.age
- Filter: person.age = $1
- TableScan: person
- "#
- );
-
- let actual_types = plan.get_parameter_types().unwrap();
- let expected_types = HashMap::from([("$1".to_string(), Some(DataType::Int32))]);
- assert_eq!(actual_types, expected_types);
-
- // replace params with values
- let param_values = vec![ScalarValue::Int32(Some(10))];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
-
- assert_snapshot!(
- plan_with_params,
- @r"
- Projection: person.id, person.age
- Filter: person.age = Int32(10)
- TableScan: person
- "
- );
-}
-
-#[test]
-fn test_infer_types_from_between_predicate() {
- let sql = "SELECT id, age FROM person WHERE age BETWEEN $1 AND $2";
-
- let plan = logical_plan(sql).unwrap();
- assert_snapshot!(
- plan,
- @r#"
- Projection: person.id, person.age
- Filter: person.age BETWEEN $1 AND $2
- TableScan: person
- "#
- );
-
- let actual_types = plan.get_parameter_types().unwrap();
- let expected_types = HashMap::from([
- ("$1".to_string(), Some(DataType::Int32)),
- ("$2".to_string(), Some(DataType::Int32)),
- ]);
- assert_eq!(actual_types, expected_types);
-
- // replace params with values
- let param_values = vec![ScalarValue::Int32(Some(10)), ScalarValue::Int32(Some(30))];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
-
- assert_snapshot!(
- plan_with_params,
- @r"
- Projection: person.id, person.age
- Filter: person.age BETWEEN Int32(10) AND Int32(30)
- TableScan: person
- "
- );
-}
-
-#[test]
-fn test_prepare_statement_infer_types_from_between_predicate() {
- let sql = "PREPARE my_plan AS SELECT id, age FROM person WHERE age BETWEEN $1 AND $2";
-
- let plan = logical_plan(sql).unwrap();
- assert_snapshot!(
- plan,
- @r#"
- Prepare: "my_plan" [Int32, Int32]
- Projection: person.id, person.age
- Filter: person.age BETWEEN $1 AND $2
- TableScan: person
- "#
- );
-
- let actual_types = plan.get_parameter_types().unwrap();
- let expected_types = HashMap::from([
- ("$1".to_string(), Some(DataType::Int32)),
- ("$2".to_string(), Some(DataType::Int32)),
- ]);
- assert_eq!(actual_types, expected_types);
-
- // replace params with values
- let param_values = vec![ScalarValue::Int32(Some(10)), ScalarValue::Int32(Some(30))];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
-
- assert_snapshot!(
- plan_with_params,
- @r"
- Projection: person.id, person.age
- Filter: person.age BETWEEN Int32(10) AND Int32(30)
- TableScan: person
- "
- );
-}
-
-#[test]
-fn test_infer_types_subquery() {
- let sql = "SELECT id, age FROM person WHERE age = (select max(age) from person where id = $1)";
-
- let plan = logical_plan(sql).unwrap();
- assert_snapshot!(
- plan,
- @r#"
- Projection: person.id, person.age
- Filter: person.age = (<subquery>)
- Subquery:
- Projection: max(person.age)
- Aggregate: groupBy=[[]], aggr=[[max(person.age)]]
- Filter: person.id = $1
- TableScan: person
- TableScan: person
- "#
- );
-
- let actual_types = plan.get_parameter_types().unwrap();
- let expected_types = HashMap::from([("$1".to_string(), Some(DataType::UInt32))]);
- assert_eq!(actual_types, expected_types);
-
- // replace params with values
- let param_values = vec![ScalarValue::UInt32(Some(10))];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
-
- assert_snapshot!(
- plan_with_params,
- @r"
- Projection: person.id, person.age
- Filter: person.age = (<subquery>)
- Subquery:
- Projection: max(person.age)
- Aggregate: groupBy=[[]], aggr=[[max(person.age)]]
- Filter: person.id = UInt32(10)
- TableScan: person
- TableScan: person
- "
- );
-}
-
-#[test]
-fn test_prepare_statement_infer_types_subquery() {
- let sql = "PREPARE my_plan AS SELECT id, age FROM person WHERE age = (select max(age) from person where id = $1)";
-
- let plan = logical_plan(sql).unwrap();
- assert_snapshot!(
- plan,
- @r#"
- Prepare: "my_plan" [UInt32]
- Projection: person.id, person.age
- Filter: person.age = (<subquery>)
- Subquery:
- Projection: max(person.age)
- Aggregate: groupBy=[[]], aggr=[[max(person.age)]]
- Filter: person.id = $1
- TableScan: person
- TableScan: person
- "#
- );
-
- let actual_types = plan.get_parameter_types().unwrap();
- let expected_types = HashMap::from([("$1".to_string(), Some(DataType::UInt32))]);
- assert_eq!(actual_types, expected_types);
-
- // replace params with values
- let param_values = vec![ScalarValue::UInt32(Some(10))];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
-
- assert_snapshot!(
- plan_with_params,
- @r"
- Projection: person.id, person.age
- Filter: person.age = (<subquery>)
- Subquery:
- Projection: max(person.age)
- Aggregate: groupBy=[[]], aggr=[[max(person.age)]]
- Filter: person.id = UInt32(10)
- TableScan: person
- TableScan: person
- "
- );
-}
-
-#[test]
-fn test_update_infer() {
- let sql = "update person set age=$1 where id=$2";
-
- let plan = logical_plan(sql).unwrap();
- assert_snapshot!(
- plan,
- @r#"
- Dml: op=[Update] table=[person]
- Projection: person.id AS id, person.first_name AS first_name, person.last_name AS last_name, $1 AS age, person.state AS state, person.salary AS salary, person.birth_date AS birth_date, person.😀 AS 😀
- Filter: person.id = $2
- TableScan: person
- "#
- );
-
- let actual_types = plan.get_parameter_types().unwrap();
- let expected_types = HashMap::from([
- ("$1".to_string(), Some(DataType::Int32)),
- ("$2".to_string(), Some(DataType::UInt32)),
- ]);
- assert_eq!(actual_types, expected_types);
-
- // replace params with values
- let param_values = vec![ScalarValue::Int32(Some(42)), ScalarValue::UInt32(Some(1))];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
-
- assert_snapshot!(
- plan_with_params,
- @r"
- Dml: op=[Update] table=[person]
- Projection: person.id AS id, person.first_name AS first_name, person.last_name AS last_name, Int32(42) AS age, person.state AS state, person.salary AS salary, person.birth_date AS birth_date, person.😀 AS 😀
- Filter: person.id = UInt32(1)
- TableScan: person
- "
- );
-}
-
-#[test]
-fn test_prepare_statement_update_infer() {
- let sql = "PREPARE my_plan AS update person set age=$1 where id=$2";
-
- let plan = logical_plan(sql).unwrap();
- assert_snapshot!(
- plan,
- @r#"
- Prepare: "my_plan" [Int32, UInt32]
- Dml: op=[Update] table=[person]
- Projection: person.id AS id, person.first_name AS first_name, person.last_name AS last_name, $1 AS age, person.state AS state, person.salary AS salary, person.birth_date AS birth_date, person.😀 AS 😀
- Filter: person.id = $2
- TableScan: person
- "#
- );
-
- let actual_types = plan.get_parameter_types().unwrap();
- let expected_types = HashMap::from([
- ("$1".to_string(), Some(DataType::Int32)),
- ("$2".to_string(), Some(DataType::UInt32)),
- ]);
- assert_eq!(actual_types, expected_types);
-
- // replace params with values
- let param_values = vec![ScalarValue::Int32(Some(42)), ScalarValue::UInt32(Some(1))];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
-
- assert_snapshot!(
- plan_with_params,
- @r"
- Dml: op=[Update] table=[person]
- Projection: person.id AS id, person.first_name AS first_name, person.last_name AS last_name, Int32(42) AS age, person.state AS state, person.salary AS salary, person.birth_date AS birth_date, person.😀 AS 😀
- Filter: person.id = UInt32(1)
- TableScan: person
- "
- );
-}
-
-#[test]
-fn test_insert_infer() {
- let sql = "insert into person (id, first_name, last_name) values ($1, $2, $3)";
- let plan = logical_plan(sql).unwrap();
- assert_snapshot!(
- plan,
- @r#"
- Dml: op=[Insert Into] table=[person]
- Projection: column1 AS id, column2 AS first_name, column3 AS last_name, CAST(NULL AS Int32) AS age, CAST(NULL AS Utf8) AS state, CAST(NULL AS Float64) AS salary, CAST(NULL AS Timestamp(Nanosecond, None)) AS birth_date, CAST(NULL AS Int32) AS 😀
- Values: ($1, $2, $3)
- "#
- );
-
- let actual_types = plan.get_parameter_types().unwrap();
- let expected_types = HashMap::from([
- ("$1".to_string(), Some(DataType::UInt32)),
- ("$2".to_string(), Some(DataType::Utf8)),
- ("$3".to_string(), Some(DataType::Utf8)),
- ]);
- assert_eq!(actual_types, expected_types);
-
- // replace params with values
- let param_values = vec![
- ScalarValue::UInt32(Some(1)),
- ScalarValue::from("Alan"),
- ScalarValue::from("Turing"),
- ];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
- assert_snapshot!(
- plan_with_params,
- @r#"
- Dml: op=[Insert Into] table=[person]
- Projection: column1 AS id, column2 AS first_name, column3 AS last_name, CAST(NULL AS Int32) AS age, CAST(NULL AS Utf8) AS state, CAST(NULL AS Float64) AS salary, CAST(NULL AS Timestamp(Nanosecond, None)) AS birth_date, CAST(NULL AS Int32) AS 😀
- Values: (UInt32(1) AS $1, Utf8("Alan") AS $2, Utf8("Turing") AS $3)
- "#
- );
-}
-
-#[test]
-fn test_prepare_statement_insert_infer() {
- let sql = "PREPARE my_plan AS insert into person (id, first_name, last_name) values ($1, $2, $3)";
- let plan = logical_plan(sql).unwrap();
- assert_snapshot!(
- plan,
- @r#"
- Prepare: "my_plan" [UInt32, Utf8, Utf8]
- Dml: op=[Insert Into] table=[person]
- Projection: column1 AS id, column2 AS first_name, column3 AS last_name, CAST(NULL AS Int32) AS age, CAST(NULL AS Utf8) AS state, CAST(NULL AS Float64) AS salary, CAST(NULL AS Timestamp(Nanosecond, None)) AS birth_date, CAST(NULL AS Int32) AS 😀
- Values: ($1, $2, $3)
- "#
- );
-
- let actual_types = plan.get_parameter_types().unwrap();
- let expected_types = HashMap::from([
- ("$1".to_string(), Some(DataType::UInt32)),
- ("$2".to_string(), Some(DataType::Utf8)),
- ("$3".to_string(), Some(DataType::Utf8)),
- ]);
- assert_eq!(actual_types, expected_types);
-
- // replace params with values
- let param_values = vec![
- ScalarValue::UInt32(Some(1)),
- ScalarValue::from("Alan"),
- ScalarValue::from("Turing"),
- ];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
- assert_snapshot!(
- plan_with_params,
- @r#"
- Dml: op=[Insert Into] table=[person]
- Projection: column1 AS id, column2 AS first_name, column3 AS last_name, CAST(NULL AS Int32) AS age, CAST(NULL AS Utf8) AS state, CAST(NULL AS Float64) AS salary, CAST(NULL AS Timestamp(Nanosecond, None)) AS birth_date, CAST(NULL AS Int32) AS 😀
- Values: (UInt32(1) AS $1, Utf8("Alan") AS $2, Utf8("Turing") AS $3)
- "#
- );
-}
-
-#[test]
-fn test_prepare_statement_to_plan_one_param() {
- let sql = "PREPARE my_plan(INT) AS SELECT id, age FROM person WHERE age = $1";
- let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
- assert_snapshot!(
- plan,
- @r#"
- Prepare: "my_plan" [Int32]
- Projection: person.id, person.age
- Filter: person.age = $1
- TableScan: person
- "#
- );
- assert_snapshot!(dt, @r#"[Int32]"#);
-
- ///////////////////
- // replace params with values
- let param_values = vec![ScalarValue::Int32(Some(10))];
-
- let plan_with_params = plan.with_param_values(param_values).unwrap();
- assert_snapshot!(
- plan_with_params,
- @r"
- Projection: person.id, person.age
- Filter: person.age = Int32(10)
- TableScan: person
- "
- );
-}
-
-#[test]
-fn test_prepare_statement_to_plan_data_type() {
- let sql = "PREPARE my_plan(DOUBLE) AS SELECT id, age FROM person WHERE age = $1";
-
- let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
- assert_snapshot!(
- plan,
- // age is defined as Int32 but prepare statement declares it as DOUBLE/Float64
- // Prepare statement and its logical plan should be created successfully
- @r#"
- Prepare: "my_plan" [Float64]
- Projection: person.id, person.age
- Filter: person.age = $1
- TableScan: person
- "#
- );
- assert_snapshot!(dt, @r#"[Float64]"#);
-
- ///////////////////
- // replace params with values still succeed and use Float64
- let param_values = vec![ScalarValue::Float64(Some(10.0))];
-
- let plan_with_params = plan.with_param_values(param_values).unwrap();
- assert_snapshot!(
- plan_with_params,
- @r"
- Projection: person.id, person.age
- Filter: person.age = Float64(10)
- TableScan: person
- "
- );
-}
-
-#[test]
-fn test_prepare_statement_to_plan_multi_params() {
- let sql = "PREPARE my_plan(INT, STRING, DOUBLE, INT, DOUBLE, STRING) AS
- SELECT id, age, $6
- FROM person
- WHERE age IN ($1, $4) AND salary > $3 and salary < $5 OR first_name < $2";
- let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
- assert_snapshot!(
- plan,
- @r#"
- Prepare: "my_plan" [Int32, Utf8, Float64, Int32, Float64, Utf8]
- Projection: person.id, person.age, $6
- Filter: person.age IN ([$1, $4]) AND person.salary > $3 AND person.salary < $5 OR person.first_name < $2
- TableScan: person
- "#
- );
- assert_snapshot!(dt, @r#"[Int32, Utf8, Float64, Int32, Float64, Utf8]"#);
-
- ///////////////////
- // replace params with values
- let param_values = vec![
- ScalarValue::Int32(Some(10)),
- ScalarValue::from("abc"),
- ScalarValue::Float64(Some(100.0)),
- ScalarValue::Int32(Some(20)),
- ScalarValue::Float64(Some(200.0)),
- ScalarValue::from("xyz"),
- ];
-
- let plan_with_params = plan.with_param_values(param_values).unwrap();
- assert_snapshot!(
- plan_with_params,
- @r#"
- Projection: person.id, person.age, Utf8("xyz") AS $6
- Filter: person.age IN ([Int32(10), Int32(20)]) AND person.salary > Float64(100) AND person.salary < Float64(200) OR person.first_name < Utf8("abc")
- TableScan: person
- "#
- );
-}
-
-#[test]
-fn test_prepare_statement_to_plan_having() {
- let sql = "PREPARE my_plan(INT, DOUBLE, DOUBLE, DOUBLE) AS
- SELECT id, sum(age)
- FROM person \
- WHERE salary > $2
- GROUP BY id
- HAVING sum(age) < $1 AND sum(age) > 10 OR sum(age) in ($3, $4)\
- ";
- let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
- assert_snapshot!(
- plan,
- @r#"
- Prepare: "my_plan" [Int32, Float64, Float64, Float64]
- Projection: person.id, sum(person.age)
- Filter: sum(person.age) < $1 AND sum(person.age) > Int64(10) OR sum(person.age) IN ([$3, $4])
- Aggregate: groupBy=[[person.id]], aggr=[[sum(person.age)]]
- Filter: person.salary > $2
- TableScan: person
- "#
- );
- assert_snapshot!(dt, @r#"[Int32, Float64, Float64, Float64]"#);
-
- ///////////////////
- // replace params with values
- let param_values = vec![
- ScalarValue::Int32(Some(10)),
- ScalarValue::Float64(Some(100.0)),
- ScalarValue::Float64(Some(200.0)),
- ScalarValue::Float64(Some(300.0)),
- ];
-
- let plan_with_params = plan.with_param_values(param_values).unwrap();
- assert_snapshot!(
- plan_with_params,
- @r#"
- Projection: person.id, sum(person.age)
- Filter: sum(person.age) < Int32(10) AND sum(person.age) > Int64(10) OR sum(person.age) IN ([Float64(200), Float64(300)])
- Aggregate: groupBy=[[person.id]], aggr=[[sum(person.age)]]
- Filter: person.salary > Float64(100)
- TableScan: person
- "#
- );
-}
-
-#[test]
-fn test_prepare_statement_to_plan_limit() {
- let sql = "PREPARE my_plan(BIGINT, BIGINT) AS
- SELECT id FROM person \
- OFFSET $1 LIMIT $2";
- let (plan, dt) = generate_prepare_stmt_and_data_types(sql);
- assert_snapshot!(
- plan,
- @r#"
- Prepare: "my_plan" [Int64, Int64]
- Limit: skip=$1, fetch=$2
- Projection: person.id
- TableScan: person
- "#
- );
- assert_snapshot!(dt, @r#"[Int64, Int64]"#);
-
- // replace params with values
- let param_values = vec![ScalarValue::Int64(Some(10)), ScalarValue::Int64(Some(200))];
- let plan_with_params = plan.with_param_values(param_values).unwrap();
- assert_snapshot!(
- plan_with_params,
- @r#"
- Limit: skip=10, fetch=200
- Projection: person.id
- TableScan: person
- "#
- );
-}
-
-#[test]
-fn test_prepare_statement_unknown_list_param() {
- let sql = "SELECT id from person where id = $2";
- let plan = logical_plan(sql).unwrap();
- let param_values = ParamValues::List(vec![]);
- let err = plan.replace_params_with_values(¶m_values).unwrap_err();
- assert_contains!(
- err.to_string(),
- "Error during planning: No value found for placeholder with id $2"
- );
-}
-
-#[test]
-fn test_prepare_statement_unknown_hash_param() {
- let sql = "SELECT id from person where id = $bar";
- let plan = logical_plan(sql).unwrap();
- let param_values = ParamValues::Map(HashMap::new());
- let err = plan.replace_params_with_values(¶m_values).unwrap_err();
- assert_contains!(
- err.to_string(),
- "Error during planning: No value found for placeholder with name $bar"
- );
-}
-
-#[test]
-fn test_prepare_statement_bad_list_idx() {
- let sql = "SELECT id from person where id = $foo";
- let plan = logical_plan(sql).unwrap();
- let param_values = ParamValues::List(vec![]);
-
- let err = plan.replace_params_with_values(¶m_values).unwrap_err();
- assert_contains!(err.to_string(), "Error during planning: Failed to parse placeholder id: invalid digit found in string");
-}
-
#[test]
fn test_inner_join_with_cast_key() {
let sql = "SELECT person.id, person.age
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org