[jira] [Commented] (CALCITE-3522) Sql validator limits decimal literals to 64 bits
[ https://issues.apache.org/jira/browse/CALCITE-3522?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17869046#comment-17869046 ] Julian Hyde commented on CALCITE-3522: -- [~mbudiu], After 5 years the subject is still "Sql validator limits decimal literals to 64 bits" I am still confused about whether it is about literals for the DECIMAL SQL type or numeric literals that are represented in base 10. Since you've taken over this case, can you modify the subject so that it matches what you intend to do? > Sql validator limits decimal literals to 64 bits > > > Key: CALCITE-3522 > URL: https://issues.apache.org/jira/browse/CALCITE-3522 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.18.0, 1.36.0 >Reporter: Changbo Shu >Assignee: Mihai Budiu >Priority: Minor > Labels: pull-request-available > Fix For: 1.38.0 > > Attachments: code.png > > > [https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java#L2983] > for example: > create table tbl(f1 double), > f1 stores a double's max value. (1.7976931348623157E308) > long max value is 9223372036854775807. > select * from table where f1=value, if value is greater than long max, > sqlvalidator will throw out of range exception. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6500) Aggregation inside Window clause nodes fails when the query is written with a different syntax
[
https://issues.apache.org/jira/browse/CALCITE-6500?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17868529#comment-17868529
]
Julian Hyde commented on CALCITE-6500:
--
Ah, I see you already did it. Thank you. (I'd use {{noformat}} rather than
{{code}} for the error stack.)
> Aggregation inside Window clause nodes fails when the query is written with a
> different syntax
> --
>
> Key: CALCITE-6500
> URL: https://issues.apache.org/jira/browse/CALCITE-6500
> Project: Calcite
> Issue Type: Bug
>Reporter: Sree Charan Manamala
>Priority: Minor
>
> A query like
> {code:java}
> SELECT
> row_number() over (partition by aggr(col1) order by col2) as c
> FROM table
> {code}
> works fine.
> where as the query when written as
> {code:java}
> SELECT
> row_number() over w as c
> FROM table
> WINDOW w as (partition by aggr(col1) order by col2)
> {code}
> throws
>
> {code:java}
> agg.lookupAggregates for call aggr(`table`.`col1`))
> java.lang.NullPointerException
>
> {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6500) Aggregation inside Window clause nodes fails when the query is written with a different syntax
[
https://issues.apache.org/jira/browse/CALCITE-6500?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17868528#comment-17868528
]
Julian Hyde commented on CALCITE-6500:
--
[~sree.manamala], Jira has a different [markup
format|https://jira.atlassian.com/secure/WikiRendererHelpAction.jspa?section=advanced]
than GitHub. Can you please fix the formatting.
> Aggregation inside Window clause nodes fails when the query is written with a
> different syntax
> --
>
> Key: CALCITE-6500
> URL: https://issues.apache.org/jira/browse/CALCITE-6500
> Project: Calcite
> Issue Type: Bug
>Reporter: Sree Charan Manamala
>Priority: Minor
>
> A query like
>
> {code:java}
> SELECT
> row_number() over (partition by aggr(col1) order by col2) as c
> FROM table
> {code}
> works fine.
> where as
>
> {code:java}
> SELECT
> row_number() over w as c
> FROM table
> WINDOW w as (partition by aggr(col1) order by col2)
> {code}
>
> throws
>
> agg.lookupAggregates for call aggr(`table`.`col1`))
> java.lang.NullPointerException
> ```
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6020) SqlToRelConverter should not replace windowed SUM with equivalent expression using SUM0
[
https://issues.apache.org/jira/browse/CALCITE-6020?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17868504#comment-17868504
]
Julian Hyde commented on CALCITE-6020:
--
The decision whether to convert {{SUM}} to {{SUM0}} should always take into
account whether the group can be empty. If the group can be empty (e.g. in
{{{}GROUP BY (){}}}, or in {{GROUP BY k1, k2}} if the argument to {{SUM}} is
nullable, or in certain kinds of window) then {{SUM}} cannot safely be
converted to {{{}SUM0{}}}, and the config option should not override that.
If we add the config option there should be tests for those kinds of things in
{{{}RelBuilderTest{}}}.
> SqlToRelConverter should not replace windowed SUM with equivalent expression
> using SUM0
> ---
>
> Key: CALCITE-6020
> URL: https://issues.apache.org/jira/browse/CALCITE-6020
> Project: Calcite
> Issue Type: Improvement
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
> Labels: pull-request-available
>
> {{SqlToRelConverter}} replaces {{SUM}} with {{SUM0}} around
> [here|https://github.com/apache/calcite/blob/e1991e08a225ef08c2402ab35c310d88fff3c222/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java#L5885]
> This might have been needed at some point in the past - but I think it will
> be better to leave it as {{SUM}} - as in case there is no {{SUM0}} in the
> system that will be replaced with a {{COALESCE(SUM(...) , 0 )}} to provide it
> - as see
> [here|https://github.com/apache/calcite/blob/e1991e08a225ef08c2402ab35c310d88fff3c222/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java#L1288]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6492) Support aggregate functions which could process DISTINCT natively
[
https://issues.apache.org/jira/browse/CALCITE-6492?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867918#comment-17867918
]
Julian Hyde commented on CALCITE-6492:
--
There are two aspects of an aggregate function that are being conflated here.
One is its syntax (is the user allowed to write DISTINCT? ) and the other is
its algebraic properties (does it always give the same results if the duplicate
values are eliminated?).
The syntax aspect drives the behavior of the validator; the algebraic
properties drive the optimizer. An effort to add new algebraic properties would
be useful. Some more examples of algebraic properties:
*
[singleton|https://calcite.apache.org/javadocAggregate/org/apache/calcite/sql/SqlSingletonAggFunction.html]
is able to generate an expression for a single row
*
[splittable|https://calcite.apache.org/javadocAggregate/org/apache/calcite/sql/SqlSplittableAggFunction.html]
can generate an expression to compensate for double-counting
* whether an aggregate function ignores NULL values (e.g. ARRAY_AGG keeps
nulls)
* whether order of input values is important (e.g. ARRAY_AGG, LISTAGG)
* whether the aggregate function returns NULL when input is empty (e.g. COUNT
returns 0)
* whether the aggregate function commutes, SUM({SUM({a, b}), SUM({c, d, e})})
* if the function doesn't commute, does it have a roll-up function? E.g. COUNT
rolls up using SUM.
Another example of syntax vs algebra. RESPECT NULLS and IGNORE NULLS syntax
only make a difference for aggregate functions (such as ARRAY_AGG) that do not
ignore null values.
> Support aggregate functions which could process DISTINCT natively
> -
>
> Key: CALCITE-6492
> URL: https://issues.apache.org/jira/browse/CALCITE-6492
> Project: Calcite
> Issue Type: Improvement
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>
> This could be usefull if the execution engine natively supports some distinct
> aggregations natively - there is no rewrite necessary for these functions.
> Currently there is support
> [SqlAggFunction#getDistinctOptionality|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java#L187-L189]
> - which have overlaps with this - possibly the closest would be to set it to
> *IGNORED* if its supported natively...however
> * that's a bit misleading as its not IGNORED; but supported...
> * there is also
> [checkArgument|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java#L125]
> which ensures that *distinct* is not accepted in tht case.
> More or less the end result would be to also enhance
> AggregateExpandDistinctAggregatesRule with the ability to ignore aggregates.
> note: In Druid
> * if approximationCountDistinct is disabled ; that [enables a calcite rule
> which rewrites *all* disitnct
> aggregates|https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/CalciteRulesManager.java#L496-L503]
> * in the meantime there are also some aggregate functions which support
> *distinct* natively like
> [string_agg|https://github.com/apache/druid/blob/c9aae9d8e683c0cc9c4687e526b8270f744c57c2/sql/src/main/java/org/apache/druid/sql/calcite/aggregation/builtin/StringSqlAggregator.java#L154]
> - which doesn't need any rewrites
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6492) Support aggregate functions which could process DISTINCT natively
[
https://issues.apache.org/jira/browse/CALCITE-6492?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867918#comment-17867918
]
Julian Hyde edited comment on CALCITE-6492 at 7/23/24 1:44 AM:
---
There are two aspects of an aggregate function that are being conflated here.
One is its syntax (is the user allowed to write DISTINCT? ) and the other is
its algebraic properties (does it always give the same results if the duplicate
values are eliminated?).
The syntax aspect drives the behavior of the validator; the algebraic
properties drive the optimizer. An effort to add new algebraic properties would
be useful. Some more examples of algebraic properties:
*
[singleton|https://calcite.apache.org/javadocAggregate/org/apache/calcite/sql/SqlSingletonAggFunction.html]
is able to generate an expression for a single row
*
[splittable|https://calcite.apache.org/javadocAggregate/org/apache/calcite/sql/SqlSplittableAggFunction.html]
can generate an expression to compensate for double-counting
* whether an aggregate function ignores NULL values (e.g. ARRAY_AGG keeps
nulls)
* whether order of input values is important (e.g. ARRAY_AGG, LISTAGG)
* whether the aggregate function returns NULL when input is empty (e.g. COUNT
returns 0)
* whether the aggregate function commutes, e.g. SUM commutes, and therefore
SUM(\{SUM(\{a, b\}), SUM(\{c, d, e\})\}) is always the same as SUM(\{a, b, c,
d, e\}).
* if the function doesn't commute, does it have a roll-up function? E.g. COUNT
rolls up using SUM.
Another example of syntax vs algebra. RESPECT NULLS and IGNORE NULLS syntax
only make a difference for aggregate functions (such as ARRAY_AGG) that do not
ignore null values.
was (Author: julianhyde):
There are two aspects of an aggregate function that are being conflated here.
One is its syntax (is the user allowed to write DISTINCT? ) and the other is
its algebraic properties (does it always give the same results if the duplicate
values are eliminated?).
The syntax aspect drives the behavior of the validator; the algebraic
properties drive the optimizer. An effort to add new algebraic properties would
be useful. Some more examples of algebraic properties:
*
[singleton|https://calcite.apache.org/javadocAggregate/org/apache/calcite/sql/SqlSingletonAggFunction.html]
is able to generate an expression for a single row
*
[splittable|https://calcite.apache.org/javadocAggregate/org/apache/calcite/sql/SqlSplittableAggFunction.html]
can generate an expression to compensate for double-counting
* whether an aggregate function ignores NULL values (e.g. ARRAY_AGG keeps
nulls)
* whether order of input values is important (e.g. ARRAY_AGG, LISTAGG)
* whether the aggregate function returns NULL when input is empty (e.g. COUNT
returns 0)
* whether the aggregate function commutes, SUM({SUM({a, b}), SUM({c, d, e})})
* if the function doesn't commute, does it have a roll-up function? E.g. COUNT
rolls up using SUM.
Another example of syntax vs algebra. RESPECT NULLS and IGNORE NULLS syntax
only make a difference for aggregate functions (such as ARRAY_AGG) that do not
ignore null values.
> Support aggregate functions which could process DISTINCT natively
> -
>
> Key: CALCITE-6492
> URL: https://issues.apache.org/jira/browse/CALCITE-6492
> Project: Calcite
> Issue Type: Improvement
>Reporter: Zoltan Haindrich
>Assignee: Zoltan Haindrich
>Priority: Major
>
> This could be usefull if the execution engine natively supports some distinct
> aggregations natively - there is no rewrite necessary for these functions.
> Currently there is support
> [SqlAggFunction#getDistinctOptionality|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/sql/SqlAggFunction.java#L187-L189]
> - which have overlaps with this - possibly the closest would be to set it to
> *IGNORED* if its supported natively...however
> * that's a bit misleading as its not IGNORED; but supported...
> * there is also
> [checkArgument|https://github.com/apache/calcite/blob/0deab6f7e0cb4ec63eae8b59477d6f0fadfd11e8/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java#L125]
> which ensures that *distinct* is not accepted in tht case.
> More or less the end result would be to also enhance
> AggregateExpandDistinctAggregatesRule with the ability to ignore aggregates.
> note: In Druid
> * if approximationCountDistinct is disabled ; that [enables a calcite rule
> which rewrites *all* disitnct
> aggregates|https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/calcite/planner/CalciteRulesManager.java#L496-L503]
> * in the meantime there are also some aggregate functions which support
> *distinct* natively
[jira] [Resolved] (CALCITE-2032) Error when implicitly converting character literal to date literal
[
https://issues.apache.org/jira/browse/CALCITE-2032?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-2032.
--
Fix Version/s: 1.38.0
Resolution: Fixed
This has probably been fixed for a long while. In
[8143eba|https://github.com/apache/calcite/commit/8143eba75e1e13cb946c9845ab2bd7c36908583a]
I added a test case, and am now marking this resolved.
> Error when implicitly converting character literal to date literal
> --
>
> Key: CALCITE-2032
> URL: https://issues.apache.org/jira/browse/CALCITE-2032
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Priority: Trivial
> Fix For: 1.38.0
>
>
> The following query implicitly converts a character literal to a date
> literal; it passes validation but fails during code generation.
> {code}
> select *
> from "scott".emp
> WHERE hiredate = '1980-12-17';
> +---+---+---+--+++--++
> | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL| COMM | DEPTNO |
> +---+---+---+--+++--++
> | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
> +---+---+---+--+++--++
> (1 row)
> !ok
> {code}
> throws
> {code}
> Error while compiling generated Java code:
> at org.apache.calcite.avatica.Helper.wrap(Helper.java:37)
> at
> org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:108)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1261)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:330)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:229)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:786)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:640)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:610)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:221)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:603)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:638)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:149)
> ... 9 more
> Caused by: org.codehaus.commons.compiler.CompileException: Line 17, Column
> 46: Incomparable types 'java.lang.Integer' and 'java.lang.String'
> at
> org.codehaus.janino.UnitCompiler.compileError(UnitCompiler.java:10092)
> at
> org.codehaus.janino.UnitCompiler.compileBoolean2(UnitCompiler.java:3080)
> {code}
> I have added the test case to {{misc.iq}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5869) LEAST_RESTRICTIVE does not use inner type of MEASURE for comparisons
[
https://issues.apache.org/jira/browse/CALCITE-5869?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867912#comment-17867912
]
Julian Hyde commented on CALCITE-5869:
--
I revised the fix in
[bb94122|https://github.com/apache/calcite/commit/bb94122fa43df86a2ef57fa375e3dc7792666eac].
> LEAST_RESTRICTIVE does not use inner type of MEASURE for comparisons
>
>
> Key: CALCITE-5869
> URL: https://issues.apache.org/jira/browse/CALCITE-5869
> Project: Calcite
> Issue Type: Bug
>Reporter: Tanner Clary
>Assignee: Tanner Clary
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> If you have a function with return type {{LEAST_RESTRICTIVE}}, that takes two
> arguments, and one of these arguments is an {{INTEGER}} and the other is a
> {{MEASURE}} you should expect that the latter is less restrictive.
> However, the logic in {{SqlTypeUtil#canCastFrom}} does not consider the case
> of measures where the type that should be used for comparisons is nested
> within. Because the {{SqlTypeFamily}} of a measure is {{ANY}}, that method
> will always return {{TRUE}}.
> I have a small fix I will open a PR for, any comments would be appreciated.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-5802) In RelBuilder add method aggregateRex, to allow aggregating complex expressions such as "1 + SUM(x + 2)"
[
https://issues.apache.org/jira/browse/CALCITE-5802?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-5802.
--
Fix Version/s: 1.38.0
Resolution: Fixed
Fixed in
[467e509|https://github.com/apache/calcite/commit/467e509f8a5348ac83534ec46b873b6645524990].
> In RelBuilder add method aggregateRex, to allow aggregating complex
> expressions such as "1 + SUM(x + 2)"
>
>
> Key: CALCITE-5802
> URL: https://issues.apache.org/jira/browse/CALCITE-5802
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.38.0
>
>
> In {{RelBuilder}} add method {{{}aggregateRex{}}}, to allow aggregating
> complex expressions such as "1 + SUM(x + 2)". These expressions are difficult
> because there is an expression ({{{}x + 2{}}}) before the aggregate, then the
> aggregate, then an expression ({{{}1 + sum{}}}) after the aggregate. For
> complex expressions such as this, the translation requires a {{Project}}
> followed by an {{Aggregate}} followed by a {{{}Project{}}}.
> Aggregate functions are not conventionally represented as {{{}RexNode{}}},
> but we allow them in the expression passed to {{{}aggregateRex{}}}: note
> \{{b.call(SqlStdOperatorTable.SUM,}} ... in the code.
> For example, to create the same effect as SQL
> {code:java}
> SELECT deptno,
> deptno + 2 AS d2,
> 3 + SUM(4 + sal) AS s
> FROM emp
> GROUP BY deptno
> {code}
> we use the {{RelBuilder}} code
> {code:java}
> RelBuilder b;
> b.scan("EMP")
> .aggregateRex(b.groupKey(b.field("DEPTNO")),
> b.field("DEPTNO"),
> b.alias(
> b.call(SqlStdOperatorTable.PLUS, b.field("DEPTNO"),
> b.literal(2)),
> "d2"),
> b.alias(
> b.call(SqlStdOperatorTable.PLUS, b.literal(3),
> b.call(SqlStdOperatorTable.SUM,
> b.call(SqlStdOperatorTable.PLUS, b.literal(4),
> b.field("SAL",
> "s"))
> .build();
> {code}
> and the resulting relational expression is
> {noformat}
> LogicalProject(DEPTNO=[$0], d2=[+($0, 2)], s=[+(3, $1)])
> LogicalAggregate(group=[{0}], agg#0=[SUM($1)])
> LogicalProject(DEPTNO=[$7], $f8=[+(4, $5)])
> LogicalTableScan(table=[[scott, EMP]])
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6013) RelBuilder should simplify plan by pruning unused measures
[
https://issues.apache.org/jira/browse/CALCITE-6013?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867911#comment-17867911
]
Julian Hyde commented on CALCITE-6013:
--
I revised the fix in
[5b996bd|https://github.com/apache/calcite/commit/5b996bd595cbe775f7534f14546db66241f32eda].
> RelBuilder should simplify plan by pruning unused measures
> --
>
> Key: CALCITE-6013
> URL: https://issues.apache.org/jira/browse/CALCITE-6013
> Project: Calcite
> Issue Type: Bug
>Reporter: Tanner Clary
>Assignee: Tanner Clary
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> If you have a test like the following (modified from
> {{SqlToRelConverterTest#testMeasureRef}})
> {code:java}
> final String sql = "select deptno as c\n"
> + "from empm\n"
> + "group by deptno";
> fixture()
> .withFactory(c ->
> c.withOperatorTable(t ->
> SqlValidatorTest.operatorTableFor(SqlLibrary.CALCITE)))
> .withCatalogReader(MockCatalogReaderExtended::create)
> .withSql(sql)
> .ok();
> {code}
> The expected results should look something like:
> {code:java}
> LogicalAggregate(group=[{0}])
> LogicalProject(C=[$7])
> LogicalTableScan(table=[[CATALOG, SALES, EMPM]])
> {code}
> Instead it looks like:
> {code:java}
> LogicalAggregate(group=[{0}])
> LogicalProject(C=[$7], COUNT_PLUS_100=[$9], COUNT_TIMES_100=[$10])
> LogicalTableScan(table=[[CATALOG, SALES, EMPM]])
> {code}
> I believe this is due to commit
> [568ce12|https://github.com/apache/calcite/commit/568ce125e6d81fe0815f1317ccaf4d558688d945#diff-5d7c774cd39d94b119eedcf3d361e72406c7864e916736c533bbe8f893ca72bcR3397].
> In essence, the measures should not be added to the projects unless necessary.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-4496) Measure columns ("SELECT ... AS MEASURE")
[
https://issues.apache.org/jira/browse/CALCITE-4496?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-4496.
--
Fix Version/s: 1.38.0
Resolution: Fixed
Fixed in
[66919e4|https://github.com/apache/calcite/commit/66919e4d44a2dc5019b595569bbd9fbfd5d163eb].
> Measure columns ("SELECT ... AS MEASURE")
> -
>
> Key: CALCITE-4496
> URL: https://issues.apache.org/jira/browse/CALCITE-4496
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.38.0
>
>
> In multi-dimensional languages such as MDX, DAX, Tableau, you can define
> calculations in your models that can be re-evaluated in other dimensional
> contexts. (The models are often called cubes, and the calculations are often
> called measures.)
> In SQL, the model is a view (or a sub-query in the FROM clause) but the
> columns are just values. Suppose you have a private {{Employees}} table, a
> {{Departments}} view that rolls {{Employees}} up to department level and has
> an {{averageSalary}} column. Now suppose you wish to roll up
> {{averageSalary}} to the region level. The values that went into
> {{averageSalary}} are not available to you, either directly or indirectly, so
> the best you can do is to average-the-averages.
> In this proposed (and experimental) feature, you can define a special kind of
> column - a measure - in the SELECT list of a view (or sub-query in a FROM
> clause), and it remains a calculation. When a query uses a measure column,
> the calculation is re-evaluated in the context of that query.
> To some extent, this breaches the "black box" property of SQL views.
> Hitherto, a SQL view can be replaced with a table that has the same contents,
> and all queries that use that view will return the same results. That
> property no longer holds. But the view remains a useful "hiding" abstraction,
> and the rows that compose that view cannot be viewed directly.
> Like dimensional models, measures in SQL would allow high-level abstractions
> such as key-performance indicators (KPIs) to be shared and composed. Unlike
> dimensional models, the models remain relational, namely, it is still
> possible to enumerate and count the rows in a model.
> Consider the following view and query that uses it:
> {code:sql}
> CREATE VIEW EmpSummary AS
> SELECT deptno,
> job,
> AVG(sal) AS avg_sal,
> AVG(sal) AS MEASURE avg_sal_measure,
> COUNT(*) + 1 AS MEASURE count_plus_one_measure
> FROM Emp
> GROUP BY deptno, job;
> SELECT deptno,
> AVG(avg_sal) AS a1,
> AGGREGATE(avg_sal_measure) AS a2,
> AGGREGATE(count_plus_one_measure) AS c1
> FROM EmpSummary
> GROUP BY deptno;{code}
> Note that there is a special aggregate function, {{AGGREGATE}}, that rolls up
> measures. Columns {{a1}} and {{a2}} will contain different values; the first
> averages the averages, and the second computes the average from the raw data.
> Column {{c1}} will return the number of employees in each department plus
> one, not rolling up the "plus one" for each distinct job in the department.
> This is just a brief sketch illustrating the purpose of measures. This
> feature is experimental, the syntax will no doubt change, and much of the
> semantics (for example, what expressions are valid as measures, whether
> measures remain measures they appear in the SELECT clause of an enclosing
> query, and what is the "context" in which a measure is evaluated) need to be
> ironed out.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6266) SqlValidatorException with LATERAL TABLE and JOIN
[
https://issues.apache.org/jira/browse/CALCITE-6266?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867908#comment-17867908
]
Julian Hyde commented on CALCITE-6266:
--
I've submitted a PR with a better/more general fix:
https://github.com/apache/calcite/pull/3871. Please review.
> SqlValidatorException with LATERAL TABLE and JOIN
> -
>
> Key: CALCITE-6266
> URL: https://issues.apache.org/jira/browse/CALCITE-6266
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.31.0, 1.32.0, 1.33.0, 1.34.0, 1.35.0, 1.36.0
>Reporter: Jeyhun Karimov
>Assignee: Julian Hyde
>Priority: Critical
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> I have the following test in SqlValidatorTest.java
> {code:java}
> @Test void test() {
> sql("select * from dept, lateral table(ramp(deptno)) CROSS JOIN (VALUES
> ('A'), ('B'))")
> .ok();
> }
> {code}
> This test passes on Calcite {{calcite-1.29.0}} and {{calcite-1.30.0}} but
> fails on other releases, including the main branch
> (c774c313a81d01c4e3e77cf296d04839c5ab04c0). The exception is:
> {code:java}
> org.opentest4j.AssertionFailedError: Validator threw unexpected exception;
> query [select * from dept, lateral table(ramp(deptno)) CROSS JOIN (VALUES
> ('A'), ('B'))]; exception [Column 'DEPTNO' not found in any table]; class
> [class org.apache.calcite.sql.validate.SqlValidatorException]; pos [line 1
> col 40 thru line 1 col 40]
> at org.junit.jupiter.api.AssertionUtils.fail(AssertionUtils.java:38)
> at org.junit.jupiter.api.Assertions.fail(Assertions.java:135)
> at org.apache.calcite.sql.test.SqlTests.checkEx(SqlTests.java:352)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.assertExceptionIsThrown(AbstractSqlTester.java:112)
> at
> org.apache.calcite.test.SqlValidatorFixture.ok(SqlValidatorFixture.java:191)
> at
> org.apache.calcite.test.SqlValidatorTest.jey2(SqlValidatorTest.java:284)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:566)
> at
> org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)
> at
> org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
> at
> org.junit.jupiter.engine.extension.SameThreadTimeoutInvocation.proceed(SameThreadTimeoutInvocation.java:45)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:92)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:86)
> at
> org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:217)
> at
> org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
> at
> org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:213)
> at
> org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:138)
> at
>
[jira] [Commented] (CALCITE-4496) Measure columns ("SELECT ... AS MEASURE")
[
https://issues.apache.org/jira/browse/CALCITE-4496?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867851#comment-17867851
]
Julian Hyde commented on CALCITE-4496:
--
Thanks to everyone who reviewed the PR. I've fixed all actionable comments. I
intend to merge shortly.
This is a work-in-progress, so I will welcome more feedback after it has landed
in main.
> Measure columns ("SELECT ... AS MEASURE")
> -
>
> Key: CALCITE-4496
> URL: https://issues.apache.org/jira/browse/CALCITE-4496
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Julian Hyde
>Priority: Major
>
> In multi-dimensional languages such as MDX, DAX, Tableau, you can define
> calculations in your models that can be re-evaluated in other dimensional
> contexts. (The models are often called cubes, and the calculations are often
> called measures.)
> In SQL, the model is a view (or a sub-query in the FROM clause) but the
> columns are just values. Suppose you have a private {{Employees}} table, a
> {{Departments}} view that rolls {{Employees}} up to department level and has
> an {{averageSalary}} column. Now suppose you wish to roll up
> {{averageSalary}} to the region level. The values that went into
> {{averageSalary}} are not available to you, either directly or indirectly, so
> the best you can do is to average-the-averages.
> In this proposed (and experimental) feature, you can define a special kind of
> column - a measure - in the SELECT list of a view (or sub-query in a FROM
> clause), and it remains a calculation. When a query uses a measure column,
> the calculation is re-evaluated in the context of that query.
> To some extent, this breaches the "black box" property of SQL views.
> Hitherto, a SQL view can be replaced with a table that has the same contents,
> and all queries that use that view will return the same results. That
> property no longer holds. But the view remains a useful "hiding" abstraction,
> and the rows that compose that view cannot be viewed directly.
> Like dimensional models, measures in SQL would allow high-level abstractions
> such as key-performance indicators (KPIs) to be shared and composed. Unlike
> dimensional models, the models remain relational, namely, it is still
> possible to enumerate and count the rows in a model.
> Consider the following view and query that uses it:
> {code:sql}
> CREATE VIEW EmpSummary AS
> SELECT deptno,
> job,
> AVG(sal) AS avg_sal,
> AVG(sal) AS MEASURE avg_sal_measure,
> COUNT(*) + 1 AS MEASURE count_plus_one_measure
> FROM Emp
> GROUP BY deptno, job;
> SELECT deptno,
> AVG(avg_sal) AS a1,
> AGGREGATE(avg_sal_measure) AS a2,
> AGGREGATE(count_plus_one_measure) AS c1
> FROM EmpSummary
> GROUP BY deptno;{code}
> Note that there is a special aggregate function, {{AGGREGATE}}, that rolls up
> measures. Columns {{a1}} and {{a2}} will contain different values; the first
> averages the averages, and the second computes the average from the raw data.
> Column {{c1}} will return the number of employees in each department plus
> one, not rolling up the "plus one" for each distinct job in the department.
> This is just a brief sketch illustrating the purpose of measures. This
> feature is experimental, the syntax will no doubt change, and much of the
> semantics (for example, what expressions are valid as measures, whether
> measures remain measures they appear in the SELECT clause of an enclosing
> query, and what is the "context" in which a measure is evaluated) need to be
> ironed out.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6493) Improve Calcite development documentation
[ https://issues.apache.org/jira/browse/CALCITE-6493?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867845#comment-17867845 ] Julian Hyde commented on CALCITE-6493: -- One way to make things consistent is to modify LintTest. It can verify code (both .java and .md) and also commit messages. Note that the rules for class names (e.g. MysqlSqlDialect) are different from the standalone word (MySQL). > Improve Calcite development documentation > - > > Key: CALCITE-6493 > URL: https://issues.apache.org/jira/browse/CALCITE-6493 > Project: Calcite > Issue Type: Improvement > Components: site >Affects Versions: 1.37.0 >Reporter: Caican Cai >Priority: Minor > Labels: pull-request-available > > I noticed that in the jira submission process (like me), some terms are not > standardized, such as Mysql -> MySQL, arrow -> Spark. Thanks Julian for the > reminder > I think we can explain it in the document -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6266) SqlValidatorException with LATERAL TABLE and JOIN
[
https://issues.apache.org/jira/browse/CALCITE-6266?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866849#comment-17866849
]
Julian Hyde edited comment on CALCITE-6266 at 7/20/24 12:08 AM:
I tried a similar query on Postgres:
{code}
with dept (deptno, dname) as (values (10,'ACCOUNTING'),(20,'RESEARCH')),
emp (empno, deptno, ename) as (values (7369, 20, 'SMITH'), (7782, 10,
'CLARK'), (7499, 30, 'ALLEN'))
select *
from dept,
lateral (select * from emp where emp.deptno = dept.deptno) as emp
cross join (values ('A'), ('B')) as v (v);
{code}
It's valid on Postgres, fails on Calcite. So I now agree that this is a bug.
Assigning to myself.
Note that this query uses LATERAL but its uses a correlated subquery rather
than TABLE (table function). The problem is the associativity (strength) of the
comma join operator versus 'cross join'. Cross join is 'winning' and it should
not be.
Here's a dev branch:
https://github.com/julianhyde/calcite/tree/6266-lateral-cross-join
was (Author: julianhyde):
I tried a similar query on Postgres:
{code}
with dept (deptno, dname) as (values (10,'ACCOUNTING'),(20,'RESEARCH')),
emp (empno, deptno, ename) as (values (7369, 20, 'SMITH'), (7782, 10,
'CLARK'), (7499, 30, 'ALLEN'))
select *
from dept,
lateral (select * from emp where emp.deptno = dept.deptno) as emp
cross join (values ('A'), ('B')) as v (v);
{code}
It's valid on Postgres, fails on Calcite. So I now agree that this is a bug.
Assigning to myself.
Here's a dev branch:
https://github.com/julianhyde/calcite/tree/6266-lateral-cross-join
> SqlValidatorException with LATERAL TABLE and JOIN
> -
>
> Key: CALCITE-6266
> URL: https://issues.apache.org/jira/browse/CALCITE-6266
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.31.0, 1.32.0, 1.33.0, 1.34.0, 1.35.0, 1.36.0
>Reporter: Jeyhun Karimov
>Assignee: Julian Hyde
>Priority: Critical
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> I have the following test in SqlValidatorTest.java
> {code:java}
> @Test void test() {
> sql("select * from dept, lateral table(ramp(deptno)) CROSS JOIN (VALUES
> ('A'), ('B'))")
> .ok();
> }
> {code}
> This test passes on Calcite {{calcite-1.29.0}} and {{calcite-1.30.0}} but
> fails on other releases, including the main branch
> (c774c313a81d01c4e3e77cf296d04839c5ab04c0). The exception is:
> {code:java}
> org.opentest4j.AssertionFailedError: Validator threw unexpected exception;
> query [select * from dept, lateral table(ramp(deptno)) CROSS JOIN (VALUES
> ('A'), ('B'))]; exception [Column 'DEPTNO' not found in any table]; class
> [class org.apache.calcite.sql.validate.SqlValidatorException]; pos [line 1
> col 40 thru line 1 col 40]
> at org.junit.jupiter.api.AssertionUtils.fail(AssertionUtils.java:38)
> at org.junit.jupiter.api.Assertions.fail(Assertions.java:135)
> at org.apache.calcite.sql.test.SqlTests.checkEx(SqlTests.java:352)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.assertExceptionIsThrown(AbstractSqlTester.java:112)
> at
> org.apache.calcite.test.SqlValidatorFixture.ok(SqlValidatorFixture.java:191)
> at
> org.apache.calcite.test.SqlValidatorTest.jey2(SqlValidatorTest.java:284)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:566)
> at
> org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)
> at
> org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
> at
> org.junit.jupiter.engine.extension.SameThreadTimeoutInvocation.proceed(SameThreadTimeoutInvocation.java:45)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93)
>
[jira] [Commented] (CALCITE-6266) SqlValidatorException with LATERAL TABLE and JOIN
[
https://issues.apache.org/jira/browse/CALCITE-6266?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867400#comment-17867400
]
Julian Hyde commented on CALCITE-6266:
--
[~Sergey Nuyanzin], I see you submitted a PR from your calcite6266 branch but
you have a newer
[calcite6266_3|https://github.com/snuyanzin/calcite/tree/calcite6266_3] branch.
Should I review that?
> SqlValidatorException with LATERAL TABLE and JOIN
> -
>
> Key: CALCITE-6266
> URL: https://issues.apache.org/jira/browse/CALCITE-6266
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.31.0, 1.32.0, 1.33.0, 1.34.0, 1.35.0, 1.36.0
>Reporter: Jeyhun Karimov
>Assignee: Julian Hyde
>Priority: Critical
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> I have the following test in SqlValidatorTest.java
> {code:java}
> @Test void test() {
> sql("select * from dept, lateral table(ramp(deptno)) CROSS JOIN (VALUES
> ('A'), ('B'))")
> .ok();
> }
> {code}
> This test passes on Calcite {{calcite-1.29.0}} and {{calcite-1.30.0}} but
> fails on other releases, including the main branch
> (c774c313a81d01c4e3e77cf296d04839c5ab04c0). The exception is:
> {code:java}
> org.opentest4j.AssertionFailedError: Validator threw unexpected exception;
> query [select * from dept, lateral table(ramp(deptno)) CROSS JOIN (VALUES
> ('A'), ('B'))]; exception [Column 'DEPTNO' not found in any table]; class
> [class org.apache.calcite.sql.validate.SqlValidatorException]; pos [line 1
> col 40 thru line 1 col 40]
> at org.junit.jupiter.api.AssertionUtils.fail(AssertionUtils.java:38)
> at org.junit.jupiter.api.Assertions.fail(Assertions.java:135)
> at org.apache.calcite.sql.test.SqlTests.checkEx(SqlTests.java:352)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.assertExceptionIsThrown(AbstractSqlTester.java:112)
> at
> org.apache.calcite.test.SqlValidatorFixture.ok(SqlValidatorFixture.java:191)
> at
> org.apache.calcite.test.SqlValidatorTest.jey2(SqlValidatorTest.java:284)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:566)
> at
> org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)
> at
> org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
> at
> org.junit.jupiter.engine.extension.SameThreadTimeoutInvocation.proceed(SameThreadTimeoutInvocation.java:45)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:92)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:86)
> at
> org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:217)
> at
> org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
> at
> org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:213)
> at
>
[jira] [Comment Edited] (CALCITE-6486) Make UnifyRule Implementations Public
[ https://issues.apache.org/jira/browse/CALCITE-6486?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867393#comment-17867393 ] Julian Hyde edited comment on CALCITE-6486 at 7/19/24 5:44 PM: --- This is what I call a "[drill-a-hole|https://issues.apache.org/jira/issues/?jql=project%20%3D%20CALCITE%20AND%20text%20~%20%22%27drill%20a%20hole%27%22]; change. It may be a good idea. But those implementations now become part of our public API and dependent projects (will rightly) complain if we change them in future. If there is a particular type of extensibility that you want to achieve, let's discuss it. Subclassing the existing implementation may be the best way to achieve it (for your project, and for Calcite, and for other projects that depend on Calcite) but it may not. was (Author: julianhyde): This is what I call a "drill-a-hole" change. It may be a good idea. But those implementations now become part of our public API and dependent projects (will rightly) complain if we change them in future. If there is a particular type of extensibility that you want to achieve, let's discuss it. Subclassing the existing implementation may be the best way to achieve it (for your project, and for Calcite, and for other projects that depend on Calcite) but it may not. > Make UnifyRule Implementations Public > - > > Key: CALCITE-6486 > URL: https://issues.apache.org/jira/browse/CALCITE-6486 > Project: Calcite > Issue Type: Wish > Components: core >Reporter: kate >Priority: Trivial > Labels: drill-a-hole > > More and more database engines are now using and advancing materialised > views. I've noticed that calcite hasn't been updated on this in a long time. > We should make the rules exposed for external use and optimisation -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-3014) SqlConformanceEnum is hard coded in a few places
[ https://issues.apache.org/jira/browse/CALCITE-3014?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Julian Hyde updated CALCITE-3014: - Labels: drill-a-hole (was: ) > SqlConformanceEnum is hard coded in a few places > > > Key: CALCITE-3014 > URL: https://issues.apache.org/jira/browse/CALCITE-3014 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.19.0 >Reporter: Lai Zhou >Priority: Major > Labels: drill-a-hole > > I found SqlConformanceEnum is hard coded in a few places. > [https://github.com/apache/calcite/blob/ee83efd360793ef4201f4cdfc2af8d837b76ca69/core/src/main/java/org/apache/calcite/rex/RexExecutorImpl.java#L81] > [https://github.com/apache/calcite/blob/72f36a8830afe7f903d8cb32cf547ea484e49fef/core/src/main/java/org/apache/calcite/interpreter/AggregateNode.java#L226] > I think it's not easy to fix them in a generic way. To support different > SQL compatibility modes well, many place of current codebase is possible to > be modified. > It will `drill a hole` to pass the SqlConformance config in the whole process > of one sql query. > May be we can put the SqlConformance config in ThreadLocal, avoiding pass it > frequently. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-4538) Make CalcRelSplitter API public.
[ https://issues.apache.org/jira/browse/CALCITE-4538?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Julian Hyde updated CALCITE-4538: - Labels: drill-a-hole (was: ) > Make CalcRelSplitter API public. > > > Key: CALCITE-4538 > URL: https://issues.apache.org/jira/browse/CALCITE-4538 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Kyle Weaver >Priority: Major > Labels: drill-a-hole > > CalcRelSplitter is a public class, but its constructor is package-private. > We'd like to implement this class in Beam, which would require making the > constructor (and possibly other pieces) public. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6486) Make UnifyRule Implementations Public
[ https://issues.apache.org/jira/browse/CALCITE-6486?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867393#comment-17867393 ] Julian Hyde commented on CALCITE-6486: -- This is what I call a "drill-a-hole" change. It may be a good idea. But those implementations now become part of our public API and dependent projects (will rightly) complain if we change them in future. If there is a particular type of extensibility that you want to achieve, let's discuss it. Subclassing the existing implementation may be the best way to achieve it (for your project, and for Calcite, and for other projects that depend on Calcite) but it may not. > Make UnifyRule Implementations Public > - > > Key: CALCITE-6486 > URL: https://issues.apache.org/jira/browse/CALCITE-6486 > Project: Calcite > Issue Type: Wish > Components: core >Reporter: kate >Priority: Trivial > > More and more database engines are now using and advancing materialised > views. I've noticed that calcite hasn't been updated on this in a long time. > We should make the rules exposed for external use and optimisation -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6486) Make UnifyRule Implementations Public
[ https://issues.apache.org/jira/browse/CALCITE-6486?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Julian Hyde updated CALCITE-6486: - Labels: drill-a-hole (was: ) > Make UnifyRule Implementations Public > - > > Key: CALCITE-6486 > URL: https://issues.apache.org/jira/browse/CALCITE-6486 > Project: Calcite > Issue Type: Wish > Components: core >Reporter: kate >Priority: Trivial > Labels: drill-a-hole > > More and more database engines are now using and advancing materialised > views. I've noticed that calcite hasn't been updated on this in a long time. > We should make the rules exposed for external use and optimisation -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6293) Support OR condition in Arrow adapter
[
https://issues.apache.org/jira/browse/CALCITE-6293?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867390#comment-17867390
]
Julian Hyde commented on CALCITE-6293:
--
A related discussion, happening currently in CALCITE-6467, is how to represent
large IN expressions efficiently. Since few algorithms can do better than
{{O(N)}}, my rule of thumb for a good RexNode representation is "smaller is
better". Which rules out DNF, CNF.
> Support OR condition in Arrow adapter
> -
>
> Key: CALCITE-6293
> URL: https://issues.apache.org/jira/browse/CALCITE-6293
> Project: Calcite
> Issue Type: Sub-task
> Components: arrow-adapter
>Reporter: hongyu guo
>Assignee: Tim Grein
>Priority: Major
>
> For example
> {code:java}
> String sql = "select \"intField\", \"stringField\"\n"
> + "from arrowdata\n"
> + "where \"intField\"=12 or \"stringField\"='12'"; {code}
> will throw an exception:
> {code:java}
> java.lang.AssertionError: cannot translate OR(=($0, 12), =($1, '12'))
> at
> org.apache.calcite.adapter.arrow.ArrowTranslator.translateMatch(ArrowTranslator.java:70)
> at
> org.apache.calcite.adapter.arrow.ArrowFilter.(ArrowFilter.java:43)
> at
> org.apache.calcite.adapter.arrow.ArrowRules$ArrowFilterRule.convert(ArrowRules.java:97)
> at
> org.apache.calcite.adapter.arrow.ArrowRules$ArrowFilterRule.onMatch(ArrowRules.java:87)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6485) Query with IN-list contains NULL throws Exception
[
https://issues.apache.org/jira/browse/CALCITE-6485?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867376#comment-17867376
]
Julian Hyde commented on CALCITE-6485:
--
Is the test case minimal? Does the problem reproduce with just a VALUES (no
FROM) or just a SELECT (no FROM or VALUES)? If ‘2’, ‘3’ is changed to just ‘2’
(string) or 2 (integer) does the problem disappear?
> Query with IN-list contains NULL throws Exception
> -
>
> Key: CALCITE-6485
> URL: https://issues.apache.org/jira/browse/CALCITE-6485
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
>
> {code:java}
> @Test void testInOperation() {
> sql("select 1 in (null, '2', '3') as f0 from (values (true, true,
> true))").ok();
> }
> {code}
> {code:java}
> Conversion to relational algebra failed to preserve datatypes:
> validated type:
> RecordType(BOOLEAN NOT NULL F0) NOT NULL
> converted type:
> RecordType(BOOLEAN F0) NOT NULL
> rel:
> LogicalValues(tuples=[[{ null }]]) {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6480) OracleDialect does not support CASE WHEN returning boolean
[
https://issues.apache.org/jira/browse/CALCITE-6480?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867127#comment-17867127
]
Julian Hyde commented on CALCITE-6480:
--
[~suibianwanwan33], Yes, but add parentheses. Thus the true literal becomes SQL
to '(1=1)'.
You should check that 'true = false' becomes '(1=1)=(1=0)'. We wouldn't want
'1=1=1=0', because that is poorly typed.
> OracleDialect does not support CASE WHEN returning boolean
> --
>
> Key: CALCITE-6480
> URL: https://issues.apache.org/jira/browse/CALCITE-6480
> Project: Calcite
> Issue Type: Wish
> Components: core
>Reporter: kate
>Priority: Minor
>
> Our requirement is to use Calcite to translate queries into different
> dialects. During validation in the TPC-DS scenario, we found that {{Oracle}}
> does not support SQL statements like
> {code:java}
> SELECT * FROM xxx
> WHERE CASE WHEN a > 10 THEN b < 5 ELSE c > 0 END;{code}
> Therefore, we hope to remove such predicates at the dialect like Oracle.
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6452) Scalar sub-query that uses IS NOT DISTINCT FROM returns incorrect result
[
https://issues.apache.org/jira/browse/CALCITE-6452?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867117#comment-17867117
]
Julian Hyde edited comment on CALCITE-6452 at 7/18/24 9:40 PM:
---
You're right. The value should be 1 for Allen, Martin, Turner, Ward (who have
distinct, not-null commission values) and 10 for the rest (who have null
commission values).
Confirmed on Postgres sqlfiddle.com:
{code:java}
with dept (deptno,dname,location) as
(VALUES(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON')),
emp (empno, ename, job, mgrno, hiredate, sal, comm, deptno) as
(VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30),
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30),
(7566,'JONES','MANAGER',7839,'1981-02-04',2975.00,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30),
(7698,'BLAKE','MANAGER',7839,'1981-01-05',2850.00,NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000.00,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,0.00,30),
(7876,'ADAMS','CLERK',7788,'1987-05-23',1100.00,NULL,20),
(7900,'JAMES','CLERK',7698,'1981-12-03',950.00,NULL,30),
(7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,NULL,20),
(7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,NULL,10))
select e.ename,
(select count(*)
from emp as f
where f.comm is not distinct from e.comm) as c
from emp as e; {code}
was (Author: julianhyde):
You're right. The value should be 1 for Allen, Martin, Turner, Ward (who have
distinct, not-null commission values) and 10 for the rest (who have null
commission values).
> Scalar sub-query that uses IS NOT DISTINCT FROM returns incorrect result
>
>
> Key: CALCITE-6452
> URL: https://issues.apache.org/jira/browse/CALCITE-6452
> Project: Calcite
> Issue Type: Improvement
>Reporter: Julian Hyde
>Assignee: TJ Banghart
>Priority: Major
>
> Scalar sub-query that uses an {{IS NOT DISTINCT FROM}} condition returns
> incorrect result. For example,
> {code:java}
> select e.ename,
> (select count(*)
> from emp as f
> where f.comm is not distinct from e.comm) as c
> from emp as e{code}
> returns
> {noformat}
> +++
> | ENAME | C |
> +++
> | ADAMS | 0 |
> | ALLEN | 1 |
> | BLAKE | 0 |
> | CLARK | 0 |
> | FORD | 0 |
> | JAMES | 0 |
> | JONES | 0 |
> | KING | 0 |
> | MARTIN | 1 |
> | MILLER | 0 |
> | SCOTT | 0 |
> | SMITH | 0 |
> | TURNER | 1 |
> | WARD | 1 |
> +++
> (14 rows)
> {noformat}
> but should return
> {noformat}
> +++
> | ENAME | C |
> +++
> | ADAMS | 0 |
> | ALLEN | 10 |
> | BLAKE | 0 |
> | CLARK | 0 |
> | FORD | 0 |
> | JAMES | 0 |
> | JONES | 0 |
> | KING | 0 |
> | MARTIN | 10 |
> | MILLER | 0 |
> | SCOTT | 0 |
> | SMITH | 0 |
> | TURNER | 10 |
> | WARD | 10 |
> +++
> (14 rows)
> {noformat}
> Also, and perhaps related, if we add a {{WHERE}} to the above query, like
> this:
> {code:java}
> select e.ename,
> (select count(*)
> from emp as f
> where f.comm is not distinct from e.comm) as c
> from emp as e
> where e.deptno = 10{code}
> Calcite throws:
> {noformat}
> Caused by: java.lang.ArrayIndexOutOfBoundsException: Index 6 out of bounds
> for length 5
> > at
> > com.google.common.collect.RegularImmutableList.get(RegularImmutableList.java:77)
> > at org.apache.calcite.rex.RexBuilder.makeInputRef(RexBuilder.java:1037)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator$AdjustProjectForCountAggregateRule.lambda$onMatch2$4(RelDecorrelator.java:2679)
> {noformat}
> I ran into this error while trying to rewrite queries that had measures and
> used {{ROLLUP}}. (Since {{ROLLUP}} will generate group keys whose value is
> NULL, we cannot use regular {{=}} when doing a self-join.)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6452) Scalar sub-query that uses IS NOT DISTINCT FROM returns incorrect result
[
https://issues.apache.org/jira/browse/CALCITE-6452?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867117#comment-17867117
]
Julian Hyde commented on CALCITE-6452:
--
You're right. The value should be 1 for Allen, Martin, Turner, Ward (who have
distinct, not-null commission values) and 10 for the rest (who have null
commission values).
> Scalar sub-query that uses IS NOT DISTINCT FROM returns incorrect result
>
>
> Key: CALCITE-6452
> URL: https://issues.apache.org/jira/browse/CALCITE-6452
> Project: Calcite
> Issue Type: Improvement
>Reporter: Julian Hyde
>Assignee: TJ Banghart
>Priority: Major
>
> Scalar sub-query that uses an {{IS NOT DISTINCT FROM}} condition returns
> incorrect result. For example,
> {code:java}
> select e.ename,
> (select count(*)
> from emp as f
> where f.comm is not distinct from e.comm) as c
> from emp as e{code}
> returns
> {noformat}
> +++
> | ENAME | C |
> +++
> | ADAMS | 0 |
> | ALLEN | 1 |
> | BLAKE | 0 |
> | CLARK | 0 |
> | FORD | 0 |
> | JAMES | 0 |
> | JONES | 0 |
> | KING | 0 |
> | MARTIN | 1 |
> | MILLER | 0 |
> | SCOTT | 0 |
> | SMITH | 0 |
> | TURNER | 1 |
> | WARD | 1 |
> +++
> (14 rows)
> {noformat}
> but should return
> {noformat}
> +++
> | ENAME | C |
> +++
> | ADAMS | 0 |
> | ALLEN | 10 |
> | BLAKE | 0 |
> | CLARK | 0 |
> | FORD | 0 |
> | JAMES | 0 |
> | JONES | 0 |
> | KING | 0 |
> | MARTIN | 10 |
> | MILLER | 0 |
> | SCOTT | 0 |
> | SMITH | 0 |
> | TURNER | 10 |
> | WARD | 10 |
> +++
> (14 rows)
> {noformat}
> Also, and perhaps related, if we add a {{WHERE}} to the above query, like
> this:
> {code:java}
> select e.ename,
> (select count(*)
> from emp as f
> where f.comm is not distinct from e.comm) as c
> from emp as e
> where e.deptno = 10{code}
> Calcite throws:
> {noformat}
> Caused by: java.lang.ArrayIndexOutOfBoundsException: Index 6 out of bounds
> for length 5
> > at
> > com.google.common.collect.RegularImmutableList.get(RegularImmutableList.java:77)
> > at org.apache.calcite.rex.RexBuilder.makeInputRef(RexBuilder.java:1037)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator$AdjustProjectForCountAggregateRule.lambda$onMatch2$4(RelDecorrelator.java:2679)
> {noformat}
> I ran into this error while trying to rewrite queries that had measures and
> used {{ROLLUP}}. (Since {{ROLLUP}} will generate group keys whose value is
> NULL, we cannot use regular {{=}} when doing a self-join.)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6293) Support OR condition in Arrow adapter
[
https://issues.apache.org/jira/browse/CALCITE-6293?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17867054#comment-17867054
]
Julian Hyde commented on CALCITE-6293:
--
We don't require DNF, for the reasons [~mbudiu] mentioned. In my experience,
various normal/canonical forms may be good for particular tasks but no form is
good for all purposes.
Our assumptions about RexNodes (i.e. requirements we place on people handing us
RexNodes) are very minimal, including that ANDs and ORs are flattened (that is,
"a OR b OR c" is represented as "OR(a, b, c)" rather than "OR(a, OR(b, c))" or
"OR(OR(a, b), c)") and that each argument to a function/operator is the exact
type required by the operator (i.e. no implicit casts).
If you want the RexNode to be in a particular format, there are plenty of
functions in RexUtil and RexSimplify to get it into that format. I don't
recommend DNF or CNF. You should consider converting calls to SEARCH using Sarg
literals (see CALCITE-4173). They are well suited for column formats like
Arrow. Expressions like 'x IS NULL OR x IN (2, 3, 5, 8)' are very compact and
efficient in Sarg form.
> Support OR condition in Arrow adapter
> -
>
> Key: CALCITE-6293
> URL: https://issues.apache.org/jira/browse/CALCITE-6293
> Project: Calcite
> Issue Type: Sub-task
> Components: arrow-adapter
>Reporter: hongyu guo
>Assignee: Tim Grein
>Priority: Major
>
> For example
> {code:java}
> String sql = "select \"intField\", \"stringField\"\n"
> + "from arrowdata\n"
> + "where \"intField\"=12 or \"stringField\"='12'"; {code}
> will throw an exception:
> {code:java}
> java.lang.AssertionError: cannot translate OR(=($0, 12), =($1, '12'))
> at
> org.apache.calcite.adapter.arrow.ArrowTranslator.translateMatch(ArrowTranslator.java:70)
> at
> org.apache.calcite.adapter.arrow.ArrowFilter.(ArrowFilter.java:43)
> at
> org.apache.calcite.adapter.arrow.ArrowRules$ArrowFilterRule.convert(ArrowRules.java:97)
> at
> org.apache.calcite.adapter.arrow.ArrowRules$ArrowFilterRule.onMatch(ArrowRules.java:87)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6266) SqlValidatorException with LATERAL TABLE and JOIN
[
https://issues.apache.org/jira/browse/CALCITE-6266?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866849#comment-17866849
]
Julian Hyde edited comment on CALCITE-6266 at 7/17/24 8:35 PM:
---
I tried a similar query on Postgres:
{code}
with dept (deptno, dname) as (values (10,'ACCOUNTING'),(20,'RESEARCH')),
emp (empno, deptno, ename) as (values (7369, 20, 'SMITH'), (7782, 10,
'CLARK'), (7499, 30, 'ALLEN'))
select *
from dept,
lateral (select * from emp where emp.deptno = dept.deptno) as emp
cross join (values ('A'), ('B')) as v (v);
{code}
It's valid on Postgres, fails on Calcite. So I now agree that this is a bug.
Assigning to myself.
Here's a dev branch:
https://github.com/julianhyde/calcite/tree/6266-lateral-cross-join
was (Author: julianhyde):
I tried a similar query on Postgres:
{code}
with dept (deptno, dname) as (values (10,'ACCOUNTING'),(20,'RESEARCH')),
emp (empno, deptno, ename) as (values (7369, 20, 'SMITH'), (7782, 10,
'CLARK'), (7499, 30, 'ALLEN'))
select *
from dept,
lateral (select * from emp where emp.deptno = dept.deptno) as emp
cross join (values ('A'), ('B')) as v (v);
{code}
It's valid on Postgres, fails on Calcite. So I now agree that this is a bug.
Assigning to myself.
> SqlValidatorException with LATERAL TABLE and JOIN
> -
>
> Key: CALCITE-6266
> URL: https://issues.apache.org/jira/browse/CALCITE-6266
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.31.0, 1.32.0, 1.33.0, 1.34.0, 1.35.0, 1.36.0
>Reporter: Jeyhun Karimov
>Assignee: Julian Hyde
>Priority: Critical
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> I have the following test in SqlValidatorTest.java
> {code:java}
> @Test void test() {
> sql("select * from dept, lateral table(ramp(deptno)) CROSS JOIN (VALUES
> ('A'), ('B'))")
> .ok();
> }
> {code}
> This test passes on Calcite {{calcite-1.29.0}} and {{calcite-1.30.0}} but
> fails on other releases, including the main branch
> (c774c313a81d01c4e3e77cf296d04839c5ab04c0). The exception is:
> {code:java}
> org.opentest4j.AssertionFailedError: Validator threw unexpected exception;
> query [select * from dept, lateral table(ramp(deptno)) CROSS JOIN (VALUES
> ('A'), ('B'))]; exception [Column 'DEPTNO' not found in any table]; class
> [class org.apache.calcite.sql.validate.SqlValidatorException]; pos [line 1
> col 40 thru line 1 col 40]
> at org.junit.jupiter.api.AssertionUtils.fail(AssertionUtils.java:38)
> at org.junit.jupiter.api.Assertions.fail(Assertions.java:135)
> at org.apache.calcite.sql.test.SqlTests.checkEx(SqlTests.java:352)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.assertExceptionIsThrown(AbstractSqlTester.java:112)
> at
> org.apache.calcite.test.SqlValidatorFixture.ok(SqlValidatorFixture.java:191)
> at
> org.apache.calcite.test.SqlValidatorTest.jey2(SqlValidatorTest.java:284)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:566)
> at
> org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)
> at
> org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
> at
> org.junit.jupiter.engine.extension.SameThreadTimeoutInvocation.proceed(SameThreadTimeoutInvocation.java:45)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
> at
>
[jira] [Updated] (CALCITE-6266) SqlValidatorException with LATERAL TABLE and JOIN
[
https://issues.apache.org/jira/browse/CALCITE-6266?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6266:
-
Fix Version/s: 1.38.0
> SqlValidatorException with LATERAL TABLE and JOIN
> -
>
> Key: CALCITE-6266
> URL: https://issues.apache.org/jira/browse/CALCITE-6266
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.31.0, 1.32.0, 1.33.0, 1.34.0, 1.35.0, 1.36.0
>Reporter: Jeyhun Karimov
>Assignee: Julian Hyde
>Priority: Critical
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> I have the following test in SqlValidatorTest.java
> {code:java}
> @Test void test() {
> sql("select * from dept, lateral table(ramp(deptno)) CROSS JOIN (VALUES
> ('A'), ('B'))")
> .ok();
> }
> {code}
> This test passes on Calcite {{calcite-1.29.0}} and {{calcite-1.30.0}} but
> fails on other releases, including the main branch
> (c774c313a81d01c4e3e77cf296d04839c5ab04c0). The exception is:
> {code:java}
> org.opentest4j.AssertionFailedError: Validator threw unexpected exception;
> query [select * from dept, lateral table(ramp(deptno)) CROSS JOIN (VALUES
> ('A'), ('B'))]; exception [Column 'DEPTNO' not found in any table]; class
> [class org.apache.calcite.sql.validate.SqlValidatorException]; pos [line 1
> col 40 thru line 1 col 40]
> at org.junit.jupiter.api.AssertionUtils.fail(AssertionUtils.java:38)
> at org.junit.jupiter.api.Assertions.fail(Assertions.java:135)
> at org.apache.calcite.sql.test.SqlTests.checkEx(SqlTests.java:352)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.assertExceptionIsThrown(AbstractSqlTester.java:112)
> at
> org.apache.calcite.test.SqlValidatorFixture.ok(SqlValidatorFixture.java:191)
> at
> org.apache.calcite.test.SqlValidatorTest.jey2(SqlValidatorTest.java:284)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:566)
> at
> org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)
> at
> org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
> at
> org.junit.jupiter.engine.extension.SameThreadTimeoutInvocation.proceed(SameThreadTimeoutInvocation.java:45)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:92)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:86)
> at
> org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:217)
> at
> org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
> at
> org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:213)
> at
> org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:138)
> at
> org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:68)
> at
>
[jira] [Commented] (CALCITE-6266) SqlValidatorException with LATERAL TABLE and JOIN
[
https://issues.apache.org/jira/browse/CALCITE-6266?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866849#comment-17866849
]
Julian Hyde commented on CALCITE-6266:
--
I tried a similar query on Postgres:
{code}
with dept (deptno, dname) as (values (10,'ACCOUNTING'),(20,'RESEARCH')),
emp (empno, deptno, ename) as (values (7369, 20, 'SMITH'), (7782, 10,
'CLARK'), (7499, 30, 'ALLEN'))
select *
from dept,
lateral (select * from emp where emp.deptno = dept.deptno) as emp
cross join (values ('A'), ('B')) as v (v);
{code}
It's valid on Postgres, fails on Calcite. So I now agree that this is a bug.
Assigning to myself.
> SqlValidatorException with LATERAL TABLE and JOIN
> -
>
> Key: CALCITE-6266
> URL: https://issues.apache.org/jira/browse/CALCITE-6266
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.31.0, 1.32.0, 1.33.0, 1.34.0, 1.35.0, 1.36.0
>Reporter: Jeyhun Karimov
>Assignee: Julian Hyde
>Priority: Critical
> Labels: pull-request-available
>
> I have the following test in SqlValidatorTest.java
> {code:java}
> @Test void test() {
> sql("select * from dept, lateral table(ramp(deptno)) CROSS JOIN (VALUES
> ('A'), ('B'))")
> .ok();
> }
> {code}
> This test passes on Calcite {{calcite-1.29.0}} and {{calcite-1.30.0}} but
> fails on other releases, including the main branch
> (c774c313a81d01c4e3e77cf296d04839c5ab04c0). The exception is:
> {code:java}
> org.opentest4j.AssertionFailedError: Validator threw unexpected exception;
> query [select * from dept, lateral table(ramp(deptno)) CROSS JOIN (VALUES
> ('A'), ('B'))]; exception [Column 'DEPTNO' not found in any table]; class
> [class org.apache.calcite.sql.validate.SqlValidatorException]; pos [line 1
> col 40 thru line 1 col 40]
> at org.junit.jupiter.api.AssertionUtils.fail(AssertionUtils.java:38)
> at org.junit.jupiter.api.Assertions.fail(Assertions.java:135)
> at org.apache.calcite.sql.test.SqlTests.checkEx(SqlTests.java:352)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.assertExceptionIsThrown(AbstractSqlTester.java:112)
> at
> org.apache.calcite.test.SqlValidatorFixture.ok(SqlValidatorFixture.java:191)
> at
> org.apache.calcite.test.SqlValidatorTest.jey2(SqlValidatorTest.java:284)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.base/java.lang.reflect.Method.invoke(Method.java:566)
> at
> org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)
> at
> org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
> at
> org.junit.jupiter.engine.extension.SameThreadTimeoutInvocation.proceed(SameThreadTimeoutInvocation.java:45)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:92)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:86)
> at
> org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:217)
> at
>
[jira] [Commented] (CALCITE-6480) OracleDialect does not support CASE WHEN returning boolean
[
https://issues.apache.org/jira/browse/CALCITE-6480?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866655#comment-17866655
]
Julian Hyde commented on CALCITE-6480:
--
[~suibianwanwan33], The optimization should be a different jira case, if
somebody actually decides to implement it. We already have logic to optimize
things like “(case when a then 1 when b then 0 when c then 1 end) = 1”, pushing
the “= 1” into each branch of the “case”.
> OracleDialect does not support CASE WHEN returning boolean
> --
>
> Key: CALCITE-6480
> URL: https://issues.apache.org/jira/browse/CALCITE-6480
> Project: Calcite
> Issue Type: Wish
> Components: core
>Reporter: kate
>Priority: Minor
>
> Our requirement is to use Calcite to translate queries into different
> dialects. During validation in the TPC-DS scenario, we found that {{Oracle}}
> does not support SQL statements like
> {code:java}
> SELECT * FROM xxx
> WHERE CASE WHEN a > 10 THEN b < 5 ELSE c > 0 END;{code}
> Therefore, we hope to remove such predicates at the dialect like Oracle.
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6480) OracleDialect does not support CASE WHEN returning boolean
[
https://issues.apache.org/jira/browse/CALCITE-6480?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866654#comment-17866654
]
Julian Hyde commented on CALCITE-6480:
--
[~nobigo] Could we use 1 and 0 rather than ‘true’ and ‘false’?
> OracleDialect does not support CASE WHEN returning boolean
> --
>
> Key: CALCITE-6480
> URL: https://issues.apache.org/jira/browse/CALCITE-6480
> Project: Calcite
> Issue Type: Wish
> Components: core
>Reporter: kate
>Priority: Minor
>
> Our requirement is to use Calcite to translate queries into different
> dialects. During validation in the TPC-DS scenario, we found that {{Oracle}}
> does not support SQL statements like
> {code:java}
> SELECT * FROM xxx
> WHERE CASE WHEN a > 10 THEN b < 5 ELSE c > 0 END;{code}
> Therefore, we hope to remove such predicates at the dialect like Oracle.
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6480) OracleDialect does not support CASE WHEN returning boolean
[
https://issues.apache.org/jira/browse/CALCITE-6480?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866652#comment-17866652
]
Julian Hyde commented on CALCITE-6480:
--
Yes, Oracle traditionally treated BOOLEAN differently from other data types
(e.g. wouldn’t allow booleans in the SELECT clause) and you’d traditionally use
DECODE rather than CASE.
I gather that recent versions of Oracle support BOOLEAN, so the functionality
you are proposing should be turned off if Oracle is above a particular version.
> OracleDialect does not support CASE WHEN returning boolean
> --
>
> Key: CALCITE-6480
> URL: https://issues.apache.org/jira/browse/CALCITE-6480
> Project: Calcite
> Issue Type: Wish
> Components: core
>Reporter: kate
>Priority: Minor
>
> Our requirement is to use Calcite to translate queries into different
> dialects. During validation in the TPC-DS scenario, we found that {{Oracle}}
> does not support SQL statements like
> {code:java}
> SELECT * FROM xxx
> WHERE CASE WHEN a > 10 THEN b < 5 ELSE c > 0 END;{code}
> Therefore, we hope to remove such predicates at the dialect like Oracle.
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6481) Optimize 'VALUES...UNION ALL...VALUES' to a single 'VALUES' when IN-list that the values include NULL is converted to Values
[
https://issues.apache.org/jira/browse/CALCITE-6481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866621#comment-17866621
]
Julian Hyde commented on CALCITE-6481:
--
This might be because null needs an implicit cast to be of the right type, and
some piece of logic doesn’t think that an expression can be converted to a
multi-row Values.
In any case, can you check that RelBuilder does the right thing here. And once
RelBuilder does the right thing, SqlToRelConverter should use it for Values if
it’s not already.
> Optimize 'VALUES...UNION ALL...VALUES' to a single 'VALUES' when IN-list that
> the values include NULL is converted to Values
>
>
> Key: CALCITE-6481
> URL: https://issues.apache.org/jira/browse/CALCITE-6481
> Project: Calcite
> Issue Type: Improvement
>Reporter: xiong duan
>Assignee: xiong duan
>Priority: Major
>
> The SQL:
> {code:java}
> with
> t1(a,y) as (select * from (values (1, 2), (3,
> null),(7369,null),(7499,30),(null, 20),(null, 5)) as t1)
> select *
> from t1
> where (t1.a,t1.y) in ((1, 2), (3, null),(7369,null),(7499,30),(null,
> 20),(null, 5));
> EnumerableHashJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[semi])
> EnumerableValues(tuples=[[{ 1, 2 }, { 3, null }, { 7369, null }, { 7499, 30
> }, { null, 20 }, { null, 5 }]])
> EnumerableUnion(all=[true])
> EnumerableValues(tuples=[[{ 3, null }]])
> EnumerableValues(tuples=[[{ 7369, null }]])
> EnumerableValues(tuples=[[{ null, 20 }]])
> EnumerableValues(tuples=[[{ null, 5 }]])
> EnumerableValues(tuples=[[{ 1, 2 }, { 7499, 30 }]])
> !plan
> with
> t1(a,y) as (select * from (values (1, 2), (3, 5),(7369,6),(7499,30),(2,
> 20),(3, 5)) as t1)
> select *
> from t1
> where (t1.a,t1.y) in ((1, 2), (3, 3),(7369,3),(7499,30),(1, 20),(3, 5));
> EnumerableHashJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[semi])
> EnumerableValues(tuples=[[{ 1, 2 }, { 3, 5 }, { 7369, 6 }, { 7499, 30 }, {
> 2, 20 }, { 3, 5 }]])
> EnumerableValues(tuples=[[{ 1, 2 }, { 3, 3 }, { 7369, 3 }, { 7499, 30 }, {
> 1, 20 }, { 3, 5 }]])
> !plan {code}
> If the IN-list includes NULL, Calcite will convert VALUES to UNION ALL.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6471) Prevent unconditional conversion of sqlNodes to string for null-check messages in SqlToRelConverter
[
https://issues.apache.org/jira/browse/CALCITE-6471?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866093#comment-17866093
]
Julian Hyde commented on CALCITE-6471:
--
Yes, you need to be explicit. When I first read the summary I thought it was a
privacy issue.
> Prevent unconditional conversion of sqlNodes to string for null-check
> messages in SqlToRelConverter
>
>
> Key: CALCITE-6471
> URL: https://issues.apache.org/jira/browse/CALCITE-6471
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: Konstantin Orlov
>Assignee: Konstantin Orlov
>Priority: Minor
>
> Currently, {{SqlToRelConverter}} contains a few places where {{sqlNode}} is
> concatenated to some prefix and passed as plain string rather that message
> supplier as parameter to {{requireNonNull}} method. These are, namely, lines
> 1019, 4031, and 5344 at commit 29c413a3:
> {code:java}
> SqlNode elseOperand =
> requireNonNull(caseNode.getElseOperand(),
> "getElseOperand for " + caseNode);
> {code}
> {code:java}
> protected RelOptTable getTargetTable(SqlNode call) {
> final SqlValidatorNamespace targetNs = getNamespace(call);
> SqlValidatorNamespace namespace;
> if (targetNs.isWrapperFor(SqlValidatorImpl.DmlNamespace.class)) {
> namespace = targetNs.unwrap(SqlValidatorImpl.DmlNamespace.class);
> } else {
> namespace = targetNs.resolve();
> }
> RelOptTable table = SqlValidatorUtil.getRelOptTable(namespace,
> catalogReader, null, null);
> return requireNonNull(table, "no table found for " + call);
> }
> {code}
> {code:java}
> return Pair.of(c, (e, fieldName) -> {
> final int j = requireNonNull(fieldMap.get(fieldName), "field " +
> fieldName);
> return rexBuilder.makeFieldAccess(e, j);
> });
> {code}
> We can shave a few microseconds by simply replacing plain string with message
> supplier.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6471) Prevent unconditional conversion of sqlNodes to string for null-check messages in SqlToRelConverter
[
https://issues.apache.org/jira/browse/CALCITE-6471?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17866086#comment-17866086
]
Julian Hyde commented on CALCITE-6471:
--
I guess you consider this a bug because of performance? If so can you change
the summary and description to frame this as a performance problem.
> Prevent unconditional conversion of sqlNodes to string for null-check
> messages in SqlToRelConverter
>
>
> Key: CALCITE-6471
> URL: https://issues.apache.org/jira/browse/CALCITE-6471
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: Konstantin Orlov
>Priority: Minor
>
> Currently, {{SqlToRelConverter}} contains a few places where {{sqlNode}} is
> concatenated to some prefix and passed as plain string rather that message
> supplier as parameter to {{requireNonNull}} method. These are, namely, lines
> 1019, 4031, and 5344 at commit 29c413a3:
> {code:java}
> SqlNode elseOperand =
> requireNonNull(caseNode.getElseOperand(),
> "getElseOperand for " + caseNode);
> {code}
> {code:java}
> protected RelOptTable getTargetTable(SqlNode call) {
> final SqlValidatorNamespace targetNs = getNamespace(call);
> SqlValidatorNamespace namespace;
> if (targetNs.isWrapperFor(SqlValidatorImpl.DmlNamespace.class)) {
> namespace = targetNs.unwrap(SqlValidatorImpl.DmlNamespace.class);
> } else {
> namespace = targetNs.resolve();
> }
> RelOptTable table = SqlValidatorUtil.getRelOptTable(namespace,
> catalogReader, null, null);
> return requireNonNull(table, "no table found for " + call);
> }
> {code}
> {code:java}
> return Pair.of(c, (e, fieldName) -> {
> final int j = requireNonNull(fieldMap.get(fieldName), "field " +
> fieldName);
> return rexBuilder.makeFieldAccess(e, j);
> });
> {code}
> We can shave a few microseconds by simply replacing plain string with message
> supplier.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6467) Performance of RelMdUtil.checkInputForCollationAndLimit when using `where col in (large literal set)`
[
https://issues.apache.org/jira/browse/CALCITE-6467?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17865519#comment-17865519
]
Julian Hyde commented on CALCITE-6467:
--
Usually Calcite keeps large IN lists as a list, because we know that converting
to OR will cause these kinds of issues. We have a threshold, whose default
value is 20. Has Pinot overridden that threshold? Could the predicate in
question have been represented as an IN list (or as a call to SEARCH)?
> Performance of RelMdUtil.checkInputForCollationAndLimit when using `where col
> in (large literal set)`
> -
>
> Key: CALCITE-6467
> URL: https://issues.apache.org/jira/browse/CALCITE-6467
> Project: Calcite
> Issue Type: Improvement
>Affects Versions: 1.32.0, 1.33.0, 1.34.0, 1.35.0, 1.36.0, 1.37.0, 1.38.0
>Reporter: Gonzalo Ortiz
>Priority: Major
> Attachments: MultistageEngineQuickStart_2024_07_12_111558.jfr,
> image-2024-07-12-15-58-33-504.png
>
>
> Recently we have updated Pinot to use Calcite 1.37. Previously we were using
> 1.31.
> After the upgrade, we have found issues when executing some queries that
> include large IN clauses. Queries like:
> {code:java}
> explain plan for
> SELECT DestCityName
> FROM (
> SELECT DestCityName
> FROM airlineStats
> WHERE DestCityName IN (
> 'a1', 'a2', 'a3', ... 'a300'
> )
> GROUP BY DestCityName
> ) as a
> {code}
> After some debug, we have found that the issue is in one of our custom rules (
> PinotSortExchangeCopyRule) when we call
> `RelMdUtil.checkInputForCollationAndLimit`
> ([link|https://github.com/apache/pinot/blob/dacc6d06907c44e83721454f1090e5f00c824f15/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotSortExchangeCopyRule.java#L64]).
>
> Comparing different Pinot versions, I've found out that the change that is
> causing problems in our scenario is #CALCITE-5036 (see
>
> [https://github.com/apache/calcite/pull/2743)|https://github.com/apache/calcite/pull/2743/files).].
> Specifically, it looks like the problem appears when `RelMdPredicates` tries
> to simplify the expression, which at the time is basically an
> `OR(DestCityName = a1, DestCityName = a2, ...)`.
> `RexSimplify.simplifyOrTerms` negates previous terms in order to apply
> possible optimizations, but in cases like this where we have hundreds of
> literals, that is very expensive. Going more in detail, it looks like most of
> the time is invested in creating new ranges:
> !image-2024-07-12-15-58-33-504.png!
>
> You may find more insights in the attached JFR file
> [^MultistageEngineQuickStart_2024_07_12_111558.jfr][^MultistageEngineQuickStart_2024_07_12_111558.jfr]
>
> I've tried to reproduce the problem with `sqline` but I wasn't able to do so.
> As far as I can see in the code, RelMdUtil.checkInputForCollationAndLimit is
> only called in SortJoinCopyRule, SortJoinTransposeRule and
> SortUnionTransposeRule. I've tried to create a test or JMH benchmark in
> Calcite to try to reproduce the issue, but I don't know codebase well enough.
>
> I don't consider myself an expert on Apache Calcite and I know we are not
> using Calcite in the most standard way (we are slowly migrating from our own
> engine to Calcite), but I'm pretty confident this issue may also affect other
> Calcite usages. At least in the trace I cannot see anything Pinot specific.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5036) `RelMetadataQuery#getPulledUpPredicates` support to analyze constant key for the operator of IS_NOT_DISTINCT_FROM
[
https://issues.apache.org/jira/browse/CALCITE-5036?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17865516#comment-17865516
]
Julian Hyde commented on CALCITE-5036:
--
Adding a call to RexSimplify seems to have caused some performance issues when
the predicate is large. See CALCITE-6467.
> `RelMetadataQuery#getPulledUpPredicates` support to analyze constant key for
> the operator of IS_NOT_DISTINCT_FROM
> -
>
> Key: CALCITE-5036
> URL: https://issues.apache.org/jira/browse/CALCITE-5036
> Project: Calcite
> Issue Type: Improvement
>Reporter: Xurenhe
>Assignee: Xurenhe
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 2h 40m
> Remaining Estimate: 0h
>
> As we know, `IS NOT DISTINCT FROM` is NULL-Safe equal operator.
> The expression of "{*}`a` IS NOT DISTINCT FROM 10{*}" is equal to "{*}(`a` =
> 10) IS TRUE{*}".
> Currently, `RelMetadataQuery#getPulledUpPredicates` could analyze the
> constant from the constant's equal condition in the filter, not support `IS
> NOT DISTINCT FROM`
> {code:java}
> -- sql
> SELECT deptno,
> mgr,
> ename
> FROM emp
> WHERE deptno IS NOT DISTINCT FROM 10{code}
> By the way, `deptno` need be rewritten to the constant project with number of
> 10, when applying the rule of {*}CoreRules#PROJECT_REDUCE_EXPRESSION{*}S
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5913) Support to get functional dependency metadata in RelMetadataQuery
[
https://issues.apache.org/jira/browse/CALCITE-5913?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17865514#comment-17865514
]
Julian Hyde commented on CALCITE-5913:
--
Is someone interested in finishing this?
I think functional dependencies would be really useful, and we should not get
too hung up on the exact API. Get something that delivers the information, and
if there are performance issues we can change the API later.
> Support to get functional dependency metadata in RelMetadataQuery
> -
>
> Key: CALCITE-5913
> URL: https://issues.apache.org/jira/browse/CALCITE-5913
> Project: Calcite
> Issue Type: New Feature
>Reporter: JingDas
>Assignee: JingDas
>Priority: Major
>
> Functional dependency analysis can be applied to various problems in query
> optimization:
> selectivity estimation, estimation of (intermediate) result sizes, *order
> optimization*
> *(in particular sort avoidance),* cost estimation, and various problems in
> the area of semantic query optimization, as said in the book《[Exploiting
> Functional Dependence in Query
> Optimization》|https://cs.uwaterloo.ca/research/tr/2000/11/CS-2000-11.thesis.pdf]
>
> In calcite, it may be metadata that something like 'FunctionalDependency'
> BuiltInMetadata as following:
> {code:java}
> public abstract class BuiltInMetadata {
>// ...
>public interface FunctionalDependency extends Metadata {
>/** Returns whether column is functionally dependent on columns. */
>Boolean functionallyDetermine(ImmutableBitSet columns, int column);
>}
> } {code}
>
> As the above book said, functional dependency analysis is a valuable and
> challenging work. I think support order optimization
> (in particular sort avoidance) by the relevant functional dependency metadata
> firstly, and then get complete functional dependency function step by step.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6467) Performance of RelMdUtil.checkInputForCollationAndLimit when using `where col in (large literal set)`
[
https://issues.apache.org/jira/browse/CALCITE-6467?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17865508#comment-17865508
]
Julian Hyde commented on CALCITE-6467:
--
Some more details from the Pinot side might be useful. If Pinot calls a Calcite
method N times and the method is O(N) now we have a quadratic algorithm - and
it’s difficult to fault either side, but we have to solve the problem
holistically.
> Performance of RelMdUtil.checkInputForCollationAndLimit when using `where col
> in (large literal set)`
> -
>
> Key: CALCITE-6467
> URL: https://issues.apache.org/jira/browse/CALCITE-6467
> Project: Calcite
> Issue Type: Improvement
>Affects Versions: 1.32.0, 1.33.0, 1.34.0, 1.35.0, 1.36.0, 1.37.0, 1.38.0
>Reporter: Gonzalo Ortiz
>Priority: Major
> Attachments: MultistageEngineQuickStart_2024_07_12_111558.jfr,
> image-2024-07-12-15-58-33-504.png
>
>
> Recently we have updated Pinot to use Calcite 1.37. Previously we were using
> 1.31.
> After the upgrade, we have found issues when executing some queries that
> include large IN clauses. Queries like:
> {code:java}
> explain plan for
> SELECT DestCityName
> FROM (
> SELECT DestCityName
> FROM airlineStats
> WHERE DestCityName IN (
> 'a1', 'a2', 'a3', ... 'a300'
> )
> GROUP BY DestCityName
> ) as a
> {code}
> After some debug, we have found that the issue is in one of our custom rules (
> PinotSortExchangeCopyRule) when we call
> `RelMdUtil.checkInputForCollationAndLimit`
> ([link|https://github.com/apache/pinot/blob/dacc6d06907c44e83721454f1090e5f00c824f15/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotSortExchangeCopyRule.java#L64]).
>
> Comparing different Pinot versions, I've found out that the change that is
> causing problems in our scenario is #CALCITE-5036 (see
>
> [https://github.com/apache/calcite/pull/2743)|https://github.com/apache/calcite/pull/2743/files).].
> Specifically, it looks like the problem appears when `RelMdPredicates` tries
> to simplify the expression, which at the time is basically an
> `OR(DestCityName = a1, DestCityName = a2, ...)`.
> `RexSimplify.simplifyOrTerms` negates previous terms in order to apply
> possible optimizations, but in cases like this where we have hundreds of
> literals, that is very expensive. Going more in detail, it looks like most of
> the time is invested in creating new ranges:
> !image-2024-07-12-15-58-33-504.png!
>
> You may find more insights in the attached JFR file
> [^MultistageEngineQuickStart_2024_07_12_111558.jfr][^MultistageEngineQuickStart_2024_07_12_111558.jfr]
>
> I've tried to reproduce the problem with `sqline` but I wasn't able to do so.
> As far as I can see in the code, RelMdUtil.checkInputForCollationAndLimit is
> only called in SortJoinCopyRule, SortJoinTransposeRule and
> SortUnionTransposeRule. I've tried to create a test or JMH benchmark in
> Calcite to try to reproduce the issue, but I don't know codebase well enough.
>
> I don't consider myself an expert on Apache Calcite and I know we are not
> using Calcite in the most standard way (we are slowly migrating from our own
> engine to Calcite), but I'm pretty confident this issue may also affect other
> Calcite usages. At least in the trace I cannot see anything Pinot specific.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6456) The Mysql log function rule LOG(x, base) is equivalent to LOG(base)/LOG(x)
[
https://issues.apache.org/jira/browse/CALCITE-6456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17865238#comment-17865238
]
Julian Hyde edited comment on CALCITE-6456 at 7/11/24 9:54 PM:
---
Can you amend the description with a simple example where MySQL and BigQuery
give different results.
In reference.md, rename the numeric1 and numeric2 parameters for both LOG to
"numeric" and "base". It's just clearer. Similarly in {{SqlFunctions}}.
The {{SqlFunctions.log}} function should not depend on {{SqlLibrary}}. That
seems like a layering violation. Change the argument back to {{int}} (or could
it be {{boolean}}?)
"MySQL" is capitalized as "MySQL", not "Mysql".
was (Author: julianhyde):
Can you amend the description with a simple example where MySQL and BigQuery
give different results.
In reference.md, rename the numeric1 and numeric2 parameters for both LOG to
"numeric" and "base". It's just clearer. Similarly in {{SqlFunctions}}.
The {{SqlFunctions.log}} function should not depend on {{SqlLibrary}}. That
seems like a layering violation. Change the argument back to {{int}} (or could
it be {{boolean}}?)
> The Mysql log function rule LOG(x, base) is equivalent to LOG(base)/LOG(x)
> --
>
> Key: CALCITE-6456
> URL: https://issues.apache.org/jira/browse/CALCITE-6456
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: Caican Cai
>Priority: Major
> Fix For: 1.38.0
>
>
> The fact should be that the log function in mysql should be equivalent to
> LOG(x, base) is equivalent to LOG(x)/LOG(base), and the calculation is not
> the same as bigquery
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6456) The Mysql log function rule LOG(x, base) is equivalent to LOG(base)/LOG(x)
[
https://issues.apache.org/jira/browse/CALCITE-6456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17865238#comment-17865238
]
Julian Hyde commented on CALCITE-6456:
--
Can you amend the description with a simple example where MySQL and BigQuery
give different results.
In reference.md, rename the numeric1 and numeric2 parameters for both LOG to
"numeric" and "base". It's just clearer. Similarly in {{SqlFunctions}}.
The {{SqlFunctions.log}} function should not depend on {{SqlLibrary}}. That
seems like a layering violation. Change the argument back to {{int}} (or could
it be {{boolean}}?)
> The Mysql log function rule LOG(x, base) is equivalent to LOG(base)/LOG(x)
> --
>
> Key: CALCITE-6456
> URL: https://issues.apache.org/jira/browse/CALCITE-6456
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: Caican Cai
>Priority: Major
> Fix For: 1.38.0
>
>
> The fact should be that the log function in mysql should be equivalent to
> LOG(x, base) is equivalent to LOG(x)/LOG(base), and the calculation is not
> the same as bigquery
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6300) Function MAP_VALUES/MAP_KEYS gives exception when mapVauleType and mapKeyType not equals map Biggest mapKeytype or mapValueType
[
https://issues.apache.org/jira/browse/CALCITE-6300?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17865234#comment-17865234
]
Julian Hyde commented on CALCITE-6300:
--
Yes, that's what I'd do. Look at the arguments, deduce that the type of
{{map('foo', cast (2 as tinyint), 'bar', 2)}} is {{map(string, integer, string,
integer): map[string, integer]}}, and implicitly convert eaech parameter to the
right type. In this case, add a widening cast to convert {{cast(2 as tinyint)}}
to type {{integer}}.
> Function MAP_VALUES/MAP_KEYS gives exception when mapVauleType and mapKeyType
> not equals map Biggest mapKeytype or mapValueType
>
>
> Key: CALCITE-6300
> URL: https://issues.apache.org/jira/browse/CALCITE-6300
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Assignee: Caican Cai
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> If we run the expression below in calcite, it will cause exception:
> {code:java}
> map_values(map('foo', cast (2 as tinyint), 'bar', 2)) {code}
> {code:java}
> java.lang.ClassCastException: java.lang.Byte cannot be cast to
> java.lang.Integer
> at
> org.apache.calcite.avatica.util.AbstractCursor$IntAccessor.getInt(AbstractCursor.java:522)
>at
> org.apache.calcite.avatica.util.AbstractCursor$ArrayAccessor.convertValue(AbstractCursor.java:1396)
> at
> org.apache.calcite.avatica.util.AbstractCursor$ArrayAccessor.getObject(AbstractCursor.java:1377)
> at
> org.apache.calcite.avatica.util.AbstractCursor$ArrayAccessor.getArray(AbstractCursor.java:1432)
> at
> org.apache.calcite.avatica.util.AbstractCursor$ArrayAccessor.getString(AbstractCursor.java:1444)
> at
> org.apache.calcite.avatica.AvaticaResultSet.getString(AvaticaResultSet.java:241)
> at org.apache.calcite.util.JdbcTypeImpl$10.get(JdbcTypeImpl.java:112)
> at org.apache.calcite.util.JdbcTypeImpl$10.get(JdbcTypeImpl.java:109) at
> org.apache.calcite.sql.test.ResultCheckers.compareResultSetWithMatcher(ResultCheckers.java:248)
> at
> org.apache.calcite.sql.test.ResultCheckers$MatcherResultChecker.checkResult(ResultCheckers.java:321)
> at
> org.apache.calcite.test.SqlOperatorTest$TesterImpl.check(SqlOperatorTest.java:14300)
> at org.apache.calcite.sql.test.SqlTester.check(SqlTester.java:160) at
> org.apache.calcite.test.SqlOperatorFixtureImpl.lambda$checkScalar$2(SqlOperatorFixtureImpl.java:226)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.forEachQuery(AbstractSqlTester.java:450)
>at
> org.apache.calcite.test.SqlOperatorFixtureImpl.checkScalar(SqlOperatorFixtureImpl.java:225)
> at
> org.apache.calcite.sql.test.SqlOperatorFixture.checkScalar(SqlOperatorFixture.java:229)
> at
> org.apache.calcite.test.SqlOperatorTest.testMapValuesFunc(SqlOperatorTest.java:7276)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
>at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498) at
> org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)
>at
> org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147)
>at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103)
> at
> org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
> at
> org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
> at
>
[jira] [Commented] (CALCITE-6310) Add REGEXP_REPLACE function (enabled in PostgreSQL library)
[
https://issues.apache.org/jira/browse/CALCITE-6310?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17865229#comment-17865229
]
Julian Hyde commented on CALCITE-6310:
--
Would it be possible to obsolete {{class SqlRegexpReplaceFunction}} and just
use {{SqlBasicFunction.create}} for all {{REGEXP_REPLACE}} variants?
> Add REGEXP_REPLACE function (enabled in PostgreSQL library)
> ---
>
> Key: CALCITE-6310
> URL: https://issues.apache.org/jira/browse/CALCITE-6310
> Project: Calcite
> Issue Type: Sub-task
>Reporter: James Duong
>Assignee: Norman Jordan
>Priority: Minor
> Labels: pull-request-available
>
> * There is an existing implementation.
> * PostgreSQL requires supporting an optional extra flags argument
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6310) Add REGEXP_REPLACE function (enabled in PostgreSQL library)
[ https://issues.apache.org/jira/browse/CALCITE-6310?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17865227#comment-17865227 ] Julian Hyde commented on CALCITE-6310: -- Regarding the two-argument version. I get that it is only supported by Redshift. But could we just make the third argument optional in Postgres? > Add REGEXP_REPLACE function (enabled in PostgreSQL library) > --- > > Key: CALCITE-6310 > URL: https://issues.apache.org/jira/browse/CALCITE-6310 > Project: Calcite > Issue Type: Sub-task >Reporter: James Duong >Assignee: Norman Jordan >Priority: Minor > Labels: pull-request-available > > * There is an existing implementation. > * PostgreSQL requires supporting an optional extra flags argument -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6465) Rework code generator
[
https://issues.apache.org/jira/browse/CALCITE-6465?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17865194#comment-17865194
]
Julian Hyde commented on CALCITE-6465:
--
I would support this.
Timeline could be as follows. It could start off as optional, later it would be
the default (preferred) code generator, and later it could become the only code
generator. Before step 2, it would need to be spun out of Flink. (Flink depends
on Calcite, and we don't want a cyclic dependency between Calcite and Flink.)
Calcite's current generator ('enumerable') generates Java for both relational
expressions (classes that implement Iterator, i.e. the Volcano execution model)
and scalar expressions. Do you see this generator doing relational expressions
or just scalar expressions?
Would the generator make any assumptions about the data format, e.g. that an
incoming row is a java.util.List and that a TIMESTAMP value is represented as a
Java java.lang.Long.
Would the generator make assumptions about how rows sent or received?
> Rework code generator
> -
>
> Key: CALCITE-6465
> URL: https://issues.apache.org/jira/browse/CALCITE-6465
> Project: Calcite
> Issue Type: New Feature
> Components: core
>Reporter: James Duong
>Assignee: James Duong
>Priority: Major
>
> Holistically replace the (or provide a separate optional) code generator to
> reduce issues such as CALCITE-3094 .
> One suggestion in the comments for CALCITE-3094 has been to use the [code
> generator from
> Flink.|https://nightlies.apache.org/flink/flink-docs-release-1.3/api/java/org/apache/flink/table/codegen/CodeGenerator.html]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5541) Upgrade JavaCC
[
https://issues.apache.org/jira/browse/CALCITE-5541?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-5541:
-
Fix Version/s: 1.38.0
> Upgrade JavaCC
> --
>
> Key: CALCITE-5541
> URL: https://issues.apache.org/jira/browse/CALCITE-5541
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: John Wright
>Assignee: Julian Hyde
>Priority: Minor
> Fix For: 1.38.0
>
>
> After
> [https://github.com/apache/calcite/commit/515f3356a6a1ab4bd570c1c20bec9a7e5d4aca5f#diff-e873041549333502af52ece8a1b34301ae5a059ff4719e9bddbaef48929e7047]
> there appears to be an issue with JavaCC codegen of the Parser.jj template
> in "newer" versions of JavaCC (6.1.2 was the version I tested).
> {code:java}
>
> build/scala_2.10/generated-sources/java/com/redacted/query/sql/parser/impl/QuerySqlParserImpl.java:15266:
> error: variable startNum might not have been initialized
> startNum.intValue(true) != endNum.intValue(true),
> ^
> redacted/build/scala_2.10/generated-sources/java/com/redacted/query/sql/parser/impl/QuerySqlParserImpl.java:15266:
> error: variable endNum might not have been initialized
> startNum.intValue(true) != endNum.intValue(true),
>^
> redacted/build/scala_2.10/generated-sources/java/com/redacted/query/sql/parser/impl/QuerySqlParserImpl.java:15281:
> error: variable startNum might not have been initialized
> span().end(e), e, startNum, endNum, reluctant);}
> ^
> redacted/build/scala_2.10/generated-sources/java/com/redacted/query/sql/parser/impl/QuerySqlParserImpl.java:15281:
> error: variable endNum might not have been initialized
> span().end(e), e, startNum, endNum, reluctant);}
> ^
> redacted/build/scala_2.10/generated-sources/java/com/redacted/query/sql/parser/impl/QuerySqlParserImpl.java:15281:
> error: variable reluctant might not have been initialized
> span().end(e), e, startNum, endNum, reluctant);}{code}
> [~jbal...@gmail.com] helped me debug this, and he pointed out which generated
> code was incorrect:
> {code:java}
> final public SqlNode PatternFactor() throws ParseException {final SqlNode e;
> final SqlNode extra;
> final SqlLiteral startNum;
> final SqlLiteral endNum;
> final SqlLiteral reluctant;
> ...
> default:
> jj_la1[163] = jj_gen;
> {if ("" != null) return e;}
> }
> {if ("" != null) return SqlStdOperatorTable.PATTERN_QUANTIFIER.createCall(
> span().end(e), e, startNum, endNum, reluctant);}
> throw new Error("Missing return statement in function");
> }{code}
> instead of:
> {code:java}
> default:
> jj_la1[163] = jj_gen;
> {if (true) return e;}
> }
> {if (true) return SqlStdOperatorTable.PATTERN_QUANTIFIER.createCall(
> span().end(e), e, startNum, endNum, reluctant);}
> throw new Error("Missing return statement in function");
> } {code}
> I've worked around this by pinning to JavaCC 4.0, however the Gradle JavaCC
> plugin we use [https://github.com/javacc/javaccPlugin] defaults to 6.1.2.
> It looks like JavaCC 4.0 was release in 2006, 6.1.2 in 2014, and current
> 7.0.12 in 2022 so it felt like it might be worth updating.
> If this is intended / known, feel free to close - Thanks
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6461) Metadata provider for determining if columns originate from aggregate functions
[
https://issues.apache.org/jira/browse/CALCITE-6461?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17864239#comment-17864239
]
Julian Hyde commented on CALCITE-6461:
--
Would it be possible to extend one of the existing metadata types? I think that
would be preferable, even if it means a minor change in behavior.
> Metadata provider for determining if columns originate from aggregate
> functions
> ---
>
> Key: CALCITE-6461
> URL: https://issues.apache.org/jira/browse/CALCITE-6461
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Stamatis Zampetakis
>Assignee: Stamatis Zampetakis
>Priority: Major
>
> Add a new metadata provider for determining whether a set of columns
> originates from aggregate functions.
> In Apache Hive, we have a use-case where we need to determine if the columns
> in the result of a query originate from aggregate functions. Along with other
> things, it is used for deciding when to materialize common table expressions.
> {code:sql}
> SELECT job, SUM(sal) as total
> FROM emp
> GROUP BY job
> {code}
> For the query above, the new metadata provider should return:
> * false when the input is column 0 (job)
> * true when the input is column 1 (total)
> * false when the input is columns {0,1}
> The proposed metadata provider presents some similarities with
> {{ColumnOrigin}} and {{ExpressionLineage}} but the latter cannot be easily
> extended to provide this information.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-5541) Upgrade JavaCC
[
https://issues.apache.org/jira/browse/CALCITE-5541?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde reassigned CALCITE-5541:
Assignee: Julian Hyde
> Upgrade JavaCC
> --
>
> Key: CALCITE-5541
> URL: https://issues.apache.org/jira/browse/CALCITE-5541
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: John Wright
>Assignee: Julian Hyde
>Priority: Minor
>
> After
> [https://github.com/apache/calcite/commit/515f3356a6a1ab4bd570c1c20bec9a7e5d4aca5f#diff-e873041549333502af52ece8a1b34301ae5a059ff4719e9bddbaef48929e7047]
> there appears to be an issue with JavaCC codegen of the Parser.jj template
> in "newer" versions of JavaCC (6.1.2 was the version I tested).
> {code:java}
>
> build/scala_2.10/generated-sources/java/com/redacted/query/sql/parser/impl/QuerySqlParserImpl.java:15266:
> error: variable startNum might not have been initialized
> startNum.intValue(true) != endNum.intValue(true),
> ^
> redacted/build/scala_2.10/generated-sources/java/com/redacted/query/sql/parser/impl/QuerySqlParserImpl.java:15266:
> error: variable endNum might not have been initialized
> startNum.intValue(true) != endNum.intValue(true),
>^
> redacted/build/scala_2.10/generated-sources/java/com/redacted/query/sql/parser/impl/QuerySqlParserImpl.java:15281:
> error: variable startNum might not have been initialized
> span().end(e), e, startNum, endNum, reluctant);}
> ^
> redacted/build/scala_2.10/generated-sources/java/com/redacted/query/sql/parser/impl/QuerySqlParserImpl.java:15281:
> error: variable endNum might not have been initialized
> span().end(e), e, startNum, endNum, reluctant);}
> ^
> redacted/build/scala_2.10/generated-sources/java/com/redacted/query/sql/parser/impl/QuerySqlParserImpl.java:15281:
> error: variable reluctant might not have been initialized
> span().end(e), e, startNum, endNum, reluctant);}{code}
> [~jbal...@gmail.com] helped me debug this, and he pointed out which generated
> code was incorrect:
> {code:java}
> final public SqlNode PatternFactor() throws ParseException {final SqlNode e;
> final SqlNode extra;
> final SqlLiteral startNum;
> final SqlLiteral endNum;
> final SqlLiteral reluctant;
> ...
> default:
> jj_la1[163] = jj_gen;
> {if ("" != null) return e;}
> }
> {if ("" != null) return SqlStdOperatorTable.PATTERN_QUANTIFIER.createCall(
> span().end(e), e, startNum, endNum, reluctant);}
> throw new Error("Missing return statement in function");
> }{code}
> instead of:
> {code:java}
> default:
> jj_la1[163] = jj_gen;
> {if (true) return e;}
> }
> {if (true) return SqlStdOperatorTable.PATTERN_QUANTIFIER.createCall(
> span().end(e), e, startNum, endNum, reluctant);}
> throw new Error("Missing return statement in function");
> } {code}
> I've worked around this by pinning to JavaCC 4.0, however the Gradle JavaCC
> plugin we use [https://github.com/javacc/javaccPlugin] defaults to 6.1.2.
> It looks like JavaCC 4.0 was release in 2006, 6.1.2 in 2014, and current
> 7.0.12 in 2022 so it felt like it might be worth updating.
> If this is intended / known, feel free to close - Thanks
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-873) Prevent sort when ORDER BY not necessary due to equality constraints
[
https://issues.apache.org/jira/browse/CALCITE-873?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17863887#comment-17863887
]
Julian Hyde commented on CALCITE-873:
-
Commit
[47a2597b|https://github.com/apache/calcite/commit/47a2597bb20d6d9f006f60330499323f98bf0c2f]
added an additional test, to make sure that sort keys that NULL literals are
removed, like any other constants.
> Prevent sort when ORDER BY not necessary due to equality constraints
>
>
> Key: CALCITE-873
> URL: https://issues.apache.org/jira/browse/CALCITE-873
> Project: Calcite
> Issue Type: Improvement
>Reporter: James R. Taylor
>Assignee: Atri Sharma
>Priority: Major
> Fix For: 1.17.0
>
>
> We're working on an optimization in Phoenix to optimize away an ORDER BY when
> it is known based on equality expressions in the WHERE clause that it is not
> necessary (PHOENIX-2194). It'd be great if Calcite could do that as well.
> Here's a example, given the following schema:
> {code}
> CREATE TABLE T (
> K1 VARCHAR,
> K2 VARCHAR,
> K3 VARCHAR,
> CONSTRAINT pk PRIMARY KEY (K1, K2, K3));
> {code}
> In the following queries, no sort is necessary:
> {code}
> SELECT * FROM T WHERE K1='A' ORDER BY K2,K3;
> SELECT * FROM T WHERE K2='B' ORDER BY K1,K3;
> SELECT * FROM T WHERE K1='A' AND K2='B' ORDER BY K3;
> {code}
> There are also some edge cases where a function may be known to select a
> *prefix* of the column value where it's still ok to not sort:
> {code}
> SELECT * FROM T WHERE K1='A' AND SUBSTR(K2,1,3)='ABC' ORDER BY K2;
> {code}
> But if another column is included in the ORDER BY after the prefixing, a sort
> would still be necessary:
> {code}
> SELECT * FROM T WHERE K1='A' AND SUBSTR(K2,1,3)='ABC' ORDER BY K2,K3;
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6413) SqlValidator does not invoke TypeCoercionImpl::binaryComparisonCoercion for both NATURAL and USING join conditions
[
https://issues.apache.org/jira/browse/CALCITE-6413?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17863854#comment-17863854
]
Julian Hyde commented on CALCITE-6413:
--
[~xtern], Before I review the PR, we need a description of the problem. Can you
change the summary to describe the problem, not the fix.
> SqlValidator does not invoke TypeCoercionImpl::binaryComparisonCoercion for
> both NATURAL and USING join conditions
> ---
>
> Key: CALCITE-6413
> URL: https://issues.apache.org/jira/browse/CALCITE-6413
> Project: Calcite
> Issue Type: Improvement
>Reporter: Maksim Zhuravkov
>Assignee: Pavel Pereslegin
>Priority: Minor
>
> This can be observed by adding these test cases to `SqlToRelConverterTest`:
> 1. Join condition ON expression
> {code:java}
> @Test void test1() {
> final String sql = "select * from emp JOIN (VALUES ('XXX')) t(deptno) ON
> emp.deptno = t.deptno";
> sql(sql).ok();
> }
> {code}
> 2. Common columns (USING/NATURAL) (since they both share the same code path
> for building join condition)
> {code:java}
> @Test void test2() {
> final String sql = "select * from emp JOIN (VALUES ('XXX')) t(deptno)
> USING (deptno)";
> sql(sql).ok();
> }
> {code}
> When test 1 runs, the SqlValidator calls
> TypeCoercionImpl::binaryComparisonCoercion
> When test 2 runs, the SqlValidator does not call
> TypeCoercionImpl::binaryComparisonCoercion.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6459) Measures inside structured types (ROW, ARRAY, MAP)
Julian Hyde created CALCITE-6459:
Summary: Measures inside structured types (ROW, ARRAY, MAP)
Key: CALCITE-6459
URL: https://issues.apache.org/jira/browse/CALCITE-6459
Project: Calcite
Issue Type: Improvement
Reporter: Julian Hyde
Decide whether it is valid for measures to occur inside structured types (ROW,
ARRAY, MAP). If invalid, throw a validation error; if valid, change the planner
to make such queries work.
A measure can occur within a ROW (struct) type because measures are part of a
table's row type. It is more difficult to see measures in arrays or maps,
because they would have to be treated as values (similar to lambdas) and could
not be expanded at compile time.
Note that {{SqlTypeUtil.fromMeasure}} only handles struct types currently.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6457) The ARRAY_CONTAINS function return false when arrayComponentType and op1 type are different
[ https://issues.apache.org/jira/browse/CALCITE-6457?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17863406#comment-17863406 ] Julian Hyde commented on CALCITE-6457: -- The summary is not a valid English sentence. Change the word 'return' to 'wrongly returns' or 'should return'. > The ARRAY_CONTAINS function return false when arrayComponentType and op1 > type are different > > > Key: CALCITE-6457 > URL: https://issues.apache.org/jira/browse/CALCITE-6457 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.37.0 >Reporter: Caican Cai >Priority: Major > Labels: pull-request-available > > In Spark, array_contains(array(1.0, 2), 1) returns true because Spark > converts array and op1 types to the biggesttype. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6413) SqlValidator does not invoke TypeCoercionImpl::binaryComparisonCoercion for both NATURAL and USING join conditions
[
https://issues.apache.org/jira/browse/CALCITE-6413?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17862822#comment-17862822
]
Julian Hyde commented on CALCITE-6413:
--
We do convert NATURAL and USING to ON - in fact we have to, because our
relational algebra (class Join) only supports the latter. But we don’t do that
rewrite until validation is complete. Mutating the AST during validation is a
bad idea.
> SqlValidator does not invoke TypeCoercionImpl::binaryComparisonCoercion for
> both NATURAL and USING join conditions
> ---
>
> Key: CALCITE-6413
> URL: https://issues.apache.org/jira/browse/CALCITE-6413
> Project: Calcite
> Issue Type: Improvement
>Reporter: Maksim Zhuravkov
>Assignee: Pavel Pereslegin
>Priority: Minor
>
> This can be observed by adding these test cases to `SqlToRelConverterTest`:
> 1. Join condition ON expression
> {code:java}
> @Test void test1() {
> final String sql = "select * from emp JOIN (VALUES ('XXX')) t(deptno) ON
> emp.deptno = t.deptno";
> sql(sql).ok();
> }
> {code}
> 2. Common columns (USING/NATURAL) (since they both share the same code path
> for building join condition)
> {code:java}
> @Test void test2() {
> final String sql = "select * from emp JOIN (VALUES ('XXX')) t(deptno)
> USING (deptno)";
> sql(sql).ok();
> }
> {code}
> When test 1 runs, the SqlValidator calls
> TypeCoercionImpl::binaryComparisonCoercion
> When test 2 runs, the SqlValidator does not call
> TypeCoercionImpl::binaryComparisonCoercion.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-4921) Nested NATURAL JOINs or JOINs with USING can't find common column
[
https://issues.apache.org/jira/browse/CALCITE-4921?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-4921.
--
Fix Version/s: 1.38.0
Resolution: Fixed
Test cases added in
[e4e86d3b|https://github.com/apache/calcite/commit/e4e86d3b3eb6a386aa1ec900dafb0be75b78e342];
thanks for the PR, [~alex_pl]!
> Nested NATURAL JOINs or JOINs with USING can't find common column
> -
>
> Key: CALCITE-4921
> URL: https://issues.apache.org/jira/browse/CALCITE-4921
> Project: Calcite
> Issue Type: Bug
>Reporter: Aleksey Plekhanov
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> {{SqlValidatorUtil#deriveNaturalJoinColumnList}} can't correctly derive
> common columns for JOIN if the left hand of it is another NATURAL JOIN or
> JOIN with USING. For example:
> {noformat}
> SELECT * FROM (values (1, 1)) as t1(a, b)
> NATURAL JOIN (values (1, 1)) as t2(a, c)
> NATURAL JOIN (values (1, 1)) as t3(a, d){noformat}
> Builds incorrect plan:
> {noformat}
> LogicalProject(A=[$0], B=[$1], C=[$3], A1=[$4], D=[$5])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalJoin(condition=[=($0, $2)], joinType=[inner])
> LogicalValues(tuples=[[{ 1, 1 }]])
> LogicalValues(tuples=[[{ 1, 1 }]])
> LogicalValues(tuples=[[{ 1, 1 }]]){noformat}
> Queries with USING also have problems:
> {noformat}
> SELECT * FROM (values (1, 1)) as t1(a, b)
> JOIN (values (1, 1)) as t2(a, c) USING (a)
> JOIN (values (1, 1)) as t3(a, d) USING (a){noformat}
> Fails with:
> {noformat}
> Column name 'A' in USING clause is not unique on one side of join{noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6454) Implement array comparison operators
[
https://issues.apache.org/jira/browse/CALCITE-6454?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17862587#comment-17862587
]
Julian Hyde commented on CALCITE-6454:
--
Here's how to test ordering of arrays in ORDER BY. Apparently NULLS FIRST has
no effect.
{noformat}
with t (i, a) as (values (1, array [1, null, 3]), (2, array [1, 1, 4]))
select * from t order by a nulls first;
i a
=== ==
2 {1,1,4}
1 {1,NULL,3}
{noformat}
> Implement array comparison operators
>
>
> Key: CALCITE-6454
> URL: https://issues.apache.org/jira/browse/CALCITE-6454
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Norman Jordan
>Priority: Major
>
> The comparison operators <, <=, >, >=, =, <> are not implemented for arrays.
> Here is an example query:
>
> {code:java}
> SELECT array[2, 2] > array[1, 1]; {code}
> [This
> page|https://popsql.com/learn-sql/postgresql/how-to-compare-arrays-in-postgresql]
> describes how the comparisons work in PostgreSQL.
>
> Check if the comparison operators for arrays exist in other DB engines.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6454) Implement array comparison operators
[
https://issues.apache.org/jira/browse/CALCITE-6454?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17862561#comment-17862561
]
Julian Hyde commented on CALCITE-6454:
--
Once arrays are comparable, you should be able to use them in {{ORDER BY}},
including with {{DESC}}. Add tests for this.
Likewise {{RANK}} and {{DENSE_RANK}}.
What is the behavior of comparison operators when arrays are different lengths
(especially when one array is a prefix of another)? Again, tests needed.
How does the presence of NULL elements in the arrays affect the results of
comparison? If the arrays are used in {{ORDER BY}} and contain null values (but
are not null), do the {{NULLS FIRST}} and {{NULLS LAST}} keywords have any
effect?
> Implement array comparison operators
>
>
> Key: CALCITE-6454
> URL: https://issues.apache.org/jira/browse/CALCITE-6454
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Norman Jordan
>Priority: Major
>
> The comparison operators <, <=, >, >=, =, <> are not implemented for arrays.
> Here is an example query:
>
> {code:java}
> SELECT array[2, 2] > array[1, 1]; {code}
> [This
> page|https://popsql.com/learn-sql/postgresql/how-to-compare-arrays-in-postgresql]
> describes how the comparisons work in PostgreSQL.
>
> Check if the comparison operators for arrays exist in other DB engines.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5634) Enable GREATEST, LEAST functions in PostgreSQL library
[
https://issues.apache.org/jira/browse/CALCITE-5634?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17862559#comment-17862559
]
Julian Hyde commented on CALCITE-5634:
--
This is about comparison operations rather than just the {{GREATEST}} and
{{LEAST}} functions. Can someone please investigate? Check whether Postgres
allows comparisons like {{<}} on array values, and whether you can use array
values in {{ORDER BY}}. If Postgres allows some of these operations, file a new
Jira case.
I am also curious whether Postgres allows comparisons on {{ROW}} values. I
would hope that {{ROW (1, 'b') < ROW (2, 'a')}} is valid and returns {{TRUE}}
(due to lexicographic ordering). If so, Calcite should make {{ROW}} values
comparable also. In my work on attributes (CALCITE-6425) it would enable to
defining a composite {{SORT_KEY}} attribute.
> Enable GREATEST, LEAST functions in PostgreSQL library
> --
>
> Key: CALCITE-5634
> URL: https://issues.apache.org/jira/browse/CALCITE-5634
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0
>Reporter: Dmitry Sysolyatin
>Assignee: Norman Jordan
>Priority: Major
> Labels: pull-request-available
> Time Spent: 1h
> Remaining Estimate: 0h
>
> The LEAST and GREATEST functions have been implemented for BigQuery and
> Oracle, but haven't been added for PostgreSQL. PostgreSQL supports LEAST,
> GREATEST as well [1].
> Also PostgreSQL's LEAST, GREATEST functions behave differently with NULL
> values than ORACLE or BigQuery.
> From PostgreSQL documentation [1]:
> ??The result will be NULL only if all the expressions evaluate to NULL.??
> From BigQuery documentation [2]:
> ??They return NULL if any of the input parameters is NULL.??
> [1]
> [https://www.postgresql.org/docs/15/functions-conditional.html#FUNCTIONS-GREATEST-LEAST]
> [2]
> [https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-4921) Nested NATURAL JOINs or JOINs with USING can't find common column
[
https://issues.apache.org/jira/browse/CALCITE-4921?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17862558#comment-17862558
]
Julian Hyde commented on CALCITE-4921:
--
If someone contributes a few tests we can close this case. A couple of queries
in {{join.iq}} would be good.
> Nested NATURAL JOINs or JOINs with USING can't find common column
> -
>
> Key: CALCITE-4921
> URL: https://issues.apache.org/jira/browse/CALCITE-4921
> Project: Calcite
> Issue Type: Bug
>Reporter: Aleksey Plekhanov
>Priority: Major
>
> {{SqlValidatorUtil#deriveNaturalJoinColumnList}} can't correctly derive
> common columns for JOIN if the left hand of it is another NATURAL JOIN or
> JOIN with USING. For example:
> {noformat}
> SELECT * FROM (values (1, 1)) as t1(a, b)
> NATURAL JOIN (values (1, 1)) as t2(a, c)
> NATURAL JOIN (values (1, 1)) as t3(a, d){noformat}
> Builds incorrect plan:
> {noformat}
> LogicalProject(A=[$0], B=[$1], C=[$3], A1=[$4], D=[$5])
> LogicalJoin(condition=[true], joinType=[inner])
> LogicalJoin(condition=[=($0, $2)], joinType=[inner])
> LogicalValues(tuples=[[{ 1, 1 }]])
> LogicalValues(tuples=[[{ 1, 1 }]])
> LogicalValues(tuples=[[{ 1, 1 }]]){noformat}
> Queries with USING also have problems:
> {noformat}
> SELECT * FROM (values (1, 1)) as t1(a, b)
> JOIN (values (1, 1)) as t2(a, c) USING (a)
> JOIN (values (1, 1)) as t3(a, d) USING (a){noformat}
> Fails with:
> {noformat}
> Column name 'A' in USING clause is not unique on one side of join{noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6452) Scalar sub-query that uses IS NOT DISTINCT FROM returns incorrect result
[
https://issues.apache.org/jira/browse/CALCITE-6452?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17861288#comment-17861288
]
Julian Hyde commented on CALCITE-6452:
--
The
[julianhyde/6452-scalar-subquery-is-not-distinct-from|https://github.com/julianhyde/calcite/tree/6452-scalar-subquery-is-not-distinct-from]
branch contains a test-case.
> Scalar sub-query that uses IS NOT DISTINCT FROM returns incorrect result
>
>
> Key: CALCITE-6452
> URL: https://issues.apache.org/jira/browse/CALCITE-6452
> Project: Calcite
> Issue Type: Improvement
>Reporter: Julian Hyde
>Priority: Major
>
> Scalar sub-query that uses an {{IS NOT DISTINCT FROM}} condition returns
> incorrect result. For example,
> {code:java}
> select e.ename,
> (select count(*)
> from emp as f
> where f.comm is not distinct from e.comm) as c
> from emp as e{code}
> returns
> {noformat}
> +++
> | ENAME | C |
> +++
> | ADAMS | 0 |
> | ALLEN | 1 |
> | BLAKE | 0 |
> | CLARK | 0 |
> | FORD | 0 |
> | JAMES | 0 |
> | JONES | 0 |
> | KING | 0 |
> | MARTIN | 1 |
> | MILLER | 0 |
> | SCOTT | 0 |
> | SMITH | 0 |
> | TURNER | 1 |
> | WARD | 1 |
> +++
> (14 rows)
> {noformat}
> but should return
> {noformat}
> +++
> | ENAME | C |
> +++
> | ADAMS | 0 |
> | ALLEN | 10 |
> | BLAKE | 0 |
> | CLARK | 0 |
> | FORD | 0 |
> | JAMES | 0 |
> | JONES | 0 |
> | KING | 0 |
> | MARTIN | 10 |
> | MILLER | 0 |
> | SCOTT | 0 |
> | SMITH | 0 |
> | TURNER | 10 |
> | WARD | 10 |
> +++
> (14 rows)
> {noformat}
> Also, and perhaps related, if we add a {{WHERE}} to the above query, like
> this:
> {code:java}
> select e.ename,
> (select count(*)
> from emp as f
> where f.comm is not distinct from e.comm) as c
> from emp as e
> where e.deptno = 10{code}
> Calcite throws:
> {noformat}
> Caused by: java.lang.ArrayIndexOutOfBoundsException: Index 6 out of bounds
> for length 5
> > at
> > com.google.common.collect.RegularImmutableList.get(RegularImmutableList.java:77)
> > at org.apache.calcite.rex.RexBuilder.makeInputRef(RexBuilder.java:1037)
> > at
> > org.apache.calcite.sql2rel.RelDecorrelator$AdjustProjectForCountAggregateRule.lambda$onMatch2$4(RelDecorrelator.java:2679)
> {noformat}
> I ran into this error while trying to rewrite queries that had measures and
> used {{ROLLUP}}. (Since {{ROLLUP}} will generate group keys whose value is
> NULL, we cannot use regular {{=}} when doing a self-join.)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6452) Scalar sub-query that uses IS NOT DISTINCT FROM returns incorrect result
Julian Hyde created CALCITE-6452:
Summary: Scalar sub-query that uses IS NOT DISTINCT FROM returns
incorrect result
Key: CALCITE-6452
URL: https://issues.apache.org/jira/browse/CALCITE-6452
Project: Calcite
Issue Type: Improvement
Reporter: Julian Hyde
Scalar sub-query that uses an {{IS NOT DISTINCT FROM}} condition returns
incorrect result. For example,
{code:java}
select e.ename,
(select count(*)
from emp as f
where f.comm is not distinct from e.comm) as c
from emp as e{code}
returns
{noformat}
+++
| ENAME | C |
+++
| ADAMS | 0 |
| ALLEN | 1 |
| BLAKE | 0 |
| CLARK | 0 |
| FORD | 0 |
| JAMES | 0 |
| JONES | 0 |
| KING | 0 |
| MARTIN | 1 |
| MILLER | 0 |
| SCOTT | 0 |
| SMITH | 0 |
| TURNER | 1 |
| WARD | 1 |
+++
(14 rows)
{noformat}
but should return
{noformat}
+++
| ENAME | C |
+++
| ADAMS | 0 |
| ALLEN | 10 |
| BLAKE | 0 |
| CLARK | 0 |
| FORD | 0 |
| JAMES | 0 |
| JONES | 0 |
| KING | 0 |
| MARTIN | 10 |
| MILLER | 0 |
| SCOTT | 0 |
| SMITH | 0 |
| TURNER | 10 |
| WARD | 10 |
+++
(14 rows)
{noformat}
Also, and perhaps related, if we add a {{WHERE}} to the above query, like this:
{code:java}
select e.ename,
(select count(*)
from emp as f
where f.comm is not distinct from e.comm) as c
from emp as e
where e.deptno = 10{code}
Calcite throws:
{noformat}
Caused by: java.lang.ArrayIndexOutOfBoundsException: Index 6 out of bounds for
length 5
> at
> com.google.common.collect.RegularImmutableList.get(RegularImmutableList.java:77)
> at org.apache.calcite.rex.RexBuilder.makeInputRef(RexBuilder.java:1037)
> at
> org.apache.calcite.sql2rel.RelDecorrelator$AdjustProjectForCountAggregateRule.lambda$onMatch2$4(RelDecorrelator.java:2679)
{noformat}
I ran into this error while trying to rewrite queries that had measures and
used {{ROLLUP}}. (Since {{ROLLUP}} will generate group keys whose value is
NULL, we cannot use regular {{=}} when doing a self-join.)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-4496) Measure columns ("SELECT ... AS MEASURE")
[
https://issues.apache.org/jira/browse/CALCITE-4496?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17861036#comment-17861036
]
Julian Hyde commented on CALCITE-4496:
--
I've created a [pull request|https://github.com/apache/calcite/pull/3837]. Can
people please review?
Measures are described in my paper, co-authored with John Fremlin, [Measures
in SQL|https://dl.acm.org/doi/pdf/10.1145/3626246.3653374] from SIGMOD 2024.
Measure support began with CALCITE-5105 (adding the {{MEASURE}} type and
{{AGGREGATE}} aggregate function), and this is the first commit that allows you
to define measures in a query. Future changes will support the {{AT}} operator
(see CALCITE-5692), and use of measures in more query patterns (e.g. measures
on measures, queries that join tables that have measures, use of measures with
{{GROUPING SETS}}, {{ROLLUP}} and {{CUBE}}).
> Measure columns ("SELECT ... AS MEASURE")
> -
>
> Key: CALCITE-4496
> URL: https://issues.apache.org/jira/browse/CALCITE-4496
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Julian Hyde
>Priority: Major
>
> In multi-dimensional languages such as MDX, DAX, Tableau, you can define
> calculations in your models that can be re-evaluated in other dimensional
> contexts. (The models are often called cubes, and the calculations are often
> called measures.)
> In SQL, the model is a view (or a sub-query in the FROM clause) but the
> columns are just values. Suppose you have a private {{Employees}} table, a
> {{Departments}} view that rolls {{Employees}} up to department level and has
> an {{averageSalary}} column. Now suppose you wish to roll up
> {{averageSalary}} to the region level. The values that went into
> {{averageSalary}} are not available to you, either directly or indirectly, so
> the best you can do is to average-the-averages.
> In this proposed (and experimental) feature, you can define a special kind of
> column - a measure - in the SELECT list of a view (or sub-query in a FROM
> clause), and it remains a calculation. When a query uses a measure column,
> the calculation is re-evaluated in the context of that query.
> To some extent, this breaches the "black box" property of SQL views.
> Hitherto, a SQL view can be replaced with a table that has the same contents,
> and all queries that use that view will return the same results. That
> property no longer holds. But the view remains a useful "hiding" abstraction,
> and the rows that compose that view cannot be viewed directly.
> Like dimensional models, measures in SQL would allow high-level abstractions
> such as key-performance indicators (KPIs) to be shared and composed. Unlike
> dimensional models, the models remain relational, namely, it is still
> possible to enumerate and count the rows in a model.
> Consider the following view and query that uses it:
> {code:sql}
> CREATE VIEW EmpSummary AS
> SELECT deptno,
> job,
> AVG(sal) AS avg_sal,
> AVG(sal) AS MEASURE avg_sal_measure,
> COUNT(*) + 1 AS MEASURE count_plus_one_measure
> FROM Emp
> GROUP BY deptno, job;
> SELECT deptno,
> AVG(avg_sal) AS a1,
> AGGREGATE(avg_sal_measure) AS a2,
> AGGREGATE(count_plus_one_measure) AS c1
> FROM EmpSummary
> GROUP BY deptno;{code}
> Note that there is a special aggregate function, {{AGGREGATE}}, that rolls up
> measures. Columns {{a1}} and {{a2}} will contain different values; the first
> averages the averages, and the second computes the average from the raw data.
> Column {{c1}} will return the number of employees in each department plus
> one, not rolling up the "plus one" for each distinct job in the department.
> This is just a brief sketch illustrating the purpose of measures. This
> feature is experimental, the syntax will no doubt change, and much of the
> semantics (for example, what expressions are valid as measures, whether
> measures remain measures they appear in the SELECT clause of an enclosing
> query, and what is the "context" in which a measure is evaluated) need to be
> ironed out.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6440) SortRemoveConstantKeysRule should remove NULL literal sort keys (e.g. ORDER BY NULL)
[
https://issues.apache.org/jira/browse/CALCITE-6440?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17861027#comment-17861027
]
Julian Hyde commented on CALCITE-6440:
--
* Your PR uses that word 'empty' again
* Don't worry about removing the unused column in the Project; field trimming
is not a job easily done by planner rules
* Yes, it should (must) be done in SortRemoveConstantKeysRule. It just needs to
remove any sort keys that are constant. If the sort has 0 keys after that
process, then the sort can be removed (unless it has fetch/limit).
* After this change, we should be able to simplify "SELECT * FROM emp ORDER BY
deptno, null, empno" to "SELECT * FROM emp ORDER BY deptno, empno" and your
change currently doesn't do that or test that.
> SortRemoveConstantKeysRule should remove NULL literal sort keys (e.g. ORDER
> BY NULL)
>
>
> Key: CALCITE-6440
> URL: https://issues.apache.org/jira/browse/CALCITE-6440
> Project: Calcite
> Issue Type: New Feature
> Components: core
>Affects Versions: 1.37.0
>Reporter: Caican Cai
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> For the following SQL:
> {code:java}
> select * from task order by null; {code}
> We could rewrite it to:
> {code:java}
> select * from task;{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-6444) Add a function library for Amazon Redshift
[
https://issues.apache.org/jira/browse/CALCITE-6444?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-6444.
--
Resolution: Fixed
Fixed in
[cdb6522d|https://github.com/apache/calcite/commit/cdb6522d3320776f6c7ce66c01747447a39a8c84];
thanks for the PR, [~njordan]!
> Add a function library for Amazon Redshift
> --
>
> Key: CALCITE-6444
> URL: https://issues.apache.org/jira/browse/CALCITE-6444
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: Norman Jordan
>Assignee: Norman Jordan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> Add a function library for Amazon Redshift. People would be able to use this
> library by adding "{{fun=redshift}}" to their connect string.
> The Babel tests assume that RedShift is a union of the Oracle and PostgreSQL
> libraries. This is not accurate. There are functions such as *INCR* that are
> supported by Oracle and not by RedShift. Problems will also occur when Oracle
> and Postgres both support a function of the same name that needs different
> implementations.
> [https://github.com/apache/calcite/blob/main/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java#L110]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6444) Add a function library for Amazon Redshift
[
https://issues.apache.org/jira/browse/CALCITE-6444?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17860917#comment-17860917
]
Julian Hyde commented on CALCITE-6444:
--
[~njordan], I added a fixup commit to your branch in
[https://github.com/julianhyde/calcite/tree/6444-redshift.] Unless you see
problems, I'll squash & merge shortly.
> Add a function library for Amazon Redshift
> --
>
> Key: CALCITE-6444
> URL: https://issues.apache.org/jira/browse/CALCITE-6444
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: Norman Jordan
>Assignee: Norman Jordan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> Add a function library for Amazon Redshift. People would be able to use this
> library by adding "{{fun=redshift}}" to their connect string.
> The Babel tests assume that RedShift is a union of the Oracle and PostgreSQL
> libraries. This is not accurate. There are functions such as *INCR* that are
> supported by Oracle and not by RedShift. Problems will also occur when Oracle
> and Postgres both support a function of the same name that needs different
> implementations.
> [https://github.com/apache/calcite/blob/main/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java#L110]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6444) Add a function library for Amazon Redshift
[
https://issues.apache.org/jira/browse/CALCITE-6444?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6444:
-
Fix Version/s: 1.38.0
> Add a function library for Amazon Redshift
> --
>
> Key: CALCITE-6444
> URL: https://issues.apache.org/jira/browse/CALCITE-6444
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: Norman Jordan
>Assignee: Norman Jordan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> Add a function library for Amazon Redshift. People would be able to use this
> library by adding "{{fun=redshift}}" to their connect string.
> The Babel tests assume that RedShift is a union of the Oracle and PostgreSQL
> libraries. This is not accurate. There are functions such as *INCR* that are
> supported by Oracle and not by RedShift. Problems will also occur when Oracle
> and Postgres both support a function of the same name that needs different
> implementations.
> [https://github.com/apache/calcite/blob/main/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java#L110]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6444) Add a function library for Amazon Redshift
[
https://issues.apache.org/jira/browse/CALCITE-6444?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17860675#comment-17860675
]
Julian Hyde commented on CALCITE-6444:
--
I reviewed. A few changes needed, but let's make sure it gets into 1.38. Nag me
if necessary. :)
> Add a function library for Amazon Redshift
> --
>
> Key: CALCITE-6444
> URL: https://issues.apache.org/jira/browse/CALCITE-6444
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: Norman Jordan
>Assignee: Norman Jordan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> Add a function library for Amazon Redshift. People would be able to use this
> library by adding "{{fun=redshift}}" to their connect string.
> The Babel tests assume that RedShift is a union of the Oracle and PostgreSQL
> libraries. This is not accurate. There are functions such as *INCR* that are
> supported by Oracle and not by RedShift. Problems will also occur when Oracle
> and Postgres both support a function of the same name that needs different
> implementations.
> [https://github.com/apache/calcite/blob/main/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java#L110]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6369) Expanding "star" gives ArrayIndexOutOfBoundsException with redundant columns and USING
[
https://issues.apache.org/jira/browse/CALCITE-6369?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17860665#comment-17860665
]
Julian Hyde edited comment on CALCITE-6369 at 6/27/24 11:46 PM:
[~njordan], Since the bug is in the validator, it's strange that you added a
test case to rel-to-sql converter. Did you consider adding a test similar to
{{SqlValidatorTest.testRewriteExpansionOfColumnReferenceBeforeResolution}} ,
which was added for CALCITE-4172.
was (Author: julianhyde):
[~njordan], Since the bug is in the validator, it's strange that you added a
test case to rel-to-sql converter. Did you consider adding a test similar to
{{SqlValidatorTest.
testRewriteExpansionOfColumnReferenceBeforeResolution}}, which was added for
CALCITE-4172.
> Expanding "star" gives ArrayIndexOutOfBoundsException with redundant columns
> and USING
> --
>
> Key: CALCITE-6369
> URL: https://issues.apache.org/jira/browse/CALCITE-6369
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Claude Brisson
>Assignee: Norman Jordan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> The query
> {code}
> select r_regionkey, * from region r0 join region r1 using (r_regionkey)
> {code}
> produces
> {code}
> java.lang.ArrayIndexOutOfBoundsException: Index 14 out of bounds for length 14
> at org.apache.calcite.runtime.PairLists$ArrayImmutablePairList.get
> (PairLists.java:573)
> at org.apache.calcite.runtime.PairLists$ArrayImmutablePairList.get
> (PairLists.java:550)
> at org.apache.calcite.sql.validate.SqlValidatorImpl$Permute.permute
> (SqlValidatorImpl.java:7443)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.expandStar
> (SqlValidatorImpl.java:697)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem
> (SqlValidatorImpl.java:453)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList
> (SqlValidatorImpl.java:4658)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect
> (SqlValidatorImpl.java:3840)
> at org.apache.calcite.sql.validate.SelectNamespace.validateImpl
> (SelectNamespace.java:61)
> at org.apache.calcite.sql.validate.AbstractNamespace.validate
> (AbstractNamespace.java:88)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace
> (SqlValidatorImpl.java:1154)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery
> (SqlValidatorImpl.java:1125)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6369) Expanding "star" gives ArrayIndexOutOfBoundsException with redundant columns and USING
[
https://issues.apache.org/jira/browse/CALCITE-6369?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17860665#comment-17860665
]
Julian Hyde commented on CALCITE-6369:
--
[~njordan], Since the bug is in the validator, it's strange that you added a
test case to rel-to-sql converter. Did you consider adding a test similar to
{{SqlValidatorTest.
testRewriteExpansionOfColumnReferenceBeforeResolution}}, which was added for
CALCITE-4172.
> Expanding "star" gives ArrayIndexOutOfBoundsException with redundant columns
> and USING
> --
>
> Key: CALCITE-6369
> URL: https://issues.apache.org/jira/browse/CALCITE-6369
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Claude Brisson
>Assignee: Norman Jordan
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> The query
> {code}
> select r_regionkey, * from region r0 join region r1 using (r_regionkey)
> {code}
> produces
> {code}
> java.lang.ArrayIndexOutOfBoundsException: Index 14 out of bounds for length 14
> at org.apache.calcite.runtime.PairLists$ArrayImmutablePairList.get
> (PairLists.java:573)
> at org.apache.calcite.runtime.PairLists$ArrayImmutablePairList.get
> (PairLists.java:550)
> at org.apache.calcite.sql.validate.SqlValidatorImpl$Permute.permute
> (SqlValidatorImpl.java:7443)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.expandStar
> (SqlValidatorImpl.java:697)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem
> (SqlValidatorImpl.java:453)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList
> (SqlValidatorImpl.java:4658)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect
> (SqlValidatorImpl.java:3840)
> at org.apache.calcite.sql.validate.SelectNamespace.validateImpl
> (SelectNamespace.java:61)
> at org.apache.calcite.sql.validate.AbstractNamespace.validate
> (AbstractNamespace.java:88)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace
> (SqlValidatorImpl.java:1154)
> at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery
> (SqlValidatorImpl.java:1125)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5802) In RelBuilder add method aggregateRex, to allow aggregating complex expressions such as "1 + SUM(x + 2)"
[
https://issues.apache.org/jira/browse/CALCITE-5802?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17860663#comment-17860663
]
Julian Hyde commented on CALCITE-5802:
--
I agree, it's a new feature. We don't tend to use the 'type' and 'priority'
fields in Jira. 'Bug' and 'Major' are just the defaults. Hopefully it's clear
from the summary that it is a new feature.
> In RelBuilder add method aggregateRex, to allow aggregating complex
> expressions such as "1 + SUM(x + 2)"
>
>
> Key: CALCITE-5802
> URL: https://issues.apache.org/jira/browse/CALCITE-5802
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Julian Hyde
>Priority: Major
>
> In {{RelBuilder}} add method {{{}aggregateRex{}}}, to allow aggregating
> complex expressions such as "1 + SUM(x + 2)". These expressions are difficult
> because there is an expression ({{{}x + 2{}}}) before the aggregate, then the
> aggregate, then an expression ({{{}1 + sum{}}}) after the aggregate. For
> complex expressions such as this, the translation requires a {{Project}}
> followed by an {{Aggregate}} followed by a {{{}Project{}}}.
> Aggregate functions are not conventionally represented as {{{}RexNode{}}},
> but we allow them in the expression passed to {{{}aggregateRex{}}}: note
> \{{b.call(SqlStdOperatorTable.SUM,}} ... in the code.
> For example, to create the same effect as SQL
> {code:java}
> SELECT deptno,
> deptno + 2 AS d2,
> 3 + SUM(4 + sal) AS s
> FROM emp
> GROUP BY deptno
> {code}
> we use the {{RelBuilder}} code
> {code:java}
> RelBuilder b;
> b.scan("EMP")
> .aggregateRex(b.groupKey(b.field("DEPTNO")),
> b.field("DEPTNO"),
> b.alias(
> b.call(SqlStdOperatorTable.PLUS, b.field("DEPTNO"),
> b.literal(2)),
> "d2"),
> b.alias(
> b.call(SqlStdOperatorTable.PLUS, b.literal(3),
> b.call(SqlStdOperatorTable.SUM,
> b.call(SqlStdOperatorTable.PLUS, b.literal(4),
> b.field("SAL",
> "s"))
> .build();
> {code}
> and the resulting relational expression is
> {noformat}
> LogicalProject(DEPTNO=[$0], d2=[+($0, 2)], s=[+(3, $1)])
> LogicalAggregate(group=[{0}], agg#0=[SUM($1)])
> LogicalProject(DEPTNO=[$7], $f8=[+(4, $5)])
> LogicalTableScan(table=[[scott, EMP]])
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5802) In RelBuilder add method aggregateRex, to allow aggregating complex expressions such as "1 + SUM(x + 2)"
[
https://issues.apache.org/jira/browse/CALCITE-5802?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-5802:
-
Description:
In {{RelBuilder}} add method {{{}aggregateRex{}}}, to allow aggregating complex
expressions such as "1 + SUM(x + 2)". These expressions are difficult because
there is an expression ({{{}x + 2{}}}) before the aggregate, then the
aggregate, then an expression ({{{}1 + sum{}}}) after the aggregate. For
complex expressions such as this, the translation requires a {{Project}}
followed by an {{Aggregate}} followed by a {{{}Project{}}}.
Aggregate functions are not conventionally represented as {{{}RexNode{}}}, but
we allow them in the expression passed to {{{}aggregateRex{}}}: note
\{{b.call(SqlStdOperatorTable.SUM,}} ... in the code.
For example, to create the same effect as SQL
{code:java}
SELECT deptno,
deptno + 2 AS d2,
3 + SUM(4 + sal) AS s
FROM emp
GROUP BY deptno
{code}
we use the {{RelBuilder}} code
{code:java}
RelBuilder b;
b.scan("EMP")
.aggregateRex(b.groupKey(b.field("DEPTNO")),
b.field("DEPTNO"),
b.alias(
b.call(SqlStdOperatorTable.PLUS, b.field("DEPTNO"),
b.literal(2)),
"d2"),
b.alias(
b.call(SqlStdOperatorTable.PLUS, b.literal(3),
b.call(SqlStdOperatorTable.SUM,
b.call(SqlStdOperatorTable.PLUS, b.literal(4),
b.field("SAL",
"s"))
.build();
{code}
and the resulting relational expression is
{noformat}
LogicalProject(DEPTNO=[$0], d2=[+($0, 2)], s=[+(3, $1)])
LogicalAggregate(group=[{0}], agg#0=[SUM($1)])
LogicalProject(DEPTNO=[$7], $f8=[+(4, $5)])
LogicalTableScan(table=[[scott, EMP]])
{noformat}
was:
In {{RelBuilder}} add method {{{}aggregateExtended{}}}, to allow aggregating
complex expressions such as "1 + SUM(x + 2)". These expressions are difficult
because there is an expression ({{{}x + 2{}}}) before the aggregate, then the
aggregate, then an expression ({{{}1 + sum{}}}) after the aggregate. For
complex expressions such as this, the translation requires a {{Project}}
followed by an {{Aggregate}} followed by a {{{}Project{}}}.
Aggregate functions are not conventionally represented as {{{}RexNode{}}}, but
we allow them in the expression passed to {{{}aggregateExtended{}}}.
For example, to create the same effect as SQL
{code:java}
SELECT deptno,
deptno + 2 AS d2,
3 + SUM(4 + sal) AS s
FROM emp
GROUP BY deptno
{code}
we use the {{RelBuilder}} code
{code:java}
RelBuilder b;
b.scan("EMP")
.aggregateRex(b.groupKey(b.field("DEPTNO")),
b.field("DEPTNO"),
b.alias(
b.call(SqlStdOperatorTable.PLUS, b.field("DEPTNO"),
b.literal(2)),
"d2"),
b.alias(
b.call(SqlStdOperatorTable.PLUS, b.literal(3),
b.call(SqlStdOperatorTable.SUM,
b.call(SqlStdOperatorTable.PLUS, b.literal(4),
b.field("SAL",
"s"))
.build();
{code}
and the resulting relational expression is
{noformat}
LogicalProject(DEPTNO=[$0], d2=[+($0, 2)], s=[+(3, $1)])
LogicalAggregate(group=[{0}], agg#0=[SUM($1)])
LogicalProject(DEPTNO=[$7], $f8=[+(4, $5)])
LogicalTableScan(table=[[scott, EMP]])
{noformat}
> In RelBuilder add method aggregateRex, to allow aggregating complex
> expressions such as "1 + SUM(x + 2)"
>
>
> Key: CALCITE-5802
> URL: https://issues.apache.org/jira/browse/CALCITE-5802
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Julian Hyde
>Priority: Major
>
> In {{RelBuilder}} add method {{{}aggregateRex{}}}, to allow aggregating
> complex expressions such as "1 + SUM(x + 2)". These expressions are difficult
> because there is an expression ({{{}x + 2{}}}) before the aggregate, then the
> aggregate, then an expression ({{{}1 + sum{}}}) after the aggregate. For
> complex expressions such as this, the translation requires a {{Project}}
> followed by an {{Aggregate}} followed by a {{{}Project{}}}.
> Aggregate functions are not conventionally represented as {{{}RexNode{}}},
> but we allow them in the expression passed to {{{}aggregateRex{}}}: note
> \{{b.call(SqlStdOperatorTable.SUM,}} ... in the code.
> For example, to create the same effect as SQL
> {code:java}
> SELECT deptno,
> deptno + 2 AS d2,
> 3 + SUM(4 + sal) AS s
> FROM emp
> GROUP BY deptno
> {code}
> we use the {{RelBuilder}} code
> {code:java}
> RelBuilder b;
> b.scan("EMP")
> .aggregateRex(b.groupKey(b.field("DEPTNO")),
> b.field("DEPTNO"),
> b.alias(
> b.call(SqlStdOperatorTable.PLUS, b.field("DEPTNO"),
> b.literal(2)),
[jira] [Updated] (CALCITE-5802) In RelBuilder add method aggregateRex, to allow aggregating complex expressions such as "1 + SUM(x + 2)"
[
https://issues.apache.org/jira/browse/CALCITE-5802?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-5802:
-
Summary: In RelBuilder add method aggregateRex, to allow aggregating
complex expressions such as "1 + SUM(x + 2)" (was: In RelBuilder add method
aggregateExtended, to allow aggregating complex expressions such as "1 + SUM(x
+ 2)")
> In RelBuilder add method aggregateRex, to allow aggregating complex
> expressions such as "1 + SUM(x + 2)"
>
>
> Key: CALCITE-5802
> URL: https://issues.apache.org/jira/browse/CALCITE-5802
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Julian Hyde
>Priority: Major
>
> In {{RelBuilder}} add method {{{}aggregateExtended{}}}, to allow aggregating
> complex expressions such as "1 + SUM(x + 2)". These expressions are difficult
> because there is an expression ({{{}x + 2{}}}) before the aggregate, then the
> aggregate, then an expression ({{{}1 + sum{}}}) after the aggregate. For
> complex expressions such as this, the translation requires a {{Project}}
> followed by an {{Aggregate}} followed by a {{{}Project{}}}.
> Aggregate functions are not conventionally represented as {{{}RexNode{}}},
> but we allow them in the expression passed to {{{}aggregateExtended{}}}.
> For example, to create the same effect as SQL
> {code:java}
> SELECT deptno,
> deptno + 2 AS d2,
> 3 + SUM(4 + sal) AS s
> FROM emp
> GROUP BY deptno
> {code}
> we use the {{RelBuilder}} code
> {code:java}
> RelBuilder b;
> b.scan("EMP")
> .aggregateRex(b.groupKey(b.field("DEPTNO")),
> b.field("DEPTNO"),
> b.alias(
> b.call(SqlStdOperatorTable.PLUS, b.field("DEPTNO"),
> b.literal(2)),
> "d2"),
> b.alias(
> b.call(SqlStdOperatorTable.PLUS, b.literal(3),
> b.call(SqlStdOperatorTable.SUM,
> b.call(SqlStdOperatorTable.PLUS, b.literal(4),
> b.field("SAL",
> "s"))
> .build();
> {code}
> and the resulting relational expression is
> {noformat}
> LogicalProject(DEPTNO=[$0], d2=[+($0, 2)], s=[+(3, $1)])
> LogicalAggregate(group=[{0}], agg#0=[SUM($1)])
> LogicalProject(DEPTNO=[$7], $f8=[+(4, $5)])
> LogicalTableScan(table=[[scott, EMP]])
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6436) JDBC adapter generates SQL with missing round brackets when WHERE clause contains parameter
[
https://issues.apache.org/jira/browse/CALCITE-6436?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17860276#comment-17860276
]
Julian Hyde commented on CALCITE-6436:
--
I can reproduce this. The generated SQL (for HSQLDB) is
{code:java}
SELECT "product_id" = 1 AS "ABC"
FROM "foodmart"."sales_fact_1997"
WHERE "product_id" = 1 = ? {code}
I suspect that
{code:java}
SELECT ("product_id" = 1) AS "ABC"
FROM "foodmart"."sales_fact_1997"
WHERE ("product_id" = 1) = ? {code}
would be acceptable to HSQLDB.
> JDBC adapter generates SQL with missing round brackets when WHERE clause
> contains parameter
> ---
>
> Key: CALCITE-6436
> URL: https://issues.apache.org/jira/browse/CALCITE-6436
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: Ulrich Kramer
>Priority: Major
>
> Running the following unit test in {{JdbcAdapterTest}} fails with
> {code:java}
> @Test void testOperatorInWhere() {
> CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
> .query("select * from \"sales_fact_1997\" "
> + "where (\"product_id\" = 1) = ?")
> .consumesPreparedStatement(p -> p.setBoolean(1, true))
> .runs();
> }
> {code}
> {noformat}
> Caused by: java.sql.SQLSyntaxErrorException: unexpected token: = : line: 3 in
> statement [SELECT *
> FROM "foodmart"."sales_fact_1997"
> WHERE "product_id" = 1 = ?
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6448) FilterReduceExpressionsRule returns empty RelNode for RexLiterals
[
https://issues.apache.org/jira/browse/CALCITE-6448?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17860251#comment-17860251
]
Julian Hyde commented on CALCITE-6448:
--
I surmise that the rule is assuming that the condition has type BOOLEAN (and
perhaps it also allows a NULL literal, whose type is something like NULLTYPE).
A good solution would be to require conditions to be BOOLEAN when we construct
a Filter (similarly Join). It would great users like Hive, but it would prevent
bugs like this, and so be a net positive.
> FilterReduceExpressionsRule returns empty RelNode for RexLiterals
> -
>
> Key: CALCITE-6448
> URL: https://issues.apache.org/jira/browse/CALCITE-6448
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Soumyakanti Das
>Priority: Major
>
> In Hive, for simple queries with {{WHERE 'foo'}} we get an empty result,
> where as for simple queries with {{WHERE t.stringColumn}} we get non-empty
> result.
> Currently in {{FilterReduceExpressionsRule}}, all {{RexLiteral}}s are
> converted to empty values by
> {code:java}
> else if (newConditionExp instanceof RexLiteral
> || RexUtil.isNullLiteral(newConditionExp, true)) {
> call.transformTo(createEmptyRelOrEquivalent(call, filter));
> {code}
> This might be not inline with {{RexLiteral#isAlwaysFalse}}, and it might be
> safer to leave the literal as it is.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6433) SUBSTRING can return incorrect empty result for some parameters
[
https://issues.apache.org/jira/browse/CALCITE-6433?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17860192#comment-17860192
]
Julian Hyde commented on CALCITE-6433:
--
Thanks for clarification. (I think you meant 'clamping', not 'glamping', and
you should amend your comment. 'Glamping' is something I did a few years ago in
Iceland... it was fun!)
> SUBSTRING can return incorrect empty result for some parameters
> ---
>
> Key: CALCITE-6433
> URL: https://issues.apache.org/jira/browse/CALCITE-6433
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.37.0
>Reporter: Iurii Gerzhedovich
>Priority: Major
>
> SUBSTRING function for cases when 3rd parameter (length) more than
> Integer.MAX_VALUE can return empty result due to code do clamp
> that value and after that it can't be more than Integer.MAX_VALUE.
> Simple way to reproduce :
> append into *SqlOperatorTest* smth like:
> {noformat}
> f.checkScalar(
> String.format("{fn SUBSTRING('abcdef', %d, %d)}", Integer.MIN_VALUE,
> 10L + Integer.MAX_VALUE),
> "abcdef",
> "VARCHAR(6) NOT NULL");
> {noformat}
> it`s all due to check after clamping
> {noformat}
> public static String substring(String c, int s, int l) {
>
> long e = (long) s + (long) l; -- here we can got incorrect length
> .
> if (s > lc || e < 1L) {
> return "";
> }
> .{noformat}
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6444) Add a function library for Amazon Redshift
[
https://issues.apache.org/jira/browse/CALCITE-6444?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17859979#comment-17859979
]
Julian Hyde commented on CALCITE-6444:
--
Thanks for doing that analysis. Furthermore, if we use {{exceptLibraries}}
errors of omission are less harmful. Suppose we omit the {{exceptLibraries}}
tag for one of those 8 functions that are in Postgres but not in Redshift. This
function is now available to users of the Redshift library when it should not
be - but must Redshift users will not be harmed by it (or even notice).
> Add a function library for Amazon Redshift
> --
>
> Key: CALCITE-6444
> URL: https://issues.apache.org/jira/browse/CALCITE-6444
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: Norman Jordan
>Assignee: Norman Jordan
>Priority: Major
> Labels: pull-request-available
>
> Add a function library for Amazon Redshift. People would be able to use this
> library by adding "{{fun=redshift}}" to their connect string.
> The Babel tests assume that RedShift is a union of the Oracle and PostgreSQL
> libraries. This is not accurate. There are functions such as *INCR* that are
> supported by Oracle and not by RedShift. Problems will also occur when Oracle
> and Postgres both support a function of the same name that needs different
> implementations.
> [https://github.com/apache/calcite/blob/main/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java#L110]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6444) Add a function library for Amazon Redshift
[
https://issues.apache.org/jira/browse/CALCITE-6444?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17859734#comment-17859734
]
Julian Hyde edited comment on CALCITE-6444 at 6/25/24 4:31 PM:
---
I see what you are saying. Something like *excludeLibraries* may be a simpler
solution. For the functions in {*}SqlLibraryOperators{*},
Functions in PostgreSQL and/or Redshift: 44
Only in PostgreSQL: 8
Only in Redshift: 5
In both with different implementations: 2
There is more discussion on the mailing list.
was (Author: njordan):
I see what you are saying. Something like *excludeLibaries* may be a simpler
solution. For the functions in {*}SqlLibraryOperators{*},
Functions in PostgreSQL and/or Redshift: 44
Only in PostgreSQL: 8
Only in Redshift: 5
In both with different implementations: 2
There is more discussion on the mailing list.
> Add a function library for Amazon Redshift
> --
>
> Key: CALCITE-6444
> URL: https://issues.apache.org/jira/browse/CALCITE-6444
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: Norman Jordan
>Assignee: Norman Jordan
>Priority: Major
> Labels: pull-request-available
>
> Add a function library for Amazon Redshift. People would be able to use this
> library by adding "{{fun=redshift}}" to their connect string.
> The Babel tests assume that RedShift is a union of the Oracle and PostgreSQL
> libraries. This is not accurate. There are functions such as *INCR* that are
> supported by Oracle and not by RedShift. Problems will also occur when Oracle
> and Postgres both support a function of the same name that needs different
> implementations.
> [https://github.com/apache/calcite/blob/main/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java#L110]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6444) Add a function library for Amazon Redshift
[
https://issues.apache.org/jira/browse/CALCITE-6444?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6444:
-
Description:
Add a function library for Amazon Redshift. People would be able to use this
library by adding "{{fun=redshift}}" to their connect string.
The Babel tests assume that RedShift is a union of the Oracle and PostgreSQL
libraries. This is not accurate. There are functions such as *INCR* that are
supported by Oracle and not by RedShift. Problems will also occur when Oracle
and Postgres both support a function of the same name that needs different
implementations.
[https://github.com/apache/calcite/blob/main/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java#L110]
was:
The Babel tests assume that RedShift is a union of the Oracle and PostgreSQL
libraries. This is not accurate. There are functions such as *INCR* that are
supported by Oracle and not by RedShift. Problems will also occur when Oracle
and Postgres both support a function of the same name that needs different
implementations.
[https://github.com/apache/calcite/blob/main/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java#L110]
> Add a function library for Amazon Redshift
> --
>
> Key: CALCITE-6444
> URL: https://issues.apache.org/jira/browse/CALCITE-6444
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: Norman Jordan
>Assignee: Norman Jordan
>Priority: Major
> Labels: pull-request-available
>
> Add a function library for Amazon Redshift. People would be able to use this
> library by adding "{{fun=redshift}}" to their connect string.
> The Babel tests assume that RedShift is a union of the Oracle and PostgreSQL
> libraries. This is not accurate. There are functions such as *INCR* that are
> supported by Oracle and not by RedShift. Problems will also occur when Oracle
> and Postgres both support a function of the same name that needs different
> implementations.
> [https://github.com/apache/calcite/blob/main/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java#L110]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6444) Add a function library for Amazon Redshift
[ https://issues.apache.org/jira/browse/CALCITE-6444?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Julian Hyde updated CALCITE-6444: - Summary: Add a function library for Amazon Redshift (was: Add an Amazon Redshift SqlLibrary) > Add a function library for Amazon Redshift > -- > > Key: CALCITE-6444 > URL: https://issues.apache.org/jira/browse/CALCITE-6444 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.34.0 >Reporter: Norman Jordan >Assignee: Norman Jordan >Priority: Major > Labels: pull-request-available > > The Babel tests assume that RedShift is a union of the Oracle and PostgreSQL > libraries. This is not accurate. There are functions such as *INCR* that are > supported by Oracle and not by RedShift. Problems will also occur when Oracle > and Postgres both support a function of the same name that needs different > implementations. > [https://github.com/apache/calcite/blob/main/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java#L110] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6442) Validator rejects FILTER in OVER windows
[
https://issues.apache.org/jira/browse/CALCITE-6442?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17859976#comment-17859976
]
Julian Hyde commented on CALCITE-6442:
--
Thank you, [~mbudiu], for going the extra mile to make Calcite excellent. As
you always do.
> Validator rejects FILTER in OVER windows
>
>
> Key: CALCITE-6442
> URL: https://issues.apache.org/jira/browse/CALCITE-6442
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: Mihai Budiu
>Priority: Minor
> Fix For: 1.38.0
>
>
> The Calcite grammar about windows says this:
> {code}
> windowedAggregateCall:
> agg '(' [ ALL | DISTINCT ] value [, value ]* ')'
> [ RESPECT NULLS | IGNORE NULLS ]
> [ WITHIN GROUP '(' ORDER BY orderItem [, orderItem ]* ')' ]
> [ FILTER '(' WHERE condition ')' ]
> OVER window
> {code}
> However, the validator rejects the following query:
> {code:sql}
> SELECT deptno,
>COUNT(DISTINCT deptno) FILTER (WHERE deptno > 10)
> OVER win AS agg
> FROM emp
> WINDOW win AS (PARTITION BY empno)
> {code}
> with the following error:
> {code}
> org.apache.calcite.sql.validate.SqlValidatorException: OVER must be applied
> to aggregate function
> at
> java.base/jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:67)
> at
> java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
> at
> java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:484)
> at
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:507)
> at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:601)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:948)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:933)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5676)
> at
> org.apache.calcite.sql.SqlOverOperator.validateCall(SqlOverOperator.java:77)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:6404)
> {code}
> The easy fix would be to remove this from the grammar.
> A harder fix would be to implement support for FILTERs in windows, but I
> don't know how hard that would be.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-768) Hopping windows
[
https://issues.apache.org/jira/browse/CALCITE-768?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-768.
-
Fix Version/s: 1.12.0
Resolution: Duplicate
Resolving as duplicate of CALCITE-1615.
> Hopping windows
> ---
>
> Key: CALCITE-768
> URL: https://issues.apache.org/jira/browse/CALCITE-768
> Project: Calcite
> Issue Type: Bug
> Components: stream
>Reporter: Julian Hyde
>Priority: Major
> Fix For: 1.12.0
>
>
> Recall that a hopping window emits a sub-total every X seconds of
> records that have arrived over the last Y seconds. A tumbling window
> is a hopping window where X and Y are equal.
> In https://calcite.incubator.apache.org/docs/stream.html#hopping-windows
> I give an example, "emit, every hour, the number of each product
> ordered over the past three hours".
> That example gives a query in terms of a GROUP BY (in the HourlyTotals
> view) followed by a moving sum. I didn't think that it was possible to
> express using just one GROUP BY, because that would violate one of the
> principles of SQL: that each record entering a GROUP BY contributes to
> precisely one output record.
> But I've just realized that the CUBE, ROLLUP and GROUPING SETS
> operators (already in SQL) violate that principle. And if they can do
> it, we can do the same. So we could add another grouping function,
> HOP(t, emit, retain).
> The query would look like this:
> {code}
> SELECT STREAM START(rowtime) AS rowtime,
> productId,
> SUM(units) AS sumUnits,
> COUNT(*) AS c
> FROM Orders
> GROUP BY HOP(rowtime, INTERVAL '1' HOUR, INTERVAL '3' HOUR),
> productId
> {code}
> Much nicer than the one in stream.html!
> The "trick" is that the HOP function is returning a list of rowtime
> values. For example, for row 1 {rowtime: '09:33', ...} it will return
> ['09:00', '10:00', '11:00']; for row 2 {rowtime: '10:05', ...} it will
> return ['10:00', '11:00', '12:00']. The system adds each row to
> several sub-totals, and emits each sub-total when it is complete. The
> sub-total for '09:00' will contain only row 1, and will be emitted at
> '10:00'; the sub-total for '10:00' will contain row 1 and row 2, and
> will be emitted at '11:00', and so forth.
> Returning multiple values is related to the flatMap function in Spark
> (and earlier selectMany in LINQ) and makes HOP's semantics similar to
> GROUPING SETS and therefore sound.
> START is a new aggregate function that returns the lower bound of the
> current sub-total; END similarly.
> Note that the "retain" argument does not need to be a whole multiple
> of the "emit" argument. This was a major limitation in the previous
> proposal.
> There are some straightforward extensions:
> * Define a TUMBLE function;
> * Add an "align" argument to HOP, to allow windows to start at, say, 5
> minutes past each hour;
> * Apply HOP to windows based on row-counts;
> * Allow user-defined windowing functions that similarly return a list
> of interval start-end points.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6440) Add optimization to remove sortkey when The Order By parameter in the Sort operator is null
[
https://issues.apache.org/jira/browse/CALCITE-6440?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17859512#comment-17859512
]
Julian Hyde commented on CALCITE-6440:
--
A better summary would be 'SortRemoveConstantKeysRule should remove NULL
literal sort keys (e.g. ORDER BY NULL)'.
> Add optimization to remove sortkey when The Order By parameter in the Sort
> operator is null
> ---
>
> Key: CALCITE-6440
> URL: https://issues.apache.org/jira/browse/CALCITE-6440
> Project: Calcite
> Issue Type: New Feature
> Components: core
>Affects Versions: 1.37.0
>Reporter: Caican Cai
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> For the following SQL:
> {code:java}
> select * from task order by null; {code}
> We could rewrite it to:
> {code:java}
> select * from task;{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6447) extract common expressions for disjunctions in Join
[
https://issues.apache.org/jira/browse/CALCITE-6447?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17859503#comment-17859503
]
Julian Hyde edited comment on CALCITE-6447 at 6/23/24 8:40 PM:
---
This is related to conjunctive normal form
([CNF|https://en.wikipedia.org/wiki/Conjunctive_normal_form]);
{{RexUtil.toCnf(RexNode)}} was added in CALCITE-394. Also
{{{}RexUtil.pullFactors{}}}, added in CALCITE-399.
was (Author: julianhyde):
This is related to conjunctive normal form
([CNF|https://en.wikipedia.org/wiki/Conjunctive_normal_form]);
{{RexUtil.toCnf(RexNode)}} was added in CALCITE-394.
> extract common expressions for disjunctions in Join
> ---
>
> Key: CALCITE-6447
> URL: https://issues.apache.org/jira/browse/CALCITE-6447
> Project: Calcite
> Issue Type: New Feature
> Components: core
>Affects Versions: 1.37.0
>Reporter: ruanhui
>Priority: Minor
> Fix For: 1.37.0
>
>
> For SQL:
> {code:java}
> select *
> from tbl_a
> join tbl_b on tbl_a.id = tbl_b.id
> where (tbl_a.x > 100 and tbl_b.y < 10)
>or (tbl_a.x > 100 and tbl_b.z > 20){code}
> we can rewrite it to
> {code:java}
> select *
> from tbl_a
> join tbl_b on tbl_a.id = tbl_b.id
> where tbl_a.x > 100
> and (tbl_b.y < 10 or tbl_b.z > 20){code}
> And in this way *tbl_a.x > 100* can be pushed down and it is likely that this
> will help reduce the amount of data involved in the join.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6447) extract common expressions for disjunctions in Join
[
https://issues.apache.org/jira/browse/CALCITE-6447?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17859503#comment-17859503
]
Julian Hyde commented on CALCITE-6447:
--
This is related to conjunctive normal form
([CNF|https://en.wikipedia.org/wiki/Conjunctive_normal_form]);
{{RexUtil.toCnf(RexNode)}} was added in CALCITE-394.
> extract common expressions for disjunctions in Join
> ---
>
> Key: CALCITE-6447
> URL: https://issues.apache.org/jira/browse/CALCITE-6447
> Project: Calcite
> Issue Type: New Feature
> Components: core
>Affects Versions: 1.37.0
>Reporter: ruanhui
>Priority: Minor
> Fix For: 1.37.0
>
>
> For SQL:
> {code:java}
> select *
> from tbl_a
> join tbl_b on tbl_a.id = tbl_b.id
> where (tbl_a.x > 100 and tbl_b.y < 10)
>or (tbl_a.x > 100 and tbl_b.z > 20){code}
> we can rewrite it to
> {code:java}
> select *
> from tbl_a
> join tbl_b on tbl_a.id = tbl_b.id
> where tbl_a.x > 100
> and (tbl_b.y < 10 or tbl_b.z > 20){code}
> And in this way *tbl_a.x > 100* can be pushed down and it is likely that this
> will help reduce the amount of data involved in the join.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6447) extract common expressions for disjunctions in Join
[
https://issues.apache.org/jira/browse/CALCITE-6447?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6447:
-
Description:
For SQL:
{code:java}
select *
from tbl_a
join tbl_b on tbl_a.id = tbl_b.id
where (tbl_a.x > 100 and tbl_b.y < 10)
or (tbl_a.x > 100 and tbl_b.z > 20){code}
we can rewrite it to
{code:java}
select *
from tbl_a
join tbl_b on tbl_a.id = tbl_b.id
where tbl_a.x > 100
and (tbl_b.y < 10 or tbl_b.z > 20){code}
And in this way *tbl_a.x > 100* can be pushed down and it is likely that this
will help reduce the amount of data involved in the join.
was:
For SQL:
{code:java}
select * from tbl_a join tbl_b on tbl_a.id = tbl_b.id where (tbl_a.x > 100 and
tbl_b.y < 10) or (tbl_a.x > 100 and tbl_b.z > 20){code}
we can rewrite it to
{code:java}
select * from tbl_a join tbl_b on tbl_a.id = tbl_b.id where tbl_a.x > 100 and
(tbl_b.y < 10 or tbl_b.z > 20){code}
And in this way *tbl_a.x > 100* can be pushed down and it is likely that this
will help reduce the amount of data involved in the join.
> extract common expressions for disjunctions in Join
> ---
>
> Key: CALCITE-6447
> URL: https://issues.apache.org/jira/browse/CALCITE-6447
> Project: Calcite
> Issue Type: New Feature
> Components: core
>Affects Versions: 1.37.0
>Reporter: ruanhui
>Priority: Minor
> Fix For: 1.37.0
>
>
> For SQL:
> {code:java}
> select *
> from tbl_a
> join tbl_b on tbl_a.id = tbl_b.id
> where (tbl_a.x > 100 and tbl_b.y < 10)
>or (tbl_a.x > 100 and tbl_b.z > 20){code}
> we can rewrite it to
> {code:java}
> select *
> from tbl_a
> join tbl_b on tbl_a.id = tbl_b.id
> where tbl_a.x > 100
> and (tbl_b.y < 10 or tbl_b.z > 20){code}
> And in this way *tbl_a.x > 100* can be pushed down and it is likely that this
> will help reduce the amount of data involved in the join.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6442) Validator rejects FILTER in OVER windows
[
https://issues.apache.org/jira/browse/CALCITE-6442?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17856954#comment-17856954
]
Julian Hyde commented on CALCITE-6442:
--
If you're going to remove it from the documentation of the grammar, it would be
good if you made it illegal in the implementation - accompanied by either a
negative SqlParserTest case or a negative SqlValidatorTest case (depending on
whether you change the parser or the validator to make it illegal).
> Validator rejects FILTER in OVER windows
>
>
> Key: CALCITE-6442
> URL: https://issues.apache.org/jira/browse/CALCITE-6442
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> The Calcite grammar about windows says this:
> {code}
> windowedAggregateCall:
> agg '(' [ ALL | DISTINCT ] value [, value ]* ')'
> [ RESPECT NULLS | IGNORE NULLS ]
> [ WITHIN GROUP '(' ORDER BY orderItem [, orderItem ]* ')' ]
> [ FILTER '(' WHERE condition ')' ]
> OVER window
> {code}
> However, the validator rejects the following query:
> {code:sql}
> SELECT deptno,
>COUNT(DISTINCT deptno) FILTER (WHERE deptno > 10)
> OVER win AS agg
> FROM emp
> WINDOW win AS (PARTITION BY empno)
> {code}
> with the following error:
> {code}
> org.apache.calcite.sql.validate.SqlValidatorException: OVER must be applied
> to aggregate function
> at
> java.base/jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:67)
> at
> java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
> at
> java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:484)
> at
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:507)
> at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:601)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:948)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:933)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5676)
> at
> org.apache.calcite.sql.SqlOverOperator.validateCall(SqlOverOperator.java:77)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:6404)
> {code}
> The easy fix would be to remove this from the grammar.
> A harder fix would be to implement support for FILTERs in windows, but I
> don't know how hard that would be.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6442) Validator rejects FILTER in OVER windows
[
https://issues.apache.org/jira/browse/CALCITE-6442?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17856953#comment-17856953
]
Julian Hyde commented on CALCITE-6442:
--
That was a copy-edit. The content was added earlier.
> Validator rejects FILTER in OVER windows
>
>
> Key: CALCITE-6442
> URL: https://issues.apache.org/jira/browse/CALCITE-6442
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> The Calcite grammar about windows says this:
> {code}
> windowedAggregateCall:
> agg '(' [ ALL | DISTINCT ] value [, value ]* ')'
> [ RESPECT NULLS | IGNORE NULLS ]
> [ WITHIN GROUP '(' ORDER BY orderItem [, orderItem ]* ')' ]
> [ FILTER '(' WHERE condition ')' ]
> OVER window
> {code}
> However, the validator rejects the following query:
> {code:sql}
> SELECT deptno,
>COUNT(DISTINCT deptno) FILTER (WHERE deptno > 10)
> OVER win AS agg
> FROM emp
> WINDOW win AS (PARTITION BY empno)
> {code}
> with the following error:
> {code}
> org.apache.calcite.sql.validate.SqlValidatorException: OVER must be applied
> to aggregate function
> at
> java.base/jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:67)
> at
> java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
> at
> java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:484)
> at
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:507)
> at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:601)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:948)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:933)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5676)
> at
> org.apache.calcite.sql.SqlOverOperator.validateCall(SqlOverOperator.java:77)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:6404)
> {code}
> The easy fix would be to remove this from the grammar.
> A harder fix would be to implement support for FILTERs in windows, but I
> don't know how hard that would be.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6444) Add an Amazon Redshift SqlLibrary
[
https://issues.apache.org/jira/browse/CALCITE-6444?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17856884#comment-17856884
]
Julian Hyde commented on CALCITE-6444:
--
I'm thinking about the effort going forward. Would it make sense for every
function in the Postgres to be automatically in the Redshift library unless the
author declares otherwise?
Say, add an {{exceptLibraries}} argument, like this:
{code:java}
@LibraryOperator(libraries = {BIG_QUERY, MYSQL, POSTGRESQL},
excludeLibraries = {REDSHIFT})
public static final SqlFunction SHA512 = ...;
{code}
> Add an Amazon Redshift SqlLibrary
> --
>
> Key: CALCITE-6444
> URL: https://issues.apache.org/jira/browse/CALCITE-6444
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.34.0
>Reporter: Norman Jordan
>Assignee: Norman Jordan
>Priority: Major
>
> The Babel tests assume that RedShift is a union of the Oracle and PostgreSQL
> libraries. This is not accurate. There are functions such as *INCR* that are
> supported by Oracle and not by RedShift. Problems will also occur when Oracle
> and Postgres both support a function of the same name that needs different
> implementations.
> [https://github.com/apache/calcite/blob/main/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java#L110]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6444) Add a RedShift SqlLibrary
[ https://issues.apache.org/jira/browse/CALCITE-6444?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17856879#comment-17856879 ] Julian Hyde commented on CALCITE-6444: -- Can you begin the description with the feature you propose to add. "Redshift" has lower-case "s". Can you change summary accordingly. Probably the summary should say "Amazon Redshift". I'm not too concerned about Babel. As a parser that spans multiple inconsistent dialects, there are bound to be cracks. Is it reasonable to assume that every function in the Postgres library will also appear in the Redshift library? Or perhaps 95%? If so, can you suggest how to achieve this painlessly. > Add a RedShift SqlLibrary > - > > Key: CALCITE-6444 > URL: https://issues.apache.org/jira/browse/CALCITE-6444 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.34.0 >Reporter: Norman Jordan >Assignee: Norman Jordan >Priority: Major > > The Babel tests assume that RedShift is a union of the Oracle and PostgreSQL > libraries. This is not accurate. There are functions such as *INCR* that are > supported by Oracle and not by RedShift. Problems will also occur when Oracle > and Postgres both support a function of the same name that needs different > implementations. > [https://github.com/apache/calcite/blob/main/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java#L110] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-6443) Create view based on LookML model
[
https://issues.apache.org/jira/browse/CALCITE-6443?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6443:
-
Description:
Create a view based on LookML model (more specifically, based on an explore in
a LookML model).
Note: I have no plans to implement this in open source, but it's helpful to set
out the requirements.
For example, the following statement using the LOOKML_VIEW table function
{code}
CREATE VIEW OrdersCube AS
SELECT *
FROM TABLE LOOKML_VIEW('orders', '
model: model_name {
view: orders {
sql: SELECT * FROM orders ;;
dimension: id {
primary_key: yes
type: number
}
dimension_group: created {
type: time
timeframes: [time, date, week, month]
sql: created_at;;
}
dimension: amount {
type: number
value_format: “0.00”
}
measure: count
measure: total_amount {
type: sum
sql: amount ;;
}
}
view: customers {
sql_table_name: customers
label: “Customer”
dimension: id {
primary_key: yes
}
dimension: zipcode
dimension: state
}
explore: orders {
from: orders
join: customers {
sql_on: customers.id = orders.customer_id ;;
}
}
}');
{code}
is equivalent to the following:
{code}
CREATE VIEW OrdersCube AS
SELECT *
FROM (
SELECT `orders.id`, // PK
`orders.customer_id`,
TIME(created_at) AS `orders.created_time`,
DATE(created_at) AS `orders.created_date`,
WEEK(created_at) AS `orders.created_week`,
MONTH(created_at) AS `orders.created_month`,
amount AS `orders.amount`, // value_format: “0.00”
COUNT(*) AS MEASURE `orders.count`,
SUM(amount) AS MEASURE `orders.total_amount`
FROM orders) AS orders
JOIN (
SELECT id AS `customers.id`, // PK
zip_code AS `customers.zip_code`,
state AS `customers.state`
FROM customers) AS customers // label: “Customer”
ON `customers.id` = `orders.customer_id`;
{code}
On these views we can execute queries that use the measures, for example
{code}
SELECT `customers.state`,
AGGREGATE(`orders.total_amount`)
FROM OrdersCube
WHERE `orders.created_year` = 2023
GROUP BY `customers.state`
ORDER BY 2 DESC LIMIT 5
{code}
was:
Create a view based on LookML model (more specifically, based on an explore in
a LookML model).
Note: I have no plans to implement this in open source, but it's helpful to set
out the requirements.
For example, the following statement using the LOOKML_VIEW table function
{code}
CREATE VIEW OrdersCube AS
SELECT *
FROM TABLE LOOKML_VIEW('orders', '
model: model_name {
view: orders {
sql: SELECT * FROM orders ;;
dimension: id {
primary_key: yes
type: number
}
dimension_group: created {
type: time
timeframes: [time, date, week, month]
sql: created_at;;
}
dimension: amount {
type: number
value_format: “0.00”
}
measure: count
measure: total_amount {
type: sum
sql: amount ;;
}
}
view: customers {
sql_table_name: customers
label: “Customer”
dimension: id {
primary_key: yes
}
dimension: zipcode
dimension: state
}
explore: orders {
from: orders
join: customers {
sql_on: customers.id = orders.customer_id ;;
}
}
}');
{code}
is equivalent to the following:
{code}
CREATE VIEW OrdersCube AS
SELECT *
FROM (
SELECT `orders.id`, // PK
`orders.customer_id`,
TIME(created_at) AS `orders.created_time`,
DATE(created_at) AS `orders.created_date`,
WEEK(created_at) AS `orders.created_week`,
MONTH(created_at) AS `orders.created_month`,
amount AS `orders.amount`, // value_format: “0.00”
COUNT(*) AS MEASURE `orders.count`,
SUM(amount) AS MEASURE `orders.total_amount`
FROM orders) AS orders
JOIN (
SELECT id AS `customers.id`, // PK
zip_code AS `customers.zip_code`,
state AS `customers.state`
FROM customers) AS customers // label: “Customer”
ON `customers.id` = `orders.customer_id`;
{code}
> Create view based on LookML model
> -
>
> Key: CALCITE-6443
> URL: https://issues.apache.org/jira/browse/CALCITE-6443
> Project: Calcite
> Issue Type: Improvement
>Reporter: Julian Hyde
>Priority: Major
>
> Create a view based on LookML model (more specifically, based on an explore
> in a LookML model).
> Note: I have no plans to implement this in open source, but it's helpful to
> set out the requirements.
> For example, the following statement using the LOOKML_VIEW table function
> {code}
> CREATE VIEW OrdersCube AS
> SELECT *
> FROM TABLE LOOKML_VIEW('orders', '
> model: model_name {
> view: orders {
> sql: SELECT * FROM orders ;;
> dimension: id {
> primary_key: yes
> type: number
> }
> dimension_group: created {
> type: time
>
[jira] [Created] (CALCITE-6443) Create view based on LookML model
Julian Hyde created CALCITE-6443:
Summary: Create view based on LookML model
Key: CALCITE-6443
URL: https://issues.apache.org/jira/browse/CALCITE-6443
Project: Calcite
Issue Type: Improvement
Reporter: Julian Hyde
Create a view based on LookML model (more specifically, based on an explore in
a LookML model).
Note: I have no plans to implement this in open source, but it's helpful to set
out the requirements.
For example, the following statement using the LOOKML_VIEW table function
{code}
CREATE VIEW OrdersCube AS
SELECT *
FROM TABLE LOOKML_VIEW('orders', '
model: model_name {
view: orders {
sql: SELECT * FROM orders ;;
dimension: id {
primary_key: yes
type: number
}
dimension_group: created {
type: time
timeframes: [time, date, week, month]
sql: created_at;;
}
dimension: amount {
type: number
value_format: “0.00”
}
measure: count
measure: total_amount {
type: sum
sql: amount ;;
}
}
view: customers {
sql_table_name: customers
label: “Customer”
dimension: id {
primary_key: yes
}
dimension: zipcode
dimension: state
}
explore: orders {
from: orders
join: customers {
sql_on: customers.id = orders.customer_id ;;
}
}
}');
{code}
is equivalent to the following:
{code}
CREATE VIEW OrdersCube AS
SELECT *
FROM (
SELECT `orders.id`, // PK
`orders.customer_id`,
TIME(created_at) AS `orders.created_time`,
DATE(created_at) AS `orders.created_date`,
WEEK(created_at) AS `orders.created_week`,
MONTH(created_at) AS `orders.created_month`,
amount AS `orders.amount`, // value_format: “0.00”
COUNT(*) AS MEASURE `orders.count`,
SUM(amount) AS MEASURE `orders.total_amount`
FROM orders) AS orders
JOIN (
SELECT id AS `customers.id`, // PK
zip_code AS `customers.zip_code`,
state AS `customers.state`
FROM customers) AS customers // label: “Customer”
ON `customers.id` = `orders.customer_id`;
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6442) Validator rejects FILTER in OVER windows
[
https://issues.apache.org/jira/browse/CALCITE-6442?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17856099#comment-17856099
]
Julian Hyde commented on CALCITE-6442:
--
Can you see who added it to the grammar, and as part of which change? I don’t
recall this ever being supported, and apparently there are no tests.
I’m not even sure of the desired semantics. If it is “5 ROWS PRECEDING”, is the
filter applied before or after determining the previous 5 rows?
> Validator rejects FILTER in OVER windows
>
>
> Key: CALCITE-6442
> URL: https://issues.apache.org/jira/browse/CALCITE-6442
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> The Calcite grammar about windows says this:
> {code}
> windowedAggregateCall:
> agg '(' [ ALL | DISTINCT ] value [, value ]* ')'
> [ RESPECT NULLS | IGNORE NULLS ]
> [ WITHIN GROUP '(' ORDER BY orderItem [, orderItem ]* ')' ]
> [ FILTER '(' WHERE condition ')' ]
> OVER window
> {code}
> However, the validator rejects the following query:
> {code:sql}
> SELECT deptno,
>COUNT(DISTINCT deptno) FILTER (WHERE deptno > 10)
> OVER win AS agg
> FROM emp
> WINDOW win AS (PARTITION BY empno)
> {code}
> with the following error:
> {code}
> org.apache.calcite.sql.validate.SqlValidatorException: OVER must be applied
> to aggregate function
> at
> java.base/jdk.internal.reflect.DirectConstructorHandleAccessor.newInstance(DirectConstructorHandleAccessor.java:67)
> at
> java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:500)
> at
> java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:484)
> at
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:507)
> at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:601)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:948)
> at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:933)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5676)
> at
> org.apache.calcite.sql.SqlOverOperator.validateCall(SqlOverOperator.java:77)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:6404)
> {code}
> The easy fix would be to remove this from the grammar.
> A harder fix would be to implement support for FILTERs in windows, but I
> don't know how hard that would be.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6440) Add optimization to remove sortkey when The Order By parameter in the Sort operator is empty
[
https://issues.apache.org/jira/browse/CALCITE-6440?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17856089#comment-17856089
]
Julian Hyde commented on CALCITE-6440:
--
[~caicancai], No, the rule should not do what you suggest. It only matches a
Sort, not a Sort on a Project, and therefore it cannot modify the Project. In
fact it doesn't even know the type of its input, and it does not know which of
its output fields are used. The unused field should be dealt with by column
pruning.
I agree with [~nobigo] that a NULL literal should be treated as a constant for
these purposes. Maybe the fix is to make sure that columns that are always NULL
(due to being NULL literals or having an IS NULL constraint) are added to
RelOptPredicateList.constantMap.
I agree with [~mbudiu]'s concerns. "null" is not the same as "empty". And in
"PruneEmptyRules", "empty" means "input always has zero rows".
> Add optimization to remove sortkey when The Order By parameter in the Sort
> operator is empty
>
>
> Key: CALCITE-6440
> URL: https://issues.apache.org/jira/browse/CALCITE-6440
> Project: Calcite
> Issue Type: New Feature
> Components: core
>Affects Versions: 1.37.0
>Reporter: Caican Cai
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> For the following SQL:
> {code:java}
> select * from task order by null; {code}
> We could rewrite it to:
> {code:java}
> select * from task;{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6441) Add BOOLAGG_AND, BOOLAGG_OR aggregate functions (enabled in Snowflake library)
[ https://issues.apache.org/jira/browse/CALCITE-6441?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17855767#comment-17855767 ] Julian Hyde edited comment on CALCITE-6441 at 6/18/24 12:20 AM: Fixed in [c8d42f3|https://github.com/apache/calcite/commit/c8d42f350c2aa4efc24a585f96eba268310aa1ab]; thanks for the PR, [~nitishkumar]! was (Author: julianhyde): Fixed in [https://github.com/apache/calcite/commit/c8d42f350c2aa4efc24a585f96eba268310aa1ab]; thanks for the PR, [~nitishkumar]! > Add BOOLAGG_AND, BOOLAGG_OR aggregate functions (enabled in Snowflake library) > -- > > Key: CALCITE-6441 > URL: https://issues.apache.org/jira/browse/CALCITE-6441 > Project: Calcite > Issue Type: Improvement >Reporter: Julian Hyde >Priority: Major > Fix For: 1.38.0 > > > Add BOOLAGG_AND, BOOLAGG_OR aggregate functions. These functions are the > Snowflake equivalent to the standard EVERY and SOME aggregate functions. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Assigned] (CALCITE-6441) Add BOOLAGG_AND, BOOLAGG_OR aggregate functions (enabled in Snowflake library)
[ https://issues.apache.org/jira/browse/CALCITE-6441?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Julian Hyde reassigned CALCITE-6441: Assignee: Nitish Kumar > Add BOOLAGG_AND, BOOLAGG_OR aggregate functions (enabled in Snowflake library) > -- > > Key: CALCITE-6441 > URL: https://issues.apache.org/jira/browse/CALCITE-6441 > Project: Calcite > Issue Type: Improvement >Reporter: Julian Hyde >Assignee: Nitish Kumar >Priority: Major > Fix For: 1.38.0 > > > Add BOOLAGG_AND, BOOLAGG_OR aggregate functions. These functions are the > Snowflake equivalent to the standard EVERY and SOME aggregate functions. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-6441) Add BOOLAGG_AND, BOOLAGG_OR aggregate functions (enabled in Snowflake library)
[ https://issues.apache.org/jira/browse/CALCITE-6441?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Julian Hyde resolved CALCITE-6441. -- Fix Version/s: 1.38.0 Resolution: Fixed Fixed in [https://github.com/apache/calcite/commit/c8d42f350c2aa4efc24a585f96eba268310aa1ab]; thanks for the PR, [~nitishkumar]! > Add BOOLAGG_AND, BOOLAGG_OR aggregate functions (enabled in Snowflake library) > -- > > Key: CALCITE-6441 > URL: https://issues.apache.org/jira/browse/CALCITE-6441 > Project: Calcite > Issue Type: Improvement >Reporter: Julian Hyde >Priority: Major > Fix For: 1.38.0 > > > Add BOOLAGG_AND, BOOLAGG_OR aggregate functions. These functions are the > Snowflake equivalent to the standard EVERY and SOME aggregate functions. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-6414) Snowflake JDBC adapter should generate BOOLOR_AGG, BOOLAND_AGG for MAX, MIN on BOOLEAN values
[
https://issues.apache.org/jira/browse/CALCITE-6414?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-6414.
--
Fix Version/s: 1.38.0
Resolution: Fixed
Fixed in
[97d62ac|https://github.com/apache/calcite/commit/97d62ac73a8f635d07b8672f1d96990855ab7d3c];
thanks for the PR, [~nitishkumar]!
> Snowflake JDBC adapter should generate BOOLOR_AGG, BOOLAND_AGG for MAX, MIN
> on BOOLEAN values
> -
>
> Key: CALCITE-6414
> URL: https://issues.apache.org/jira/browse/CALCITE-6414
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Justin Swett
>Assignee: Nitish Kumar
>Priority: Trivial
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> The rewriteMacMInExpr is generating incorrect SQL for Snowflake.
> Instead of BOOL_OR, it should be BOOLOR_AGG and instead BOOL_AND, it should
> be BOOLAND_AGG
>
> I think adding the following expectation in testMaxMinOnBooleanColumn should
> repro:
>
> {code:java}
> @Test void testMaxMinOnBooleanColumn(){
> ...
> final String expectedSnowflake = "SELECT BOOLOR_AGG(\"brand_name\" =
> 'a'), "
> + "BOOLAND_AGG(\"brand_name\" = 'a'), "
> + "MIN(\"brand_name\")\n"
> + "FROM \"foodmart\".\"product\"";
> sql(query)
> .ok(expected)
> .withBigQuery().ok(expectedBigQuery)
> .withPostgresql().ok(expectedPostgres)
> .withSnowflak().ok(expectedSnowflake)
> .withRedshift().ok(expectedPostgres);
> }
> {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6414) Snowflake JDBC adapter should generate BOOLOR_AGG, BOOLAND_AGG for MAX, MIN on BOOLEAN values
[
https://issues.apache.org/jira/browse/CALCITE-6414?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17855763#comment-17855763
]
Julian Hyde commented on CALCITE-6414:
--
I have decomposed this PR into two (one to add the functions - see CALCITE-6441
- and one to fix the JDBC adapter's SQL generation) and will merge to main
shortly.
> Snowflake JDBC adapter should generate BOOLOR_AGG, BOOLAND_AGG for MAX, MIN
> on BOOLEAN values
> -
>
> Key: CALCITE-6414
> URL: https://issues.apache.org/jira/browse/CALCITE-6414
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Justin Swett
>Assignee: Nitish Kumar
>Priority: Trivial
> Labels: pull-request-available
>
> The rewriteMacMInExpr is generating incorrect SQL for Snowflake.
> Instead of BOOL_OR, it should be BOOLOR_AGG and instead BOOL_AND, it should
> be BOOLAND_AGG
>
> I think adding the following expectation in testMaxMinOnBooleanColumn should
> repro:
>
> {code:java}
> @Test void testMaxMinOnBooleanColumn(){
> ...
> final String expectedSnowflake = "SELECT BOOLOR_AGG(\"brand_name\" =
> 'a'), "
> + "BOOLAND_AGG(\"brand_name\" = 'a'), "
> + "MIN(\"brand_name\")\n"
> + "FROM \"foodmart\".\"product\"";
> sql(query)
> .ok(expected)
> .withBigQuery().ok(expectedBigQuery)
> .withPostgresql().ok(expectedPostgres)
> .withSnowflak().ok(expectedSnowflake)
> .withRedshift().ok(expectedPostgres);
> }
> {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6441) Add BOOLAGG_AND, BOOLAGG_OR aggregate functions (enabled in Snowflake library)
Julian Hyde created CALCITE-6441: Summary: Add BOOLAGG_AND, BOOLAGG_OR aggregate functions (enabled in Snowflake library) Key: CALCITE-6441 URL: https://issues.apache.org/jira/browse/CALCITE-6441 Project: Calcite Issue Type: Improvement Reporter: Julian Hyde Add BOOLAGG_AND, BOOLAGG_OR aggregate functions. These functions are the Snowflake equivalent to the standard EVERY and SOME aggregate functions. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6439) FILTER_INSTANCE in RelOptRule does not take effect
[
https://issues.apache.org/jira/browse/CALCITE-6439?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17855454#comment-17855454
]
Julian Hyde commented on CALCITE-6439:
--
There should be existing code that converts {{WHERE NULL}} to {{WHERE FALSE}}.
If there isn't, you should fix {{RelBuilder.filter}} and add a test to
{{RelBuilderTest}}. {{Filter.isValid}} should check that the condition is of
type {{BOOLEAN}}, and the {{AND}}, {{OR}}, {{NOT}} operators should do the same
of their arguments.
> FILTER_INSTANCE in RelOptRule does not take effect
> --
>
> Key: CALCITE-6439
> URL: https://issues.apache.org/jira/browse/CALCITE-6439
> Project: Calcite
> Issue Type: Wish
> Components: core
>Affects Versions: 1.37.0
>Reporter: Caican Cai
>Priority: Minor
> Fix For: 1.38.0
>
>
> test example
> {code:java}
> @Test void testFilterNull() {
> final String sql = "select * from emp where null";
> sql(sql).withRule(PruneEmptyRules.FILTER_INSTANCE).check();
> } {code}
> logical plan
> {code:java}
>
>
>
>
>
>
>
>
>
>
> {code}
> unchanged.
> The logical plan that should be generated after optimization
> {code:java}
> LogicalValues(tuples=[[]]){code}
> or
> {code:java}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalValues(tuples=[[]]) {code} {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
