Re: Can calcite translate Oracle SQL to MySQL

If you're looking for a "translate X-to-Y dialect of SQL" tool, jOOQ does this fairly well. What you have to do is parse the string of SQL into a jOOQ query AST, then render it using a different SQL dialect. You can experiment with the results of this online here: Format, pretty print, and

Re: Ability to access Calcite JDBC remotely from another JDBC client such as DBeaver/DataGrip

Heya, I actually wrote quite a long tutorial on how to connect to Calcite via SQuirreL/DBeaver or similar tools. It's not a simple process. Here is the guide: Making Apache Calcite work in SQuirreL SQL Client (github.com) On

Re: [Help Needed] Dissecting CTE out of Calcite LogicPlan

On a semi-tangent I would be curious to see the images, if Ken could maybe upload to a public image host like imgur (or similar) and then share the links here. On Wed, Jul 26, 2023 at 2:20 PM Julian Hyde wrote: > Your images did not come through (ASF mail server strips them from all > emails)

Re: Developing a new SqlDialect

I can't offer much in the way of advice for Dialect development, but anecdotally it took me maybe 3-4 weeks of reading the Calcite source code every day after work (mostly the tests) and whatever tutorial material I could find to start to wrap my head around the general API and program flow. (I

Re: [DISCUSS] Apache Calcite Meetup March 2023

at 18:00 (UTC)? > > It's hard to find a slot that is perfect for everyone so I will consider > > first the preferences/constraints of people that will present. > > > > Best, > > Stamatis > > > > > > On Fri, Feb 3, 2023 at 5:58 PM Gavin Ray wrote: >

Re: [DISCUSS] Apache Calcite Meetup March 2023

on Mar 29, so I could give a shorter talk on the same topic > at the meetup, if people are interested. > > Julian > > [1] https://www.datacouncil.ai/talks/cubing-and-metrics-in-sql > > On Tue, Jan 24, 2023 at 5:10 AM Gavin Ray wrote: > > > > Hey Stamatis, > &g

Re: [ANNOUNCE] Benchao Li joins Calcite PMC

Congratulations Benchao!!! On Sat, Jan 28, 2023, 9:08 AM Benchao Li wrote: > Thanks everyone! > > It's a great honor for me to join the Calcite PMC! Looking forward for more > interactions with the community. > > > > 163 于2023年1月28日周六 21:45写道： > > > Congratulations Benchao! > > > > >

Re: [DISCUSS] Apache Calcite Meetup March 2023

Hey Stamatis, I would love to attend virtually, and I have a topic I would like to discuss with the community during open discussion at the end if time permits (how JSON is handled in Calcite dialects) On Tue, Jan 24, 2023 at 5:44 AM Stamatis Zampetakis wrote: > Hi all, > > Last meetup [1]

Re: [ANNOUNCE] New Calcite PMC chair: Stamatis Zampetakis

Congratulations Stamatis, well-deserved! On Thu, Jan 19, 2023 at 1:34 PM Julian Hyde wrote: > As many of you remember, Stamatis has served as Chair before. This is > the first time that Calcite has re-elected a previous Chair, and that > speaks to Stamatis' excellent qualities. Stamatis' style

Re: Using Calcite in a multi-tenant application

It is a little bit difficult to explain if you aren't already pretty familiar with Calcite, but I've used Calcite for multi-tenant before. What you do is hold open the single root CalciteConnection, and then create a sub-schema per tenant Then, you scope the current statement to a sub-schema

Hosted online Calcite playground

Hi all, I wrote an am hosting a service that creates session-scoped Calcite schemas that have the Chinook dataset loaded This allows people to experiment with Calcite, or check how certain operations would be planned, without needing to set anything up The service can be used here:

Re: Does someone interested in integration calcite + PostgreSQL wire protocol ?

Understandable, no worries =) On Sun, Sep 11, 2022 at 2:49 AM Dmitry Sysolyatin wrote: > unfortunately, I can't reveal the details of the implementation until our > company has given the official right to make this project open source :( > > > 10 сент. 2022 г., в 20:16, Gavin

Re: Does someone interested in integration calcite + PostgreSQL wire protocol ?

columns > 3. Security, it is hard to do any security related things on top of > external Postgres pg_catalog table. For example, to show user only his > tables, schemas and so on, so on > > > 10 сент. 2022 г., в 18:58, Gavin Ray написал(а): > > > > I have a basic J

