[jira] [Updated] (CALCITE-6143) RelBuilder.aggregate pruneInputOfAggregate+dedupAggregateCalls blocks can be wrong if both trigger
[
https://issues.apache.org/jira/browse/CALCITE-6143?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-6143:
---
Description:
{code:java}
// pseudo-code repro case
b.scan("table")
.project(b.field(0), b.field(1), b.field(2))
.aggregate(b.groupKey(ImmutableBitSet.of(1)), b.aggregateCall(SUM,
b.field(2)), b.aggregatecall(SUM, b.field(2))){code}
^^ easier to see the change that introduced the bug in some ways than think
about a repro, basically
[https://github.com/apache/calcite/commit/de4631f62cc06b22199c1c14b687ea8a06ea06ec#diff-3be99bddc7edf13dc8198da7425d7e97c97237e3561c263fd74903b4a42d8cd9R2434]
refactored "groupSet" to a 'final' var, so introduced a 'groupSet2' to handle
permutations to it, but missed the last use at the bottom of the method for
dedupAggregate. So if we get to that point,
[https://github.com/apache/calcite/blame/379f41d3be465992328d5659ea62b8355e0399d1/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2511]
the aggregate gets re-built with the original groupSet, ignoring any
permutation that happened to groupSet2 in the aggregate input pruning.
Obviously one can workaround by turning one of these configs off, but they're
both on by default so this is a bit of a landmine in Calcite 1.35, since it's
unlikely to be caught by a test until someone writes a specific sort of query
in production.
was:
{code:java}
// pseudo-code repro case
b.scan("table")
.project(b.field(0), b.field(1), b.field(2))
.aggregate(b.groupKey(ImmutableBitSet.of(1)), b.aggregateCall(SUM,
b.field(2)), b.aggregatecall(SUM, b.field(2))){code}
^^ easier to see the change that introduced the bug in some ways than think
about a repro, basically
[https://github.com/apache/calcite/commit/de4631f62cc06b22199c1c14b687ea8a06ea06ec#diff-3be99bddc7edf13dc8198da7425d7e97c97237e3561c263fd74903b4a42d8cd9R2434]
refactored "groupSet" to a 'final' var, so introduced a 'groupSet2' to handle
permutations to it, but missed the last use at the bottom of the method for
dedupAggregate. So if we get to that point,
[https://github.com/apache/calcite/blame/379f41d3be465992328d5659ea62b8355e0399d1/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2511]
the aggregate gets re-built with the original groupSet, ignoring any
permutation that happened to groupSet2 in the aggregate pruning.
Obviously one can workaround by turning one of these configs off, but they're
both on by default so this is a bit of a landmine in Calcite 1.35, since it's
unlikely to be caught by a test until someone writes a specific sort of query
in production.
> RelBuilder.aggregate pruneInputOfAggregate+dedupAggregateCalls blocks can be
> wrong if both trigger
> --
>
> Key: CALCITE-6143
> URL: https://issues.apache.org/jira/browse/CALCITE-6143
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> {code:java}
> // pseudo-code repro case
> b.scan("table")
> .project(b.field(0), b.field(1), b.field(2))
> .aggregate(b.groupKey(ImmutableBitSet.of(1)), b.aggregateCall(SUM,
> b.field(2)), b.aggregatecall(SUM, b.field(2))){code}
> ^^ easier to see the change that introduced the bug in some ways than think
> about a repro, basically
> [https://github.com/apache/calcite/commit/de4631f62cc06b22199c1c14b687ea8a06ea06ec#diff-3be99bddc7edf13dc8198da7425d7e97c97237e3561c263fd74903b4a42d8cd9R2434]
> refactored "groupSet" to a 'final' var, so introduced a 'groupSet2' to
> handle permutations to it, but missed the last use at the bottom of the
> method for dedupAggregate. So if we get to that point,
> [https://github.com/apache/calcite/blame/379f41d3be465992328d5659ea62b8355e0399d1/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2511]
> the aggregate gets re-built with the original groupSet, ignoring any
> permutation that happened to groupSet2 in the aggregate input pruning.
> Obviously one can workaround by turning one of these configs off, but they're
> both on by default so this is a bit of a landmine in Calcite 1.35, since it's
> unlikely to be caught by a test until someone writes a specific sort of query
> in production.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-6143) RelBuilder.aggregate pruneInputOfAggregate+dedupAggregateCalls blocks can be wrong if both trigger
[
https://issues.apache.org/jira/browse/CALCITE-6143?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-6143:
---
Description:
{code:java}
// pseudo-code repro case
b.scan("table")
.project(b.field(0), b.field(1), b.field(2))
.aggregate(b.groupKey(ImmutableBitSet.of(1)), b.aggregateCall(SUM,
b.field(2)), b.aggregatecall(SUM, b.field(2))){code}
^^ easier to see the change that introduced the bug in some ways than think
about a repro, basically
[https://github.com/apache/calcite/commit/de4631f62cc06b22199c1c14b687ea8a06ea06ec#diff-3be99bddc7edf13dc8198da7425d7e97c97237e3561c263fd74903b4a42d8cd9R2434]
refactored "groupSet" to a 'final' var, so introduced a 'groupSet2' to handle
permutations to it, but missed the last use at the bottom of the method for
dedupAggregate. So if we get to that point,
[https://github.com/apache/calcite/blame/379f41d3be465992328d5659ea62b8355e0399d1/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2511]
the aggregate gets re-built with the original groupSet, ignoring any
permutation that happened to groupSet2 in the aggregate pruning.
Obviously one can workaround by turning one of these configs off, but they're
both on by default so this is a bit of a landmine in Calcite 1.35, since it's
unlikely to be caught by a test until someone writes a specific sort of query
in production.
was:
{code:java}
// pseudo-code repro case
b.scan("table")
.project(b.field(0), b.field(1), b.field(2))
.aggregate(b.groupKey(ImmutableBitSet.of(1)), b.aggregateCall(SUM,
b.field(2)), b.aggregatecall(SUM, b.field(2))){code}
^^ easier to see the change that introduced the bug in some ways than think
about a repro, basically
[https://github.com/apache/calcite/commit/de4631f62cc06b22199c1c14b687ea8a06ea06ec#diff-3be99bddc7edf13dc8198da7425d7e97c97237e3561c263fd74903b4a42d8cd9R2434]
refactored "groupSet" to a 'final' var, but missed the last use at the bottom
of the method for dedupAggregate. So if we get to that point,
[https://github.com/apache/calcite/blame/379f41d3be465992328d5659ea62b8355e0399d1/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2511]
the aggregate gets re-built with the original groupSet, ignoring any
permutation that happened to groupSet2 in the aggregate pruning.
Obviously one can workaround by turning one of these configs off, but they're
both on by default so this is a bit of a landmine in Calcite 1.35, since it's
unlikely to be caught by a test until someone writes a specific sort of query
in production.
> RelBuilder.aggregate pruneInputOfAggregate+dedupAggregateCalls blocks can be
> wrong if both trigger
> --
>
> Key: CALCITE-6143
> URL: https://issues.apache.org/jira/browse/CALCITE-6143
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> {code:java}
> // pseudo-code repro case
> b.scan("table")
> .project(b.field(0), b.field(1), b.field(2))
> .aggregate(b.groupKey(ImmutableBitSet.of(1)), b.aggregateCall(SUM,
> b.field(2)), b.aggregatecall(SUM, b.field(2))){code}
> ^^ easier to see the change that introduced the bug in some ways than think
> about a repro, basically
> [https://github.com/apache/calcite/commit/de4631f62cc06b22199c1c14b687ea8a06ea06ec#diff-3be99bddc7edf13dc8198da7425d7e97c97237e3561c263fd74903b4a42d8cd9R2434]
> refactored "groupSet" to a 'final' var, so introduced a 'groupSet2' to
> handle permutations to it, but missed the last use at the bottom of the
> method for dedupAggregate. So if we get to that point,
> [https://github.com/apache/calcite/blame/379f41d3be465992328d5659ea62b8355e0399d1/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2511]
> the aggregate gets re-built with the original groupSet, ignoring any
> permutation that happened to groupSet2 in the aggregate pruning.
> Obviously one can workaround by turning one of these configs off, but they're
> both on by default so this is a bit of a landmine in Calcite 1.35, since it's
> unlikely to be caught by a test until someone writes a specific sort of query
> in production.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6143) RelBuilder.aggregate pruneInputOfAggregate+dedupAggregateCalls blocks can be wrong if both trigger
[
https://issues.apache.org/jira/browse/CALCITE-6143?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17791376#comment-17791376
]
Steven Talbot commented on CALCITE-6143:
Not sure if my first link is quite jumping to the right point in github, but
here is where groupSet2 is declared
https://github.com/apache/calcite/blame/379f41d3be465992328d5659ea62b8355e0399d1/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2433
> RelBuilder.aggregate pruneInputOfAggregate+dedupAggregateCalls blocks can be
> wrong if both trigger
> --
>
> Key: CALCITE-6143
> URL: https://issues.apache.org/jira/browse/CALCITE-6143
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> {code:java}
> // pseudo-code repro case
> b.scan("table")
> .project(b.field(0), b.field(1), b.field(2))
> .aggregate(b.groupKey(ImmutableBitSet.of(1)), b.aggregateCall(SUM,
> b.field(2)), b.aggregatecall(SUM, b.field(2))){code}
> ^^ easier to see the change that introduced the bug in some ways than think
> about a repro, basically
> [https://github.com/apache/calcite/commit/de4631f62cc06b22199c1c14b687ea8a06ea06ec#diff-3be99bddc7edf13dc8198da7425d7e97c97237e3561c263fd74903b4a42d8cd9R2434]
> refactored "groupSet" to a 'final' var, but missed the last use at the
> bottom of the method for dedupAggregate. So if we get to that point,
> [https://github.com/apache/calcite/blame/379f41d3be465992328d5659ea62b8355e0399d1/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2511]
> the aggregate gets re-built with the original groupSet, ignoring any
> permutation that happened to groupSet2 in the aggregate pruning.
> Obviously one can workaround by turning one of these configs off, but they're
> both on by default so this is a bit of a landmine in Calcite 1.35, since it's
> unlikely to be caught by a test until someone writes a specific sort of query
> in production.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6143) RelBuilder.aggregate pruneInputOfAggregate+dedupAggregateCalls blocks can be wrong if both trigger
Steven Talbot created CALCITE-6143:
--
Summary: RelBuilder.aggregate
pruneInputOfAggregate+dedupAggregateCalls blocks can be wrong if both trigger
Key: CALCITE-6143
URL: https://issues.apache.org/jira/browse/CALCITE-6143
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
{code:java}
// pseudo-code repro case
b.scan("table")
.project(b.field(0), b.field(1), b.field(2))
.aggregate(b.groupKey(ImmutableBitSet.of(1)), b.aggregateCall(SUM,
b.field(2)), b.aggregatecall(SUM, b.field(2))){code}
^^ easier to see the change that introduced the bug in some ways than think
about a repro, basically
[https://github.com/apache/calcite/commit/de4631f62cc06b22199c1c14b687ea8a06ea06ec#diff-3be99bddc7edf13dc8198da7425d7e97c97237e3561c263fd74903b4a42d8cd9R2434]
refactored "groupSet" to a 'final' var, but missed the last use at the bottom
of the method for dedupAggregate. So if we get to that point,
[https://github.com/apache/calcite/blame/379f41d3be465992328d5659ea62b8355e0399d1/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2511]
the aggregate gets re-built with the original groupSet, ignoring any
permutation that happened to groupSet2 in the aggregate pruning.
Obviously one can workaround by turning one of these configs off, but they're
both on by default so this is a bit of a landmine in Calcite 1.35, since it's
unlikely to be caught by a test until someone writes a specific sort of query
in production.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-6086) SqlImplementor does not properly handle SAFE_CAST
[
https://issues.apache.org/jira/browse/CALCITE-6086?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17791371#comment-17791371
]
Steven Talbot commented on CALCITE-6086:
Yep, seem like that is the same, thanks!
> SqlImplementor does not properly handle SAFE_CAST
> -
>
> Key: CALCITE-6086
> URL: https://issues.apache.org/jira/browse/CALCITE-6086
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Assignee: Oliver Lee
>Priority: Major
>
> This block
> [https://github.com/apache/calcite/blob/782d327d24c04e2161102b22f8880204462befd4/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L815]
> needs to fire for the (added in 1.35.0) kind SqlKind.SAFE_CAST, or the
> SAFE_CAST does not work in SQL.
> I could submit a repro if it's not obvious, but it should be as simple as
> something like
> `b.scan("emps").project(b.rexBuilder.makeAbstractCast(b.typeFactory.createSqlType(SqlTypeName.INTEGER),
> b.field(0), true))` and trying to write SQL from that. The second
> SqlDataTypeSpec argument won't get added, and it will fail the assertion
> error about two operands in SqlCastFunction.unparse
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5948) Use explicit casting if element type in ARRAY/MAP does not equal derived component type
[
https://issues.apache.org/jira/browse/CALCITE-5948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17786827#comment-17786827
]
Steven Talbot commented on CALCITE-5948:
[~taoran] thanks for the response! Our use case is we parse user-provided SQL
snippets as dimension/measure definitions and use them to build out SQL queries
against various analytical DBs.
We prefer no auto-CASTs like this for two reasons:
# Confusing/unsightly for users when they view the SQL we generate.
# Prefer to not insist on Calcite's strict typing literally showing up in the
SQL, because ensuring every possible type has the right name/semantics in every
supported analytical DB is fraught. DBs do the type unification for you under
the hood if passed a list of e.g. heterogenous numeric types in SQL.
So not a huge deal, but just a nice to have. I can work around this by
transforming the tree that comes out of parse to a subclass of these operators
that removes this behavior, so I'm not blocked.
> Use explicit casting if element type in ARRAY/MAP does not equal derived
> component type
> ---
>
> Key: CALCITE-5948
> URL: https://issues.apache.org/jira/browse/CALCITE-5948
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> First, we need to reach a consensus to allow types of the same family to
> coexist in multiset such as array and map.
> It means the form like `{*}array(1, cast(2 as tinyint)){*}` is correct(the
> LeastRestrictiveType is Integer). However, this function validate success in
> calcite but it failed in runtime, exception stack is:
> {code:java}
> java.lang.ClassCastException: class java.lang.Byte cannot be cast to class
> 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
> {code}
>
> And `{*}map[1, 1, 2, cast(1 as tinyint)]{*}` is correct but calcite throw
> exception:
> {code:java}
> java.lang.AssertionError: Expected query to throw exception, but it did not;
> query [values (map[1, 1, 2, cast(1 as tinyint)])]; expected [Parameters must
> be of the same type]
> at org.apache.calcite.sql.test.SqlTests.checkEx(SqlTests.java:240)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.assertExceptionIsThrown(AbstractSqlTester.java:111)
> at
> org.apache.calcite.test.SqlOperatorFixtureImpl.checkQueryFails(SqlOperatorFixtureImpl.java:174)
> {code}
>
> std ArrayConstructor.
> {code:java}
> public class SqlArrayValueConstructor extends SqlMultisetValueConstructor {
> public SqlArrayValueConstructor() {
> super("ARRAY", SqlKind.ARRAY_VALUE_CONSTRUCTOR);
> }
> @Override public RelDataType inferReturnType(SqlOperatorBinding opBinding) {
> RelDataType type =
> getComponentType(
> opBinding.getTypeFactory(),
> opBinding.collectOperandTypes());
> --> we need explicit cast here
> requireNonNull(type, "inferred array element type");
> return SqlTypeUtil.createArrayType(
> opBinding.getTypeFactory(), type, false);
> }
> } {code}
> std map constructor:
> {code:java}
> public class SqlMapValueConstructor extends SqlMultisetValueConstructor {
> public SqlMapValueConstructor() {
> super("MAP", SqlKind.MAP_VALUE_CONSTRUCTOR);
> }
> @Override public RelDataType inferReturnType(SqlOperatorBinding opBinding) {
> Pair<@Nullable RelDataType, @Nullable RelDataType> type =
> getComponentTypes(
> opBinding.getTypeFactory(), opBinding.collectOperandTypes());
> --> we need explicit cast here
> return SqlTypeUtil.createMapType(
> opBinding.getTypeFactory(),
> requireNonNull(type.left, "inferred key type"),
>
[jira] [Commented] (CALCITE-5948) Use explicit casting if element type in ARRAY/MAP does not equal derived component type
[
https://issues.apache.org/jira/browse/CALCITE-5948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17786545#comment-17786545
]
Steven Talbot commented on CALCITE-5948:
Looking through the commit, there doesn't seem to be a way to disable this
behavior, right? For our app, we prefer to leave the SQL alone unless
absolutely necessary. There are various hooks in SqlValidator to disable such
rewrites ("withCallRewrite false", or "withTypeCoercionFactory" with
"needToCast" false). But since this fix works by reaching back into the SqlCall
in "inferReturnType" and mutating it outside of the scope of the validator,
none of those configs apply.
I can file an issue if there is indeed no way to disable this. I think this
should be configurable.
> Use explicit casting if element type in ARRAY/MAP does not equal derived
> component type
> ---
>
> Key: CALCITE-5948
> URL: https://issues.apache.org/jira/browse/CALCITE-5948
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.35.0
>Reporter: Ran Tao
>Assignee: Ran Tao
>Priority: Major
> Labels: pull-request-available
> Fix For: 1.36.0
>
>
> First, we need to reach a consensus to allow types of the same family to
> coexist in multiset such as array and map.
> It means the form like `{*}array(1, cast(2 as tinyint)){*}` is correct(the
> LeastRestrictiveType is Integer). However, this function validate success in
> calcite but it failed in runtime, exception stack is:
> {code:java}
> java.lang.ClassCastException: class java.lang.Byte cannot be cast to class
> 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
> {code}
>
> And `{*}map[1, 1, 2, cast(1 as tinyint)]{*}` is correct but calcite throw
> exception:
> {code:java}
> java.lang.AssertionError: Expected query to throw exception, but it did not;
> query [values (map[1, 1, 2, cast(1 as tinyint)])]; expected [Parameters must
> be of the same type]
> at org.apache.calcite.sql.test.SqlTests.checkEx(SqlTests.java:240)
> at
> org.apache.calcite.sql.test.AbstractSqlTester.assertExceptionIsThrown(AbstractSqlTester.java:111)
> at
> org.apache.calcite.test.SqlOperatorFixtureImpl.checkQueryFails(SqlOperatorFixtureImpl.java:174)
> {code}
>
> std ArrayConstructor.
> {code:java}
> public class SqlArrayValueConstructor extends SqlMultisetValueConstructor {
> public SqlArrayValueConstructor() {
> super("ARRAY", SqlKind.ARRAY_VALUE_CONSTRUCTOR);
> }
> @Override public RelDataType inferReturnType(SqlOperatorBinding opBinding) {
> RelDataType type =
> getComponentType(
> opBinding.getTypeFactory(),
> opBinding.collectOperandTypes());
> --> we need explicit cast here
> requireNonNull(type, "inferred array element type");
> return SqlTypeUtil.createArrayType(
> opBinding.getTypeFactory(), type, false);
> }
> } {code}
> std map constructor:
> {code:java}
> public class SqlMapValueConstructor extends SqlMultisetValueConstructor {
> public SqlMapValueConstructor() {
> super("MAP", SqlKind.MAP_VALUE_CONSTRUCTOR);
> }
> @Override public RelDataType inferReturnType(SqlOperatorBinding opBinding) {
> Pair<@Nullable RelDataType, @Nullable RelDataType> type =
> getComponentTypes(
> opBinding.getTypeFactory(), opBinding.collectOperandTypes());
> --> we need explicit cast here
> return SqlTypeUtil.createMapType(
> opBinding.getTypeFactory(),
> requireNonNull(type.left, "inferred key type"),
> requireNonNull(type.right, "inferred value type"),
> false);
> }
> }{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6086) SqlImplementor does not properly handle SAFE_CAST
Steven Talbot created CALCITE-6086:
--
Summary: SqlImplementor does not properly handle SAFE_CAST
Key: CALCITE-6086
URL: https://issues.apache.org/jira/browse/CALCITE-6086
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
This block
[https://github.com/apache/calcite/blob/782d327d24c04e2161102b22f8880204462befd4/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L815]
needs to fire for the (added in 1.35.0) kind SqlKind.SAFE_CAST, or the
SAFE_CAST does not work in SQL.
I could submit a repro if it's not obvious, but it should be as simple as
something like
`b.scan("emps").project(b.rexBuilder.makeAbstractCast(b.typeFactory.createSqlType(SqlTypeName.INTEGER),
b.field(0), true))` and trying to write SQL from that. The second
SqlDataTypeSpec argument won't get added, and it will fail the assertion error
about two operands in SqlCastFunction.unparse
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-6085) RelBuilder.aggregate_ "pruneInputOfAggregate" logic needs to discard unused top of stack
Steven Talbot created CALCITE-6085: -- Summary: RelBuilder.aggregate_ "pruneInputOfAggregate" logic needs to discard unused top of stack Key: CALCITE-6085 URL: https://issues.apache.org/jira/browse/CALCITE-6085 Project: Calcite Issue Type: Bug Reporter: Steven Talbot Minor edge case one that can be turned off with config, but this code [https://github.com/apache/calcite/blob/379f41d3be465992328d5659ea62b8355e0399d1/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2355-L2368] needs to discard the top of the stack. Otherwise, if you're e.g. trying to build things for a UNION, you end up with an unwanted extra frame on the stack that causes all kinds of problems. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Updated] (CALCITE-5856) ARG0_NULLABLE_IF_EMPTY should consider GROUPING SETS (.., ())
[ https://issues.apache.org/jira/browse/CALCITE-5856?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Steven Talbot updated CALCITE-5856: --- Description: Currently, the rule switches off getGroupCount to determine if an aggregation is empty. However, "select sum(1) as s from empty group by grouping sets((a), ())" will return null, but getGroupCount() will return non-zero, so the return type for an operator using ARG0_NULLABLE_IF_EMPTY will incorrectly be marked as non-nullable (if the input was non-nullable). As a practical matter, this mostly matters if you try to rewrite GROUPING SETS to some kind of UNION, at which point the branch of the UNION emulating the "()" grouping set _will_ infer nullable using this strategy, and the type of your rewritten RelNode will no longer match. was: Currently, the rule switches off getGroupCount to determine if an aggregation is empty. However, "select sum(1) as s from empty group by grouping sets((a), ())" will return null, but getGroupCount() will return non-zero, so the return type for an operator using ARG0_NULLABLE_IF_EMPTY will incorrectly be marked as non-nullable (if the input was non-nullable). As a practical matter, this mostly matters if you try to rewrite GROUPING SETS to some kind of UNION, at which point the branch of the UNION on the "()" grouping set _will_ infer nullable using this strategy, and the type of your rewritten RelNode will no longer match. > ARG0_NULLABLE_IF_EMPTY should consider GROUPING SETS (.., ()) > - > > Key: CALCITE-5856 > URL: https://issues.apache.org/jira/browse/CALCITE-5856 > Project: Calcite > Issue Type: Bug >Reporter: Steven Talbot >Priority: Major > > Currently, the rule switches off getGroupCount to determine if an aggregation > is empty. However, "select sum(1) as s from empty group by grouping sets((a), > ())" will return null, but getGroupCount() will return non-zero, so the > return type for an operator using ARG0_NULLABLE_IF_EMPTY will incorrectly be > marked as non-nullable (if the input was non-nullable). > > As a practical matter, this mostly matters if you try to rewrite GROUPING > SETS to some kind of UNION, at which point the branch of the UNION emulating > the "()" grouping set _will_ infer nullable using this strategy, and the type > of your rewritten RelNode will no longer match. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5856) ARG0_NULLABLE_IF_EMPTY should consider GROUPING SETS (.., ())
Steven Talbot created CALCITE-5856: -- Summary: ARG0_NULLABLE_IF_EMPTY should consider GROUPING SETS (.., ()) Key: CALCITE-5856 URL: https://issues.apache.org/jira/browse/CALCITE-5856 Project: Calcite Issue Type: Bug Reporter: Steven Talbot Currently, the rule switches off getGroupCount to determine if an aggregation is empty. However, "select sum(1) as s from empty group by grouping sets((a), ())" will return null, but getGroupCount() will return non-zero, so the return type for an operator using ARG0_NULLABLE_IF_EMPTY will incorrectly be marked as non-nullable (if the input was non-nullable). As a practical matter, this mostly matters if you try to rewrite GROUPING SETS to some kind of UNION, at which point the branch of the UNION on the "()" grouping set _will_ infer nullable using this strategy, and the type of your rewritten RelNode will no longer match. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Created] (CALCITE-5846) AggregateExpandWithinDistinctRule drops filters on non-distinct aggCalls
Steven Talbot created CALCITE-5846: -- Summary: AggregateExpandWithinDistinctRule drops filters on non-distinct aggCalls Key: CALCITE-5846 URL: https://issues.apache.org/jira/browse/CALCITE-5846 Project: Calcite Issue Type: Bug Reporter: Steven Talbot This line [https://github.com/apache/calcite/blob/2dba40e7a0a5651eac5a30d9e0a72f178bd9bff2/core/src/main/java/org/apache/calcite/rel/rules/AggregateExpandWithinDistinctRule.java#L346-L348] drops any such filterArg. Related to CALCITE-4726. When the rule was first introduced any such aggregates were blocked, but that change lets them through. -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-5696) Support trailing comma in SELECT list for Babel parser (like BigQuery)
[
https://issues.apache.org/jira/browse/CALCITE-5696?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17721556#comment-17721556
]
Steven Talbot commented on CALCITE-5696:
Testing quickly, it would seem the answer is "no" for all four of those. And
the doc only mentions SELECT. Guess they just thought it was only valuable in
the SELECT clause?
> Support trailing comma in SELECT list for Babel parser (like BigQuery)
> --
>
> Key: CALCITE-5696
> URL: https://issues.apache.org/jira/browse/CALCITE-5696
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> {code:java}
> select some_col, from my_table{code}
> works in BigQuery but does not parse in the babel parser. Probably no harm in
> just making the babel parser able to parse that in general. But this is
> needed for full support for Google standard SQL
> https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#trailing_commas
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5696) Support trailing comma in SELECT list for Babel parser (like BigQuery)
Steven Talbot created CALCITE-5696:
--
Summary: Support trailing comma in SELECT list for Babel parser
(like BigQuery)
Key: CALCITE-5696
URL: https://issues.apache.org/jira/browse/CALCITE-5696
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
{code:java}
select some_col, from my_table{code}
works in BigQuery but does not parse in the babel parser. Probably no harm in
just making the babel parser able to parse that in general. But this is needed
for full support for Google standard SQL
https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#trailing_commas
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5330) TIMESTAMPDIFF call cannot work with RelJson
[
https://issues.apache.org/jira/browse/CALCITE-5330?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-5330:
---
Description:
The TIMESTAMPDIFF function parses out with TimeUnit, rather than TimeUnitRange.
Due to
[https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampDiffFunction.java#L62,]
you can't even workaround by subbing in TimeUnitRange after parse.
TimeUnit is incompatible with JSON serialization in RelJson, which uses
RelEnumTypes to serialize enum values, and that only has TimeUnitRange
[https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/rel/externalize/RelEnumTypes.java#L79]
So attempting to serialize you get
{noformat}
cannot serialize enum value to JSON:
org.apache.calcite.avatica.util.TimeUnit.MINUTE
java.lang.AssertionError: cannot serialize enum value to JSON:
org.apache.calcite.avatica.util.TimeUnit.MINUTE
at
org.apache.calcite.rel.externalize.RelEnumTypes.fromEnum(RelEnumTypes.java:102)
at
org.apache.calcite.rel.externalize.RelEnumTypes.fromEnum(RelEnumTypes.java:94)
at org.apache.calcite.rel.externalize.RelJson.toJson(RelJson.java:491)
at org.apache.calcite.rel.externalize.RelJson.toJson(RelJson.java:517)
at org.apache.calcite.rel.externalize.RelJson.toJson(RelJson.java:390)
at org.apache.calcite.rel.externalize.RelJson.toJson(RelJson.java:402)
at
org.apache.calcite.rel.externalize.RelJsonWriter.put(RelJsonWriter.java:90)
at
org.apache.calcite.rel.externalize.RelJsonWriter.explain_(RelJsonWriter.java:73){noformat}
was:
The TIMESTAMPDIFF function parses out with TimeUnit, rather than TimeUnitRange.
Due to
[https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampDiffFunction.java#L62,]
you can't even workaround by subbing in TimeUnitRange after parse.
TimeUnit is incompatible with JSON serialization in RelJson, which uses
RelEnumTypes to serialize enum values, and that only has TimeUnitRange
[https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/rel/externalize/RelEnumTypes.java#L79]
So attempting to serialize you get
{noformat}
cannot serialize enum value to JSON:
org.apache.calcite.avatica.util.TimeUnit.MINUTE
java.lang.AssertionError: cannot serialize enum value to JSON:
org.apache.calcite.avatica.util.TimeUnit.MINUTE
at
org.apache.calcite.rel.externalize.RelEnumTypes.fromEnum(RelEnumTypes.java:102)
at
org.apache.calcite.rel.externalize.RelEnumTypes.fromEnum(RelEnumTypes.java:94)
at org.apache.calcite.rel.externalize.RelJson.toJson(RelJson.java:491)
at org.apache.calcite.rel.externalize.RelJson.toJson(RelJson.java:517)
at org.apache.calcite.rel.externalize.RelJson.toJson(RelJson.java:390)
at org.apache.calcite.rel.externalize.RelJson.toJson(RelJson.java:402)
at
org.apache.calcite.rel.externalize.RelJsonWriter.put(RelJsonWriter.java:90)
at
org.apache.calcite.rel.externalize.RelJsonWriter.explain_(RelJsonWriter.java:73){noformat}
> TIMESTAMPDIFF call cannot work with RelJson
> ---
>
> Key: CALCITE-5330
> URL: https://issues.apache.org/jira/browse/CALCITE-5330
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> The TIMESTAMPDIFF function parses out with TimeUnit, rather than
> TimeUnitRange. Due to
> [https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampDiffFunction.java#L62,]
> you can't even workaround by subbing in TimeUnitRange after parse.
> TimeUnit is incompatible with JSON serialization in RelJson, which uses
> RelEnumTypes to serialize enum values, and that only has TimeUnitRange
> [https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/rel/externalize/RelEnumTypes.java#L79]
> So attempting to serialize you get
> {noformat}
> cannot serialize enum value to JSON:
> org.apache.calcite.avatica.util.TimeUnit.MINUTE
> java.lang.AssertionError: cannot serialize enum value to JSON:
> org.apache.calcite.avatica.util.TimeUnit.MINUTE
> at
> org.apache.calcite.rel.externalize.RelEnumTypes.fromEnum(RelEnumTypes.java:102)
> at
> org.apache.calcite.rel.externalize.RelEnumTypes.fromEnum(RelEnumTypes.java:94)
> at org.apache.calcite.rel.externalize.RelJson.toJson(RelJson.java:491)
> at org.apache.calcite.rel.externalize.RelJson.toJson(RelJson.java:517)
> at org.apache.calcite.rel.externalize.RelJson.toJson(RelJson.java:390)
> at
[jira] [Created] (CALCITE-5330) TIMESTAMPDIFF call cannot work with RelJson
Steven Talbot created CALCITE-5330:
--
Summary: TIMESTAMPDIFF call cannot work with RelJson
Key: CALCITE-5330
URL: https://issues.apache.org/jira/browse/CALCITE-5330
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
The TIMESTAMPDIFF function parses out with TimeUnit, rather than TimeUnitRange.
Due to
[https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampDiffFunction.java#L62,]
you can't even workaround by subbing in TimeUnitRange after parse.
TimeUnit is incompatible with JSON serialization in RelJson, which uses
RelEnumTypes to serialize enum values, and that only has TimeUnitRange
[https://github.com/apache/calcite/blob/2c30a56158cdd351d35725006bc1f76bb6aac75b/core/src/main/java/org/apache/calcite/rel/externalize/RelEnumTypes.java#L79]
So attempting to serialize you get
{noformat}
cannot serialize enum value to JSON:
org.apache.calcite.avatica.util.TimeUnit.MINUTE
java.lang.AssertionError: cannot serialize enum value to JSON:
org.apache.calcite.avatica.util.TimeUnit.MINUTE
at
org.apache.calcite.rel.externalize.RelEnumTypes.fromEnum(RelEnumTypes.java:102)
at
org.apache.calcite.rel.externalize.RelEnumTypes.fromEnum(RelEnumTypes.java:94)
at org.apache.calcite.rel.externalize.RelJson.toJson(RelJson.java:491)
at org.apache.calcite.rel.externalize.RelJson.toJson(RelJson.java:517)
at org.apache.calcite.rel.externalize.RelJson.toJson(RelJson.java:390)
at org.apache.calcite.rel.externalize.RelJson.toJson(RelJson.java:402)
at
org.apache.calcite.rel.externalize.RelJsonWriter.put(RelJsonWriter.java:90)
at
org.apache.calcite.rel.externalize.RelJsonWriter.explain_(RelJsonWriter.java:73){noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Closed] (CALCITE-4631) sql->rel->sql bug with unparsing timestampdiff
[
https://issues.apache.org/jira/browse/CALCITE-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot closed CALCITE-4631.
--
Resolution: Duplicate
Looks like I missed this is actually a dup
> sql->rel->sql bug with unparsing timestampdiff
> --
>
> Key: CALCITE-4631
> URL: https://issues.apache.org/jira/browse/CALCITE-4631
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Steven Talbot
>Priority: Major
>
> e.g. the following test in RelToSqlConverter.java demonstrates the end-to-end
> problem:
>
> {code:java}
> @Test void testTimestampDiff() {
> final String query = "select timestampdiff(day, a.\"hire_date\",
> b.\"hire_date\") "
> + "from \"employee\" a join \"employee\" b using (\"employee_id\")";
> final String expected = "TBD, can't get it to generate right now";
> sql(query).ok(expected);
> }{code}
> throws, top of stack:
> {noformat}
> class org.apache.calcite.sql.SqlSyntax$7: SPECIAL
> java.lang.UnsupportedOperationException: class
> org.apache.calcite.sql.SqlSyntax$7: SPECIAL
> at org.apache.calcite.util.Util.needToImplement(Util.java:1101)
> at org.apache.calcite.sql.SqlSyntax$7.unparse(SqlSyntax.java:129)
> at org.apache.calcite.sql.SqlOperator.unparse(SqlOperator.java:383)
> at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:455)
> at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:123)
> at org.apache.calcite.sql.SqlUtil.unparseBinarySyntax(SqlUtil.java:425)
> at org.apache.calcite.sql.SqlSyntax$4.unparse(SqlSyntax.java:78)
> at org.apache.calcite.sql.SqlOperator.unparse(SqlOperator.java:383)
> at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:455)
> at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:126)
> at
> org.apache.calcite.sql.fun.SqlCastFunction.unparse(SqlCastFunction.java:181)
> at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:455)
> at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:126)
> at
> org.apache.calcite.sql.pretty.SqlPrettyWriter$FrameImpl.list2(SqlPrettyWriter.java:1304)
> at
> org.apache.calcite.sql.pretty.SqlPrettyWriter$FrameImpl.list(SqlPrettyWriter.java:1284)
> at
> org.apache.calcite.sql.pretty.SqlPrettyWriter.list(SqlPrettyWriter.java:1081)
> at
> org.apache.calcite.sql.SqlSelectOperator.unparse(SqlSelectOperator.java:154)
> at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:455)
> at org.apache.calcite.sql.SqlSelect.unparse(SqlSelect.java:261)
> at org.apache.calcite.sql.SqlNode.toSqlString(SqlNode.java:156)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.toSql(RelToSqlConverterTest.java:216){noformat}
> From the debugger, it looks like a SqlKind.REINTERPRET operator gets
> generated by the SqlToRelConverter, and nothing knows how to handle that
> unparsing to SQL.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5311) SqlNode.parserPosition missing closing ')' for expression subquery
[
https://issues.apache.org/jira/browse/CALCITE-5311?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17613782#comment-17613782
]
Steven Talbot commented on CALCITE-5311:
The best fix I can think of is to move that special case up into the
"createCall" method, but not sure if that would have other implications.
> SqlNode.parserPosition missing closing ')' for expression subquery
> --
>
> Key: CALCITE-5311
> URL: https://issues.apache.org/jira/browse/CALCITE-5311
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> Here's an obscure one!
> Background: I'm trying to use the values of parserPos to recover the original
> slices of SQL corresponding to various SQL entities.
> When we parse a statement like
>
> {code:java}
> SELECT a IN (SELECT a FROM c WHERE d) FROM t{code}
> The parsed SqlNode corresponding to "a IN (SELECT a FROM c WHERE d)" stops at
> the index of "d", not the ")".
>
> I am almost sure this is due to this very original special case code in the
> parser:
> [https://github.com/apache/calcite/blame/a505b25eacc473c6ec0ef8abd40c1ccae86297b6/core/src/main/codegen/templates/Parser.jj#L3602]
> The parser drops the "list" of the single parenthesized query in favor of
> just the query, which completely makes sense, but in doing so it discards the
> parserPos of the list, which includes the index of ")", for the parserPos of
> the query, which does not. Then, when the parserPositions of the arguments to
> the IN are added together, the final parserPosition does not include the ')'.
> Test is perhaps easier to see:
>
> {code:java}
> @Test
> void testSubqueryParserPosParse() throws SqlParseException {
> String sql = "SELECT a IN (SELECT a FROM c WHERE d) FROM t";
> SqlNode parsed = SqlParser.create(sql).parseQuery();
> SqlCall inCall = (SqlCall) ((SqlSelect) parsed).getSelectList().get(0);
> assertEquals("a IN (SELECT a FROM c WHERE d)",
> sql.substring(
> inCall.getParserPosition().getColumnNum() - 1, // pos is 1-indexed
> inCall.getParserPosition().getEndColumnNum() // but inclusive
> ));
> } {code}
> {noformat}
> org.opentest4j.AssertionFailedError:
> Expected :a IN (SELECT a FROM c WHERE d)
> Actual :a IN (SELECT a FROM c WHERE d
> {noformat}
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5311) SqlNode.parserPosition missing closing ')' for expression subquery
Steven Talbot created CALCITE-5311:
--
Summary: SqlNode.parserPosition missing closing ')' for expression
subquery
Key: CALCITE-5311
URL: https://issues.apache.org/jira/browse/CALCITE-5311
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
Here's an obscure one!
Background: I'm trying to use the values of parserPos to recover the original
slices of SQL corresponding to various SQL entities.
When we parse a statement like
{code:java}
SELECT a IN (SELECT a FROM c WHERE d) FROM t{code}
The parsed SqlNode corresponding to "a IN (SELECT a FROM c WHERE d)" stops at
the index of "d", not the ")".
I am almost sure this is due to this very original special case code in the
parser:
[https://github.com/apache/calcite/blame/a505b25eacc473c6ec0ef8abd40c1ccae86297b6/core/src/main/codegen/templates/Parser.jj#L3602]
The parser drops the "list" of the single parenthesized query in favor of just
the query, which completely makes sense, but in doing so it discards the
parserPos of the list, which includes the index of ")", for the parserPos of
the query, which does not. Then, when the parserPositions of the arguments to
the IN are added together, the final parserPosition does not include the ')'.
Test is perhaps easier to see:
{code:java}
@Test
void testSubqueryParserPosParse() throws SqlParseException {
String sql = "SELECT a IN (SELECT a FROM c WHERE d) FROM t";
SqlNode parsed = SqlParser.create(sql).parseQuery();
SqlCall inCall = (SqlCall) ((SqlSelect) parsed).getSelectList().get(0);
assertEquals("a IN (SELECT a FROM c WHERE d)",
sql.substring(
inCall.getParserPosition().getColumnNum() - 1, // pos is 1-indexed
inCall.getParserPosition().getEndColumnNum() // but inclusive
));
} {code}
{noformat}
org.opentest4j.AssertionFailedError:
Expected :a IN (SELECT a FROM c WHERE d)
Actual :a IN (SELECT a FROM c WHERE d
{noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5254) Infinite Loop in SubstitutionVisitor.go
[
https://issues.apache.org/jira/browse/CALCITE-5254?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17600064#comment-17600064
]
Steven Talbot commented on CALCITE-5254:
Ah, could be ultimately that. I’m unsure about what responsibilities each
rule has in terms of guarantees it makes about how it will or won’t touch
the mutable rels.
Bottom line if the fault can lie in a rule, and a rule can be passed by
caller, I do think this code may still want the safety cap on max
iterations, even it hitting the cap raises an exception.
> Infinite Loop in SubstitutionVisitor.go
> ---
>
> Key: CALCITE-5254
> URL: https://issues.apache.org/jira/browse/CALCITE-5254
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> Happens when you have a materialization "target" of an Aggregate/Scan
> attempting to substitute for an a "query" with a matching Scan.
> The following test should repro (in CustomMaterializedViewRecognitionRuleTest)
> {code:java}
> @Test void testNoInifiniteLoopOnAggregateScanMaterialization() {
> final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
> rootSchema.add("mv1", new AbstractTable() {
> @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) {
> return typeFactory.builder()
> .add("count", SqlTypeName.INTEGER)
> .build();
> }
> });
> final Frameworks.ConfigBuilder config = Frameworks.newConfigBuilder()
> .parserConfig(SqlParser.Config.DEFAULT)
> .defaultSchema(
> CalciteAssert.addSchema(rootSchema,
> CalciteAssert.SchemaSpec.SCOTT_WITH_TEMPORAL))
> .traitDefs((List) null);
> final RelBuilder relBuilder = RelBuilder.create(config.build());
> final RelNode query = relBuilder.scan("EMP")
> .project(relBuilder.field("DEPTNO"))
> .aggregate(
> relBuilder.groupKey(0),
> relBuilder.aggregateCall(SqlStdOperatorTable.COUNT)
> )
> .build();
> final RelNode target = relBuilder.scan("EMP")
> .aggregate(relBuilder.groupKey(),
> relBuilder.aggregateCall(SqlStdOperatorTable.COUNT))
> .build();
> final RelNode replacement = relBuilder.scan("mv1").build();
> final RelOptMaterialization relOptMaterialization =
> new RelOptMaterialization(replacement,
> target, null, Lists.newArrayList("mv1"));
> final List>> relOptimized =
> RelOptMaterializations.useMaterializedViews(query,
> ImmutableList.of(relOptMaterialization),
> SubstitutionVisitor.DEFAULT_RULES);
> System.out.println("never get here!");
> } {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5254) Infinite Loop in SubstitutionVisitor.go
[
https://issues.apache.org/jira/browse/CALCITE-5254?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17597961#comment-17597961
]
Steven Talbot commented on CALCITE-5254:
Also, given that the "fix" there relies on the rule to do the right thing to
prevent an infinite loop, and the rules are part of a public interface, where a
caller can specify them, we may want an additional fix where
SubstitutionVisitor.go has some safety cap on the max number of times it will
substitute parts of the query before giving up.
> Infinite Loop in SubstitutionVisitor.go
> ---
>
> Key: CALCITE-5254
> URL: https://issues.apache.org/jira/browse/CALCITE-5254
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> Happens when you have a materialization "target" of an Aggregate/Scan
> attempting to substitute for an a "query" with a matching Scan.
> The following test should repro (in CustomMaterializedViewRecognitionRuleTest)
> {code:java}
> @Test void testNoInifiniteLoopOnAggregateScanMaterialization() {
> final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
> rootSchema.add("mv1", new AbstractTable() {
> @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) {
> return typeFactory.builder()
> .add("count", SqlTypeName.INTEGER)
> .build();
> }
> });
> final Frameworks.ConfigBuilder config = Frameworks.newConfigBuilder()
> .parserConfig(SqlParser.Config.DEFAULT)
> .defaultSchema(
> CalciteAssert.addSchema(rootSchema,
> CalciteAssert.SchemaSpec.SCOTT_WITH_TEMPORAL))
> .traitDefs((List) null);
> final RelBuilder relBuilder = RelBuilder.create(config.build());
> final RelNode query = relBuilder.scan("EMP")
> .project(relBuilder.field("DEPTNO"))
> .aggregate(
> relBuilder.groupKey(0),
> relBuilder.aggregateCall(SqlStdOperatorTable.COUNT)
> )
> .build();
> final RelNode target = relBuilder.scan("EMP")
> .aggregate(relBuilder.groupKey(),
> relBuilder.aggregateCall(SqlStdOperatorTable.COUNT))
> .build();
> final RelNode replacement = relBuilder.scan("mv1").build();
> final RelOptMaterialization relOptMaterialization =
> new RelOptMaterialization(replacement,
> target, null, Lists.newArrayList("mv1"));
> final List>> relOptimized =
> RelOptMaterializations.useMaterializedViews(query,
> ImmutableList.of(relOptMaterialization),
> SubstitutionVisitor.DEFAULT_RULES);
> System.out.println("never get here!");
> } {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5254) Infinite Loop in SubstitutionVisitor.go
[
https://issues.apache.org/jira/browse/CALCITE-5254?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17597958#comment-17597958
]
Steven Talbot commented on CALCITE-5254:
The loop gets stuck because it is checking if it makes any progress, and the
TrivialRule will repeatedly substitute the Scan in the target into the Scan in
the query, so it thinks that it has made progress. However, the query hasn't
changed since we've just substituted the leaf node for an identity, and the
loop continues, indefinitely.
I think the solution is to simply block the trivial rule from substituting if
the match is MutableScan.
{noformat}
diff --git
a/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java
b/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java
index 8044fca68..04ff84214 100644
--- a/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java
+++ b/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java
@@ -1059,7 +1059,7 @@ private TrivialRule() {
}
@Override public @Nullable UnifyResult apply(UnifyRuleCall call) {
- if (call.query.equals(call.target)) {
+ if (call.query.equals(call.target) && !(call.query instanceof
MutableScan)) {
return call.result(call.target);
}
return null;{noformat}
But I might be missing a subtlety of how this bug could occur in other
situations with identity substitutions.
> Infinite Loop in SubstitutionVisitor.go
> ---
>
> Key: CALCITE-5254
> URL: https://issues.apache.org/jira/browse/CALCITE-5254
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> Happens when you have a materialization "target" of an Aggregate/Scan
> attempting to substitute for an a "query" with a matching Scan.
> The following test should repro (in CustomMaterializedViewRecognitionRuleTest)
> {code:java}
> @Test void testNoInifiniteLoopOnAggregateScanMaterialization() {
> final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
> rootSchema.add("mv1", new AbstractTable() {
> @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) {
> return typeFactory.builder()
> .add("count", SqlTypeName.INTEGER)
> .build();
> }
> });
> final Frameworks.ConfigBuilder config = Frameworks.newConfigBuilder()
> .parserConfig(SqlParser.Config.DEFAULT)
> .defaultSchema(
> CalciteAssert.addSchema(rootSchema,
> CalciteAssert.SchemaSpec.SCOTT_WITH_TEMPORAL))
> .traitDefs((List) null);
> final RelBuilder relBuilder = RelBuilder.create(config.build());
> final RelNode query = relBuilder.scan("EMP")
> .project(relBuilder.field("DEPTNO"))
> .aggregate(
> relBuilder.groupKey(0),
> relBuilder.aggregateCall(SqlStdOperatorTable.COUNT)
> )
> .build();
> final RelNode target = relBuilder.scan("EMP")
> .aggregate(relBuilder.groupKey(),
> relBuilder.aggregateCall(SqlStdOperatorTable.COUNT))
> .build();
> final RelNode replacement = relBuilder.scan("mv1").build();
> final RelOptMaterialization relOptMaterialization =
> new RelOptMaterialization(replacement,
> target, null, Lists.newArrayList("mv1"));
> final List>> relOptimized =
> RelOptMaterializations.useMaterializedViews(query,
> ImmutableList.of(relOptMaterialization),
> SubstitutionVisitor.DEFAULT_RULES);
> System.out.println("never ger here!");
> } {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5254) Infinite Loop in SubstitutionVisitor.go
[
https://issues.apache.org/jira/browse/CALCITE-5254?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-5254:
---
Description:
Happens when you have a materialization "target" of an Aggregate/Scan
attempting to substitute for an a "query" with a matching Scan.
The following test should repro (in CustomMaterializedViewRecognitionRuleTest)
{code:java}
@Test void testNoInifiniteLoopOnAggregateScanMaterialization() {
final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
rootSchema.add("mv1", new AbstractTable() {
@Override public RelDataType getRowType(RelDataTypeFactory typeFactory) {
return typeFactory.builder()
.add("count", SqlTypeName.INTEGER)
.build();
}
});
final Frameworks.ConfigBuilder config = Frameworks.newConfigBuilder()
.parserConfig(SqlParser.Config.DEFAULT)
.defaultSchema(
CalciteAssert.addSchema(rootSchema,
CalciteAssert.SchemaSpec.SCOTT_WITH_TEMPORAL))
.traitDefs((List) null);
final RelBuilder relBuilder = RelBuilder.create(config.build());
final RelNode query = relBuilder.scan("EMP")
.project(relBuilder.field("DEPTNO"))
.aggregate(
relBuilder.groupKey(0),
relBuilder.aggregateCall(SqlStdOperatorTable.COUNT)
)
.build();
final RelNode target = relBuilder.scan("EMP")
.aggregate(relBuilder.groupKey(),
relBuilder.aggregateCall(SqlStdOperatorTable.COUNT))
.build();
final RelNode replacement = relBuilder.scan("mv1").build();
final RelOptMaterialization relOptMaterialization =
new RelOptMaterialization(replacement,
target, null, Lists.newArrayList("mv1"));
final List>> relOptimized =
RelOptMaterializations.useMaterializedViews(query,
ImmutableList.of(relOptMaterialization),
SubstitutionVisitor.DEFAULT_RULES);
System.out.println("never get here!");
} {code}
was:
Happens when you have a materialization "target" of an Aggregate/Scan
attempting to substitute for an a "query" with a matching Scan.
The following test should repro (in CustomMaterializedViewRecognitionRuleTest)
{code:java}
@Test void testNoInifiniteLoopOnAggregateScanMaterialization() {
final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
rootSchema.add("mv1", new AbstractTable() {
@Override public RelDataType getRowType(RelDataTypeFactory typeFactory) {
return typeFactory.builder()
.add("count", SqlTypeName.INTEGER)
.build();
}
});
final Frameworks.ConfigBuilder config = Frameworks.newConfigBuilder()
.parserConfig(SqlParser.Config.DEFAULT)
.defaultSchema(
CalciteAssert.addSchema(rootSchema,
CalciteAssert.SchemaSpec.SCOTT_WITH_TEMPORAL))
.traitDefs((List) null);
final RelBuilder relBuilder = RelBuilder.create(config.build());
final RelNode query = relBuilder.scan("EMP")
.project(relBuilder.field("DEPTNO"))
.aggregate(
relBuilder.groupKey(0),
relBuilder.aggregateCall(SqlStdOperatorTable.COUNT)
)
.build();
final RelNode target = relBuilder.scan("EMP")
.aggregate(relBuilder.groupKey(),
relBuilder.aggregateCall(SqlStdOperatorTable.COUNT))
.build();
final RelNode replacement = relBuilder.scan("mv1").build();
final RelOptMaterialization relOptMaterialization =
new RelOptMaterialization(replacement,
target, null, Lists.newArrayList("mv1"));
final List>> relOptimized =
RelOptMaterializations.useMaterializedViews(query,
ImmutableList.of(relOptMaterialization),
SubstitutionVisitor.DEFAULT_RULES);
System.out.println("never ger here!");
} {code}
> Infinite Loop in SubstitutionVisitor.go
> ---
>
> Key: CALCITE-5254
> URL: https://issues.apache.org/jira/browse/CALCITE-5254
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> Happens when you have a materialization "target" of an Aggregate/Scan
> attempting to substitute for an a "query" with a matching Scan.
> The following test should repro (in CustomMaterializedViewRecognitionRuleTest)
> {code:java}
> @Test void testNoInifiniteLoopOnAggregateScanMaterialization() {
> final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
> rootSchema.add("mv1", new AbstractTable() {
> @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) {
> return typeFactory.builder()
> .add("count", SqlTypeName.INTEGER)
> .build();
> }
> });
> final Frameworks.ConfigBuilder config = Frameworks.newConfigBuilder()
> .parserConfig(SqlParser.Config.DEFAULT)
> .defaultSchema(
> CalciteAssert.addSchema(rootSchema,
> CalciteAssert.SchemaSpec.SCOTT_WITH_TEMPORAL))
> .traitDefs((List) null);
> final RelBuilder relBuilder =
[jira] [Created] (CALCITE-5254) Infinite Loop in SubstituionVisitor.go
Steven Talbot created CALCITE-5254:
--
Summary: Infinite Loop in SubstituionVisitor.go
Key: CALCITE-5254
URL: https://issues.apache.org/jira/browse/CALCITE-5254
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
Happens when you have a materialization "target" of an Aggregate/Scan
attempting to substitute for an a "query" with a matching Scan.
The following test should repro (in CustomMaterializedViewRecognitionRuleTest)
{code:java}
@Test void testNoInifiniteLoopOnAggregateScanMaterialization() {
final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
rootSchema.add("mv1", new AbstractTable() {
@Override public RelDataType getRowType(RelDataTypeFactory typeFactory) {
return typeFactory.builder()
.add("count", SqlTypeName.INTEGER)
.build();
}
});
final Frameworks.ConfigBuilder config = Frameworks.newConfigBuilder()
.parserConfig(SqlParser.Config.DEFAULT)
.defaultSchema(
CalciteAssert.addSchema(rootSchema,
CalciteAssert.SchemaSpec.SCOTT_WITH_TEMPORAL))
.traitDefs((List) null);
final RelBuilder relBuilder = RelBuilder.create(config.build());
final RelNode query = relBuilder.scan("EMP")
.project(relBuilder.field("DEPTNO"))
.aggregate(
relBuilder.groupKey(0),
relBuilder.aggregateCall(SqlStdOperatorTable.COUNT)
)
.build();
final RelNode target = relBuilder.scan("EMP")
.aggregate(relBuilder.groupKey(),
relBuilder.aggregateCall(SqlStdOperatorTable.COUNT))
.build();
final RelNode replacement = relBuilder.scan("mv1").build();
final RelOptMaterialization relOptMaterialization =
new RelOptMaterialization(replacement,
target, null, Lists.newArrayList("mv1"));
final List>> relOptimized =
RelOptMaterializations.useMaterializedViews(query,
ImmutableList.of(relOptMaterialization),
SubstitutionVisitor.DEFAULT_RULES);
System.out.println("never ger here!");
} {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5254) Infinite Loop in SubstitutionVisitor.go
[
https://issues.apache.org/jira/browse/CALCITE-5254?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-5254:
---
Summary: Infinite Loop in SubstitutionVisitor.go (was: Infinite Loop in
SubstituionVisitor.go)
> Infinite Loop in SubstitutionVisitor.go
> ---
>
> Key: CALCITE-5254
> URL: https://issues.apache.org/jira/browse/CALCITE-5254
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> Happens when you have a materialization "target" of an Aggregate/Scan
> attempting to substitute for an a "query" with a matching Scan.
> The following test should repro (in CustomMaterializedViewRecognitionRuleTest)
> {code:java}
> @Test void testNoInifiniteLoopOnAggregateScanMaterialization() {
> final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
> rootSchema.add("mv1", new AbstractTable() {
> @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) {
> return typeFactory.builder()
> .add("count", SqlTypeName.INTEGER)
> .build();
> }
> });
> final Frameworks.ConfigBuilder config = Frameworks.newConfigBuilder()
> .parserConfig(SqlParser.Config.DEFAULT)
> .defaultSchema(
> CalciteAssert.addSchema(rootSchema,
> CalciteAssert.SchemaSpec.SCOTT_WITH_TEMPORAL))
> .traitDefs((List) null);
> final RelBuilder relBuilder = RelBuilder.create(config.build());
> final RelNode query = relBuilder.scan("EMP")
> .project(relBuilder.field("DEPTNO"))
> .aggregate(
> relBuilder.groupKey(0),
> relBuilder.aggregateCall(SqlStdOperatorTable.COUNT)
> )
> .build();
> final RelNode target = relBuilder.scan("EMP")
> .aggregate(relBuilder.groupKey(),
> relBuilder.aggregateCall(SqlStdOperatorTable.COUNT))
> .build();
> final RelNode replacement = relBuilder.scan("mv1").build();
> final RelOptMaterialization relOptMaterialization =
> new RelOptMaterialization(replacement,
> target, null, Lists.newArrayList("mv1"));
> final List>> relOptimized =
> RelOptMaterializations.useMaterializedViews(query,
> ImmutableList.of(relOptMaterialization),
> SubstitutionVisitor.DEFAULT_RULES);
> System.out.println("never ger here!");
> } {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Resolved] (CALCITE-5222) Support more EXTRACT field values (or allow arbitrary in parse?)
[
https://issues.apache.org/jira/browse/CALCITE-5222?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot resolved CALCITE-5222.
Resolution: Duplicate
> Support more EXTRACT field values (or allow arbitrary in parse?)
>
>
> Key: CALCITE-5222
> URL: https://issues.apache.org/jira/browse/CALCITE-5222
> Project: Calcite
> Issue Type: Improvement
>Reporter: Steven Talbot
>Priority: Major
>
> E.g. a number of BigQuery EXTRACTs from
> [https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#extract]
> do not parse with the babel parser
> {noformat}
> EXTRACT(DAYOFWEEK FROM )
> EXTRACT(TIME FROM ){noformat}
> (also DATE, DATETIME, DAYOFYEAR... there may be others)
> Other dialects may have similar problems, e.g. "EXTRACT(JULIAN...)" in
> Postgres looks like it wouldn't parse either.
> Calcite chasing down every value ever implemented by a DB vendor here seems
> like it might be too tall of a task, but perhaps allowing for an arbitrary
> keyword in that syntactic position, and then users of the parser would have
> to handle it if the parse of the EXTRACT comes out with an arbitrary symbol,
> rather than a TimeUnit enum value for one of the known EXTRACT fields.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5222) Support more EXTRACT field values (or allow arbitrary in parse?)
[
https://issues.apache.org/jira/browse/CALCITE-5222?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17572979#comment-17572979
]
Steven Talbot commented on CALCITE-5222:
Ah, totally, and I see that it was merged, thanks Julian!
> Support more EXTRACT field values (or allow arbitrary in parse?)
>
>
> Key: CALCITE-5222
> URL: https://issues.apache.org/jira/browse/CALCITE-5222
> Project: Calcite
> Issue Type: Improvement
>Reporter: Steven Talbot
>Priority: Major
>
> E.g. a number of BigQuery EXTRACTs from
> [https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#extract]
> do not parse with the babel parser
> {noformat}
> EXTRACT(DAYOFWEEK FROM )
> EXTRACT(TIME FROM ){noformat}
> (also DATE, DATETIME, DAYOFYEAR... there may be others)
> Other dialects may have similar problems, e.g. "EXTRACT(JULIAN...)" in
> Postgres looks like it wouldn't parse either.
> Calcite chasing down every value ever implemented by a DB vendor here seems
> like it might be too tall of a task, but perhaps allowing for an arbitrary
> keyword in that syntactic position, and then users of the parser would have
> to handle it if the parse of the EXTRACT comes out with an arbitrary symbol,
> rather than a TimeUnit enum value for one of the known EXTRACT fields.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Commented] (CALCITE-5222) Support more EXTRACT field values (or allow arbitrary in parse?)
[
https://issues.apache.org/jira/browse/CALCITE-5222?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17572695#comment-17572695
]
Steven Talbot commented on CALCITE-5222:
^^ probably, sorry for missing that, though this would be a good reminder to
make it work in the parser as well, if that other issue doesn't cover the
parser.
> Support more EXTRACT field values (or allow arbitrary in parse?)
>
>
> Key: CALCITE-5222
> URL: https://issues.apache.org/jira/browse/CALCITE-5222
> Project: Calcite
> Issue Type: Improvement
>Reporter: Steven Talbot
>Priority: Major
>
> E.g. a number of BigQuery EXTRACTs from
> [https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#extract]
> do not parse with the babel parser
> {noformat}
> EXTRACT(DAYOFWEEK FROM )
> EXTRACT(TIME FROM ){noformat}
> (also DATE, DATETIME, DAYOFYEAR... there may be others)
> Other dialects may have similar problems, e.g. "EXTRACT(JULIAN...)" in
> Postgres looks like it wouldn't parse either.
> Calcite chasing down every value ever implemented by a DB vendor here seems
> like it might be too tall of a task, but perhaps allowing for an arbitrary
> keyword in that syntactic position, and then users of the parser would have
> to handle it if the parse of the EXTRACT comes out with an arbitrary symbol,
> rather than a TimeUnit enum value for one of the known EXTRACT fields.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5222) Support more EXTRACT field values (or allow arbitrary in parse?)
Steven Talbot created CALCITE-5222:
--
Summary: Support more EXTRACT field values (or allow arbitrary in
parse?)
Key: CALCITE-5222
URL: https://issues.apache.org/jira/browse/CALCITE-5222
Project: Calcite
Issue Type: Improvement
Reporter: Steven Talbot
E.g. a number of BigQuery EXTRACTs from
[https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#extract]
do not parse with the babel parser
{noformat}
EXTRACT(DAYOFWEEK FROM )
EXTRACT(TIME FROM ){noformat}
(also DATE, DATETIME, DAYOFYEAR... there may be others)
Other dialects may have similar problems, e.g. "EXTRACT(JULIAN...)" in Postgres
looks like it wouldn't parse either.
Calcite chasing down every value ever implemented by a DB vendor here seems
like it might be too tall of a task, but perhaps allowing for an arbitrary
keyword in that syntactic position, and then users of the parser would have to
handle it if the parse of the EXTRACT comes out with an arbitrary symbol,
rather than a TimeUnit enum value for one of the known EXTRACT fields.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (CALCITE-5207) SubstitutionVisitor should not canonicalize DatetimePlus
[
https://issues.apache.org/jira/browse/CALCITE-5207?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-5207:
---
Description:
>From CALCITE-3914, "canonicalizeNode" attempts to canonicalize any
>SqlKind.PLUS rexCall. Unfortunately, this includes a
>`SqlDatetimePlusOperator`. When the canonicalize causes the operand order to
>reverse, this will result in an exception in the line
{code:java}
rexBuilder.makeCall(call.getOperator(), right, left) {code}
in the operators return type inference (since now operand 1 is a timestamp and
operand 0 is an interval)
{code:java}
final IntervalSqlType unitType =
(IntervalSqlType) opBinding.getOperandType(1) {code}
A call like the following should repro when used in tests like those in
[https://github.com/apache/calcite/commit/ee1a9d2cacb67da4c5d7e8f0441c94a40dc69d66:]
{code:java}
final RexNode ts_plus_interval =
rexBuilder.makeCall(
SqlStdOperatorTable.DATETIME_PLUS,
rexBuilder.makeTimestampLiteral(new TimestampString("2018-01-01
00:00:00"), 0),
rexBuilder.makeIntervalLiteral(
new BigDecimal(12),
new SqlIntervalQualifier(TimeUnit.MONTH, null,
SqlParserPos.ZERO)
)); {code}
was:
>From CALCITE-3914, "canonicalizeNode" attempts to canonicalize any
>SqlKind.PLUS rexCall. Unfortunately, this includes a
>`SqlDatetimePlusOperator`. When the canonicalize causes the operand order to
>reverse, this will result in an exception in the line
{code:java}
rexBuilder.makeCall(call.getOperator(), right, left) {code}
in the operators return type inference
{code:java}
final IntervalSqlType unitType =
(IntervalSqlType) opBinding.getOperandType(1) {code}
A call like the following should repro when used in tests like those in
https://github.com/apache/calcite/commit/ee1a9d2cacb67da4c5d7e8f0441c94a40dc69d66:
{code:java}
final RexNode ts_plus_interval =
rexBuilder.makeCall(
SqlStdOperatorTable.DATETIME_PLUS,
rexBuilder.makeTimestampLiteral(new TimestampString("2018-01-01
00:00:00"), 0),
rexBuilder.makeIntervalLiteral(
new BigDecimal(12),
new SqlIntervalQualifier(TimeUnit.MONTH, null,
SqlParserPos.ZERO)
)); {code}
> SubstitutionVisitor should not canonicalize DatetimePlus
>
>
> Key: CALCITE-5207
> URL: https://issues.apache.org/jira/browse/CALCITE-5207
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> From CALCITE-3914, "canonicalizeNode" attempts to canonicalize any
> SqlKind.PLUS rexCall. Unfortunately, this includes a
> `SqlDatetimePlusOperator`. When the canonicalize causes the operand order to
> reverse, this will result in an exception in the line
> {code:java}
> rexBuilder.makeCall(call.getOperator(), right, left) {code}
> in the operators return type inference (since now operand 1 is a timestamp
> and operand 0 is an interval)
> {code:java}
> final IntervalSqlType unitType =
> (IntervalSqlType) opBinding.getOperandType(1) {code}
> A call like the following should repro when used in tests like those in
> [https://github.com/apache/calcite/commit/ee1a9d2cacb67da4c5d7e8f0441c94a40dc69d66:]
>
> {code:java}
> final RexNode ts_plus_interval =
> rexBuilder.makeCall(
> SqlStdOperatorTable.DATETIME_PLUS,
> rexBuilder.makeTimestampLiteral(new
> TimestampString("2018-01-01 00:00:00"), 0),
> rexBuilder.makeIntervalLiteral(
> new BigDecimal(12),
> new SqlIntervalQualifier(TimeUnit.MONTH,
> null, SqlParserPos.ZERO)
> )); {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5207) SubstitutionVisitor should not canonicalize DatetimePlus
Steven Talbot created CALCITE-5207:
--
Summary: SubstitutionVisitor should not canonicalize DatetimePlus
Key: CALCITE-5207
URL: https://issues.apache.org/jira/browse/CALCITE-5207
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
>From CALCITE-3914, "canonicalizeNode" attempts to canonicalize any
>SqlKind.PLUS rexCall. Unfortunately, this includes a
>`SqlDatetimePlusOperator`. When the canonicalize causes the operand order to
>reverse, this will result in an exception in the line
{code:java}
rexBuilder.makeCall(call.getOperator(), right, left) {code}
in the operators return type inference
{code:java}
final IntervalSqlType unitType =
(IntervalSqlType) opBinding.getOperandType(1) {code}
A call like the following should repro when used in tests like those in
https://github.com/apache/calcite/commit/ee1a9d2cacb67da4c5d7e8f0441c94a40dc69d66:
{code:java}
final RexNode ts_plus_interval =
rexBuilder.makeCall(
SqlStdOperatorTable.DATETIME_PLUS,
rexBuilder.makeTimestampLiteral(new TimestampString("2018-01-01
00:00:00"), 0),
rexBuilder.makeIntervalLiteral(
new BigDecimal(12),
new SqlIntervalQualifier(TimeUnit.MONTH, null,
SqlParserPos.ZERO)
)); {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (CALCITE-5200) FILTER_INTO_JOIN rule in RelOptMaterializations is too "smart"
Steven Talbot created CALCITE-5200:
--
Summary: FILTER_INTO_JOIN rule in RelOptMaterializations is too
"smart"
Key: CALCITE-5200
URL: https://issues.apache.org/jira/browse/CALCITE-5200
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
Specifically, when it fires (using the names in
"RelOptMaterialization.substitute") if the "root" has a filter on the right
table of a LEFT join, and "materialization" does not, then the rule turns the
left join into an INNER join in "root", which then fails to match
"materialization", preventing valid optimization. ("valid" in the sense that
the optimization works either without the filter or with an inner join).
I'm lacking the setup to write a test at the moment, and I don't see a test
file that targets RelOptMaterializations specifically, but something like
{noformat}
RelNode root = relBuilder.scan(emp)
.scan(dept)
.join(JoinType.LEFT, "deptNo")
.filter(relBuilder.equals(relBuilder.field(2, "dept", "deptNo"),
relBuilder.literal(1))
.project()
.aggregate()
RelNode materialization = relBuilder.scan(emp)
.scan(dept)
.join(JoinType.LEFT, "deptNo")
.project()
.aggregate(){noformat}
The "smart" config in the FilterIntoJoin rule is what does this rewrite, and I
would contend that we should use the "dumb" config instead for
RelOptMaterializations, because for the purposes of getting a match the rule
should change exactly what it needs to change and nothing else.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Commented] (CALCITE-5183) Impossible condition in return type inference for SqlIntervalOperator?
[ https://issues.apache.org/jira/browse/CALCITE-5183?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17557100#comment-17557100 ] Steven Talbot commented on CALCITE-5183: For the record, I did end up needing to use MULTIPLY (thanks!) because TIMESTAMP_ADD (the workaround I was using) requires a TimeUnit rather than a TimeUnitRange, which blows up in RelJson (which I am also using). > Impossible condition in return type inference for SqlIntervalOperator? > -- > > Key: CALCITE-5183 > URL: https://issues.apache.org/jira/browse/CALCITE-5183 > Project: Calcite > Issue Type: Bug >Reporter: Steven Talbot >Priority: Major > > I could be wrong here, but I don't see how to make this work, and I don't see > any tests for it being used on the relBuilder side of the fence (where the > "impossible" type inference is invoked for me). > > [https://github.com/apache/calcite/commit/03c76a7d2b896042ab417ddc36f1849f874ad3dd#diff-f6836cabfabc14be277e9f7406ab38996aaa45bac86969cecd64189c2fd7c745R57] > requires the second argument to the Interval call to be literal wrapper > around a SqlIntervalQualifier. In the context of RexCallBinding, I don't > believe this is possible, since the argument has to be a RexLiteral, and > RexLiteral exposes no way to directly wrap a value as a SqlIntervalQualifier > like this. Again, this is where my reading of the code gets uncertain, but > neither signature of RexBuilder.makeIntervalLiteral will give you what you > want, and I don't see any way this could be possible. > > This issue means that there seems to be no way to use > `SqlStdOperatorTable.INTERVAL` with RelBuilder.call. -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Commented] (CALCITE-5183) Impossible condition in return type inference for SqlIntervalOperator?
[
https://issues.apache.org/jira/browse/CALCITE-5183?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17551198#comment-17551198
]
Steven Talbot commented on CALCITE-5183:
^^ regardless of the ergonomics of that interval creation API for the
RelBuilder, it doesn't work here.
(in Kotlin)
{code:java}
val literal = relBuilder.literal(1)
relBuilder.values(listOf(listOf(literal)),
relBuilder.typeFactory.createStructType(listOf(literal.type), listOf("n")))
val intervalQualifier = SqlIntervalQualifier(TimeUnit.DAY, null,
SqlParserPos.ZERO)
val intervalLiteral =
relBuilder.rexBuilder.makeIntervalLiteral(intervalQualifier)
relBuilder.call(SqlStdOperatorTable.INTERVAL, relBuilder.field(0),
intervalLiteral){code}
produces
{noformat}
cannot convert SYMBOL literal to class
org.apache.calcite.sql.SqlIntervalQualifier
java.lang.AssertionError: cannot convert SYMBOL literal to class
org.apache.calcite.sql.SqlIntervalQualifier
at org.apache.calcite.rex.RexLiteral.getValueAs(RexLiteral.java:1143)
at
org.apache.calcite.rex.RexCallBinding.getOperandLiteralValue(RexCallBinding.java:100)
at
org.apache.calcite.sql.validate.SqlNonNullableAccessors.getOperandLiteralValueOrThrow(SqlNonNullableAccessors.java:108)
at
org.apache.calcite.sql.fun.SqlIntervalOperator.returnType(SqlIntervalOperator.java:59)
at
org.apache.calcite.sql.type.SqlTypeTransformCascade.inferReturnType(SqlTypeTransformCascade.java:58)
at org.apache.calcite.sql.SqlOperator.inferReturnType(SqlOperator.java:537)
at org.apache.calcite.rex.RexBuilder.deriveReturnType(RexBuilder.java:290)
at org.apache.calcite.tools.RelBuilder.call(RelBuilder.java:689)
at org.apache.calcite.tools.RelBuilder.call(RelBuilder.java:667){noformat}
(the call to makeLiteral without a number for the interval basically produces
the same thing as RexBuilder.makeFlag(TimeUnit))
OR, even if you try to fake the "number" for the interval qualifier, it still
doesn't work
{code:java}
val literal = relBuilder.literal(1)
relBuilder.values(listOf(listOf(literal)),
relBuilder.typeFactory.createStructType(listOf(literal.type), listOf("n")))
val intervalQualifier = SqlIntervalQualifier(TimeUnit.DAY, null,
SqlParserPos.ZERO)
val ignored = BigDecimal(13)
val intervalLiteral = relBuilder.rexBuilder.makeIntervalLiteral(ignored,
intervalQualifier)
relBuilder.call(SqlStdOperatorTable.INTERVAL, relBuilder.field(0),
intervalLiteral){code}
produces
{noformat}
cannot convert INTERVAL_DAY literal to class
org.apache.calcite.sql.SqlIntervalQualifier
java.lang.AssertionError: cannot convert INTERVAL_DAY literal to class
org.apache.calcite.sql.SqlIntervalQualifier
at org.apache.calcite.rex.RexLiteral.getValueAs(RexLiteral.java:1143)
at
org.apache.calcite.rex.RexCallBinding.getOperandLiteralValue(RexCallBinding.java:100)
at
org.apache.calcite.sql.validate.SqlNonNullableAccessors.getOperandLiteralValueOrThrow(SqlNonNullableAccessors.java:108)
at
org.apache.calcite.sql.fun.SqlIntervalOperator.returnType(SqlIntervalOperator.java:59)
at
org.apache.calcite.sql.type.SqlTypeTransformCascade.inferReturnType(SqlTypeTransformCascade.java:58)
at org.apache.calcite.sql.SqlOperator.inferReturnType(SqlOperator.java:537)
at org.apache.calcite.rex.RexBuilder.deriveReturnType(RexBuilder.java:290)
at org.apache.calcite.tools.RelBuilder.call(RelBuilder.java:689)
at org.apache.calcite.tools.RelBuilder.call(RelBuilder.java:667){noformat}
> Impossible condition in return type inference for SqlIntervalOperator?
> --
>
> Key: CALCITE-5183
> URL: https://issues.apache.org/jira/browse/CALCITE-5183
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> I could be wrong here, but I don't see how to make this work, and I don't see
> any tests for it being used on the relBuilder side of the fence (where the
> "impossible" type inference is invoked for me).
>
> [https://github.com/apache/calcite/commit/03c76a7d2b896042ab417ddc36f1849f874ad3dd#diff-f6836cabfabc14be277e9f7406ab38996aaa45bac86969cecd64189c2fd7c745R57]
> requires the second argument to the Interval call to be literal wrapper
> around a SqlIntervalQualifier. In the context of RexCallBinding, I don't
> believe this is possible, since the argument has to be a RexLiteral, and
> RexLiteral exposes no way to directly wrap a value as a SqlIntervalQualifier
> like this. Again, this is where my reading of the code gets uncertain, but
> neither signature of RexBuilder.makeIntervalLiteral will give you what you
> want, and I don't see any way this could be possible.
>
> This issue means that there seems to be no way to use
> `SqlStdOperatorTable.INTERVAL` with RelBuilder.call.
--
This message was sent by Atlassian Jira
[jira] [Created] (CALCITE-5183) Impossible condition in return type inference for SqlIntervalOperator?
Steven Talbot created CALCITE-5183: -- Summary: Impossible condition in return type inference for SqlIntervalOperator? Key: CALCITE-5183 URL: https://issues.apache.org/jira/browse/CALCITE-5183 Project: Calcite Issue Type: Bug Reporter: Steven Talbot I could be wrong here, but I don't see how to make this work, and I don't see any tests for it being used on the relBuilder side of the fence (where the "impossible" type inference is invoked for me). [https://github.com/apache/calcite/commit/03c76a7d2b896042ab417ddc36f1849f874ad3dd#diff-f6836cabfabc14be277e9f7406ab38996aaa45bac86969cecd64189c2fd7c745R57] requires the second argument to the Interval call to be a SqlIntervalQualifier. In the context of RexCallBinding, I don't believe this is possible, since the argument has to be a RexLiteral, and RexLiteral exposes no way to directly wrap a value as a SqlIntervalQualifier like this. Again, this is where my reading of the code gets uncertain, but neither signature of RexBuilder.makeIntervalLiteral will give you what you want, and I don't see any way this could be possible. This issue means that there seems to be no way to use `SqlStdOperatorTable.INTERVAL` with RelBuilder.call. -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Updated] (CALCITE-5183) Impossible condition in return type inference for SqlIntervalOperator?
[ https://issues.apache.org/jira/browse/CALCITE-5183?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Steven Talbot updated CALCITE-5183: --- Description: I could be wrong here, but I don't see how to make this work, and I don't see any tests for it being used on the relBuilder side of the fence (where the "impossible" type inference is invoked for me). [https://github.com/apache/calcite/commit/03c76a7d2b896042ab417ddc36f1849f874ad3dd#diff-f6836cabfabc14be277e9f7406ab38996aaa45bac86969cecd64189c2fd7c745R57] requires the second argument to the Interval call to be literal wrapper around a SqlIntervalQualifier. In the context of RexCallBinding, I don't believe this is possible, since the argument has to be a RexLiteral, and RexLiteral exposes no way to directly wrap a value as a SqlIntervalQualifier like this. Again, this is where my reading of the code gets uncertain, but neither signature of RexBuilder.makeIntervalLiteral will give you what you want, and I don't see any way this could be possible. This issue means that there seems to be no way to use `SqlStdOperatorTable.INTERVAL` with RelBuilder.call. was: I could be wrong here, but I don't see how to make this work, and I don't see any tests for it being used on the relBuilder side of the fence (where the "impossible" type inference is invoked for me). [https://github.com/apache/calcite/commit/03c76a7d2b896042ab417ddc36f1849f874ad3dd#diff-f6836cabfabc14be277e9f7406ab38996aaa45bac86969cecd64189c2fd7c745R57] requires the second argument to the Interval call to be a SqlIntervalQualifier. In the context of RexCallBinding, I don't believe this is possible, since the argument has to be a RexLiteral, and RexLiteral exposes no way to directly wrap a value as a SqlIntervalQualifier like this. Again, this is where my reading of the code gets uncertain, but neither signature of RexBuilder.makeIntervalLiteral will give you what you want, and I don't see any way this could be possible. This issue means that there seems to be no way to use `SqlStdOperatorTable.INTERVAL` with RelBuilder.call. > Impossible condition in return type inference for SqlIntervalOperator? > -- > > Key: CALCITE-5183 > URL: https://issues.apache.org/jira/browse/CALCITE-5183 > Project: Calcite > Issue Type: Bug >Reporter: Steven Talbot >Priority: Major > > I could be wrong here, but I don't see how to make this work, and I don't see > any tests for it being used on the relBuilder side of the fence (where the > "impossible" type inference is invoked for me). > > [https://github.com/apache/calcite/commit/03c76a7d2b896042ab417ddc36f1849f874ad3dd#diff-f6836cabfabc14be277e9f7406ab38996aaa45bac86969cecd64189c2fd7c745R57] > requires the second argument to the Interval call to be literal wrapper > around a SqlIntervalQualifier. In the context of RexCallBinding, I don't > believe this is possible, since the argument has to be a RexLiteral, and > RexLiteral exposes no way to directly wrap a value as a SqlIntervalQualifier > like this. Again, this is where my reading of the code gets uncertain, but > neither signature of RexBuilder.makeIntervalLiteral will give you what you > want, and I don't see any way this could be possible. > > This issue means that there seems to be no way to use > `SqlStdOperatorTable.INTERVAL` with RelBuilder.call. -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Commented] (CALCITE-5179) AssertionError in values with more than two items when SqlDialect#supportsAliasedValues is false
[ https://issues.apache.org/jira/browse/CALCITE-5179?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17548613#comment-17548613 ] Steven Talbot commented on CALCITE-5179: And thanks for the quick fix! > AssertionError in values with more than two items when > SqlDialect#supportsAliasedValues is false > > > Key: CALCITE-5179 > URL: https://issues.apache.org/jira/browse/CALCITE-5179 > Project: Calcite > Issue Type: Bug >Reporter: Steven Talbot >Assignee: Jiajun Xie >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > The code at > [https://github.com/apache/calcite/blob/4b349032c17b95735b12593a65f7027d54d8b8b6/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L802-L803] > creates a UNION ALL with an arbitrary number of operands, but [the UNION_ALL > operator is a > SqlSetOperator|https://github.com/apache/calcite/blob/4b349032c17b95735b12593a65f7027d54d8b8b6/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java#L108-L109] > which is a "SqlBinaryOperator" (Binary syntax). So that generates an > assertion error when it unparses more than two values at > [https://github.com/apache/calcite/blob/4b349032c17b95735b12593a65f7027d54d8b8b6/core/src/main/java/org/apache/calcite/sql/SqlUtil.java#L426] > > I don't _think_ I've done anything nonstandard, other than use a dialect with > that flag set to false. To reproduce, create a values RelNode with more than > two items, convert it with RelToSqlConverter with a dialect using > supportsAliasedValues() = false (like Redshift), and attempt to unparse the > resulting SqlNode. -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Commented] (CALCITE-5179) AssertionError in values with more than two items when SqlDialect#supportsAliasedValues is false
[ https://issues.apache.org/jira/browse/CALCITE-5179?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17548579#comment-17548579 ] Steven Talbot commented on CALCITE-5179: Eh, I guess a binary tree would be no good, because I think then the unparsing would put parens all over the place to try to respect the precedence of the tree, when of course it doesn't matter. This solution is probably fine. If you want to use a large number of values, increase your stack size :) > AssertionError in values with more than two items when > SqlDialect#supportsAliasedValues is false > > > Key: CALCITE-5179 > URL: https://issues.apache.org/jira/browse/CALCITE-5179 > Project: Calcite > Issue Type: Bug >Reporter: Steven Talbot >Assignee: Jiajun Xie >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > The code at > [https://github.com/apache/calcite/blob/4b349032c17b95735b12593a65f7027d54d8b8b6/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L802-L803] > creates a UNION ALL with an arbitrary number of operands, but [the UNION_ALL > operator is a > SqlSetOperator|https://github.com/apache/calcite/blob/4b349032c17b95735b12593a65f7027d54d8b8b6/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java#L108-L109] > which is a "SqlBinaryOperator" (Binary syntax). So that generates an > assertion error when it unparses more than two values at > [https://github.com/apache/calcite/blob/4b349032c17b95735b12593a65f7027d54d8b8b6/core/src/main/java/org/apache/calcite/sql/SqlUtil.java#L426] > > I don't _think_ I've done anything nonstandard, other than use a dialect with > that flag set to false. To reproduce, create a values RelNode with more than > two items, convert it with RelToSqlConverter with a dialect using > supportsAliasedValues() = false (like Redshift), and attempt to unparse the > resulting SqlNode. -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Commented] (CALCITE-5179) AssertionError in values with more than two items when SqlDialect#supportsAliasedValues is false
[ https://issues.apache.org/jira/browse/CALCITE-5179?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17548577#comment-17548577 ] Steven Talbot commented on CALCITE-5179: ^^ indeed, in fact in an ideal world UNION ALL would not be a binary operator, since the nesting required, if you had a very large number of rows in your values (turned into a union all) could easily blow out the stack trying to unparse it. Or, you could structure the union operators as a binary tree rather than the single branching tree. That being said, maybe such a large number (thousands?) of values is not something we want to overcomplicate the code to support. > AssertionError in values with more than two items when > SqlDialect#supportsAliasedValues is false > > > Key: CALCITE-5179 > URL: https://issues.apache.org/jira/browse/CALCITE-5179 > Project: Calcite > Issue Type: Bug >Reporter: Steven Talbot >Assignee: Jiajun Xie >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > The code at > [https://github.com/apache/calcite/blob/4b349032c17b95735b12593a65f7027d54d8b8b6/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L802-L803] > creates a UNION ALL with an arbitrary number of operands, but [the UNION_ALL > operator is a > SqlSetOperator|https://github.com/apache/calcite/blob/4b349032c17b95735b12593a65f7027d54d8b8b6/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java#L108-L109] > which is a "SqlBinaryOperator" (Binary syntax). So that generates an > assertion error when it unparses more than two values at > [https://github.com/apache/calcite/blob/4b349032c17b95735b12593a65f7027d54d8b8b6/core/src/main/java/org/apache/calcite/sql/SqlUtil.java#L426] > > I don't _think_ I've done anything nonstandard, other than use a dialect with > that flag set to false. To reproduce, create a values RelNode with more than > two items, convert it with RelToSqlConverter with a dialect using > supportsAliasedValues() = false (like Redshift), and attempt to unparse the > resulting SqlNode. -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Created] (CALCITE-5179) supportsAliasedValues() false generates assertion-failing UNION ALL SqlNode
Steven Talbot created CALCITE-5179: -- Summary: supportsAliasedValues() false generates assertion-failing UNION ALL SqlNode Key: CALCITE-5179 URL: https://issues.apache.org/jira/browse/CALCITE-5179 Project: Calcite Issue Type: Bug Reporter: Steven Talbot The code at [https://github.com/apache/calcite/blob/4b349032c17b95735b12593a65f7027d54d8b8b6/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java#L802-L803] creates a UNION ALL with an arbitrary number of operands, but [the UNION_ALL operator is a SqlSetOperator|https://github.com/apache/calcite/blob/4b349032c17b95735b12593a65f7027d54d8b8b6/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java#L108-L109] which is a "SqlBinaryOperator" (Binary syntax). So that generates an assertion error when it unparses more than two values at [https://github.com/apache/calcite/blob/4b349032c17b95735b12593a65f7027d54d8b8b6/core/src/main/java/org/apache/calcite/sql/SqlUtil.java#L426] I don't _think_ I've done anything nonstandard, other than use a dialect with that flag set to false. To reproduce, create a values RelNode with more than two items, convert it with RelToSqlConverter with a dialect using supportsAliasedValues() = false (like Redshift), and attempt to unparse the resulting SqlNode. -- This message was sent by Atlassian Jira (v8.20.7#820007)
[jira] [Commented] (CALCITE-5147) Parser configured with BigQuery dialect cannot parse timestamp literal
[
https://issues.apache.org/jira/browse/CALCITE-5147?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17534510#comment-17534510
]
Steven Talbot commented on CALCITE-5147:
The parser does not accept TIMESTAMP "2018-02-17 13:22:04" either, actually.
The double quoted string tokenizes as BIG_QUERY_DOUBLE_QUOTED_STRING in
Parser.jj. So yes, good point, it should accept both.
> Parser configured with BigQuery dialect cannot parse timestamp literal
> --
>
> Key: CALCITE-5147
> URL: https://issues.apache.org/jira/browse/CALCITE-5147
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> It looks like
> [CALCITE-4724|https://github.com/apache/calcite/commit/c1052b343724c4a95bcf25419b70bc6032e0846b#diff-e873041549333502af52ece8a1b34301ae5a059ff4719e9bddbaef48929e7047R7796]
> creates a special string literal token for BigQuery. But that means that
> (AFAICT)
> [Parser.jj|https://github.com/apache/calcite/blob/b9c2099ea92a575084b55a206efc5dd341c0df62/core/src/main/codegen/templates/Parser.jj#L4529]
> then does not find the expected token type after a TIMESTAMP to make a
> timestamp literal, so something as simple as
> {code:java}
> SqlParser.create("SELECT TIMESTAMP '2018-02-17 13:22:04'",
> BigQuerySqlDialect.DEFAULT.configureParser(SqlParser.config())).parseQuery()
> {code}
> fails.
> Probably most other places in Parser.jj that use the "" token
> suffer from the same issue.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Created] (CALCITE-5147) Parser configured with BigQuery dialect cannot parse timestamp literal
Steven Talbot created CALCITE-5147:
--
Summary: Parser configured with BigQuery dialect cannot parse
timestamp literal
Key: CALCITE-5147
URL: https://issues.apache.org/jira/browse/CALCITE-5147
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
It looks like
[https://github.com/apache/calcite/commit/c1052b343724c4a95bcf25419b70bc6032e0846b#diff-e873041549333502af52ece8a1b34301ae5a059ff4719e9bddbaef48929e7047R7796]
creates a special string literal token for BigQuery. But that means that
(AFAICT)
[https://github.com/apache/calcite/blob/b9c2099ea92a575084b55a206efc5dd341c0df62/core/src/main/codegen/templates/Parser.jj#L4529]
then does not find the expected token type after a TIMESTAMP to make a
timestamp literal, so something as simple as
{code:java}
SqlParser.create("SELECT TIMESTAMP '2018-02-17 13:22:04'",
BigQuerySqlDialect.DEFAULT.configureParser(SqlParser.config())).parseQuery()
{code}
fails.
Probably most other places in Parser.jj that use the "" token
suffer from the same issue.
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Updated] (CALCITE-4760) Should not need to initialize a jdbc:calcite driver to make a RelBuilder
[
https://issues.apache.org/jira/browse/CALCITE-4760?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-4760:
---
Description:
We just tracked down a nasty issue with Calcite being shaded as a dependency in
our code that could cause a stack like
{noformat}
No suitable driver found for jdbc:calcite:
org/apache/calcite/tools/Frameworks.java:184:in withPrepare
org/apache/calcite/tools/RelBuilder.java:225:in create
{noformat}
when calling RelBuilder.create.
Our fault ultimately, but [~julianhyde] pointed out that Calcite really
shouldn't be trying to make a JDBC connection here.
was:
We just tracked down a nasty issue with Calcite being shaded as a dependency in
our code that could cause a stack like
{noformat}
No suitable driver found for jdbc:calcite:
org/apache/calcite/tools/Frameworks.java:184:in withPrepare
org/apache/calcite/tools/RelBuilder.java:225:in create
{noformat}
{{}}
{{}}
when calling RelBuilder.create.
Our fault ultimately, but [~julianhyde] pointed out that Calcite really
shouldn't be trying to make a JDBC connection here.
{{}}
> Should not need to initialize a jdbc:calcite driver to make a RelBuilder
>
>
> Key: CALCITE-4760
> URL: https://issues.apache.org/jira/browse/CALCITE-4760
> Project: Calcite
> Issue Type: Improvement
>Reporter: Steven Talbot
>Priority: Major
>
> We just tracked down a nasty issue with Calcite being shaded as a dependency
> in our code that could cause a stack like
>
> {noformat}
> No suitable driver found for jdbc:calcite:
> org/apache/calcite/tools/Frameworks.java:184:in withPrepare
> org/apache/calcite/tools/RelBuilder.java:225:in create
> {noformat}
>
> when calling RelBuilder.create.
> Our fault ultimately, but [~julianhyde] pointed out that Calcite really
> shouldn't be trying to make a JDBC connection here.
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4760) Should not need to initialize a jdbc:calcite driver to make a RelBuilder
Steven Talbot created CALCITE-4760:
--
Summary: Should not need to initialize a jdbc:calcite driver to
make a RelBuilder
Key: CALCITE-4760
URL: https://issues.apache.org/jira/browse/CALCITE-4760
Project: Calcite
Issue Type: Improvement
Reporter: Steven Talbot
We just tracked down a nasty issue with Calcite being shaded as a dependency in
our code that could cause a stack like
{noformat}
No suitable driver found for jdbc:calcite:
org/apache/calcite/tools/Frameworks.java:184:in withPrepare
org/apache/calcite/tools/RelBuilder.java:225:in create
{noformat}
{{}}
{{}}
when calling RelBuilder.create.
Our fault ultimately, but [~julianhyde] pointed out that Calcite really
shouldn't be trying to make a JDBC connection here.
{{}}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4485) Invalid SQL being produced by RelToSqlConverter when the join condition is tautology
[
https://issues.apache.org/jira/browse/CALCITE-4485?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17394828#comment-17394828
]
Steven Talbot commented on CALCITE-4485:
Change seems good to me. A nit would be that you could check the join
type/always-truthiness in the join flattening loop and bail out early, but in
any realistically-sized join tree that's going to matter not at all. Another
nit I suppose would be that the "// Find the topmost enclosing Join" loop would
look less scary if `j` was init'd to `join`, just because otherwise it's
assuming something about the way "stack" works to avoid an NPE. But the
assumption is true, so obviously it's fine.
> Invalid SQL being produced by RelToSqlConverter when the join condition is
> tautology
>
>
> Key: CALCITE-4485
> URL: https://issues.apache.org/jira/browse/CALCITE-4485
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Ondřej Štumpf
>Assignee: Julian Hyde
>Priority: Major
> Attachments: comma_vs_joins_postgres.sql
>
>
> h5. Summary
> When using {{INNER}} join and a join condition that is always true, the
> {{RelToSqlConverter}} may generate an invalid SQL.
> h5. Reproducer (Kotlin; TPCH dataset)
> {code:java}
> val frameworkConfig = ...
> val relationalBuilder = RelBuilder.create(frameworkConfig)
> val rootRelationalNode = relationalBuilder
> .scan("tpch", "customer")
> .aggregate(
> relationalBuilder.groupKey(
> relationalBuilder.field("nation_name")
> ),
> relationalBuilder.count().`as`("cnt1")
> )
> .project(relationalBuilder.field("nation_name"),
> relationalBuilder.field("cnt1"))
> .`as`("cust")
> .scan("tpch", "lineitem")
> .aggregate(
> relationalBuilder.groupKey(),
> relationalBuilder.count().`as`("cnt2")
> )
> .project(relationalBuilder.field("cnt2"))
> .`as`("lineitem")
> .join(JoinRelType.INNER)
> .scan("tpch", "part")
> .join(
> JoinRelType.LEFT,
> relationalBuilder.call(
> SqlStdOperatorTable.EQUALS,
> relationalBuilder.field(2, "cust", "nation_name"),
> relationalBuilder.field(2, "part", "p_brand")
> )
> )
> .project(
> relationalBuilder.field("cust", "nation_name"),
> relationalBuilder.alias(
> relationalBuilder.call(
> SqlStdOperatorTable.MINUS,
> relationalBuilder.field("cnt1"),
> relationalBuilder.field("cnt2")
> ),
> "f1")
> )
> .build()
> println(
> RelToSqlConverter(SqlDialect.DatabaseProduct.POSTGRESQL.dialect)
> .visitRoot(rootRelationalNode)
> .asStatement()
> .toSqlString(SqlDialect.DatabaseProduct.POSTGRESQL.dialect)
> .sql
> )
> {code}
> h5. Result SQL
> {code:sql}
> SELECT "t"."c_name", "t"."cnt1" - "t0"."cnt2" AS "f1"
> FROM (SELECT "c_name", COUNT(*) AS "cnt1"
> FROM "tpch"."customer"
> GROUP BY "c_name") AS "t",
> (SELECT COUNT(*) AS "cnt2"
> FROM "tpch"."lineitem") AS "t0"
> LEFT JOIN "tpch"."part" ON "t"."c_name" = "part"."p_brand"
> {code}
> which produces error (on Postgres):
> {code}
> [42P01] ERROR: invalid reference to FROM-clause entry for table "t" Hint:
> There is an entry for table "t", but it cannot be referenced from this part
> of the query. Position: 265
> {code}
> h5. Expected SQL
> {code:sql}
> SELECT "t"."c_name", "t"."cnt1" - "t0"."cnt2" AS "f1"
> FROM (SELECT "c_name", COUNT(*) AS "cnt1"
> FROM "tpch"."customer"
> GROUP BY "c_name") AS "t" CROSS JOIN
> (SELECT COUNT(*) AS "cnt2"
> FROM "tpch"."lineitem") AS "t0"
> LEFT JOIN "tpch"."part" ON "t"."c_name" = "part"."p_brand"
> {code}
> h5. Suggestion for a fix
> The {{CROSS JOIN}} syntax was introduced in SQL 92 and seems to be widely
> supported by DB engines, therefore I suggest to change the default in
> {{SqlDialect#emulateJoinTypeForCrossJoin}} from {{JoinType.COMMA}} to
> {{JoinType.CROSS}}.
> I have checked all supported DB products from {{SqlDialect.DatabaseProduct}}
> and they all seem to support the {{CROSS JOIN}} syntax, except these, which I
> have not been able to verify: Infobright, Luciddb, Paraccel, Netezza. For
> sake of backward compatibility, I suggest to override the
> {{emulateJoinTypeForCrossJoin}} method in these dialects to behave as before,
> i.e. {{JoinType.COMMA}}.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4632) Exception in RelToSqlConverter: "Cannot convert x to DECIMAL(n, m) due to overflow"
Steven Talbot created CALCITE-4632:
--
Summary: Exception in RelToSqlConverter: "Cannot convert x to
DECIMAL(n, m) due to overflow"
Key: CALCITE-4632
URL: https://issues.apache.org/jira/browse/CALCITE-4632
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
Test in RelToSqlConverter.java
{code:java}
@Test void testSelectWhereInDecimal() {
final Function relFn = b -> b
.scan("EMP")
.filter(
b.or(b.isNull(b.field("COMM")),
(b.in(b.field("COMM"), b.literal(new BigDecimal("1.0")),
b.literal(new BigDecimal("2.0"))
.build();
final String expected = "SELECT *\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "WHERE \"COMM\" IS NULL OR \"COMM\" IN (1.0, 2.0)";
relFn(relFn).ok(expected);
}{code}
Stack trace:
{noformat}
Cannot convert 2.0 to DECIMAL(2, 1) due to overflow
java.lang.IllegalArgumentException: Cannot convert 2.0 to DECIMAL(2, 1) due
to overflow
at org.apache.calcite.rex.RexBuilder.makeLiteral(RexBuilder.java:990)
at
org.apache.calcite.rex.RexBuilder.makeExactLiteral(RexBuilder.java:1046)
at org.apache.calcite.rex.RexBuilder.makeLiteral(RexBuilder.java:1592)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.lambda$toIn$2(SqlImplementor.java:937)
at
java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
at
com.google.common.collect.CollectSpliterators$1WithCharacteristics.lambda$forEachRemaining$1(CollectSpliterators.java:67)
at
java.util.stream.Streams$RangeIntSpliterator.forEachRemaining(Streams.java:110)
at
com.google.common.collect.CollectSpliterators$1WithCharacteristics.forEachRemaining(CollectSpliterators.java:67)
at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:482)
at
java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:472)
at
java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at
java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:499)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toIn(SqlImplementor.java:939)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:912)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:804)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:338)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:134)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:142)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:185)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:173)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitRoot(SqlImplementor.java:153)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.toSql(RelToSqlConverterTest.java:216)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.toSql(RelToSqlConverterTest.java:204)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.access$300(RelToSqlConverterTest.java:111)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.exec(RelToSqlConverterTest.java:5999)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.ok(RelToSqlConverterTest.java:5967)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.testSelectWhereInDecimal(RelToSqlConverterTest.java:339){noformat}
Almost certainly because we just grab the type of the first arg at
https://github.com/apache/calcite/blob/204b5ab42d9e365c55636cd0aca9f750f4d50e5d/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L801-L804,
which in a decimal type is not guaranteed to be compatible with the remainder
of the args. Probably need to call one of the type normalizing functions?
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4631) sql->rel->sql bug with unparsing timestampdiff
[
https://issues.apache.org/jira/browse/CALCITE-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-4631:
---
Description:
e.g. the following test in RelToSqlConverter.java demonstrates the end-to-end
problem:
{code:java}
@Test void testTimestampDiff() {
final String query = "select timestampdiff(day, a.\"hire_date\",
b.\"hire_date\") "
+ "from \"employee\" a join \"employee\" b using (\"employee_id\")";
final String expected = "TBD, can't get it to generate right now";
sql(query).ok(expected);
}{code}
throws, top of stack:
{noformat}
class org.apache.calcite.sql.SqlSyntax$7: SPECIAL
java.lang.UnsupportedOperationException: class
org.apache.calcite.sql.SqlSyntax$7: SPECIAL
at org.apache.calcite.util.Util.needToImplement(Util.java:1101)
at org.apache.calcite.sql.SqlSyntax$7.unparse(SqlSyntax.java:129)
at org.apache.calcite.sql.SqlOperator.unparse(SqlOperator.java:383)
at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:455)
at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:123)
at org.apache.calcite.sql.SqlUtil.unparseBinarySyntax(SqlUtil.java:425)
at org.apache.calcite.sql.SqlSyntax$4.unparse(SqlSyntax.java:78)
at org.apache.calcite.sql.SqlOperator.unparse(SqlOperator.java:383)
at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:455)
at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:126)
at
org.apache.calcite.sql.fun.SqlCastFunction.unparse(SqlCastFunction.java:181)
at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:455)
at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:126)
at
org.apache.calcite.sql.pretty.SqlPrettyWriter$FrameImpl.list2(SqlPrettyWriter.java:1304)
at
org.apache.calcite.sql.pretty.SqlPrettyWriter$FrameImpl.list(SqlPrettyWriter.java:1284)
at
org.apache.calcite.sql.pretty.SqlPrettyWriter.list(SqlPrettyWriter.java:1081)
at
org.apache.calcite.sql.SqlSelectOperator.unparse(SqlSelectOperator.java:154)
at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:455)
at org.apache.calcite.sql.SqlSelect.unparse(SqlSelect.java:261)
at org.apache.calcite.sql.SqlNode.toSqlString(SqlNode.java:156)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.toSql(RelToSqlConverterTest.java:216){noformat}
>From the debugger, it looks like a SqlKind.REINTERPRET operator gets generated
>by the SqlToRelConverter, and nothing knows how to handle that unparsing to
>SQL.
was:
class org.apache.calcite.sql.SqlSyntax$7: SPECIAL
e.g. the following test in RelToSqlConverter.java demonstrates the end-to-end
problem:
{code:java}
@Test void testTimestampDiff() {
final String query = "select timestampdiff(day, a.\"hire_date\",
b.\"hire_date\") "
+ "from \"employee\" a join \"employee\" b using (\"employee_id\")";
final String expected = "TBD, can't get it to generate right now";
sql(query).ok(expected);
}{code}
throws, top of stack:
{noformat}
class org.apache.calcite.sql.SqlSyntax$7: SPECIAL
java.lang.UnsupportedOperationException: class
org.apache.calcite.sql.SqlSyntax$7: SPECIAL
at org.apache.calcite.util.Util.needToImplement(Util.java:1101)
at org.apache.calcite.sql.SqlSyntax$7.unparse(SqlSyntax.java:129)
at org.apache.calcite.sql.SqlOperator.unparse(SqlOperator.java:383)
at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:455)
at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:123)
at org.apache.calcite.sql.SqlUtil.unparseBinarySyntax(SqlUtil.java:425)
at org.apache.calcite.sql.SqlSyntax$4.unparse(SqlSyntax.java:78)
at org.apache.calcite.sql.SqlOperator.unparse(SqlOperator.java:383)
at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:455)
at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:126)
at
org.apache.calcite.sql.fun.SqlCastFunction.unparse(SqlCastFunction.java:181)
at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:455)
at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:126)
at
org.apache.calcite.sql.pretty.SqlPrettyWriter$FrameImpl.list2(SqlPrettyWriter.java:1304)
at
org.apache.calcite.sql.pretty.SqlPrettyWriter$FrameImpl.list(SqlPrettyWriter.java:1284)
at
org.apache.calcite.sql.pretty.SqlPrettyWriter.list(SqlPrettyWriter.java:1081)
at
org.apache.calcite.sql.SqlSelectOperator.unparse(SqlSelectOperator.java:154)
at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:455)
at org.apache.calcite.sql.SqlSelect.unparse(SqlSelect.java:261)
at org.apache.calcite.sql.SqlNode.toSqlString(SqlNode.java:156)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.toSql(RelToSqlConverterTest.java:216){noformat}
>From the debugger, it
[jira] [Created] (CALCITE-4631) sql->rel->sql bug with unparsing timestampdiff
Steven Talbot created CALCITE-4631:
--
Summary: sql->rel->sql bug with unparsing timestampdiff
Key: CALCITE-4631
URL: https://issues.apache.org/jira/browse/CALCITE-4631
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
class org.apache.calcite.sql.SqlSyntax$7: SPECIAL
e.g. the following test in RelToSqlConverter.java demonstrates the end-to-end
problem:
{code:java}
@Test void testTimestampDiff() {
final String query = "select timestampdiff(day, a.\"hire_date\",
b.\"hire_date\") "
+ "from \"employee\" a join \"employee\" b using (\"employee_id\")";
final String expected = "TBD, can't get it to generate right now";
sql(query).ok(expected);
}{code}
throws, top of stack:
{noformat}
class org.apache.calcite.sql.SqlSyntax$7: SPECIAL
java.lang.UnsupportedOperationException: class
org.apache.calcite.sql.SqlSyntax$7: SPECIAL
at org.apache.calcite.util.Util.needToImplement(Util.java:1101)
at org.apache.calcite.sql.SqlSyntax$7.unparse(SqlSyntax.java:129)
at org.apache.calcite.sql.SqlOperator.unparse(SqlOperator.java:383)
at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:455)
at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:123)
at org.apache.calcite.sql.SqlUtil.unparseBinarySyntax(SqlUtil.java:425)
at org.apache.calcite.sql.SqlSyntax$4.unparse(SqlSyntax.java:78)
at org.apache.calcite.sql.SqlOperator.unparse(SqlOperator.java:383)
at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:455)
at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:126)
at
org.apache.calcite.sql.fun.SqlCastFunction.unparse(SqlCastFunction.java:181)
at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:455)
at org.apache.calcite.sql.SqlCall.unparse(SqlCall.java:126)
at
org.apache.calcite.sql.pretty.SqlPrettyWriter$FrameImpl.list2(SqlPrettyWriter.java:1304)
at
org.apache.calcite.sql.pretty.SqlPrettyWriter$FrameImpl.list(SqlPrettyWriter.java:1284)
at
org.apache.calcite.sql.pretty.SqlPrettyWriter.list(SqlPrettyWriter.java:1081)
at
org.apache.calcite.sql.SqlSelectOperator.unparse(SqlSelectOperator.java:154)
at org.apache.calcite.sql.SqlDialect.unparseCall(SqlDialect.java:455)
at org.apache.calcite.sql.SqlSelect.unparse(SqlSelect.java:261)
at org.apache.calcite.sql.SqlNode.toSqlString(SqlNode.java:156)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.toSql(RelToSqlConverterTest.java:216){noformat}
>From the debugger, it looks like a SqlKind.REINTERPRET operator gets generated
>by the SqlToRelConverter, and nothing knows how to handle that unparsing to
>SQL.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4524) Make some fields non-nullable (SqlSelect.selectList, DataContext.getTypeFactory)
[
https://issues.apache.org/jira/browse/CALCITE-4524?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17355281#comment-17355281
]
Steven Talbot commented on CALCITE-4524:
[~julianhyde] I believe this fixed a bug. The following tweak of an existing
test in RelToSqlConverter before this fix blows up with the below stack trace
{code:java}
@Test void testValuesEmpty2() {
final String sql = "select *\n"
+ "from (values (1, 'a'), (2, 'bb')) as t(x, y)\n"
+ "limit 0";
final RuleSet rules =
RuleSets.ofList(PruneEmptyRules.SORT_FETCH_ZERO_INSTANCE);
final String sql2 = "SELECT SUBSTRING(y, 1, 1) FROM (" + sql + ") t";
final String expected = "SELECT SUBSTRING(`Y` FROM 1 FOR 1)\n" +
"FROM (SELECT NULL AS `X`, NULL AS `Y`) AS `t`\n" +
"WHERE 1 = 0";
sql(sql2).optimize(rules, null).withMysql().ok(expected);
}
{code}
{noformat}
java.lang.IndexOutOfBoundsException: Index: 1, Size:
1java.lang.IndexOutOfBoundsException: Index: 1, Size: 1 at
java.util.ArrayList.rangeCheck(ArrayList.java:657) at
java.util.ArrayList.get(ArrayList.java:433) at
org.apache.calcite.sql.SqlNodeList.get(SqlNodeList.java:154) at
org.apache.calcite.rel.rel2sql.SqlImplementor$Result$1.field(SqlImplementor.java:1720)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:698)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:1134)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.callToSql(SqlImplementor.java:853)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:829)
...{noformat}
Basically, the code in SqlImplementor$Result.field does (did) not handle the
explicit singleton star list. Of course, post this change, it now does. Do you
want a tracking bug?
> Make some fields non-nullable (SqlSelect.selectList,
> DataContext.getTypeFactory)
>
>
> Key: CALCITE-4524
> URL: https://issues.apache.org/jira/browse/CALCITE-4524
> Project: Calcite
> Issue Type: Bug
>Reporter: Julian Hyde
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.27.0
>
>
> Make some fields non-nullable: {{SqlSelect.selectList}},
> {{DataContext.getTypeFactory()}}, {{DataContext.getQueryProvider()}}.
> Add {{class DataContexts}}, with a few useful implementations of {{interface
> DataContext}}.
> Resolve some other TODOs relating to nullability.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4620) Join on CASE causes AssertionError in RelToSqlConverter
[
https://issues.apache.org/jira/browse/CALCITE-4620?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17351260#comment-17351260
]
Steven Talbot commented on CALCITE-4620:
I think that all makes sense. I certainly like taking out that giant switch
causing these problems. One thought [~julianhyde] should we not implement
SqlOperator.reverse() in terms of SqlKind.reverse(). Otherwise there are two
different sources of truth about what is "reversible". Though I wouldn't
exactly expect that list of reversible ops to change much, and maybe there's a
good reason for having it in both places.
> Join on CASE causes AssertionError in RelToSqlConverter
> ---
>
> Key: CALCITE-4620
> URL: https://issues.apache.org/jira/browse/CALCITE-4620
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Assignee: Julian Hyde
>Priority: Major
>
> Basically CALCITE-4610 but this time with a CASE WHEN.
> I can provide a repro test if needed, but any join with a condition like in
> that same test
> {code:java}
> CASE WHEN a = 'some_literal' THEN true ELSE c = 'some_other_literal' END{code}
> The CASE travels through the pipeline, and the RelToSqlConverter crashes on
> it in convertConditionToSqlNode.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4620) join on non-reducible CASE causes AssertionError in RelToSqlConverter
[
https://issues.apache.org/jira/browse/CALCITE-4620?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17350655#comment-17350655
]
Steven Talbot commented on CALCITE-4620:
Yep, I can repro, here's a test that blows up:
{code:java}
@Test void testCaseGeneratingJoinCondition() {
final String sql = "SELECT v1.deptno, v2.deptno\n"
+ "FROM dept v1 LEFT JOIN emp v2 ON CASE WHEN v2.job = 'PRESIDENT' THEN
true ELSE v1.deptno = 10 END\n"
+ "WHERE v2.job LIKE 'PRESIDENT'";
final String expected = "SELECT \"DEPT\".\"DEPTNO\","
+ " \"EMP\".\"DEPTNO\" AS \"DEPTNO0\"\n"
+ "FROM \"SCOTT\".\"DEPT\"\n"
+ "LEFT JOIN \"SCOTT\".\"EMP\""
+ " ON \"DEPT\".\"DEPTNO\" = \"EMP\".\"DEPTNO\"\n" // this would of
course change
+ "WHERE \"EMP\".\"JOB\" LIKE 'PRESIDENT'";
sql(sql)
.schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
.ok(expected);
}
{code}
Stack trace from master (fd6ffc90)
{noformat}
CASE(=($5, 'PRESIDENT'), true, =(CAST($0):INTEGER NOT NULL, 10))
java.lang.AssertionError: CASE(=($5, 'PRESIDENT'), true, =(CAST($0):INTEGER NOT
NULL, 10))
at
org.apache.calcite.rel.rel2sql.SqlImplementor.convertConditionToSqlNode(SqlImplementor.java:381)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:222)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:531)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:134)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:142)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:185){noformat}
What's interesting, and gave me pause, is that in the debugger I saw a similar
condition convert to an `IS_NOT_TRUE` instead of a CASE... but of course that
_also_ blows up in the RelToSqlConverter at that point.
It doesn't look like IS_TRUE/IS_NOT_TRUE/IS_FALSE/IS_NOT_FALSE are implemented
in that method either.
> join on non-reducible CASE causes AssertionError in RelToSqlConverter
> -
>
> Key: CALCITE-4620
> URL: https://issues.apache.org/jira/browse/CALCITE-4620
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> Basically CALCITE-4610 but this time with a CASE WHEN.
> I can provide a repro test if needed, but any join with a condition like in
> that same test
> {code:java}
> CASE WHEN a = 'some_literal' THEN true ELSE c = 'some_other_literal' END{code}
> The CASE travels through the pipeline, and the RelToSqlConverter crashes on
> it in convertConditionToSqlNode.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4620) join on non-reducible CASE causes AssertionError in RelToSqlConverter
[
https://issues.apache.org/jira/browse/CALCITE-4620?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17350652#comment-17350652
]
Steven Talbot commented on CALCITE-4620:
Actually, hold on, let me try to repro with a simple Calcite test to be sure
this is not something else.
> join on non-reducible CASE causes AssertionError in RelToSqlConverter
> -
>
> Key: CALCITE-4620
> URL: https://issues.apache.org/jira/browse/CALCITE-4620
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> Basically CALCITE-4610 but this time with a CASE WHEN.
> I can provide a repro test if needed, but any join with a condition like in
> that same test
> {code:java}
> CASE WHEN a = 'some_literal' THEN true ELSE c = 'some_other_literal' END{code}
> The CASE travels through the pipeline, and the RelToSqlConverter crashes on
> it in convertConditionToSqlNode.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4620) join on non-reducible CASE causes AssertionError in RelToSqlConverter
[
https://issues.apache.org/jira/browse/CALCITE-4620?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-4620:
---
Description:
Basically CALCITE-4610 but this time with a CASE WHEN.
I can provide a repro test if needed, but any join with a condition like in
that same test
{code:java}
CASE WHEN a = 'some_literal' THEN true ELSE c = 'some_other_literal' END{code}
The CASE travels through the pipeline, and the RelToSqlConverter crashes on it
in convertConditionToSqlNode.
was:
Basically CALCITE-4610 but this time with a CASE WHEN.
I can provide a repro test if needed, but any join with a condition like
{code:java}
CASE WHEN a = 'some_literal' THEN true ELSE c = 'some_other_literal' END{code}
The CASE travels through the pipeline, and the RelToSqlConverter crashes on it
in convertConditionToSqlNode.
> join on non-reducible CASE causes AssertionError in RelToSqlConverter
> -
>
> Key: CALCITE-4620
> URL: https://issues.apache.org/jira/browse/CALCITE-4620
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> Basically CALCITE-4610 but this time with a CASE WHEN.
> I can provide a repro test if needed, but any join with a condition like in
> that same test
> {code:java}
> CASE WHEN a = 'some_literal' THEN true ELSE c = 'some_other_literal' END{code}
> The CASE travels through the pipeline, and the RelToSqlConverter crashes on
> it in convertConditionToSqlNode.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4620) join on non-reducible CASE causes AssertionError in RelToSqlConverter
Steven Talbot created CALCITE-4620:
--
Summary: join on non-reducible CASE causes AssertionError in
RelToSqlConverter
Key: CALCITE-4620
URL: https://issues.apache.org/jira/browse/CALCITE-4620
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
Basically CALCITE-4610 but this time with a CASE WHEN.
I can provide a repro test if needed, but any join with a condition like
{code:java}
CASE WHEN a = 'some_literal' THEN true ELSE c = 'some_other_literal' END{code}
The CASE travels through the pipeline, and the RelToSqlConverter crashes on it
in convertConditionToSqlNode.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4610) Sarg in JOIN -> AssertionError in RelToSqlConverter
[
https://issues.apache.org/jira/browse/CALCITE-4610?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17347267#comment-17347267
]
Steven Talbot commented on CALCITE-4610:
I'm not intimately familiar with SqlImplementor, but that seems like a
reasonable enough fix to me.
> Sarg in JOIN -> AssertionError in RelToSqlConverter
> ---
>
> Key: CALCITE-4610
> URL: https://issues.apache.org/jira/browse/CALCITE-4610
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Steven Talbot
>Assignee: Julian Hyde
>Priority: Blocker
> Fix For: 1.27.0
>
>
>
> {code:java}
> @Test void testSargGeneratingJoinCondition() {
> final String sql = "SELECT v1.deptno, v2.deptno\n"
> + "FROM dept v1 LEFT JOIN emp v2 ON v1.deptno = v2.deptno AND v1.deptno
> < 15 AND v1.deptno > 10\n"
> + "WHERE v2.job LIKE 'PRESIDENT'";
> // note: not quite the right expected SQL, but the point is this test just
> blows up
> final String expected = "SELECT \"DEPT\".\"DEPTNO\","
> + " \"EMP\".\"DEPTNO\" AS \"DEPTNO0\"\n"
> + "FROM \"SCOTT\".\"DEPT\"\n"
> + "LEFT JOIN \"SCOTT\".\"EMP\""
> + " ON \"DEPT\".\"DEPTNO\" = \"EMP\".\"DEPTNO\"\n"
> + "WHERE \"EMP\".\"JOB\" LIKE 'PRESIDENT'";
> sql(sql)
> .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
> .ok(expected);
> }
> {code}
> ^^ test in RelToSqlConverterTest.java
> I'm not entirely sure where in the flow between the parse into RelNode and
> the conversion from RelNode->SqlNode the Sarg is supposed to be expanded, but
> clearly, by the time it hits the RelToSqlConverter, it's too late.
>
> Stack trace:
>
> {noformat}
> SEARCH($0, Sarg[(10..15)])SEARCH($0, Sarg[(10..15)])java.lang.AssertionError:
> SEARCH($0, Sarg[(10..15)]) at
> org.apache.calcite.rel.rel2sql.SqlImplementor.convertConditionToSqlNode(SqlImplementor.java:379)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.convertConditionToSqlNode(SqlImplementor.java:320)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:215)
> 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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:525) at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:131)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:139)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:186)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:180)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:326)
> 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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:525) at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:131)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:139)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:186)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:180)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:336)
> 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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:525) at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:131)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:139)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:186)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:174)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.visitRoot(SqlImplementor.java:154)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.toSql(RelToSqlConverterTest.java:197)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.access$300(RelToSqlConverterTest.java:99)
> at
>
[jira] [Created] (CALCITE-4610) Sarg in JOIN -> AssertionError in RelToSqlConverter
Steven Talbot created CALCITE-4610:
--
Summary: Sarg in JOIN -> AssertionError in RelToSqlConverter
Key: CALCITE-4610
URL: https://issues.apache.org/jira/browse/CALCITE-4610
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
{code:java}
@Test void testSargGeneratingJoinCondition() {
final String sql = "SELECT v1.deptno, v2.deptno\n"
+ "FROM dept v1 LEFT JOIN emp v2 ON v1.deptno = v2.deptno AND v1.deptno <
15 AND v1.deptno > 10\n"
+ "WHERE v2.job LIKE 'PRESIDENT'";
// note: not quite the right expected SQL, but the point is this test just
blows up
final String expected = "SELECT \"DEPT\".\"DEPTNO\","
+ " \"EMP\".\"DEPTNO\" AS \"DEPTNO0\"\n"
+ "FROM \"SCOTT\".\"DEPT\"\n"
+ "LEFT JOIN \"SCOTT\".\"EMP\""
+ " ON \"DEPT\".\"DEPTNO\" = \"EMP\".\"DEPTNO\"\n"
+ "WHERE \"EMP\".\"JOB\" LIKE 'PRESIDENT'";
sql(sql)
.schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
.ok(expected);
}
{code}
^^ test in RelToSqlConverterTest.java
I'm not entirely sure where in the flow between the parse into RelNode and the
conversion from RelNode->SqlNode the Sarg is supposed to be expanded, but
clearly, by the time it hits the RelToSqlConverter, it's too late.
Stack trace:
{noformat}
SEARCH($0, Sarg[(10..15)])SEARCH($0, Sarg[(10..15)])java.lang.AssertionError:
SEARCH($0, Sarg[(10..15)]) at
org.apache.calcite.rel.rel2sql.SqlImplementor.convertConditionToSqlNode(SqlImplementor.java:379)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.convertConditionToSqlNode(SqlImplementor.java:320)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:215)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:525) at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:131)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:139)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:186)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:180)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:326)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:525) at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:131)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:139)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:186)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:180)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:336)
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.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:525) at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:131)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:139)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:186)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitInput(SqlImplementor.java:174)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.visitRoot(SqlImplementor.java:154)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.toSql(RelToSqlConverterTest.java:197)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.access$300(RelToSqlConverterTest.java:99)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.exec(RelToSqlConverterTest.java:5723)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.ok(RelToSqlConverterTest.java:5693){noformat}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4495) Sarg simplification with IS_NULL and unknown type throws IllegalArgumentException
[
https://issues.apache.org/jira/browse/CALCITE-4495?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17283830#comment-17283830
]
Steven Talbot commented on CALCITE-4495:
[~julianhyde] I think RexLiteral.valueMatchesType must return true, no?
Otherwise that precondition would fail instead. In any case, the `value` is a
Sarg[false] and typeName is NULL (the type generated by
createUnknownType()).
On the other hand, now that you say that, it does look like a Sarg[false] with
a typeName NULL _should_ return false from valueMatchesType. Hm.
> Sarg simplification with IS_NULL and unknown type throws
> IllegalArgumentException
> -
>
> Key: CALCITE-4495
> URL: https://issues.apache.org/jira/browse/CALCITE-4495
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
> Fix For: 1.27.0
>
>
> Apologies. After spending an hour or so trying to write a simple repro for
> this I was unable to, so filing a bug for now so I have something to
> reference on my end.
> There is a condition in which this code explodes, here's the traceback:
> {noformat}
> Java::JavaLang::IllegalArgumentException:
> com.google.common.base.Preconditions.checkArgument(com/google/common/base/Preconditions.java:128)
>
> org.apache.calcite.rex.RexLiteral.(org/apache/calcite/rex/RexLiteral.java:221)
>
> org.apache.calcite.rex.RexBuilder.makeLiteral(org/apache/calcite/rex/RexBuilder.java:982)
>
> org.apache.calcite.rex.RexBuilder.makeSearchArgumentLiteral(org/apache/calcite/rex/RexBuilder.java:1077)
>
> org.apache.calcite.rex.RexSimplify$SargCollector.fix(org/apache/calcite/rex/RexSimplify.java:2719)
>
> org.apache.calcite.rex.RexSimplify.lambda$simplifyAnd$4(org/apache/calcite/rex/RexSimplify.java:1335)
> java.util.ArrayList.forEach(java/util/ArrayList.java:1257)
>
> org.apache.calcite.rex.RexSimplify.simplifyAnd(org/apache/calcite/rex/RexSimplify.java:1335)
>
> org.apache.calcite.rex.RexSimplify.simplify(org/apache/calcite/rex/RexSimplify.java:274)
>
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(org/apache/calcite/rex/RexSimplify.java:243)
>
> org.apache.calcite.rex.RexSimplify.simplifyUnknownAsFalse(org/apache/calcite/rex/RexSimplify.java:232)
>
> org.apache.calcite.plan.SubstitutionVisitor.splitFilter(org/apache/calcite/plan/SubstitutionVisitor.java:307)
>
> org.apache.calcite.rel.rules.materialize.MaterializedViewRule.computeCompensationPredicates(org/apache/calcite/rel/rules/materialize/MaterializedViewRule.java:834){noformat}
> It has _something_ to do with trying to calling into `splitFilter` with an
> OR(SEARCH\(x), IS_NULL\(x)) and a IS_NOT_NULL\(x), where x is an unknown type.
>
> Here is my failed attempt to make a repro case (in Kotlin, again sorry):
> {noformat}
> @Test
> fun testUnknownBadness() {
> val relBuilder = relBuilder()
> val unknownTypeFunc = object: SqlOperator("some_func", SqlKind.OTHER, 0,
> false, null, null, null) {
> override fun getSyntax(): SqlSyntax {
> return SqlSyntax.FUNCTION
> }
> override fun inferReturnType(opBinding: SqlOperatorBinding):
> RelDataType {
> return opBinding.typeFactory.createUnknownType()
> }
> }
> val boolFunc = object: SqlOperator("bool_func", SqlKind.OTHER, 0, false,
> ReturnTypes.BOOLEAN, null, null) {
> override fun getSyntax(): SqlSyntax {
> return SqlSyntax.FUNCTION
> }
> }
> relBuilder.values(listOf("age").toTypedArray(), 25)
> relBuilder.project(relBuilder.field("age"),
> relBuilder.call(unknownTypeFunc, relBuilder.field("age")))
> val ex = RexUtil.EXECUTOR
> val simpl = RexSimplify(relBuilder.rexBuilder, RelOptPredicateList.EMPTY,
> ex)
> SubstitutionVisitor.splitFilter(simpl,
> relBuilder.rexBuilder.makeCall(
> SqlStdOperatorTable.OR,
> relBuilder.call(SqlStdOperatorTable.IS_NULL, relBuilder.field(1)),
> relBuilder.call(boolFunc, relBuilder.field(1))
> ),
> relBuilder.call(SqlStdOperatorTable.IS_NOT_NULL, relBuilder.field(1)))
> }
> {noformat}
> Chatting with [~julianhyde], we have the strong suspicion this relates to
> CALCITE-4446, though I can't say for sure because I'm unable to boil it down
> right now into something simple.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4495) Sarg simplification with IS_NULL and unknown type throws IllegalArgumentException
[
https://issues.apache.org/jira/browse/CALCITE-4495?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-4495:
---
Description:
Apologies. After spending an hour or so trying to write a simple repro for this
I was unable to, so filing a bug for now so I have something to reference on my
end.
There is a condition in which this code explodes, here's the traceback:
{noformat}
Java::JavaLang::IllegalArgumentException:
com.google.common.base.Preconditions.checkArgument(com/google/common/base/Preconditions.java:128)
org.apache.calcite.rex.RexLiteral.(org/apache/calcite/rex/RexLiteral.java:221)
org.apache.calcite.rex.RexBuilder.makeLiteral(org/apache/calcite/rex/RexBuilder.java:982)
org.apache.calcite.rex.RexBuilder.makeSearchArgumentLiteral(org/apache/calcite/rex/RexBuilder.java:1077)
org.apache.calcite.rex.RexSimplify$SargCollector.fix(org/apache/calcite/rex/RexSimplify.java:2719)
org.apache.calcite.rex.RexSimplify.lambda$simplifyAnd$4(org/apache/calcite/rex/RexSimplify.java:1335)
java.util.ArrayList.forEach(java/util/ArrayList.java:1257)
org.apache.calcite.rex.RexSimplify.simplifyAnd(org/apache/calcite/rex/RexSimplify.java:1335)
org.apache.calcite.rex.RexSimplify.simplify(org/apache/calcite/rex/RexSimplify.java:274)
org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(org/apache/calcite/rex/RexSimplify.java:243)
org.apache.calcite.rex.RexSimplify.simplifyUnknownAsFalse(org/apache/calcite/rex/RexSimplify.java:232)
org.apache.calcite.plan.SubstitutionVisitor.splitFilter(org/apache/calcite/plan/SubstitutionVisitor.java:307)
org.apache.calcite.rel.rules.materialize.MaterializedViewRule.computeCompensationPredicates(org/apache/calcite/rel/rules/materialize/MaterializedViewRule.java:834){noformat}
It has _something_ to do with trying to calling into `splitFilter` with an
OR(SEARCH(x), IS_NULL(x)) and a IS_NOT_NULL(x), where x is an unknown type.
Here is my failed attempt to make a repro case (in Kotlin, again sorry):
{noformat}
@Test
fun testUnknownBadness() {
val relBuilder = relBuilder()
val unknownTypeFunc = object: SqlOperator("some_func", SqlKind.OTHER, 0,
false, null, null, null) {
override fun getSyntax(): SqlSyntax {
return SqlSyntax.FUNCTION
}
override fun inferReturnType(opBinding: SqlOperatorBinding):
RelDataType {
return opBinding.typeFactory.createUnknownType()
}
}
val boolFunc = object: SqlOperator("bool_func", SqlKind.OTHER, 0, false,
ReturnTypes.BOOLEAN, null, null) {
override fun getSyntax(): SqlSyntax {
return SqlSyntax.FUNCTION
}
}
relBuilder.values(listOf("age").toTypedArray(), 25)
relBuilder.project(relBuilder.field("age"),
relBuilder.call(unknownTypeFunc, relBuilder.field("age")))
val ex = RexUtil.EXECUTOR
val simpl = RexSimplify(relBuilder.rexBuilder, RelOptPredicateList.EMPTY,
ex)
SubstitutionVisitor.splitFilter(simpl,
relBuilder.rexBuilder.makeCall(
SqlStdOperatorTable.OR,
relBuilder.call(SqlStdOperatorTable.IS_NULL, relBuilder.field(1)),
relBuilder.call(boolFunc, relBuilder.field(1))
),
relBuilder.call(SqlStdOperatorTable.IS_NOT_NULL, relBuilder.field(1)))
}
{noformat}
Chatting with [~julianhyde], we have the strong suspicion this relates to
CALCITE-4446, though I can't say for sure because I'm unable to boil it down
right now into something simple.
was:
Apologies. After spending an hour or so trying to write a simple repro for this
I was unable to, so filing a bug for now so I have something to reference on my
end.
There is a condition in which this code explodes, here's the traceback:
{noformat}
Java::JavaLang::IllegalArgumentException:
com.google.common.base.Preconditions.checkArgument(com/google/common/base/Preconditions.java:128)
org.apache.calcite.rex.RexLiteral.(org/apache/calcite/rex/RexLiteral.java:221)
org.apache.calcite.rex.RexBuilder.makeLiteral(org/apache/calcite/rex/RexBuilder.java:982)
org.apache.calcite.rex.RexBuilder.makeSearchArgumentLiteral(org/apache/calcite/rex/RexBuilder.java:1077)
org.apache.calcite.rex.RexSimplify$SargCollector.fix(org/apache/calcite/rex/RexSimplify.java:2719)
org.apache.calcite.rex.RexSimplify.lambda$simplifyAnd$4(org/apache/calcite/rex/RexSimplify.java:1335)
java.util.ArrayList.forEach(java/util/ArrayList.java:1257)
org.apache.calcite.rex.RexSimplify.simplifyAnd(org/apache/calcite/rex/RexSimplify.java:1335)
org.apache.calcite.rex.RexSimplify.simplify(org/apache/calcite/rex/RexSimplify.java:274)
org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(org/apache/calcite/rex/RexSimplify.java:243)
org.apache.calcite.rex.RexSimplify.simplifyUnknownAsFalse(org/apache/calcite/rex/RexSimplify.java:232)
org.apache.calcite.plan.SubstitutionVisitor.splitFilter(org/apache/calcite/plan/SubstitutionVisitor.java:307)
[jira] [Created] (CALCITE-4495) Sarg simplification with IS_NULL and unknown type throws IllegalArgumentException
Steven Talbot created CALCITE-4495:
--
Summary: Sarg simplification with IS_NULL and unknown type throws
IllegalArgumentException
Key: CALCITE-4495
URL: https://issues.apache.org/jira/browse/CALCITE-4495
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
Apologies. After spending an hour or so trying to write a simple repro for this
I was unable to, so filing a bug for now so I have something to reference on my
end.
There is a condition in which this code explodes, here's the traceback:
{noformat}
Java::JavaLang::IllegalArgumentException:
com.google.common.base.Preconditions.checkArgument(com/google/common/base/Preconditions.java:128)
org.apache.calcite.rex.RexLiteral.(org/apache/calcite/rex/RexLiteral.java:221)
org.apache.calcite.rex.RexBuilder.makeLiteral(org/apache/calcite/rex/RexBuilder.java:982)
org.apache.calcite.rex.RexBuilder.makeSearchArgumentLiteral(org/apache/calcite/rex/RexBuilder.java:1077)
org.apache.calcite.rex.RexSimplify$SargCollector.fix(org/apache/calcite/rex/RexSimplify.java:2719)
org.apache.calcite.rex.RexSimplify.lambda$simplifyAnd$4(org/apache/calcite/rex/RexSimplify.java:1335)
java.util.ArrayList.forEach(java/util/ArrayList.java:1257)
org.apache.calcite.rex.RexSimplify.simplifyAnd(org/apache/calcite/rex/RexSimplify.java:1335)
org.apache.calcite.rex.RexSimplify.simplify(org/apache/calcite/rex/RexSimplify.java:274)
org.apache.calcite.rex.RexSimplify.simplifyUnknownAs(org/apache/calcite/rex/RexSimplify.java:243)
org.apache.calcite.rex.RexSimplify.simplifyUnknownAsFalse(org/apache/calcite/rex/RexSimplify.java:232)
org.apache.calcite.plan.SubstitutionVisitor.splitFilter(org/apache/calcite/plan/SubstitutionVisitor.java:307)
org.apache.calcite.rel.rules.materialize.MaterializedViewRule.computeCompensationPredicates(org/apache/calcite/rel/rules/materialize/MaterializedViewRule.java:834){noformat}
It has _something_ to do with trying to calling into `splitFilter` with an
OR(x, IS_NULL(x)) and a IS_NOT_NULL(x), where x is an unknown type.
Here is my failed attempt to make a repro case (in Kotlin, again sorry):
{noformat}
@Test
fun testUnknownBadness() {
val relBuilder = relBuilder()
val unknownTypeFunc = object: SqlOperator("some_func", SqlKind.OTHER, 0,
false, null, null, null) {
override fun getSyntax(): SqlSyntax {
return SqlSyntax.FUNCTION
}
override fun inferReturnType(opBinding: SqlOperatorBinding):
RelDataType {
return opBinding.typeFactory.createUnknownType()
}
}
val boolFunc = object: SqlOperator("bool_func", SqlKind.OTHER, 0, false,
ReturnTypes.BOOLEAN, null, null) {
override fun getSyntax(): SqlSyntax {
return SqlSyntax.FUNCTION
}
}
relBuilder.values(listOf("age").toTypedArray(), 25)
relBuilder.project(relBuilder.field("age"),
relBuilder.call(unknownTypeFunc, relBuilder.field("age")))
val ex = RexUtil.EXECUTOR
val simpl = RexSimplify(relBuilder.rexBuilder, RelOptPredicateList.EMPTY,
ex)
SubstitutionVisitor.splitFilter(simpl,
relBuilder.rexBuilder.makeCall(
SqlStdOperatorTable.OR,
relBuilder.call(SqlStdOperatorTable.IS_NULL, relBuilder.field(1)),
relBuilder.call(boolFunc, relBuilder.field(1))
),
relBuilder.call(SqlStdOperatorTable.IS_NOT_NULL, relBuilder.field(1)))
}
{noformat}
Chatting with [~julianhyde], we have the strong suspicion this relates to
CALCITE-4446, though I can't say for sure because I'm unable to boil it down
right now into something simple.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4249) Assertion error for NOT operator in join condition in Rel2SqlConverter
[
https://issues.apache.org/jira/browse/CALCITE-4249?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-4249:
---
Description:
The following test shows the bug if you put it in RelToSqlConverter
{code:java}
@Test void testJoinWithNotLikeConditionRel2Sql() {
final Function relFn = b -> b
.scan("EMP")
.scan("DEPT")
.join(JoinRelType.LEFT,
b.and(
b.call(SqlStdOperatorTable.EQUALS,
b.field(2, 0, "DEPTNO"),
b.field(2, 1, "DEPTNO")),
b.call(SqlStdOperatorTable.NOT,
b.call(SqlStdOperatorTable.LIKE,
b.field(2, 1, "DNAME"),
b.literal("ACCOUNTING_FOO"))
)
))
.build();
final String expectedSql = "SELECT *\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "LEFT JOIN \"scott\".\"DEPT\" "
+ "ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\" "
+ "AND \"DEPT\".\"DNAME\" NOT LIKE 'ACCOUNTING'";
relFn(relFn).ok(expectedSql);
}
{code}
It blows up with the following stacktrace top:
{noformat}
java.lang.AssertionError: NOT(LIKE($9, 'ACCOUNTING_FOO'))
at
org.apache.calcite.rel.rel2sql.SqlImplementor.convertConditionToSqlNode(SqlImplementor.java:350)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.convertConditionToSqlNode(SqlImplementor.java:286)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:213)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:524)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:131)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:139){noformat}
was:
The following test shows the bug if you put it in RelToSqlConverter
{code:java}
@Test void testJoinWithNotLikeConditionRel2Sql() {
final Function relFn = b -> b
.scan("EMP")
.scan("DEPT")
.join(JoinRelType.LEFT,
b.and(
b.call(SqlStdOperatorTable.EQUALS,
b.field(2, 0, "DEPTNO"),
b.field(2, 1, "DEPTNO")),
b.call(SqlStdOperatorTable.NOT,
b.call(SqlStdOperatorTable.LIKE,
b.field(2, 1, "DNAME"),
b.literal("ACCOUNTING_FOO"))
)
))
.build();
final String expectedSql = "SELECT *\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "LEFT JOIN \"scott\".\"DEPT\" "
+ "ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\" "
+ "AND \"DEPT\".\"DNAME\" NOT LIKE 'ACCOUNTING'";
relFn(relFn).ok(expectedSql);
}
{code}
It blows up with the following stacktrace
{noformat}
java.lang.AssertionError: NOT(LIKE($9, 'ACCOUNTING_FOO'))
at
org.apache.calcite.rel.rel2sql.SqlImplementor.convertConditionToSqlNode(SqlImplementor.java:350)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.convertConditionToSqlNode(SqlImplementor.java:286)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:213)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:524)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:131)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:139){noformat}
> Assertion error for NOT operator in join condition in Rel2SqlConverter
> --
>
> Key: CALCITE-4249
> URL: https://issues.apache.org/jira/browse/CALCITE-4249
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> The following test shows the bug if you put it in RelToSqlConverter
> {code:java}
> @Test void testJoinWithNotLikeConditionRel2Sql() {
> final Function relFn = b -> b
> .scan("EMP")
> .scan("DEPT")
> .join(JoinRelType.LEFT,
> b.and(
> b.call(SqlStdOperatorTable.EQUALS,
> b.field(2, 0, "DEPTNO"),
> b.field(2, 1, "DEPTNO")),
> b.call(SqlStdOperatorTable.NOT,
> b.call(SqlStdOperatorTable.LIKE,
> b.field(2, 1, "DNAME"),
> b.literal("ACCOUNTING_FOO"))
> )
> ))
> .build();
> final String expectedSql = "SELECT *\n"
> + "FROM \"scott\".\"EMP\"\n"
> + "LEFT JOIN \"scott\".\"DEPT\" "
> + "ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\" "
> + "AND \"DEPT\".\"DNAME\" NOT LIKE 'ACCOUNTING'";
> relFn(relFn).ok(expectedSql);
> }
> {code}
> It blows up with the following stacktrace top:
> {noformat}
> java.lang.AssertionError: NOT(LIKE($9, 'ACCOUNTING_FOO'))
> at
>
[jira] [Commented] (CALCITE-4249) Assertion error for NOT operator in Rel2SqlConverter
[
https://issues.apache.org/jira/browse/CALCITE-4249?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17194546#comment-17194546
]
Steven Talbot commented on CALCITE-4249:
Apologies if this is a dup (many things come up when you search for
"assertionerror" and "not").
> Assertion error for NOT operator in Rel2SqlConverter
>
>
> Key: CALCITE-4249
> URL: https://issues.apache.org/jira/browse/CALCITE-4249
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> The following test shows the bug if you put it in RelToSqlConverter
> {code:java}
> @Test void testJoinWithNotLikeConditionRel2Sql() {
> final Function relFn = b -> b
> .scan("EMP")
> .scan("DEPT")
> .join(JoinRelType.LEFT,
> b.and(
> b.call(SqlStdOperatorTable.EQUALS,
> b.field(2, 0, "DEPTNO"),
> b.field(2, 1, "DEPTNO")),
> b.call(SqlStdOperatorTable.NOT,
> b.call(SqlStdOperatorTable.LIKE,
> b.field(2, 1, "DNAME"),
> b.literal("ACCOUNTING_FOO"))
> )
> ))
> .build();
> final String expectedSql = "SELECT *\n"
> + "FROM \"scott\".\"EMP\"\n"
> + "LEFT JOIN \"scott\".\"DEPT\" "
> + "ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\" "
> + "AND \"DEPT\".\"DNAME\" NOT LIKE 'ACCOUNTING'";
> relFn(relFn).ok(expectedSql);
> }
> {code}
> It blows up with the following stacktrace
> {noformat}
> java.lang.AssertionError: NOT(LIKE($9, 'ACCOUNTING_FOO'))
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.convertConditionToSqlNode(SqlImplementor.java:350)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.convertConditionToSqlNode(SqlImplementor.java:286)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:213)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:524)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:131)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:139){noformat}
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-4249) Assertion error for NOT operator in join condition in Rel2SqlConverter
[
https://issues.apache.org/jira/browse/CALCITE-4249?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-4249:
---
Summary: Assertion error for NOT operator in join condition in
Rel2SqlConverter (was: Assertion error for NOT operator in Rel2SqlConverter)
> Assertion error for NOT operator in join condition in Rel2SqlConverter
> --
>
> Key: CALCITE-4249
> URL: https://issues.apache.org/jira/browse/CALCITE-4249
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> The following test shows the bug if you put it in RelToSqlConverter
> {code:java}
> @Test void testJoinWithNotLikeConditionRel2Sql() {
> final Function relFn = b -> b
> .scan("EMP")
> .scan("DEPT")
> .join(JoinRelType.LEFT,
> b.and(
> b.call(SqlStdOperatorTable.EQUALS,
> b.field(2, 0, "DEPTNO"),
> b.field(2, 1, "DEPTNO")),
> b.call(SqlStdOperatorTable.NOT,
> b.call(SqlStdOperatorTable.LIKE,
> b.field(2, 1, "DNAME"),
> b.literal("ACCOUNTING_FOO"))
> )
> ))
> .build();
> final String expectedSql = "SELECT *\n"
> + "FROM \"scott\".\"EMP\"\n"
> + "LEFT JOIN \"scott\".\"DEPT\" "
> + "ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\" "
> + "AND \"DEPT\".\"DNAME\" NOT LIKE 'ACCOUNTING'";
> relFn(relFn).ok(expectedSql);
> }
> {code}
> It blows up with the following stacktrace
> {noformat}
> java.lang.AssertionError: NOT(LIKE($9, 'ACCOUNTING_FOO'))
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.convertConditionToSqlNode(SqlImplementor.java:350)
> at
> org.apache.calcite.rel.rel2sql.SqlImplementor.convertConditionToSqlNode(SqlImplementor.java:286)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:213)
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:524)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:131)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:139){noformat}
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4249) Assertion error for NOT operator in Rel2SqlConverter
Steven Talbot created CALCITE-4249:
--
Summary: Assertion error for NOT operator in Rel2SqlConverter
Key: CALCITE-4249
URL: https://issues.apache.org/jira/browse/CALCITE-4249
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
The following test shows the bug if you put it in RelToSqlConverter
{code:java}
@Test void testJoinWithNotLikeConditionRel2Sql() {
final Function relFn = b -> b
.scan("EMP")
.scan("DEPT")
.join(JoinRelType.LEFT,
b.and(
b.call(SqlStdOperatorTable.EQUALS,
b.field(2, 0, "DEPTNO"),
b.field(2, 1, "DEPTNO")),
b.call(SqlStdOperatorTable.NOT,
b.call(SqlStdOperatorTable.LIKE,
b.field(2, 1, "DNAME"),
b.literal("ACCOUNTING_FOO"))
)
))
.build();
final String expectedSql = "SELECT *\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "LEFT JOIN \"scott\".\"DEPT\" "
+ "ON \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\" "
+ "AND \"DEPT\".\"DNAME\" NOT LIKE 'ACCOUNTING'";
relFn(relFn).ok(expectedSql);
}
{code}
It blows up with the following stacktrace
{noformat}
java.lang.AssertionError: NOT(LIKE($9, 'ACCOUNTING_FOO'))
at
org.apache.calcite.rel.rel2sql.SqlImplementor.convertConditionToSqlNode(SqlImplementor.java:350)
at
org.apache.calcite.rel.rel2sql.SqlImplementor.convertConditionToSqlNode(SqlImplementor.java:286)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:213)
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:524)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:131)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitInput(RelToSqlConverter.java:139){noformat}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4209) RelBuilder: ability to avoid generating empty() on limit 0
Steven Talbot created CALCITE-4209:
--
Summary: RelBuilder: ability to avoid generating empty() on limit 0
Key: CALCITE-4209
URL: https://issues.apache.org/jira/browse/CALCITE-4209
Project: Calcite
Issue Type: Improvement
Reporter: Steven Talbot
When you call
{code:java}
relBuilder.limit(0, 0){code}
It triggers a call to RelBuilder.empty at
[https://github.com/apache/calcite/blob/88d18185e6177c9df587bdd23dd4049f59adc2e4/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L2531].
This is fine as the default behavior, but for cases where we intend to convert
the relational algebra to SQL and display it somewhere, it would be preferable
to turn this behavior off, either as an extra flag to this method or a
configuration parameter. The call to empty() often results in a VALUES or a
select list of literal NULLs, which looks ugly and can confuse a user.
Moreover, there are certain databases (like BigQuery) that can cheaply handle a
LIMIT 0 query as a form of validation, and if we munge the query with 'empty()'
we lose the ability to validate its correctness by going to the DB.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4165) RelToSqlConverter fails on literal, untyped nulls
Steven Talbot created CALCITE-4165:
--
Summary: RelToSqlConverter fails on literal, untyped nulls
Key: CALCITE-4165
URL: https://issues.apache.org/jira/browse/CALCITE-4165
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
Simple test in RelToSqlConverterTest (the expected SQL is probably not exactly
the right expected SQL, but the test explodes on 1.24 so it doesn't matter for
our purposes):
{code:java}
@Test void testUntypedNull() {
final String sql = "SELECT NULL FROM \"warehouse_class\"";
final String expected = "SELECT NULL FROM \"warehouse_class\"";
sql(sql).ok(expected);
}{code}
stack trace top (on current master ad53962f76a48e2627da1c155eb5f005150d3dcb)
{noformat}
Unsupported type when convertTypeToSpec: NULLUnsupported type when
convertTypeToSpec: NULL
java.lang.UnsupportedOperationException: Unsupported type when
convertTypeToSpec: NULL at
org.apache.calcite.sql.type.SqlTypeUtil.convertTypeToSpec(SqlTypeUtil.java:1027)
at org.apache.calcite.sql.SqlDialect.getCastSpec(SqlDialect.java:778)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.castNullType(RelToSqlConverter.java:361){noformat}
Seems to be an unintended side-effect of
[https://github.com/apache/calcite/commit/8db7c9d6ddc317b5fee8705e136c0794c44b463a#diff-fe53d9c0364e000f0aeafa786587e815R1079]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4159) RexSimplify should simplify more always true OR expressions
Steven Talbot created CALCITE-4159:
--
Summary: RexSimplify should simplify more always true OR
expressions
Key: CALCITE-4159
URL: https://issues.apache.org/jira/browse/CALCITE-4159
Project: Calcite
Issue Type: Improvement
Reporter: Steven Talbot
The following filters for a number are always true
{noformat}
x > 0 or x <= 0 or x is null
x < 0 or x >= 0 or x is null
x < 1 or x > 0 or x is null{noformat}
where the 1/0 can be replaced by any number where the first is greater than the
second
The following filter for a varchar is always true:
{noformat}
x LIKE '%' OR x is null{noformat}
It would be nice if RexSimplify.simplifyOrTerms handled these cases.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4148) MaterializedViewAggregateRule invalid optimization with time unit "rollupable" column in query
Steven Talbot created CALCITE-4148:
--
Summary: MaterializedViewAggregateRule invalid optimization with
time unit "rollupable" column in query
Key: CALCITE-4148
URL: https://issues.apache.org/jira/browse/CALCITE-4148
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
The issue occurs when you have a time unit in your query and no group fields in
your view aggregate, and the view aggregate sits directly on top of a TableScan.
Here is a test that is almost a regression test in MaterializedViewRelOptRules
test:
{code:java}
@Test void testAggregateMaterializationAggregateFuncsNoInvalidMatch() {
sql("select "
+ "count(*) as c\n"
+ "from \"events\"\n",
"select floor(cast(\"ts\" as timestamp) to minute), count(*)\n"
+ "from \"events\" group by floor(cast(\"ts\" as timestamp) to
minute)")
.noMat();
}
{code}
However, this test passes in current code. The reason is that the SQL for the
materialization is converted to
{noformat}
LogicalAggregate(group=[{}], C=[COUNT()])
LogicalProject($f0=[0])
LogicalTableScan(table=[[hr, events]])
{noformat}
with the interleaved LogicalProject projecting an unused constant.
Without that logical project (from what I saw hitting the bug in my code), what
happens is that MaterializedViewAggregateRule.generateMapping gets the input,
in this case the table, sees that the input contains the field (in this case
"ts") that can be rolled up into the floor, and maps it. Downstream code then
mishandles that unexpected mapping, and optimization occurs where it should not.
It seems as though this rule generally expects the input to the view aggregate
to be a project that contains only fields the aggregate will group by or
aggregate. I can make my code comply with that paradigm, but still it seems
this is worth fixing.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-4090) DB2 aliasing breaks with a complex select above a subselect
[
https://issues.apache.org/jira/browse/CALCITE-4090?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17144498#comment-17144498
]
Steven Talbot edited comment on CALCITE-4090 at 6/24/20, 11:52 PM:
---
There is a related case that is important to have a test for, because I hit it
even after making what seemed like an obvious quick fix for above:
{code:java}
@Test void testDb2DialectSubselectFromUnion() {
String query = "select count(foo), \"units_per_case\" "
+ "from (select \"units_per_case\", \"cases_per_pallet\", \"product_id\",
1 as foo from \"product\" where \"cases_per_pallet\" > 100 "
+ "union all select \"units_per_case\", \"cases_per_pallet\",
\"product_id\", 1 as foo from \"product\" where \"cases_per_pallet\" < 100) "
+ "where \"cases_per_pallet\" > 100\n"
+ "group by \"product_id\", \"units_per_case\" "
+ "order by \"units_per_case\" desc";
final String expected = "SELECT COUNT(*), t3.units_per_case\n" +
"FROM (SELECT product.units_per_case, product.cases_per_pallet,
product.product_id, 1 AS " +
"FOO\n" +
"FROM foodmart.product AS product\n" +
"WHERE product.cases_per_pallet > 100\n" +
"UNION ALL\n" +
"SELECT product0.units_per_case, product0.cases_per_pallet,
product0.product_id, 1 AS " +
"FOO\n" +
"FROM foodmart.product AS product0\n" +
"WHERE product0.cases_per_pallet < 100) AS t3\n" +
"WHERE t3.cases_per_pallet > 100\n" +
"GROUP BY t3.product_id, t3.units_per_case\n" +
"ORDER BY t3.units_per_case DESC";
sql(query).withDb2().ok(expected);
}
{code}
was (Author: swtalbot):
There is a related case that is important to have a test for, because I hit it
even after making what seemed like an obvious quick fix for above:
{code:java}
@Test void testDb2DialectSubselectFromUnion() {
String query = "select count(foo), \"units_per_case\" "
+ "from (select \"units_per_case\", \"cases_per_pallet\", \"product_id\",
1 as foo from \"product\" where \"cases_per_pallet\" > 100 "
+ "union all select \"units_per_case\", \"cases_per_pallet\",
\"product_id\", 1 as foo from \"product\" where \"cases_per_pallet\" < 100) "
+ "where \"cases_per_pallet\" > 100\n"
+ "group by \"product_id\", \"units_per_case\" "
+ "order by \"units_per_case\" desc";
final String expected = "SELECT COUNT(*), t.units_per_case\n" +
"FROM (SELECT product.units_per_case, product.cases_per_pallet,
product.product_id, 1 AS " +
"FOO\n" +
"FROM foodmart.product AS product) AS t\n" +
"WHERE t.cases_per_pallet > 100\n" +
"GROUP BY t.product_id, t.units_per_case\n" +
"ORDER BY t.units_per_case DESC";
sql(query).withDb2().ok(expected);
}
{code}
> DB2 aliasing breaks with a complex select above a subselect
> ---
>
> Key: CALCITE-4090
> URL: https://issues.apache.org/jira/browse/CALCITE-4090
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> Test in RelToSqlConverterTest:
> {code:java}
> @Test void testDb2DialectSubselect() {
> String query = "select count(foo), \"units_per_case\" "
> + "from (select \"units_per_case\", \"cases_per_pallet\",
> \"product_id\", 1 as foo from \"product\") where \"cases_per_pallet\" > 100 "
> + "group by \"product_id\", \"units_per_case\" "
> + "order by \"units_per_case\" desc";
> final String expected = "SELECT COUNT(*), t.units_per_case\n" +
> "FROM (SELECT product.units_per_case, product.cases_per_pallet,
> product.product_id, 1 AS " +
> "FOO\n" +
> "FROM foodmart.product AS product) AS t\n" +
> "WHERE t.cases_per_pallet > 100\n" +
> "GROUP BY t.product_id, t.units_per_case\n" +
> "ORDER BY t.units_per_case DESC";
> sql(query).withDb2().ok(expected);
> }
> {code}
> The test fails with the "t." alias qualifier in the group by/order by/main
> select actually being "t0.".
> From stepping through the code in the debugger, I believe this is a general
> problem with the way aliases are calculated in situations like this by
> SqlImplementor, but other dialects with hasImplicitTableAlias() do not force
> qualified contexts and therefore do not hit this.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-4090) DB2 aliasing breaks with a complex select above a subselect
[
https://issues.apache.org/jira/browse/CALCITE-4090?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17144498#comment-17144498
]
Steven Talbot commented on CALCITE-4090:
There is a related case that is important to have a test for, because I hit it
even after making what seemed like an obvious quick fix for above:
{code:java}
@Test void testDb2DialectSubselectFromUnion() {
String query = "select count(foo), \"units_per_case\" "
+ "from (select \"units_per_case\", \"cases_per_pallet\", \"product_id\",
1 as foo from \"product\" where \"cases_per_pallet\" > 100 "
+ "union all select \"units_per_case\", \"cases_per_pallet\",
\"product_id\", 1 as foo from \"product\" where \"cases_per_pallet\" < 100) "
+ "where \"cases_per_pallet\" > 100\n"
+ "group by \"product_id\", \"units_per_case\" "
+ "order by \"units_per_case\" desc";
final String expected = "SELECT COUNT(*), t.units_per_case\n" +
"FROM (SELECT product.units_per_case, product.cases_per_pallet,
product.product_id, 1 AS " +
"FOO\n" +
"FROM foodmart.product AS product) AS t\n" +
"WHERE t.cases_per_pallet > 100\n" +
"GROUP BY t.product_id, t.units_per_case\n" +
"ORDER BY t.units_per_case DESC";
sql(query).withDb2().ok(expected);
}
{code}
> DB2 aliasing breaks with a complex select above a subselect
> ---
>
> Key: CALCITE-4090
> URL: https://issues.apache.org/jira/browse/CALCITE-4090
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> Test in RelToSqlConverterTest:
> {code:java}
> @Test void testDb2DialectSubselect() {
> String query = "select count(foo), \"units_per_case\" "
> + "from (select \"units_per_case\", \"cases_per_pallet\",
> \"product_id\", 1 as foo from \"product\") where \"cases_per_pallet\" > 100 "
> + "group by \"product_id\", \"units_per_case\" "
> + "order by \"units_per_case\" desc";
> final String expected = "SELECT COUNT(*), t.units_per_case\n" +
> "FROM (SELECT product.units_per_case, product.cases_per_pallet,
> product.product_id, 1 AS " +
> "FOO\n" +
> "FROM foodmart.product AS product) AS t\n" +
> "WHERE t.cases_per_pallet > 100\n" +
> "GROUP BY t.product_id, t.units_per_case\n" +
> "ORDER BY t.units_per_case DESC";
> sql(query).withDb2().ok(expected);
> }
> {code}
> The test fails with the "t." alias qualifier in the group by/order by/main
> select actually being "t0.".
> From stepping through the code in the debugger, I believe this is a general
> problem with the way aliases are calculated in situations like this by
> SqlImplementor, but other dialects with hasImplicitTableAlias() do not force
> qualified contexts and therefore do not hit this.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4090) DB2 aliasing breaks with a complex select above a subselect
Steven Talbot created CALCITE-4090:
--
Summary: DB2 aliasing breaks with a complex select above a
subselect
Key: CALCITE-4090
URL: https://issues.apache.org/jira/browse/CALCITE-4090
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
Test in RelToSqlConverterTest:
{code:java}
@Test void testDb2DialectSubselect() {
String query = "select count(foo), \"units_per_case\" "
+ "from (select \"units_per_case\", \"cases_per_pallet\", \"product_id\",
1 as foo from \"product\") where \"cases_per_pallet\" > 100 "
+ "group by \"product_id\", \"units_per_case\" "
+ "order by \"units_per_case\" desc";
final String expected = "SELECT COUNT(*), t.units_per_case\n" +
"FROM (SELECT product.units_per_case, product.cases_per_pallet,
product.product_id, 1 AS " +
"FOO\n" +
"FROM foodmart.product AS product) AS t\n" +
"WHERE t.cases_per_pallet > 100\n" +
"GROUP BY t.product_id, t.units_per_case\n" +
"ORDER BY t.units_per_case DESC";
sql(query).withDb2().ok(expected);
}
{code}
The test fails with the "t." alias qualifier in the group by/order by/main
select actually being "t0.".
>From stepping through the code in the debugger, I believe this is a general
>problem with the way aliases are calculated in situations like this by
>SqlImplementor, but other dialects with hasImplicitTableAlias() do not force
>qualified contexts and therefore do not hit this.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-4081) Round-tripping a DECIMAL literal through Planner -> RelToSqlConverter -> Planner throws validation error
Steven Talbot created CALCITE-4081:
--
Summary: Round-tripping a DECIMAL literal through Planner ->
RelToSqlConverter -> Planner throws validation error
Key: CALCITE-4081
URL: https://issues.apache.org/jira/browse/CALCITE-4081
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
Take the following SQL:
SELECT CAST('.11' AS DECIMAL(38,6)) AS "num"
And, in the default Calcite SQL dialect with all defaults, send it through
Planner parse -> valiate -> rel to get a RelNode. The RelNode will have a
Project with the "111.1" literal in there as a RexLiteral with
Decimal type.
The use RelToSqlConverter to convert that RelNode back to SQL. The SQL now
looks like
SELECT .11 AS "num"
If you attempt to parse and validate that SQL, you get "Numeric literal
'.11' out of range" throw by
SqlValidatorImpl.validateLiteral()
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3975) ProjectFilterTransposeRule should succeed for project that happens to reference all input columns
[
https://issues.apache.org/jira/browse/CALCITE-3975?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17121395#comment-17121395
]
Steven Talbot edited comment on CALCITE-3975 at 6/1/20, 11:13 PM:
--
I see. That all makes sense. To me at first, before we got deep into this, the
most straightforward reading of what "ProjectFilterTransposeRule" would do, in
terms of behaving similarly to other "transpose" rules, would be to transpose
only if the filter was on an expression in the Project, and otherwise do
nothing. In other words, something like
{noformat}
SELECT sal + comm AS remuneration, depto
FROM Emp
WHERE deptno = 10{noformat}
becomes
{noformat}
SELECT *
FROM (
SELECT sal + comm AS remuneration, deptno AS predicate
FROM Emp)
WHERE deptno = 10{noformat}
and then other expressions like yours above that do not have such properties
get left alone.
But I see now that would be a pretty radically different rule.
was (Author: swtalbot):
I see. That all makes sense. To me at first, before we got into this, the most
straightforward reading of what "ProjectFilterTransposeRule" would do, in terms
of behaving similarly to other "transpose" rules, would be to transpose only if
the filter was on an expression in the Project, and otherwise do nothing. In
other words, something like
{noformat}
SELECT sal + comm AS remuneration, depto
FROM Emp
WHERE deptno = 10{noformat}
becomes
{noformat}
SELECT *
FROM (
SELECT sal + comm AS remuneration, deptno AS predicate
FROM Emp)
WHERE deptno = 10{noformat}
and then other expressions like yours above that do not have such properties
get left alone.
But I see now that would be a pretty radically different rule.
> ProjectFilterTransposeRule should succeed for project that happens to
> reference all input columns
> -
>
> Key: CALCITE-3975
> URL: https://issues.apache.org/jira/browse/CALCITE-3975
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.22.0
>Reporter: Steven Talbot
>Assignee: Julian Hyde
>Priority: Major
>
> ... that is, I think
> If I make the trivial fix of just "only skip trivial projects", something
> like
> {noformat}
> && origProj.getProjects().stream().allMatch((proj) -> proj instanceof
> RexInputRef) {noformat}
> at
> [https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354
>
> |https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354]HepPlanner
> goes into infinite recursion with the rule.
> But here's the test case:
>
> {code:java}
> @Test public void testPushProjectPastFilter3() {
> final String sql = "select empno + deptno, ename, job, mgr, hiredate, sal,
> comm, slacker from emp where sal = 10 * comm\n"
> + "and upper(ename) = 'FOO'";
> sql(sql).withRule(ProjectFilterTransposeRule.INSTANCE).check();
> }
> {code}
>
>
>
> {noformat}
>
>
>
>
>
>
>
>
>
>
> {noformat}
>
> There's no reason the rule shouldn't succeed here, right? Or am I missing
> something?
> The reason this rule goes into an infinite recursion with hepplanner is
> because it sticks a project on top after transpose to handle common
> expressions extracted from the filter and the project. Ideally, it could have
> a mode where it could avoid doing that and do a true "transpose" if there was
> no need for it. For example, I don't think there is a a need for a reproject
> on top in this test case: you can just transpose and everything works as it
> should. This would be another way to avoid infinite recursion.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3975) ProjectFilterTransposeRule should succeed for project that happens to reference all input columns
[
https://issues.apache.org/jira/browse/CALCITE-3975?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17121395#comment-17121395
]
Steven Talbot commented on CALCITE-3975:
I see. That all makes sense. To me at first, before we got into this, the most
straightforward reading of what "ProjectFilterTransposeRule" would do, in terms
of behaving similarly to other "transpose" rules, would be to transpose only if
the filter was on an expression in the Project, and otherwise do nothing. In
other words, something like
SELECT sal + comm AS remuneration, depto
FROM Emp
WHERE deptno = 10
becomes
SELECT *
FROM (
SELECT sal + comm AS remuneration, deptno AS predicate
FROM Emp)
WHERE deptno = 10
and then other expressions like yours above that do not have such properties
get left alone.
But I see now that would be a pretty radically different rule.
> ProjectFilterTransposeRule should succeed for project that happens to
> reference all input columns
> -
>
> Key: CALCITE-3975
> URL: https://issues.apache.org/jira/browse/CALCITE-3975
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.22.0
>Reporter: Steven Talbot
>Assignee: Julian Hyde
>Priority: Major
>
> ... that is, I think
> If I make the trivial fix of just "only skip trivial projects", something
> like
> {noformat}
> && origProj.getProjects().stream().allMatch((proj) -> proj instanceof
> RexInputRef) {noformat}
> at
> [https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354
>
> |https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354]HepPlanner
> goes into infinite recursion with the rule.
> But here's the test case:
>
> {code:java}
> @Test public void testPushProjectPastFilter3() {
> final String sql = "select empno + deptno, ename, job, mgr, hiredate, sal,
> comm, slacker from emp where sal = 10 * comm\n"
> + "and upper(ename) = 'FOO'";
> sql(sql).withRule(ProjectFilterTransposeRule.INSTANCE).check();
> }
> {code}
>
>
>
> {noformat}
>
>
>
>
>
>
>
>
>
>
> {noformat}
>
> There's no reason the rule shouldn't succeed here, right? Or am I missing
> something?
> The reason this rule goes into an infinite recursion with hepplanner is
> because it sticks a project on top after transpose to handle common
> expressions extracted from the filter and the project. Ideally, it could have
> a mode where it could avoid doing that and do a true "transpose" if there was
> no need for it. For example, I don't think there is a a need for a reproject
> on top in this test case: you can just transpose and everything works as it
> should. This would be another way to avoid infinite recursion.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3975) ProjectFilterTransposeRule should succeed for project that happens to reference all input columns
[
https://issues.apache.org/jira/browse/CALCITE-3975?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17121395#comment-17121395
]
Steven Talbot edited comment on CALCITE-3975 at 6/1/20, 11:13 PM:
--
I see. That all makes sense. To me at first, before we got into this, the most
straightforward reading of what "ProjectFilterTransposeRule" would do, in terms
of behaving similarly to other "transpose" rules, would be to transpose only if
the filter was on an expression in the Project, and otherwise do nothing. In
other words, something like
{noformat}
SELECT sal + comm AS remuneration, depto
FROM Emp
WHERE deptno = 10{noformat}
becomes
{noformat}
SELECT *
FROM (
SELECT sal + comm AS remuneration, deptno AS predicate
FROM Emp)
WHERE deptno = 10{noformat}
and then other expressions like yours above that do not have such properties
get left alone.
But I see now that would be a pretty radically different rule.
was (Author: swtalbot):
I see. That all makes sense. To me at first, before we got into this, the most
straightforward reading of what "ProjectFilterTransposeRule" would do, in terms
of behaving similarly to other "transpose" rules, would be to transpose only if
the filter was on an expression in the Project, and otherwise do nothing. In
other words, something like
SELECT sal + comm AS remuneration, depto
FROM Emp
WHERE deptno = 10
becomes
SELECT *
FROM (
SELECT sal + comm AS remuneration, deptno AS predicate
FROM Emp)
WHERE deptno = 10
and then other expressions like yours above that do not have such properties
get left alone.
But I see now that would be a pretty radically different rule.
> ProjectFilterTransposeRule should succeed for project that happens to
> reference all input columns
> -
>
> Key: CALCITE-3975
> URL: https://issues.apache.org/jira/browse/CALCITE-3975
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.22.0
>Reporter: Steven Talbot
>Assignee: Julian Hyde
>Priority: Major
>
> ... that is, I think
> If I make the trivial fix of just "only skip trivial projects", something
> like
> {noformat}
> && origProj.getProjects().stream().allMatch((proj) -> proj instanceof
> RexInputRef) {noformat}
> at
> [https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354
>
> |https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354]HepPlanner
> goes into infinite recursion with the rule.
> But here's the test case:
>
> {code:java}
> @Test public void testPushProjectPastFilter3() {
> final String sql = "select empno + deptno, ename, job, mgr, hiredate, sal,
> comm, slacker from emp where sal = 10 * comm\n"
> + "and upper(ename) = 'FOO'";
> sql(sql).withRule(ProjectFilterTransposeRule.INSTANCE).check();
> }
> {code}
>
>
>
> {noformat}
>
>
>
>
>
>
>
>
>
>
> {noformat}
>
> There's no reason the rule shouldn't succeed here, right? Or am I missing
> something?
> The reason this rule goes into an infinite recursion with hepplanner is
> because it sticks a project on top after transpose to handle common
> expressions extracted from the filter and the project. Ideally, it could have
> a mode where it could avoid doing that and do a true "transpose" if there was
> no need for it. For example, I don't think there is a a need for a reproject
> on top in this test case: you can just transpose and everything works as it
> should. This would be another way to avoid infinite recursion.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-2997) Avoid pushing down join condition in SqlToRelConverter
[
https://issues.apache.org/jira/browse/CALCITE-2997?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17106592#comment-17106592
]
Steven Talbot commented on CALCITE-2997:
cc [~julianhyde]
I'd move to reopen this. Even if the original case perhaps does not need to
turn this off, I think there are valid cases where we would want to add a
config parameter to disable this behavior. While the push down provides a more
optimized plan, it also creates a more complex tree of RelNodes and writes much
more verbose SQL if those RelNodes are translated back to SQL with the Projects
that this rule creates. I could see a variety of cases where one would prefer
for those reasons to disable this behavior.
> Avoid pushing down join condition in SqlToRelConverter
> --
>
> Key: CALCITE-2997
> URL: https://issues.apache.org/jira/browse/CALCITE-2997
> Project: Calcite
> Issue Type: Bug
>Reporter: Jin Xing
>Priority: Major
>
> In current code, *SqlToRelConverter:createJoin* is calling
> *RelOptUtil.pushDownJoinConditions* for optimization. And we can find below
> conversion from *SqlNode* to *RelNode*:
> {code:java}
> SqlNode:
> select * from A join B on A.x = B.x * 2
> RelNode (Logical-Plan):
> Join (condition:col0=col1)
> |-Project(x as col0)
> | |-Scan(A)
> |-Project(x * 2 as col1)
> |-Scan(B){code}
> As we can see the logical plan(*RelNode*) posted above is not the pure
> reflection of the original SQL String(*SqlNode*). The optimization is mixed
> into the phase on which AST is converted to Logical-Plan. Actually optimizing
> rule of JoinPushExpressionsRule is doing exactly the same kind of thing.
> Shall we just keep the optimization inside Optimized-Logical-Plan ? I mean
> shall we avoid calling *RelOptUtil.pushDownJoinConditions* in
> *SqlToRelConverter:createJoin*
> I raised this issue because that we are doing something based on the
> Logical-Plan. And it makes us really confused that the Logical-Plan doesn't
> corresponds to SqlNode.
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3975) ProjectFilterTransposeRule should succeed for project that happens to reference all input columns
[
https://issues.apache.org/jira/browse/CALCITE-3975?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-3975:
---
Description:
... that is, I think
If I make the trivial fix of just "only skip trivial projects", something like
{noformat}
&& origProj.getProjects().stream().allMatch((proj) -> proj instanceof
RexInputRef) {noformat}
at
[https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354
|https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354]HepPlanner
goes into infinite recursion with the rule.
But here's the test case:
{code:java}
@Test public void testPushProjectPastFilter3() {
final String sql = "select empno + deptno, ename, job, mgr, hiredate, sal,
comm, slacker from emp where sal = 10 * comm\n"
+ "and upper(ename) = 'FOO'";
sql(sql).withRule(ProjectFilterTransposeRule.INSTANCE).check();
}
{code}
{noformat}
{noformat}
There's no reason the rule shouldn't succeed here, right? Or am I missing
something?
The reason this rule goes into an infinite recursion with hepplanner is because
it sticks a project on top after transpose to handle common expressions
extracted from the filter and the project. Ideally, it could have a mode where
it could avoid doing that and do a true "transpose" if there was no need for
it. For example, I don't think there is a a need for a reproject on top in this
test case: you can just transpose and everything works as it should. This would
be another way to avoid infinite recursion.
was:
... that is, I think
If I make the trivial fix of just "only skip trivial projects", something like
{noformat}
&& origProj.getProjects().stream().allMatch((proj) -> proj instanceof
RexInputRef) {noformat}
at
[https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354
|https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354]HepPlanner
goes into infinite recursion with the rule.
But here's the test case:
{code:java}
@Test public void testPushProjectPastFilter3() {
final String sql = "select empno + deptno, ename, job, mgr, hiredate, sal,
comm, slacker from emp where sal = 10 * comm\n"
+ "and upper(ename) = 'FOO'";
sql(sql).withRule(ProjectFilterTransposeRule.INSTANCE).check();
}
{code}
{noformat}
{noformat}
There's no reason the rule shouldn't fire here, right? Or am I missing
something?
The reason this rule goes into an infinite recursion with hepplanner is because
it sticks a project on top after transpose to handle common expressions
extracted from the filter and the project. Ideally, it could have a mode where
it could avoid doing that and do a true "transpose" if there was no need for
it. For example, I don't think there is a a need for a reproject on top in this
test case: you can just transpose and everything works as it should. This would
be another way to avoid infinite recursion.
> ProjectFilterTransposeRule should succeed for project that happens to
> reference all input columns
> -
>
> Key: CALCITE-3975
> URL: https://issues.apache.org/jira/browse/CALCITE-3975
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> ... that is, I think
> If I make the trivial fix of just "only skip trivial projects", something
> like
> {noformat}
> && origProj.getProjects().stream().allMatch((proj) -> proj instanceof
> RexInputRef) {noformat}
> at
> [https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354
>
> |https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354]HepPlanner
> goes into infinite recursion with the rule.
> But here's the test case:
>
> {code:java}
> @Test public void testPushProjectPastFilter3() {
> final String sql = "select empno + deptno, ename, job, mgr, hiredate, sal,
> comm, slacker from emp where sal = 10 * comm\n"
> + "and upper(ename) = 'FOO'";
> sql(sql).withRule(ProjectFilterTransposeRule.INSTANCE).check();
> }
> {code}
>
>
>
> {noformat}
>
>
>
>
>
>
>
[jira] [Updated] (CALCITE-3975) ProjectFilterTransposeRule should succeed for project that happens to reference all input columns
[
https://issues.apache.org/jira/browse/CALCITE-3975?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-3975:
---
Summary: ProjectFilterTransposeRule should succeed for project that happens
to reference all input columns (was: ProjectFilterTransposeRule should fire
for project that happens to reference all input columns)
> ProjectFilterTransposeRule should succeed for project that happens to
> reference all input columns
> -
>
> Key: CALCITE-3975
> URL: https://issues.apache.org/jira/browse/CALCITE-3975
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> ... that is, I think
> If I make the trivial fix of just "only skip trivial projects", something
> like
> {noformat}
> && origProj.getProjects().stream().allMatch((proj) -> proj instanceof
> RexInputRef) {noformat}
> at
> [https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354
>
> |https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354]HepPlanner
> goes into infinite recursion with the rule.
> But here's the test case:
>
> {code:java}
> @Test public void testPushProjectPastFilter3() {
> final String sql = "select empno + deptno, ename, job, mgr, hiredate, sal,
> comm, slacker from emp where sal = 10 * comm\n"
> + "and upper(ename) = 'FOO'";
> sql(sql).withRule(ProjectFilterTransposeRule.INSTANCE).check();
> }
> {code}
>
>
>
> {noformat}
>
>
>
>
>
>
>
>
>
>
> {noformat}
>
> There's no reason the rule shouldn't fire here, right? Or am I missing
> something?
> The reason this rule goes into an infinite recursion with hepplanner is
> because it sticks a project on top after transpose to handle common
> expressions extracted from the filter and the project. Ideally, it could have
> a mode where it could avoid doing that and do a true "transpose" if there was
> no need for it. For example, I don't think there is a a need for a reproject
> on top in this test case: you can just transpose and everything works as it
> should. This would be another way to avoid infinite recursion.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-3975) ProjectFilterTransposeRule should fire for project that happens to reference all input columns
Steven Talbot created CALCITE-3975:
--
Summary: ProjectFilterTransposeRule should fire for project that
happens to reference all input columns
Key: CALCITE-3975
URL: https://issues.apache.org/jira/browse/CALCITE-3975
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
... that is, I think
If I make the trivial fix of just "only skip trivial projects", something like
{noformat}
&& origProj.getProjects().stream().allMatch((proj) -> proj instanceof
RexInputRef) {noformat}
at
[https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354
|https://github.com/apache/calcite/blob/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/PushProjector.java#L354]HepPlanner
goes into infinite recursion with the rule.
But here's the test case:
{code:java}
@Test public void testPushProjectPastFilter3() {
final String sql = "select empno + deptno, ename, job, mgr, hiredate, sal,
comm, slacker from emp where sal = 10 * comm\n"
+ "and upper(ename) = 'FOO'";
sql(sql).withRule(ProjectFilterTransposeRule.INSTANCE).check();
}
{code}
{noformat}
{noformat}
There's no reason the rule shouldn't fire here, right? Or am I missing
something?
The reason this rule goes into an infinite recursion with hepplanner is because
it sticks a project on top after transpose to handle common expressions
extracted from the filter and the project. Ideally, it could have a mode where
it could avoid doing that and do a true "transpose" if there was no need for
it. For example, I don't think there is a a need for a reproject on top in this
test case: you can just transpose and everything works as it should. This would
be another way to avoid infinite recursion.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3957) AggregateMergeRule should be able to merge SUM0 into COUNT even with empty group by
[ https://issues.apache.org/jira/browse/CALCITE-3957?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17091906#comment-17091906 ] Steven Talbot commented on CALCITE-3957: See some discussion where this was originally implemented on CALCITE-1172 > AggregateMergeRule should be able to merge SUM0 into COUNT even with empty > group by > --- > > Key: CALCITE-3957 > URL: https://issues.apache.org/jira/browse/CALCITE-3957 > Project: Calcite > Issue Type: Bug >Reporter: Steven Talbot >Priority: Major > > Basically, the comment at > [https://github.com/apache/calcite/blame/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/AggregateMergeRule.java#L117-L120] > does not apply if the top aggregate is a SUM0, I believe. Without a fix for > this, there's this one case where this rule falls down and isn't able to > merge something that just got rolled up: otherwise it's a pretty nice > invariant that if one of our standard rollup rules for aggregates (say, in > MaterializedViewRule) just split the aggregate, this rule should be able to > merge it back. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3957) AggregateMergeRule should be able to merge SUM0 into COUNT even with empty group by
Steven Talbot created CALCITE-3957: -- Summary: AggregateMergeRule should be able to merge SUM0 into COUNT even with empty group by Key: CALCITE-3957 URL: https://issues.apache.org/jira/browse/CALCITE-3957 Project: Calcite Issue Type: Bug Reporter: Steven Talbot Basically, the comment at [https://github.com/apache/calcite/blame/571731b80a58eb095ebac7123285c375e7afff90/core/src/main/java/org/apache/calcite/rel/rules/AggregateMergeRule.java#L117-L120] does not apply if the top aggregate is a SUM0, I believe. Without a fix for this, there's this one case where this rule falls down and isn't able to merge something that just got rolled up: otherwise it's a pretty nice invariant that if one of our standard rollup rules for aggregates (say, in MaterializedViewRule) just split the aggregate, this rule should be able to merge it back. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3936) RelToSqlConverter changes target of ambiguous HAVING clause with a Project on Filter on Aggregate
[
https://issues.apache.org/jira/browse/CALCITE-3936?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17086044#comment-17086044
]
Steven Talbot edited comment on CALCITE-3936 at 4/17/20, 8:24 PM:
--
Yes, the only bummer with (1) here is that probably very frequently, this rel
structure will have a Project on top of it that effectively drops away the agg
calls used for the HAVING. In that case, if we force a subselect when we see
the Filter/Aggregate/Project without considering what's above it, we'd end up
writing SQL that was less pretty and understandable than it could have been.
Perhaps there's a creative solution to that.
was (Author: swtalbot):
Yes, the only bummer with (1) here is that probably very frequently, this rel
structure will have a Project on top of it that effectively drops away the
aggregates used for the HAVING. In that case, if we force a subselect when we
see the Filter/Aggregate/Project without considering what's above it, we'd end
up writing SQL that was less pretty and understandable than it could have been.
Perhaps there's a creative solution to that.
> RelToSqlConverter changes target of ambiguous HAVING clause with a Project on
> Filter on Aggregate
> -
>
> Key: CALCITE-3936
> URL: https://issues.apache.org/jira/browse/CALCITE-3936
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> ... for dialects with SqlConformance.isHavingAlias=false
> Very, very similar to -CALCITE-3593.-
> Reproducing test case in RelToSqlConverter:
> {code:java}
> @Test public void testHavingAlias2() {
> final String query = "select \"product_id\" + 1, sum(\"gross_weight\") as
> gross_weight\n" +
> " from \"product\"\n" +
> " group by \"product_id\"\n" +
> " having sum(\"product\".\"gross_weight\") < 200";
> final String expected = "SELECT product_id + 1, GROSS_WEIGHT\n" +
> "FROM (SELECT product_id, SUM(gross_weight) AS GROSS_WEIGHT\n" +
> "FROM foodmart.product\n" +
> "GROUP BY product_id\n" +
> "HAVING SUM(product.gross_weight) < 200) AS t1"
> // (or) "HAVING gross_weight < 200) AS t1"
> // (or) ") AS t1\nWHERE t1.gross_weight < 200) AS t1"
> // INSTEAD, we get "HAVING SUM(gross_weight) < 200) AS t1"
> // which on BigQuery gives you an error about aggregating aggregates
> ;
> sql(query).withBigQuery().ok(expected);
> }
> {code}
> In that one, the pattern was Project/Filter/Aggregate, here it is
> Filter/Aggregate/Project. In 3593, the project created a new alias, which got
> added to the same SELECT clause and caused the ambiguity. Here, the aggregate
> creates an alias, but the filter will write a HAVING clause using the aliases
> from before the Aggregate, and that will cause the SQL engine to think that
> the filter is on the aggregate field, rather than on the underlying field.
> Note that this is less an absurdly unlikely occurrence than it might seem
> because when Calcite's default aliasing kicks in and everything gets the name
> "$f6", "$f4", etc, so chances of a collision are higher if you have multiply
> nested selects with default aliases.
> Potential fixes:
> # force a subselect, as was done for 3593.
> # Force the expression in the HAVING to be fully aliased by table (works at
> least in BigQuery, where I tested)
> # Write the HAVING expression in terms of the aliases from the aggregate,
> rather than what's coming from the aggregate (also works on BigQuery)
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3936) RelToSqlConverter changes target of ambiguous HAVING clause with a Project on Filter on Aggregate
[
https://issues.apache.org/jira/browse/CALCITE-3936?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17086044#comment-17086044
]
Steven Talbot commented on CALCITE-3936:
Yes, the only bummer with (1) here is that probably very frequently, this rel
structure will have a Project on top of it that effectively drops away the
aggregates used for the HAVING. In that case, if we force a subselect when we
see the Filter/Aggregate/Project without considering what's above it, we'd end
up writing SQL that was less pretty and understandable than it could have been.
Perhaps there's a creative solution to that.
> RelToSqlConverter changes target of ambiguous HAVING clause with a Project on
> Filter on Aggregate
> -
>
> Key: CALCITE-3936
> URL: https://issues.apache.org/jira/browse/CALCITE-3936
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> ... for dialects with SqlConformance.isHavingAlias=false
> Very, very similar to -CALCITE-3593.-
> Reproducing test case in RelToSqlConverter:
> {code:java}
> @Test public void testHavingAlias2() {
> final String query = "select \"product_id\" + 1, sum(\"gross_weight\") as
> gross_weight\n" +
> " from \"product\"\n" +
> " group by \"product_id\"\n" +
> " having sum(\"product\".\"gross_weight\") < 200";
> final String expected = "SELECT product_id + 1, GROSS_WEIGHT\n" +
> "FROM (SELECT product_id, SUM(gross_weight) AS GROSS_WEIGHT\n" +
> "FROM foodmart.product\n" +
> "GROUP BY product_id\n" +
> "HAVING SUM(product.gross_weight) < 200) AS t1"
> // (or) "HAVING gross_weight < 200) AS t1"
> // (or) ") AS t1\nWHERE t1.gross_weight < 200) AS t1"
> // INSTEAD, we get "HAVING SUM(gross_weight) < 200) AS t1"
> // which on BigQuery gives you an error about aggregating aggregates
> ;
> sql(query).withBigQuery().ok(expected);
> }
> {code}
> In that one, the pattern was Project/Filter/Aggregate, here it is
> Filter/Aggregate/Project. In 3593, the project created a new alias, which got
> added to the same SELECT clause and caused the ambiguity. Here, the aggregate
> creates an alias, but the filter will write a HAVING clause using the aliases
> from before the Aggregate, and that will cause the SQL engine to think that
> the filter is on the aggregate field, rather than on the underlying field.
> Note that this is less an absurdly unlikely occurrence than it might seem
> because when Calcite's default aliasing kicks in and everything gets the name
> "$f6", "$f4", etc, so chances of a collision are higher if you have multiply
> nested selects with default aliases.
> Potential fixes:
> # force a subselect, as was done for 3593.
> # Force the expression in the HAVING to be fully aliased by table (works at
> least in BigQuery, where I tested)
> # Write the HAVING expression in terms of the aliases from the aggregate,
> rather than what's coming from the aggregate (also works on BigQuery)
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-3936) RelToSqlConverter changes target of ambiguous HAVING clause with a Project on Filter on Aggregate
Steven Talbot created CALCITE-3936:
--
Summary: RelToSqlConverter changes target of ambiguous HAVING
clause with a Project on Filter on Aggregate
Key: CALCITE-3936
URL: https://issues.apache.org/jira/browse/CALCITE-3936
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
... for dialects with SqlConformance.isHavingAlias=false
Very, very similar to -CALCITE-3593.-
Reproducing test case in RelToSqlConverter:
{code:java}
@Test public void testHavingAlias2() {
final String query = "select \"product_id\" + 1, sum(\"gross_weight\") as
gross_weight\n" +
" from \"product\"\n" +
" group by \"product_id\"\n" +
" having sum(\"product\".\"gross_weight\") < 200";
final String expected = "SELECT product_id + 1, GROSS_WEIGHT\n" +
"FROM (SELECT product_id, SUM(gross_weight) AS GROSS_WEIGHT\n" +
"FROM foodmart.product\n" +
"GROUP BY product_id\n" +
"HAVING SUM(product.gross_weight) < 200) AS t1"
// (or) "HAVING gross_weight < 200) AS t1"
// (or) ") AS t1\nWHERE t1.gross_weight < 200) AS t1"
// INSTEAD, we get "HAVING SUM(gross_weight) < 200) AS t1"
// which on BigQuery gives you an error about aggregating aggregates
;
sql(query).withBigQuery().ok(expected);
}
{code}
In that one, the pattern was Project/Filter/Aggregate, here it is
Filter/Aggregate/Project. In 3593, the project created a new alias, which got
added to the same SELECT clause and caused the ambiguity. Here, the aggregate
creates an alias, but the filter will write a HAVING clause using the aliases
from before the Aggregate, and that will cause the SQL engine to think that the
filter is on the aggregate field, rather than on the underlying field.
Note that this is less an absurdly unlikely occurrence than it might seem
because when Calcite's default aliasing kicks in and everything gets the name
"$f6", "$f4", etc, so chances of a collision are higher if you have multiply
nested selects with default aliases.
Potential fixes:
# force a subselect, as was done for 3593.
# Force the expression in the HAVING to be fully aliased by table (works at
least in BigQuery, where I tested)
# Write the HAVING expression in terms of the aliases from the aggregate,
rather than what's coming from the aggregate (also works on BigQuery)
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3922) ProjectRemoveRule drops aliases when used with HepPlanner
[
https://issues.apache.org/jira/browse/CALCITE-3922?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17083514#comment-17083514
]
Steven Talbot commented on CALCITE-3922:
Good point. I should probably just rename the result of the planner to what I
put into it. I do that elsewhere and just sort of spaced out here. And, in
terms of ProjectMergeRule, that's actually where I started but it's got
[https://github.com/apache/calcite/blob/f1857aa305c99dca40ee3c940ee689d77f27eaee/core/src/main/java/org/apache/calcite/rel/rules/ProjectMergeRule.java#L94]
to explicitly skip this case in favor of ProjectRemoveRule, for some reason.
> ProjectRemoveRule drops aliases when used with HepPlanner
> -
>
> Key: CALCITE-3922
> URL: https://issues.apache.org/jira/browse/CALCITE-3922
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> The line at
> [https://github.com/apache/calcite/blob/15fa9bc67a8ed12ec6b8e0d1dbced8760ae307ab/core/src/main/java/org/apache/calcite/rel/rules/ProjectRemoveRule.java#L67]
> checks for the input being an instanceof Project, but in the HepPlanner
> every input is always a HepRelVertex.
>
> I struggled writing a test for this, but I finally got one (in
> HepPlannerTest). The thing the test does is similar to how I hit this: you
> tend to only have trivial projects on top of other trivial projects if a rule
> moved them around. RelBuilder and SqlToRelConverter will just pre-squash them
> for you.
>
> {code:java}
> @Test public void testProjectRemoveRuleWithAliases() {
> HepProgram prog = HepProgram.builder()
> .addRuleInstance(FilterProjectTransposeRule.INSTANCE)
> .addRuleInstance(ProjectRemoveRule.INSTANCE)
> .build();
> HepPlanner planner = new HepPlanner(prog);
> RelBuilder relBuilder =
> RelBuilder.create(Frameworks.newConfigBuilder().build());
> String[] fieldNames = {"name", "other"};
> relBuilder.values(fieldNames, "something", "something else");
> relBuilder.project(relBuilder.field("name"));
> // add a filter so the projects won't squash together: it will be moved
> below by the transpose rule
> relBuilder.filter(relBuilder.call(SqlStdOperatorTable.LIKE,
> relBuilder.field("name"), relBuilder.literal("something")));
> relBuilder.rename(ImmutableList.of("name1"));
> planner.setRoot(relBuilder.build());
> RelNode opt = planner.findBestExp();
> assertEquals(LogicalValues.class, opt.getInput(0).getInput(0).getClass(),
> "should have removed the project");
> assertEquals("name1", opt.getRowType().getFieldNames().get(0), "should
> have kept the rename");
> }
> {code}
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-3922) ProjectRemoveRule drops aliases when used with HepPlanner
Steven Talbot created CALCITE-3922:
--
Summary: ProjectRemoveRule drops aliases when used with HepPlanner
Key: CALCITE-3922
URL: https://issues.apache.org/jira/browse/CALCITE-3922
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
The line at
[https://github.com/apache/calcite/blob/15fa9bc67a8ed12ec6b8e0d1dbced8760ae307ab/core/src/main/java/org/apache/calcite/rel/rules/ProjectRemoveRule.java#L67]
checks for the input being an instanceof Project, but in the HepPlanner every
input is always a HepRelVertex.
I struggled writing a test for this, but I finally got one (in HepPlannerTest).
The thing the test does is similar to how I hit this: you tend to only have
trivial projects on top of other trivial projects if a rule moved them around.
RelBuilder and SqlToRelConverter will just pre-squash them for you.
{code:java}
@Test public void testProjectRemoveRuleWithAliases() {
HepProgram prog = HepProgram.builder()
.addRuleInstance(FilterProjectTransposeRule.INSTANCE)
.addRuleInstance(ProjectRemoveRule.INSTANCE)
.build();
HepPlanner planner = new HepPlanner(prog);
RelBuilder relBuilder =
RelBuilder.create(Frameworks.newConfigBuilder().build());
String[] fieldNames = {"name", "other"};
relBuilder.values(fieldNames, "something", "something else");
relBuilder.project(relBuilder.field("name"));
// add a filter so the projects won't squash together: it will be moved
below by the transpose rule
relBuilder.filter(relBuilder.call(SqlStdOperatorTable.LIKE,
relBuilder.field("name"), relBuilder.literal("something")));
relBuilder.rename(ImmutableList.of("name1"));
planner.setRoot(relBuilder.build());
RelNode opt = planner.findBestExp();
assertEquals(LogicalValues.class, opt.getInput(0).getInput(0).getClass(),
"should have removed the project");
assertEquals("name1", opt.getRowType().getFieldNames().get(0), "should have
kept the rename");
}
{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3877) Null lowerBound/upperBond in RexWindow causes NPE in RexShuttle
[
https://issues.apache.org/jira/browse/CALCITE-3877?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17068027#comment-17068027
]
Steven Talbot commented on CALCITE-3877:
Would that fix mean there is no way to ever create RexWindows that would not
write a RANGE BETWEEN...?
I suppose the idea is that such a step to turn "RANGE BETWEEN UNBOUNDING
PRECEEDING AND UNBOUNDED FOLLOWING" into nothing could be done at a different
level?
> Null lowerBound/upperBond in RexWindow causes NPE in RexShuttle
> ---
>
> Key: CALCITE-3877
> URL: https://issues.apache.org/jira/browse/CALCITE-3877
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Assignee: Julian Hyde
>Priority: Major
> Fix For: 1.23.0
>
>
> Here's a test that can be dropped into RexShuttleTest.java
>
> {code:java}
> @Test public void handlesNullBoundsInRexWindow() {
> final RelBuilder builder =
> RelBuilder.create(RelBuilderTest.config().build());
> final RexNode over = builder.getRexBuilder().makeOver(
> builder.getTypeFactory().createSqlType(SqlTypeName.BIGINT),
> SqlStdOperatorTable.ROW_NUMBER,
> ImmutableList.of(),
> ImmutableList.of(),
> ImmutableList.of(),
> null,
> null,
> true,
> true,
> true,
> true,
>true
> );
> assertThat(over.accept(new RexShuttle()), is(over));
> }
> {code}
>
>
> That will raise an NPE. Fix is to handle NULLs when doing the child accept on
> the RexWindowBound[s] in RexShuttle.visitWindow.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3876) Should not combine projects when top project contains window function referencing window function from bottom project
[
https://issues.apache.org/jira/browse/CALCITE-3876?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17067904#comment-17067904
]
Steven Talbot commented on CALCITE-3876:
I see. I had not seen *_ProjectToWindowRule_* before. I will have to play
around with it and see if I can use it at all, and then I will have a better
understanding.
But yes, from the discussion on CALCITE-3079 it seems ultimately
ProjectMergeRule will have to be fixed. I don't know if I'll have a chance to
contribute a fix in the next little bit, but if I do I will comment back here.
Or should we close this as a duplicate of CALCITE-3079 ?
> Should not combine projects when top project contains window function
> referencing window function from bottom project
> -
>
> Key: CALCITE-3876
> URL: https://issues.apache.org/jira/browse/CALCITE-3876
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> Here's a test in RelToSqlConverterTest that should (roughly) pass
> {code:java}
> @Test public void testWindowOnWindowDoesNotCombineProjects() {
> final String query = "SELECT \n" +
> " ROW_NUMBER() OVER (ORDER BY rn) FROM\n" +
> " (SELECT *, \n" +
> " ROW_NUMBER() OVER (ORDER BY \"product_id\") as rn\n" +
> " FROM \"foodmart\".\"product\")";
> final String expected = "SELECT \n" +
> " ROW_NUMBER() OVER (ORDER BY rn) FROM\n" +
> " (SELECT *, \n" +
> " ROW_NUMBER() OVER (ORDER BY \"product_id\") as rn\n" +
> " FROM \"foodmart\".\"product\")";
> sql(query)
> .withPostgresql()
> .ok(expected);
> }{code}
> Instead, we see the result
> {noformat}
> SELECT ROW_NUMBER() OVER (ORDER BY ROW_NUMBER() OVER (ORDER BY
> \"product_id\"))\nFROM \"foodmart\".\"product\"{noformat}
> That's because multiple steps in the pipeline want to squish those two
> projects together, but that's not valid to do (at least in Redshift where I
> tested, but I assume in most other SQL dialects as well).
> So the expected behavior is that SqlToRelConverter, RelBuilder, and
> RelToSqlConverter are all knowledgeable enough about window functions to
> examine the arguments and avoid merging projects (or combining SelectNode[s],
> in the case of RelToSqlConverter), when one window references another window.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3876) Should not combine projects when top project contains window function referencing window function from bottom project
[
https://issues.apache.org/jira/browse/CALCITE-3876?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17067904#comment-17067904
]
Steven Talbot edited comment on CALCITE-3876 at 3/26/20, 5:54 PM:
--
I see. I had not seen *_ProjectToWindowRule_* before. I will have to play
around with it and see if I can use it at all, and then I will have a better
understanding.
But yes, from the discussion on CALCITE-3079 it seems ultimately
ProjectMergeRule will have to be fixed. I don't know if I'll have a chance to
contribute a fix in the next little bit, but if I do I will comment back here.
Or should we close this as a duplicate of CALCITE-3079 ?
was (Author: swtalbot):
I see. I had not seen *_ProjectToWindowRule_* before. I will have to play
around with it and see if I can use it at all, and then I will have a better
understanding.
But yes, from the discussion on CALCITE-3079 it seems ultimately
ProjectMergeRule will have to be fixed. I don't know if I'll have a chance to
contribute a fix in the next little bit, but if I do I will comment back here.
Or should we close this as a duplicate of CALCITE-3079 ?
> Should not combine projects when top project contains window function
> referencing window function from bottom project
> -
>
> Key: CALCITE-3876
> URL: https://issues.apache.org/jira/browse/CALCITE-3876
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> Here's a test in RelToSqlConverterTest that should (roughly) pass
> {code:java}
> @Test public void testWindowOnWindowDoesNotCombineProjects() {
> final String query = "SELECT \n" +
> " ROW_NUMBER() OVER (ORDER BY rn) FROM\n" +
> " (SELECT *, \n" +
> " ROW_NUMBER() OVER (ORDER BY \"product_id\") as rn\n" +
> " FROM \"foodmart\".\"product\")";
> final String expected = "SELECT \n" +
> " ROW_NUMBER() OVER (ORDER BY rn) FROM\n" +
> " (SELECT *, \n" +
> " ROW_NUMBER() OVER (ORDER BY \"product_id\") as rn\n" +
> " FROM \"foodmart\".\"product\")";
> sql(query)
> .withPostgresql()
> .ok(expected);
> }{code}
> Instead, we see the result
> {noformat}
> SELECT ROW_NUMBER() OVER (ORDER BY ROW_NUMBER() OVER (ORDER BY
> \"product_id\"))\nFROM \"foodmart\".\"product\"{noformat}
> That's because multiple steps in the pipeline want to squish those two
> projects together, but that's not valid to do (at least in Redshift where I
> tested, but I assume in most other SQL dialects as well).
> So the expected behavior is that SqlToRelConverter, RelBuilder, and
> RelToSqlConverter are all knowledgeable enough about window functions to
> examine the arguments and avoid merging projects (or combining SelectNode[s],
> in the case of RelToSqlConverter), when one window references another window.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-3877) Null lowerBound/upperBond in RexWindow causes NPE in RexShuttle
Steven Talbot created CALCITE-3877:
--
Summary: Null lowerBound/upperBond in RexWindow causes NPE in
RexShuttle
Key: CALCITE-3877
URL: https://issues.apache.org/jira/browse/CALCITE-3877
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
Here's a test that can be dropped into RexShuttleTest.java
{code:java}
@Test public void handlesNullBoundsInRexWindow() {
final RelBuilder builder = RelBuilder.create(RelBuilderTest.config().build());
final RexNode over = builder.getRexBuilder().makeOver(
builder.getTypeFactory().createSqlType(SqlTypeName.BIGINT),
SqlStdOperatorTable.ROW_NUMBER,
ImmutableList.of(),
ImmutableList.of(),
ImmutableList.of(),
null,
null,
true,
true,
true,
true,
true
);
assertThat(over.accept(new RexShuttle()), is(over));
}
{code}
That will raise an NPE. Fix is to handle NULLs when doing the child accept on
the RexWindowBound[s] in RexShuttle.visitWindow.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (CALCITE-3876) Should not combine projects when top project contains window function referencing window function from bottom project
Steven Talbot created CALCITE-3876:
--
Summary: Should not combine projects when top project contains
window function referencing window function from bottom project
Key: CALCITE-3876
URL: https://issues.apache.org/jira/browse/CALCITE-3876
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
Here's a test in RelToSqlConverterTest that should (roughly) pass
{code:java}
@Test public void testWindowOnWindowDoesNotCombineProjects() {
final String query = "SELECT \n" +
" ROW_NUMBER() OVER (ORDER BY rn) FROM\n" +
" (SELECT *, \n" +
" ROW_NUMBER() OVER (ORDER BY \"product_id\") as rn\n" +
" FROM \"foodmart\".\"product\")";
final String expected = "SELECT \n" +
" ROW_NUMBER() OVER (ORDER BY rn) FROM\n" +
" (SELECT *, \n" +
" ROW_NUMBER() OVER (ORDER BY \"product_id\") as rn\n" +
" FROM \"foodmart\".\"product\")";
sql(query)
.withPostgresql()
.ok(expected);
}{code}
Instead, we see the result
{noformat}
SELECT ROW_NUMBER() OVER (ORDER BY ROW_NUMBER() OVER (ORDER BY
\"product_id\"))\nFROM \"foodmart\".\"product\"{noformat}
That's because multiple steps in the pipeline want to squish those two projects
together, but that's not valid to do (at least in Redshift where I tested, but
I assume in most other SQL dialects as well).
So the expected behavior is that SqlToRelConverter, RelBuilder, and
RelToSqlConverter are all knowledgeable enough about window functions to
examine the arguments and avoid merging projects (or combining SelectNode[s],
in the case of RelToSqlConverter), when one window references another window.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3831) AbstractMaterializedViewRule throws exception with an Aggregate on a Project projecting duplicate RexNodes
[
https://issues.apache.org/jira/browse/CALCITE-3831?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-3831:
---
Description:
Note that this only happens when a rollup is required ("forceRollup" in the
terminology of "rewriteView").
Here's a test in MaterializationTest that shows the behavior:
{code:java}
@Test public void testAggregateMaterializationOnProjectWithDuplicates() {
checkMaterialize(
"select \"empid\" / 2, \"empid\", floor(cast('1997-01-20 12:34:56' as
timestamp) to month), count(*) + 1 as c, sum(\"empid\") as s\n"
+ "from \"emps\" group by \"empid\" / 2, \"empid\", floor(cast('1997-01-20
12:34:56' as timestamp) to month)",
"select e1, e2, f, sum(e1) as s from (select \"empid\" as e1, \"empid\" as e2,
floor(cast('1997-01-20 12:34:56' as timestamp) to year) as f\n"
+ "from \"emps\" ) t1 " +
"group by e1,e2,f");
}{code}
The query SQL uses that somewhat artificial subquery to force the SQL to rel
parser to create the project with duplicates for the test.
Obviously this is a silly SQL query to write, but in the process of building
more complex queries it's not absurd to end up with a project with duplicate
RexNodes.
Here's the top of the stack produced by that test (sorry my line number locally
are a bit different from current master):
{noformat}
Caused by: org.apache.calcite.util.mapping.Mappings$NoElementException: source
#0 has no target in mapping [size=3, sourceCount=4, targetCount=23,
elements=[1:1, 2:5, 3:4]]Caused by:
org.apache.calcite.util.mapping.Mappings$NoElementException: source #0 has no
target in mapping [size=3, sourceCount=4, targetCount=23, elements=[1:1, 2:5,
3:4]] at
org.apache.calcite.util.mapping.Mappings$AbstractMapping.getTarget(Mappings.java:879)
at
org.apache.calcite.rel.rules.AbstractMaterializedViewRule$MaterializedViewAggregateRule.rewriteView(AbstractMaterializedViewRule.java:1502)
at
org.apache.calcite.rel.rules.AbstractMaterializedViewRule.perform(AbstractMaterializedViewRule.java:521){noformat}
I tried to make a quick fix for this, since on the surface the problem in the
code seems simple, at the site of the exception
([https://github.com/apache/calcite/blob/134430e481bb5495d0852434041428104e29874e/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java#L1513])
we do a lookup in a mapping. The mapping comes about from inverting a mapping
that maps projects in the view to matching projects in the query (the relevant
line is
[https://github.com/apache/calcite/blob/134430e481bb5495d0852434041428104e29874e/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java#L1436]),
and so if two query projects match the same view project, they will overwrite
each other, and the lookup on the inverse mapping will fail.
Trying the quick fix of setting the inverse mapping at the same time we set the
rewriting mapping (i.e. `inverseMapping.set(i, k);` for the above line, and a
couple others) fixed the above exception, but led to a different exception. I
didn't spend too long digging in though, and I worry I was missing something.
was:
Note that this only happens when a rollup is required ("forceRollup" in the
terminology of "rewriteView").
Here's a test in MaterializationTest that shows the behavior:
{code:java}
@Test public void testAggregateMaterializationOnProjectWithDuplicates() {
checkMaterialize(
"select \"empid\" / 2, \"empid\", floor(cast('1997-01-20 12:34:56' as
timestamp) to month), count(*) + 1 as c, sum(\"empid\") as s\n"
+ "from \"emps\" group by \"empid\" / 2, \"empid\", floor(cast('1997-01-20
12:34:56' as timestamp) to month)",
"select e1, e2, f, sum(e1) as s from (select \"empid\" as e1, \"empid\" as e2,
floor(cast('1997-01-20 12:34:56' as timestamp) to year) as f\n"
+ "from \"emps\" ) t1 " +
"group by e1,e2,f");
}{code}
The query SQL uses that somewhat artificial subquery to force the SQL to rel
parser to create the project with duplicates for the test.
Obviously this is a silly SQL query to write, but in the process of building
more complex queries it's not absurd to end up with a project with duplicate
RexNodes.
Here's the top of the stack produced by that test:
{noformat}
Caused by: org.apache.calcite.util.mapping.Mappings$NoElementException: source
#0 has no target in mapping [size=3, sourceCount=4, targetCount=23,
elements=[1:1, 2:5, 3:4]]Caused by:
org.apache.calcite.util.mapping.Mappings$NoElementException: source #0 has no
target in mapping [size=3, sourceCount=4, targetCount=23, elements=[1:1, 2:5,
3:4]] at
org.apache.calcite.util.mapping.Mappings$AbstractMapping.getTarget(Mappings.java:879)
at
org.apache.calcite.rel.rules.AbstractMaterializedViewRule$MaterializedViewAggregateRule.rewriteView(AbstractMaterializedViewRule.java:1502)
at
[jira] [Updated] (CALCITE-3831) AbstractMaterializedViewRule throws exception with an Aggregate on a Project projecting duplicate RexNodes
[
https://issues.apache.org/jira/browse/CALCITE-3831?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-3831:
---
Description:
Note that this only happens when a rollup is required ("forceRollup" in the
terminology of "rewriteView").
Here's a test in MaterializationTest that shows the behavior:
{code:java}
@Test public void testAggregateMaterializationOnProjectWithDuplicates() {
checkMaterialize(
"select \"empid\" / 2, \"empid\", floor(cast('1997-01-20 12:34:56' as
timestamp) to month), count(*) + 1 as c, sum(\"empid\") as s\n"
+ "from \"emps\" group by \"empid\" / 2, \"empid\", floor(cast('1997-01-20
12:34:56' as timestamp) to month)",
"select e1, e2, f, sum(e1) as s from (select \"empid\" as e1, \"empid\" as e2,
floor(cast('1997-01-20 12:34:56' as timestamp) to year) as f\n"
+ "from \"emps\" ) t1 " +
"group by e1,e2,f");
}{code}
The query SQL uses that somewhat artificial subquery to force the SQL to rel
parser to create the project with duplicates for the test.
Obviously this is a silly SQL query to write, but in the process of building
more complex queries it's not absurd to end up with a project with duplicate
RexNodes.
Here's the top of the stack produced by that test:
{noformat}
Caused by: org.apache.calcite.util.mapping.Mappings$NoElementException: source
#0 has no target in mapping [size=3, sourceCount=4, targetCount=23,
elements=[1:1, 2:5, 3:4]]Caused by:
org.apache.calcite.util.mapping.Mappings$NoElementException: source #0 has no
target in mapping [size=3, sourceCount=4, targetCount=23, elements=[1:1, 2:5,
3:4]] at
org.apache.calcite.util.mapping.Mappings$AbstractMapping.getTarget(Mappings.java:879)
at
org.apache.calcite.rel.rules.AbstractMaterializedViewRule$MaterializedViewAggregateRule.rewriteView(AbstractMaterializedViewRule.java:1502)
at
org.apache.calcite.rel.rules.AbstractMaterializedViewRule.perform(AbstractMaterializedViewRule.java:521){noformat}
I tried to make a quick fix for this, since on the surface the problem in the
code seems simple, at [the site of the exception|#L1513]] we do a lookup in a
mapping. The mapping comes about from inverting a mapping that maps projects in
the view to matching projects in the query ([the relevant line is
here|[https://github.com/apache/calcite/blob/134430e481bb5495d0852434041428104e29874e/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java#L1436]]),
and so if two query projects match the same view project, they will overwrite
each other, and the lookup on the inverse mapping will fail.
Trying the quick fix of setting the inverse mapping at the same time we set the
rewriting mapping (i.e. `inverseMapping.set(i, k);` for the above line, and a
couple others) fixed the above exception, but led to a different exception. I
didn't spend too long digging in though, and I worry I was missing something.
was:
Note that this only happens when a rollup is required ("forceRollup" in the
terminology of "rewriteView").
Here's a test in MaterializationTest that shows the behavior:
{code:java}
@Test public void testAggregateMaterializationOnProjectWithDuplicates() {
checkMaterialize(
"select \"empid\" / 2, \"empid\", floor(cast('1997-01-20 12:34:56' as
timestamp) to month), count(*) + 1 as c, sum(\"empid\") as s\n"
+ "from \"emps\" group by \"empid\" / 2, \"empid\", floor(cast('1997-01-20
12:34:56' as timestamp) to month)",
"select e1, e2, f, sum(e1) as s from (select \"empid\" as e1, \"empid\" as e2,
floor(cast('1997-01-20 12:34:56' as timestamp) to year) as f\n"
+ "from \"emps\" ) t1 " +
"group by e1,e2,f");
}{code}
The query SQL uses that somewhat artificial subquery to force the SQL to rel
parser to create the project with duplicates for the test.
Obviously this is a silly SQL query to write, but in the process of building
more complex queries it's not absurd to end up with a project with duplicate
RexNodes.
Here's the top of the stack produced by that test:
{noformat}
Caused by: org.apache.calcite.util.mapping.Mappings$NoElementException: source
#0 has no target in mapping [size=3, sourceCount=4, targetCount=23,
elements=[1:1, 2:5, 3:4]]Caused by:
org.apache.calcite.util.mapping.Mappings$NoElementException: source #0 has no
target in mapping [size=3, sourceCount=4, targetCount=23, elements=[1:1, 2:5,
3:4]] at
org.apache.calcite.util.mapping.Mappings$AbstractMapping.getTarget(Mappings.java:879)
at
org.apache.calcite.rel.rules.AbstractMaterializedViewRule$MaterializedViewAggregateRule.rewriteView(AbstractMaterializedViewRule.java:1502)
at
org.apache.calcite.rel.rules.AbstractMaterializedViewRule.perform(AbstractMaterializedViewRule.java:521){noformat}
I tried to make a quick fix for this, since on the surface the problem in the
code seems simple, at [the site of the
[jira] [Created] (CALCITE-3831) AbstractMaterializedViewRule throws exception with an Aggregate on a Project projecting duplicate RexNodes
Steven Talbot created CALCITE-3831:
--
Summary: AbstractMaterializedViewRule throws exception with an
Aggregate on a Project projecting duplicate RexNodes
Key: CALCITE-3831
URL: https://issues.apache.org/jira/browse/CALCITE-3831
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
Note that this only happens when a rollup is required ("forceRollup" in the
terminology of "rewriteView").
Here's a test in MaterializationTest that shows the behavior:
{code:java}
@Test public void testAggregateMaterializationOnProjectWithDuplicates() {
checkMaterialize(
"select \"empid\" / 2, \"empid\", floor(cast('1997-01-20 12:34:56' as
timestamp) to month), count(*) + 1 as c, sum(\"empid\") as s\n"
+ "from \"emps\" group by \"empid\" / 2, \"empid\", floor(cast('1997-01-20
12:34:56' as timestamp) to month)",
"select e1, e2, f, sum(e1) as s from (select \"empid\" as e1, \"empid\" as e2,
floor(cast('1997-01-20 12:34:56' as timestamp) to year) as f\n"
+ "from \"emps\" ) t1 " +
"group by e1,e2,f");
}{code}
The query SQL uses that somewhat artificial subquery to force the SQL to rel
parser to create the project with duplicates for the test.
Obviously this is a silly SQL query to write, but in the process of building
more complex queries it's not absurd to end up with a project with duplicate
RexNodes.
Here's the top of the stack produced by that test:
{noformat}
Caused by: org.apache.calcite.util.mapping.Mappings$NoElementException: source
#0 has no target in mapping [size=3, sourceCount=4, targetCount=23,
elements=[1:1, 2:5, 3:4]]Caused by:
org.apache.calcite.util.mapping.Mappings$NoElementException: source #0 has no
target in mapping [size=3, sourceCount=4, targetCount=23, elements=[1:1, 2:5,
3:4]] at
org.apache.calcite.util.mapping.Mappings$AbstractMapping.getTarget(Mappings.java:879)
at
org.apache.calcite.rel.rules.AbstractMaterializedViewRule$MaterializedViewAggregateRule.rewriteView(AbstractMaterializedViewRule.java:1502)
at
org.apache.calcite.rel.rules.AbstractMaterializedViewRule.perform(AbstractMaterializedViewRule.java:521){noformat}
I tried to make a quick fix for this, since on the surface the problem in the
code seems simple, at [the site of the
exception|[https://github.com/apache/calcite/blob/134430e481bb5495d0852434041428104e29874e/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java#L1513]]
we do a lookup in a mapping. The mapping comes about from inverting a mapping
that maps projects in the view to matching projects in the query ([the relevant
line is
here|[https://github.com/apache/calcite/blob/134430e481bb5495d0852434041428104e29874e/core/src/main/java/org/apache/calcite/rel/rules/AbstractMaterializedViewRule.java#L1436]]),
and so if two query projects match the same view project, they will overwrite
each other, and the lookup on the inverse mapping will fail.
Trying the quick fix of setting the inverse mapping at the same time we set the
rewriting mapping (i.e. `inverseMapping.set(i, k);` for the above line, and a
couple others) fixed the above exception, but led to a different exception. I
didn't spend too long digging in though, and I worry I was missing something.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3593) RelToSqlConverter changes target of ambiguous HAVING clause with a Project on Filter on Aggregate
[
https://issues.apache.org/jira/browse/CALCITE-3593?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-3593:
---
Description:
Best shown with the shell of a test in RelToSqlConverter test.
The following SQL on BigQuery
{code:java}
select product_id - 1000 as product_id
from (
select product_id, avg(gross_weight) as agw
from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as product
where net_weight < 100
group by product_id having product_id > 0){code}
produces one result, because the having filter applies to the product id before
subtraction, of course.
Running it through the machinery in that test
(`sql(query).withBigQuery().ok(expected)`) translates it to:
{noformat}
SELECT product_id - 1000 AS product_id
from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as product
WHERE net_weight < 100
GROUP BY product_id
HAVING product_id > 0{noformat}
This changes the meaning of the query: now the HAVING is on the
after-subtraction product_id and you get no results, rather than the one result.
Note that this is _not_ true in HyperSQL, as it has different semantics around
the HAVING namespace.
{noformat}
select "product_id" - 1000 as "product_id"
from (
select "product_id", avg("gross_weight") as agw
from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight" FROM
(VALUES(0))) as product
where "net_weight" < 100
group by "product_id" having "product_id" > 0){noformat}
becomes
{noformat}
SELECT "product_id" - 1000 AS "product_id"
from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight" FROM
(VALUES(0))) as product
WHERE "net_weight" < 100
GROUP BY "product_id"
HAVING "product_id" > 0{noformat}
But the meaning is preserved: both return a row.
I'm not enough of a SQL standards expert to know which one is being more
compliant, but presumably both would have to be supported via some sort of flag?
I think the fix would be to force the subselect on dialects such as BigQuery
that have this behavior. Probably something that looks a lot like
[https://github.com/apache/calcite/blob/3530daaa8cad43aad6845b6c79e4bc1ca0e72f5f/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1043-L1047]
The test, of course, looks like pretty silly SQL no one would ever write, but
the point is this is what's generated when you have
{noformat}
Project f(x) as x
Filter g(x)
Aggregate {}, ...{noformat}
was:
Best shown with the shell of a test in RelToSqlConverter test.
The following SQL on BigQuery
{code:java}
select product_id - 1000 as product_id
from (
select product_id, avg(gross_weight) as agw
from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as product
where net_weight < 100
group by product_id having product_id > 0){code}
produces one result, because the having filter applies to the product id before
subtraction, of course.
Running it through the machinery in that test
(`sql(query).withBigQuery().ok(expected)`) translates it to:
{noformat}
SELECT product_id - 1000 AS product_id
from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as product
WHERE net_weight < 100
GROUP BY product_id
HAVING product_id > 0{noformat}
This changes the meaning of the query: now the HAVING is on the
after-subtraction product_id and you get no results, rather than the one result.
Note that this is _not_ true in HyperSQL, as it has different semantics around
the HAVING namespace.
{noformat}
select "product_id" - 1000 as "product_id"
from (
select "product_id", avg("gross_weight") as agw
from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight" FROM
(VALUES(0))) as product
where "net_weight" < 100
group by "product_id" having "product_id" > 0){noformat}
becomes
{noformat}
SELECT "product_id" - 1000 AS "product_id"
from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight" FROM
(VALUES(0))) as product
WHERE "net_weight" < 100
GROUP BY "product_id"
HAVING "product_id" > 0{noformat}
But the meaning is preserved: both return a row.
I'm not enough of a SQL standards expert to know which one is being more
compliant, but presumably both would have to be supported via some sort of flag?
I think the fix would be to force the subselect on dialects such as BigQuery
that have this behavior. Probably something that looks a lot like
[https://github.com/apache/calcite/blob/3530daaa8cad43aad6845b6c79e4bc1ca0e72f5f/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1043-L1047]
> RelToSqlConverter changes target of ambiguous HAVING clause with a Project on
> Filter on Aggregate
> -
>
> Key: CALCITE-3593
> URL: https://issues.apache.org/jira/browse/CALCITE-3593
> Project: Calcite
> Issue Type: Bug
>
[jira] [Updated] (CALCITE-3593) RelToSqlConverter changes target of ambiguous HAVING clause with a Project on Filter on Aggregate
[
https://issues.apache.org/jira/browse/CALCITE-3593?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-3593:
---
Summary: RelToSqlConverter changes target of ambiguous HAVING clause with a
Project on Filter on Aggregate (was: RelToSqlConverter changes target of
ambiguous having clause with a Project on top of an Aggregate)
> RelToSqlConverter changes target of ambiguous HAVING clause with a Project on
> Filter on Aggregate
> -
>
> Key: CALCITE-3593
> URL: https://issues.apache.org/jira/browse/CALCITE-3593
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> Best shown with the shell of a test in RelToSqlConverter test.
> The following SQL on BigQuery
> {code:java}
> select product_id - 1000 as product_id
> from (
> select product_id, avg(gross_weight) as agw
> from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as
> product
> where net_weight < 100
> group by product_id having product_id > 0){code}
> produces one result, because the having filter applies to the product id
> before subtraction, of course.
> Running it through the machinery in that test
> (`sql(query).withBigQuery().ok(expected)`) translates it to:
> {noformat}
> SELECT product_id - 1000 AS product_id
> from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as
> product
> WHERE net_weight < 100
> GROUP BY product_id
> HAVING product_id > 0{noformat}
> This changes the meaning of the query: now the HAVING is on the
> after-subtraction product_id and you get no results, rather than the one
> result.
> Note that this is _not_ true in HyperSQL, as it has different semantics
> around the HAVING namespace.
> {noformat}
> select "product_id" - 1000 as "product_id"
> from (
> select "product_id", avg("gross_weight") as agw
> from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight"
> FROM (VALUES(0))) as product
> where "net_weight" < 100
> group by "product_id" having "product_id" > 0){noformat}
> becomes
> {noformat}
> SELECT "product_id" - 1000 AS "product_id"
> from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight"
> FROM (VALUES(0))) as product
> WHERE "net_weight" < 100
> GROUP BY "product_id"
> HAVING "product_id" > 0{noformat}
> But the meaning is preserved: both return a row.
> I'm not enough of a SQL standards expert to know which one is being more
> compliant, but presumably both would have to be supported via some sort of
> flag?
> I think the fix would be to force the subselect on dialects such as BigQuery
> that have this behavior. Probably something that looks a lot like
> [https://github.com/apache/calcite/blob/3530daaa8cad43aad6845b6c79e4bc1ca0e72f5f/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1043-L1047]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Updated] (CALCITE-3593) RelToSqlConverter changes target of ambiguous having clause with a Project on top of an Aggregate
[
https://issues.apache.org/jira/browse/CALCITE-3593?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-3593:
---
Description:
Best shown with the shell of a test in RelToSqlConverter test.
The following SQL on BigQuery
{code:java}
select product_id - 1000 as product_id
from (
select product_id, avg(gross_weight) as agw
from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as product
where net_weight < 100
group by product_id having product_id > 0){code}
produces one result, because the having filter applies to the product id before
subtraction, of course.
Running it through the machinery in that test
(`sql(query).withBigQuery().ok(expected)`) translates it to:
{noformat}
SELECT product_id - 1000 AS product_id
from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as product
WHERE net_weight < 100
GROUP BY product_id
HAVING product_id > 0{noformat}
This changes the meaning of the query: now the HAVING is on the
after-subtraction product_id and you get no results, rather than the one result.
Note that this is _not_ true in HyperSQL, as it has different semantics around
the HAVING namespace.
{noformat}
select "product_id" - 1000 as "product_id"
from (
select "product_id", avg("gross_weight") as agw
from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight" FROM
(VALUES(0))) as product
where "net_weight" < 100
group by "product_id" having "product_id" > 0){noformat}
becomes
{noformat}
SELECT "product_id" - 1000 AS "product_id"
from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight" FROM
(VALUES(0))) as product
WHERE "net_weight" < 100
GROUP BY "product_id"
HAVING "product_id" > 0{noformat}
But the meaning is preserved: both return a row.
I'm not enough of a SQL standards expert to know which one is being more
compliant, but presumably both would have to be supported via some sort of flag?
I think the fix would be to force the subselect on dialects such as BigQuery
that have this behavior. Probably something that looks a lot like
[https://github.com/apache/calcite/blob/3530daaa8cad43aad6845b6c79e4bc1ca0e72f5f/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1043-L1047]
was:
Best shown with the shell of a test in RelToSqlConverter test.
The following SQL on BigQuery
{code:java}
select product_id - 1000 as product_id
from (
select product_id, avg(gross_weight) as agw
from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as product
where net_weight < 100
group by product_id having product_id > 0){code}
produces one result, because the having filter applies to the product id before
subtraction, of course.
Running it through the machinery in that test translates it to:
{noformat}
SELECT product_id - 1000 AS product_id
from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as product
WHERE net_weight < 100
GROUP BY product_id
HAVING product_id > 0{noformat}
This changes the meaning of the query: now the HAVING is on the
after-subtraction product_id and you get no results, rather than the one result.
Note that this is _not_ true in HyperSQL, as it has different semantics around
the HAVING namespace.
{noformat}
select "product_id" - 1000 as "product_id"
from (
select "product_id", avg("gross_weight") as agw
from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight" FROM
(VALUES(0))) as product
where "net_weight" < 100
group by "product_id" having "product_id" > 0){noformat}
becomes
{noformat}
SELECT "product_id" - 1000 AS "product_id"
from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight" FROM
(VALUES(0))) as product
WHERE "net_weight" < 100
GROUP BY "product_id"
HAVING "product_id" > 0{noformat}
But the meaning is preserved: both return a row.
I'm not enough of a SQL standards expert to know which one is being more
compliant, but presumably both would have to be supported via some sort of flag?
I think the fix would be to force the subselect on dialects such as BigQuery
that have this behavior. Probably something that looks a lot like
[https://github.com/apache/calcite/blob/3530daaa8cad43aad6845b6c79e4bc1ca0e72f5f/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1043-L1047]
> RelToSqlConverter changes target of ambiguous having clause with a Project on
> top of an Aggregate
> -
>
> Key: CALCITE-3593
> URL: https://issues.apache.org/jira/browse/CALCITE-3593
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> Best shown with the shell of a test in RelToSqlConverter test.
> The following SQL on BigQuery
> {code:java}
> select product_id - 1000 as product_id
> from (
> select product_id,
[jira] [Updated] (CALCITE-3593) RelToSqlConverter changes target of ambiguous having clause with a Project on top of an Aggregate
[
https://issues.apache.org/jira/browse/CALCITE-3593?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Steven Talbot updated CALCITE-3593:
---
Description:
Best shown with the shell of a test in RelToSqlConverter test.
The following SQL on BigQuery
{code:java}
select product_id - 1000 as product_id
from (
select product_id, avg(gross_weight) as agw
from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as product
where net_weight < 100
group by product_id having product_id > 0){code}
produces one result, because the having filter applies to the product id before
subtraction, of course.
Running it through the machinery in that test translates it to:
{noformat}
SELECT product_id - 1000 AS product_id
from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as product
WHERE net_weight < 100
GROUP BY product_id
HAVING product_id > 0{noformat}
This changes the meaning of the query: now the HAVING is on the
after-subtraction product_id and you get no results, rather than the one result.
Note that this is _not_ true in HyperSQL, as it has different semantics around
the HAVING namespace.
{noformat}
select "product_id" - 1000 as "product_id"
from (
select "product_id", avg("gross_weight") as agw
from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight" FROM
(VALUES(0))) as product
where "net_weight" < 100
group by "product_id" having "product_id" > 0){noformat}
becomes
{noformat}
SELECT "product_id" - 1000 AS "product_id"
from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight" FROM
(VALUES(0))) as product
WHERE "net_weight" < 100
GROUP BY "product_id"
HAVING "product_id" > 0{noformat}
But the meaning is preserved: both return a row.
I'm not enough of a SQL standards expert to know which one is being more
compliant, but presumably both would have to be supported via some sort of flag?
I think the fix would be to force the subselect on dialects such as BigQuery
that have this behavior. Probably something that looks a lot like
[https://github.com/apache/calcite/blob/3530daaa8cad43aad6845b6c79e4bc1ca0e72f5f/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1043-L1047]
was:
Best shown with the shell of a test in RelToSqlConverter test.
The following SQL on BigQuery
{code:java}
select product_id - 1000 as product_id
from (
select product_id, avg(gross_weight) as agw
from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as product
where net_weight < 100
group by product_id having product_id > 0){code}
produces one result, because the having filter applies to the product id before
subtraction, of course.
Running it through the machinery in that test translates it to:
{noformat}
SELECT product_id - 1000 AS product_id
from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as product
WHERE net_weight < 100
GROUP BY product_id
HAVING product_id > 0{noformat}
This changes the meaning of the query: now the HAVING is on the
after-subtraction product_id and you get no results, rather than the one result.
Note that this is _not_ true in HyperSQL, as it has different semantics around
the HAVING namespace.
{noformat}
select "product_id" - 1000 as "product_id"
from (
select "product_id", avg("gross_weight") as agw
from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight" FROM
(VALUES(0))) as product
where "net_weight" < 100
group by "product_id" having "product_id" > 0){noformat}
becomes
{noformat}
SELECT "product_id" - 1000 AS "product_id"
from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight" FROM
(VALUES(0))) as product
WHERE "net_weight" < 100
GROUP BY "product_id"
HAVING "product_id" > 0{noformat}
But the meaning is preserved: both return a row.
I'm not enough of a SQL standards expert to know which one is being more
compliant, but presumably both would have to be supported via some sort of flag?
I think the fix would be to force the subselect on dialects such as BigQuery
that have this behavior. Probably something that looks a lot like
[https://github.com/apache/calcite/blob/3530daaa8cad43aad6845b6c79e4bc1ca0e72f5f/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1043-L1047]
> RelToSqlConverter changes target of ambiguous having clause with a Project on
> top of an Aggregate
> -
>
> Key: CALCITE-3593
> URL: https://issues.apache.org/jira/browse/CALCITE-3593
> Project: Calcite
> Issue Type: Bug
>Reporter: Steven Talbot
>Priority: Major
>
> Best shown with the shell of a test in RelToSqlConverter test.
> The following SQL on BigQuery
> {code:java}
> select product_id - 1000 as product_id
> from (
> select product_id, avg(gross_weight) as agw
>
[jira] [Created] (CALCITE-3593) RelToSqlConverter changes target of ambiguous having clause with a Project on top of an Aggregate
Steven Talbot created CALCITE-3593:
--
Summary: RelToSqlConverter changes target of ambiguous having
clause with a Project on top of an Aggregate
Key: CALCITE-3593
URL: https://issues.apache.org/jira/browse/CALCITE-3593
Project: Calcite
Issue Type: Bug
Reporter: Steven Talbot
Best shown with the shell of a test in RelToSqlConverter test.
The following SQL on BigQuery
{code:java}
select product_id - 1000 as product_id
from (
select product_id, avg(gross_weight) as agw
from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as product
where net_weight < 100
group by product_id having product_id > 0){code}
produces one result, because the having filter applies to the product id before
subtraction, of course.
Running it through the machinery in that test translates it to:
{noformat}
SELECT product_id - 1000 AS product_id
from (SELECT 1 as product_id, 70 as net_weight, 170 as gross_weight) as product
WHERE net_weight < 100
GROUP BY product_id
HAVING product_id > 0{noformat}
This changes the meaning of the query: now the HAVING is on the
after-subtraction product_id and you get no results, rather than the one result.
Note that this is _not_ true in HyperSQL, as it has different semantics around
the HAVING namespace.
{noformat}
select "product_id" - 1000 as "product_id"
from (
select "product_id", avg("gross_weight") as agw
from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight" FROM
(VALUES(0))) as product
where "net_weight" < 100
group by "product_id" having "product_id" > 0){noformat}
becomes
{noformat}
SELECT "product_id" - 1000 AS "product_id"
from (SELECT 1 as "product_id", 70 as "net_weight", 170 as "gross_weight" FROM
(VALUES(0))) as product
WHERE "net_weight" < 100
GROUP BY "product_id"
HAVING "product_id" > 0{noformat}
But the meaning is preserved: both return a row.
I'm not enough of a SQL standards expert to know which one is being more
compliant, but presumably both would have to be supported via some sort of flag?
I think the fix would be to force the subselect on dialects such as BigQuery
that have this behavior. Probably something that looks a lot like
[https://github.com/apache/calcite/blob/3530daaa8cad43aad6845b6c79e4bc1ca0e72f5f/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1043-L1047]
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3475) BigQuery SQL dialect unparseCall override broken for UNION ALL
[ https://issues.apache.org/jira/browse/CALCITE-3475?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16967094#comment-16967094 ] Steven Talbot commented on CALCITE-3475: Linked a proposed fix in [https://github.com/apache/calcite/pull/1559] > BigQuery SQL dialect unparseCall override broken for UNION ALL > -- > > Key: CALCITE-3475 > URL: https://issues.apache.org/jira/browse/CALCITE-3475 > Project: Calcite > Issue Type: Bug >Reporter: Steven Talbot >Assignee: Julian Hyde >Priority: Major > Fix For: 1.22.0 > > > The break on line > [https://github.com/apache/calcite/blob/ff43dcb95caa251e5fc8120980fc70c8fea8ac40/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java#L134] > should be inside the if. Otherwise, UNION ALL does not trigger the if, hits > the `break`, and then nothing is unparsed at all! > > From looking at the code, INTERSECT and EXCEPT would seem to have the same > problem, except per BQ syntax specification at > [https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#set-operators], > the "ALL" is not supported with either of those, so it may not matter (but > the code may want to explicitly blow up, saying that those aren't supported?) -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (CALCITE-3475) BigQuery SQL dialect unparseCall override broken for UNION ALL
Steven Talbot created CALCITE-3475: -- Summary: BigQuery SQL dialect unparseCall override broken for UNION ALL Key: CALCITE-3475 URL: https://issues.apache.org/jira/browse/CALCITE-3475 Project: Calcite Issue Type: Bug Reporter: Steven Talbot The break on line [https://github.com/apache/calcite/blob/ff43dcb95caa251e5fc8120980fc70c8fea8ac40/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java#L134] should be inside the if. Otherwise, UNION ALL does not trigger the if, hits the `break`, and then nothing is unparsed at all! >From looking at the code, INTERSECT and EXCEPT would seem to have the same >problem, except per BQ syntax specification at >[https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#set-operators], > the "ALL" is not supported with either of those, so it may not matter (but >the code may want to explicitly blow up, saying that those aren't supported?) -- This message was sent by Atlassian Jira (v8.3.4#803005)
