You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Luis Brassara <lu...@gmail.com> on 2023/09/26 17:57:58 UTC
Access to Calcite plan execution
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!
Re: Access to Calcite plan execution
Posted by Julian Hyde <jh...@gmail.com>.
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 <mm...@apache.org> 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
> mmior@apache.org
>
>
> On Tue, Sep 26, 2023 at 2:07 PM Luis Brassara <lu...@gmail.com>
> 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!
>>
Re: Access to Calcite plan execution
Posted by Michael Mior <mm...@apache.org>.
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
mmior@apache.org
On Tue, Sep 26, 2023 at 2:07 PM Luis Brassara <lu...@gmail.com>
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!
>