Re: Does someone interested in integration calcite + PostgreSQL wire protocol ?

of the metadata But this is not ideal and it requires having an external Postgres data + some setup On Sat, Sep 10, 2022 at 11:55 AM Gavin Ray wrote: > +1 > > On Sat, Sep 10, 2022 at 11:30 AM Dmitry Sysolyatin < > dm.sysolya...@gmail.com> wrote: > >> Hello! >> >> I've

Re: Does someone interested in integration calcite + PostgreSQL wire protocol ?

+1 On Sat, Sep 10, 2022 at 11:30 AM Dmitry Sysolyatin wrote: > Hello! > > I've been working on the integration calcite and PostgreSQL wire protocol > [1] to emulate Postgres server (Of course it includes emulating > `pg_catalog` schema). It gives a user the ability to connect to a calcite >

Re: [DISCUSS] Towards Calcite 1.32.0

This may not be the appropriate place to ask, but is there any chance of merging the below for this release, or maybe next release? It's been around a few years and I think it is one of the few blockers in place for taking advantage of MULTISET functionality, much-appreciated if you want to return

Re: Calcite Release 1.31 started

Hooray!! On Wed, Aug 3, 2022 at 2:34 PM Michael Mior wrote: > Thanks for all your work Andrei! > > -- > Michael Mior > mm...@apache.org > > > On Wed, Aug 3, 2022 at 12:44 PM Andrei Sereda wrote: > > > 1.31 has been released. > > > > You can resume dev work on the main branch. > > > > Thanks

Re: How does one leverage Calcite to federate queries over multiple underlying engines?

I've been meaning to write and publish something on this since I had the same question + it seems to come up often There are essentially two important things you need to know about Calcite to work with federated data: 1. Calcite schemas work like filesystem hierarchies. You have a "rootSchema"

Re: New Polymorphic Tables functionality -- interesting use cases or am I misunderstanding?

ue_date") > ) > ) AS S > > > The result will be > docnonamedue_dateprincipleinterest > 123 Mary 01/01/2014 234.56 345.67 > 234 Edgar 01/01/2014 654.32 543.21 > > > More examples could be found in the description of CALCITE-4865[1]. > Users could define a c

New Polymorphic Tables functionality -- interesting use cases or am I misunderstanding?

I see there's a recent commit which references this: [CALCITE-4864] Supports Polymorphic Table function - ASF JIRA (apache.org) Something Teiid [0] did was to use JSON_TABLE() from dynamic data at runtime. I had an opportunity to speak to the

"Proxying" schema that intercepts calls to members and returns stub values?

How difficult would it be to create a proxying schema that returns the integer value 1 for every column requested This would be useful for a few things probably, but one neat use it would unlock is the ability to use Calcite for translation between SQL dialects without having a schema beforehand.

A guide to connecting to Calcite with SQuirreL, a SQL GUI client

Hey all, I took time to write a guide on how to configure SQuirreL SQL so that it can connect to Calcite sources It's a useful alternative to sqlline if you like GUI tools. I hope someone else finds it helpful: Making Apache Calcite work in SQuirreL SQL Client (github.com)

Re: What is the difference between running expressions with RelRunner + RelNode vs JDBC Connection and SQL string

a JDBC connection, and not every query is most naturally > expressed in SQL, so we tried to make all of the combinations possible. > Some of the non-JDBC options may still create a JDBC connection under the > covers, which is not ideal. > > > > On Jun 21, 2022, at 1:07 PM, Gavin Ray wr

What is the difference between running expressions with RelRunner + RelNode vs JDBC Connection and SQL string

