Michael’s suggestion of a custom EnumerableFilter is a good one. Another way is
to use the logical relational algebra as a data structure. You can get it in
JSON from the EXPLAIN PLAN command, and it is available in many other formats
(XML, Java objects).
Julian
$ ./sqlline
Building Apache Calcite 1.36.0-SNAPSHOT
sqlline version 1.12.0
sqlline> !connect jdbc:calcite:model=core/src/test/resources/hsqldb-model.json
sa sa
sqlline> select * from emp where deptno = 20 and job = 'MANAGER';
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | null | 20 |
+-------+-------+---------+------+------------+---------+------+--------+
1 row selected (0.674 seconds)
sqlline> !set outputFormat csv
sqlline> explain plan without implementation as json for
. . . .> select * from emp where deptno = 20 and job = 'MANAGER';
'PLAN'
'{
"rels": [
{ "id": "0", "relOp": "JdbcTableScan",
"table": [ "SCOTT", "EMP" ],
"inputs": []
},
{
"id": "1",
"relOp": "LogicalFilter",
"condition": {
"op": { "name": "AND", "kind": "AND", "syntax": "BINARY" },
"operands": [
{
"op": { "name": "=", "kind": "EQUALS", "syntax": "BINARY" },
"operands": [
{
"op": { "name": "CAST", "kind": "CAST", "syntax": "SPECIAL" },
"operands": [
{ "input": 7, "name": "$7" }
],
"type": { "type": "INTEGER", "nullable": true }
},
{
"literal": 20,
"type": { "type": "INTEGER", "nullable": false }
}
]
},
{
"op": { "name": "=", "kind": "EQUALS", "syntax": "BINARY" },
"operands": [
{ "input": 2, "name": "$2" },
{
"literal": "MANAGER",
"type": { "type": "VARCHAR", "nullable": false, "precision": 9 }
}
]
}
]
}
},
{
"id": "2",
"relOp": "LogicalProject",
"fields": [ "EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM",
"DEPTNO" ],
"exprs": [
{ "input": 0, "name": "$0" },
{ "input": 1, "name": "$1" },
{ "input": 2, "name": "$2" },
{ "input": 3, "name": "$3" },
{ "input": 4, "name": "$4" },
{ "input": 5, "name": "$5" },
{ "input": 6, "name": "$6" },
{ "input": 7, "name": "$7" }
]
}
]
}'
1 row selected (0.005 seconds)
> On Sep 26, 2023, at 12:22 PM, Michael Mior <[email protected]> wrote:
>
> Keep in mind that for general queries, this is a very non-trivial problem
> and an active area of research. However, doing this for the case of a
> filter applied to a single table should be much more straightforward. One
> way to do this would be to create your own subclass of EnumerableFilter
> with its own convention and then choose your rules carefully (along with a
> copy of EnumerableFilterRule for your new convention).
>
> You would then implement whatever logging logic you need inside
> EnumerableFilter. It's still a nontrivial amount of code, but definitely
> doable. Others might have suggestions on an easier way to implement this.
>
> --
> Michael Mior
> [email protected]
>
>
> On Tue, Sep 26, 2023 at 2:07 PM Luis Brassara <[email protected]>
> wrote:
>
>> Hi, all.
>>
>> I'm using Apache Calcite with SQL.
>>
>> I'm trying to execute an SQL query like
>>
>> SELECT * FROM table WHERE some_field = 1 AND other_field = 5)
>>
>> where table is:
>>
>> some_field | other field
>> ------------------------
>> row 1: 1 | 2
>> row 2: 1 | 3
>>
>>
>> This query will return empty results.
>>
>> I want to tell the user why the query returned empty results, row by row.
>> Then, I want to be able to access the query plan and transverse it to the
>> node that produced the empty results (other_field = 5) and I want to have
>> in context the current value of other_field (2) so I can print messages
>> like:
>>
>> Row 1 failed because `other_field` was `2` and `5` was expected
>> Row 2 failed because `other_field` was `3` and `5` was expected
>>
>> I'm not sure which Calcite classes should I use to achieve that, any advice
>> / example?
>>
>> Thanks in advance!
>>
