Repository navigation
in_list expression not working #6073
Description
Describe the bug
I am getting an error when i try to use the in_list expression: "Error during planning: Invalid function 'in_list'"
To Reproduce
I am running this query in SessionContext::sql btw
select in_list('word', list) from data
where list is a utf8 array
Expected behavior
to return 'true'
Activity
jiangzhx commentedon Apr 21, 2023
In_list did not registered as a UDF function.
the doc last updated by @izveigor.
hope @izveigor have time to help for check.
izveigor commentedon Apr 22, 2023
Hello, @bubbajoe.
The function
in_listis a fluent-style API using Rust code (For example:col("a").gt(lit(6)).and(col("b").lt(lit(7))))To use
in_listfunction in SQL check: https://arrow.apache.org/datafusion/user-guide/sql/subqueries.html#inbubbajoe commentedon Apr 22, 2023
@izveigor sSorry I am a bit confused, can you give an SQL example?
jiangzhx commentedon Apr 23, 2023
@bubbajoe, the document you linked is not for SQL functions. SQL scalar and aggregate functions can be found at https://arrow.apache.org/datafusion/user-guide/sql/scalar_functions.html#.
I believe we do not have an SQL function to check an expression is in an array.
Maybe you can give us some suggestions. If you have used similar functions in other engines, it could help us implement a similar functionality.
izveigor commentedon Apr 23, 2023
@bubbajoe, I don’t check but I think in your case you can use “IN”: “SELECT ‘word’ IN list;”
I hope it will solve your problem.
bubbajoe commentedon Apr 23, 2023
@jiangzhx Basically some of the functions and operators here:
https://www.postgresql.org/docs/9.2/functions-array.html
The most important being: array_length(anyarray), unnest(anyarray), and checking if an item is in an array.
Example: (
select * from any(items) = 'item')BTW this query seems to parse correctly in datafusion, i just get an Unsupported/implemented error. I would perfer this way instead of something like array_contains(anyarray, anyelement)
This is the error i get:
"This feature is not implemented: Unsupported ast node in sqltorel: AnyOp(Identifier(Ident { value: \"items\", quote_style: None }))"bubbajoe commentedon Apr 23, 2023
@izveigor Doesn't even parse the sql, it would need something like
unnestto make the array elements into rows to be able to process it as a subquery.bubbajoe commentedon Apr 25, 2023
#6075
bubbajoe commentedon Apr 25, 2023
Let me close this issue since in_list isn't here: https://arrow.apache.org/datafusion/user-guide/sql/scalar_functions.html
Thanks for the clarification!