>From what I've been able to tell, surface-level functionally the below work the same but I'm wondering whether there are any differences/any scenarios to prefer one to the other? == fun executeQuery(sql: String): ResultSet { val planner =

Re: [jira] [Created] (CALCITE-5187) "CalciteSchema call getSubSchemaMap when prepareSql" will Causes SQL execution to slow down

Out of curiosity, why are you recalculating subschemas on queries? It seems like you might be able to cache the schema information on initial source add, and then use DB event triggers to cause a recalculation on schema change On Thu, Jun 9, 2022 at 11:25 PM itxiangkui (Jira) wrote: >

Re: Implementing JSON_TABLE, even hackily as a UDF?

yde wrote: > Is there any overlap with the file adapter? The file adapter can read > using various transports (file, http, optional compression) and various > formats (csv, json, html tables). > > > On May 31, 2022, at 12:47 PM, Gavin Ray wrote: > > > > I don't think it'

Re: Implementing JSON_TABLE, even hackily as a UDF?

wip 2 (github.com) <https://gist.github.com/GavinRay97/fbd16dd2b893cb59c720e6d514c5e39a#file-main-java> On Tue, May 31, 2022 at 2:44 PM Julian Hyde wrote: > What is JSON_TABLE? Is it in the SQL standard? Can you give a simple > example? > > On Tue, May 31, 2022 at 10:03 A

Re: Implementing JSON_TABLE, even hackily as a UDF?

ist.github.com/GavinRay97/b42c69992a0d0c3a01f1c7e1b9ab1999> This is the output of main() json = {"id":1, "name":"John"} json = {"id":1, "name":"John"} json = {"id":1, "name":"John"} EXPR$0: id EXPR$0: id

Implementing JSON_TABLE, even hackily as a UDF?

I'm interested in implementing JSON_TABLE functionality for Calcite This opens up some neat usecases, like adding HTTP request UDF's then using JSON_TABLE to convert the result into a table: SELECT JSON_TABLE( HTTP_GET('http://localhost:8080/api/v1/users/1')) Adding support for all of the

Re: [ANNOUNCE] Vladimir Ozerov joins Calcite PMC

Congrats! For some reason I thought you were on the PMC, so I guess that mistaken assumption is correct now at least =D On Tue, May 24, 2022 at 10:47 AM Ruben Q L wrote: > I am pleased to announce that Vladimir has accepted an invitation to join > the Calcite PMC. Vladimir has been a

Re: [ANNOUNCE] Chunwei Lei joins Calcite PMC

In the words of a true poet of our time: *"I just wanna say Congratulations" * - *Post Malone* On Tue, May 24, 2022 at 10:47 AM Ruben Q L wrote: > I am pleased to announce that Chunwei has accepted an invitation to join > the > Calcite PMC. Chunwei has been a consistent and helpful figure

Re: I built a toolkit for building Postgres wire-protocol backends, and then built a Calcite wrapper for it

WHERE c.oid = '1' > > 4-5 tasks were created in calcite JIRA only for this query. There are many > others queries that should work ) > > Maybe when we will finish emulating pg_catalog we will open-source the code > of the integration calcite with Postgres wire protocol. > &

I built a toolkit for building Postgres wire-protocol backends, and then built a Calcite wrapper for it

A while ago I saw a mention on the mailing list of someone interested in making Calcite speak Postgres' wire protocol, so all existing tools/client libraries could interface with it. I couldn't find anything on the internet for this -- either for Calcite,or surprisingly even a JVM toolkit for

Re: Implementing a modifiable adapter

Hey Mike, I can't offer much in the way of experience on the above -- but you may also get pointers if you're able to share an overview of what you're trying to do (if you can disclose) You say a set of mutable objects, is this something like in-memory collections? Also, are you by chance the

Re: [QUESTION] Window functions and Relational Operator Tree

This might help: https://github.com/apache/calcite/blob/e08ca8c5df5dee154ae1a1e6adfa2f9ce693ccf7/core/src/test/java/org/apache/calcite/plan/RelWriterTest.java#L570-L595 And it's JSON + text representations:

Re: Changes to the rule pattern interface

I don't have any weight behind my opinion or experience, but anything that lowers the barrier to entry to Calcite for newcomers is a huge win in my mind. I assume the reason for the changes was because codegen improved performance? Could it make sense to allow both options, the

Re: [DISCUSS] Should DEFAULT be a keyword

The hacky way would be to add a grammar clause like this I think: | "DEFAULT" To the "TableRef2" node in the parser grammar: https://github.com/apache/calcite/blob/82dd78a14f6aef2eeec2f9c94978d04b4acc5359/core/src/main/codegen/templates/Parser.jj#L2087-L2276 Not sure all the implications this

Re: A basic, DB-agnostic question about the concept of a "Physical Plan"

er SQL or some native query language > > (CQL > > > for Cassandra, for example), but then the DB will do all the steps > again, > > > parse, validate, etc., build a logical plan, optimize it and run it as > it > > > thinks it's best, which can be radically differen

A basic, DB-agnostic question about the concept of a "Physical Plan"

