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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 于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
>>
