Hey,
Firstly, thank you all for your help!
In the meantime, we found this stack overflow thread: [
https://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives/10240302#10240302
]
The interesting part is the second example: `SELECT my_column FROM my_table
WHERE search_column IN (SELECT * FROM unnest(?))`
We tried it, and Calcite threw an `Illegal use of dynamic parameter`
exception, based on the source code, this was because Calcite doesn't infer
type information in this case.
We tried, and succeeded with: `SELECT my_column FROM my_table WHERE
search_column IN (SELECT * FROM unnest(cast(? as VARCHAR ARRAY)))`
Do I see correctly that this somewhat aligns with the idea of treating the
“external” array as a table?
The generated code that this query got translated to, seems to use
`EnumerableDefaults.semiJoin` to combine a full scan on `my_table` with the
values of the dynamic param.
Does anyone see any (potential) problem with this workaround?
Probably relevant, this is how we executed the query:
- use JDBC to connect to Calcite.
- call `connection#prepareStatement` with the said query.
- set the array on the prepared statement with
`preparedStatement.setArray(index, connection.createArrayOf(
SqlType.VARCHAR.name(), collection.toArray()));`
---
> BTW, do you know any databases that have the ability as you described?
We personally didn't try this yet, but based on the same Stack Overflow
thread, it seems like Postgres / JDBC 4 may support this use-case via `=
ANY(?)`
We tried (with Calcite) using `= ANY (?)` too, but behaved very similarly
to `IN (?)` which currently is an equivalent of `= ?`.
This isn't too surprising based on Postgres' docs (
https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-ANY-SOME),
but still, might be interesting.
Thanks,
Ben
On Wed, 25 Oct 2023 at 22:18, Julian Hyde wrote:
> I have wanted this feature in SQL APIs for thirty years.
>
> I call it the ‘join to array’ problem. If you have a collection of
> employee ids, the best you can do is the following:
>
> void getEmployees(int[] empIds, Consumer nameConsumer) {
> PreparedStatement stmt =
> connection.prepareStatement(“select ename from emp where empno = ?”);
> for (int empNo in empNos) {
> stmt.setInt(1, empNo);
> ResultSet r = stmt.executeQuery();
> if (r.next()) {
> nameConsumer.accept(r.getString(1));
> }
> }
> }
>
> I always wanted to treat the “external” array as a table inside SQL:
>
> PreparedStatmenet stmt =
> connection.prepareStatement(“select ename\n”
> + "from emp cross join ? as empno\n”
> + "where emp.empno = empno.value")
>
> This wish to join to arrays, and remove the boundary between SQL and
> programming language data structures, let me to create the Saffron language
> [1] and later Morel [2].
>
> I think it would be nice to add support in Calcite for bind variables
> whose values are arrays. Quite how that would look in SQL, and to the JDBC
> client, I don’t know.
>
> Julian
>
> [1]
> https://swarm.workshop.perforce.com/view/guest/julian_hyde/saffron/doc/overview.html
> [2]
> http://blog.hydromatic.net/2020/02/25/morel-a-functional-language-for-data.html
>
>
> > On Oct 25, 2023, at 4:26 AM, Ruben Q L wrote:
> >
> > Benedek, AFAIK what you describe is simply not possible with Calcite
> > currently.
> >
> > Best,
> > Ruben
> >
> >
> > On Wed, Oct 25, 2023 at 12:22 PM Benchao Li
> wrote:
> >
> >> Benedek,
> >>
> >> Per my understanding, dynamic parameters should be some placeholders
> >> of literals, which means that each dynamic parameter can only be a
> >> single literal. Hence, your requirement cannot be achieved by this
> >> definition.
> >>
> >> BTW, do you know any databases that have the ability as you described?
> >>
> >> Benedek Halasi 于2023年10月25日周三 19:12写道：
> >>>
> >>> Hi,
> >>>
> >>> We're looking for a way to parse and execute queries with Apache
> Calcite
> >>> that contain an `IN (?)` expression, where the dynamic parameter is a
> set
> >>> of values.
> >>>
> >>> As we've observed, Calcite handles dynamic parameters as single values,
> >> and
> >>> we haven't found a way to make it handle them differently. This causes
> >> some
> >>> transformations to be flawed (e.g., `IN (?)` gets transformed to `=
> ?`).
> >>>
> >>> Has anyone encountered this? Is there a go-to solution?
> >>>
> >>> Thanks in advance,
> >>> Ben
> >>
> >>
> >>
> >> --
> >>
> >> Best,
> >> Benchao Li
> >>
>
>