I'm on my second pass of the book "How Query Engines Work" by Arrow's own Andy Grove (Really great read, huge recommendation: https://leanpub.com/how-query-engines-work) Something I'm not sure I'm fully understanding is what qualifies something as a Physical Plan A logical plan is

Re: Context-aware/stateful SQL operator functions? (IE adding a stack for number of times an operator has been called in query)

em can someone update the JIRA accordingly? > > Best, > Stamatis > > [1] https://issues.apache.org/jira/browse/CALCITE-4989 > > On Tue, Mar 22, 2022 at 3:56 AM Gavin Ray wrote: > > > Wow, really appreciate you taking the time to explain how it works. > > > &g

Re: Context-aware/stateful SQL operator functions? (IE adding a stack for number of times an operator has been called in query)

> "JSON output clause" only controls the serialization format for json > function's output, > and "JSON FORMAT" should be the default. Hence the behavior without "FORMAT > JSON" > should be corrected IMHO. > > [1] > > https://github.com/apache/

Re: Context-aware/stateful SQL operator functions? (IE adding a stack for number of times an operator has been called in query)

am just curious how the "FORMAT JSON" modifier works. On Sun, Mar 20, 2022 at 10:42 PM Gavin Ray wrote: > Hot damn, you're right -- that doesn't look like it's double-escaped! > I will try to this out ASAP and post an update here, thank you for the tip > =) > > > On

Re: Context-aware/stateful SQL operator functions? (IE adding a stack for number of times an operator has been called in query)

> result of 'format json' is what you want. > > [1] > > https://github.com/apache/calcite/blob/master/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java#L4381 > > > Gavin Ray 于2022年3月19日周六 05:19写道： > > > That sounds very reasonable to me > &g

Re: Context-aware/stateful SQL operator functions? (IE adding a stack for number of times an operator has been called in query)

d fold together multiple nested JSON_ functions. Perhaps > we need to add a new (internal) JSON_ function that can do all of the steps. > > Julian > > > > On Mar 18, 2022, at 8:50 AM, Gavin Ray wrote: > > > > Sorry to beat a dead horse here, but I'm one of those wei

Context-aware/stateful SQL operator functions? (IE adding a stack for number of times an operator has been called in query)

Sorry to beat a dead horse here, but I'm one of those weirdos that gets a lot of use out of Calcite's JSON operators. Calcite's JSON implementation is broken for queries that have more than one depth of JSON object/array calls. The reason is because the operator calls "jsonize()", which parses

Re: Setting the "defaultSchema" of a live CalciteConnection?

statements. > > Julian > > > > > > > > > On Mar 15, 2022, at 11:49 AM, Gavin Ray wrote: > > > > I have a scenario in which I have a rootSchema that holds multiple > > datasource schemas > > There's a tool I'd like to integrate it with, but it

Setting the "defaultSchema" of a live CalciteConnection?

I have a scenario in which I have a rootSchema that holds multiple datasource schemas There's a tool I'd like to integrate it with, but it can't handle (catalog -> schema -> schema) structure I figured an easy way around this would be to iterate the names of the datasource schemas, and then and

Re: Can't find java file for ImmutableCsvProjectTableScanRule

I believe you need to make sure that the "Immutables" annotations processor is set up correctly Like Fan Liya mentioned, that class is generated by the annotations, so you need to configure Gradle/Maven to process them: If you haven't already, make sure to enable Annotation Processors in your IDE

Where in Calcite do I add support for decoding more types from JDBC results? JavaTypeFactoryImpl?

I am trying to fix an error that occurs when de-serializing Avatica ResultSets that contains rows of type "PGObject": https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/util/PGobject.html "PGobject is a class used to describe unknown types An unknown type is any type that is

Re: I would like to fix "CALCITE-4739: json_object pushdown JDBC". Looking through the codebase, not obvious where to start, hints greatly appreciated

teIterable(CalciteMetaImpl.java:578) at org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:569) at org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:184) On Fri, Feb 25, 2022 at 3:43 PM Gavin Ray wrote: > Okay, I have a basic test passing: &

Re: I would like to fix "CALCITE-4739: json_object pushdown JDBC". Looking through the codebase, not obvious where to start, hints greatly appreciated

