[jira] [Comment Edited] (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=17849032#comment-17849032
]
Julian Hyde edited comment on CALCITE-6414 at 5/23/24 4:40 PM:
---
Makes sense. Looks like a small change to {{SqlDialect.rewriteMaxMin}} or
{{rewriteMaxMinExpr}} (added in CALCITE-6220) should do it. The fix should also
deal with the unused {{expectedRedshift}} variable in
{{testMaxMinOnBooleanColumn}}.
was (Author: julianhyde):
Makes sense. Looks like a small change to {{SqlDialect.rewriteMaxMin}} or
{{rewriteMaxMinExpr}} (added in CALCITE-6220) should do it. The fix should also
deal with the unused \{{expectedRedshift}} variable in
\{{testMaxMinOnBooleanColumn}}
> 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
>Priority: Trivial
>
> 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=17849032#comment-17849032
]
Julian Hyde commented on CALCITE-6414:
--
Makes sense. Looks like a small change to {{SqlDialect.rewriteMaxMin}} or
{{rewriteMaxMinExpr}} (added in CALCITE-6220) should do it. The fix should also
deal with the unused \{{expectedRedshift}} variable in
\{{testMaxMinOnBooleanColumn}}
> 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
>Priority: Trivial
>
> 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-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=17848330#comment-17848330
]
Julian Hyde commented on CALCITE-6413:
--
One small quibble. I believe the coercion logic is invoked by the
sql-to-rel-converter, not the validator.
The fix should include two tests:
* One where the conversion is not valid (i.e. the types are not comparable);
the validator test should check that an error is thrown.
* One where the conversion is valid but the coercion generates non-trivial
code; a sql-to-rel-converter test should check that two equivalent queries
written using ON and USING generate identical plans.
> 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
>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] [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=17848328#comment-17848328
]
Julian Hyde commented on CALCITE-6414:
--
[~jswett], I changed the summary. Let me know if the new summary is wrong.
I guess Snowflake doesn't allow MAX and MIN on BOOLEAN values?
> 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
>Priority: Trivial
>
> 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] [Updated] (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 updated CALCITE-6414:
-
Summary: Snowflake JDBC adapter should generate BOOLOR_AGG, BOOLAND_AGG for
MAX, MIN on BOOLEAN values (was: Resolve Snoflake SQL generation of BOOL_OR,
BOOL_AND )
> 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
>Priority: Trivial
>
> 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-6409) Char types and Boolean types are comparable
[ https://issues.apache.org/jira/browse/CALCITE-6409?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17846030#comment-17846030 ] Julian Hyde commented on CALCITE-6409: -- Who said the types need to be comparable? Comparable means I can use them in an expression like 'x < y'. For NVL and NVL2 we need them to be assignable to a most general type, as in 'w = b ? x : y' in Java, and also similar to CASE and UNION SQL operators. [~mbudiu], I think some confusion arises because SameOperandTypeChecker says operands "must be comparable"; it has a subclass ComparableTypeChecker, so what would be the purpose of that subclass? I wonder whether SameOperandTypeChecker has been used for functions that it should not be; and once those functions used it wrongly, someone changed the definition of 'same' to make those functions work. > Char types and Boolean types are comparable > --- > > Key: CALCITE-6409 > URL: https://issues.apache.org/jira/browse/CALCITE-6409 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.37.0 >Reporter: Caican Cai >Priority: Critical > Fix For: 1.38.0 > > > In the SameOperandTypeChecker method, the char type and the boolean type are > comparable because of the call to the isComparable method. > Comparability of char types and boolean types returns true in the > isComparable method. > However, char types and Boolean types in Spark are incomparable. Does Calcite > have fixed standards here? -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6400) MAP_ENTRIES allows null as a map key
[
https://issues.apache.org/jira/browse/CALCITE-6400?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17845929#comment-17845929
]
Julian Hyde edited comment on CALCITE-6400 at 5/13/24 1:45 PM:
---
How about “MAP_ENTRIES function should throw if a key value is null”?
An earlier comment said the summary should not say “what is to be done”. I
agree with that comment. My proposed summary is not describing a fix, merely
stating desired behavior. The phrases “should” and “should not” are useful
because they clearly distinguish observed vs desired behavior.
was (Author: julianhyde):
How about “MAP_ENTRIES should throw if a key value is null”?
An earlier comment said the summary should not say “what is to be done”. I
agree with that comment. My proposed summary is not describing a fix, merely
stating desired behavior. The phrases “should” and “should not” are useful
because they clearly distinguish observed vs desired behavior.
> MAP_ENTRIES allows null as a map key
>
>
> Key: CALCITE-6400
> URL: https://issues.apache.org/jira/browse/CALCITE-6400
> 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
>
>
> map_entries does not allow null as the key value of the map. The null
> mentioned here is a subset of the set, not that the set is empty.
> {code:java}
> scala> val df = spark.sql("select map_entries(map('foo', 1, null, 2.0))")
> df: org.apache.spark.sql.DataFrame = [map_entries(map(foo, 1, NULL, 2.0)):
> array>]
> scala> df.show()
> org.apache.spark.SparkRuntimeException: [NULL_MAP_KEY] Cannot use null as map
> key.
> at
> org.apache.spark.sql.errors.QueryExecutionErrors$.nullAsMapKeyNotAllowedError(QueryExecutionErrors.scala:445)
> at
> org.apache.spark.sql.catalyst.util.ArrayBasedMapBuilder.put(ArrayBasedMapBuilder.scala:56)
> at
> org.apache.spark.sql.catalyst.expressions.CreateMap.eval(complexTypeCreator.scala:248)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.eval(Expression.scala:542)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.eval(Expression.scala:542)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.org$apache$spark$sql$catalyst$optimizer$ConstantFolding$$constantFolding(expressions.scala:80)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.$anonfun$constantFolding$4(expressions.scala:90)
> at
> org.apache.spark.sql.catalyst.trees.UnaryLike.mapChildren(TreeNode.scala:1249)
> at
> org.apache.spark.sql.catalyst.trees.UnaryLike.mapChildren$(TreeNode.scala:1248)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.mapChildren(Expression.scala:532)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.org$apache$spark$sql$catalyst$optimizer$ConstantFolding$$constantFolding(expressions.scala:90)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.$anonfun$applyOrElse$1(expressions.scala:94)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$1(QueryPlan.scala:207)
> at
> org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:104)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.transformExpression$1(QueryPlan.scala:207)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.recursiveTransform$1(QueryPlan.scala:218)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$3(QueryPlan.scala:223)
> at
> scala.collection.TraversableLike.$anonfun$map$1(TraversableLike.scala:286)
> at scala.collection.immutable.List.foreach(List.scala:431)
> at scala.collection.TraversableLike.map(TraversableLike.scala:286)
> at scala.collection.TraversableLike.map$(TraversableLike.scala:279)
> at scala.collection.immutable.List.map(List.scala:305)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.recursiveTransform$1(QueryPlan.scala:223)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$4(QueryPlan.scala:228)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.mapProductIterator(TreeNode.scala:355)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.mapExpressions(QueryPlan.scala:228)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.applyOrElse(expressions.scala:94)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.applyOrElse(expressions.scala:93)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.$anonfun$transformDownWithPruning$1(TreeNode.scala:512)
> at
> org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:104)
> at
>
[jira] [Commented] (CALCITE-6400) MAP_ENTRIES allows null as a map key
[
https://issues.apache.org/jira/browse/CALCITE-6400?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17845929#comment-17845929
]
Julian Hyde commented on CALCITE-6400:
--
How about “MAP_ENTRIES should throw if a key value is null”?
An earlier comment said the summary should not say “what is to be done”. I
agree with that comment. My proposed summary is not describing a fix, merely
stating desired behavior. The phrases “should” and “should not” are useful
because they clearly distinguish observed vs desired behavior.
> MAP_ENTRIES allows null as a map key
>
>
> Key: CALCITE-6400
> URL: https://issues.apache.org/jira/browse/CALCITE-6400
> 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
>
>
> map_entries does not allow null as the key value of the map. The null
> mentioned here is a subset of the set, not that the set is empty.
> {code:java}
> scala> val df = spark.sql("select map_entries(map('foo', 1, null, 2.0))")
> df: org.apache.spark.sql.DataFrame = [map_entries(map(foo, 1, NULL, 2.0)):
> array>]
> scala> df.show()
> org.apache.spark.SparkRuntimeException: [NULL_MAP_KEY] Cannot use null as map
> key.
> at
> org.apache.spark.sql.errors.QueryExecutionErrors$.nullAsMapKeyNotAllowedError(QueryExecutionErrors.scala:445)
> at
> org.apache.spark.sql.catalyst.util.ArrayBasedMapBuilder.put(ArrayBasedMapBuilder.scala:56)
> at
> org.apache.spark.sql.catalyst.expressions.CreateMap.eval(complexTypeCreator.scala:248)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.eval(Expression.scala:542)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.eval(Expression.scala:542)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.org$apache$spark$sql$catalyst$optimizer$ConstantFolding$$constantFolding(expressions.scala:80)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.$anonfun$constantFolding$4(expressions.scala:90)
> at
> org.apache.spark.sql.catalyst.trees.UnaryLike.mapChildren(TreeNode.scala:1249)
> at
> org.apache.spark.sql.catalyst.trees.UnaryLike.mapChildren$(TreeNode.scala:1248)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.mapChildren(Expression.scala:532)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.org$apache$spark$sql$catalyst$optimizer$ConstantFolding$$constantFolding(expressions.scala:90)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.$anonfun$applyOrElse$1(expressions.scala:94)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$1(QueryPlan.scala:207)
> at
> org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:104)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.transformExpression$1(QueryPlan.scala:207)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.recursiveTransform$1(QueryPlan.scala:218)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$3(QueryPlan.scala:223)
> at
> scala.collection.TraversableLike.$anonfun$map$1(TraversableLike.scala:286)
> at scala.collection.immutable.List.foreach(List.scala:431)
> at scala.collection.TraversableLike.map(TraversableLike.scala:286)
> at scala.collection.TraversableLike.map$(TraversableLike.scala:279)
> at scala.collection.immutable.List.map(List.scala:305)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.recursiveTransform$1(QueryPlan.scala:223)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$4(QueryPlan.scala:228)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.mapProductIterator(TreeNode.scala:355)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.mapExpressions(QueryPlan.scala:228)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.applyOrElse(expressions.scala:94)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.applyOrElse(expressions.scala:93)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.$anonfun$transformDownWithPruning$1(TreeNode.scala:512)
> at
> org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:104)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.transformDownWithPruning(TreeNode.scala:512)
> at
> org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.org$apache$spark$sql$catalyst$plans$logical$AnalysisHelper$$super$transformDownWithPruning(LogicalPlan.scala:31)
> at
> org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning(AnalysisHelper.scala:267)
> at
>
[jira] [Comment Edited] (CALCITE-6409) Char types and Boolean types are comparable
[ https://issues.apache.org/jira/browse/CALCITE-6409?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17845744#comment-17845744 ] Julian Hyde edited comment on CALCITE-6409 at 5/12/24 8:42 PM: --- [~caicancai], Please describe what research you have done, including related Jira cases. Also, make sure that your use case really needs comparable types before you complain about the definition of "comparable". was (Author: julianhyde): [~caicancai], Please describe what research you have done, including related Jira cases. > Char types and Boolean types are comparable > --- > > Key: CALCITE-6409 > URL: https://issues.apache.org/jira/browse/CALCITE-6409 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.37.0 >Reporter: Caican Cai >Priority: Critical > Fix For: 1.38.0 > > > In the SameOperandTypeChecker method, the char type and the boolean type are > comparable because of the call to the isComparable method. > Comparability of char types and boolean types returns true in the > isComparable method. > However, char types and Boolean types in Spark are incomparable. Does Calcite > have fixed standards here? -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6409) Char types and Boolean types are comparable
[ https://issues.apache.org/jira/browse/CALCITE-6409?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17845744#comment-17845744 ] Julian Hyde commented on CALCITE-6409: -- [~caicancai], Please describe what research you have done, including related Jira cases. > Char types and Boolean types are comparable > --- > > Key: CALCITE-6409 > URL: https://issues.apache.org/jira/browse/CALCITE-6409 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.37.0 >Reporter: Caican Cai >Priority: Critical > Fix For: 1.38.0 > > > In the SameOperandTypeChecker method, the char type and the boolean type are > comparable because of the call to the isComparable method. > Comparability of char types and boolean types returns true in the > isComparable method. > However, char types and Boolean types in Spark are incomparable. Does Calcite > have fixed standards here? -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6410) dateadd(MONTH, 3, date '2016-02-24') parsing failed
[
https://issues.apache.org/jira/browse/CALCITE-6410?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17845743#comment-17845743
]
Julian Hyde commented on CALCITE-6410:
--
Not a bug. That syntax is only supported in the Babel parser.
> dateadd(MONTH, 3, date '2016-02-24') parsing failed
> ---
>
> Key: CALCITE-6410
> URL: https://issues.apache.org/jira/browse/CALCITE-6410
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: Caican Cai
>Priority: Major
> Fix For: 1.38.0
>
>
> The dateadd and datediff tests are missing in SqlOperatorTest. I tried to add
> them to improve it. The test code is as follows
> {code:java}
> @Test void testDateAdd2() {
> final SqlOperatorFixture f = Fixtures.forOperators(true)
> .setFor(SqlLibraryOperators.DATEADD);
> MONTH_VARIANTS.forEach(s ->
> f.checkScalar("dateadd(" + s
> + ", 3, date '2016-02-24')",
> "2016-05-24 12:42:25", "TIMESTAMP(0) NOT NULL"));
> } {code}
> fail message：
> java.lang.RuntimeException: Error while parsing query: values (dateadd(MONTH,
> 3, date '2016-02-24'))
> at
> org.apache.calcite.sql.test.AbstractSqlTester.parseAndValidate(AbstractSqlTester.java:159)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.validateAndThen(AbstractSqlTester.java:250)
> at
> org.apache.calcite.test.SqlOperatorFixtureImpl.lambda$forEachQueryValidateAndThen$1(SqlOperatorFixtureImpl.java:154)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.forEachQuery(AbstractSqlTester.java:450)
> at
> org.apache.calcite.test.SqlOperatorFixtureImpl.forEachQueryValidateAndThen(SqlOperatorFixtureImpl.java:153)
> at
> org.apache.calcite.test.SqlOperatorFixtureImpl.checkType(SqlOperatorFixtureImpl.java:130)
> at
> org.apache.calcite.sql.test.SqlOperatorFixture.checkScalar(SqlOperatorFixture.java:220)
> at
> org.apache.calcite.test.SqlOperatorTest.lambda$testDateAdd2$150(SqlOperatorTest.java:12789)
> at java.util.Arrays$ArrayList.forEach(Arrays.java:3880)
> at
> org.apache.calcite.test.SqlOperatorTest.testDateAdd2(SqlOperatorTest.java:12788)
> 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
> 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] [Updated] (CALCITE-6408) Not-null ThreadLocal
[
https://issues.apache.org/jira/browse/CALCITE-6408?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6408:
-
Fix Version/s: 1.38.0
> Not-null ThreadLocal
>
>
> Key: CALCITE-6408
> URL: https://issues.apache.org/jira/browse/CALCITE-6408
> Project: Calcite
> Issue Type: Improvement
>Reporter: Julian Hyde
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.38.0
>
>
> Many points in the code need to wrap {{ThreadLocal.get()}} in
> {{requireNonNull}} or {{castNonNull}} to keep checkerFramework happy. This
> change removes those, by providing a sub-class of ThreadLocal whose values
> are known to be never null.
> In CALCITE-915 we added {{class TryThreadLocal}} to make it easier to
> remember to unset a thread-local value, and in commit
> [f82028f4|https://github.com/apache/calcite/commit/f82028f4b983707bd9af3d9ba73820ef2431e971]
> we made its {{get}} method return a not-null value. But the only constructor
> was {{{}TryThreadLocal.of(T initialValue){}}}. In this change, we add
> {{{}TryThreadLocal.withSupplier(Supplier){}}}, so that each thread can
> have its own value, and convert all uses of {{ThreadLocal}} where to use it.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-6408) Not-null ThreadLocal
[
https://issues.apache.org/jira/browse/CALCITE-6408?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde reassigned CALCITE-6408:
Assignee: Julian Hyde
> Not-null ThreadLocal
>
>
> Key: CALCITE-6408
> URL: https://issues.apache.org/jira/browse/CALCITE-6408
> Project: Calcite
> Issue Type: Improvement
>Reporter: Julian Hyde
>Assignee: Julian Hyde
>Priority: Major
>
> Many points in the code need to wrap {{ThreadLocal.get()}} in
> {{requireNonNull}} or {{castNonNull}} to keep checkerFramework happy. This
> change removes those, by providing a sub-class of ThreadLocal whose values
> are known to be never null.
> In CALCITE-915 we added {{class TryThreadLocal}} to make it easier to
> remember to unset a thread-local value, and in commit
> [f82028f4|https://github.com/apache/calcite/commit/f82028f4b983707bd9af3d9ba73820ef2431e971]
> we made its {{get}} method return a not-null value. But the only constructor
> was {{{}TryThreadLocal.of(T initialValue){}}}. In this change, we add
> {{{}TryThreadLocal.withSupplier(Supplier){}}}, so that each thread can
> have its own value, and convert all uses of {{ThreadLocal}} where to use it.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6408) Not-null ThreadLocal
Julian Hyde created CALCITE-6408:
Summary: Not-null ThreadLocal
Key: CALCITE-6408
URL: https://issues.apache.org/jira/browse/CALCITE-6408
Project: Calcite
Issue Type: Improvement
Reporter: Julian Hyde
Many points in the code need to wrap {{ThreadLocal.get()}} in
{{requireNonNull}} or {{castNonNull}} to keep checkerFramework happy. This
change removes those, by providing a sub-class of ThreadLocal whose values are
known to be never null.
In CALCITE-915 we added {{class TryThreadLocal}} to make it easier to remember
to unset a thread-local value, and in commit
[f82028f4|https://github.com/apache/calcite/commit/f82028f4b983707bd9af3d9ba73820ef2431e971]
we made its {{get}} method return a not-null value. But the only constructor
was {{{}TryThreadLocal.of(T initialValue){}}}. In this change, we add
{{{}TryThreadLocal.withSupplier(Supplier){}}}, so that each thread can have
its own value, and convert all uses of {{ThreadLocal}} where to use it.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6397) Add NVL2 function (enabled in Spark library)
[
https://issues.apache.org/jira/browse/CALCITE-6397?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17845468#comment-17845468
]
Julian Hyde commented on CALCITE-6397:
--
The Spark specification is not always great. According to the Spark site, the
ADD_MONTHS takes a string argument, but the same function on DataBricks site
takes a date.
My instinct (reading the Oracle description of NVL2) is that {{NVL2(a, b, c)}}
is equivalent to {{CASE WHEN a IS NULL THEN b ELSE c END}} and should use the
same type-checking strategy (find the most general type of b and c, fail if
there is no general type).
> Add NVL2 function (enabled in Spark library)
>
>
> Key: CALCITE-6397
> URL: https://issues.apache.org/jira/browse/CALCITE-6397
> Project: Calcite
> Issue Type: New Feature
>Reporter: Caican Cai
>Priority: Major
> Labels: pull-request-available
>
> Add NVL2 function (enabled in Spark library)
>
> https://spark.apache.org/docs/2.3.0/api/sql/index.html#nvl2
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6403) Rule AGGREGATE_EXPAND_DISTINCT_AGGREGATES is unsound
[
https://issues.apache.org/jira/browse/CALCITE-6403?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17844800#comment-17844800
]
Julian Hyde commented on CALCITE-6403:
--
Does the same problem occur when the input relation is empty but the optimizer
cannot prove that it is empty? E.g.
{code:java}
SELECT COUNT(*), COUNT(DISTINCT deptno)
FROM emp
WHERE deptno < 0 {code}
> Rule AGGREGATE_EXPAND_DISTINCT_AGGREGATES is unsound
>
>
> Key: CALCITE-6403
> URL: https://issues.apache.org/jira/browse/CALCITE-6403
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.37.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> Here is a simple SQL program that operates on a collection with no input rows:
> {code:sql}
> SELECT COUNT(*), COUNT(DISTINCT COL1) FROM T WHERE false
> {code}
> The rewrite rule expands this into:
> {code}
> LogicalProject(EXPR$0=[CAST($0):BIGINT NOT NULL], EXPR$1=[$1]), id = 39
> LogicalAggregate(group=[{}], EXPR$0=[MIN($1) FILTER $3],
> EXPR$1=[COUNT($0) FILTER $2]), id = 37
> LogicalProject(COL1=[$0], EXPR$0=[$1], $g_0=[=($2, 0)], $g_1=[=($2,
> 1)]), id = 35
> LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$0=[COUNT()],
> $g=[GROUPING($0)]), id = 30
> LogicalValues(tuples=[[]]), id = 22
> {code}
> Notice that there is an inner group-by aggregate that produces an empty set,
> and an outer aggregation that uses MIN. MIN for an empty collection is NULL,
> whereas the original query should have produced 0.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6402) Aggregates implied in grouping sets have a wrong nullability at validation stage
[
https://issues.apache.org/jira/browse/CALCITE-6402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17844737#comment-17844737
]
Julian Hyde edited comment on CALCITE-6402 at 5/8/24 6:41 PM:
--
I agree with your analysis.
It seems we need a means other than {{int groupCount}} to determine whether the
output will contain rows that are the aggregate of zero rows. Can you propose
something? I would be open to obsoleting {{groupCount}} (i.e. deprecating the
method and ceasing to use it) and using something else.
When implementing CALCITE-704, I [commented
that|https://github.com/apache/calcite/blob/7ef829e17cf382e34b2c5ee36a664d98a7258c31/core/src/main/java/org/apache/calcite/sql/SqlFilterOperator.java#L103]
that "[pretending that group-count is 0] tells the aggregate function that it
might be invoked with 0 rows in a group". I think we should continue with that
approach, or maybe make it more explicit.
was (Author: julianhyde):
I agree with your analysis.
It seems we need a means other than {{int groupCount}} to determine whether the
output will contain rows that are the aggregate of zero rows. Can you propose
something? I would be open to obsoleting {{groupCount}} (i.e. deprecating the
method and ceasing to use it) and using something else.
> Aggregates implied in grouping sets have a wrong nullability at validation
> stage
>
>
> Key: CALCITE-6402
> URL: https://issues.apache.org/jira/browse/CALCITE-6402
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Claude Brisson
>Priority: Major
>
> As long as the empty set is present among the grouping sets, which is always
> the case for CUBE and ROLLAP, the (unfiltered) result will contain a row with
> the global aggregate. And on such a row, most standard aggregate functions
> are nullable (even on a non-null column, for the empty rowset).
> But the SUM function, for instance, has the following return type inference:
> {code}
> public static final SqlReturnTypeInference AGG_SUM = opBinding -> {
> final RelDataTypeFactory typeFactory = opBinding.getTypeFactory();
> final RelDataType type = typeFactory.getTypeSystem()
> .deriveSumType(typeFactory, opBinding.getOperandType(0));
> if (opBinding.getGroupCount() == 0 || opBinding.hasFilter()) {
> return typeFactory.createTypeWithNullability(type, true);
> } else {
> return type;
> }
> };
> {code}
> If the operand is not nullable, since the group count will be non-zero for a
> rollup, a cube or a grouping sets containing the empty set.
> It seems to me that the group count itself is not a sufficient information to
> determine the nullability, we may be lacking a boolean stating whether the
> empty group is implied, or the complete list of groups instead of the groups
> count.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6402) Aggregates implied in grouping sets have a wrong nullability at validation stage
[
https://issues.apache.org/jira/browse/CALCITE-6402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17844737#comment-17844737
]
Julian Hyde commented on CALCITE-6402:
--
I agree with your analysis.
It seems we need a means other than {{int groupCount}} to determine whether the
output will contain rows that are the aggregate of zero rows. Can you propose
something? I would be open to obsoleting {{groupCount}} (i.e. deprecating the
method and ceasing to use it) and using something else.
> Aggregates implied in grouping sets have a wrong nullability at validation
> stage
>
>
> Key: CALCITE-6402
> URL: https://issues.apache.org/jira/browse/CALCITE-6402
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Claude Brisson
>Priority: Major
>
> As long as the empty set is present among the grouping sets, which is always
> the case for CUBE and ROLLAP, the (unfiltered) result will contain a row with
> the global aggregate. And on such a row, most standard aggregate functions
> are nullable (even on a non-null column, for the empty rowset).
> But the SUM function, for instance, has the following return type inference:
> {code}
> public static final SqlReturnTypeInference AGG_SUM = opBinding -> {
> final RelDataTypeFactory typeFactory = opBinding.getTypeFactory();
> final RelDataType type = typeFactory.getTypeSystem()
> .deriveSumType(typeFactory, opBinding.getOperandType(0));
> if (opBinding.getGroupCount() == 0 || opBinding.hasFilter()) {
> return typeFactory.createTypeWithNullability(type, true);
> } else {
> return type;
> }
> };
> {code}
> If the operand is not nullable, since the group count will be non-zero for a
> rollup, a cube or a grouping sets containing the empty set.
> It seems to me that the group count itself is not a sufficient information to
> determine the nullability, we may be lacking a boolean stating whether the
> empty group is implied, or the complete list of groups instead of the groups
> count.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6400) MAP_ENTRIES does not allow null as a map key
[
https://issues.apache.org/jira/browse/CALCITE-6400?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17844732#comment-17844732
]
Julian Hyde commented on CALCITE-6400:
--
When I read the current summary, 'MAP_ENTRIES does not allow null as a map
key', it seems to be saying 'MAP_ENTRIES should allow null as a map key'. But
Spark according to your example, Spark does not allow null. So, do you want to
be like Spark?
> MAP_ENTRIES does not allow null as a map key
>
>
> Key: CALCITE-6400
> URL: https://issues.apache.org/jira/browse/CALCITE-6400
> 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
>
>
> map_entries does not allow null as the key value of the map. The null
> mentioned here is a subset of the set, not that the set is empty.
> {code:java}
> scala> val df = spark.sql("select map_entries(map('foo', 1, null, 2.0))")
> df: org.apache.spark.sql.DataFrame = [map_entries(map(foo, 1, NULL, 2.0)):
> array>]
> scala> df.show()
> org.apache.spark.SparkRuntimeException: [NULL_MAP_KEY] Cannot use null as map
> key.
> at
> org.apache.spark.sql.errors.QueryExecutionErrors$.nullAsMapKeyNotAllowedError(QueryExecutionErrors.scala:445)
> at
> org.apache.spark.sql.catalyst.util.ArrayBasedMapBuilder.put(ArrayBasedMapBuilder.scala:56)
> at
> org.apache.spark.sql.catalyst.expressions.CreateMap.eval(complexTypeCreator.scala:248)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.eval(Expression.scala:542)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.eval(Expression.scala:542)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.org$apache$spark$sql$catalyst$optimizer$ConstantFolding$$constantFolding(expressions.scala:80)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.$anonfun$constantFolding$4(expressions.scala:90)
> at
> org.apache.spark.sql.catalyst.trees.UnaryLike.mapChildren(TreeNode.scala:1249)
> at
> org.apache.spark.sql.catalyst.trees.UnaryLike.mapChildren$(TreeNode.scala:1248)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.mapChildren(Expression.scala:532)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.org$apache$spark$sql$catalyst$optimizer$ConstantFolding$$constantFolding(expressions.scala:90)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.$anonfun$applyOrElse$1(expressions.scala:94)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$1(QueryPlan.scala:207)
> at
> org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:104)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.transformExpression$1(QueryPlan.scala:207)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.recursiveTransform$1(QueryPlan.scala:218)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$3(QueryPlan.scala:223)
> at
> scala.collection.TraversableLike.$anonfun$map$1(TraversableLike.scala:286)
> at scala.collection.immutable.List.foreach(List.scala:431)
> at scala.collection.TraversableLike.map(TraversableLike.scala:286)
> at scala.collection.TraversableLike.map$(TraversableLike.scala:279)
> at scala.collection.immutable.List.map(List.scala:305)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.recursiveTransform$1(QueryPlan.scala:223)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$4(QueryPlan.scala:228)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.mapProductIterator(TreeNode.scala:355)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.mapExpressions(QueryPlan.scala:228)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.applyOrElse(expressions.scala:94)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.applyOrElse(expressions.scala:93)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.$anonfun$transformDownWithPruning$1(TreeNode.scala:512)
> at
> org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:104)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.transformDownWithPruning(TreeNode.scala:512)
> at
> org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.org$apache$spark$sql$catalyst$plans$logical$AnalysisHelper$$super$transformDownWithPruning(LogicalPlan.scala:31)
> at
> org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning(AnalysisHelper.scala:267)
> at
> org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning$(AnalysisHelper.scala:263)
> at
>
[jira] [Commented] (CALCITE-6396) Add ADD_MONTHS function (enabled in Spark library)
[
https://issues.apache.org/jira/browse/CALCITE-6396?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17844481#comment-17844481
]
Julian Hyde commented on CALCITE-6396:
--
The first argument, and the return, should be a {{{}DATE{}}}.
Is {{ADD_MONTHS(d, n)}} equivalent to {{{}DATEADD(MONTH, d, n){}}}? If so, you
should implement it using rewrites (same way that {{DATEADD}} is implemented),
not by adding a method to {{{}SqlFunctions{}}}.
You should test negative values of n.
> Add ADD_MONTHS function (enabled in Spark library)
> --
>
> Key: CALCITE-6396
> URL: https://issues.apache.org/jira/browse/CALCITE-6396
> Project: Calcite
> Issue Type: New Feature
> Components: core
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.38.0
>
>
> Add ADD_MONTHS function (enabled in Spark library)
>
> https://spark.apache.org/docs/2.3.0/api/sql/index.html#add_months
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Comment Edited] (CALCITE-6400) MAP_ENTRIES does not allow the key to be empty
[
https://issues.apache.org/jira/browse/CALCITE-6400?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17843477#comment-17843477
]
Julian Hyde edited comment on CALCITE-6400 at 5/4/24 5:27 PM:
--
Can you improve the summary? It's not clear whether you think Calcite should
allow the entry to be empty or should not. Also the test you have provided has
a null value not an empty collection.
Also, 'avatica' is the wrong component.
was (Author: julianhyde):
Can you improve the summary? It's not clear whether you think Calcite should
allow the entry to be empty or should not. Also the test you have provided has
a null value not an empty collection.
Also, the component is not 'avatica'.
> MAP_ENTRIES does not allow the key to be empty
> ---
>
> Key: CALCITE-6400
> URL: https://issues.apache.org/jira/browse/CALCITE-6400
> Project: Calcite
> Issue Type: Bug
> Components: avatica
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> {code:java}
> scala> val df = spark.sql("select map_entries(map('foo', 1, null, 2.0))")
> df: org.apache.spark.sql.DataFrame = [map_entries(map(foo, 1, NULL, 2.0)):
> array>]
> scala> df.show()
> org.apache.spark.SparkRuntimeException: [NULL_MAP_KEY] Cannot use null as map
> key.
> at
> org.apache.spark.sql.errors.QueryExecutionErrors$.nullAsMapKeyNotAllowedError(QueryExecutionErrors.scala:445)
> at
> org.apache.spark.sql.catalyst.util.ArrayBasedMapBuilder.put(ArrayBasedMapBuilder.scala:56)
> at
> org.apache.spark.sql.catalyst.expressions.CreateMap.eval(complexTypeCreator.scala:248)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.eval(Expression.scala:542)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.eval(Expression.scala:542)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.org$apache$spark$sql$catalyst$optimizer$ConstantFolding$$constantFolding(expressions.scala:80)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.$anonfun$constantFolding$4(expressions.scala:90)
> at
> org.apache.spark.sql.catalyst.trees.UnaryLike.mapChildren(TreeNode.scala:1249)
> at
> org.apache.spark.sql.catalyst.trees.UnaryLike.mapChildren$(TreeNode.scala:1248)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.mapChildren(Expression.scala:532)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.org$apache$spark$sql$catalyst$optimizer$ConstantFolding$$constantFolding(expressions.scala:90)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.$anonfun$applyOrElse$1(expressions.scala:94)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$1(QueryPlan.scala:207)
> at
> org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:104)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.transformExpression$1(QueryPlan.scala:207)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.recursiveTransform$1(QueryPlan.scala:218)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$3(QueryPlan.scala:223)
> at
> scala.collection.TraversableLike.$anonfun$map$1(TraversableLike.scala:286)
> at scala.collection.immutable.List.foreach(List.scala:431)
> at scala.collection.TraversableLike.map(TraversableLike.scala:286)
> at scala.collection.TraversableLike.map$(TraversableLike.scala:279)
> at scala.collection.immutable.List.map(List.scala:305)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.recursiveTransform$1(QueryPlan.scala:223)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$4(QueryPlan.scala:228)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.mapProductIterator(TreeNode.scala:355)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.mapExpressions(QueryPlan.scala:228)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.applyOrElse(expressions.scala:94)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.applyOrElse(expressions.scala:93)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.$anonfun$transformDownWithPruning$1(TreeNode.scala:512)
> at
> org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:104)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.transformDownWithPruning(TreeNode.scala:512)
> at
> org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.org$apache$spark$sql$catalyst$plans$logical$AnalysisHelper$$super$transformDownWithPruning(LogicalPlan.scala:31)
> at
> org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning(AnalysisHelper.scala:267)
> at
>
[jira] [Comment Edited] (CALCITE-6400) MAP_ENTRIES does not allow the key to be empty
[
https://issues.apache.org/jira/browse/CALCITE-6400?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17843477#comment-17843477
]
Julian Hyde edited comment on CALCITE-6400 at 5/4/24 5:26 PM:
--
Can you improve the summary? It's not clear whether you think Calcite should
allow the entry to be empty or should not. Also the test you have provided has
a null value not an empty collection.
Also, the component is not 'avatica'.
was (Author: julianhyde):
Can you improve the summary? It's not clear whether you think Calcite should
allow the entry to be empty or should not. Also the test you have provided has
a null value not an empty collection.
> MAP_ENTRIES does not allow the key to be empty
> ---
>
> Key: CALCITE-6400
> URL: https://issues.apache.org/jira/browse/CALCITE-6400
> Project: Calcite
> Issue Type: Bug
> Components: avatica
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> {code:java}
> scala> val df = spark.sql("select map_entries(map('foo', 1, null, 2.0))")
> df: org.apache.spark.sql.DataFrame = [map_entries(map(foo, 1, NULL, 2.0)):
> array>]
> scala> df.show()
> org.apache.spark.SparkRuntimeException: [NULL_MAP_KEY] Cannot use null as map
> key.
> at
> org.apache.spark.sql.errors.QueryExecutionErrors$.nullAsMapKeyNotAllowedError(QueryExecutionErrors.scala:445)
> at
> org.apache.spark.sql.catalyst.util.ArrayBasedMapBuilder.put(ArrayBasedMapBuilder.scala:56)
> at
> org.apache.spark.sql.catalyst.expressions.CreateMap.eval(complexTypeCreator.scala:248)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.eval(Expression.scala:542)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.eval(Expression.scala:542)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.org$apache$spark$sql$catalyst$optimizer$ConstantFolding$$constantFolding(expressions.scala:80)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.$anonfun$constantFolding$4(expressions.scala:90)
> at
> org.apache.spark.sql.catalyst.trees.UnaryLike.mapChildren(TreeNode.scala:1249)
> at
> org.apache.spark.sql.catalyst.trees.UnaryLike.mapChildren$(TreeNode.scala:1248)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.mapChildren(Expression.scala:532)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.org$apache$spark$sql$catalyst$optimizer$ConstantFolding$$constantFolding(expressions.scala:90)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.$anonfun$applyOrElse$1(expressions.scala:94)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$1(QueryPlan.scala:207)
> at
> org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:104)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.transformExpression$1(QueryPlan.scala:207)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.recursiveTransform$1(QueryPlan.scala:218)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$3(QueryPlan.scala:223)
> at
> scala.collection.TraversableLike.$anonfun$map$1(TraversableLike.scala:286)
> at scala.collection.immutable.List.foreach(List.scala:431)
> at scala.collection.TraversableLike.map(TraversableLike.scala:286)
> at scala.collection.TraversableLike.map$(TraversableLike.scala:279)
> at scala.collection.immutable.List.map(List.scala:305)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.recursiveTransform$1(QueryPlan.scala:223)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$4(QueryPlan.scala:228)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.mapProductIterator(TreeNode.scala:355)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.mapExpressions(QueryPlan.scala:228)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.applyOrElse(expressions.scala:94)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.applyOrElse(expressions.scala:93)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.$anonfun$transformDownWithPruning$1(TreeNode.scala:512)
> at
> org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:104)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.transformDownWithPruning(TreeNode.scala:512)
> at
> org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.org$apache$spark$sql$catalyst$plans$logical$AnalysisHelper$$super$transformDownWithPruning(LogicalPlan.scala:31)
> at
> org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning(AnalysisHelper.scala:267)
> at
>
[jira] [Commented] (CALCITE-6400) MAP_ENTRIES does not allow the key to be empty
[
https://issues.apache.org/jira/browse/CALCITE-6400?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17843477#comment-17843477
]
Julian Hyde commented on CALCITE-6400:
--
Can you improve the summary? It's not clear whether you think Calcite should
allow the entry to be empty or should not. Also the test you have provided has
a null value not an empty collection.
> MAP_ENTRIES does not allow the key to be empty
> ---
>
> Key: CALCITE-6400
> URL: https://issues.apache.org/jira/browse/CALCITE-6400
> Project: Calcite
> Issue Type: Bug
> Components: avatica
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> {code:java}
> scala> val df = spark.sql("select map_entries(map('foo', 1, null, 2.0))")
> df: org.apache.spark.sql.DataFrame = [map_entries(map(foo, 1, NULL, 2.0)):
> array>]
> scala> df.show()
> org.apache.spark.SparkRuntimeException: [NULL_MAP_KEY] Cannot use null as map
> key.
> at
> org.apache.spark.sql.errors.QueryExecutionErrors$.nullAsMapKeyNotAllowedError(QueryExecutionErrors.scala:445)
> at
> org.apache.spark.sql.catalyst.util.ArrayBasedMapBuilder.put(ArrayBasedMapBuilder.scala:56)
> at
> org.apache.spark.sql.catalyst.expressions.CreateMap.eval(complexTypeCreator.scala:248)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.eval(Expression.scala:542)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.eval(Expression.scala:542)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.org$apache$spark$sql$catalyst$optimizer$ConstantFolding$$constantFolding(expressions.scala:80)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.$anonfun$constantFolding$4(expressions.scala:90)
> at
> org.apache.spark.sql.catalyst.trees.UnaryLike.mapChildren(TreeNode.scala:1249)
> at
> org.apache.spark.sql.catalyst.trees.UnaryLike.mapChildren$(TreeNode.scala:1248)
> at
> org.apache.spark.sql.catalyst.expressions.UnaryExpression.mapChildren(Expression.scala:532)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$.org$apache$spark$sql$catalyst$optimizer$ConstantFolding$$constantFolding(expressions.scala:90)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.$anonfun$applyOrElse$1(expressions.scala:94)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$1(QueryPlan.scala:207)
> at
> org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:104)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.transformExpression$1(QueryPlan.scala:207)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.recursiveTransform$1(QueryPlan.scala:218)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$3(QueryPlan.scala:223)
> at
> scala.collection.TraversableLike.$anonfun$map$1(TraversableLike.scala:286)
> at scala.collection.immutable.List.foreach(List.scala:431)
> at scala.collection.TraversableLike.map(TraversableLike.scala:286)
> at scala.collection.TraversableLike.map$(TraversableLike.scala:279)
> at scala.collection.immutable.List.map(List.scala:305)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.recursiveTransform$1(QueryPlan.scala:223)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.$anonfun$mapExpressions$4(QueryPlan.scala:228)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.mapProductIterator(TreeNode.scala:355)
> at
> org.apache.spark.sql.catalyst.plans.QueryPlan.mapExpressions(QueryPlan.scala:228)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.applyOrElse(expressions.scala:94)
> at
> org.apache.spark.sql.catalyst.optimizer.ConstantFolding$$anonfun$apply$1.applyOrElse(expressions.scala:93)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.$anonfun$transformDownWithPruning$1(TreeNode.scala:512)
> at
> org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:104)
> at
> org.apache.spark.sql.catalyst.trees.TreeNode.transformDownWithPruning(TreeNode.scala:512)
> at
> org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.org$apache$spark$sql$catalyst$plans$logical$AnalysisHelper$$super$transformDownWithPruning(LogicalPlan.scala:31)
> at
> org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning(AnalysisHelper.scala:267)
> at
> org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning$(AnalysisHelper.scala:263)
> at
> org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.transformDownWithPruning(LogicalPlan.scala:31)
> at
> org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.transformDownWithPruning(LogicalPlan.scala:31)
> at
>
[jira] [Updated] (CALCITE-6393) Byte code of SqlFunctions is invalid
[
https://issues.apache.org/jira/browse/CALCITE-6393?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6393:
-
Affects Version/s: 1.36.0
> Byte code of SqlFunctions is invalid
>
>
> Key: CALCITE-6393
> URL: https://issues.apache.org/jira/browse/CALCITE-6393
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.36.0
>Reporter: Sergey Nuyanzin
>Priority: Major
>
> The issue is a result of testing of Apache Calcite 1.37.0 rc 4 in this thread
> [1]
> There is test project andprocedure provided by [~MasseGuillaume] [2] (see
> also original thread where this was first discussed [3])
> it shows that since Calcite 1.36.0 it starts failing as
> {noformat}
> java.lang.ArrayIndexOutOfBoundsException: Index 65536 out of bounds for
> length 297
> at org.objectweb.asm.ClassReader.readLabel(ClassReader.java:2695)
> at org.objectweb.asm.ClassReader.createLabel(ClassReader.java:2711)
> at
> org.objectweb.asm.ClassReader.readTypeAnnotations(ClassReader.java:2777)
> at org.objectweb.asm.ClassReader.readCode(ClassReader.java:1929)
> at org.objectweb.asm.ClassReader.readMethod(ClassReader.java:1515)
> at org.objectweb.asm.ClassReader.accept(ClassReader.java:745)
> {noformat}
> Also since Calcite 1.27.0 it starts failing as
> {noformat}
> java.lang.IllegalArgumentException: Invalid end label (must be visited
> first)
> at
> org.objectweb.asm.util.CheckMethodAdapter.checkLabel(CheckMethodAdapter.java:1453)
> at
> org.objectweb.asm.util.CheckMethodAdapter.visitLocalVariableAnnotation(CheckMethodAdapter.java:996)
> at
> org.objectweb.asm.MethodVisitor.visitLocalVariableAnnotation(MethodVisitor.java:757)
> at
> org.objectweb.asm.commons.MethodRemapper.visitLocalVariableAnnotation(MethodRemapper.java:257)
> at org.objectweb.asm.ClassReader.readCode(ClassReader.java:2614)
> at org.objectweb.asm.ClassReader.readMethod(ClassReader.java:1515)
> {noformat}
> [1] https://lists.apache.org/thread/n6cs1l86mt6fc5q8pcxr97czs3p6w65f
> [2] https://github.com/MasseGuillaume/asm-remapper-bug
> [3] https://lists.apache.org/thread/o736wz4qnr4l285bj5gv073cy0qll9t0
--
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=17842472#comment-17842472
]
Julian Hyde commented on CALCITE-4496:
--
An update on the development of this feature. Basic measures are working, and
work continues in my
[4496-measure|https://github.com/julianhyde/calcite/tree/4496-measure] branch;
and I have forked the
[5692-at|https://github.com/julianhyde/calcite/tree/5692-at] branch to work on
the AT operator (CALCITE-5692).
> 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-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=17841666#comment-17841666
]
Julian Hyde commented on CALCITE-6300:
--
Maybe it could get away with it, but it probably shouldn’t. SqlCallBinding has
a “functional programming” vibe and other code might be surprised at side
effects.
if code wants to know the most general key and value types - that is, the
effective type of the arguments in a particular call - the best thing is to ask
for the deduced return type, which will be a map type, and use its key or value
types.
> 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.37.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
>
[jira] [Updated] (CALCITE-6386) Elasticsearch adapter throws NullPointerException when used with with model.json and no username, password or pathPrefix
[
https://issues.apache.org/jira/browse/CALCITE-6386?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6386:
-
Summary: Elasticsearch adapter throws NullPointerException when used with
with model.json and no username, password or pathPrefix (was: NPE when using
ES adapter with model.json and no specified username, password or pathPrefix)
> Elasticsearch adapter throws NullPointerException when used with with
> model.json and no username, password or pathPrefix
>
>
> Key: CALCITE-6386
> URL: https://issues.apache.org/jira/browse/CALCITE-6386
> Project: Calcite
> Issue Type: Bug
> Components: elasticsearch-adapter
>Affects Versions: 1.36.0
>Reporter: guluo
>Priority: Major
> Labels: pull-request-available
>
> Reproduction steps：
> 1 Creating model.json, according to the calcite doc about [Elasticsearch
> adapter
> (apache.org)|https://calcite.apache.org/docs/elasticsearch_adapter.html]
> {
> "version": "1.0",
> "defaultSchema": "elasticsearch",
> "schemas": [
> {
> "type": "custom",
> "name": "elasticsearch",
> "factory":
> "org.apache.calcite.adapter.elasticsearch.ElasticsearchSchemaFactory",
> "operand": {
> "coordinates": "\{'127.0.0.1': 9200}"
> }
> }
> ]
> }
>
> 2 Connecting es by sqlline
> sqlline> !connect jdbc:calcite:model=/root/build/calcite//model.json
>
> 3 We would get NPE,as follow.
> Caused by: java.lang.NullPointerException: at index 1
> at
> com.google.common.collect.ObjectArrays.checkElementNotNull(ObjectArrays.java:232)
> at
> com.google.common.collect.ObjectArrays.checkElementsNotNull(ObjectArrays.java:222)
> at
> com.google.common.collect.ObjectArrays.checkElementsNotNull(ObjectArrays.java:216)
> at
> com.google.common.collect.ImmutableList.construct(ImmutableList.java:354)
> at com.google.common.collect.ImmutableList.of(ImmutableList.java:128)
> at
> org.apache.calcite.adapter.elasticsearch.ElasticsearchSchemaFactory.connect(ElasticsearchSchemaFactory.java:202)
> at
> org.apache.calcite.adapter.elasticsearch.ElasticsearchSchemaFactory.create(ElasticsearchSchemaFactory.java:176)
> at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:275)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6386) NPE when using ES adapter with model.json and no specified username, password or pathPrefix
[
https://issues.apache.org/jira/browse/CALCITE-6386?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17841501#comment-17841501
]
Julian Hyde commented on CALCITE-6386:
--
I changed the summary to remove abbreviations. Please update the pr.
> NPE when using ES adapter with model.json and no specified username, password
> or pathPrefix
> ---
>
> Key: CALCITE-6386
> URL: https://issues.apache.org/jira/browse/CALCITE-6386
> Project: Calcite
> Issue Type: Bug
> Components: elasticsearch-adapter
>Affects Versions: 1.36.0
>Reporter: guluo
>Priority: Major
> Labels: pull-request-available
>
> Reproduction steps：
> 1 Creating model.json, according to the calcite doc about [Elasticsearch
> adapter
> (apache.org)|https://calcite.apache.org/docs/elasticsearch_adapter.html]
> {
> "version": "1.0",
> "defaultSchema": "elasticsearch",
> "schemas": [
> {
> "type": "custom",
> "name": "elasticsearch",
> "factory":
> "org.apache.calcite.adapter.elasticsearch.ElasticsearchSchemaFactory",
> "operand": {
> "coordinates": "\{'127.0.0.1': 9200}"
> }
> }
> ]
> }
>
> 2 Connecting es by sqlline
> sqlline> !connect jdbc:calcite:model=/root/build/calcite//model.json
>
> 3 We would get NPE,as follow.
> Caused by: java.lang.NullPointerException: at index 1
> at
> com.google.common.collect.ObjectArrays.checkElementNotNull(ObjectArrays.java:232)
> at
> com.google.common.collect.ObjectArrays.checkElementsNotNull(ObjectArrays.java:222)
> at
> com.google.common.collect.ObjectArrays.checkElementsNotNull(ObjectArrays.java:216)
> at
> com.google.common.collect.ImmutableList.construct(ImmutableList.java:354)
> at com.google.common.collect.ImmutableList.of(ImmutableList.java:128)
> at
> org.apache.calcite.adapter.elasticsearch.ElasticsearchSchemaFactory.connect(ElasticsearchSchemaFactory.java:202)
> at
> org.apache.calcite.adapter.elasticsearch.ElasticsearchSchemaFactory.create(ElasticsearchSchemaFactory.java:176)
> at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:275)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6373) Distinct optimization produces broken sql query
[
https://issues.apache.org/jira/browse/CALCITE-6373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17841285#comment-17841285
]
Julian Hyde commented on CALCITE-6373:
--
I suspect that the fix only fixes the special case of one variable. If there
are two or more variables they might be re-ordered in the generated SQL. And
even with one variable, the variable might be duplicated in the generated SQL.
I guess it's OK to fix a special case of the bug. But the commit message should
perhaps note that it is a limited fix.
> Distinct optimization produces broken sql query
> ---
>
> Key: CALCITE-6373
> URL: https://issues.apache.org/jira/browse/CALCITE-6373
> Project: Calcite
> Issue Type: Bug
> Components: core, jdbc-adapter
>Affects Versions: 1.36.0
>Reporter: Corvin Kuebler
>Priority: Major
>
> Can be reproduced with the following test in JDBCTests:
> {code:java}
> String statement = "SELECT\n" +
>" DISTINCT \"case-column\"\n" +
>"FROM (\n" +
>" SELECT \n" +
>" CASE\n" +
>" WHEN CAST(? AS VARCHAR) = \"ENAME\" THEN CAST(?
> AS INTEGER)\n" +
>" ELSE CAST(? AS INTEGER)\n" +
>" END AS \"case-column\"\n" +
>" FROM \"EMP\")";
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query(statement)
> .consumesPreparedStatement(p -> {
> p.setString(1, "name");
> p.setInt(2, 2);
> p.setInt(3, 1);
> })
> .planHasSql("");
> {code}
> Lets assume the following statement is passed through calcite:
> Before:
> {code:java}
> SELECT
> DISTINCT "case-column"
> FROM
>SELECT
>CASE
> WHEN CAST(? AS VARCHAR) = "store_name" THEN CAST(? AS INTEGER)
> ELSE CAST(? AS INTEGER)
> END AS "case-column"
>FROM "foodmart"."store"
> {code}
> After:
> {code:java}
> SELECT
>CASE
> WHEN ? = "ENAME" THEN ?
> ELSE ?
>END AS "case-column"
> FROM
>"SCOTT"."EMP"
> GROUP BY
>CASE
> WHEN ? = "ENAME" THEN ?
> ELSE ?
>END
> {code}
> The produced statement hast two issues:
> 1. The missing casts (see also
> https://issues.apache.org/jira/browse/CALCITE-6346)
> 2. Instead of pushing the DISTINCT it is replaced with a GROUP BY. This is
> usually fine *but* since the field is a case statement containing dynamic
> parameters it is not.
> During sql syntax evaluation the database will give an error (the field in
> the select is not contained in group by). This is because the dynamic
> parameters are not evaluated during sql syntax evaluation.
> I think this could be fixed by adding an alias to the field in the select
> clause and referencing it in the group by clause instead of duplicating the
> case statement and the dynamic parameters.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6383) The class SameOperandTypeChecker is incorrectly documented
[
https://issues.apache.org/jira/browse/CALCITE-6383?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17841062#comment-17841062
]
Julian Hyde commented on CALCITE-6383:
--
Pr looks good except than you can’t use “*” for emphasis in javadoc. It is html
not markdown.
> The class SameOperandTypeChecker is incorrectly documented
> --
>
> Key: CALCITE-6383
> URL: https://issues.apache.org/jira/browse/CALCITE-6383
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Mihai Budiu
>Priority: Minor
> Labels: pull-request-available
>
> The SameOperandTypeChecker claims that it checks whether operands have the
> same type (the class name suggests this, as does the JavaDoc).
> {code:java}
> /**
> * Parameter type-checking strategy where all operand types must be the same.
> */
> public class SameOperandTypeChecker implements SqlSingleOperandTypeChecker {
> {code}
> But the code does something this:
> {code:java}
> for (int i : operandList) {
> if (prev >= 0) {
> if (!SqlTypeUtil.isComparable(types[i], types[prev])) {
> {code}
> The documentation for isComparable says:
> {code:java}
> /**
>* Returns whether two types are comparable. They need to be scalar types of
>* the same family, or struct types whose fields are pairwise comparable.
> {code}
> Thus the class only checks that the operands have the same type *family*, not
> the same *type*.
> I am not sure what the right fix is, though, since changing the class name
> would be a pretty big breaking change. But I suspect this confusion is a
> source of a few bugs. An instance is [CALCITE-6382]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6374) LatticeSuggester throw npe when agg call covered with cast
[ https://issues.apache.org/jira/browse/CALCITE-6374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17838774#comment-17838774 ] Julian Hyde commented on CALCITE-6374: -- [~allendang], Can you please remove the PNG and replace it with text. Use full words, e.g. NullPointerException. This makes the case searchable. > LatticeSuggester throw npe when agg call covered with cast > --- > > Key: CALCITE-6374 > URL: https://issues.apache.org/jira/browse/CALCITE-6374 > Project: Calcite > Issue Type: Bug > Components: core >Reporter: allendang >Priority: Critical > Attachments: image-2024-04-18-21-44-04-262.png > > > org.apache.calcite.materialize.LatticeSuggesterTest#testExpressionEvolution > > final String q1 = "select\n" > + " \"num_children_at_home\" + 10 as \"n10\",\n" > + " \"num_children_at_home\" + 14 as \"n14\",\n" > + " cast(sum(\"num_children_at_home\" + 12) as double) as \"n12\",\n" > + " sum(\"num_children_at_home\" + 13) as \"n13\"\n" > + "from \"customer\"\n" > + "group by \"num_children_at_home\" + 10," > + " \"num_children_at_home\" + 14"; > > measure.name > java.lang.NullPointerException: measure.name > at java.util.Objects.requireNonNull(Objects.java:228) > at > org.apache.calcite.materialize.LatticeSuggester.deriveAlias(LatticeSuggester.java:290) > at > org.apache.calcite.materialize.LatticeSuggester.lambda$addFrame$3(LatticeSuggester.java:250) > at org.apache.calcite.util.Util$TransformingList.get(Util.java:2810) > at java.util.AbstractList$Itr.next(AbstractList.java:358) > at java.util.AbstractCollection.toArray(AbstractCollection.java:141) > at com.google.common.collect.ImmutableList.copyOf(ImmutableList.java:265) > at com.google.common.collect.ImmutableList.copyOf(ImmutableList.java:238) > at org.apache.calcite.materialize.Lattice$Measure.(Lattice.java:567) > at > org.apache.calcite.materialize.LatticeSuggester.addFrame(LatticeSuggester.java:240) > at > org.apache.calcite.materialize.LatticeSuggester.lambda$addQuery$1(LatticeSuggester.java:143) > at java.util.ArrayList.forEach(ArrayList.java:1259) > at > org.apache.calcite.materialize.LatticeSuggester.addQuery(LatticeSuggester.java:143) > at > org.apache.calcite.materialize.LatticeSuggesterTest$Tester.addQuery(LatticeSuggesterTest.java:851) > at > org.apache.calcite.materialize.LatticeSuggesterTest.testExpressionEvolution(LatticeSuggesterTest.java:551) > !image-2024-04-18-21-44-04-262.png! -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6375) Fix failing quidem tests for FORMAT in CAST
[ https://issues.apache.org/jira/browse/CALCITE-6375?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17838773#comment-17838773 ] Julian Hyde commented on CALCITE-6375: -- [~jerin_john], Jira summaries should not use the word 'fix'. Your summary 'Fix failing quidem tests for FORMAT in CAST' describes a task; can you instead describe the bug? Or maybe it's a feature, e.g. 'Enable tests that ...'. > Fix failing quidem tests for FORMAT in CAST > --- > > Key: CALCITE-6375 > URL: https://issues.apache.org/jira/browse/CALCITE-6375 > Project: Calcite > Issue Type: Bug >Reporter: Jerin John >Priority: Minor > > CALCITE-2980 implemented the FORMAT clause used inside the CAST operator. We > had imported a large number of quidem tests from Apache Impala for the same, > stored in the file: > [cast-with-format.iq|https://github.com/apache/calcite/blob/main/core/src/test/resources/sql/cast-with-format.iq]. > > Many of these tests are currently disabled as they're either unsupported or > incorrectly implemented, some tests outputs may also need to be changed as > per dialect behavior. Creating this placeholder issue to track and fix the > pending tests. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6373) Distinct optimization produces broken sql query
[
https://issues.apache.org/jira/browse/CALCITE-6373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17838248#comment-17838248
]
Julian Hyde commented on CALCITE-6373:
--
I agree that any SQL-to-SQL transformation that alters the number or order of
parameters (question marks in the text) is invalid.
However, I don’t think the “distinct optimization” is to blame. The rel-to-SQL
process should ensure that parameters are output in sequential order. I don’t
know how to do that in general; is it possible to output PL/SQL or pgsql that
assigns the parameters to variables, then uses those variables multiple times?
> Distinct optimization produces broken sql query
> ---
>
> Key: CALCITE-6373
> URL: https://issues.apache.org/jira/browse/CALCITE-6373
> Project: Calcite
> Issue Type: Bug
> Components: core, jdbc-adapter
>Affects Versions: 1.36.0
>Reporter: Corvin Kuebler
>Priority: Major
>
> Can be reproduced with the following test in JDBCTests:
> {code:java}
> String statement = "SELECT\n" +
>" DISTINCT \"case-column\"\n" +
>"FROM (\n" +
>" SELECT \n" +
>" CASE\n" +
>" WHEN CAST(? AS VARCHAR) = \"ENAME\" THEN CAST(?
> AS INTEGER)\n" +
>" ELSE CAST(? AS INTEGER)\n" +
>" END AS \"case-column\"\n" +
>" FROM \"EMP\")";
> CalciteAssert.model(JdbcTest.SCOTT_MODEL)
> .query(statement)
> .consumesPreparedStatement(p -> {
> p.setString(1, "name");
> p.setInt(2, 2);
> p.setInt(3, 1);
> })
> .planHasSql("");
> {code}
> Lets assume the following statement is passed through calcite:
> Before:
> {code:java}
> SELECT
> DISTINCT "case-column"
> FROM
>SELECT
>CASE
> WHEN CAST(? AS VARCHAR) = "store_name" THEN CAST(? AS INTEGER)
> ELSE CAST(? AS INTEGER)
> END AS "case-column"
>FROM "foodmart"."store"
> {code}
> After:
> {code:java}
> SELECT
>CASE
> WHEN ? = "ENAME" THEN ?
> ELSE ?
>END AS "case-column"
> FROM
>"SCOTT"."EMP"
> GROUP BY
>CASE
> WHEN ? = "ENAME" THEN ?
> ELSE ?
>END
> {code}
> The produced statement hast two issues:
> 1. The missing casts (see also
> https://issues.apache.org/jira/browse/CALCITE-6346)
> 2. Instead of pushing the DISTINCT it is replaced with a GROUP BY. This is
> usually fine *but* since the field is a case statement containing dynamic
> parameters it is not.
> During sql syntax evaluation the database will give an error (the field in
> the select is not contained in group by). This is because the dynamic
> parameters are not evaluated during sql syntax evaluation.
> I think this could be fixed by adding an alias to the field in the select
> clause and referencing it in the group by clause instead of duplicating the
> case statement and the dynamic parameters.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6367) Add timezone support for FORMAT clause in CAST (enabled in BigQuery)
[
https://issues.apache.org/jira/browse/CALCITE-6367?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17837465#comment-17837465
]
Julian Hyde commented on CALCITE-6367:
--
[~jerin_john] Can you add links to any related cases (not just mention them in
comments). Links are bi-directional and help us prevent duplicate work.
> Add timezone support for FORMAT clause in CAST (enabled in BigQuery)
>
>
> Key: CALCITE-6367
> URL: https://issues.apache.org/jira/browse/CALCITE-6367
> Project: Calcite
> Issue Type: Bug
>Reporter: Jerin John
>Priority: Minor
>
> This issue is a followup on CALCITE-6269 that fixes some of Calcite's
> existing format elements implementation to be aligned to BQ functionality.
> Two major formats that might require a bit more rework is adding support for
> the TZH/TZM elements along with time zone areas as described below:
> * [Parsing timestamp
> literals|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_tz_as_string]
> with timezones as used by BQ does not seem to be supported yet (format
> element TZR is unimplemented, BQ has TZH, TZM for hour and minute offsets)
> (eg: {{cast('2020.06.03 00:00:53+00' as timestamp format '.MM.DD
> HH:MI:SSTZH')}}
> * BQ format [timezone as string
> |https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_tz_as_string]
> can take an additional argument {{{}AT TIME ZONE 'Asia/Kolkata'{}}}, which
> would require additional parser changes and time zone parameter to be plumbed
> in to the cast operator call.
> One important thing to consider, is that the {{SimpleDateFormat}} class which
> currently stores the datetime object in {{{}CAST{}}}, may not fully support
> timezone features as described and might warrant a broader refactoring of
> this code to use timezone compatible data types.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6363) Introduce a rule to derive more filters from inner join condition
[
https://issues.apache.org/jira/browse/CALCITE-6363?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17837462#comment-17837462
]
Julian Hyde commented on CALCITE-6363:
--
Thanks for finding JoinConditionPushRule (CALCITE-5073). This case seems to be
a generalization of that. Maybe the logic should be added to
JoinConditionPushRule. What's your opinion, [~libenchao]?
I would like to see some test cases for left and right joins. It is possible to
move conditions across outer joins, in some cases.
I don't believe that all the changes to RexNormalize and RexUtil are necessary.
> Introduce a rule to derive more filters from inner join condition
> -
>
> Key: CALCITE-6363
> URL: https://issues.apache.org/jira/browse/CALCITE-6363
> Project: Calcite
> Issue Type: New Feature
> Components: core
>Reporter: ruanhui
>Priority: Minor
> Labels: pull-request-available
>
> Sometimes we can infer more predicates from inner Join , for example, in the
> query
> SELECT * FROM ta INNER JOIN tb ON ta.x = tb.y WHERE ta.x > 10
> we can infer condition tb.y > 10 and we can push it down to the table tb.
> In this way, it is possible to reduce the amount of data involved in the Join.
> To achieve this, here is my idea.
> The core data strucature is two Multimap:
> predicateMap : a map for inputRef to corresponding predicate such as: $1 ->
> [$1 > 10, $1 < 20, $1 = $2]
> equivalenceMap : a map for inputRef to corresponding equivalent values or
> inputRefs such as: $1 -> [$2, 1]
> The filter derivation is divided into 4 steps:
> 1. construct predicate map and equivalence map by traversing all conjunctions
> in the condition
> 2. search map and rewrite predicates with equivalent inputRefs or literals
> 2.1 find all inputRefs that are equivalent to the current inputRef, and then
> rewrite all predicates involving equivalent inputRefs using inputRef, for
> example if we have inputRef $1 = equivInputRef $2, then we can rewrite \{$2 =
> 10} to \{$1 = 10}.
> 2.2 find all predicates involving current inputRef. If any predicate refers
> to another inputRef, rewrite the predicate with the literal/constant
> equivalent to that inputRef, such as: if we have inputRef \{$1 > $2} and \{$2
> = 10} then we can infer new condition \{$1 > 10}.
> 2.3 derive new predicates based on equivalence relation in equivalenceMultimap
> 3. compose all original predicates and derived predicates
> 4. simplify expression such as range merging, like \{$1 > 10 AND $1 > 20} =>
> \{$1 > 20}, \{$1 > $2 AND $1 > $2} => \{$1 > $2}
> Anyone interested in this, please feel free to comment on this issue.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6363) Introduce a rule to derive more filters from inner join condition
[
https://issues.apache.org/jira/browse/CALCITE-6363?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17837461#comment-17837461
]
Julian Hyde commented on CALCITE-6363:
--
On the mailing list [~jamesstarr] replied:
{quote}The keyword I think you want is transitive filter pushdown. The reduce
expression rule handles some of the trivial cases outlined as examples.
Also, you will need to simplify the pushed down filters after they are
extracted to prevent infinite loops.
Ideally, for the equivalenceMap, an arbitrary subtree that only
references a single side of the join could be used.
Example 1:
SELECT *
FROM t1, t2
WHERE subString(t1.zip, 0, 6) = subString(t2.zip, 0, 6)
AND subString(t1.zip, 0, 6) IN ()
{quote}
> Introduce a rule to derive more filters from inner join condition
> -
>
> Key: CALCITE-6363
> URL: https://issues.apache.org/jira/browse/CALCITE-6363
> Project: Calcite
> Issue Type: New Feature
> Components: core
>Reporter: ruanhui
>Priority: Minor
> Labels: pull-request-available
>
> Sometimes we can infer more predicates from inner Join , for example, in the
> query
> SELECT * FROM ta INNER JOIN tb ON ta.x = tb.y WHERE ta.x > 10
> we can infer condition tb.y > 10 and we can push it down to the table tb.
> In this way, it is possible to reduce the amount of data involved in the Join.
> To achieve this, here is my idea.
> The core data strucature is two Multimap:
> predicateMap : a map for inputRef to corresponding predicate such as: $1 ->
> [$1 > 10, $1 < 20, $1 = $2]
> equivalenceMap : a map for inputRef to corresponding equivalent values or
> inputRefs such as: $1 -> [$2, 1]
> The filter derivation is divided into 4 steps:
> 1. construct predicate map and equivalence map by traversing all conjunctions
> in the condition
> 2. search map and rewrite predicates with equivalent inputRefs or literals
> 2.1 find all inputRefs that are equivalent to the current inputRef, and then
> rewrite all predicates involving equivalent inputRefs using inputRef, for
> example if we have inputRef $1 = equivInputRef $2, then we can rewrite \{$2 =
> 10} to \{$1 = 10}.
> 2.2 find all predicates involving current inputRef. If any predicate refers
> to another inputRef, rewrite the predicate with the literal/constant
> equivalent to that inputRef, such as: if we have inputRef \{$1 > $2} and \{$2
> = 10} then we can infer new condition \{$1 > 10}.
> 2.3 derive new predicates based on equivalence relation in equivalenceMultimap
> 3. compose all original predicates and derived predicates
> 4. simplify expression such as range merging, like \{$1 > 10 AND $1 > 20} =>
> \{$1 > 20}, \{$1 > $2 AND $1 > $2} => \{$1 > $2}
> Anyone interested in this, please feel free to comment on this issue.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6363) Introduce a rule to derive more filters from inner join condition
[
https://issues.apache.org/jira/browse/CALCITE-6363?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836988#comment-17836988
]
Julian Hyde commented on CALCITE-6363:
--
I really wish you had logged the case BEFORE writing a big complex PR.
> Introduce a rule to derive more filters from inner join condition
> -
>
> Key: CALCITE-6363
> URL: https://issues.apache.org/jira/browse/CALCITE-6363
> Project: Calcite
> Issue Type: New Feature
> Components: core
>Reporter: ruanhui
>Priority: Minor
> Labels: pull-request-available
>
> Sometimes we can infer more predicates from inner Join , for example, in the
> query
> SELECT * FROM ta INNER JOIN tb ON ta.x = tb.y WHERE ta.x > 10
> we can infer condition tb.y > 10 and we can push it down to the table tb.
> In this way, it is possible to reduce the amount of data involved in the Join.
> To achieve this, here is my idea.
> The core data strucature is two Multimap:
> predicateMap : a map for inputRef to corresponding predicate such as: $1 ->
> [$1 > 10, $1 < 20, $1 = $2]
> equivalenceMap : a map for inputRef to corresponding equivalent values or
> inputRefs such as: $1 -> [$2, 1]
> The filter derivation is divided into 4 steps:
> 1. construct predicate map and equivalence map by traversing all conjunctions
> in the condition
> 2. search map and rewrite predicates with equivalent inputRefs or literals
> 2.1 find all inputRefs that are equivalent to the current inputRef, and then
> rewrite all predicates involving equivalent inputRefs using inputRef, for
> example if we have inputRef $1 = equivInputRef $2, then we can rewrite \{$2 =
> 10} to \{$1 = 10}.
> 2.2 find all predicates involving current inputRef. If any predicate refers
> to another inputRef, rewrite the predicate with the literal/constant
> equivalent to that inputRef, such as: if we have inputRef \{$1 > $2} and \{$2
> = 10} then we can infer new condition \{$1 > 10}.
> 2.3 derive new predicates based on equivalence relation in equivalenceMultimap
> 3. compose all original predicates and derived predicates
> 4. simplify expression such as range merging, like \{$1 > 10 AND $1 > 20} =>
> \{$1 > 20}, \{$1 > $2 AND $1 > $2} => \{$1 > $2}
> Anyone interested in this, please feel free to comment on this issue.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6363) Introduce a rule to derive more filters from inner join condition
[
https://issues.apache.org/jira/browse/CALCITE-6363?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836987#comment-17836987
]
Julian Hyde commented on CALCITE-6363:
--
Don’t we have some existing rules (metadata) for this? Can you link any related
issues (open or fixed) that you know about.
> Introduce a rule to derive more filters from inner join condition
> -
>
> Key: CALCITE-6363
> URL: https://issues.apache.org/jira/browse/CALCITE-6363
> Project: Calcite
> Issue Type: New Feature
> Components: core
>Reporter: ruanhui
>Priority: Minor
> Labels: pull-request-available
>
> Sometimes we can infer more predicates from inner Join , for example, in the
> query
> SELECT * FROM ta INNER JOIN tb ON ta.x = tb.y WHERE ta.x > 10
> we can infer condition tb.y > 10 and we can push it down to the table tb.
> In this way, it is possible to reduce the amount of data involved in the Join.
> To achieve this, here is my idea.
> The core data strucature is two Multimap:
> predicateMap : a map for inputRef to corresponding predicate such as: $1 ->
> [$1 > 10, $1 < 20, $1 = $2]
> equivalenceMap : a map for inputRef to corresponding equivalent values or
> inputRefs such as: $1 -> [$2, 1]
> The filter derivation is divided into 4 steps:
> 1. construct predicate map and equivalence map by traversing all conjunctions
> in the condition
> 2. search map and rewrite predicates with equivalent inputRefs or literals
> 2.1 find all inputRefs that are equivalent to the current inputRef, and then
> rewrite all predicates involving equivalent inputRefs using inputRef, for
> example if we have inputRef $1 = equivInputRef $2, then we can rewrite \{$2 =
> 10} to \{$1 = 10}.
> 2.2 find all predicates involving current inputRef. If any predicate refers
> to another inputRef, rewrite the predicate with the literal/constant
> equivalent to that inputRef, such as: if we have inputRef \{$1 > $2} and \{$2
> = 10} then we can infer new condition \{$1 > 10}.
> 2.3 derive new predicates based on equivalence relation in equivalenceMultimap
> 3. compose all original predicates and derived predicates
> 4. simplify expression such as range merging, like \{$1 > 10 AND $1 > 20} =>
> \{$1 > 20}, \{$1 > $2 AND $1 > $2} => \{$1 > $2}
> Anyone interested in this, please feel free to comment on this issue.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6357) Calcite enforces select arguments count to be same as row schema fields which causes aliases to be ignored
[
https://issues.apache.org/jira/browse/CALCITE-6357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836338#comment-17836338
]
Julian Hyde commented on CALCITE-6357:
--
We added {{RelRoot}} way back in CALCITE-819 to express the fact that you want
columns to have particular names, and that you want the relation to be sorted
by columns that are not returned in the result.
If you need that, use {{RelRoot}}. But it is not a {{RelNode}} because adding
those capabilities to {{RelNode}} would hamper the operations of the planner.
> Calcite enforces select arguments count to be same as row schema fields which
> causes aliases to be ignored
> --
>
> Key: CALCITE-6357
> URL: https://issues.apache.org/jira/browse/CALCITE-6357
> Project: Calcite
> Issue Type: Bug
>Reporter: Brachi Packter
>Priority: Major
>
> Calcite RelBuilder.ProjectNamed checks if row size in the select is identical
> to schema fields, if no, it creates a project with fields as they appear in
> the select , meaning if they have aliases, they are returning with their
> aliases.
> Here, it checks if they are identical:
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2063
> using RexUtil.isIdentity method:
> ```
> public static boolean isIdentity(List exps,
> RelDataType inputRowType) {
> return inputRowType.getFieldCount() == exps.size()
> && containIdentity(exps, inputRowType, Litmus.IGNORE);
> }
> ```
> This is the problematic part `inputRowType.getFieldCount() == exps.size()`
> If they are identical, and return with their aliases, it is ignored in the
> "rename" method later on
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2125
> and alias is skipped
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2137
> This doesn't impact calcite queries, but in Apache Beam they are doing some
> optimization on top of it,
> https://github.com/apache/beam/blob/master/sdks/java/extensions/sql/src/main/java/org/apache/beam/sdk/extensions/sql/impl/rule/BeamAggregateProjectMergeRule.java
> which causes aliases to be ignored, and data is returning suddenly without
> correct column field.
> I believe the isIdentity check can causes more issues if not fixed, we need
> to understand why is it enforced? isn't it valid to have different size of
> fields in select from what we have in the schema?
> In our case we have a one big row and we run on it different queries, each
> with different fields in the select.
> Beam issue
> https://github.com/apache/beam/issues/30498
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6358) Support all PostgreSQL 14 date/time patterns
[ https://issues.apache.org/jira/browse/CALCITE-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836284#comment-17836284 ] Julian Hyde commented on CALCITE-6358: -- Yes, in theory, it might. I don't have time to look at the code, but you can probably construct an example that behaves differently in "Postgres functions" than "BigQuery functions". And you probably should, so that we all remember that there is a level of indirection. > Support all PostgreSQL 14 date/time patterns > > > Key: CALCITE-6358 > URL: https://issues.apache.org/jira/browse/CALCITE-6358 > Project: Calcite > Issue Type: Sub-task >Reporter: Norman Jordan >Priority: Minor > > Many of the date/time format patterns supported by PostgreSQL 14 are not > supported in Calcite. > * HH > * US > * > * S > * AM > * A.M. > * am > * a.m. > * PM > * P.M. > * pm > * p.m. > * Y,YYY > * YYY > * Y > * IYYY > * IYY > * IY > * I > * BC > * B.C. > * bc > * b.c. > * AD > * A.D. > * ad > * a.d. > * MONTH > * month > * MON > * mon > * DAY > * day > * Dy > * dy > * IDDD > * ID > * TZH > * TZM > * OF > There are also template pattern modifiers that need to be supported. > * FM (prefix) > * TH (suffix) > * th (suffix) > * FX (prefix) > * TM (prefix) > Some format patterns in Calcite behave differently from PostgreSQL 14. > * FF1 > * FF2 > * FF4 > * FF5 > * FF6 > Also verify that the other existing format strings produce results that match > PostgreSQL 14. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6358) Support all PostgreSQL 14 date/time patterns
[ https://issues.apache.org/jira/browse/CALCITE-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836261#comment-17836261 ] Julian Hyde commented on CALCITE-6358: -- In Calcite, the set of format elements is tied to particular functions, not set system-wide. You could, say, have a TO_CHAR function that uses Postgres and a TO_TIMESTAMP function that uses BigQuery elements. So, my question is: which functions do you intend to test as part of this case? > Support all PostgreSQL 14 date/time patterns > > > Key: CALCITE-6358 > URL: https://issues.apache.org/jira/browse/CALCITE-6358 > Project: Calcite > Issue Type: Sub-task >Reporter: Norman Jordan >Priority: Minor > > Many of the date/time format patterns supported by PostgreSQL 14 are not > supported in Calcite. > * HH > * US > * > * S > * AM > * A.M. > * am > * a.m. > * PM > * P.M. > * pm > * p.m. > * Y,YYY > * YYY > * Y > * IYYY > * IYY > * IY > * I > * BC > * B.C. > * bc > * b.c. > * AD > * A.D. > * ad > * a.d. > * MONTH > * month > * MON > * mon > * DAY > * day > * Dy > * dy > * IDDD > * ID > * TZH > * TZM > * OF > There are also template pattern modifiers that need to be supported. > * FM (prefix) > * TH (suffix) > * th (suffix) > * FX (prefix) > * TM (prefix) > Some format patterns in Calcite behave differently from PostgreSQL 14. > * FF1 > * FF2 > * FF4 > * FF5 > * FF6 > Also verify that the other existing format strings produce results that match > PostgreSQL 14. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6361) Uncollect.deriveUncollectRowType crashes if the input data is not a collection
[ https://issues.apache.org/jira/browse/CALCITE-6361?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836014#comment-17836014 ] Julian Hyde commented on CALCITE-6361: -- By the way, please don't say 'crashes'. Say what error is thrown. > Uncollect.deriveUncollectRowType crashes if the input data is not a collection > -- > > Key: CALCITE-6361 > URL: https://issues.apache.org/jira/browse/CALCITE-6361 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.37.0 >Reporter: Mihai Budiu >Priority: Minor > > This happens because the type checker calls getComponentType() without > checking first that the field type has components. It should report an error > in such a case. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6361) Uncollect.deriveUncollectRowType crashes if the input data is not a collection
[ https://issues.apache.org/jira/browse/CALCITE-6361?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17836013#comment-17836013 ] Julian Hyde commented on CALCITE-6361: -- Is there a SQL query that reproduces this problem? It's sometimes reasonable that methods such as deriveUncollectRowType() make certain assumptions -- the alternative is an overly defensive programming style. An alternative formulation of the same question: Do you consider this to be a user error or an internal error? > Uncollect.deriveUncollectRowType crashes if the input data is not a collection > -- > > Key: CALCITE-6361 > URL: https://issues.apache.org/jira/browse/CALCITE-6361 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.37.0 >Reporter: Mihai Budiu >Priority: Minor > > This happens because the type checker calls getComponentType() without > checking first that the field type has components. It should report an error > in such a case. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6358) Support all PostgreSQL 14 date/time patterns
[ https://issues.apache.org/jira/browse/CALCITE-6358?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17835918#comment-17835918 ] Julian Hyde commented on CALCITE-6358: -- [~njordan], To be clear, which functions are you proposing to fix? TO_CHAR? TO_TIMESTAMP? CAST(... FORMAT ...)? > Support all PostgreSQL 14 date/time patterns > > > Key: CALCITE-6358 > URL: https://issues.apache.org/jira/browse/CALCITE-6358 > Project: Calcite > Issue Type: Sub-task >Reporter: Norman Jordan >Priority: Minor > > Many of the date/time format patterns supported by PostgreSQL 14 are not > supported in Calcite. > * HH > * US > * > * S > * AM > * A.M. > * am > * a.m. > * PM > * P.M. > * pm > * p.m. > * Y,YYY > * YYY > * Y > * IYYY > * IYY > * IY > * I > * BC > * B.C. > * bc > * b.c. > * AD > * A.D. > * ad > * a.d. > * MONTH > * month > * MON > * mon > * DAY > * day > * Dy > * dy > * IDDD > * ID > * TZH > * TZM > * OF > There are also template pattern modifiers that need to be supported. > * FM (prefix) > * TH (suffix) > * th (suffix) > * FX (prefix) > * TM (prefix) > Some format patterns in Calcite behave differently from PostgreSQL 14. > * FF1 > * FF2 > * FF4 > * FF5 > * FF6 > Also verify that the other existing format strings produce results that match > PostgreSQL 14. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6357) Calcite enforces select arguments count to be same as row schema fields which causes aliases to be ignored
[
https://issues.apache.org/jira/browse/CALCITE-6357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17835917#comment-17835917
]
Julian Hyde commented on CALCITE-6357:
--
[~brachi_packter], It might be useful to reduce this to a test case in
{{RelBuilderTest}}. Can you do that?
> Calcite enforces select arguments count to be same as row schema fields which
> causes aliases to be ignored
> --
>
> Key: CALCITE-6357
> URL: https://issues.apache.org/jira/browse/CALCITE-6357
> Project: Calcite
> Issue Type: Bug
>Reporter: Brachi Packter
>Priority: Major
>
> Calcite RelBuilder.ProjectNamed checks if row size in the select is identical
> to schema fields, if no, it creates a project with fields as they appear in
> the select , meaning if they have aliases, they are returning with their
> aliases.
> Here, it checks if they are identical:
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2063
> using RexUtil.isIdentity method:
> ```
> public static boolean isIdentity(List exps,
> RelDataType inputRowType) {
> return inputRowType.getFieldCount() == exps.size()
> && containIdentity(exps, inputRowType, Litmus.IGNORE);
> }
> ```
> This is the problematic part `inputRowType.getFieldCount() == exps.size()`
> If they are identical, and return with their aliases, it is ignored in the
> "rename" method later on
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2125
> and alias is skipped
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2137
> This doesn't impact calcite queries, but in Apache Beam they are doing some
> optimization on top of it,
> https://github.com/apache/beam/blob/master/sdks/java/extensions/sql/src/main/java/org/apache/beam/sdk/extensions/sql/impl/rule/BeamAggregateProjectMergeRule.java
> which causes aliases to be ignored, and data is returning suddenly without
> correct column field.
> I believe the isIdentity check can causes more issues if not fixed, we need
> to understand why is it enforced? isn't it valid to have different size of
> fields in select from what we have in the schema?
> In our case we have a one big row and we run on it different queries, each
> with different fields in the select.
> Beam issue
> https://github.com/apache/beam/issues/30498
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6357) Calcite enforces select arguments count to be same as row schema fields which causes aliases to be ignored
[
https://issues.apache.org/jira/browse/CALCITE-6357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17835893#comment-17835893
]
Julian Hyde commented on CALCITE-6357:
--
*Caveat*: I haven't read every word you've written above; I only scanned the
Beam case. I'm correcting what seem to be mistaken assumptions, in the hope
that it will allow you to diagnose your problem faster. I hope that I am not
dissuading other Calcite community members who may have more time from jumping
in to help.
{quote}I presume you would agree that names of output columns is as much part
of data integrity as the values{quote}
No, I would not. Calcite does not commit to preserving column names, only their
types and ordering. It recognizes duplicate relational expressions (via
memoization), forms equivalence sets of relational expressions, and after
optimization will return one of the relational expressions in that subset.
{quote}isn't it a valid case to have a row with wider schema from what you
actually need to select?{quote}
Sure, you can write "select x, y from aTableWithAHundredColumns". That's a
Project (with two expressions) on a Scan (returning 100 columns). My point is
that the Project knows that its input has 100 columns.
> Calcite enforces select arguments count to be same as row schema fields which
> causes aliases to be ignored
> --
>
> Key: CALCITE-6357
> URL: https://issues.apache.org/jira/browse/CALCITE-6357
> Project: Calcite
> Issue Type: Bug
>Reporter: Brachi Packter
>Priority: Major
>
> Calcite RelBuilder.ProjectNamed checks if row size in the select is identical
> to schema fields, if no, it creates a project with fields as they appear in
> the select , meaning if they have aliases, they are returning with their
> aliases.
> Here, it checks if they are identical:
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2063
> using RexUtil.isIdentity method:
> ```
> public static boolean isIdentity(List exps,
> RelDataType inputRowType) {
> return inputRowType.getFieldCount() == exps.size()
> && containIdentity(exps, inputRowType, Litmus.IGNORE);
> }
> ```
> This is the problematic part `inputRowType.getFieldCount() == exps.size()`
> If they are identical, and return with their aliases, it is ignored in the
> "rename" method later on
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2125
> and alias is skipped
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2137
> This doesn't impact calcite queries, but in Apache Beam they are doing some
> optimization on top of it,
> https://github.com/apache/beam/blob/master/sdks/java/extensions/sql/src/main/java/org/apache/beam/sdk/extensions/sql/impl/rule/BeamAggregateProjectMergeRule.java
> which causes aliases to be ignored, and data is returning suddenly without
> correct column field.
> I believe the isIdentity check can causes more issues if not fixed, we need
> to understand why is it enforced? isn't it valid to have different size of
> fields in select from what we have in the schema?
> In our case we have a one big row and we run on it different queries, each
> with different fields in the select.
> Beam issue
> https://github.com/apache/beam/issues/30498
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6357) Calcite enforces select arguments count to be same as row schema fields which causes aliases to be ignored
[
https://issues.apache.org/jira/browse/CALCITE-6357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17835833#comment-17835833
]
Julian Hyde commented on CALCITE-6357:
--
If the number of fields does not match, that's probably a problem on your end.
{{RelBuilder}} almost always requires number of fields to match.
Regarding column aliases. Calcite generally doesn't promise to preserve
aliases, but in some cases you can force a rename. At
[RelBuilder#2125|https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2125]
it seems that {{force}} is false. For the behavior you want, {{force}} would
need to be true.
> Calcite enforces select arguments count to be same as row schema fields which
> causes aliases to be ignored
> --
>
> Key: CALCITE-6357
> URL: https://issues.apache.org/jira/browse/CALCITE-6357
> Project: Calcite
> Issue Type: Bug
>Reporter: Brachi Packter
>Priority: Major
>
> Calcite RelBuilder.ProjectNamed checks if row size in the select is identical
> to schema fields, if no, it creates a project with fields as they appear in
> the select , meaning if they have aliases, they are returning with their
> aliases.
> Here, it checks if they are identical:
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2063
> using RexUtil.isIdentity method:
> ```
> public static boolean isIdentity(List exps,
> RelDataType inputRowType) {
> return inputRowType.getFieldCount() == exps.size()
> && containIdentity(exps, inputRowType, Litmus.IGNORE);
> }
> ```
> This is the problematic part `inputRowType.getFieldCount() == exps.size()`
> If they are identical, and return with their aliases, it is ignored in the
> "rename" method later on
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2125
> and alias is skipped
> https://github.com/apache/calcite/blob/f14cf4c32b9079984a988bbad40230aa6a59b127/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2137
> This doesn't impact calcite queries, but in Apache Beam they are doing some
> optimization on top of it,
> https://github.com/apache/beam/blob/master/sdks/java/extensions/sql/src/main/java/org/apache/beam/sdk/extensions/sql/impl/rule/BeamAggregateProjectMergeRule.java
> which causes aliases to be ignored, and data is returning suddenly without
> correct column field.
> I believe the isIdentity check can causes more issues if not fixed, we need
> to understand why is it enforced? isn't it valid to have different size of
> fields in select from what we have in the schema?
> In our case we have a one big row and we run on it different queries, each
> with different fields in the select.
> Beam issue
> https://github.com/apache/beam/issues/30498
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6345) Intervals with more than 100 years are not supported
[
https://issues.apache.org/jira/browse/CALCITE-6345?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17833710#comment-17833710
]
Julian Hyde commented on CALCITE-6345:
--
Can you reframe this as a feature request, rather than a bug?
> Intervals with more than 100 years are not supported
>
>
> Key: CALCITE-6345
> URL: https://issues.apache.org/jira/browse/CALCITE-6345
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Mihai Budiu
>Priority: Minor
> Labels: pull-request-available
>
> Adding the following SqlValidatorTest:
> {code:java}
> expr("INTERVAL '100-2' YEAR TO MONTH").assertInterval(is(122L));
> {code}
> causes the following exception:
> {code}
> org.apache.calcite.runtime.CalciteContextException: From line 1, column 9 to
> line 1, column 38: Interval field value 100 exceeds precision of YEAR(2) field
> at
> java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native
> Method)
> at
> java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
> at
> java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
> at
> java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
> at
> org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:507)
> 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.SqlIntervalQualifier.fieldExceedsPrecisionException(SqlIntervalQualifier.java:1355)
> at
> org.apache.calcite.sql.SqlIntervalQualifier.checkLeadFieldInRange(SqlIntervalQualifier.java:475)
> at
> org.apache.calcite.sql.SqlIntervalQualifier.evaluateIntervalLiteralAsYearToMonth(SqlIntervalQualifier.java:626)
> at
> org.apache.calcite.sql.SqlIntervalQualifier.evaluateIntervalLiteral(SqlIntervalQualifier.java:1293)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateLiteral(SqlValidatorImpl.java:3429)
> {code}
> The spec does not limit years to 2 digits, so I don't know where the YEAR(2)
> time is coming from.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6321) Add copy(List constants) method to Window class.
[ https://issues.apache.org/jira/browse/CALCITE-6321?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17833029#comment-17833029 ] Julian Hyde commented on CALCITE-6321: -- [~mbudiu], Which commit fixed this? > Add copy(List constants) method to Window class. > - > > Key: CALCITE-6321 > URL: https://issues.apache.org/jira/browse/CALCITE-6321 > Project: Calcite > Issue Type: Improvement >Reporter: Thomas D'Silva >Priority: Major > Labels: pull-request-available > Fix For: 1.37.0 > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6343) AS alias operator strips MEASUREness from measures
[
https://issues.apache.org/jira/browse/CALCITE-6343?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17831113#comment-17831113
]
Julian Hyde commented on CALCITE-6343:
--
Your PR looks fine, but I would like you to add a simple SQL test -- maybe in
{{measure.iq}} or SqlValidatorTest -- that demonstrates the derived type. If
you ask me to read the test you wrote tomorrow, I wouldn't be able to tell you
what it's testing.
> AS alias operator strips MEASUREness from measures
> --
>
> Key: CALCITE-6343
> URL: https://issues.apache.org/jira/browse/CALCITE-6343
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Barry Kelly
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> CALCITE-5869 introduced a change which removes MEASURE when inferring the
> return type when an operator is applied.
> The {{AS}} keyword for aliases is implemented as an operator in the SQL AST.
> Using {{AS}} removes MEASURE when typing expressions that have an alias in
> the {{SELECT}} clause.
> Thus, the type of {{SELECT m}} and {{SELECT m AS m}} are different when {{m}}
> is a measure. This is not desirable.
> Proposed fix: don't change type when using the {{AS}} operator.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6343) AS alias operator strips MEASUREness from measures
[
https://issues.apache.org/jira/browse/CALCITE-6343?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17831110#comment-17831110
]
Julian Hyde commented on CALCITE-6343:
--
I edited the description, removing the explicit link. Note that CALCITE-5689
now has strike-through, because the bug is closed.
It seems reasonable that "{{SELECT m AS m}}" should return a measure if {{m}}
is a measure. But we also need a gesture to convert m into a value; any
suggestions what that should be? Also, can we agree that "{{SELECT * FROM t}}"
is equivalent to writing "{{SELECT m, n, o, p FROM t}}", with each of the
columns remaining measures (or non-measures).
Is this behavior documented anywhere?
> AS alias operator strips MEASUREness from measures
> --
>
> Key: CALCITE-6343
> URL: https://issues.apache.org/jira/browse/CALCITE-6343
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Barry Kelly
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> CALCITE-5869 introduced a change which removes MEASURE when inferring the
> return type when an operator is applied.
> The {{AS}} keyword for aliases is implemented as an operator in the SQL AST.
> Using {{AS}} removes MEASURE when typing expressions that have an alias in
> the {{SELECT}} clause.
> Thus, the type of {{SELECT m}} and {{SELECT m AS m}} are different when {{m}}
> is a measure. This is not desirable.
> Proposed fix: don't change type when using the {{AS}} operator.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6343) AS alias operator strips MEASUREness from measures
[
https://issues.apache.org/jira/browse/CALCITE-6343?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-6343:
-
Description:
CALCITE-5869 introduced a change which removes MEASURE when inferring the
return type when an operator is applied.
The {{AS}} keyword for aliases is implemented as an operator in the SQL AST.
Using {{AS}} removes MEASURE when typing expressions that have an alias in the
{{SELECT}} clause.
Thus, the type of {{SELECT m}} and {{SELECT m AS m}} are different when {{m}}
is a measure. This is not desirable.
Proposed fix: don't change type when using the {{AS}} operator.
was:
[CALCITE-5869|https://issues.apache.org/jira/browse/CALCITE-5869] introduced a
change which removes MEASURE when inferring the return type when an operator is
applied.
The {{AS}} keyword for aliases is implemented as an operator in the SQL AST.
Using {{AS}} removes MEASURE when typing expressions that have an alias in the
{{SELECT}} clause.
Thus, the type of {{SELECT m}} and {{SELECT m AS m}} are different when {{m}}
is a measure. This is not desirable.
Proposed fix: don't change type when using the {{AS}} operator.
> AS alias operator strips MEASUREness from measures
> --
>
> Key: CALCITE-6343
> URL: https://issues.apache.org/jira/browse/CALCITE-6343
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Barry Kelly
>Priority: Minor
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> CALCITE-5869 introduced a change which removes MEASURE when inferring the
> return type when an operator is applied.
> The {{AS}} keyword for aliases is implemented as an operator in the SQL AST.
> Using {{AS}} removes MEASURE when typing expressions that have an alias in
> the {{SELECT}} clause.
> Thus, the type of {{SELECT m}} and {{SELECT m AS m}} are different when {{m}}
> is a measure. This is not desirable.
> Proposed fix: don't change type when using the {{AS}} operator.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6315) Support PostgreSQL TO_CHAR, TO_DATE, TO_TIMESTAMP
[ https://issues.apache.org/jira/browse/CALCITE-6315?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17829930#comment-17829930 ] Julian Hyde commented on CALCITE-6315: -- OK, great, make sure that it tests all format elements. > Support PostgreSQL TO_CHAR, TO_DATE, TO_TIMESTAMP > - > > Key: CALCITE-6315 > URL: https://issues.apache.org/jira/browse/CALCITE-6315 > Project: Calcite > Issue Type: Sub-task >Reporter: James Duong >Priority: Minor > > PostgreSQL supports different format strings than the version we have > implemented. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6338) RelMdCollation#project can return an incomplete list of collations
[
https://issues.apache.org/jira/browse/CALCITE-6338?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17829666#comment-17829666
]
Julian Hyde commented on CALCITE-6338:
--
Makes sense. I would mention 'in the presence of aliasing' (as in
[aliasing|https://en.wikipedia.org/wiki/Aliasing_(computing)]) or something in
the summary, and in your test case.
I would like to see a test case where there are multiple aliased columns. If 1
and 2 are aliases, and 3 and 4 are aliases, therefore [1 3] [1 4] [2 3] [2 4]
are equivalent collations.
I don't like how in your implementation one line became 20. I used to
understand that method, now I no longer do. Introduce abstractions so that the
implementation is at most 2 or 3 lines.
> RelMdCollation#project can return an incomplete list of collations
> --
>
> Key: CALCITE-6338
> URL: https://issues.apache.org/jira/browse/CALCITE-6338
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Ruben Q L
>Assignee: Ruben Q L
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> {{RelMdCollation#project}} can return an incomplete list of collations.
> Let us say we have a Project that projects the following expressions (notice
> that $2 will become $1 and $2 after the projection): $0, $2, $2, $3
> The Project's input has collation [2, 3]
> In order to calculate the Project's own collation, {{RelMdCollation#project}}
> will be called, and a MultiMap targets will be computed because, as in this
> case, a certain "source field" (e.g. 2) can have multiple project targets
> (e.g. 1 and 2). However, when the collation is being computed, *only the
> first target will be considered* (and the rest will be discarded):
> {code}
> public static @Nullable List project(RelMetadataQuery mq,
> RelNode input, List projects) {
> ...
> for (RelFieldCollation ifc : ic.getFieldCollations()) {
> final Collection integers = targets.get(ifc.getFieldIndex());
> if (integers.isEmpty()) {
> continue loop; // cannot do this collation
> }
> fieldCollations.add(ifc.withFieldIndex(integers.iterator().next()));
> // <-- HERE!!
> }
> {code}
> Because of this, the Project's collation will be [1 3], but there is also
> another valid one ([2 3]), so the correct (complete) result should be: [1 3]
> [2 3]
> This seems a minor problem, but it can be the root cause of more relevant
> issues. For instance, at the moment I have a scenario (not so easy to
> reproduce with a unit test) where a certain plan with a certain combination
> of rules in a HepPlanner results in a StackOverflow due to
> SortJoinTransposeRule being fired infinitely. The root cause is that, after
> the first application, the rule does not detect that the Join's left input is
> already sorted (due to the previous application of the rule), because there
> is a "problematic" Project on it (that shows the problem described above),
> which returns only one collation, whereas the second collation (the one being
> discarded) is the Sort's collation, so it would be one that would prevent the
> SortJoinTransposeRule from being re-applied over and over.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5155) Custom time frames
[
https://issues.apache.org/jira/browse/CALCITE-5155?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17829343#comment-17829343
]
Julian Hyde commented on CALCITE-5155:
--
I'm not aware of any SQL systems that have a SQL syntax. Many of the useful
cases are complex (imagine defining the alignment rules of ISOYEAR from
scratch) so would have to be accomplished by a plugin (Java code) rather than a
sublanguage.
> Custom time frames
> --
>
> Key: CALCITE-5155
> URL: https://issues.apache.org/jira/browse/CALCITE-5155
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Julian Hyde
>Assignee: Julian Hyde
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.33.0
>
> Time Spent: 2h 10m
> Remaining Estimate: 0h
>
> Allow a type system to define its own time units and how they are rolled up.
> Currently, time units are used in the {{EXTRACT}}, {{FLOOR}}, {{TRUNC}}
> functions, and include {{YEAR}}, {{QUARTER}}, {{MONTH}}, {{HOUR}},
> {{MINUTE}}, {{NANOSECOND}}. For example {{FLOOR(t TO HOUR)}} is valid.
> A type system would be allowed to define extra time units. Once a time unit
> is defined the {{EXTRACT}}, {{FLOOR}} and {{TRUNC}} functions should just
> work.
> The definition of might consist of a base unit and multiplier. So
> {{MINUTE15}} would be based on {{MINUTE}} with a multiplier of 15.
> Various rules know that you can roll up {{FLOOR(t TO DAY)}} to {{FLOOR(t TO
> MONTH)}} but you cannot roll {{FLOOR(t TO WEEK)}} to {{FLOOR(t TO MONTH)}}.
> When you define a new time unit, the type system can deduce that full set of
> time units that it can roll up to, and which can roll up to it.
> Should we support time units that do not evenly divide the next largest time
> unit? For example the number of seconds since the top of the hour modulo 7.
> 60 and 3,600 are not a multiples of 7, so {{SecondOfHourMod7}} would be
> different from {{SecondOfMinuteMod7}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6315) Support PostgreSQL TO_CHAR, TO_DATE, TO_TIMESTAMP
[
https://issues.apache.org/jira/browse/CALCITE-6315?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17828522#comment-17828522
]
Julian Hyde commented on CALCITE-6315:
--
If you want to help, add some tests. For CALCITE-2980 I added
{{cast-with-format.iq}} based on Impala's tests. If you added a Quidem test for
the above format strings, with output as generated by Postgres, then it's easy
to check whether Calcite's implementation is compliant for each format string.
> Support PostgreSQL TO_CHAR, TO_DATE, TO_TIMESTAMP
> -
>
> Key: CALCITE-6315
> URL: https://issues.apache.org/jira/browse/CALCITE-6315
> Project: Calcite
> Issue Type: Sub-task
>Reporter: James Duong
>Priority: Minor
>
> PostgreSQL supports different format strings than the version we have
> implemented.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6335) Quidem tests should allow specifying optimization passes to apply to programs
[ https://issues.apache.org/jira/browse/CALCITE-6335?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17828131#comment-17828131 ] Julian Hyde commented on CALCITE-6335: -- I agree. Currently we look over XML plans produced by RelOptRulesTest but we have no means to execute them and therefore we are not sure whether they give the desired result. CALCITE-6332 is a case in point. > Quidem tests should allow specifying optimization passes to apply to programs > - > > Key: CALCITE-6335 > URL: https://issues.apache.org/jira/browse/CALCITE-6335 > Project: Calcite > Issue Type: Improvement > Components: core >Affects Versions: 1.36.0 >Reporter: Mihai Budiu >Priority: Minor > > This facility would make it much easier to test various optimization passes. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-2980) Implement the FORMAT clause of the CAST operator
[
https://issues.apache.org/jira/browse/CALCITE-2980?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17828130#comment-17828130
]
Julian Hyde commented on CALCITE-2980:
--
It's a judgment call for you to make. I think it's based on where you are on
the bell curve that stretches from nothing works (0%) to everything works
(100%). If you're at 80% you should enable everything, and disable the few
things that work. If you're at 20% you should maybe make a new test, assembled
by copy-pasting statements or fragments from the full test that actually work.
> Implement the FORMAT clause of the CAST operator
>
>
> Key: CALCITE-2980
> URL: https://issues.apache.org/jira/browse/CALCITE-2980
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Jerin John
>Priority: Major
> Labels: pull-request-available
>
> SQL:2016 adds an optional {{FORMAT format}} clause to the {{CAST}} operator.
> It is a standard way to do what functions like {{TO_DATE}}, {{TO_NUMBER}},
> {{TO_CHAR}}, {{TO_TIMESTAMP}} have done in an ad hoc way (and with differing
> specifications among databases).
> Here is an example:
> {code:java}
> cast('01-05-2017' as date format 'DD-MM-')
> {code}
> The following paragraphs are copied from IMPALA-4018, which describes
> implementing this in Impala. (That case also describes cases where the
> implementations of {{TO_TIMESTAMP}} etc. in Hive, Impala, Oracle and
> PostgreSQL are not consistent with each other. We should take note as we
> implement these functions in Calcite.)
> SQL:2016 defines the following datetime templates
> {noformat}
> ::=
> { }...
> ::=
>
> |
> ::=
>
> |
> |
> |
> |
> |
> |
> |
> |
> |
> |
> |
> |
> |
> ::=
>
> |
> |
> |
> |
> |
> |
> |
> ::=
> | YYY | YY | Y
> ::=
> | RR
> ::=
> MM
> ::=
> DD
> ::=
> DDD
> ::=
> HH | HH12
> ::=
> HH24
> ::=
> MI
> ::=
> SS
> ::=
> S
> ::=
> FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9
> ::=
> A.M. | P.M.
> ::=
> TZH
> ::=
> TZM
> {noformat}
> SQL:2016 also introduced the {{FORMAT}} clause for {{CAST}} which is the
> standard way to do string <> datetime conversions
> {noformat}
> ::=
> CAST
>AS
> [ FORMAT ]
>
> ::=
>
> |
> ::=
>
> |
> ::=
>
> {noformat}
> For example:
> {noformat}
> CAST( AS [FORMAT ])
> CAST( AS [FORMAT ])
> cast(dt as string format 'DD-MM-')
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6270) Support FORMAT in CAST from Numeric and BYTES to String (Enabled in BigQuery)
[ https://issues.apache.org/jira/browse/CALCITE-6270?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17827761#comment-17827761 ] Julian Hyde commented on CALCITE-6270: -- [~jerin_john], Please use Calcite's type names in jira summaries. > Support FORMAT in CAST from Numeric and BYTES to String (Enabled in BigQuery) > - > > Key: CALCITE-6270 > URL: https://issues.apache.org/jira/browse/CALCITE-6270 > Project: Calcite > Issue Type: Bug >Reporter: Jerin John >Assignee: Jerin John >Priority: Minor > > Calcite is missing the below formats supported in BQ CAST with FORMAT: > * [Numeric to > STRING|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_numeric_type_as_string] > * [BYTES <-> > STRING|https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_bytes_as_string] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6327) getValidatedNodeTypeIfKnown should never throw
[ https://issues.apache.org/jira/browse/CALCITE-6327?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17827760#comment-17827760 ] Julian Hyde commented on CALCITE-6327: -- A test case would be useful. > getValidatedNodeTypeIfKnown should never throw > -- > > Key: CALCITE-6327 > URL: https://issues.apache.org/jira/browse/CALCITE-6327 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Claude Brisson >Priority: Major > > During validation, when a SqlNode has been rewritten (for instance when a > COALESCE call has been rewritten as a CASE call) but does not yet have a > RelDataType, the method SqlValidatorImpl.getValidatedNodeTypeIfKnown() throws > an exception because it relies on > SqlValidatorImpl.getValidatedNodeType(originalExpr), not on > SqlValidatorImpl.getValidatedNodeTypeIfKnown(originalExpr). > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6327) getValidatedNodeTypeIfKnown should never throw
[ https://issues.apache.org/jira/browse/CALCITE-6327?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17827759#comment-17827759 ] Julian Hyde commented on CALCITE-6327: -- That sounds right. However, mutations of the AST make me nervous. I know we mutate the AST for a few reasons (e.g. expanding '*') but if you are doing an unsanctioned mutation then the blame is on you, not Calcite. (Since Calcite was originally written, I have become more of a pure functional programmer and I wish we should remove all mutations.) > getValidatedNodeTypeIfKnown should never throw > -- > > Key: CALCITE-6327 > URL: https://issues.apache.org/jira/browse/CALCITE-6327 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Claude Brisson >Priority: Major > > During validation, when a SqlNode has been rewritten (for instance when a > COALESCE call has been rewritten as a CASE call) but does not yet have a > RelDataType, the method SqlValidatorImpl.getValidatedNodeTypeIfKnown() throws > an exception because it relies on > SqlValidatorImpl.getValidatedNodeType(originalExpr), not on > SqlValidatorImpl.getValidatedNodeTypeIfKnown(originalExpr). > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6317) Incorrect constant replacement when group keys are NULL
[
https://issues.apache.org/jira/browse/CALCITE-6317?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17827758#comment-17827758
]
Julian Hyde commented on CALCITE-6317:
--
Since the root cause is {{RelMdPredicates}}, can you add a test to
{{RelMetadataTest}}. It should be similar to {{testGetPredicatesForLiteralAgg}}.
> Incorrect constant replacement when group keys are NULL
> ---
>
> Key: CALCITE-6317
> URL: https://issues.apache.org/jira/browse/CALCITE-6317
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Mihai Budiu
>Assignee: Mihai Budiu
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> Here is a query taken from agg.iq:
> {code:sql}
> select deptno, gender, grouping_id(deptno, gender, deptno), count(*) as c
> from emp
> where deptno = 10
> group by rollup(gender, deptno)
> {code}
> The query plan initially is
> {code}
> LogicalProject(DEPTNO=[$1], GENDER=[$0], EXPR$2=[$2], C=[$3]), id = 72
> LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]],
> EXPR$2=[GROUPING_ID($1, $0, $1)], C=[COUNT()]), id = 71
> LogicalProject(GENDER=[$2], DEPTNO=[$1]), id = 70
> LogicalFilter(condition=[=($1, 10)]), id = 66
> LogicalTableScan(table=[[schema, EMP]]), id = 65
> {code}
> After applying PROJECT_REDUCE_EXPRESSIONS the plan looks like:
> {code}
> LogicalProject(DEPTNO=[CAST(10):INTEGER], GENDER=[$0], EXPR$2=[$2],
> C=[$3]), id = 82
> LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]],
> EXPR$2=[GROUPING_ID($1, $0, $1)], C=[COUNT()]), id = 78
> LogicalProject(GENDER=[$2], DEPTNO=[CAST(10):INTEGER]), id = 84
> LogicalFilter(condition=[=($1, 10)]), id = 74
> LogicalTableScan(table=[[schema, EMP]]), id = 65
> {code}
> The problem is in the outer LogicalProject, where the value 10 has replaced
> DEPTNO.
> However, DEPTNO can also be NULL, because of the groups in the
> LogicalAggregate.
> The constant should not be pushed past the aggregation.
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6209) Long queries are failing with "java.net.SocketTimeoutException: Read timed out" after 3 minutes
[
https://issues.apache.org/jira/browse/CALCITE-6209?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17827756#comment-17827756
]
Julian Hyde commented on CALCITE-6209:
--
Thank you for adding the link. And, thanks for the fix!
> Long queries are failing with "java.net.SocketTimeoutException: Read timed
> out" after 3 minutes
> ---
>
> Key: CALCITE-6209
> URL: https://issues.apache.org/jira/browse/CALCITE-6209
> Project: Calcite
> Issue Type: Bug
> Components: avatica
>Affects Versions: avatica-1.24.0
>Reporter: Konstantin
>Assignee: Istvan Toth
>Priority: Critical
> Labels: pull-request-available
> Fix For: avatica-1.25.0
>
> Attachments: screenshot-1.png
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Long queries are failing with "java.net.SocketTimeoutException: Read timed
> out" after 3 minutes.
> It's expected that they take more than 3 minutes and we need some way to
> increase timeout.
> I tried to change it via +http_connection_timeout+ property but it didn't
> help.
> Looks like that property is related to connect timeout only, but not to
> request timeout.
> I checked source code but didn't found any ways to change it there.
> Looks like it's always using default timeout=3minutes.
> !screenshot-1.png!
> Stack-trace:
> {code:java}
> org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error: Error while executing
> SQL "SELECT count(1) FROM trace": java.net.SocketTimeoutException: Read timed
> out
> at
> org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
> at
> org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:600)
> at
> org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$2(SQLQueryJob.java:503)
> at
> org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190)
> at
> org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:510)
> at
> org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:962)
> at
> org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:4130)
> at
> org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
> at
> org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190)
> at
> org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
> at
> org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5148)
> at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
> at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
> Caused by: java.sql.SQLException: Error while executing SQL "SELECT count(1)
> FROM trace": java.net.SocketTimeoutException: Read timed out
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164)
> at
> org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:218)
> at
> org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)
> at
> org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
> ... 12 more
> Caused by: java.lang.RuntimeException: java.net.SocketTimeoutException: Read
> timed out
> at
> org.apache.calcite.avatica.remote.AvaticaCommonsHttpClientImpl.send(AvaticaCommonsHttpClientImpl.java:155)
> at
> org.apache.calcite.avatica.remote.RemoteService.apply(RemoteService.java:36)
> at
> org.apache.calcite.avatica.remote.JsonService.apply(JsonService.java:140)
> at
> org.apache.calcite.avatica.remote.RemoteMeta$14.call(RemoteMeta.java:310)
> at
> org.apache.calcite.avatica.remote.RemoteMeta$14.call(RemoteMeta.java:307)
> at
> org.apache.calcite.avatica.AvaticaConnection.invokeWithRetries(AvaticaConnection.java:795)
> at
> org.apache.calcite.avatica.remote.RemoteMeta.fetch(RemoteMeta.java:306)
> at
> org.apache.calcite.avatica.MetaImpl$FetchIterator.moveNext(MetaImpl.java:1643)
> at
> org.apache.calcite.avatica.MetaImpl$FetchIterator.(MetaImpl.java:1611)
> at
> org.apache.calcite.avatica.MetaImpl$FetchIterator.(MetaImpl.java:1586)
> at
> org.apache.calcite.avatica.MetaImpl$FetchIterable.iterator(MetaImpl.java:1581)
> at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:98)
>
[jira] [Commented] (CALCITE-6314) Add RANDOM function (enabled in Postgres library)
[ https://issues.apache.org/jira/browse/CALCITE-6314?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17827755#comment-17827755 ] Julian Hyde commented on CALCITE-6314: -- Looks like you figured it out. By the way, when we're figuring out who did what work, we generally use the author of the git commit. > Add RANDOM function (enabled in Postgres library) > - > > Key: CALCITE-6314 > URL: https://issues.apache.org/jira/browse/CALCITE-6314 > Project: Calcite > Issue Type: Sub-task >Reporter: James Duong >Assignee: James Duong >Priority: Major > Labels: pull-request-available > Fix For: 1.37.0 > > > This is an alias for RAND(), except it should not support passing in a seed. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-2980) Implement the FORMAT clause of the CAST operator
[
https://issues.apache.org/jira/browse/CALCITE-2980?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17827753#comment-17827753
]
Julian Hyde commented on CALCITE-2980:
--
A good test is really valuable. I wouldn't disable the whole test, because
you'll never find time to re-enable it. So, I'd take the time to selectively
disable tests. Use 'if' rather than comments, to minimize changes to the .iq
file.
It's possible that it's less effort to implement a format string element than
to disable a test. If so, do that.
> Implement the FORMAT clause of the CAST operator
>
>
> Key: CALCITE-2980
> URL: https://issues.apache.org/jira/browse/CALCITE-2980
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Jerin John
>Priority: Major
> Labels: pull-request-available
>
> SQL:2016 adds an optional {{FORMAT format}} clause to the {{CAST}} operator.
> It is a standard way to do what functions like {{TO_DATE}}, {{TO_NUMBER}},
> {{TO_CHAR}}, {{TO_TIMESTAMP}} have done in an ad hoc way (and with differing
> specifications among databases).
> Here is an example:
> {code:java}
> cast('01-05-2017' as date format 'DD-MM-')
> {code}
> The following paragraphs are copied from IMPALA-4018, which describes
> implementing this in Impala. (That case also describes cases where the
> implementations of {{TO_TIMESTAMP}} etc. in Hive, Impala, Oracle and
> PostgreSQL are not consistent with each other. We should take note as we
> implement these functions in Calcite.)
> SQL:2016 defines the following datetime templates
> {noformat}
> ::=
> { }...
> ::=
>
> |
> ::=
>
> |
> |
> |
> |
> |
> |
> |
> |
> |
> |
> |
> |
> |
> ::=
>
> |
> |
> |
> |
> |
> |
> |
> ::=
> | YYY | YY | Y
> ::=
> | RR
> ::=
> MM
> ::=
> DD
> ::=
> DDD
> ::=
> HH | HH12
> ::=
> HH24
> ::=
> MI
> ::=
> SS
> ::=
> S
> ::=
> FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9
> ::=
> A.M. | P.M.
> ::=
> TZH
> ::=
> TZM
> {noformat}
> SQL:2016 also introduced the {{FORMAT}} clause for {{CAST}} which is the
> standard way to do string <> datetime conversions
> {noformat}
> ::=
> CAST
>AS
> [ FORMAT ]
>
> ::=
>
> |
> ::=
>
> |
> ::=
>
> {noformat}
> For example:
> {noformat}
> CAST( AS [FORMAT ])
> CAST( AS [FORMAT ])
> cast(dt as string format 'DD-MM-')
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6332) Optimization CoreRules.AGGREGATE_EXPAND_DISTINCT_AGGREGATES_TO_JOIN produces incorrect results for aggregates with groupSets
[
https://issues.apache.org/jira/browse/CALCITE-6332?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17827752#comment-17827752
]
Julian Hyde commented on CALCITE-6332:
--
There are two algorithms to rewrite distinct aggregates. The older one uses
join. The newer one, introduced in CALCITE-732, uses grouping sets.
The difference is whether you call {{Config.withJoin(true)}}. As far as I know,
the newer one is superior in all cases. The older one may have bugs, or
limitations such as this one - not being able to handle an {{Aggregate}} with
grouping sets.
One solution is to make the join-based rule refuse to fire if it sees grouping
sets. The other is to discourage people from using that rule.
> Optimization CoreRules.AGGREGATE_EXPAND_DISTINCT_AGGREGATES_TO_JOIN produces
> incorrect results for aggregates with groupSets
>
>
> Key: CALCITE-6332
> URL: https://issues.apache.org/jira/browse/CALCITE-6332
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Mihai Budiu
>Priority: Minor
>
> The optimization rule does not seem to consider the groupSets at all.
> The following two queries produce the same resulting plan:
> {code:sql}
> select count(distinct deptno) as cd, count(*) as c
> from emp
> group by cube(deptno)
> {code}
> {code:sql}
> select count(distinct deptno) as cd, count(*) as c
> from emp
> group by deptno
> {code}
> (Notice that one query has a cube, while the other one doesn't)
> The produced plan is:
> {code}
> LogicalProject(CD=[$1], C=[$2]), id = 196
> LogicalAggregate(group=[{0}], CD=[COUNT($0)], C=[$SUM0($1)]), id = 201
> LogicalAggregate(group=[{0}], C=[COUNT()]), id = 198
> LogicalProject(DEPTNO=[$8]), id = 192
> LogicalTableScan(table=[[schema, EMP]]), id = 163
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6333) Queries with distinct aggregations with filter throw NPE when planned using joins
[
https://issues.apache.org/jira/browse/CALCITE-6333?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17827570#comment-17827570
]
Julian Hyde commented on CALCITE-6333:
--
Your PR seems to make the NPE go away by removing the filter. But does the new
plan give the correct result?
> Queries with distinct aggregations with filter throw NPE when planned using
> joins
> -
>
> Key: CALCITE-6333
> URL: https://issues.apache.org/jira/browse/CALCITE-6333
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Abhishek Agarwal
>Priority: Major
> Labels: pull-request-available
>
> If I changed the test "testDistinctWithFilterWithoutGroupBy" and use the
> AGGREGATE_EXPAND_DISTINCT_AGGREGATES_TO_JOIN rule instead of
> AGGREGATE_EXPAND_DISTINCT_AGGREGATES
> {code:java}
> @Test void testDistinctWithFilterWithoutGroupBy() {
> final String sql = "SELECT SUM(comm), COUNT(DISTINCT sal) FILTER (WHERE
> sal > 1000)\n"
> + "FROM emp";
> sql(sql)
> .withRule(CoreRules.AGGREGATE_EXPAND_DISTINCT_AGGREGATES_TO_JOIN)
> .check();
> }
> {code}
> the test fails with the exception below
> {code:java}
> java.lang.NullPointerException: sourceOf.get(2)
> at java.base/java.util.Objects.requireNonNull(Objects.java:347)
> at
> org.apache.calcite.rel.rules.AggregateExpandDistinctAggregatesRule.doRewrite(AggregateExpandDistinctAggregatesRule.java:740)
> at
> org.apache.calcite.rel.rules.AggregateExpandDistinctAggregatesRule.onMatch(AggregateExpandDistinctAggregatesRule.java:260)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:337)
> at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:556)
> at
> org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:420)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeRuleInstance(HepPlanner.java:243)
> at
> org.apache.calcite.plan.hep.HepInstruction$RuleInstance$State.execute(HepInstruction.java:178)
> at
> org.apache.calcite.plan.hep.HepPlanner.lambda$executeProgram$0(HepPlanner.java:211)
> at
> com.google.common.collect.ImmutableList.forEach(ImmutableList.java:405)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:210)
> at
> org.apache.calcite.plan.hep.HepProgram$State.execute(HepProgram.java:118)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:205)
> at
> org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:191)
> at
> org.apache.calcite.test.RelOptFixture.checkPlanning(RelOptFixture.java:378)
> at org.apache.calcite.test.RelOptFixture.check(RelOptFixture.java:330)
> at org.apache.calcite.test.RelOptFixture.check(RelOptFixture.java:314)
> at
> org.apache.calcite.test.RelOptRulesTest.testDistinctWithFilterWithoutGroupBy(RelOptRulesTest.java:1462)
> 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:725)
> 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.TimeoutInvocation.proceed(TimeoutInvocation.java:46)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
> at
> org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
> at
> org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
> at
> org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
> 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] [Commented] (CALCITE-6324) Type inferred for result of STDDEV, VAR_SAMP, etc. is incorrect
[ https://issues.apache.org/jira/browse/CALCITE-6324?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17825877#comment-17825877 ] Julian Hyde commented on CALCITE-6324: -- You can't say the type is 'incorrect' when the standard leaves it implementation-defined, and other implementations are inconsistent. The reasoning is similar to AVG. By default, AVG applied to an INTEGER column yields an INTEGER result. That is probably too few decimal digits in most cases. But at least the rules are simple: the result type equals the argument type. So it you want 3 digits to the right of the decimal from AVG, you can simply apply a cast to its argument. Your point that STDDEV's internal accumulator overflows seems to be somewhat separate from the complaints about the return type. Because STDDEV's formula sums the squares its arguments and then takes the square root, it makes sense that the accumulator would have twice as many digits of precision as the result. > Type inferred for result of STDDEV, VAR_SAMP, etc. is incorrect > --- > > Key: CALCITE-6324 > URL: https://issues.apache.org/jira/browse/CALCITE-6324 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Mihai Budiu >Assignee: Mihai Budiu >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > These functions are all use the same type inference algorithm, essentially > the algorithm used by AVG. > But if the values processed are decimal, STDDEV (and others) need much higher > precision to represent the result. (I am not sure that the inference is right > for integer types either, btw.) > This surfaced during the implementation of a fix for [CALCITE-6322]: if we > use the type inferred for these functions, the result overflows and causes a > runtime exception. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6309) Add REGEXP_LIKE function (enabled in Postgres library)
[ https://issues.apache.org/jira/browse/CALCITE-6309?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17825856#comment-17825856 ] Julian Hyde commented on CALCITE-6309: -- Thank you for doing this excellent research. I think that the best way to proceed is to commit something useful that has known and documented limitations. You could simply say, "In this version we support flags i (case-insensitive) and c (case-sensitive)". People can contribute further flags in future commits. > Add REGEXP_LIKE function (enabled in Postgres library) > -- > > Key: CALCITE-6309 > URL: https://issues.apache.org/jira/browse/CALCITE-6309 > Project: Calcite > Issue Type: Sub-task >Reporter: James Duong >Priority: Minor > Labels: pull-request-available > > * The Spark version of this is being implemented in CALCITE-6278 > * The PostgreSQL version requires supporting a 3-arg version that takes in > flags. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6309) Add REGEXP_LIKE function (enabled in Postgres library)
[ https://issues.apache.org/jira/browse/CALCITE-6309?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17825839#comment-17825839 ] Julian Hyde commented on CALCITE-6309: -- I think it would be OK (and simpler) to make the third argument available (optional) in all conformances. There is no strong requirement that we reject a 3-argument call if Calcite is pretending to be Hive or Spark. > Add REGEXP_LIKE function (enabled in Postgres library) > -- > > Key: CALCITE-6309 > URL: https://issues.apache.org/jira/browse/CALCITE-6309 > Project: Calcite > Issue Type: Sub-task >Reporter: James Duong >Priority: Minor > Labels: pull-request-available > > * The Spark version of this is being implemented in CALCITE-6278 > * The PostgreSQL version requires supporting a 3-arg version that takes in > flags. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6209) Long queries are failing with "java.net.SocketTimeoutException: Read timed out" after 3 minutes
[
https://issues.apache.org/jira/browse/CALCITE-6209?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17825753#comment-17825753
]
Julian Hyde commented on CALCITE-6209:
--
[~stoty], Please add a link to the commit on main branch.
> Long queries are failing with "java.net.SocketTimeoutException: Read timed
> out" after 3 minutes
> ---
>
> Key: CALCITE-6209
> URL: https://issues.apache.org/jira/browse/CALCITE-6209
> Project: Calcite
> Issue Type: Bug
> Components: avatica
>Affects Versions: avatica-1.24.0
>Reporter: Konstantin
>Assignee: Istvan Toth
>Priority: Critical
> Labels: pull-request-available
> Fix For: avatica-1.25.0
>
> Attachments: screenshot-1.png
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Long queries are failing with "java.net.SocketTimeoutException: Read timed
> out" after 3 minutes.
> It's expected that they take more than 3 minutes and we need some way to
> increase timeout.
> I tried to change it via +http_connection_timeout+ property but it didn't
> help.
> Looks like that property is related to connect timeout only, but not to
> request timeout.
> I checked source code but didn't found any ways to change it there.
> Looks like it's always using default timeout=3minutes.
> !screenshot-1.png!
> Stack-trace:
> {code:java}
> org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error: Error while executing
> SQL "SELECT count(1) FROM trace": java.net.SocketTimeoutException: Read timed
> out
> at
> org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
> at
> org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:600)
> at
> org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$2(SQLQueryJob.java:503)
> at
> org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190)
> at
> org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:510)
> at
> org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:962)
> at
> org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:4130)
> at
> org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
> at
> org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:190)
> at
> org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
> at
> org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5148)
> at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
> at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
> Caused by: java.sql.SQLException: Error while executing SQL "SELECT count(1)
> FROM trace": java.net.SocketTimeoutException: Read timed out
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:164)
> at
> org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:218)
> at
> org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)
> at
> org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
> ... 12 more
> Caused by: java.lang.RuntimeException: java.net.SocketTimeoutException: Read
> timed out
> at
> org.apache.calcite.avatica.remote.AvaticaCommonsHttpClientImpl.send(AvaticaCommonsHttpClientImpl.java:155)
> at
> org.apache.calcite.avatica.remote.RemoteService.apply(RemoteService.java:36)
> at
> org.apache.calcite.avatica.remote.JsonService.apply(JsonService.java:140)
> at
> org.apache.calcite.avatica.remote.RemoteMeta$14.call(RemoteMeta.java:310)
> at
> org.apache.calcite.avatica.remote.RemoteMeta$14.call(RemoteMeta.java:307)
> at
> org.apache.calcite.avatica.AvaticaConnection.invokeWithRetries(AvaticaConnection.java:795)
> at
> org.apache.calcite.avatica.remote.RemoteMeta.fetch(RemoteMeta.java:306)
> at
> org.apache.calcite.avatica.MetaImpl$FetchIterator.moveNext(MetaImpl.java:1643)
> at
> org.apache.calcite.avatica.MetaImpl$FetchIterator.(MetaImpl.java:1611)
> at
> org.apache.calcite.avatica.MetaImpl$FetchIterator.(MetaImpl.java:1586)
> at
> org.apache.calcite.avatica.MetaImpl$FetchIterable.iterator(MetaImpl.java:1581)
> at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:98)
>
[jira] [Commented] (CALCITE-6319) Add negative test to pow and power functions
[ https://issues.apache.org/jira/browse/CALCITE-6319?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17825094#comment-17825094 ] Julian Hyde commented on CALCITE-6319: -- None of the tests you added are negative tests. > Add negative test to pow and power functions > > > Key: CALCITE-6319 > URL: https://issues.apache.org/jira/browse/CALCITE-6319 > Project: Calcite > Issue Type: Test >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add negative test to pow and power functions -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6319) Add negative test to pow and power functions
[ https://issues.apache.org/jira/browse/CALCITE-6319?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17825093#comment-17825093 ] Julian Hyde commented on CALCITE-6319: -- It's much easier to read your case summary if you use upper-case for SQL keywords and function names. > Add negative test to pow and power functions > > > Key: CALCITE-6319 > URL: https://issues.apache.org/jira/browse/CALCITE-6319 > Project: Calcite > Issue Type: Test >Affects Versions: 1.36.0 >Reporter: Caican Cai >Priority: Minor > Labels: pull-request-available > Fix For: 1.37.0 > > > Add negative test to pow and power functions -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[
https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17825091#comment-17825091
]
Julian Hyde commented on CALCITE-6278:
--
Regarding the character literal syntax. That's configurable in Calcite (e.g.
{{LEX=BIG_QUERY}}; see CALCITE-4847). There's an easy way to check: does
{{SELECT CHAR_LENGTH('\t')}} return 1 or 2?
If Calcite isn't able to emulate the behavior of character literals in Spark 2+
or Hive, log a bug. It needs to be separate from this case.
> Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
>
>
> Key: CALCITE-6278
> URL: https://issues.apache.org/jira/browse/CALCITE-6278
> Project: Calcite
> Issue Type: Improvement
>Reporter: EveyWu
>Priority: Minor
> Labels: pull-request-available
> Attachments: image-2024-03-07-09-32-27-002.png,
> image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png,
> image-2024-03-09-11-38-08-797.png
>
>
> Add Spark functions that have been implemented but have different
> OperandTypes/Returns.
> Add Function
> [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp],
> [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like]
> # Since this function has the same implementation as the Spark
> [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike]
> function, the implementation can be directly reused.
> # -Since Spark 2.0, string literals (including regex patterns) are unescaped
> in SQL parser, also fix this bug in calcite.-
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[
https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17824994#comment-17824994
]
Julian Hyde commented on CALCITE-6278:
--
I think by 'unescaping' you mean handling backslashes in character literals. In
Spark and Hive, backslashes have a special meaning in character literals. In
Calcite, they don't.
(There was another discussion recently about the ESCAPE clause and escape
characters in LIKE. It gets very confusing especially when we are trying to
emulate MySQL. But I don't think you are talking about that. See CALCITE-6180.)
It doesn't make sense to deal with backslashes in the {{RLIKE}} function. Spark
and Hive don't do it, and neither should Calcite. Think of it this way: If I
have a table {{t}} with string columns {{x}} and {{y}}:
{code}
CREATE TABLE t (x VARCHAR(20), y VARCHAR(20);
{code}
I should be able to apply {{RLIKE}} to those strings:
{code}
SELECT *
FROM t
WHERE x RLIKE y
{code}
Unescaping may have happened when I populated that table, but no unescaping
happens when I execute that SELECT query.
> Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
>
>
> Key: CALCITE-6278
> URL: https://issues.apache.org/jira/browse/CALCITE-6278
> Project: Calcite
> Issue Type: Improvement
>Reporter: EveyWu
>Priority: Minor
> Labels: pull-request-available
> Attachments: image-2024-03-07-09-32-27-002.png,
> image-2024-03-09-11-13-49-064.png, image-2024-03-09-11-37-27-816.png,
> image-2024-03-09-11-38-08-797.png
>
>
> Add Spark functions that have been implemented but have different
> OperandTypes/Returns.
> Add Function
> [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp],
> [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like]
> # Since this function has the same implementation as the Spark
> [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike]
> function, the implementation can be directly reused.
> # Since Spark 2.0, string literals (including regex patterns) are unescaped
> in SQL parser, also fix this bug in calcite.
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6278) Add REGEXP, REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17824456#comment-17824456 ] Julian Hyde commented on CALCITE-6278: -- Can you expand on “Since Spark 2.0, string literals (including regex patterns) are unescaped in SQL parser” and give examples? Give a test case that proves it is happening in the parser, not in RLIKE. If unescaping is happening in Spark’s parser, Calcite should also do it in the parser. And in fact this should be a separate Jira. Does the Hive parser have similar behavior? > Add REGEXP, REGEXP_LIKE function (enabled in Spark library) > > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > Attachments: image-2024-03-07-09-32-27-002.png > > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp], > [REGEXP_LIKE|https://spark.apache.org/docs/latest/api/sql/index.html#regexp_like] > # Since this function has the same implementation as the Spark > [RLIKE|https://spark.apache.org/docs/latest/api/sql/index.html#rlike] > function, the implementation can be directly reused. > # Since Spark 2.0, string literals (including regex patterns) are unescaped > in SQL parser, also fix this bug in calcite. > > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6305) Increase coverage of unit tests for arrow adapter
[ https://issues.apache.org/jira/browse/CALCITE-6305?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17824453#comment-17824453 ] Julian Hyde commented on CALCITE-6305: -- Should be “Arrow” not “arrow”. Sorry to be pedantic, but this is going to be in our commit log and release notes forever. > Increase coverage of unit tests for arrow adapter > - > > Key: CALCITE-6305 > URL: https://issues.apache.org/jira/browse/CALCITE-6305 > Project: Calcite > Issue Type: Sub-task >Reporter: hongyu guo >Priority: Major > > Add more unit tests for arrow adapter. > for example > * casts, including lossy casts > * complex filter condition > * complex SQL > * and more... > Note: Calcite's support for the arrow feather format is not complete, so > before adding the testing process, we may need to implement new features > first. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6306) FILTER clauses for aggregate functions are not supported in MySQL, MariaDB and Starrocks
[
https://issues.apache.org/jira/browse/CALCITE-6306?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17824451#comment-17824451
]
Julian Hyde commented on CALCITE-6306:
--
Can you change the subject so it’s clear which calcite component this issue
affects? As currently written it sounds like a bug in MySQL.
> FILTER clauses for aggregate functions are not supported in MySQL, MariaDB
> and Starrocks
>
>
> Key: CALCITE-6306
> URL: https://issues.apache.org/jira/browse/CALCITE-6306
> Project: Calcite
> Issue Type: Bug
>Reporter: hongyu guo
>Priority: Minor
> Labels: pull-request-available
>
> {code:sql}
> mysql> select sum(x) filter (where x = 1) from t;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near '(where x = 1) from t' at line 1 {code}
> See details in https://modern-sql.com/feature/filter
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6284) Invalid conversion triggers ClassCastException
[
https://issues.apache.org/jira/browse/CALCITE-6284?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17824169#comment-17824169
]
Julian Hyde commented on CALCITE-6284:
--
Thank you!
> Invalid conversion triggers ClassCastException
> --
>
> Key: CALCITE-6284
> URL: https://issues.apache.org/jira/browse/CALCITE-6284
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Tim Nieradzik
>Priority: Major
>
> When passing an incompatible value (e.g. {{{}string{}}}) to a numeric
> parameter (e.g. {{{}TINYINT{}}}) via a prepared statement, a
> {{ClassCastException}} is thrown.
> Test case:
> {code:java}
> @Test void bindStringParameter() {
> for (SqlTypeName tpe : SqlTypeName.INT_TYPES) {
> final String sql =
> "with cte as (select cast(100 as " + tpe.getName() + ") as empid)"
> + "select * from cte where empid = ?";
> CalciteAssert.hr()
> .query(sql)
> .consumesPreparedStatement(p -> {
> p.setString(1, "100");
> })
> .returnsUnordered("EMPID=100");
> }
> } {code}
> Implement a validation with a more user-friendly message.
> See also [GitHub
> discussion|https://github.com/apache/calcite/pull/3687#discussion_r1489891786].
--
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=17824168#comment-17824168
]
Julian Hyde commented on CALCITE-6293:
--
[~asolimando], +1 on merging what we have. The new tickets should be under an
umbrella "Improve the Arrow adapter".
> Support OR condition in arrow adapter
> -
>
> Key: CALCITE-6293
> URL: https://issues.apache.org/jira/browse/CALCITE-6293
> Project: Calcite
> Issue Type: Sub-task
>Reporter: hongyu guo
>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] [Updated] (CALCITE-6302) Release Calcite 1.37.0
[ https://issues.apache.org/jira/browse/CALCITE-6302?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Julian Hyde updated CALCITE-6302: - Fix Version/s: 1.37.0 > Release Calcite 1.37.0 > -- > > Key: CALCITE-6302 > URL: https://issues.apache.org/jira/browse/CALCITE-6302 > Project: Calcite > Issue Type: Task >Reporter: Sergey Nuyanzin >Assignee: Sergey Nuyanzin >Priority: Major > Fix For: 1.37.0 > > > Releasing Calcite 1.37.0 -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6248) Illegal dates are accepted by casts
[
https://issues.apache.org/jira/browse/CALCITE-6248?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17823772#comment-17823772
]
Julian Hyde commented on CALCITE-6248:
--
Postel's law applies: "be conservative in what you send, be liberal in what you
accept". If we are sending a date, we need to use ISO-8601 format. But if we
are receiving a date in a cast, we should accept anything reasonable. Certainly
I think leading zeros (or missing leading zeros) are harmless.
> Illegal dates are accepted by casts
> ---
>
> Key: CALCITE-6248
> URL: https://issues.apache.org/jira/browse/CALCITE-6248
> Project: Calcite
> Issue Type: Bug
> Components: avatica, core
>Affects Versions: 1.36.0
>Reporter: Mihai Budiu
>Priority: Minor
> Labels: pull-request-available
> Time Spent: 20m
> Remaining Estimate: 0h
>
> The following test passes in SqlOperatorTest:
> {code:java}
> @Test public void testIllegalDate() {
> final SqlOperatorFixture f = fixture();
> f.checkScalar("cast('1945-02-32' as DATE)",
> "1945-03-04", "DATE NOT NULL");
> }
> {code}
> There is no February 32, I suspect that this expression should produce an
> error.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6284) Invalid conversion triggers ClassCastException
[
https://issues.apache.org/jira/browse/CALCITE-6284?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17823770#comment-17823770
]
Julian Hyde commented on CALCITE-6284:
--
Can you fill out the description? Describe what's going on (and going wrong) in
the test case.
> Invalid conversion triggers ClassCastException
> --
>
> Key: CALCITE-6284
> URL: https://issues.apache.org/jira/browse/CALCITE-6284
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Tim Nieradzik
>Priority: Major
>
> Test case:
> {code:java}
> @Test void bindStringParameter() {
> for (SqlTypeName tpe : SqlTypeName.INT_TYPES) {
> final String sql =
> "with cte as (select cast(100 as " + tpe.getName() + ") as empid)"
> + "select * from cte where empid = ?";
> CalciteAssert.hr()
> .query(sql)
> .consumesPreparedStatement(p -> {
> p.setString(1, "100");
> })
> .returnsUnordered("EMPID=100");
> }
> } {code}
> This throws a {{ClassCastException}} which should be a more user-friendly
> message.
> Discussion: [https://github.com/apache/calcite/pull/3687]}}{}}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6239) Add a PostGIS dialect that supports ST_ functions
[ https://issues.apache.org/jira/browse/CALCITE-6239?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17823753#comment-17823753 ] Julian Hyde commented on CALCITE-6239: -- [~bchapuis], In my [6239-postgis-dialect|https://github.com/julianhyde/calcite/tree/6239-postgis-dialect] branch I have added a commit that attempts to decouple the GEOMETRY type from the JTS Geometry class. Please take a look. > Add a PostGIS dialect that supports ST_ functions > - > > Key: CALCITE-6239 > URL: https://issues.apache.org/jira/browse/CALCITE-6239 > Project: Calcite > Issue Type: New Feature > Components: core, spatial >Reporter: Bertil Chapuis >Assignee: Bertil Chapuis >Priority: Minor > Labels: pull-request-available > > Calcite implements support for spatial types (geometry, point, etc.) and > spatial functions (ST_), and it can connect to PostGIS via a JdbcSchema. > However, the Postgresql dialect does not currently handle spatial types and > functions. As a result, Calcite tries to execute the spatial functions at the > level of the JVM instead of pushing them down to postgis. > As a result, the following query gets executed, but the type of the geom > column is incorrect: > SELECT id, geom FROM public.spatial_table > The following query fails with a ClassCastException as Calcite tries to use > the java implementation of the ST_SRID function: > SELECT id, ST_SRID(geom) FROM public.spatial_table > java.lang.ClassCastException: class org.postgresql.util.PGobject cannot be > cast to class org.locationtech.jts.geom.Geometry > (org.postgresql.util.PGobject and org.locationtech.jts.geom.Geometry are in > unnamed module of loader 'app') > In my current understanding, this issue could be addressed with a new > PostgisSqlDialect that extends PostgresqlSqlDialect and adds support for > spatial types and functions. Here is a tentative roadmap: > - Add all the spatial functions to the SqlKind class > - Create a PostgisSqlDialect class that extends PostgresqlSqlDialect > - Add support for the spatial types (geometry) by overriding the getCastSpec > method of the SqlDialect class > - Add support for the spatial functions by overriding the supportsFunction > method of the SqlDialect class > - Add support for the spatial aggregate functions by overriding the > supportsAggregateFunction method of the SqlDialect class -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6292) Support more arrow type
[
https://issues.apache.org/jira/browse/CALCITE-6292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17823745#comment-17823745
]
Julian Hyde commented on CALCITE-6292:
--
Arrow types are very similar to SQL and JDBC types. You should create a good
mapping between the types, and do most of the reasoning in the SQL domain.
> Support more arrow type
> ---
>
> Key: CALCITE-6292
> URL: https://issues.apache.org/jira/browse/CALCITE-6292
> Project: Calcite
> Issue Type: Sub-task
>Reporter: hongyu guo
>Priority: Major
>
> All arrow type:
> {code:java}
> public static enum ArrowTypeID {
> Null(Type.Null),
> Struct(Type.Struct_),
> List(Type.List),
> LargeList(Type.LargeList),
> FixedSizeList(Type.FixedSizeList),
> Union(Type.Union),
> Map(Type.Map),
> Int(Type.Int),
> FloatingPoint(Type.FloatingPoint),
> Utf8(Type.Utf8),
> LargeUtf8(Type.LargeUtf8),
> Binary(Type.Binary),
> LargeBinary(Type.LargeBinary),
> FixedSizeBinary(Type.FixedSizeBinary),
> Bool(Type.Bool),
> Decimal(Type.Decimal),
> Date(Type.Date),
> Time(Type.Time),
> Timestamp(Type.Timestamp),
> Interval(Type.Interval),
> Duration(Type.Duration),
> NONE(Type.NONE);
> }
> {code}
> we support now:
> {code:java}
> public static ArrowFieldType of(ArrowType arrowType) {
> switch (arrowType.getTypeID()) {
> case Int:
> int bitWidth = ((ArrowType.Int) arrowType).getBitWidth();
> switch (bitWidth) {
> case 64:
> return LONG;
> case 32:
> return INT;
> case 16:
> return SHORT;
> case 8:
> return BYTE;
> default:
> throw new IllegalArgumentException("Unsupported Int bit width: " +
> bitWidth);
> }
> case Bool:
> return BOOLEAN;
> case Utf8:
> return STRING;
> case FloatingPoint:
> FloatingPointPrecision precision = ((ArrowType.FloatingPoint)
> arrowType).getPrecision();
> switch (precision) {
> case SINGLE:
> return FLOAT;
> case DOUBLE:
> return DOUBLE;
> default:
> throw new IllegalArgumentException("Unsupported Floating point
> precision: " + precision);
> }
> case Date:
> return DATE;
> case Decimal:
> return DECIMAL;
> default:
> throw new IllegalArgumentException("Unsupported type: " + arrowType);
> }
> }
> {code}
--
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=17823744#comment-17823744
]
Julian Hyde commented on CALCITE-6293:
--
Can you provide more details?
This case (and others you have logged recently) may make sense for your
organization's planning, but does not provide enough details to know what
feature you intend to build. OR is already trivially supported by queries on
the Arrow adapter - all the data comes out of Arrow and is filtered in
EnumerableCalc or whatever - but if you intend to do something better you
should give details.
> Support OR condition in arrow adapter
> -
>
> Key: CALCITE-6293
> URL: https://issues.apache.org/jira/browse/CALCITE-6293
> Project: Calcite
> Issue Type: Sub-task
>Reporter: hongyu guo
>Priority: Major
>
> for example
> {code:java}
> String sql = "select \"intField\", \"stringField\"\n"
> + "from arrowdata\n"
> + "where \"intField\"=12 or \"stringField\"='12'"; {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6297) Support sub-queries in arrow adapter
[ https://issues.apache.org/jira/browse/CALCITE-6297?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17823468#comment-17823468 ] Julian Hyde commented on CALCITE-6297: -- I don’t understand what this means. Add description. > Support sub-queries in arrow adapter > > > Key: CALCITE-6297 > URL: https://issues.apache.org/jira/browse/CALCITE-6297 > Project: Calcite > Issue Type: Sub-task >Reporter: hongyu guo >Priority: Major > -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6278) Add REGEXP、REGEXP_LIKE function (enabled in Spark library)
[ https://issues.apache.org/jira/browse/CALCITE-6278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17823342#comment-17823342 ] Julian Hyde commented on CALCITE-6278: -- Use a real comma in the subject, please. Does RLIKE have identical semantics to REGEXP_LIKE in Spark? If so, say so in the test. Calcite's RLIKE function is used by the Hive library as well as Spark. Have your changes to escaping broken Hive? The escaping behavior is very subtle. Add more tests, and comments in those tests explaining what is going on. > Add REGEXP、REGEXP_LIKE function (enabled in Spark library) > --- > > Key: CALCITE-6278 > URL: https://issues.apache.org/jira/browse/CALCITE-6278 > Project: Calcite > Issue Type: Improvement >Reporter: EveyWu >Priority: Minor > Labels: pull-request-available > > Add Spark functions that have been implemented but have different > OperandTypes/Returns. > Add Function > [REGEXP|https://spark.apache.org/docs/latest/api/sql/index.html#regexp] > Since this function has the same implementation as the Big Query > [REGEXP_CONTAINS|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_contains] > function. the implementation can be directly reused. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Resolved] (CALCITE-6288) Error while compiling LIKE function after update to 1.36
[
https://issues.apache.org/jira/browse/CALCITE-6288?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-6288.
--
Resolution: Not A Problem
Make sure that the column has SQL type {{{}MAP{}}}. Add some
test cases that use SQL types, not Java types, and check your understanding of
how Calcite reasons about SQL types.
Closing as not-a-bug.
> Error while compiling LIKE function after update to 1.36
>
>
> Key: CALCITE-6288
> URL: https://issues.apache.org/jira/browse/CALCITE-6288
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Konstantin
>Priority: Major
> Attachments: Error.txt, NormalGenCode.txt,
> image-2024-03-04-12-02-53-920.png, image-2024-03-04-12-03-15-637.png
>
>
> After update to calcite v1.36 we are getting the following error on execution
> of the following query:
> {code:sql}
> SELECT * FROM "calls_m" WHERE "params"['web.url'] LIKE '%resign%';
> {code}
> {code:java}
> -> CompileException: Line 16, Column 174: No applicable constructor/method
> found for actual parameters "java.lang.Object, java.lang.String"; candidates
> are: "public boolean
> org.apache.calcite.runtime.SqlFunctions$LikeFunction.like(java.lang.String,
> java.lang.String, java.lang.String)", "public boolean
> org.apache.calcite.runtime.SqlFunctions$LikeFunction.like(java.lang.String,
> java.lang.String)"
> {code}
> [^Error.txt]
> In v.1.33 it was working fine.
> Generated code from v1.33:
> [^NormalGenCode.txt]
> So, in 1.33 it was calling:
> {code:java}
> Boolean.valueOf(org.apache.calcite.runtime.SqlFunctions.like(item_value ==
> null ? (String) null : item_value.toString(), "%resign%"));
> {code}
> but in 1.36 it's:
> {code:java}
> Boolean.valueOf($L4J$C$new_org_apache_calcite_runtime_SqlFunctions_LikeFunction_.like(item_value,
> "%resign%"));
> {code}
> So, it's not calling +toString()+ anymore.
> Looks like it's because it's using reflective implementor instead of method
> implementor:
> v.1.33
> !image-2024-03-04-12-02-53-920.png!
> v.1.36
> !image-2024-03-04-12-03-15-637.png!
> org.apache.calcite.adapter.enumerable.RexImpTable.Builder
> Table DDL:
> {code:sql}
> CREATE TABLE "profiler"."calls_m" (
> "start_timestamp" TIMESTAMP(3) NOT NULL NOT NULL,
> "method_id" JavaType(long) NOT NULL NOT NULL,
> "method_name" JavaType(class java.lang.String),
> "duration" JavaType(int) NOT NULL NOT NULL,
> "cpu_time" JavaType(long) NOT NULL NOT NULL,
> "suspend_duration" JavaType(int) NOT NULL NOT NULL,
> "queue_wait_duration" JavaType(int) NOT NULL NOT NULL,
> "memory_used" JavaType(long) NOT NULL NOT NULL,
> "calls" JavaType(int) NOT NULL NOT NULL,
> "transactions" JavaType(long) NOT NULL NOT NULL,
> "logs_generated" JavaType(int) NOT NULL NOT NULL,
> "logs_written" JavaType(int) NOT NULL NOT NULL,
> "file_read" JavaType(long) NOT NULL NOT NULL,
> "file_written" JavaType(long) NOT NULL NOT NULL,
> "net_read" JavaType(long) NOT NULL NOT NULL,
> "net_written" JavaType(long) NOT NULL NOT NULL,
> "node_name" JavaType(class java.lang.String),
> "thread_name" JavaType(class java.lang.String),
> "dump_dir_id" JavaType(int) NOT NULL NOT NULL,
> "trace_id" JavaType(class
> com.netcracker.profiler.calcite.trace.TraceId),
> "profiler_url" JavaType(class java.lang.String),
> "params" JavaType(interface java.util.Map)
> );
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6289) View in union cannot be reused
[
https://issues.apache.org/jira/browse/CALCITE-6289?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17823332#comment-17823332
]
Julian Hyde commented on CALCITE-6289:
--
What would the plan look like if reuse was occurring?
Reusing the plan nodes is one thing. It's quite possible - especially under the
Volcano planner - that the LogicalAggregate is in fact reused.
But reusing the data at runtime is different. For that, the solution is a DAG
plan using the Spool operator. You should be able to find Jira cases concerning
Spool.
> View in union cannot be reused
> --
>
> Key: CALCITE-6289
> URL: https://issues.apache.org/jira/browse/CALCITE-6289
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.36.0
>Reporter: yisha zhou
>Priority: Major
>
> When union two different projection of same view, the view cannot be reuse.
> To reproduce the problem, please create a `MockRelViewTable` in
> `MockCatalogReaderExtended` use code below:
> {code:java}
> List empModifiableViewNames5 =
> ImmutableList.of(salesSchema.getCatalogName(), salesSchema.getName(),
> "EMP_VIEW");
> TableMacro empModifiableViewMacro5 =
> MockModifiableViewRelOptTable.viewMacro(rootSchema,
> "select EMPNO, DEPTNO, ENAME\n"
> + "from EMPDEFAULTS\n"
> + "group by EMPNO, DEPTNO, ENAME",
> empModifiableViewNames5.subList(0, 2),
> ImmutableList.of(empModifiableViewNames5.get(2)), true);
> TranslatableTable empModifiableView5 =
> empModifiableViewMacro5.apply(ImmutableList.of());
> MockTable mockEmpViewTable5 =
> MockRelViewTable.create(
> (ViewTable) empModifiableView5, this,
> empModifiableViewNames5.get(0), empModifiableViewNames5.get(1),
> empModifiableViewNames5.get(2), false, 20, null);
> registerTable(mockEmpViewTable5); {code}
> And then add a test in `SqlToRelConverterTest`:
>
> {code:java}
> @Test void testView() {
> final String sql = "select empno from EMP_VIEW\n"
> + "union all\n"
> + "select deptno from EMP_VIEW";
> sql(sql).withExtendedTester().ok();
> } {code}
> You will get the plan:
>
> {code:java}
> LogicalUnion(all=[true])
> LogicalProject(EMPNO=[$0])
> LogicalAggregate(group=[{0, 1, 2}])
> LogicalProject(EMPNO=[$0], DEPTNO=[$7], ENAME=[$1])
> LogicalTableScan(table=[[CATALOG, SALES, EMPDEFAULTS]])
> LogicalProject(DEPTNO=[$1])
> LogicalAggregate(group=[{0, 1, 2}])
> LogicalProject(EMPNO=[$0], DEPTNO=[$7], ENAME=[$1])
> LogicalTableScan(table=[[CATALOG, SALES, EMPDEFAULTS]]) {code}
> Obviously, RelNode tree in the view is not reused. The root cause is that we
> expand the views in `SqlToRelConverter#convertIdentifier` . Therefore I
> suggest to reintroduce the `SqlToRelConverter.Config#isConvertTableAccess`
> which is removed in
> [CALCITE-3801|https://issues.apache.org/jira/browse/CALCITE-3801]. So that we
> can expand view at the time we want, e.g. after divide the projections in
> union and logic in the view into two subgraph.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6288) Error while compiling LIKE function after update to 1.36
[
https://issues.apache.org/jira/browse/CALCITE-6288?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17823070#comment-17823070
]
Julian Hyde commented on CALCITE-6288:
--
The SQL doesn’t seem to have a valid type. An item in “params” has type Object,
not String. I’m not even sure what SQL type Object would map to. You were
fortunate that Calcite generated “.toString()” previously, but Calcite doesn’t
seem wrong to remove it.
So, I don’t think this is a bug.
> Error while compiling LIKE function after update to 1.36
>
>
> Key: CALCITE-6288
> URL: https://issues.apache.org/jira/browse/CALCITE-6288
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.36.0
>Reporter: Konstantin
>Priority: Major
> Attachments: Error.txt, NormalGenCode.txt,
> image-2024-03-04-12-02-53-920.png, image-2024-03-04-12-03-15-637.png
>
>
> After update to calcite v1.36 we are getting the following error on execution
> of the following query:
> {code:sql}
> SELECT * FROM "calls_m" WHERE "params"['web.url'] LIKE '%resign%';
> {code}
> {code:java}
> -> CompileException: Line 16, Column 174: No applicable constructor/method
> found for actual parameters "java.lang.Object, java.lang.String"; candidates
> are: "public boolean
> org.apache.calcite.runtime.SqlFunctions$LikeFunction.like(java.lang.String,
> java.lang.String, java.lang.String)", "public boolean
> org.apache.calcite.runtime.SqlFunctions$LikeFunction.like(java.lang.String,
> java.lang.String)"
> {code}
> [^Error.txt]
> In v.1.33 it was working fine.
> Generated code from v1.33:
> [^NormalGenCode.txt]
> So, in 1.33 it was calling:
> {code:java}
> Boolean.valueOf(org.apache.calcite.runtime.SqlFunctions.like(item_value ==
> null ? (String) null : item_value.toString(), "%resign%"));
> {code}
> but in 1.36 it's:
> {code:java}
> Boolean.valueOf($L4J$C$new_org_apache_calcite_runtime_SqlFunctions_LikeFunction_.like(item_value,
> "%resign%"));
> {code}
> So, it's not calling +toString()+ anymore.
> Looks like it's because it's using reflective implementor instead of method
> implementor:
> v.1.33
> !image-2024-03-04-12-02-53-920.png!
> v.1.36
> !image-2024-03-04-12-03-15-637.png!
> org.apache.calcite.adapter.enumerable.RexImpTable.Builder
> Table DDL:
> {code:sql}
> CREATE TABLE "profiler"."calls_m" (
> "start_timestamp" TIMESTAMP(3) NOT NULL NOT NULL,
> "method_id" JavaType(long) NOT NULL NOT NULL,
> "method_name" JavaType(class java.lang.String),
> "duration" JavaType(int) NOT NULL NOT NULL,
> "cpu_time" JavaType(long) NOT NULL NOT NULL,
> "suspend_duration" JavaType(int) NOT NULL NOT NULL,
> "queue_wait_duration" JavaType(int) NOT NULL NOT NULL,
> "memory_used" JavaType(long) NOT NULL NOT NULL,
> "calls" JavaType(int) NOT NULL NOT NULL,
> "transactions" JavaType(long) NOT NULL NOT NULL,
> "logs_generated" JavaType(int) NOT NULL NOT NULL,
> "logs_written" JavaType(int) NOT NULL NOT NULL,
> "file_read" JavaType(long) NOT NULL NOT NULL,
> "file_written" JavaType(long) NOT NULL NOT NULL,
> "net_read" JavaType(long) NOT NULL NOT NULL,
> "net_written" JavaType(long) NOT NULL NOT NULL,
> "node_name" JavaType(class java.lang.String),
> "thread_name" JavaType(class java.lang.String),
> "dump_dir_id" JavaType(int) NOT NULL NOT NULL,
> "trace_id" JavaType(class
> com.netcracker.profiler.calcite.trace.TraceId),
> "profiler_url" JavaType(class java.lang.String),
> "params" JavaType(interface java.util.Map)
> );
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6281) Add test infrastructure to ensure that Calcite is consistent with the SQL dialects it is trying to emulate
[
https://issues.apache.org/jira/browse/CALCITE-6281?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17820939#comment-17820939
]
Julian Hyde commented on CALCITE-6281:
--
Work in progress, based on Stamatis' [PR
3704|https://github.com/apache/calcite/pull/3704]:
[6281-emulation-tests|https://github.com/julianhyde/calcite/tree/6281-emulation-tests].
> Add test infrastructure to ensure that Calcite is consistent with the SQL
> dialects it is trying to emulate
> --
>
> Key: CALCITE-6281
> URL: https://issues.apache.org/jira/browse/CALCITE-6281
> Project: Calcite
> Issue Type: Improvement
>Reporter: Julian Hyde
>Assignee: Julian Hyde
>Priority: Major
>
> Add test infrastructure to ensure that Calcite is consistent with the SQL
> dialects it is trying to emulate.
> Calcite is able to emulate other SQL dialects. For example, it can pretend to
> be MySQL by setting "lib=mysql, lex=mysql, conformance=mysql_5". In that
> mode, the user would expect there to be a SUBSTR function, and that
> {{SUBSTR('abc' FROM 0)}} would [return the empty string, the same as
> MySQL|https://github.com/apache/calcite/blob/022d878a73dec796bb72743804a6dded7c239bd3/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java#L9428].
> (On Postgres, the same expression returns 'abc'.)
> How do we trust that MySQL does indeed have that behavior? How do we find out
> if, in a later release, the MySQL team decides to change the behavior. That
> is the goal of this case.
> We propose to add tests that run expressions on both MySQL and
> Calcite-pretending-to-be-MySQL, and ensure that the result is the same on
> both. We use
> [testContainers|https://github.com/testcontainers/testcontainers-java] to
> instantiate, via Docker, and instance of MySQL.
> See discussion [Ensuring that Calcite is consistent with other SQL
> systems|https://lists.apache.org/thread/mxy9p6cy8sssf1sq3gz7zkzm6hsysns5].
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Assigned] (CALCITE-6281) Add test infrastructure to ensure that Calcite is consistent with the SQL dialects it is trying to emulate
[
https://issues.apache.org/jira/browse/CALCITE-6281?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde reassigned CALCITE-6281:
Assignee: Julian Hyde
> Add test infrastructure to ensure that Calcite is consistent with the SQL
> dialects it is trying to emulate
> --
>
> Key: CALCITE-6281
> URL: https://issues.apache.org/jira/browse/CALCITE-6281
> Project: Calcite
> Issue Type: Improvement
>Reporter: Julian Hyde
>Assignee: Julian Hyde
>Priority: Major
>
> Add test infrastructure to ensure that Calcite is consistent with the SQL
> dialects it is trying to emulate.
> Calcite is able to emulate other SQL dialects. For example, it can pretend to
> be MySQL by setting "lib=mysql, lex=mysql, conformance=mysql_5". In that
> mode, the user would expect there to be a SUBSTR function, and that
> {{SUBSTR('abc' FROM 0)}} would [return the empty string, the same as
> MySQL|https://github.com/apache/calcite/blob/022d878a73dec796bb72743804a6dded7c239bd3/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java#L9428].
> (On Postgres, the same expression returns 'abc'.)
> How do we trust that MySQL does indeed have that behavior? How do we find out
> if, in a later release, the MySQL team decides to change the behavior. That
> is the goal of this case.
> We propose to add tests that run expressions on both MySQL and
> Calcite-pretending-to-be-MySQL, and ensure that the result is the same on
> both. We use
> [testContainers|https://github.com/testcontainers/testcontainers-java] to
> instantiate, via Docker, and instance of MySQL.
> See discussion [Ensuring that Calcite is consistent with other SQL
> systems|https://lists.apache.org/thread/mxy9p6cy8sssf1sq3gz7zkzm6hsysns5].
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-6219) Support SQL Validation for Tables with columns tagged as 'filter required'
[
https://issues.apache.org/jira/browse/CALCITE-6219?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-6219.
--
Fix Version/s: 1.37.0
Resolution: Fixed
Fixed in
[2558c13c|https://github.com/apache/calcite/commit/2558c13cdb8b6a8e1608112a902b1bf9d97b5386].
Thanks, [~oliverlee]!
> Support SQL Validation for Tables with columns tagged as 'filter required'
> --
>
> Key: CALCITE-6219
> URL: https://issues.apache.org/jira/browse/CALCITE-6219
> Project: Calcite
> Issue Type: New Feature
>Reporter: Oliver Lee
>Assignee: Oliver Lee
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.37.0
>
>
> Suppose that a user's Table definitions come in with a tag on certain columns
> that indicates the the column is required to have a filter on it for all
> incoming queries.
>
> I would like to add support to validate that incoming queries satisfies the
> table condition.
> If all of the table's specified fields has a filter on it (present in a WHERE
> or HAVING clause for the query), then it will not error.
>
> ex.
> {{EMP}} table specifies that {{EMPNO}} requires a filter
>
> {{select * from emp where empno = 1}} -> No error
> {{select * from emp where ename = 'bob' -> Error}}
> {{select * from emp -> Error}}
>
> The validation would occur after the namespace validation in
> {{SqlValidatorImpl}} as a separate pass.
>
> I am envisioning that the full filter validation algorithm will form a couple
> of key steps
> * Scanning the catalog/schema/tables and determining which fields are
> tagged to always require filters
> * A pass through the SQL statement to see if a certain field needs to be
> filtered multiple times (potentially for CTE, joins? needs further
> investigation)
> * A pass through the SQL statement to discover filters on the statement
>
> In determining whether a {{WHERE}} or {{HAVING}} clause contains a certain
> field identifier, there will need to be a helper visitor for WHERE or HAVING
> SqlNodes to collect all of the SqlIdentifiers that could be nested within the
> {{operandList}}
>
> Special considerations:
> * joins
> * CTEs
> * subqueries
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6281) Add test infrastructure to ensure that Calcite is consistent with the SQL dialects it is trying to emulate
[
https://issues.apache.org/jira/browse/CALCITE-6281?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17820926#comment-17820926
]
Julian Hyde commented on CALCITE-6281:
--
The only goal is to compare specifications: given an expression, do Calcite and
D (a database implementing dialect X) return the same results? (Stretch goal:
make sure that the results have the same type, modulo differences in the type
systems; make sure that Calcite gives an error if and only if D gives an error.)
> Add test infrastructure to ensure that Calcite is consistent with the SQL
> dialects it is trying to emulate
> --
>
> Key: CALCITE-6281
> URL: https://issues.apache.org/jira/browse/CALCITE-6281
> Project: Calcite
> Issue Type: Improvement
>Reporter: Julian Hyde
>Priority: Major
>
> Add test infrastructure to ensure that Calcite is consistent with the SQL
> dialects it is trying to emulate.
> Calcite is able to emulate other SQL dialects. For example, it can pretend to
> be MySQL by setting "lib=mysql, lex=mysql, conformance=mysql_5". In that
> mode, the user would expect there to be a SUBSTR function, and that
> {{SUBSTR('abc' FROM 0)}} would [return the empty string, the same as
> MySQL|https://github.com/apache/calcite/blob/022d878a73dec796bb72743804a6dded7c239bd3/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java#L9428].
> (On Postgres, the same expression returns 'abc'.)
> How do we trust that MySQL does indeed have that behavior? How do we find out
> if, in a later release, the MySQL team decides to change the behavior. That
> is the goal of this case.
> We propose to add tests that run expressions on both MySQL and
> Calcite-pretending-to-be-MySQL, and ensure that the result is the same on
> both. We use
> [testContainers|https://github.com/testcontainers/testcontainers-java] to
> instantiate, via Docker, and instance of MySQL.
> See discussion [Ensuring that Calcite is consistent with other SQL
> systems|https://lists.apache.org/thread/mxy9p6cy8sssf1sq3gz7zkzm6hsysns5].
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6219) Support SQL Validation for Tables with columns tagged as 'filter required'
[
https://issues.apache.org/jira/browse/CALCITE-6219?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17820898#comment-17820898
]
Julian Hyde commented on CALCITE-6219:
--
[~oliverlee], Looks like we've resolved your comment (accessing columns by name
vs ordinal) so I'll merge shortly.
> Support SQL Validation for Tables with columns tagged as 'filter required'
> --
>
> Key: CALCITE-6219
> URL: https://issues.apache.org/jira/browse/CALCITE-6219
> Project: Calcite
> Issue Type: New Feature
>Reporter: Oliver Lee
>Assignee: Oliver Lee
>Priority: Major
> Labels: pull-request-available
>
> Suppose that a user's Table definitions come in with a tag on certain columns
> that indicates the the column is required to have a filter on it for all
> incoming queries.
>
> I would like to add support to validate that incoming queries satisfies the
> table condition.
> If all of the table's specified fields has a filter on it (present in a WHERE
> or HAVING clause for the query), then it will not error.
>
> ex.
> {{EMP}} table specifies that {{EMPNO}} requires a filter
>
> {{select * from emp where empno = 1}} -> No error
> {{select * from emp where ename = 'bob' -> Error}}
> {{select * from emp -> Error}}
>
> The validation would occur after the namespace validation in
> {{SqlValidatorImpl}} as a separate pass.
>
> I am envisioning that the full filter validation algorithm will form a couple
> of key steps
> * Scanning the catalog/schema/tables and determining which fields are
> tagged to always require filters
> * A pass through the SQL statement to see if a certain field needs to be
> filtered multiple times (potentially for CTE, joins? needs further
> investigation)
> * A pass through the SQL statement to discover filters on the statement
>
> In determining whether a {{WHERE}} or {{HAVING}} clause contains a certain
> field identifier, there will need to be a helper visitor for WHERE or HAVING
> SqlNodes to collect all of the SqlIdentifiers that could be nested within the
> {{operandList}}
>
> Special considerations:
> * joins
> * CTEs
> * subqueries
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6281) Add test infrastructure to ensure that Calcite is consistent with the SQL dialects it is trying to emulate
Julian Hyde created CALCITE-6281:
Summary: Add test infrastructure to ensure that Calcite is
consistent with the SQL dialects it is trying to emulate
Key: CALCITE-6281
URL: https://issues.apache.org/jira/browse/CALCITE-6281
Project: Calcite
Issue Type: Improvement
Reporter: Julian Hyde
Add test infrastructure to ensure that Calcite is consistent with the SQL
dialects it is trying to emulate.
Calcite is able to emulate other SQL dialects. For example, it can pretend to
be MySQL by setting "lib=mysql, lex=mysql, conformance=mysql_5". In that mode,
the user would expect there to be a SUBSTR function, and that {{SUBSTR('abc'
FROM 0)}} would [return the empty string, the same as
MySQL|https://github.com/apache/calcite/blob/022d878a73dec796bb72743804a6dded7c239bd3/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java#L9428].
(On Postgres, the same expression returns 'abc'.)
How do we trust that MySQL does indeed have that behavior? How do we find out
if, in a later release, the MySQL team decides to change the behavior. That is
the goal of this case.
We propose to add tests that run expressions on both MySQL and
Calcite-pretending-to-be-MySQL, and ensure that the result is the same on both.
We use [testContainers|https://github.com/testcontainers/testcontainers-java]
to instantiate, via Docker, and instance of MySQL.
See discussion [Ensuring that Calcite is consistent with other SQL
systems|https://lists.apache.org/thread/mxy9p6cy8sssf1sq3gz7zkzm6hsysns5].
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6275) Parser for data types ignores element nullability in collections
[
https://issues.apache.org/jira/browse/CALCITE-6275?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17820199#comment-17820199
]
Julian Hyde commented on CALCITE-6275:
--
In standard SQL, I don't think that NOT NULL is strictly part of the data type
(unlike say Kotlin). It is a constraint on the column that uses that data type.
These days, most of us agree that Kotlin got it right but prepare for it to get
messy if try to impose that order on SQL.
I also recall that a SQL record {{(a, b)}} is deemed to be NULL if and only if
a and b are both null.
> Parser for data types ignores element nullability in collections
>
>
> Key: CALCITE-6275
> URL: https://issues.apache.org/jira/browse/CALCITE-6275
> Project: Calcite
> Issue Type: Bug
> Components: core, server
>Affects Versions: 1.36.0
>Reporter: Mihai Budiu
>Priority: Major
> Labels: pull-request-available
>
> The parser (Parser.jj) has this production rule for DataType:
> {code}
> // Type name with optional scale and precision.
> SqlDataTypeSpec DataType() :
> {
> SqlTypeNameSpec typeName;
> final Span s;
> }
> {
> typeName = TypeName() {
> s = Span.of(typeName.getParserPos());
> }
> (
> typeName = CollectionsTypeName(typeName)
> )*
> {
> return new SqlDataTypeSpec(typeName,
> s.add(typeName.getParserPos()).pos());
> }
> }
> {code}
> Note that there is no way to specify the nullability for the elements of a
> collection, they are always assumed to be non-null. This is most pertinent
> for the server component, where in DDL one cannot specify a table column of
> type INTEGER ARRAY; one always gets an INTEGER NOT NULL ARRAY instead.
> But note that SqlCollectionTypeNameSpec cannot even represent the nullability
> of the elements' type, it takes a SqlTypeNameSpec instead of a
> SqlDataTypeSpec.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5638) Assertion Failure during planning correlated query with orderby
[
https://issues.apache.org/jira/browse/CALCITE-5638?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17820172#comment-17820172
]
Julian Hyde commented on CALCITE-5638:
--
The problem still occurs if I remove the {{{}ORDER BY{}}}. It's worth doing
some work to find the minimal test case.
> Assertion Failure during planning correlated query with orderby
> ---
>
> Key: CALCITE-5638
> URL: https://issues.apache.org/jira/browse/CALCITE-5638
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.34.0, 1.35.0
>Reporter: Mihai Budiu
>Assignee: Hanumath Rao Maduri
>Priority: Major
>
> Here is a test which fails if pasted in jdbcTest.java:
> {code}
> @Test void testCrash() throws Exception {
> String hsqldbMemUrl = "jdbc:hsqldb:mem:.";
> Connection baseConnection = DriverManager.getConnection(hsqldbMemUrl);
> Statement baseStmt = baseConnection.createStatement();
> baseStmt.execute("CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d
> INTEGER, e INTEGER)");
> baseStmt.close();
> baseConnection.commit();
> Properties info = new Properties();
> info.put("model",
> "inline:"
> + "{\n"
> + " version: '1.0',\n"
> + " defaultSchema: 'BASEJDBC',\n"
> + " schemas: [\n"
> + " {\n"
> + " type: 'jdbc',\n"
> + " name: 'BASEJDBC',\n"
> + " jdbcDriver: '" + jdbcDriver.class.getName() + "',\n"
> + " jdbcUrl: '" + hsqldbMemUrl + "',\n"
> + " jdbcCatalog: null,\n"
> + " jdbcSchema: null\n"
> + " }\n"
> + " ]\n"
> + "}");
> Connection calciteConnection =
> DriverManager.getConnection("jdbc:calcite:", info);
> String statement = "SELECT b, d, (SELECT count(*) FROM t1 AS x WHERE
> x.c>t1.c AND x.dc ORDER BY 1,2,3";
> ResultSet rs =
> calciteConnection.prepareStatement(statement).executeQuery();
> rs.close();
> calciteConnection.close();
> }
> {code}
> The stack trace is:
> {noformat}
> Required columns \{2, 3} not subset of left columns \{0, 1, 2}
> java.lang.AssertionError: Required columns \{2, 3} not subset of left columns
> \{0, 1, 2}
> at org.apache.calcite.util.Litmus.lambda$static$0(Litmus.java:31)
> at org.apache.calcite.util.Litmus.check(Litmus.java:76)
> at org.apache.calcite.rel.core.Correlate.isValid(Correlate.java:145)
> at org.apache.calcite.rel.core.Correlate.(Correlate.java:109)
> at
> org.apache.calcite.rel.logical.LogicalCorrelate.(LogicalCorrelate.java:72)
> at
> org.apache.calcite.rel.logical.LogicalCorrelate.create(LogicalCorrelate.java:115)
> at
> org.apache.calcite.rel.core.RelFactories$CorrelateFactoryImpl.createCorrelate(RelFactories.java:440)
> at org.apache.calcite.tools.RelBuilder.join(RelBuilder.java:2865)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule.rewriteScalarQuery(SubQueryRemoveRule.java:136)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule.apply(SubQueryRemoveRule.java:94)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule.matchProject(SubQueryRemoveRule.java:828)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule.access$200(SubQueryRemoveRule.java:75)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule$Config.lambda$static$0(SubQueryRemoveRule.java:906)
> at
> org.apache.calcite.rel.rules.SubQueryRemoveRule.onMatch(SubQueryRemoveRule.java:86)
> at
> org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:337)
> at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:556)
> at org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:420)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeRuleCollection(HepPlanner.java:286)
> at
> org.apache.calcite.plan.hep.HepInstruction$RuleCollection$State.execute(HepInstruction.java:105)
> at
> org.apache.calcite.plan.hep.HepPlanner.lambda$executeProgram$0(HepPlanner.java:211)
> at com.google.common.collect.ImmutableList.forEach(ImmutableList.java:422)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:210)
> at
> org.apache.calcite.plan.hep.HepProgram$State.execute(HepProgram.java:118)
> at
> org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:205)
> at org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:191)
> at org.apache.calcite.tools.Programs.lambda$of$0(Programs.java:177)
> at
> org.apache.calcite.tools.Programs$SequenceProgram.run(Programs.java:337)
> at org.apache.calcite.prepare.Prepare.optimize(Prepare.java:177)
> at
[jira] [Commented] (CALCITE-6279) Use Null to replace the results of some Math functions that return NaN.
[
https://issues.apache.org/jira/browse/CALCITE-6279?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17820166#comment-17820166
]
Julian Hyde commented on CALCITE-6279:
--
Also, do not talk about what Java does. Calcite users do not care what language
it is implemented in.
Distinguishing specification from implementation is a key skill for an
engineer.
> Use Null to replace the results of some Math functions that return NaN.
> ---
>
> Key: CALCITE-6279
> URL: https://issues.apache.org/jira/browse/CALCITE-6279
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Affects Versions: 1.36.0
>Reporter: Caican Cai
>Priority: Minor
> Fix For: 1.37.0
>
>
> Among Calcite's Math functions, there are some functions that return double
> types. They are calculated using Java's Math method in SqlFunctions, so when
> the result is empty, NaN will be returned, but according to SQL rules, NULL
> should be returned.
> e.g
> {code:java}
> f.checkScalarApprox("sqrt(-1)", "DOUBLE NOT NULL",
> "NaN"); {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
