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 <jhyde.apa...@gmail.com> 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<String> 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 <rube...@gmail.com> 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 <libenc...@apache.org> > 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 <benedek.hal...@teampicnic.com> 于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 > >> > >