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