Re: Supporting a function with different capabilities depending on database
> if the validator encounters an operator, it looks (by name) for the operator > in the currently active library to see if it is available, then tries to > resolve arguments and return values Yes, that is pretty much correct. Except that there can be several active libraries, so the validator will use the one it hits first. And for a few functions, there is one function but it has different behaviors based on the conformance or type system. (E.g. the return type of AVG depends on the type system.) Conformance and type system can be set independently of library (libraries). On Tue, Mar 12, 2024 at 10:52 AM James Duong wrote: > > I’m referring to using the “fun” connection property and setting it to > ‘postgresql’, which enables PostgreSQL built-in functions and operators. > > I believe this is referred to as a “library” as well. > > I saw this SOUNDEX PR and think it covers what I’m looking for: > https://github.com/apache/calcite/pull/3307 > > Just want to make sure my understanding is correct – if the validator > encounters an operator, it looks (by name) for the operator in the currently > active library to see if it is available, then tries to resolve arguments and > return values. > > Thanks for the help. > > > From: Julian Hyde > Date: Monday, March 11, 2024 at 5:08 PM > To: dev@calcite.apache.org > Subject: Re: Supporting a function with different capabilities depending on > database > "...different capabilities depending on database” > > What exactly do you mean by “database”? If I create a Calcite model that has > one schema backed by a Postgres JDBC catalog and another schema backed by a > MySQL JDBC catalog, what “database” would you expect a connection to this > model to have? > > Calcite has words for these concepts, but I would like you to do the > research, find similar Jira cases, and use the right words for these concepts. > > Julian > > > > On Mar 8, 2024, at 1:40 PM, James Duong > > wrote: > > > > Incidentally, in the PR that added the FloorCeil approach, it was suggested > > based on the SqlStdOperatorTable.like(boolean, boolean) function. > > > > However I don’t see this method referenced anywhere in either the parser or > > regular Java code, and if I delete it, the project still builds. I also > > don’t see it referenced in the PR it was introduced in so it appears to be > > superfluous? I must be missing something – is this invoked with reflection > > somewhere? > > > > Thanks > > > > From: James Duong > > Date: Friday, March 8, 2024 at 12:12 PM > > To: dev@calcite.apache.org > > Subject: Supporting a function with different capabilities depending on > > database > > Hi Calcite dev, > > > > I’m working on adding support for more PostgreSQL functions based on > > PostgreSQL 14 – See https://issues.apache.org/jira/browse/CALCITE-6308. > > > > Some functions such as LOG have different semantics depending on which > > database is used: > > > > * In PostgreSQL, LOG with one argument is to base e while in BigQuery it > > is to base 10. > > * In PostgreSQL, LOG with two arguments has the arguments in the reverse > > order as BigQuery. > > > > Other functions support fewer arguments depending on the database: > > > > * REGEXP_LIKE in PostgreSQL allows an optional extra parameter to > > specify a string of flags. > > > > What would be the right way to model this? Would an approach similar to > > FLOOR/CEIL be best? In that, if the parser encounters FLOOR/CEIL arguments, > > it calls a function which returns a different SqlOperator depending on the > > conformance for the parser. > > > > Thanks, > > James > > Warning: The sender of this message could not be validated and may not be the > actual sender.
Re: Supporting a function with different capabilities depending on database
I’m referring to using the “fun” connection property and setting it to ‘postgresql’, which enables PostgreSQL built-in functions and operators. I believe this is referred to as a “library” as well. I saw this SOUNDEX PR and think it covers what I’m looking for: https://github.com/apache/calcite/pull/3307 Just want to make sure my understanding is correct – if the validator encounters an operator, it looks (by name) for the operator in the currently active library to see if it is available, then tries to resolve arguments and return values. Thanks for the help. From: Julian Hyde Date: Monday, March 11, 2024 at 5:08 PM To: dev@calcite.apache.org Subject: Re: Supporting a function with different capabilities depending on database "...different capabilities depending on database” What exactly do you mean by “database”? If I create a Calcite model that has one schema backed by a Postgres JDBC catalog and another schema backed by a MySQL JDBC catalog, what “database” would you expect a connection to this model to have? Calcite has words for these concepts, but I would like you to do the research, find similar Jira cases, and use the right words for these concepts. Julian > On Mar 8, 2024, at 1:40 PM, James Duong > wrote: > > Incidentally, in the PR that added the FloorCeil approach, it was suggested > based on the SqlStdOperatorTable.like(boolean, boolean) function. > > However I don’t see this method referenced anywhere in either the parser or > regular Java code, and if I delete it, the project still builds. I also don’t > see it referenced in the PR it was introduced in so it appears to be > superfluous? I must be missing something – is this invoked with reflection > somewhere? > > Thanks > > From: James Duong > Date: Friday, March 8, 2024 at 12:12 PM > To: dev@calcite.apache.org > Subject: Supporting a function with different capabilities depending on > database > Hi Calcite dev, > > I’m working on adding support for more PostgreSQL functions based on > PostgreSQL 14 – See https://issues.apache.org/jira/browse/CALCITE-6308. > > Some functions such as LOG have different semantics depending on which > database is used: > > * In PostgreSQL, LOG with one argument is to base e while in BigQuery it > is to base 10. > * In PostgreSQL, LOG with two arguments has the arguments in the reverse > order as BigQuery. > > Other functions support fewer arguments depending on the database: > > * REGEXP_LIKE in PostgreSQL allows an optional extra parameter to specify > a string of flags. > > What would be the right way to model this? Would an approach similar to > FLOOR/CEIL be best? In that, if the parser encounters FLOOR/CEIL arguments, > it calls a function which returns a different SqlOperator depending on the > conformance for the parser. > > Thanks, > James Warning: The sender of this message could not be validated and may not be the actual sender.
Re: Supporting a function with different capabilities depending on database
"...different capabilities depending on database” What exactly do you mean by “database”? If I create a Calcite model that has one schema backed by a Postgres JDBC catalog and another schema backed by a MySQL JDBC catalog, what “database” would you expect a connection to this model to have? Calcite has words for these concepts, but I would like you to do the research, find similar Jira cases, and use the right words for these concepts. Julian > On Mar 8, 2024, at 1:40 PM, James Duong > wrote: > > Incidentally, in the PR that added the FloorCeil approach, it was suggested > based on the SqlStdOperatorTable.like(boolean, boolean) function. > > However I don’t see this method referenced anywhere in either the parser or > regular Java code, and if I delete it, the project still builds. I also don’t > see it referenced in the PR it was introduced in so it appears to be > superfluous? I must be missing something – is this invoked with reflection > somewhere? > > Thanks > > From: James Duong > Date: Friday, March 8, 2024 at 12:12 PM > To: dev@calcite.apache.org > Subject: Supporting a function with different capabilities depending on > database > Hi Calcite dev, > > I’m working on adding support for more PostgreSQL functions based on > PostgreSQL 14 – See https://issues.apache.org/jira/browse/CALCITE-6308. > > Some functions such as LOG have different semantics depending on which > database is used: > > * In PostgreSQL, LOG with one argument is to base e while in BigQuery it > is to base 10. > * In PostgreSQL, LOG with two arguments has the arguments in the reverse > order as BigQuery. > > Other functions support fewer arguments depending on the database: > > * REGEXP_LIKE in PostgreSQL allows an optional extra parameter to specify > a string of flags. > > What would be the right way to model this? Would an approach similar to > FLOOR/CEIL be best? In that, if the parser encounters FLOOR/CEIL arguments, > it calls a function which returns a different SqlOperator depending on the > conformance for the parser. > > Thanks, > James
Re: Supporting a function with different capabilities depending on database
Incidentally, in the PR that added the FloorCeil approach, it was suggested based on the SqlStdOperatorTable.like(boolean, boolean) function. However I don’t see this method referenced anywhere in either the parser or regular Java code, and if I delete it, the project still builds. I also don’t see it referenced in the PR it was introduced in so it appears to be superfluous? I must be missing something – is this invoked with reflection somewhere? Thanks From: James Duong Date: Friday, March 8, 2024 at 12:12 PM To: dev@calcite.apache.org Subject: Supporting a function with different capabilities depending on database Hi Calcite dev, I’m working on adding support for more PostgreSQL functions based on PostgreSQL 14 – See https://issues.apache.org/jira/browse/CALCITE-6308. Some functions such as LOG have different semantics depending on which database is used: * In PostgreSQL, LOG with one argument is to base e while in BigQuery it is to base 10. * In PostgreSQL, LOG with two arguments has the arguments in the reverse order as BigQuery. Other functions support fewer arguments depending on the database: * REGEXP_LIKE in PostgreSQL allows an optional extra parameter to specify a string of flags. What would be the right way to model this? Would an approach similar to FLOOR/CEIL be best? In that, if the parser encounters FLOOR/CEIL arguments, it calls a function which returns a different SqlOperator depending on the conformance for the parser. Thanks, James
Supporting a function with different capabilities depending on database
Hi Calcite dev, I’m working on adding support for more PostgreSQL functions based on PostgreSQL 14 – See https://issues.apache.org/jira/browse/CALCITE-6308. Some functions such as LOG have different semantics depending on which database is used: * In PostgreSQL, LOG with one argument is to base e while in BigQuery it is to base 10. * In PostgreSQL, LOG with two arguments has the arguments in the reverse order as BigQuery. Other functions support fewer arguments depending on the database: * REGEXP_LIKE in PostgreSQL allows an optional extra parameter to specify a string of flags. What would be the right way to model this? Would an approach similar to FLOOR/CEIL be best? In that, if the parser encounters FLOOR/CEIL arguments, it calls a function which returns a different SqlOperator depending on the conformance for the parser. Thanks, James
