alamb commented on code in PR #8977:
URL: https://github.com/apache/arrow-datafusion/pull/8977#discussion_r1465244710
##########
datafusion/core/tests/sql/select.rs:
##########
@@ -572,6 +652,56 @@ async fn test_named_query_parameters() -> Result<()> {
Ok(())
}
+#[tokio::test]
+async fn test_parameter_type_coercion() -> Result<()> {
+ let ctx = SessionContext::new();
+ let signed_ints: Int32Array = vec![-1, 0, 1].into();
+ let unsigned_ints: UInt64Array = vec![1, 2, 3].into();
+ let batch = RecordBatch::try_from_iter(vec![
+ ("signed", Arc::new(signed_ints) as ArrayRef),
+ ("unsigned", Arc::new(unsigned_ints) as ArrayRef),
+ ])?;
+ ctx.register_batch("test", batch)?;
+ let results = ctx.sql("SELECT signed, unsigned FROM test WHERE $foo >=
signed AND signed <= $bar AND unsigned <= $baz AND unsigned = $str")
+ .await?
+ .with_param_values(vec![
+ ("foo", ScalarValue::from(1_u64)),
+ ("bar", ScalarValue::from(-1_i64)),
+ ("baz", ScalarValue::from(2_i32)),
+ ("str", ScalarValue::from("1")),
+ ])?
+ .collect().await?;
+ let expected = vec![
+ "+--------+----------+",
+ "| signed | unsigned |",
+ "+--------+----------+",
+ "| -1 | 1 |",
+ "+--------+----------+",
+ ];
+ assert_batches_sorted_eq!(expected, &results);
+ Ok(())
+}
+
+#[tokio::test]
+async fn test_parameter_invalid_types() -> Result<()> {
+ let ctx = SessionContext::new();
+ let signed_ints: Int32Array = vec![-1, 0, 1].into();
+ let unsigned_ints: UInt64Array = vec![1, 2, 3].into();
+ let batch = RecordBatch::try_from_iter(vec![
+ ("signed", Arc::new(signed_ints) as ArrayRef),
+ ("unsigned", Arc::new(unsigned_ints) as ArrayRef),
+ ])?;
+ ctx.register_batch("test", batch)?;
+ let results = ctx
+ .sql("SELECT signed, unsigned FROM test WHERE signed = $text")
+ .await?
+ .with_param_values(vec![("text", ScalarValue::from("not a number"))])?
+ .collect()
+ .await;
+ assert_eq!(results.unwrap_err().strip_backtrace(), "");
Review Comment:
🤔 I am also surprised this works, but apparently strings can be coerced to
numbers (`null` in this case):
```
❯ create table test (signed int) as values (-1), (0), (1);
0 rows in set. Query took 0.005 seconds.
❯ select * from test;
+--------+
| signed |
+--------+
| -1 |
| 0 |
| 1 |
+--------+
3 rows in set. Query took 0.001 seconds.
❯ select * from test where signed = 'not a number';
0 rows in set. Query took 0.003 seconds.
```
Maybe you can try something like this:
```sql
❯ create table test3 as values ([1,2,3]);
0 rows in set. Query took 0.002 seconds.
❯ select * from test3;
+-----------+
| column1 |
+-----------+
| [1, 2, 3] |
+-----------+
1 row in set. Query took 0.001 seconds.
❯ select * from test3 where column1 = 4;
Arrow error: Invalid argument error: Invalid comparison operation:
List(Field { name: "item", data_type: Int64, nullable: true, dict_id: 0,
dict_is_ordered: false, metadata: {} }) == List(Field { name: "item",
data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata:
{} })
```
Here is an example of creating a list array:
https://docs.rs/arrow/latest/arrow/array/type.ListArray.html#method.from_iter_primitive
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
