Re: Invalid IntervalSQLType

Thanks, Julian. Now it makes sense. Regards, Chathura On Fri, Feb 11, 2022 at 2:02 AM Julian Hyde wrote: > There are two kinds of intervals (in Calcite and in standard SQL). > Days-hours-minutes-seconds-millseconds intervals, and year-month intervals. > The former are represented internally

Re: Allow Cascades driver invoking "derive" on the nodes produced by "passThrough"

Hi Vladimir, +1 for making the rule driver more public. We've faced similar problems in the downstream project. The solution was to copy and paste the TopDownRuleDrive code with small fixes since it was not possible to override the default behavior. -- Roman Kondakov On 11.02.2022 02:50,

Re: [DISCUSS] Assigning reviewers

I agree that automatic assignment is an imperfect tool. It’s difficult to know what functional area (let alone module) a bug or feature belongs to, and even then, who is the “owner” of that area. But it’s worth a try. We have quite a good process going for release managers: people sign up in

Re: Invalid IntervalSQLType

There are two kinds of intervals (in Calcite and in standard SQL). Days-hours-minutes-seconds-millseconds intervals, and year-month intervals. The former are represented internally in milliseconds. The latter are represented in months. This is one area where Postgres does things differently

Re: Invalid IntervalSQLType

Thanks, Stamatis. Below is the original SQL query. select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice*(1-l_discount)) as sum_disc_price, sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge, avg(l_quantity) as avg_qty,

Re: Why are nested aggregations illegal? Best alternatives?

Yes, if you want to do multiple layers of aggregation, use CTEs (WITH) or nested sub-queries. For example, the following is I believe valid standard SQL, and actually computes something useful: WITH q1 AS (SELECT deptno, job, AVG(sal) AS avg_sal FROM emp GROUP BY deptno, job)

Re: Why are nested aggregations illegal? Best alternatives?

I wish you could unsend emails :) Answering my own question, no, because that would return three rows with the average :D On Thu, Feb 10, 2022 at 3:16 PM Justin Swanhart wrote: > Just out of curiosity, is the second level aggregation using AVG in a > window context? It the frame is the whole

Re: Why are nested aggregations illegal? Best alternatives?

Just out of curiosity, is the second level aggregation using AVG in a window context? It the frame is the whole table and it aggregates over it? On Thu, Feb 10, 2022 at 3:12 PM Justin Swanhart wrote: > That is really neat about Oracle. > > The alternative in general is to use a subquery: >

Re: ARRAY_CONCAT does not work

Let’s continue conversation in https://issues.apache.org/jira/browse/CALCITE-4999 . I already pushed back there on the idea that ARRAY_CONCAT’s type derivation was wrong, and Dmitry conceded. > On Feb 10, 2022, at 5:08 AM, Michael Mior

Re: Why are nested aggregations illegal? Best alternatives?

That is really neat about Oracle. The alternative in general is to use a subquery: SELECT avg(avg(sal)) FROM emp GROUP BY deptno; becomes select avg(the_avg) from (select avg(sal) from emp group b deptno) an_alias; or with the_cte as (select avg(sal) x from emp group by deptno) select avg(x)

Re: Why are nested aggregations illegal? Best alternatives?