uot;); SqlWriter.Frame listFrame = writer.startList("", ""); for (int i = 1; i < call.operandCount(); i += 2) { writer.literal("'" + call.operand(i).toString() + "'"); writer.sep(",", true); call.operand(i + 1).unparse(writer, l

Re: I would like to fix "CALCITE-4739: json_object pushdown JDBC". Looking through the codebase, not obvious where to start, hints greatly appreciated

It looks like this can be done through: "SqlDialect#unparseCall(SqlWriter writer, SqlCall call, int leftPrec, int rightPrec)" Hopefully I am on the right track here :sweat_smile:

Re: calcite multi-threading problem

Also I just checked and this is updated on master so it should be solved in next release FWIW: https://github.com/apache/calcite/blob/cbbe5701b7f61d7f8df12d314ba5aabf898c1cae/gradle.properties#L116 On Fri, Feb 25, 2022 at 10:16 AM Gavin Ray wrote: > The fix for this is just to add jan

Re: calcite multi-threading problem

The fix for this is just to add janino and commons-compiler as exclusions in the calcite/avatica dependencies And then add the updated version so that it overwrites them I tested this on your code and it worked Also, please listen to Stamatis/Julian advice org.apache.calcite calcite-core

[jira] [Created] (CALCITE-5022) JSON values from queries against H2 (possibly other) schemas encoded as bytes (IE: "B@6e9a0bea")

Gavin Ray created CALCITE-5022: -- Summary: JSON values from queries against H2 (possibly other) schemas encoded as bytes (IE: "B@6e9a0bea") Key: CALCITE-5022 URL: https://issues.apache.org/jira/browse/CA

I would like to fix "CALCITE-4739: json_object pushdown JDBC". Looking through the codebase, not obvious where to start, hints greatly appreciated

As title implies, as part of a larger effort to fix a couple of things related to JSON support, I'd really like to add support for converting JSON_OBJECT and JSON_ARRAYAGG calls to the right DB-specific syntax: https://issues.apache.org/jira/browse/CALCITE-4739 I thought that maybe this

Re: dynamic reflective schema

> the total seconds since the start of a day? > > > > > > ---Original--- > > From: "xiaobo " > > Date: Thu, Feb 24, 2022 12:20 PM > > To: "dev"; > > Subject: Re: dynamic reflective schema > > > > yes, we call the > > getRowTyp

Re: Why are nested aggregations illegal? Best alternatives?

Well actually, there is one exception -- there's a small bug with the output being improperly escaped but that bug is already on JIRA and seems much easier to approach. https://issues.apache.org/jira/browse/CALCITE-4989 On Wed, Feb 23, 2022 at 6:46 PM Gavin Ray wrote: > Oh wow, that fi

Re: Why are nested aggregations illegal? Best alternatives?

---+ 1 row selected (0.01 seconds) On Wed, Feb 23, 2022 at 6:30 PM Gavin Ray wrote: > Okay, I think maybe I have an idea what is going on > > The parser grammar for "JsonNameAndValue": > > https://github.com/apache/calcite/blob/82dd78a14f6aef2eeec2f9c94978d04b4acc5359/core/s

Re: Why are nested aggregations illegal? Best alternatives?

is context. */ ACCEPT_NON_QUERY, So it's not a bug, but Calcite's parser just doesn't take query expressions for JSON values. I guess that changing this might break a bunch of stuff, and probably is very difficult? On Wed, Feb 23, 2022 at 6:10 PM Gavin Ray wrote: > Sorry, I realized this mi

Re: Why are nested aggregations illegal? Best alternatives?

=2eed69c44fa63adf9830213163ba73d0 MYSQL: https://dbfiddle.uk/?rdbms=mysql_8.0=f94c7957eae4f5ebe4c879c17fbe64ea On Wed, Feb 23, 2022 at 2:59 PM Gavin Ray wrote: > I think this actually is a bug in Calcite's parser or it's interpretation > I tested on H2, Oracle, and MySQL, the below is valid on thos

Re: Why are nested aggregations illegal? Best alternatives?

'description', `todos`.`description` )) FROM `todos` WHERE `todos`.`user_id` = `users`.`id` ) )) FROM `users` WHERE

Re: Why are nested aggregations illegal? Best alternatives?

u declare this a > Calcite bug. > > Julian > > > On Feb 23, 2022, at 09:22, Gavin Ray wrote: > > > > No dice still unfortunately =/ > > > > If it's any easier, I put a self-contained single class reproduction on > > Github: > > > https://gi

