Re: How to avoid SUM0 or disable a rule ?

[Vamshi Krishna]

It's done in the SqlToRelConverter.java:5427. I don't think there is a
way currently to disable it (i may be wrong).
There should be a configurable option to disable this.


-Vamshi

On Tue, Jun 11, 2019 at 2:31 PM Muhammad Gelbana  wrote:
>
> I just cleared the reducible aggregate calls collection at runtime (to void
> the rule) and I'm still facing the same problem. This onviously has nothing
> to do with the rule. I'll investigate further. Thanks for your help.
>
> Thanks,
> Gelbana
>
>
> On Tue, Jun 11, 2019 at 8:16 PM Haisheng Yuan 
> wrote:
>
> > Hi Gelbana,
> >
> > You can construct your own AggregateReduceFunctionsRule instance by
> > specifying the functions you want to reduce:
> >
> > public AggregateReduceFunctionsRule(Class 
> > aggregateClass,
> > RelBuilderFactory relBuilderFactory, EnumSet 
> > functionsToReduce) {
> >
> >
> > But I think the issue you described might be a bug, can you open a JIRA
> > issue with a test case if possible?
> >
> > - Haisheng
> >
> > --
> > 发件人：Muhammad Gelbana
> > 日 期：2019年06月12日 01:46:28
> > 收件人：
> > 主 题：How to avoid SUM0 or disable a rule ?
> >
> > Executing the following query produces unexpected results
> >
> > SELECT
> > "Calcs"."key" AS "key",
> > SUM("Calcs"."num2") AS "sum:num2:ok",
> > SUM("Calcs"."num2") AS "$__alias__0"
> > FROM "TestV1"."Calcs" "Calcs"
> > GROUP BY 1
> > ORDER BY 3 ASC NULLS FIRST
> > LIMIT 10
> >
> > The returned results contains 0 instead of NULLs while running the query
> > against a PostgreSQL instance returns NULLs as expected.
> >
> >
> > The reason for that is that Calcite uses SUM0 implementation instead of SUM.
> > I found that the AggregateReduceFunctionsRule rule is the one that converts
> > the SUM aggregate call to SUM0, so is there a way to remove this rule
> > before planning ?
> >
> > Thanks,
> > Gelbana
> >
> >

Re: About the Spatial functionality

[Vamshi Krishna]

Hi Julian,

I have tested the fix posted for CALCITE-2072 and i was able to use the
geometry functions.

Thanks for the fix.

Thanks,
Vamshi.

On Fri, Dec 8, 2017 at 4:38 PM, Julian Hyde  wrote:

> I don’t know. I don’t have time to look at the code right now.
>
> > On Dec 8, 2017, at 12:53 PM, Vamshi Krishna 
> wrote:
> >
> > I think you meant the following:
> >
> > 1. Create a user defined operator table for spatial functions (Say
> > SpatialOperatorTable).
> > 2. Honor the newly defined operator table (SpatialOperatorTable) when
> > fun=spatial at CalciteConnectionConfiImpl.java#L102
> >
> > Regarding Step  1: I don't see a way to convert or load the functions in
> > GeoFunctions class into SqlFunction format other than manually adding
> them
> > similar to OracleSqlOperatorTable. Is my understanding right ?
> >
> >
> >
> > On Fri, Dec 8, 2017 at 2:01 PM, Julian Hyde  jh...@apache.org>> wrote:
> >
> >> You’re basically running into https://issues.apache.org/ <
> https://issues.apache.org/>
> >> jira/browse/CALCITE-2072 <https://issues.apache.org/ <
> https://issues.apache.org/>
> >> jira/browse/CALCITE-2072>. The fix for that issue is straightforward - a
> >> couple of lines around https://github.com/apache/ <
> https://github.com/apache/>
> >> calcite/blob/master/core/src/main/java/org/apache/calcite/config/
> >> CalciteConnectionConfigImpl.java#L102 <https://github.com/apache/ <
> https://github.com/apache/>
> >> calcite/blob/master/core/src/main/java/org/apache/calcite/config/
> >> CalciteConnectionConfigImpl.java#L102> - so can you make that fix and
> see
> >> whether it fixes the problem.
> >>
> >>> On Dec 8, 2017, at 6:52 AM, Vamshi Krishna  >
> >> wrote:
> >>>
> >>> Hi Christian,
> >>>
> >>> Yes,  I have changed the conformance level to LENIENT.
> >>> I was able test the create table syntax using geometry data type.
> >>>
> >>>
> >>> Here's what i have in the code:
> >>> //create root schema
> >>> rootSchema = Frameworks.createRootSchema(true);
> >>>
> >>> //add geo functions
> >>>  ModelHandler.addFunctions(rootSchema, null,
> >> ImmutableList.of(),
> >>>GeoFunctions.class.getName(), "*", true);
> >>>
> >>>
> >>>   // Initialize default planner
> >>>   FrameworkConfig calciteFrameworkConfig =
> >>> Frameworks.newConfigBuilder()
> >>>   .operatorTable(ChainedSqlOperatorTable.of(
> >>> OracleSqlOperatorTable.instance(),SqlStdOperatorTable.instance()))
> >>>
> >>> .parserConfig(SqlParser.configBuilder().setConformance(LENIENT)
> >>>   // Lexical configuration defines how identifiers
> >>> are quoted, whether they are converted to upper or lower
> >>>   // case when they are read, and whether
> >> identifiers
> >>> are matched case-sensitively.
> >>>   .setParserFactory(SqlParserImpl.FACTORY)
> >>>   .setLex(Lex.ORACLE)
> >>>   .build())
> >>>   // Sets the schema to use by the planner
> >>>   .defaultSchema(rootSchema.add("CATALOG",schema))
> >>>   .traitDefs(traitDefs)
> >>>   // Context provides a way to store data within the
> planner
> >>> session that can be accessed in planner rules.
> >>>   .context(Contexts.EMPTY_CONTEXT)
> >>>   // Rule sets to use in transformation phases. Each
> >>> transformation phase can use a different set of rules.
> >>>   .ruleSets(RuleSets.ofList())
> >>>   // Custom cost factory to use during optimization
> >>>   .costFactory(null)
> >>>   .typeSystem(RelDataTypeSystem.DEFAULT)
> >>>   .build();
> >>>
> >>>   this.planner = new CustomPlannerImpl(calciteFrameworkConfig);
> >>>
> >>>   planner.parse();
> >>>
> >>>   planner.validate(); <<-- reporting error with no match found.
> >>>
> >>> Currently i am only looking for syntax and data type validation support
> >> and
> >>> not the runtime implementation of the geo functions.

Re: About the Spatial functionality

[Vamshi Krishna]

I think you meant the following:

1. Create a user defined operator table for spatial functions (Say
SpatialOperatorTable).
2. Honor the newly defined operator table (SpatialOperatorTable) when
fun=spatial at CalciteConnectionConfiImpl.java#L102

Regarding Step  1: I don't see a way to convert or load the functions in
GeoFunctions class into SqlFunction format other than manually adding them
similar to OracleSqlOperatorTable. Is my understanding right ?



On Fri, Dec 8, 2017 at 2:01 PM, Julian Hyde  wrote:

> You’re basically running into https://issues.apache.org/
> jira/browse/CALCITE-2072 <https://issues.apache.org/
> jira/browse/CALCITE-2072>. The fix for that issue is straightforward - a
> couple of lines around https://github.com/apache/
> calcite/blob/master/core/src/main/java/org/apache/calcite/config/
> CalciteConnectionConfigImpl.java#L102 <https://github.com/apache/
> calcite/blob/master/core/src/main/java/org/apache/calcite/config/
> CalciteConnectionConfigImpl.java#L102> - so can you make that fix and see
> whether it fixes the problem.
>
> > On Dec 8, 2017, at 6:52 AM, Vamshi Krishna 
> wrote:
> >
> > Hi Christian,
> >
> > Yes,  I have changed the conformance level to LENIENT.
> > I was able test the create table syntax using geometry data type.
> >
> >
> > Here's what i have in the code:
> >  //create root schema
> >  rootSchema = Frameworks.createRootSchema(true);
> >
> >  //add geo functions
> >   ModelHandler.addFunctions(rootSchema, null,
> ImmutableList.of(),
> > GeoFunctions.class.getName(), "*", true);
> >
> >
> >// Initialize default planner
> >FrameworkConfig calciteFrameworkConfig =
> > Frameworks.newConfigBuilder()
> >.operatorTable(ChainedSqlOperatorTable.of(
> > OracleSqlOperatorTable.instance(),SqlStdOperatorTable.instance()))
> >
> > .parserConfig(SqlParser.configBuilder().setConformance(LENIENT)
> >// Lexical configuration defines how identifiers
> > are quoted, whether they are converted to upper or lower
> >// case when they are read, and whether
> identifiers
> > are matched case-sensitively.
> >.setParserFactory(SqlParserImpl.FACTORY)
> >.setLex(Lex.ORACLE)
> >.build())
> >// Sets the schema to use by the planner
> >.defaultSchema(rootSchema.add("CATALOG",schema))
> >.traitDefs(traitDefs)
> >// Context provides a way to store data within the planner
> > session that can be accessed in planner rules.
> >.context(Contexts.EMPTY_CONTEXT)
> >// Rule sets to use in transformation phases. Each
> > transformation phase can use a different set of rules.
> >.ruleSets(RuleSets.ofList())
> >// Custom cost factory to use during optimization
> >.costFactory(null)
> >.typeSystem(RelDataTypeSystem.DEFAULT)
> >.build();
> >
> >this.planner = new CustomPlannerImpl(calciteFrameworkConfig);
> >
> >planner.parse();
> >
> >planner.validate(); <<-- reporting error with no match found.
> >
> > Currently i am only looking for syntax and data type validation support
> and
> > not the runtime implementation of the geo functions.
> >
> >
> >
> > I am not sure if the GeoFunctions extension can be used for this purpose
> > similar to functions in OracleSqlOperator.
> >
> > Thanks,
> > Vamshi.
> >
> >
> >
> >
> >
> > On Thu, Dec 7, 2017 at 10:26 PM, Christian Tzolov 
> > wrote:
> >
> >> Hi Vamshi,
> >>
> >> Have you set the conformance to such that supports Geometry? i've been
> >> using lenient like this: jdbc:calcite:conformance=LENIENT;
> >> ​model=...my model​
> >> ​
> >> ​
> >> ​
> >>
> >> On 7 December 2017 at 13:53, Vamshi Krishna  >
> >> wrote:
> >>
> >>> Hello Team,
> >>>
> >>> I have tried to use these functions by adding it to my schema as given
> >>> below:
> >>>
> >>> ModelHandler.addFunctions(rootSchema, null,
> ImmutableList.of(),
> >>> GeoFunctions.class.getName(), "*", true);
> >>>
> >>> but i run into an validation issue when calling the planner's validate
> >>> routine

Re: About the Spatial functionality

[Vamshi Krishna]

Hi Christian,

Yes,  I have changed the conformance level to LENIENT.
I was able test the create table syntax using geometry data type.


Here's what i have in the code:
  //create root schema
  rootSchema = Frameworks.createRootSchema(true);

  //add geo functions
   ModelHandler.addFunctions(rootSchema, null, ImmutableList.of(),
 GeoFunctions.class.getName(), "*", true);


// Initialize default planner
FrameworkConfig calciteFrameworkConfig =
Frameworks.newConfigBuilder()
.operatorTable(ChainedSqlOperatorTable.of(
OracleSqlOperatorTable.instance(),SqlStdOperatorTable.instance()))

.parserConfig(SqlParser.configBuilder().setConformance(LENIENT)
// Lexical configuration defines how identifiers
are quoted, whether they are converted to upper or lower
// case when they are read, and whether identifiers
are matched case-sensitively.
.setParserFactory(SqlParserImpl.FACTORY)
.setLex(Lex.ORACLE)
.build())
// Sets the schema to use by the planner
.defaultSchema(rootSchema.add("CATALOG",schema))
.traitDefs(traitDefs)
// Context provides a way to store data within the planner
session that can be accessed in planner rules.
.context(Contexts.EMPTY_CONTEXT)
// Rule sets to use in transformation phases. Each
transformation phase can use a different set of rules.
.ruleSets(RuleSets.ofList())
// Custom cost factory to use during optimization
.costFactory(null)
.typeSystem(RelDataTypeSystem.DEFAULT)
.build();

this.planner = new CustomPlannerImpl(calciteFrameworkConfig);

planner.parse();

planner.validate(); <<-- reporting error with no match found.

Currently i am only looking for syntax and data type validation support and
not the runtime implementation of the geo functions.



I am not sure if the GeoFunctions extension can be used for this purpose
similar to functions in OracleSqlOperator.

Thanks,
Vamshi.





On Thu, Dec 7, 2017 at 10:26 PM, Christian Tzolov 
wrote:

> Hi Vamshi,
>
> Have you set the conformance to such that supports Geometry? i've been
> using lenient like this: jdbc:calcite:conformance=LENIENT;
> ​model=...my model​
> ​
> ​
> ​
>
> On 7 December 2017 at 13:53, Vamshi Krishna 
> wrote:
>
> > Hello Team,
> >
> > I have tried to use these functions by adding it to my schema as given
> > below:
> >
> > ModelHandler.addFunctions(rootSchema, null, ImmutableList.of(),
> > GeoFunctions.class.getName(), "*", true);
> >
> > but i run into an validation issue when calling the planner's validate
> > routine with the below error:
> > No match found for function signature ST_MAKEPOINT(, ,
> > )
> >
> > Do we have to register these functions manually similar to
> > OracleSqlOperatorTable or is there another way out for this ?
> >
> >
> > Thanks,
> > Vamshi.
> >
> >
> >
> >
> > On Sat, Dec 2, 2017 at 4:33 AM, Christian Tzolov 
> > wrote:
> >
> > > @Michael, sure go ahead and use the query if you find it fit. I am
> > looking
> > > forward to read this paper! If you need an "external" opinion about how
> > > Calcite fits in the broader  data management ecosystem or "patterns"
> for
> > > building Calcite adapters i can share few ideas ;)
> > >
> > > @Julian, i'm aware and agree with the approach for providing spatial
> > > support. Currently I'm only trying to make it work (somehow) in the
> > context
> > > of the Geode adapter! My goal is to mention it during my talk at Apache
> > > Geode Summit [1] on Monday.
> > >
> > > While on the topic of my talk [1], i've been looking for tempting
> reasons
> > > to engage/involve the Geode community with the project or at least
> start
> > > the argument. Here are my slides [2] (for internal use only until
> > Monday).
> > > In the pros and cons section i've shared some ideas:
> > >
> > > 1. In the context of data exploration and cleansing, an obvious
> advantage
> > > is the easiness for integration with 3rd party DMS tools and
> > >
> > > 2. In the same context the easy to correlate Geode data with data from
> > > multiple Sql and NoSql data stores (e.g. Data Federation).
> > >
> > > 3. But given that Geode is primarily used for OLTP-ish (e.g.
> > > transactional)  workloads, even the OQ

