You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Luke Segars <lu...@lukesegars.com> on 2022/05/15 20:52:52 UTC
Questions for properly handling Postgres system functions
Hello,
I'm a relatively new Calcite user, and am working on configuring Calcite to
handle queries that include Postgres system functions
<https://www.postgresql.org/docs/current/functions-info.html>. I'm running
into three issues and wondering if anyone could help educate me or provide
examples:
1. Certain functions work correctly "out of the box", like current_user
and session_user, though I don't understand how they produce their results.
When I run SELECT current_user, the ResultSet contains a single row with
a value of "sa". This looks like it's expected in some Calcite tests
<https://github.com/apache/calcite/blob/master/babel/src/test/resources/sql/redshift.iq#L2112>,
but I'd like to modify this value to be something application-specific. Is
there some way to override the implementation with my own?
- For example, I'd like to be able to provide the logged in user's
username.
2. Certain functions appear to be parsed and validated correctly but run
into runtime exceptions, like current_schema. Here's the exception
<https://gist.github.com/anyweez/7ee86345c663e313e91c7842f3ffb70c> I am
getting when I evaluate "SELECT current_schema", but I don't understand
why an error is occurring for this function but not others, or how I go
about fixing it. I've tried implementing a ScalarFunction named
current_schema, but it doesn't change the outcome.
3. Certain functions may be niladic, like current_schema, but parsing
fails when the version with parentheses is used even when I'm using Babel
compliance level. My understanding is that using Babel conformance
<https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java#L280>
should allow parentheses to be optional but supported -- any idea how to
make "SELECT current_schema()" work like "SELECT current_schema" does?
- From the docs <https://calcite.apache.org/docs/reference.html>:
"Calls to niladic functions such as CURRENT_DATE do not accept
parentheses
in standard SQL. Calls with parentheses, such as CURRENT_DATE() are
accepted in certain conformance levels." Is there another step beyond
setting the conformance level?
Here's the core of the Calcite configuration I'm using, note that I'm
including the Postgres operator table and using Babel conformance level:
public class CustomQueryRunner {
// ... certain fields and methods excluded for brevity ...
private void configurePlanner() {
SqlParser.Config parserConfig = SqlParser.config()
.withConformance(SqlConformanceEnum.BABEL)
.withCaseSensitive(false);
// List of operators that we want to support (organized into
'operator tables')
SqlOperatorTable operatorTable = SqlOperatorTables.chain(List.of(
SqlStdOperatorTable.instance(),
SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
SqlLibrary.POSTGRESQL
)
));
FrameworkConfig fwc = Frameworks.newConfigBuilder()
.parserConfig(parserConfig)
.operatorTable(operatorTable)
.defaultSchema(this.rootSchema.getSubSchema("pg_catalog",
false).plus())
.build();
this.planner = Frameworks.getPlanner(fwc);
}
public SqlResultSet execute(String query) {
// Parse the query, identify syntax errors and throw SqlParseException
if
// any are found. Query is syntactically valid if no exception occurs.
SqlNode sqlNode = planner.parse(query);
// Semantically validate the query; this ensures that all references are
// to entities that exist (schemas, tables, functions, operators, etc).
If
// not, a ValidationException will be thrown.
SqlNode validated = planner.validate(sqlNode);
RelRoot relRoot = planner.rel(validated);
// Run the on the rootSchema and iterate over results
PreparedStatement run = RelRunners.run(relRoot.project());
ResultSet resultSet = run.executeQuery();
// ... method continues ...
}
}
Thanks in advance! I'm loving Calcite - thank you all for your efforts.
Re: Questions for properly handling Postgres system functions
Posted by Yanjing Wang <zh...@gmail.com>.
Hi Luke,
--Is there another step beyond setting the conformance level?
It seem there is no other approach except setting the conformance, see
[1][2].
you can try Frameworks.newConfigBuilder()
.sqlValidatorConfig(SqlValidator.Config.DEFAULT.withConformance(SqlConformanceEnum.LENIENT)).
[1]
https://github.com/apache/calcite/blob/f278efb0411ba29c7bd167f5d02e566bf542acb3/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java#L345
[2]
https://github.com/apache/calcite/blob/f278efb0411ba29c7bd167f5d02e566bf542acb3/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L5976
Stamatis Zampetakis <za...@gmail.com> 于2022年5月28日周六 06:03写道：
> Hi Luke,
>
> I am very happy to see more people using Calcite and in particular giving
> positive feedback about it. Thanks for doing this, I am sure others
> appreciate it as well.
>
> I don't understand 100% your use-case but I assume you are querying
> Postgres via the JDBC adapter of Calcite.
> When you are using the JDBC adapter usually there is a part of the query
> that runs on the underlying DBMS (Postgres in this case) and another part
> of the query that is executed in-memory using Calcite operators.
>
> Regarding the various functions you mentioned, CURRENT_USER,
> CURRENT_SCHEMA, etc, the result depends on where they are executed.
>
> If the function belongs to the part of the query that is pushed in the DBMS
> then it will return exactly the value that the DBMS would return in this
> case.
>
> If the function belongs to the part of the query that is executed in-memory
> then you have to check what Calcite does in this case.
> For example, when CURRENT_USER runs using Enumerable/Bindable operators
> then the function does a lookup in the DataContext [1] to return the
> appropriate value.
> Having said that, if you populate the data context with the desired value
> you can somewhat customize the output result.
>
> Check the plan of your queries to see which part runs in the database and
> which part runs in Calcite.
> By adding/removing rules in the planner or playing with SqlDialects [2] you
> can customize what is pushed in the database and what not.
>
> Best,
> Stamatis
>
> [1]
>
> https://github.com/apache/calcite/blob/4969b9690efe999c522daf1151c4a00a33be0eb0/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java#L2526
> [2]
>
> https://github.com/apache/calcite/blob/4969b9690efe999c522daf1151c4a00a33be0eb0/core/src/main/java/org/apache/calcite/sql/SqlDialect.java#L770
>
> On Mon, May 16, 2022 at 2:27 AM Luke Segars <lu...@lukesegars.com> wrote:
>
> > Hello,
> >
> > I'm a relatively new Calcite user, and am working on configuring Calcite
> to
> > handle queries that include Postgres system functions
> > <https://www.postgresql.org/docs/current/functions-info.html>. I'm
> running
> > into three issues and wondering if anyone could help educate me or
> provide
> > examples:
> >
> > 1. Certain functions work correctly "out of the box", like
> current_user
> > and session_user, though I don't understand how they produce their
> > results.
> > When I run SELECT current_user, the ResultSet contains a single row
> with
> > a value of "sa". This looks like it's expected in some Calcite tests
> > <
> >
> https://github.com/apache/calcite/blob/master/babel/src/test/resources/sql/redshift.iq#L2112
> > >,
> > but I'd like to modify this value to be something
> application-specific.
> > Is
> > there some way to override the implementation with my own?
> > - For example, I'd like to be able to provide the logged in user's
> > username.
> > 2. Certain functions appear to be parsed and validated correctly but
> run
> > into runtime exceptions, like current_schema. Here's the exception
> > <https://gist.github.com/anyweez/7ee86345c663e313e91c7842f3ffb70c> I
> am
> > getting when I evaluate "SELECT current_schema", but I don't
> understand
> > why an error is occurring for this function but not others, or how I
> go
> > about fixing it. I've tried implementing a ScalarFunction named
> > current_schema, but it doesn't change the outcome.
> > 3. Certain functions may be niladic, like current_schema, but parsing
> > fails when the version with parentheses is used even when I'm using
> > Babel
> > compliance level. My understanding is that using Babel conformance
> > <
> >
> https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java#L280
> > >
> > should allow parentheses to be optional but supported -- any idea how
> to
> > make "SELECT current_schema()" work like "SELECT current_schema" does?
> > - From the docs <https://calcite.apache.org/docs/reference.html>:
> > "Calls to niladic functions such as CURRENT_DATE do not accept
> > parentheses
> > in standard SQL. Calls with parentheses, such as CURRENT_DATE() are
> > accepted in certain conformance levels." Is there another step
> beyond
> > setting the conformance level?
> >
> > Here's the core of the Calcite configuration I'm using, note that I'm
> > including the Postgres operator table and using Babel conformance level:
> >
> > public class CustomQueryRunner {
> > // ... certain fields and methods excluded for brevity ...
> > private void configurePlanner() {
> > SqlParser.Config parserConfig = SqlParser.config()
> > .withConformance(SqlConformanceEnum.BABEL)
> > .withCaseSensitive(false);
> >
> > // List of operators that we want to support (organized into
> > 'operator tables')
> > SqlOperatorTable operatorTable = SqlOperatorTables.chain(List.of(
> > SqlStdOperatorTable.instance(),
> > SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
> > SqlLibrary.POSTGRESQL
> > )
> > ));
> >
> > FrameworkConfig fwc = Frameworks.newConfigBuilder()
> > .parserConfig(parserConfig)
> > .operatorTable(operatorTable)
> > .defaultSchema(this.rootSchema.getSubSchema("pg_catalog",
> > false).plus())
> > .build();
> >
> > this.planner = Frameworks.getPlanner(fwc);
> > }
> >
> > public SqlResultSet execute(String query) {
> > // Parse the query, identify syntax errors and throw
> SqlParseException
> > if
> > // any are found. Query is syntactically valid if no exception
> occurs.
> > SqlNode sqlNode = planner.parse(query);
> >
> > // Semantically validate the query; this ensures that all references
> > are
> > // to entities that exist (schemas, tables, functions, operators,
> etc).
> > If
> > // not, a ValidationException will be thrown.
> > SqlNode validated = planner.validate(sqlNode);
> > RelRoot relRoot = planner.rel(validated);
> >
> > // Run the on the rootSchema and iterate over results
> > PreparedStatement run = RelRunners.run(relRoot.project());
> > ResultSet resultSet = run.executeQuery();
> >
> > // ... method continues ...
> > }
> > }
> >
> > Thanks in advance! I'm loving Calcite - thank you all for your efforts.
> >
>
Re: Questions for properly handling Postgres system functions
Posted by Stamatis Zampetakis <za...@gmail.com>.
Hi Luke,
I am very happy to see more people using Calcite and in particular giving
positive feedback about it. Thanks for doing this, I am sure others
appreciate it as well.
I don't understand 100% your use-case but I assume you are querying
Postgres via the JDBC adapter of Calcite.
When you are using the JDBC adapter usually there is a part of the query
that runs on the underlying DBMS (Postgres in this case) and another part
of the query that is executed in-memory using Calcite operators.
Regarding the various functions you mentioned, CURRENT_USER,
CURRENT_SCHEMA, etc, the result depends on where they are executed.
If the function belongs to the part of the query that is pushed in the DBMS
then it will return exactly the value that the DBMS would return in this
case.
If the function belongs to the part of the query that is executed in-memory
then you have to check what Calcite does in this case.
For example, when CURRENT_USER runs using Enumerable/Bindable operators
then the function does a lookup in the DataContext [1] to return the
appropriate value.
Having said that, if you populate the data context with the desired value
you can somewhat customize the output result.
Check the plan of your queries to see which part runs in the database and
which part runs in Calcite.
By adding/removing rules in the planner or playing with SqlDialects [2] you
can customize what is pushed in the database and what not.
Best,
Stamatis
[1]
https://github.com/apache/calcite/blob/4969b9690efe999c522daf1151c4a00a33be0eb0/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java#L2526
[2]
https://github.com/apache/calcite/blob/4969b9690efe999c522daf1151c4a00a33be0eb0/core/src/main/java/org/apache/calcite/sql/SqlDialect.java#L770
On Mon, May 16, 2022 at 2:27 AM Luke Segars <lu...@lukesegars.com> wrote:
> Hello,
>
> I'm a relatively new Calcite user, and am working on configuring Calcite to
> handle queries that include Postgres system functions
> <https://www.postgresql.org/docs/current/functions-info.html>. I'm running
> into three issues and wondering if anyone could help educate me or provide
> examples:
>
> 1. Certain functions work correctly "out of the box", like current_user
> and session_user, though I don't understand how they produce their
> results.
> When I run SELECT current_user, the ResultSet contains a single row with
> a value of "sa". This looks like it's expected in some Calcite tests
> <
> https://github.com/apache/calcite/blob/master/babel/src/test/resources/sql/redshift.iq#L2112
> >,
> but I'd like to modify this value to be something application-specific.
> Is
> there some way to override the implementation with my own?
> - For example, I'd like to be able to provide the logged in user's
> username.
> 2. Certain functions appear to be parsed and validated correctly but run
> into runtime exceptions, like current_schema. Here's the exception
> <https://gist.github.com/anyweez/7ee86345c663e313e91c7842f3ffb70c> I am
> getting when I evaluate "SELECT current_schema", but I don't understand
> why an error is occurring for this function but not others, or how I go
> about fixing it. I've tried implementing a ScalarFunction named
> current_schema, but it doesn't change the outcome.
> 3. Certain functions may be niladic, like current_schema, but parsing
> fails when the version with parentheses is used even when I'm using
> Babel
> compliance level. My understanding is that using Babel conformance
> <
> https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java#L280
> >
> should allow parentheses to be optional but supported -- any idea how to
> make "SELECT current_schema()" work like "SELECT current_schema" does?
> - From the docs <https://calcite.apache.org/docs/reference.html>:
> "Calls to niladic functions such as CURRENT_DATE do not accept
> parentheses
> in standard SQL. Calls with parentheses, such as CURRENT_DATE() are
> accepted in certain conformance levels." Is there another step beyond
> setting the conformance level?
>
> Here's the core of the Calcite configuration I'm using, note that I'm
> including the Postgres operator table and using Babel conformance level:
>
> public class CustomQueryRunner {
> // ... certain fields and methods excluded for brevity ...
> private void configurePlanner() {
> SqlParser.Config parserConfig = SqlParser.config()
> .withConformance(SqlConformanceEnum.BABEL)
> .withCaseSensitive(false);
>
> // List of operators that we want to support (organized into
> 'operator tables')
> SqlOperatorTable operatorTable = SqlOperatorTables.chain(List.of(
> SqlStdOperatorTable.instance(),
> SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
> SqlLibrary.POSTGRESQL
> )
> ));
>
> FrameworkConfig fwc = Frameworks.newConfigBuilder()
> .parserConfig(parserConfig)
> .operatorTable(operatorTable)
> .defaultSchema(this.rootSchema.getSubSchema("pg_catalog",
> false).plus())
> .build();
>
> this.planner = Frameworks.getPlanner(fwc);
> }
>
> public SqlResultSet execute(String query) {
> // Parse the query, identify syntax errors and throw SqlParseException
> if
> // any are found. Query is syntactically valid if no exception occurs.
> SqlNode sqlNode = planner.parse(query);
>
> // Semantically validate the query; this ensures that all references
> are
> // to entities that exist (schemas, tables, functions, operators, etc).
> If
> // not, a ValidationException will be thrown.
> SqlNode validated = planner.validate(sqlNode);
> RelRoot relRoot = planner.rel(validated);
>
> // Run the on the rootSchema and iterate over results
> PreparedStatement run = RelRunners.run(relRoot.project());
> ResultSet resultSet = run.executeQuery();
>
> // ... method continues ...
> }
> }
>
> Thanks in advance! I'm loving Calcite - thank you all for your efforts.
>