Some databases, e.g. Oracle, allow TWO levels of nesting: SELECT avg(sal) FROM emp GROUP BY deptno; AVG(SAL) 1,566.67 2,175.00 2,916.65 SELECT avg(avg(sal)) FROM emp GROUP BY deptno; AVG(SUM(SAL)) = 9,675 The first level aggregates by department (returning 3

Re: Contributor role request

Thank you! The tests failed for a strange reason (no JDBC driver was found). I added a test of my own (forgot to do it in the first place). Now the CI seems to be in the same state (a build is 'created', but waiting for an approval). Could you please approve it once more? On Thu, Feb 10, 2022 at

Re: Why are nested aggregations illegal? Best alternatives?

This is a SQL limitation. mysql> select sum(1); ++ | sum(1) | ++ | 1 | ++ 1 row in set (0.00 sec) mysql> select sum(sum(1)); ERROR (HY000): Invalid use of group function On Thu, Feb 10, 2022 at 12:39 PM Gavin Ray wrote: > Went to test this query out and found

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(

Allow Cascades driver invoking "derive" on the nodes produced by "passThrough"

Hi, In the Cascades driver, it is possible to propagate the requests top-down using the "passThrough", method and then notify parents bottom-up about the concrete physical implementations of inputs using the "derive" method. In some optimizers, the valid parent node cannot be created before the

[jira] [Created] (CALCITE-5009) Remove transaparent jdbc connection re-creation code

Istvan Toth created CALCITE-5009: Summary: Remove transaparent jdbc connection re-creation code Key: CALCITE-5009 URL: https://issues.apache.org/jira/browse/CALCITE-5009 Project: Calcite

Re: ARRAY_CONCAT does not work

Exactly. I understand the problem is not CHAR(1) vs CHAR(7), but the record type. That is the point I was trying to make. -- Michael Mior mm...@apache.org Le jeu. 10 févr. 2022 à 07:47, Dmitry Sysolyatin a écrit : > Michael, the problem is not because CHAR(1) and CHAR(7). Calcite can derive >

Re: Contributor role request

You just ask. GitHub implemented this a while ago for first-time contributors because of the potential for abuse. It has been approved. -- Michael Mior mm...@apache.org Le jeu. 10 févr. 2022 à 07:58, Roman Puchkovskiy < roman.puchkovs...@gmail.com> a écrit : > Thanks! > I've published a PR

Re: Contributor role request

Thanks! I've published a PR https://github.com/apache/calcite/pull/2718 , but it seems that someone needs to 'approve running workflows' for the PR. How does one obtain such an approval? Sorry if I missed some obvious mention of it. чт, 10 февр. 2022 г. в 16:24, Michael Mior : > > Welcome! I've

Re: ARRAY_CONCAT does not work

Michael, the problem is not because CHAR(1) and CHAR(7). Calcite can derive common type in this case = CHAR(7) and all will work ok. The problem is that one type is [ ARRAY] and another [ ARRAY]. I see two options for resolving this problem: 1. Allow casting scalar type to RecordType with one

Re: Contributor role request

Welcome! I've added you as a contributor. -- Michael Mior mm...@apache.org Le jeu. 10 févr. 2022 à 07:15, Roman Puchkovskiy < roman.puchkovs...@gmail.com> a écrit : > Hello! I'd like to contribute to the Calcite project, could you please > grant me the Contributor role so that I can assign a

Contributor role request

Hello! I'd like to contribute to the Calcite project, could you please grant me the Contributor role so that I can assign a JIRA issue to myself? My Apache JIRA login is 'rpuch'. Cheers, Roman Puchkovskiy

Re: Visualizing Calcite data in Apache Superset

You can connect to Calcite using JDBC. I'm not familiar with Superset, but a quick glance suggests that it does connect to other JDBC sources, so this seems like a possibility. -- Michael Mior mm...@apache.org Le dim. 30 janv. 2022 à 12:42, Gunnar Morling a écrit : > Hey all, > > I'm looking

[jira] [Created] (CALCITE-5008) Ignore synthetic and static methods in MetadataDef

Roman Puchkovskiy created CALCITE-5008: -- Summary: Ignore synthetic and static methods in MetadataDef Key: CALCITE-5008 URL: https://issues.apache.org/jira/browse/CALCITE-5008 Project: Calcite

Re: ARRAY_CONCAT does not work

The two types in your example are incompatible. One is an array of CHAR(1). The other is an array of records, each with a single CHAR(7) field. -- Michael Mior mm...@apache.org Le ven. 4 févr. 2022 à 11:27, Dmitry Sysolyatin a écrit : > So, the previous case started to work. But I faced with

Re: Invalid IntervalSQLType

I agree with Stamatis, and as always adding a unit test and using git bisect can help spot the exact commit that introduced the change. HTH, Alessandro On Thu, 10 Feb 2022 at 12:37, Stamatis Zampetakis wrote: > Hi Chathura, > > It is difficult to reason about correctness without having the

Re: Invalid IntervalSQLType

Hi Chathura, It is difficult to reason about correctness without having the actual SQL query at hand. The fact that you have milliseconds is not by itself a problem and has to do with the way Calcite internally represents intervals (see comment in [1]). Also from the examples you provided the