Re: About the Spatial functionality

[Vamshi Krishna]

Hello Team,

I have tried to use these functions by adding it to my schema as given
below:

ModelHandler.addFunctions(rootSchema, null, ImmutableList.of(),
GeoFunctions.class.getName(), "*", true);

but i run into an validation issue when calling the planner's validate
routine with the below error:
No match found for function signature ST_MAKEPOINT(, ,
)

Do we have to register these functions manually similar to
OracleSqlOperatorTable or is there another way out for this ?


Thanks,
Vamshi.




On Sat, Dec 2, 2017 at 4:33 AM, Christian Tzolov  wrote:

> @Michael, sure go ahead and use the query if you find it fit. I am looking
> forward to read this paper! If you need an "external" opinion about how
> Calcite fits in the broader  data management ecosystem or "patterns" for
> building Calcite adapters i can share few ideas ;)
>
> @Julian, i'm aware and agree with the approach for providing spatial
> support. Currently I'm only trying to make it work (somehow) in the context
> of the Geode adapter! My goal is to mention it during my talk at Apache
> Geode Summit [1] on Monday.
>
> While on the topic of my talk [1], i've been looking for tempting reasons
> to engage/involve the Geode community with the project or at least start
> the argument. Here are my slides [2] (for internal use only until Monday).
> In the pros and cons section i've shared some ideas:
>
> 1. In the context of data exploration and cleansing, an obvious advantage
> is the easiness for integration with 3rd party DMS tools and
>
> 2. In the same context the easy to correlate Geode data with data from
> multiple Sql and NoSql data stores (e.g. Data Federation).
>
> 3. But given that Geode is primarily used for OLTP-ish (e.g.
> transactional)  workloads, even the OQL is considered a second-class
> citizen. Therefore i find the concept of "SQL Stream" quite relevant and i
> expect that the Geode community will find it interesting too. Geode already
> provides limited CQ (Continues Querying) functionality and IMO the "Sql
> Stream" will be like advanced CQ++. Unfortunately i haven't had time to
> build and prototype in the context of Geode. Can you point me to some code
> examples? I know about the Tests but are there and actual adapters or other
> applications that use the "SQL Stream"?
>
> 4. @Julian you have mentioned the idea about dynamic materializations.  But
> i'm not sure i completely understand the approach and that confident to
> bring it for discussion. If you have some written references that can help
> me i will appreciate it.
>
> Cheers,
> Christian
>
>
> [1] Enable SQL/JDBC Access to Apache Geode/GemFire Using Apache Calcite:
> https://springoneplatform.io/sessions/enable-sql-jdbc-
> access-to-apache-geode-gemfire-using-apache-calcite
> [2] Slides:
> https://docs.google.com/presentation/d/1zo473pcupWEjRXOA_
> W5rgaKSmS2Vmyl2U2ATKmrS26M/edit?usp=sharing
>
>
> On 1 December 2017 at 21:05, Julian Hyde  wrote:
>
> > The Natural earth dataset (which https://github.com/zzolo/geo_
> > simple_countries_wkt_csv  > simple_countries_wkt_csv> is based upon) is Public Domain, which makes it
> > suitable for our purposes.
> >
> > > Although inefficient (all spatial computations happen on calcite side)
> > it is still very cool! :)
> >
> > That’s exactly what I was going for. First make it work (by adding all
> > OpenGIS functions as UDFs), then make it fast (by adding rewrite rules
> that
> > recognize functions and particular patterns of materialized views).
> >
> > Your query is a spatial join of polygons (cities) to polygons
> (countries).
> > I have in mind a materialized view where polygons are sliced into
> bounding
> > “tiles” and I think it should speed up this kind of query.
> >
> > Julian
> >
> >
> > > On Dec 1, 2017, at 9:09 AM, Christian Tzolov 
> wrote:
> > >
> > > The OpenGIS Spec datasets sounds like right, "canonical" spatial
> dataset
> > to have.
> > >
> > > In the meantime for the purposes of my tests i found a dataset (
> > https://github.com/zzolo/geo_simple_countries_wkt_csv <
> > https://github.com/zzolo/geo_simple_countries_wkt_csv>) that contains
> > world country boundaries as WKT polygons along with their names, ISO
> > abbreviations  and other metadata. I've also converted the csv into json
> > (attached) to make it easy for loading in Geode.
> > >
> > > This allows me to run crazy queries like this :)
> > >
> > > SELECT "NAME", ST_Distance("Country", ST_GeomFromText('POINT(23.288269
> > 42.731883)')) as "distanceToBG"
> > > FROM (
> > >   SELECT
> > >"NAME",
> > > ST_GeomFromText('POLYGON((4.822998 52.427652, 4.971313 52.427652,
> > 4.971313 52.333661, 4.822998 52.333661, 4.822998 52.427652))') AS
> > "Amsterdam",
> > > ST_GeomFromText("WKT") AS "Country"
> > >   FROM "geode"."Country"
> > > )
> > > WHERE ST_Contains("Country", "Amsterdam");
> > >
> > > E.g. retrieves the countries that contain the Amsterdam, NL area and
> for
> > the result computes the distances to So