Re: Why are nested aggregations illegal? Best alternatives?

at 3:45 AM Julian Hyde wrote: > Try ‘value ((‘ in place of ‘value (‘. > > Julian > > > On Feb 21, 2022, at 9:33 AM, Gavin Ray wrote: > > > > I hadn't thought about the fact that ORM's probably have to solve this > > problem as well > > That is a great

Re: dynamic reflective schema

avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:163) > ~[avatica-core-1.20.0.jar:1.20.0] > at > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:227) > ~[avatica-core-1.20.0.jar:1.20.0] > at com.xsmartware.common.util.SQLUti

Re: Calcite CSV Adapter => SQL Client

Just an FYI -- an ElasticSearch adapter exists: https://calcite.apache.org/docs/elasticsearch_adapter.html https://github.com/apache/calcite/tree/master/elasticsearch On Tue, Feb 22, 2022 at 4:19 PM Oualid wrote: > Hi, > > I am contacting you because I am working on the development of

Re: Why are nested aggregations illegal? Best alternatives?

could look > into these frameworks as well for more inspiration. > > Moreover your approach of decomposing the query into individual parts is > commonly known as the N+1 problem [1]. > > Lastly, keep in mind that you can introduce custom UDF, UDAF functions if > you need more flexibility

Re: "Unable to implement EnumerableNestedLoopJoin in SELECT(a, b, ARRAY(c, d, ARRAY(e)))"

hich made me a bit cautious about merging this > to master. I would definitely like to find some time to review this again. > > Best, > Stamatis > > [1] https://github.com/apache/calcite/pull/2116 > > On Sat, Feb 19, 2022 at 6:17 PM Gavin Ray wrote: > > > Digging into this mo

[jira] [Created] (CALCITE-5016) NPE: "variable $cor0 is not found", "Unable to implement EnumerableCorrelate" in nested ARRAY() query

Gavin Ray created CALCITE-5016: -- Summary: NPE: "variable $cor0 is not found", "Unable to implement EnumerableCorrelate" in nested ARRAY() query Key: CALCITE-5016 URL: https://issues.apache.org

Re: dynamic reflective schema

Ah, you don't want to use ReflectiveSchema, it's a simple schema type meant for easily making test schemas You want to extend from "AbstractSchema" and override the function "Map getTableMap()" For the "Table" type, you probably want to use "JsonScannableTable" The CsvSchema example does exactly

Re: "Unable to implement EnumerableNestedLoopJoin in SELECT(a, b, ARRAY(c, d, ARRAY(e)))"

to offer $250 if anyone could help me fix this or figure out an alternative solution. On Mon, Feb 14, 2022 at 4:26 PM Gavin Ray wrote: > Apologies for the slow reply Ruben, I appreciate your help. > The full stack trace (I was prototyping in sqlline) seems to be more > helpful: >

Re: can we set a default schema for calcite connection to avoid writing schema names in sql

o examples -- one of setting "HR" as default schema, so that the query is just: "SELECT * FROM emps" And another using the rootSchema, so that it is namespaced under "hr.emps" On Sat, Feb 19, 2022 at 10:26 AM Gavin Ray wrote: > I will write full working implementat

Re: can we set a default schema for calcite connection to avoid writing schema names in sql

onnection string > parameters at page: > https://calcite.apache.org/docs/adapter.html > the question is : is a init schema the default schema, if not , can you > add a defaultSchema paramter for the jdbc connection string? > > Regards > > > > > > ----

Re: can we set a default schema for calcite connection to avoid writing schema names in sql

tSchema.add("hr", schema); > > FrameworkConfig builder = Frameworks.newConfigBuilder() > .defaultSchema(rootSchema.getSubSchema("hr")) > .parserConfig(SqlParser.config().withCaseSensitive(false)) > .build(); > > > &g

Re: "Unable to implement EnumerableNestedLoopJoin in SELECT(a, b, ARRAY(c, d, ARRAY(e)))"

2022 at 3:24 PM Gavin Ray wrote: > > > The following query seems to work perfectly: > > > > SELECT > > "houses"."id", > > "houses"."name", > > "houses"."address", > > ARRAY(

"Unable to implement EnumerableNestedLoopJoin in SELECT(a, b, ARRAY(c, d, ARRAY(e)))"

The following query seems to work perfectly: SELECT "houses"."id", "houses"."name", "houses"."address", ARRAY( SELECT "users"."id", "users"."name" FROM "users" WHERE "users"."house_id" = "houses"."id" ) AS

Re: Why are nested aggregations illegal? Best alternatives?

Ah wait nevermind, got excited and spoke too soon. Looking at it more closely, that data isn't correct. At least it's in somewhat the right shape, ha! On Sat, Feb 12, 2022 at 9:57 PM Gavin Ray wrote: > After ~5 hours, I think I may have made some progress =) > > I have this, which

Re: Why are nested aggregations illegal? Best alternatives?

the dog | [{1, John, 1, [{1, 1, Take out the trash}, {2, 1, Watch my favorite show}, { | | 8 | 3 | Feed the cat | [{1, John, 1, [{1, 1, Take out the trash}, {2, 1, Watch my favorite show}, { | +----+---------++

Re: can we set a default schema for calcite connection to avoid writing schema names in sql

we want to set a target > subschema such as RelfectiveShcema to be the default one, and we guess the > default schema setting operation should be done before the connection is > opened. > > > > ------ Original -- > From: "Gavin Ray";; > Se

Re: Why are nested aggregations illegal? Best alternatives?

Nevermind, this is a standard term not something Calcite-specific it seems! https://en.wikipedia.org/wiki/Correlated_subquery On Sat, Feb 12, 2022 at 3:46 PM Gavin Ray wrote: > Forgive my ignorance/lack of experience > > I am somewhat familiar with the ARRAY() function, but not su

Re: Why are nested aggregations illegal? Best alternatives?

ot; mean here? Thanks, as usual Julian On Sat, Feb 12, 2022 at 3:08 PM Julian Hyde wrote: > Correlated ARRAY sub-query? > > > On Feb 12, 2022, at 10:40 AM, Gavin Ray wrote: > > > > Apologies for the delay in replying > > > > This makes things clear and s

Re: Why are nested aggregations illegal? Best alternatives?

ation. Consider > >>>> > >>>> SELECT t.x + 1 AS a, 2 + SUM(t.y + 3) AS b > >>>> FROM t > >>>> GROUP BY t.x > >>>> > >>>> The expressions “t.y” and “t.y + 3” occur before aggregation; “t.x”, > >&

Re: can we set a default schema for calcite connection to avoid writing schema names in sql

Hey Xiabo, You can do this, however it is easiest to do from the "FrameworkConfig" object, like this: import org.apache.calcite.tools.FrameworkConfig // Need to set case-sensitive to false, or else it tries to // look up capitalized table names and fails // // IE: "EMPS" instead of "emps" val

Why are nested aggregations illegal? Best alternatives?

Went to test this query out and found that it can't be performed: SELECT JSON_OBJECT( KEY 'users' VALUE JSON_ARRAYAGG( JSON_OBJECT( KEY 'name' VALUE "users"."name", KEY 'todos' VALUE JSON_ARRAYAGG( JSON_OBJECT(

Re: Failed to import gradle

t; Gradle files. > > > > > On Jan 27, 2022, at 6:55 AM, Gavin Ray wrote: > > > > It didn't work for me either. > > > > I had to remove some of the Gradle tasks, related to checks IIRC. > > What I did that made it work was: > > > > 1. Modify &quo

[jira] [Created] (CALCITE-5005) Calcite fails to build on Linux due to style check because Kotlin files use ␍␊ line-endings

Gavin Ray created CALCITE-5005: -- Summary: Calcite fails to build on Linux due to style check because Kotlin files use ␍␊ line-endings Key: CALCITE-5005 URL: https://issues.apache.org/jira/browse/CALCITE-5005

Re: In over my head with advanced "RelBuilder" query building. Advice/thoughts appreciated

SELECT * FROM "public"."todo" WHERE (("_0_root.base"."id") = ("user_id")) ) AS "_1_root.ar.root.todos.base" ) AS "_3_roo

In over my head with advanced "RelBuilder" query building. Advice/thoughts appreciated

I am trying to work out the RelBuilder calls to make to generate expressions for nested queries Given some query like this: query { user(where: { id: { _gt: 5 } }) { name todos { text } } } I need to return a single row, which is an array of JSON objects, like: [{ name:

Re: Trying to call "PreparedStatement.run()" throws "Cannot invoke "calcite.schema.Schema.getTable(String)" because the return value of "calcite.schema.SchemaPlus.getSubSchema(String)" is null"

nt/purpose of the "name" value is, but it's been related to two things I've hit now, so it's got to be critical. On Mon, Jan 3, 2022 at 3:44 PM Gavin Ray wrote: > Ahhh -- thanks for the code sample Gunnar and the clarification that you > can do it two ways Julian. > > Data-source meta

Some updated benchmarks of GraphQL on Calcite

In case anyone is interested, I have finished the "next leg" of the GraphQL implementation. Now the "DataFetcher" functions (what I would call a "resolver") are auto-generated as well. This makes the GraphQL API actually queryable/executable, rather than what I'd shown before which was

Re: Using Calcite with Python

> think it would be most interesting to integrate Flight with Avatica as > an > > > alternative transport. But it would certainly also be useful to allow > the > > > Arrow adapter to connect to any Flight endpoint. > > > > > > -- > > > Mic

Re: Getting "AssertionError: Rule's description should be unique" after adding JOIN to query. Fails on "optPlanner.changeTraits()"

ps://github.com/apache/calcite/blob/8570cf2b293e9acab9525fbe76709d970b9f7104/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcConvention.java#L65 > > On Mon, Jan 31, 2022 at 10:56 PM Gavin Ray wrote: > > > Not sure if this is helpful, but enabling TRACE logging shows this

Re: How to handle the fact that different databases have different ideas of what a "db"/"schema" are?

f792b331fefeb0ed2106c53/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/JdbcStoragePlugin.java#L301 > > > On Thu, Jan 27, 2022 at 7:38 PM Gavin Ray wrote: > > > The filesystem hierarchy is a great analogy, I understand it much better > > now I think -- t

Re: Getting "AssertionError: Rule's description should be unique" after adding JOIN to query. Fails on "optPlanner.changeTraits()"

,out:ENUMERABLE)] rels [#7] java.lang.AssertionError: Rule's description should be unique; existing rule=JdbcToEnumerableConverterRule(in:JDBC.PUBLIC,out:ENUMERABLE); new rule=JdbcToEnumerableConverterRule(in:JDBC.PUBLIC,out:ENUMERABLE) On Mon, Jan 31, 2022 at 4:42 PM Gavin Ray wrote: > I t

Getting "AssertionError: Rule's description should be unique" after adding JOIN to query. Fails on "optPlanner.changeTraits()"

I took the "FrameworksTest#executeQuery()" code from the Calcite repo here and have been using it: https://github.com/apache/calcite/blob/de847c38f3544f9c7282984f32dc1093bdb2fb60/core/src/test/java/org/apache/calcite/tools/FrameworksTest.java#L444-L477 Have been getting an error I don't

Re: Using Calcite with Python

This is really interesting stuff you've done in the example notebooks Nicola & Michael, I wonder if you could benefit from the recently-released Arrow Flight SQL? https://www.dremio.com/subsurface/arrow-flight-and-arrow-flight-sql-accelerating-data-movement/ I have asked Jacques about this a bit

JaCoCo adds a synthetic method "$jacocoInit" to things, which causes an assertion in org.apache.calcite.rel.metadata.MetadataDef constructor to fail

Have been rewriting my code on this test for a few hours now, when I finally decided to step through the debugger This line is what is throwing an assertion failure: // Handler must have the same methods as Metadata, each method having // additional "subclass-of-RelNode, RelMetadataQuery"

Re: How to handle the fact that different databases have different ideas of what a "db"/"schema" are?

e to look like a one-level or > two-level database, we probably don’t make it particularly easy. > > Julian > > > > On Jan 27, 2022, at 7:25 AM, Gavin Ray wrote: > > > > My RDBMS experience is nearly exclusively Postgres > > While working on this project, I've ma

How to handle the fact that different databases have different ideas of what a "db"/"schema" are?

My RDBMS experience is nearly exclusively Postgres While working on this project, I've made the assumption that the structure of a database is: Database -> Schema -> Table It turns out that this isn't accurate. In MySQL for instance, "Schema" is an alias for "DB". >From the below StackOverflow

Re: Failed to import gradle

It didn't work for me either. I had to remove some of the Gradle tasks, related to checks IIRC. What I did that made it work was: 1. Modify "gradle.properties" in the root of the repo, adding the following line: skipAutostyle=true 2. Modify every instance of "val skipAutostyle by

  1   2   >