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