[jira] [Created] (CALCITE-1799) OR IN Subquery conversion wrong
Gian Merlino created CALCITE-1799:
-
Summary: OR IN Subquery conversion wrong
Key: CALCITE-1799
URL: https://issues.apache.org/jira/browse/CALCITE-1799
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.12.0
Reporter: Gian Merlino
Assignee: Julian Hyde
This query:
{code}
select * from emp where deptno = 10 or deptno in (
select dept.deptno from dept where deptno < 5)
{code}
Is converted to this by SqlToRelConverter:
{code}
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalFilter(condition=[OR(=($7, 10), true)])
LogicalJoin(condition=[=($7, $9)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}])
LogicalProject(DEPTNO=[$0])
LogicalFilter(condition=[<($0, 5)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{code}
But that's not right. {code}LogicalFilter(condition=[OR(=($7, 10),
true)]){code} is always true and is in the wrong place anyway (it's applied
after the inner join, where all the deptno = 10 records have already been
removed).
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Commented] (CALCITE-1792) RelToSqlConverter doesn't handle cartesian join (join cond as TRUE)
[
https://issues.apache.org/jira/browse/CALCITE-1792?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16019391#comment-16019391
]
Muhammad Gelbana commented on CALCITE-1792:
---
[~julianhyde], why would you support the FALSE literal ? I'm working on
supporting cartesian and inner joins for Drill and I had to do why you did. I
modified the
org.apache.calcite.adapter.jdbc.JdbcRules.JdbcJoin.implement(JdbcImplementor)
implementation to be
> RelToSqlConverter doesn't handle cartesian join (join cond as TRUE)
> ---
>
> Key: CALCITE-1792
> URL: https://issues.apache.org/jira/browse/CALCITE-1792
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Sergiy Simonov
>Assignee: Jess Balint
>Priority: Minor
> Fix For: 1.13.0
>
>
> this test fails (added in {{RelToSqlConverterTest}}):
> {code}
> @Test public void testCartesianProductWithFullJoinSyntax() {
> String query = "select * from \"department\"\n"
> + "FULL JOIN \"employee\" ON TRUE";
> String expected = "SELECT *\nFROM \"foodmart\".\"department\"\n"
> + "FULL JOIN \"foodmart\".\"employee\" ON TRUE";
> sql(query).ok(expected);
> }
> {code}
> {{RelToSqlConverter}} is checking that the join condition is a {{RexCall}}.
> In this case (and {{RelBuilder.join()}} with no join cond), the join cond is
> a {{RexLiteral}} with a value of {{true}}.
> Suggested fix is to handle the case with this specific join condition before
> {{convertConditionToSqlNode()}}:
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Comment Edited] (CALCITE-1792) RelToSqlConverter doesn't handle cartesian join (join cond as TRUE)
[
https://issues.apache.org/jira/browse/CALCITE-1792?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16019391#comment-16019391
]
Muhammad Gelbana edited comment on CALCITE-1792 at 5/22/17 10:00 AM:
-
[~julianhyde], why would you support the FALSE literal ? I'm working on
supporting cartesian and inner joins for Drill and I had to do why you did. I
modified the
org.apache.calcite.adapter.jdbc.JdbcRules.JdbcJoin.implement(JdbcImplementor)
implementation to be
{code:java}
public JdbcImplementor.Result implement(JdbcImplementor implementor) {
final JdbcImplementor.Result leftResult = implementor.visitChild(0,
left);
final JdbcImplementor.Result rightResult = implementor.visitChild(1,
right);
final JdbcImplementor.Context leftContext =
leftResult.qualifiedContext();
final JdbcImplementor.Context rightContext =
rightResult.qualifiedContext();
SqlNode sqlCondition = null;
JoinConditionType joinCondition = JoinConditionType.NONE;
JoinType joinType = JoinType.COMMA;
if (leftContext != null && rightContext != null) {
sqlCondition = convertConditionToSqlNode(condition, leftContext,
rightContext, left.getRowType().getFieldCount());
if (sqlCondition != null) {
joinType = JoinType.INNER;
joinCondition = JoinConditionType.ON;
}
}
SqlJoin join = new SqlJoin(POS, leftResult.asFrom(),
SqlLiteral.createBoolean(false, POS), joinType.symbol(POS),
rightResult.asFrom(), joinCondition.symbol(POS),
sqlCondition);
return implementor.result(join, leftResult, rightResult);
}
{code}
And the convertConditionToSqlNode method to accept RexLiteral
{code:java}
if (!(node instanceof RexCall) && !(node instanceof RexLiteral)) {
throw new AssertionError(node);
}
{code}
and added a Literal case
{code:java}
case LITERAL:
RexLiteral literal = (RexLiteral) node;
if (literal.isAlwaysTrue()) {
return null;
}
break;
{code}
was (Author: mgelbana):
[~julianhyde], why would you support the FALSE literal ? I'm working on
supporting cartesian and inner joins for Drill and I had to do why you did. I
modified the
org.apache.calcite.adapter.jdbc.JdbcRules.JdbcJoin.implement(JdbcImplementor)
implementation to be
> RelToSqlConverter doesn't handle cartesian join (join cond as TRUE)
> ---
>
> Key: CALCITE-1792
> URL: https://issues.apache.org/jira/browse/CALCITE-1792
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Sergiy Simonov
>Assignee: Jess Balint
>Priority: Minor
> Fix For: 1.13.0
>
>
> this test fails (added in {{RelToSqlConverterTest}}):
> {code}
> @Test public void testCartesianProductWithFullJoinSyntax() {
> String query = "select * from \"department\"\n"
> + "FULL JOIN \"employee\" ON TRUE";
> String expected = "SELECT *\nFROM \"foodmart\".\"department\"\n"
> + "FULL JOIN \"foodmart\".\"employee\" ON TRUE";
> sql(query).ok(expected);
> }
> {code}
> {{RelToSqlConverter}} is checking that the join condition is a {{RexCall}}.
> In this case (and {{RelBuilder.join()}} with no join cond), the join cond is
> a {{RexLiteral}} with a value of {{true}}.
> Suggested fix is to handle the case with this specific join condition before
> {{convertConditionToSqlNode()}}:
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Comment Edited] (CALCITE-1792) RelToSqlConverter doesn't handle cartesian join (join cond as TRUE)
[
https://issues.apache.org/jira/browse/CALCITE-1792?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16019391#comment-16019391
]
Muhammad Gelbana edited comment on CALCITE-1792 at 5/22/17 10:02 AM:
-
[~julianhyde], why would you support the *FALSE* literal ? I'm working on
supporting cartesian and inner joins for Drill and I had to do the following. I
modified the
{code}org.apache.calcite.adapter.jdbc.JdbcRules.JdbcJoin.implement(JdbcImplementor){code}
implementation to be
{code:java}
public JdbcImplementor.Result implement(JdbcImplementor implementor) {
final JdbcImplementor.Result leftResult = implementor.visitChild(0,
left);
final JdbcImplementor.Result rightResult = implementor.visitChild(1,
right);
final JdbcImplementor.Context leftContext =
leftResult.qualifiedContext();
final JdbcImplementor.Context rightContext =
rightResult.qualifiedContext();
SqlNode sqlCondition = null;
JoinConditionType joinCondition = JoinConditionType.NONE;
JoinType joinType = JoinType.COMMA;
if (leftContext != null && rightContext != null) {
sqlCondition = convertConditionToSqlNode(condition, leftContext,
rightContext, left.getRowType().getFieldCount());
if (sqlCondition != null) {
joinType = JoinType.INNER;
joinCondition = JoinConditionType.ON;
}
}
SqlJoin join = new SqlJoin(POS, leftResult.asFrom(),
SqlLiteral.createBoolean(false, POS), joinType.symbol(POS),
rightResult.asFrom(), joinCondition.symbol(POS),
sqlCondition);
return implementor.result(join, leftResult, rightResult);
}
{code}
And the convertConditionToSqlNode method to accept RexLiteral
{code:java}
if (!(node instanceof RexCall) && !(node instanceof RexLiteral)) {
throw new AssertionError(node);
}
{code}
and added a Literal case
{code:java}
case LITERAL:
RexLiteral literal = (RexLiteral) node;
if (literal.isAlwaysTrue()) {
return null;
}
break;
{code}
was (Author: mgelbana):
[~julianhyde], why would you support the FALSE literal ? I'm working on
supporting cartesian and inner joins for Drill and I had to do why you did. I
modified the
org.apache.calcite.adapter.jdbc.JdbcRules.JdbcJoin.implement(JdbcImplementor)
implementation to be
{code:java}
public JdbcImplementor.Result implement(JdbcImplementor implementor) {
final JdbcImplementor.Result leftResult = implementor.visitChild(0,
left);
final JdbcImplementor.Result rightResult = implementor.visitChild(1,
right);
final JdbcImplementor.Context leftContext =
leftResult.qualifiedContext();
final JdbcImplementor.Context rightContext =
rightResult.qualifiedContext();
SqlNode sqlCondition = null;
JoinConditionType joinCondition = JoinConditionType.NONE;
JoinType joinType = JoinType.COMMA;
if (leftContext != null && rightContext != null) {
sqlCondition = convertConditionToSqlNode(condition, leftContext,
rightContext, left.getRowType().getFieldCount());
if (sqlCondition != null) {
joinType = JoinType.INNER;
joinCondition = JoinConditionType.ON;
}
}
SqlJoin join = new SqlJoin(POS, leftResult.asFrom(),
SqlLiteral.createBoolean(false, POS), joinType.symbol(POS),
rightResult.asFrom(), joinCondition.symbol(POS),
sqlCondition);
return implementor.result(join, leftResult, rightResult);
}
{code}
And the convertConditionToSqlNode method to accept RexLiteral
{code:java}
if (!(node instanceof RexCall) && !(node instanceof RexLiteral)) {
throw new AssertionError(node);
}
{code}
and added a Literal case
{code:java}
case LITERAL:
RexLiteral literal = (RexLiteral) node;
if (literal.isAlwaysTrue()) {
return null;
}
break;
{code}
> RelToSqlConverter doesn't handle cartesian join (join cond as TRUE)
> ---
>
> Key: CALCITE-1792
> URL: https://issues.apache.org/jira/browse/CALCITE-1792
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Sergiy Simonov
>Assignee: Jess Balint
>Priority: Minor
> Fix For: 1.13.0
>
>
> this test fails (added in {{RelToSqlConverterTest}}):
> {code}
> @Test public void testCartesianProductWithFullJoinSyntax() {
> String query = "select * from \"department\"\n"
> + "FULL JOIN \"employee\" ON TRUE";
> String expected = "SELECT *\nFROM \"foodmart\".\"department\"\n"
> + "FULL JOIN \"foodmart\".\"employee\" ON TRUE";
> sql(query).ok(expected);
> }
> {code}
> {{RelToSqlConverter}} is
[jira] [Commented] (CALCITE-1645) Row per match syntax support for MATCH_RECOGNIZE
[ https://issues.apache.org/jira/browse/CALCITE-1645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16019466#comment-16019466 ] Zhiqiang He commented on CALCITE-1645: -- [~julianhyde] 2,3,4: fixed, please review it. thanks. 1: The boolean type default value is false, it means "ONE ROW PER MATCH", it will changed many test case. and these test case reltosql result is not equals input sql. 5: every test case in match recoginze is test different case. It can not be removed. and I think some test case can be merged. I will merged it in next merge request. > Row per match syntax support for MATCH_RECOGNIZE > - > > Key: CALCITE-1645 > URL: https://issues.apache.org/jira/browse/CALCITE-1645 > Project: Calcite > Issue Type: Sub-task > Components: core >Affects Versions: 1.11.0 >Reporter: Zhiqiang He >Assignee: Zhiqiang He > Labels: features > > h1. [ONE ROW | ALL ROWS] PER MATCH: Choosing Summaries or Details for Each > Match > You will sometimes want summary data about the matches and other times need > details. You can do that with the following SQL: > * ONE ROW PER MATCH > Each match produces one summary row. This is the default. > * ALL ROWS PER MATCH > A match spanning multiple rows will produce one output row for each row in > the match. > The output is explained in "Row Pattern Output". > The MATCH_RECOGNIZE clause may find a match with zero rows. For an empty > match, ONE ROW PER MATCH returns a summary row: the PARTITION BY columns take > the values from the row where the empty match occurs, and the measure columns > are evaluated over an empty set of rows. > ALL ROWS PER MATCH has three suboptions: > * ALL ROWS PER MATCH SHOW EMPTY MATCHES > * ALL ROWS PER MATCH OMIT EMPTY MATCHES > * ALL ROWS PER MATCH WITH UNMATCHED ROWS -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Updated] (CALCITE-1797) Support view partial rewriting in aggregate materialized view rewriting
[ https://issues.apache.org/jira/browse/CALCITE-1797?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jesus Camacho Rodriguez updated CALCITE-1797: - Description: Simple extension for AbstractMaterializedViewRule similar to CALCITE-1791, however for views/queries rooted with an Aggregate node. (was: Simple extension for AbstractMaterializedViewRule similar to CALCITE-1791, however for views/queries rooted with an Aggregate node. In this case, we need to rely on FK-UK relationship to check whether the missing tables are just appending columns to the view result or even filtering the result, but not changing their multiplicity. If they meet these requirements, we can join the missing tables to the view and the view plan (that will be modified accordingly), and let the rewriting algorithm work the same way and enforce any missing predicate.) > Support view partial rewriting in aggregate materialized view rewriting > --- > > Key: CALCITE-1797 > URL: https://issues.apache.org/jira/browse/CALCITE-1797 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Jesus Camacho Rodriguez >Assignee: Jesus Camacho Rodriguez > Fix For: 1.13.0 > > > Simple extension for AbstractMaterializedViewRule similar to CALCITE-1791, > however for views/queries rooted with an Aggregate node. -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Commented] (CALCITE-1790) Simplify CASE P1 THEN P@ THEN ... ELSE TRUE/FALSE
[
https://issues.apache.org/jira/browse/CALCITE-1790?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16019619#comment-16019619
]
Remus Rusanu commented on CALCITE-1790:
---
With a test like CalciteSqlOperatorTest>SqlOperatorBaseTest.testAndOperator2
I'm seeing incorrect Java code being generated:
{code}
org.apache.calcite.DataContext root;
public org.apache.calcite.linq4j.Enumerable bind(final
org.apache.calcite.DataContext root0) {
root = root0;
final org.apache.calcite.linq4j.Enumerable _inputEnumerable =
org.apache.calcite.linq4j.Linq4j.asEnumerable(new Object[] {
new Object[] {
true,
null,
false}});
return new org.apache.calcite.linq4j.AbstractEnumerable(){
public org.apache.calcite.linq4j.Enumerator enumerator() {
return new org.apache.calcite.linq4j.Enumerator(){
public final org.apache.calcite.linq4j.Enumerator inputEnumerator =
_inputEnumerable.enumerator();
public void reset() {
inputEnumerator.reset();
}
public boolean moveNext() {
return inputEnumerator.moveNext();
}
public void close() {
inputEnumerator.close();
}
public Object current() {
final Object[] current = (Object[]) inputEnumerator.current();
final boolean inp2_ =
org.apache.calcite.runtime.SqlFunctions.toBoolean(current[2]);
final Boolean inp1_ = (Boolean) current[1];
final boolean inp0_ =
org.apache.calcite.runtime.SqlFunctions.toBoolean(current[0]);
final boolean v0 = !inp2_;
return inp2_ &&
org.apache.calcite.runtime.SqlFunctions.isTrue(inp1_) || inp0_ && v0 ?
Boolean.TRUE : (inp2_ &&
org.apache.calcite.runtime.SqlFunctions.isNotFalse(inp1_) ? (inp1_ == null ?
(Boolean) null : Boolean.TRUE) : Boolean.FALSE) == null || (inp0_ && v0 ||
$L4J$C$_null) ? (Boolean) null : Boolean.FALSE;
}
static final Object $L4J$C$_null = !null;
};
}:
};
}
public Class getElementType() {
return java.lang.Boolean.class;
}
{code}
The line {{static final Object $L4J$C$_null = !null;}} triggers compile error:
{code}
"pool-1-thread-7"
at
org.codehaus.janino.UnitCompiler.compileError(UnitCompiler.java:10092)
at
org.codehaus.janino.UnitCompiler.compileBoolean2(UnitCompiler.java:2848)
at org.codehaus.janino.UnitCompiler.access$4800(UnitCompiler.java:183)
at
org.codehaus.janino.UnitCompiler$8.visitNullLiteral(UnitCompiler.java:2810)
at org.codehaus.janino.Java$NullLiteral.accept(Java.java:4396)
at
org.codehaus.janino.UnitCompiler.compileBoolean(UnitCompiler.java:2830)
at
org.codehaus.janino.UnitCompiler.compileBoolean2(UnitCompiler.java:2860)
at org.codehaus.janino.UnitCompiler.access$4900(UnitCompiler.java:183)
at
org.codehaus.janino.UnitCompiler$8.visitUnaryOperation(UnitCompiler.java:2796)
at org.codehaus.janino.Java$UnaryOperation.accept(Java.java:3647)
at
org.codehaus.janino.UnitCompiler.compileBoolean(UnitCompiler.java:2830)
at org.codehaus.janino.UnitCompiler.compileGet2(UnitCompiler.java:3288)
at org.codehaus.janino.UnitCompiler.compileGet2(UnitCompiler.java:3711)
at org.codehaus.janino.UnitCompiler.access$6200(UnitCompiler.java:183)
at
org.codehaus.janino.UnitCompiler$10.visitUnaryOperation(UnitCompiler.java:3244)
at org.codehaus.janino.Java$UnaryOperation.accept(Java.java:3647)
at org.codehaus.janino.UnitCompiler.compileGet(UnitCompiler.java:3278)
at
org.codehaus.janino.UnitCompiler.compileGetValue(UnitCompiler.java:4345)
at org.codehaus.janino.UnitCompiler.compile2(UnitCompiler.java:1649)
at org.codehaus.janino.UnitCompiler.access$800(UnitCompiler.java:183)
at
org.codehaus.janino.UnitCompiler$4.visitFieldDeclaration(UnitCompiler.java:931)
at org.codehaus.janino.Java$FieldDeclaration.accept(Java.java:1818)
at org.codehaus.janino.UnitCompiler.compile(UnitCompiler.java:956)
at org.codehaus.janino.UnitCompiler.fakeCompile(UnitCompiler.java:968)
at org.codehaus.janino.UnitCompiler.compile2(UnitCompiler.java:652)
at org.codehaus.janino.UnitCompiler.compile2(UnitCompiler.java:620)
at org.codehaus.janino.UnitCompiler.access$200(UnitCompiler.java:183)
at
org.codehaus.janino.UnitCompiler$2.visitAnonymousClassDeclaration(UnitCompiler.java:343)
at
org.codehaus.janino.Java$AnonymousClassDeclaration.accept(Java.java:894)
at org.codehaus.janino.UnitCompiler.compile(UnitCompiler.java:352)
at org.codehaus.janino.UnitCompiler.compileGet2(UnitCompiler.java:4194)
at org.codehaus.janino.UnitCompiler.access$7300(UnitCompiler.java:183)
at
org.codehaus.janino.UnitCompiler$10.visitNewAnonymousCla
[jira] [Commented] (CALCITE-1790) Simplify CASE P1 THEN P@ THEN ... ELSE TRUE/FALSE
[
https://issues.apache.org/jira/browse/CALCITE-1790?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16019622#comment-16019622
]
Remus Rusanu commented on CALCITE-1790:
---
I'm going to propose to pull the plug on this one. As I see it the risk
outweighs the benefit. It triggers many test failures the are possibly exposing
existing problems, but overall the end result is less stable. And I think the
problems will keep piling, my hypothesis is that the breaking of the evaluation
order implicit in CASE is exposing too many sensistive code areas to handle
null/unknown when they did not expect before.
Add to this the fact that the complexity cap would make the behavior surprising
(sometime CASE gets converted, sometimes it does not).
> Simplify CASE P1 THEN P@ THEN ... ELSE TRUE/FALSE
> ---
>
> Key: CALCITE-1790
> URL: https://issues.apache.org/jira/browse/CALCITE-1790
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Remus Rusanu
>Assignee: Remus Rusanu
>Priority: Minor
>
> In HIVE-14431 [~jcamachorodriguez] proposed a simplification for CASE when
> all branches are not nullable boolean expression into an alternative
> AND/OR/NOT based expression. This allows for more aggressive reductions and
> split/push-down on the whole. Meantime the simplifier code migrated to
> Calcite so I'm reviving this here.
> The proposed simplification is:
> {code}
> CASE
> WHEN p1 THEN ex1
> WHEN p2 THEN ex2
> ...
> WHEN pn THEN exn
> ELSE TRUE/FALSE
> END
> {code}
> to be transformed into:
> {code}
> (p1 AND ex1)
> OR (not(p1) AND p2 AND x2)
> ...
> OR (not(p1) AND not(p2) ... AND not(pn-1) AND Pn AND exn)
> [OR (not(p1) AND not(p2) ... AND not(pn))]
> {code}
> The last OR is depending on the ELSE branch being TRUE/FALSE.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Comment Edited] (CALCITE-1790) Simplify CASE P1 THEN P@ THEN ... ELSE TRUE/FALSE
[
https://issues.apache.org/jira/browse/CALCITE-1790?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16019622#comment-16019622
]
Remus Rusanu edited comment on CALCITE-1790 at 5/22/17 2:27 PM:
I'm going to propose to pull the plug on this one. As I see it the risk
outweighs the benefit. It triggers many test failures that are possibly
exposing existing problems, but overall the end result is less stable. And I
think the problems will keep piling, my hypothesis is that the breaking of the
evaluation order implicit in CASE is exposing too many sensistive code areas to
handle null/unknown when they did not expect before.
Add to this the fact that the complexity cap would make the behavior surprising
(sometime CASE gets converted, sometimes it does not).
was (Author: rusanu):
I'm going to propose to pull the plug on this one. As I see it the risk
outweighs the benefit. It triggers many test failures the are possibly exposing
existing problems, but overall the end result is less stable. And I think the
problems will keep piling, my hypothesis is that the breaking of the evaluation
order implicit in CASE is exposing too many sensistive code areas to handle
null/unknown when they did not expect before.
Add to this the fact that the complexity cap would make the behavior surprising
(sometime CASE gets converted, sometimes it does not).
> Simplify CASE P1 THEN P@ THEN ... ELSE TRUE/FALSE
> ---
>
> Key: CALCITE-1790
> URL: https://issues.apache.org/jira/browse/CALCITE-1790
> Project: Calcite
> Issue Type: Improvement
> Components: core
>Reporter: Remus Rusanu
>Assignee: Remus Rusanu
>Priority: Minor
>
> In HIVE-14431 [~jcamachorodriguez] proposed a simplification for CASE when
> all branches are not nullable boolean expression into an alternative
> AND/OR/NOT based expression. This allows for more aggressive reductions and
> split/push-down on the whole. Meantime the simplifier code migrated to
> Calcite so I'm reviving this here.
> The proposed simplification is:
> {code}
> CASE
> WHEN p1 THEN ex1
> WHEN p2 THEN ex2
> ...
> WHEN pn THEN exn
> ELSE TRUE/FALSE
> END
> {code}
> to be transformed into:
> {code}
> (p1 AND ex1)
> OR (not(p1) AND p2 AND x2)
> ...
> OR (not(p1) AND not(p2) ... AND not(pn-1) AND Pn AND exn)
> [OR (not(p1) AND not(p2) ... AND not(pn))]
> {code}
> The last OR is depending on the ELSE branch being TRUE/FALSE.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Resolved] (CALCITE-1796) Update materialized views documentation
[ https://issues.apache.org/jira/browse/CALCITE-1796?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Jesus Camacho Rodriguez resolved CALCITE-1796. -- Resolution: Fixed Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/e71157b . > Update materialized views documentation > --- > > Key: CALCITE-1796 > URL: https://issues.apache.org/jira/browse/CALCITE-1796 > Project: Calcite > Issue Type: Improvement >Reporter: Jesus Camacho Rodriguez >Assignee: Jesus Camacho Rodriguez > Fix For: 1.13.0 > > > After CALCITE-1731 and follow-up works such as CALCITE-1791, CALCITE-1795 and > CALCITE-1797, we need to update the documentation to reflect clearly the > coverage of the materialized view rewriting algorithm, as it is already > larger than the rewriting coverage of the paper used for the baseline > algorithm. -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Commented] (CALCITE-1797) Support view partial rewriting in aggregate materialized view rewriting
[ https://issues.apache.org/jira/browse/CALCITE-1797?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16019634#comment-16019634 ] Jesus Camacho Rodriguez commented on CALCITE-1797: -- Fix-up in http://git-wip-us.apache.org/repos/asf/calcite/commit/9917849 . I lifted the restrictions (only apply if FK-UK relationship exists) for this part of the rewriting as they are not necessary. > Support view partial rewriting in aggregate materialized view rewriting > --- > > Key: CALCITE-1797 > URL: https://issues.apache.org/jira/browse/CALCITE-1797 > Project: Calcite > Issue Type: Improvement > Components: core >Reporter: Jesus Camacho Rodriguez >Assignee: Jesus Camacho Rodriguez > Fix For: 1.13.0 > > > Simple extension for AbstractMaterializedViewRule similar to CALCITE-1791, > however for views/queries rooted with an Aggregate node. -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Created] (CALCITE-1800) JDBC adapter fails to SELECT FROM a UNION query
Viktor Batytskyi created CALCITE-1800:
-
Summary: JDBC adapter fails to SELECT FROM a UNION query
Key: CALCITE-1800
URL: https://issues.apache.org/jira/browse/CALCITE-1800
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.13.0
Reporter: Viktor Batytskyi
Assignee: Julian Hyde
Fix For: 1.12.0
Relational representation of the following query fails to be converter to sql
string in Calcite:
{code}
select sum(case when "product_id"=0 then "net_weight" else 0 end) as net_weight
from ( select "product_id", "net_weight" from "product"
union all
select "product_id", 0 as "net_weight" from "sales_fact_1997" ) t0
{code}
The conversion fails with:
{code}
java.lang.RuntimeException: While invoking method 'public
org.apache.calcite.rel.rel2sql.SqlImplementor$Result
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Aggregate)'
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:528)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:96)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:100)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.ok(RelToSqlConverterTest.java:1559)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.testUnionWrappedInASelect(RelToSqlConverterTest.java:654)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at
com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117)
at
com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42)
at
com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262)
at
com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84)
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 com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
Caused by: java.lang.reflect.InvocationTargetException
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)
... 31 more
Caused by: java.lang.RuntimeException: While invoking method 'public
org.apache.calcite.rel.rel2sql.SqlImplementor$Result
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Project)'
at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:528)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:96)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:100)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:179)
... 36 more
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke
[jira] [Commented] (CALCITE-1800) JDBC adapter fails to SELECT FROM a UNION query
[
https://issues.apache.org/jira/browse/CALCITE-1800?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16019782#comment-16019782
]
Viktor Batytskyi commented on CALCITE-1800:
---
[~julianhyde]
Please take a look: https://github.com/apache/calcite/pull/454
> JDBC adapter fails to SELECT FROM a UNION query
>
>
> Key: CALCITE-1800
> URL: https://issues.apache.org/jira/browse/CALCITE-1800
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.13.0
>Reporter: Viktor Batytskyi
>Assignee: Julian Hyde
> Fix For: 1.12.0
>
> Original Estimate: 2h
> Remaining Estimate: 2h
>
> Relational representation of the following query fails to be converter to sql
> string in Calcite:
> {code}
> select sum(case when "product_id"=0 then "net_weight" else 0 end) as
> net_weight
> from ( select "product_id", "net_weight" from "product"
> union all
> select "product_id", 0 as "net_weight" from "sales_fact_1997" ) t0
> {code}
> The conversion fails with:
> {code}
> java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplementor$Result
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Aggregate)'
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:528)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:96)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:100)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.ok(RelToSqlConverterTest.java:1559)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.testUnionWrappedInASelect(RelToSqlConverterTest.java:654)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
> at
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
> at
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
> at
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
> at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
> at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
> at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
> at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
> at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
> at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
> at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
> at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
> at
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117)
> at
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42)
> at
> com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262)
> at
> com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84)
> 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 com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
> Caused by: java.lang.reflect.InvocationTargetException
> 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)
> ... 31 more
> Caused by: java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplementor$Result
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Project)'
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUti
[jira] [Created] (CALCITE-1801) RelRunners connection garbage-collected prematurely
JD Zheng created CALCITE-1801:
-
Summary: RelRunners connection garbage-collected prematurely
Key: CALCITE-1801
URL: https://issues.apache.org/jira/browse/CALCITE-1801
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.13.0
Environment: osx 10.11.6, JavaSE-1.8, junit-4.12
Reporter: JD Zheng
Assignee: Julian Hyde
Priority: Minor
I am using RelRunners to execute pre-built relnode tree. It works perfectly
when I put the following code in the junit class:
protected String execute(RelNode rel) {
try (final PreparedStatement preparedStatement = RelRunners.run(rel)) {
final ResultSet resultSet = preparedStatement.executeQuery();
return printResult(resultSet, true);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
However,if I put these code inside a src class, the returned resultSet is
closed.
More interestingly, if I expand the RelRunners.run() in the method like this:
public static ResultSet execute(RelNode rel) {
/*
try (final PreparedStatement preparedStatement = RelRunners.run(rel)) {
return preparedStatement.executeQuery();
} catch (SQLException e) {
throw new AQLExecuteErrorException(e);
}
*/
try (Connection connection =
DriverManager.getConnection("jdbc:calcite:")) {
final RelRunner runner = connection.unwrap(RelRunner.class);
PreparedStatement preparedStatement = runner.prepare(rel);
return preparedStatement.executeQuery();
} catch (SQLException e) {
throw new AQLExecuteErrorException(e);
}
}
It works again.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Commented] (CALCITE-1801) RelRunners connection garbage-collected prematurely
[
https://issues.apache.org/jira/browse/CALCITE-1801?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020014#comment-16020014
]
JD Zheng commented on CALCITE-1801:
---
comments from Julian:
"Maybe we use some callback to create a connection. And maybe if you are using
an anonymous class it causes the connection to be garbage-collected sooner."
> RelRunners connection garbage-collected prematurely
> ---
>
> Key: CALCITE-1801
> URL: https://issues.apache.org/jira/browse/CALCITE-1801
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.13.0
> Environment: osx 10.11.6, JavaSE-1.8, junit-4.12
>Reporter: JD Zheng
>Assignee: Julian Hyde
>Priority: Minor
>
> I am using RelRunners to execute pre-built relnode tree. It works perfectly
> when I put the following code in the junit class:
>protected String execute(RelNode rel) {
> try (final PreparedStatement preparedStatement = RelRunners.run(rel))
> {
> final ResultSet resultSet = preparedStatement.executeQuery();
> return printResult(resultSet, true);
> } catch (SQLException e) {
> throw new RuntimeException(e);
> }
> }
> However,if I put these code inside a src class, the returned resultSet is
> closed.
> More interestingly, if I expand the RelRunners.run() in the method like this:
>public static ResultSet execute(RelNode rel) {
> /*
> try (final PreparedStatement preparedStatement = RelRunners.run(rel))
> {
> return preparedStatement.executeQuery();
> } catch (SQLException e) {
> throw new AQLExecuteErrorException(e);
> }
> */
> try (Connection connection =
> DriverManager.getConnection("jdbc:calcite:")) {
> final RelRunner runner = connection.unwrap(RelRunner.class);
> PreparedStatement preparedStatement = runner.prepare(rel);
> return preparedStatement.executeQuery();
> } catch (SQLException e) {
> throw new AQLExecuteErrorException(e);
> }
> }
> It works again.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Updated] (CALCITE-1801) RelRunners connection garbage-collected prematurely
[
https://issues.apache.org/jira/browse/CALCITE-1801?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
JD Zheng updated CALCITE-1801:
--
Attachment: RelBuilderTest.java
1. Added one helper class RelImplementer.
2. Added two tests (testRunWithRunnersInAWrapperClass() will fail,
testRunWithRunnerInAWrapperClass will succeed):
/** Tests {@link org.apache.calcite.tools.RelRunners} for a table scan +
filter
* query using a wrapper class */
@Test public void testRunWithRunnersInAWrapperClass() throws Exception {
// Equivalent SQL:
// SELECT * FROM EMP WHERE DEPTNO = 20
final RelBuilder builder = RelBuilder.create(config().build());
RelNode root =
builder.scan("EMP")
.filter(
builder.equals(builder.field("DEPTNO"), builder.literal(20)))
.build();
// Note that because the table has been resolved in the RelNode tree
// we do not need to supply a "schema" as context to the runner.
try (final ResultSet resultset = RelImplementer.execute(root)) {
String s = CalciteAssert.toString(resultset);
final String result = ""
+ "EMPNO=7369; ENAME=SMITH; JOB=CLERK; MGR=7902; HIREDATE=1980-12-17;
SAL=800.00; COMM=null; DEPTNO=20\n"
+ "EMPNO=7566; ENAME=JONES; JOB=MANAGER; MGR=7839;
HIREDATE=1981-02-04; SAL=2975.00; COMM=null; DEPTNO=20\n"
+ "EMPNO=7788; ENAME=SCOTT; JOB=ANALYST; MGR=7566;
HIREDATE=1987-04-19; SAL=3000.00; COMM=null; DEPTNO=20\n"
+ "EMPNO=7876; ENAME=ADAMS; JOB=CLERK; MGR=7788; HIREDATE=1987-05-23;
SAL=1100.00; COMM=null; DEPTNO=20\n"
+ "EMPNO=7902; ENAME=FORD; JOB=ANALYST; MGR=7566;
HIREDATE=1981-12-03; SAL=3000.00; COMM=null; DEPTNO=20\n";
assertThat(s, is(result));
}
}
/** Tests {@link org.apache.calcite.tools.RelRunner} for a table scan + filter
* query using a wrapper class */
@Test public void testRunWithRunnerInAWrapperClass() throws Exception {
// Equivalent SQL:
// SELECT * FROM EMP WHERE DEPTNO = 20
final RelBuilder builder = RelBuilder.create(config().build());
RelNode root =
builder.scan("EMP")
.filter(
builder.equals(builder.field("DEPTNO"), builder.literal(20)))
.build();
// Note that because the table has been resolved in the RelNode tree
// we do not need to supply a "schema" as context to the runner.
try (final ResultSet resultset = RelImplementer.executeWithRelRunner(root))
{
String s = CalciteAssert.toString(resultset);
final String result = ""
+ "EMPNO=7369; ENAME=SMITH; JOB=CLERK; MGR=7902; HIREDATE=1980-12-17;
SAL=800.00; COMM=null; DEPTNO=20\n"
+ "EMPNO=7566; ENAME=JONES; JOB=MANAGER; MGR=7839;
HIREDATE=1981-02-04; SAL=2975.00; COMM=null; DEPTNO=20\n"
+ "EMPNO=7788; ENAME=SCOTT; JOB=ANALYST; MGR=7566;
HIREDATE=1987-04-19; SAL=3000.00; COMM=null; DEPTNO=20\n"
+ "EMPNO=7876; ENAME=ADAMS; JOB=CLERK; MGR=7788; HIREDATE=1987-05-23;
SAL=1100.00; COMM=null; DEPTNO=20\n"
+ "EMPNO=7902; ENAME=FORD; JOB=ANALYST; MGR=7566;
HIREDATE=1981-12-03; SAL=3000.00; COMM=null; DEPTNO=20\n";
assertThat(s, is(result));
}
}
Test result:
Tests run: 83, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 5.26 sec <<<
FAILURE! - in org.apache.calcite.test.RelBuilderTest
testRunWithRunnersInAWrapperClass(org.apache.calcite.test.RelBuilderTest) Time
elapsed: 0.68 sec <<< ERROR!
java.sql.SQLException: next() called on closed cursor
at
org.apache.calcite.test.RelBuilderTest.testRunWithRunnersInAWrapperClass(RelBuilderTest.java:1739)
Results :
Tests in error:
RelBuilderTest.testRunWithRunnersInAWrapperClass:1739 ยป SQL next() called on
c...
Tests run: 83, Failures: 0, Errors: 1, Skipped: 0
> RelRunners connection garbage-collected prematurely
> ---
>
> Key: CALCITE-1801
> URL: https://issues.apache.org/jira/browse/CALCITE-1801
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.13.0
> Environment: osx 10.11.6, JavaSE-1.8, junit-4.12
>Reporter: JD Zheng
>Assignee: Julian Hyde
>Priority: Minor
> Attachments: RelBuilderTest.java
>
>
> I am using RelRunners to execute pre-built relnode tree. It works perfectly
> when I put the following code in the junit class:
>protected String execute(RelNode rel) {
> try (final PreparedStatement preparedStatement = RelRunners.run(rel))
> {
> final ResultSet resultSet = preparedStatement.executeQuery();
> return printResult(resultSet, true);
> } catch (SQLException e) {
> throw new RuntimeException(e);
> }
> }
> However,if I put these code inside a src class, the returned resultSet is
> closed.
> More interestingly, if I expand the R
[jira] [Created] (CALCITE-1802) Add post-aggregation step for Union in materialized view rewriting
Jesus Camacho Rodriguez created CALCITE-1802:
Summary: Add post-aggregation step for Union in materialized view
rewriting
Key: CALCITE-1802
URL: https://issues.apache.org/jira/browse/CALCITE-1802
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Jesus Camacho Rodriguez
Assignee: Jesus Camacho Rodriguez
Fix For: 1.13.0
Follow-up on CALCITE-1795.
Rewriting for Aggregate queries needs a post-aggregation step that is not
currently added.
Query:
{code:sql}
SELECT empid, deptname, SUM(salary) AS s
FROM emps
JOIN depts ON (emps.deptno = depts.deptno)
WHERE salary > 1
GROUP BY empid, deptname;
{code}
Materialized view definition:
{code:sql}
SELECT empid, deptname, SUM(salary) AS s
FROM emps
JOIN depts ON (emps.deptno = depts.deptno)
WHERE salary > 12000
GROUP BY empid, deptname;
{code}
Rewriting:
{code:sql}
SELECT empid, deptname, SUM(s)
FROM (
SELECT empid, deptname, s
FROM mv
UNION ALL
SELECT empid, deptname, SUM(salary) AS s
FROM emps
JOIN depts ON (emps.deptno = depts.deptno)
WHERE salary > 1 AND salary <= 12000
GROUP BY empid, deptname);
{code}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Updated] (CALCITE-1801) RelRunners connection garbage-collected prematurely
[
https://issues.apache.org/jira/browse/CALCITE-1801?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-1801:
-
Description:
I am using RelRunners to execute pre-built relnode tree. It works perfectly
when I put the following code in the junit class:
{code}
protected String execute(RelNode rel) {
try (final PreparedStatement preparedStatement = RelRunners.run(rel)) {
final ResultSet resultSet = preparedStatement.executeQuery();
return printResult(resultSet, true);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
{code}
However, if I put these code inside a src class, the returned resultSet is
closed.
More interestingly, if I expand the RelRunners.run() in the method like this:
{code}
public static ResultSet execute(RelNode rel) {
/*
try (final PreparedStatement preparedStatement = RelRunners.run(rel)) {
return preparedStatement.executeQuery();
} catch (SQLException e) {
throw new AQLExecuteErrorException(e);
}
*/
try (Connection connection =
DriverManager.getConnection("jdbc:calcite:")) {
final RelRunner runner = connection.unwrap(RelRunner.class);
PreparedStatement preparedStatement = runner.prepare(rel);
return preparedStatement.executeQuery();
} catch (SQLException e) {
throw new AQLExecuteErrorException(e);
}
}
{code}
It works again.
was:
I am using RelRunners to execute pre-built relnode tree. It works perfectly
when I put the following code in the junit class:
protected String execute(RelNode rel) {
try (final PreparedStatement preparedStatement = RelRunners.run(rel)) {
final ResultSet resultSet = preparedStatement.executeQuery();
return printResult(resultSet, true);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
However,if I put these code inside a src class, the returned resultSet is
closed.
More interestingly, if I expand the RelRunners.run() in the method like this:
public static ResultSet execute(RelNode rel) {
/*
try (final PreparedStatement preparedStatement = RelRunners.run(rel)) {
return preparedStatement.executeQuery();
} catch (SQLException e) {
throw new AQLExecuteErrorException(e);
}
*/
try (Connection connection =
DriverManager.getConnection("jdbc:calcite:")) {
final RelRunner runner = connection.unwrap(RelRunner.class);
PreparedStatement preparedStatement = runner.prepare(rel);
return preparedStatement.executeQuery();
} catch (SQLException e) {
throw new AQLExecuteErrorException(e);
}
}
It works again.
> RelRunners connection garbage-collected prematurely
> ---
>
> Key: CALCITE-1801
> URL: https://issues.apache.org/jira/browse/CALCITE-1801
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.13.0
> Environment: osx 10.11.6, JavaSE-1.8, junit-4.12
>Reporter: JD Zheng
>Assignee: Julian Hyde
>Priority: Minor
> Attachments: RelBuilderTest.java
>
>
> I am using RelRunners to execute pre-built relnode tree. It works perfectly
> when I put the following code in the junit class:
> {code}
>protected String execute(RelNode rel) {
> try (final PreparedStatement preparedStatement = RelRunners.run(rel))
> {
> final ResultSet resultSet = preparedStatement.executeQuery();
> return printResult(resultSet, true);
> } catch (SQLException e) {
> throw new RuntimeException(e);
> }
> }
> {code}
> However, if I put these code inside a src class, the returned resultSet is
> closed.
> More interestingly, if I expand the RelRunners.run() in the method like this:
> {code}
>public static ResultSet execute(RelNode rel) {
> /*
> try (final PreparedStatement preparedStatement = RelRunners.run(rel))
> {
> return preparedStatement.executeQuery();
> } catch (SQLException e) {
> throw new AQLExecuteErrorException(e);
> }
> */
> try (Connection connection =
> DriverManager.getConnection("jdbc:calcite:")) {
> final RelRunner runner = connection.unwrap(RelRunner.class);
> PreparedStatement preparedStatement = runner.prepare(rel);
> return preparedStatement.executeQuery();
> } catch (SQLException e) {
> throw new AQLExecuteErrorException(e);
> }
> }
> {code}
> It works again.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Comment Edited] (CALCITE-1801) RelRunners connection garbage-collected prematurely
[
https://issues.apache.org/jira/browse/CALCITE-1801?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020018#comment-16020018
]
Julian Hyde edited comment on CALCITE-1801 at 5/22/17 7:11 PM:
---
1. Added one helper class RelImplementer.
2. Added two tests (testRunWithRunnersInAWrapperClass() will fail,
testRunWithRunnerInAWrapperClass will succeed):
{code}
/** Tests {@link org.apache.calcite.tools.RelRunners} for a table scan +
filter
* query using a wrapper class */
@Test public void testRunWithRunnersInAWrapperClass() throws Exception {
// Equivalent SQL:
// SELECT * FROM EMP WHERE DEPTNO = 20
final RelBuilder builder = RelBuilder.create(config().build());
RelNode root =
builder.scan("EMP")
.filter(
builder.equals(builder.field("DEPTNO"), builder.literal(20)))
.build();
// Note that because the table has been resolved in the RelNode tree
// we do not need to supply a "schema" as context to the runner.
try (final ResultSet resultset = RelImplementer.execute(root)) {
String s = CalciteAssert.toString(resultset);
final String result = ""
+ "EMPNO=7369; ENAME=SMITH; JOB=CLERK; MGR=7902; HIREDATE=1980-12-17;
SAL=800.00; COMM=null; DEPTNO=20\n"
+ "EMPNO=7566; ENAME=JONES; JOB=MANAGER; MGR=7839;
HIREDATE=1981-02-04; SAL=2975.00; COMM=null; DEPTNO=20\n"
+ "EMPNO=7788; ENAME=SCOTT; JOB=ANALYST; MGR=7566;
HIREDATE=1987-04-19; SAL=3000.00; COMM=null; DEPTNO=20\n"
+ "EMPNO=7876; ENAME=ADAMS; JOB=CLERK; MGR=7788; HIREDATE=1987-05-23;
SAL=1100.00; COMM=null; DEPTNO=20\n"
+ "EMPNO=7902; ENAME=FORD; JOB=ANALYST; MGR=7566;
HIREDATE=1981-12-03; SAL=3000.00; COMM=null; DEPTNO=20\n";
assertThat(s, is(result));
}
}
/** Tests {@link org.apache.calcite.tools.RelRunner} for a table scan + filter
* query using a wrapper class */
@Test public void testRunWithRunnerInAWrapperClass() throws Exception {
// Equivalent SQL:
// SELECT * FROM EMP WHERE DEPTNO = 20
final RelBuilder builder = RelBuilder.create(config().build());
RelNode root =
builder.scan("EMP")
.filter(
builder.equals(builder.field("DEPTNO"), builder.literal(20)))
.build();
// Note that because the table has been resolved in the RelNode tree
// we do not need to supply a "schema" as context to the runner.
try (final ResultSet resultset = RelImplementer.executeWithRelRunner(root))
{
String s = CalciteAssert.toString(resultset);
final String result = ""
+ "EMPNO=7369; ENAME=SMITH; JOB=CLERK; MGR=7902; HIREDATE=1980-12-17;
SAL=800.00; COMM=null; DEPTNO=20\n"
+ "EMPNO=7566; ENAME=JONES; JOB=MANAGER; MGR=7839;
HIREDATE=1981-02-04; SAL=2975.00; COMM=null; DEPTNO=20\n"
+ "EMPNO=7788; ENAME=SCOTT; JOB=ANALYST; MGR=7566;
HIREDATE=1987-04-19; SAL=3000.00; COMM=null; DEPTNO=20\n"
+ "EMPNO=7876; ENAME=ADAMS; JOB=CLERK; MGR=7788; HIREDATE=1987-05-23;
SAL=1100.00; COMM=null; DEPTNO=20\n"
+ "EMPNO=7902; ENAME=FORD; JOB=ANALYST; MGR=7566;
HIREDATE=1981-12-03; SAL=3000.00; COMM=null; DEPTNO=20\n";
assertThat(s, is(result));
}
}
{code}
Test result:
{noformat}
Tests run: 83, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 5.26 sec <<<
FAILURE! - in org.apache.calcite.test.RelBuilderTest
testRunWithRunnersInAWrapperClass(org.apache.calcite.test.RelBuilderTest) Time
elapsed: 0.68 sec <<< ERROR!
java.sql.SQLException: next() called on closed cursor
at
org.apache.calcite.test.RelBuilderTest.testRunWithRunnersInAWrapperClass(RelBuilderTest.java:1739)
Results :
Tests in error:
RelBuilderTest.testRunWithRunnersInAWrapperClass:1739 ยป SQL next() called on
c...
Tests run: 83, Failures: 0, Errors: 1, Skipped: 0
{noformat}
was (Author: jdhwlp):
1. Added one helper class RelImplementer.
2. Added two tests (testRunWithRunnersInAWrapperClass() will fail,
testRunWithRunnerInAWrapperClass will succeed):
/** Tests {@link org.apache.calcite.tools.RelRunners} for a table scan +
filter
* query using a wrapper class */
@Test public void testRunWithRunnersInAWrapperClass() throws Exception {
// Equivalent SQL:
// SELECT * FROM EMP WHERE DEPTNO = 20
final RelBuilder builder = RelBuilder.create(config().build());
RelNode root =
builder.scan("EMP")
.filter(
builder.equals(builder.field("DEPTNO"), builder.literal(20)))
.build();
// Note that because the table has been resolved in the RelNode tree
// we do not need to supply a "schema" as context to the runner.
try (final ResultSet resultset = RelImplementer.execute(root)) {
String s = CalciteAssert.toString(resultset);
final String result = ""
+ "EMPNO=7369; ENAME=SMITH; JOB=CLERK; MGR=7902; HI
[jira] [Commented] (CALCITE-1792) RelToSqlConverter doesn't handle cartesian join (join cond as TRUE)
[
https://issues.apache.org/jira/browse/CALCITE-1792?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020043#comment-16020043
]
Julian Hyde commented on CALCITE-1792:
--
Let me reverse the question: why NOT support the FALSE literal?
I know it's somewhat arbitrary. I know FALSE doesn't appear in joins very
often, but it made sense to treat all boolean literals the same.
Also, I use RexNode.isAlwaysTrue and .isAlwaysFalse rather than looking for
just literals, so it will generate "TRUE" for "a_mandatory_column IS NOT NULL".
That's somewhat arbitrary too, but that's what [~ssimonov]'s patch did, so I
kept it.
> RelToSqlConverter doesn't handle cartesian join (join cond as TRUE)
> ---
>
> Key: CALCITE-1792
> URL: https://issues.apache.org/jira/browse/CALCITE-1792
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Sergiy Simonov
>Assignee: Jess Balint
>Priority: Minor
> Fix For: 1.13.0
>
>
> this test fails (added in {{RelToSqlConverterTest}}):
> {code}
> @Test public void testCartesianProductWithFullJoinSyntax() {
> String query = "select * from \"department\"\n"
> + "FULL JOIN \"employee\" ON TRUE";
> String expected = "SELECT *\nFROM \"foodmart\".\"department\"\n"
> + "FULL JOIN \"foodmart\".\"employee\" ON TRUE";
> sql(query).ok(expected);
> }
> {code}
> {{RelToSqlConverter}} is checking that the join condition is a {{RexCall}}.
> In this case (and {{RelBuilder.join()}} with no join cond), the join cond is
> a {{RexLiteral}} with a value of {{true}}.
> Suggested fix is to handle the case with this specific join condition before
> {{convertConditionToSqlNode()}}:
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Updated] (CALCITE-1799) OR IN Subquery conversion wrong
[
https://issues.apache.org/jira/browse/CALCITE-1799?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-1799:
-
Labels: sub-query (was: )
> OR IN Subquery conversion wrong
> ---
>
> Key: CALCITE-1799
> URL: https://issues.apache.org/jira/browse/CALCITE-1799
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.12.0
>Reporter: Gian Merlino
>Assignee: Julian Hyde
> Labels: sub-query
>
> This query:
> {code}
> select * from emp where deptno = 10 or deptno in (
> select dept.deptno from dept where deptno < 5)
> {code}
> Is converted to this by SqlToRelConverter:
> {code}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalFilter(condition=[OR(=($7, 10), true)])
> LogicalJoin(condition=[=($7, $9)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[<($0, 5)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> But that's not right. {code}LogicalFilter(condition=[OR(=($7, 10),
> true)]){code} is always true and is in the wrong place anyway (it's applied
> after the inner join, where all the deptno = 10 records have already been
> removed).
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Commented] (CALCITE-1799) OR IN Subquery conversion wrong
[
https://issues.apache.org/jira/browse/CALCITE-1799?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020055#comment-16020055
]
Julian Hyde commented on CALCITE-1799:
--
There are two ways to deal with sub-queries. The new way is to disabling
sub-query conversion in SqlToRelConverter, which causes RexSubQuery expressions
to be created, which are then handled by SubQueryRemoveRule. (Search for tests
that call {{withLateDecorrelation}}.) Does the problem exist if you do things
the new way?
> OR IN Subquery conversion wrong
> ---
>
> Key: CALCITE-1799
> URL: https://issues.apache.org/jira/browse/CALCITE-1799
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.12.0
>Reporter: Gian Merlino
>Assignee: Julian Hyde
> Labels: sub-query
>
> This query:
> {code}
> select * from emp where deptno = 10 or deptno in (
> select dept.deptno from dept where deptno < 5)
> {code}
> Is converted to this by SqlToRelConverter:
> {code}
> LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
> SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
> LogicalFilter(condition=[OR(=($7, 10), true)])
> LogicalJoin(condition=[=($7, $9)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0}])
> LogicalProject(DEPTNO=[$0])
> LogicalFilter(condition=[<($0, 5)])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> But that's not right. {code}LogicalFilter(condition=[OR(=($7, 10),
> true)]){code} is always true and is in the wrong place anyway (it's applied
> after the inner join, where all the deptno = 10 records have already been
> removed).
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Updated] (CALCITE-1798) In JDBC adapter, generate dialect-specific SQL for FLOOR operator
[
https://issues.apache.org/jira/browse/CALCITE-1798?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-1798:
-
Labels: dialect (was: )
> In JDBC adapter, generate dialect-specific SQL for FLOOR operator
> -
>
> Key: CALCITE-1798
> URL: https://issues.apache.org/jira/browse/CALCITE-1798
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Chris Baynes
>Assignee: Julian Hyde
> Labels: dialect
>
> The FLOOR operator (on dates) is currently broken for all jdbc dialects.
> The syntax allowed by the parser looks like: "FLOOR(datetime to timeUnit)".
> However no jdbc dialect (as far as I'm aware) actually name the function
> FLOOR:
> In postgres: DATE_TRUNC('year', my_datetime)
> In hsqldb: TRUNC ( my_datetime, '' )
> In oracle: TRUNC(my_datetime, 'YEAR')
> In mysql: There's no direct equivalent in mysql (though it could be emulated
> with some nasty timestamp diffing)
> The other issue is that the timeUnits are sometimes also named differently by
> each dialect (e.g. '' in hsqldb).
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Updated] (CALCITE-1798) In JDBC adapter, generate dialect-specific SQL for FLOOR operator
[
https://issues.apache.org/jira/browse/CALCITE-1798?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-1798:
-
Component/s: jdbc-adapter
> In JDBC adapter, generate dialect-specific SQL for FLOOR operator
> -
>
> Key: CALCITE-1798
> URL: https://issues.apache.org/jira/browse/CALCITE-1798
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Chris Baynes
>Assignee: Julian Hyde
> Labels: dialect
>
> The FLOOR operator (on dates) is currently broken for all jdbc dialects.
> The syntax allowed by the parser looks like: "FLOOR(datetime to timeUnit)".
> However no jdbc dialect (as far as I'm aware) actually name the function
> FLOOR:
> In postgres: DATE_TRUNC('year', my_datetime)
> In hsqldb: TRUNC ( my_datetime, '' )
> In oracle: TRUNC(my_datetime, 'YEAR')
> In mysql: There's no direct equivalent in mysql (though it could be emulated
> with some nasty timestamp diffing)
> The other issue is that the timeUnits are sometimes also named differently by
> each dialect (e.g. '' in hsqldb).
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Updated] (CALCITE-1798) In JDBC adapter, generate dialect-specific SQL for FLOOR operator
[
https://issues.apache.org/jira/browse/CALCITE-1798?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-1798:
-
Summary: In JDBC adapter, generate dialect-specific SQL for FLOOR operator
(was: Jdbc dialect specific datetime floor operators)
> In JDBC adapter, generate dialect-specific SQL for FLOOR operator
> -
>
> Key: CALCITE-1798
> URL: https://issues.apache.org/jira/browse/CALCITE-1798
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Chris Baynes
>Assignee: Julian Hyde
> Labels: dialect
>
> The FLOOR operator (on dates) is currently broken for all jdbc dialects.
> The syntax allowed by the parser looks like: "FLOOR(datetime to timeUnit)".
> However no jdbc dialect (as far as I'm aware) actually name the function
> FLOOR:
> In postgres: DATE_TRUNC('year', my_datetime)
> In hsqldb: TRUNC ( my_datetime, '' )
> In oracle: TRUNC(my_datetime, 'YEAR')
> In mysql: There's no direct equivalent in mysql (though it could be emulated
> with some nasty timestamp diffing)
> The other issue is that the timeUnits are sometimes also named differently by
> each dialect (e.g. '' in hsqldb).
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Resolved] (CALCITE-1798) In JDBC adapter, generate dialect-specific SQL for FLOOR operator
[
https://issues.apache.org/jira/browse/CALCITE-1798?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-1798.
--
Resolution: Fixed
Fix Version/s: 1.13.0
Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/f8ab9078. Thanks
for the PR, [~chris-baynes]!
bq. The convertToHsqlDb method isn't at all specific to flooring, it could also
be used in other timestamp functions. Where would be a good place to create
hsql dialect specifics?
I don't know. But I wouldn't worry too much - if those methods are static and
private or package-private you can freely refactor them as functionality gets
added.
bq. Would like to write some dialect specific tests to check the generated sql
output, perhaps a JdbcDialectTest class. Is this possible without having the
actual db available?
I added a test,
[RelToSqlConverterTest.testFloor()|https://insight.io/github.com/apache/calcite/blob/f8ab9078bae8c519ce4c86d741490779ea899a14/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java?line=642].
You can use that as a model for other cross-dialect SQL generation tests.
> In JDBC adapter, generate dialect-specific SQL for FLOOR operator
> -
>
> Key: CALCITE-1798
> URL: https://issues.apache.org/jira/browse/CALCITE-1798
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Reporter: Chris Baynes
>Assignee: Julian Hyde
> Labels: dialect
> Fix For: 1.13.0
>
>
> The FLOOR operator (on dates) is currently broken for all jdbc dialects.
> The syntax allowed by the parser looks like: "FLOOR(datetime to timeUnit)".
> However no jdbc dialect (as far as I'm aware) actually name the function
> FLOOR:
> In postgres: DATE_TRUNC('year', my_datetime)
> In hsqldb: TRUNC ( my_datetime, '' )
> In oracle: TRUNC(my_datetime, 'YEAR')
> In mysql: There's no direct equivalent in mysql (though it could be emulated
> with some nasty timestamp diffing)
> The other issue is that the timeUnits are sometimes also named differently by
> each dialect (e.g. '' in hsqldb).
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Commented] (CALCITE-1645) Row per match syntax support for MATCH_RECOGNIZE
[ https://issues.apache.org/jira/browse/CALCITE-1645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020155#comment-16020155 ] Julian Hyde commented on CALCITE-1645: -- Thanks for fixing 2, 3, 4. Regarding 1. I still think it should be boolean, not Boolean. The goal of a RelNode should be to make it easy to write rules. Which includes canonization: if two expressions are equivalent, they should be structurally the same. It is less important whether the generated SQL looks the same as the input. Also, we don't want people who write rules to have to know whether ONE ROW PER MATCH or ALL ROWS PER MATCH is the default. That should be sorted out at SQL-to-Rel time. (Did you know that people can write "COUNT(ALL x)" which means the same as "COUNT(x)" and is the opposite of "COUNT(DISTINCT x)"? But of course we don't record whether they wrote "ALL". AggregateCall.distinct is a boolean, not a Boolean.) Regarding 5. Merging some test cases next request would be great. Thanks. It's not urgent. I just want to keep some balance between number of tests (maintenance burden) and coverage. We can reduce the number of tests quite a bit, I think, without much drop in coverage. > Row per match syntax support for MATCH_RECOGNIZE > - > > Key: CALCITE-1645 > URL: https://issues.apache.org/jira/browse/CALCITE-1645 > Project: Calcite > Issue Type: Sub-task > Components: core >Affects Versions: 1.11.0 >Reporter: Zhiqiang He >Assignee: Zhiqiang He > Labels: features > > h1. [ONE ROW | ALL ROWS] PER MATCH: Choosing Summaries or Details for Each > Match > You will sometimes want summary data about the matches and other times need > details. You can do that with the following SQL: > * ONE ROW PER MATCH > Each match produces one summary row. This is the default. > * ALL ROWS PER MATCH > A match spanning multiple rows will produce one output row for each row in > the match. > The output is explained in "Row Pattern Output". > The MATCH_RECOGNIZE clause may find a match with zero rows. For an empty > match, ONE ROW PER MATCH returns a summary row: the PARTITION BY columns take > the values from the row where the empty match occurs, and the measure columns > are evaluated over an empty set of rows. > ALL ROWS PER MATCH has three suboptions: > * ALL ROWS PER MATCH SHOW EMPTY MATCHES > * ALL ROWS PER MATCH OMIT EMPTY MATCHES > * ALL ROWS PER MATCH WITH UNMATCHED ROWS -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Comment Edited] (CALCITE-1645) Row per match syntax support for MATCH_RECOGNIZE
[ https://issues.apache.org/jira/browse/CALCITE-1645?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020155#comment-16020155 ] Julian Hyde edited comment on CALCITE-1645 at 5/22/17 8:37 PM: --- Thanks for fixing 2, 3, 4. Regarding 1. I still think it should be boolean, not Boolean. The goal of a RelNode should be to make it easy to write rules. Which includes canonization: if two expressions are equivalent, they should be structurally the same. It is less important whether the generated SQL looks the same as the input. Also, we don't want people who write rules to have to know whether ONE ROW PER MATCH or ALL ROWS PER MATCH is the default. That should be sorted out at SQL-to-Rel time. (Did you know that people can write "COUNT(ALL x)" which means the same as "COUNT\(x)" and is the opposite of "COUNT(DISTINCT x)"? But of course we don't record whether they wrote "ALL". AggregateCall.distinct is a boolean, not a Boolean.) Regarding 5. Merging some test cases next request would be great. Thanks. It's not urgent. I just want to keep some balance between number of tests (maintenance burden) and coverage. We can reduce the number of tests quite a bit, I think, without much drop in coverage. was (Author: julianhyde): Thanks for fixing 2, 3, 4. Regarding 1. I still think it should be boolean, not Boolean. The goal of a RelNode should be to make it easy to write rules. Which includes canonization: if two expressions are equivalent, they should be structurally the same. It is less important whether the generated SQL looks the same as the input. Also, we don't want people who write rules to have to know whether ONE ROW PER MATCH or ALL ROWS PER MATCH is the default. That should be sorted out at SQL-to-Rel time. (Did you know that people can write "COUNT(ALL x)" which means the same as "COUNT(x)" and is the opposite of "COUNT(DISTINCT x)"? But of course we don't record whether they wrote "ALL". AggregateCall.distinct is a boolean, not a Boolean.) Regarding 5. Merging some test cases next request would be great. Thanks. It's not urgent. I just want to keep some balance between number of tests (maintenance burden) and coverage. We can reduce the number of tests quite a bit, I think, without much drop in coverage. > Row per match syntax support for MATCH_RECOGNIZE > - > > Key: CALCITE-1645 > URL: https://issues.apache.org/jira/browse/CALCITE-1645 > Project: Calcite > Issue Type: Sub-task > Components: core >Affects Versions: 1.11.0 >Reporter: Zhiqiang He >Assignee: Zhiqiang He > Labels: features > > h1. [ONE ROW | ALL ROWS] PER MATCH: Choosing Summaries or Details for Each > Match > You will sometimes want summary data about the matches and other times need > details. You can do that with the following SQL: > * ONE ROW PER MATCH > Each match produces one summary row. This is the default. > * ALL ROWS PER MATCH > A match spanning multiple rows will produce one output row for each row in > the match. > The output is explained in "Row Pattern Output". > The MATCH_RECOGNIZE clause may find a match with zero rows. For an empty > match, ONE ROW PER MATCH returns a summary row: the PARTITION BY columns take > the values from the row where the empty match occurs, and the measure columns > are evaluated over an empty set of rows. > ALL ROWS PER MATCH has three suboptions: > * ALL ROWS PER MATCH SHOW EMPTY MATCHES > * ALL ROWS PER MATCH OMIT EMPTY MATCHES > * ALL ROWS PER MATCH WITH UNMATCHED ROWS -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Updated] (CALCITE-1800) JDBC adapter fails to SELECT FROM a UNION query
[
https://issues.apache.org/jira/browse/CALCITE-1800?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-1800:
-
Affects Version/s: (was: 1.13.0)
1.9.0
> JDBC adapter fails to SELECT FROM a UNION query
>
>
> Key: CALCITE-1800
> URL: https://issues.apache.org/jira/browse/CALCITE-1800
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.9.0
>Reporter: Viktor Batytskyi
>Assignee: Julian Hyde
> Fix For: 1.13.0
>
> Original Estimate: 2h
> Remaining Estimate: 2h
>
> Relational representation of the following query fails to be converter to sql
> string in Calcite:
> {code}
> select sum(case when "product_id"=0 then "net_weight" else 0 end) as
> net_weight
> from ( select "product_id", "net_weight" from "product"
> union all
> select "product_id", 0 as "net_weight" from "sales_fact_1997" ) t0
> {code}
> The conversion fails with:
> {code}
> java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplementor$Result
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Aggregate)'
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:528)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:96)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:100)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.ok(RelToSqlConverterTest.java:1559)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.testUnionWrappedInASelect(RelToSqlConverterTest.java:654)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
> at
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
> at
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
> at
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
> at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
> at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
> at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
> at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
> at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
> at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
> at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
> at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
> at
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117)
> at
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42)
> at
> com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262)
> at
> com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84)
> 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 com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
> Caused by: java.lang.reflect.InvocationTargetException
> 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)
> ... 31 more
> Caused by: java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplementor$Result
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Project)'
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:528)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.d
[jira] [Updated] (CALCITE-1800) JDBC adapter fails to SELECT FROM a UNION query
[
https://issues.apache.org/jira/browse/CALCITE-1800?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-1800:
-
Fix Version/s: (was: 1.12.0)
1.13.0
> JDBC adapter fails to SELECT FROM a UNION query
>
>
> Key: CALCITE-1800
> URL: https://issues.apache.org/jira/browse/CALCITE-1800
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.9.0
>Reporter: Viktor Batytskyi
>Assignee: Julian Hyde
> Fix For: 1.13.0
>
> Original Estimate: 2h
> Remaining Estimate: 2h
>
> Relational representation of the following query fails to be converter to sql
> string in Calcite:
> {code}
> select sum(case when "product_id"=0 then "net_weight" else 0 end) as
> net_weight
> from ( select "product_id", "net_weight" from "product"
> union all
> select "product_id", 0 as "net_weight" from "sales_fact_1997" ) t0
> {code}
> The conversion fails with:
> {code}
> java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplementor$Result
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Aggregate)'
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:528)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:96)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:100)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.ok(RelToSqlConverterTest.java:1559)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.testUnionWrappedInASelect(RelToSqlConverterTest.java:654)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
> at
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
> at
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
> at
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
> at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
> at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
> at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
> at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
> at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
> at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
> at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
> at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
> at
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117)
> at
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42)
> at
> com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262)
> at
> com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84)
> 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 com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
> Caused by: java.lang.reflect.InvocationTargetException
> 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)
> ... 31 more
> Caused by: java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplementor$Result
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Project)'
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:528)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch
[jira] [Created] (CALCITE-1803) Add post aggregation support in Druid to optimize druid queries.
Junxian Wu created CALCITE-1803:
---
Summary: Add post aggregation support in Druid to optimize druid
queries.
Key: CALCITE-1803
URL: https://issues.apache.org/jira/browse/CALCITE-1803
Project: Calcite
Issue Type: Bug
Components: druid
Affects Versions: 1.11.0
Reporter: Junxian Wu
Assignee: Julian Hyde
Druid post aggregations are not supported when parsing SQL queries. By
implementing post aggregations, we can offload some computation to the druid
cluster rather than aggregate on the client side.
Example usage:
{{SELECT SUM("column1") - SUM("column2") FROM "table";}}
This query will be parsed into two separate Druid aggregations according to
current rules. Then the results will be subtracted in Calcite. By using the
{{postAggregations}} field in the druid query, the subtraction could be done in
Druid cluster. Although the previous example is simple, the difference will be
obvious when the number of result rows are large. (Multiple rows result will
happen when group by is used).
Questions:
After I push Post aggregation into Druid query, what should I change on the
project relational correlation? In the case of the example above, the
{{BindableProject}} will have the expression to representation the subtraction.
If I push the post aggregation into druid query, the expression of subtraction
should be replaced by the representation of the post aggregations result. For
now, the project expression seems can only point to the aggregations results.
Since post aggregations have to point to aggregations results too, it could not
be placed in the parallel level as aggregation. Where should I put post
aggregations?
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Updated] (CALCITE-1803) Add post aggregation support in Druid to optimize druid queries.
[
https://issues.apache.org/jira/browse/CALCITE-1803?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Junxian Wu updated CALCITE-1803:
Issue Type: New Feature (was: Bug)
> Add post aggregation support in Druid to optimize druid queries.
>
>
> Key: CALCITE-1803
> URL: https://issues.apache.org/jira/browse/CALCITE-1803
> Project: Calcite
> Issue Type: New Feature
> Components: druid
>Affects Versions: 1.11.0
>Reporter: Junxian Wu
>Assignee: Julian Hyde
> Original Estimate: 336h
> Remaining Estimate: 336h
>
> Druid post aggregations are not supported when parsing SQL queries. By
> implementing post aggregations, we can offload some computation to the druid
> cluster rather than aggregate on the client side.
> Example usage:
> {{SELECT SUM("column1") - SUM("column2") FROM "table";}}
> This query will be parsed into two separate Druid aggregations according to
> current rules. Then the results will be subtracted in Calcite. By using the
> {{postAggregations}} field in the druid query, the subtraction could be done
> in Druid cluster. Although the previous example is simple, the difference
> will be obvious when the number of result rows are large. (Multiple rows
> result will happen when group by is used).
> Questions:
> After I push Post aggregation into Druid query, what should I change on the
> project relational correlation? In the case of the example above, the
> {{BindableProject}} will have the expression to representation the
> subtraction. If I push the post aggregation into druid query, the expression
> of subtraction should be replaced by the representation of the post
> aggregations result. For now, the project expression seems can only point to
> the aggregations results. Since post aggregations have to point to
> aggregations results too, it could not be placed in the parallel level as
> aggregation. Where should I put post aggregations?
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Commented] (CALCITE-1800) JDBC adapter fails to SELECT FROM a UNION query
[
https://issues.apache.org/jira/browse/CALCITE-1800?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020481#comment-16020481
]
Julian Hyde commented on CALCITE-1800:
--
I can reproduce the case, and I have verified that the one line fix solves the
problem, but I don't understand quite why. My intuition says that a UNION
doesn't per se require an alias, yet we are requiring one.
[~minjikim], Can please you review this fix?
> JDBC adapter fails to SELECT FROM a UNION query
>
>
> Key: CALCITE-1800
> URL: https://issues.apache.org/jira/browse/CALCITE-1800
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.9.0
>Reporter: Viktor Batytskyi
>Assignee: Julian Hyde
> Fix For: 1.13.0
>
> Original Estimate: 2h
> Remaining Estimate: 2h
>
> Relational representation of the following query fails to be converter to sql
> string in Calcite:
> {code}
> select sum(case when "product_id"=0 then "net_weight" else 0 end) as
> net_weight
> from ( select "product_id", "net_weight" from "product"
> union all
> select "product_id", 0 as "net_weight" from "sales_fact_1997" ) t0
> {code}
> The conversion fails with:
> {code}
> java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplementor$Result
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Aggregate)'
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:528)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:96)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:100)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.ok(RelToSqlConverterTest.java:1559)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.testUnionWrappedInASelect(RelToSqlConverterTest.java:654)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
> at
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
> at
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
> at
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
> at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
> at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
> at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
> at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
> at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
> at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
> at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
> at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
> at
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117)
> at
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42)
> at
> com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262)
> at
> com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84)
> 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 com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
> Caused by: java.lang.reflect.InvocationTargetException
> 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)
> ... 31 more
> Caused by: java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplement
[jira] [Commented] (CALCITE-1803) Add post aggregation support in Druid to optimize druid queries.
[
https://issues.apache.org/jira/browse/CALCITE-1803?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020492#comment-16020492
]
Julian Hyde commented on CALCITE-1803:
--
Do you mind if we change the terminology? "Post aggregation" suggests
aggregation that happens after something. But I think you mean
"Post-aggregation projects". Or in simpler English, "Projects after
aggregation".
To answer your question: You will need to have a DruidQuery that contains a
Scan followed by an Aggregate followed by a Project.
Currently DruidProjectRule will not allow the Project to be pushed in, because
"sap" (scan, aggregate, project) is not a valid signature according to
DruidQuery.VALID_SIG. But you should make it valid.
I'm curious:
* Does Druid allow filters after aggregation? (I.e. HAVING)
* I know that Druid allows sort after aggregation. But is this before or after
the post-aggregation projects?
> Add post aggregation support in Druid to optimize druid queries.
>
>
> Key: CALCITE-1803
> URL: https://issues.apache.org/jira/browse/CALCITE-1803
> Project: Calcite
> Issue Type: New Feature
> Components: druid
>Affects Versions: 1.11.0
>Reporter: Junxian Wu
>Assignee: Julian Hyde
> Original Estimate: 336h
> Remaining Estimate: 336h
>
> Druid post aggregations are not supported when parsing SQL queries. By
> implementing post aggregations, we can offload some computation to the druid
> cluster rather than aggregate on the client side.
> Example usage:
> {{SELECT SUM("column1") - SUM("column2") FROM "table";}}
> This query will be parsed into two separate Druid aggregations according to
> current rules. Then the results will be subtracted in Calcite. By using the
> {{postAggregations}} field in the druid query, the subtraction could be done
> in Druid cluster. Although the previous example is simple, the difference
> will be obvious when the number of result rows are large. (Multiple rows
> result will happen when group by is used).
> Questions:
> After I push Post aggregation into Druid query, what should I change on the
> project relational correlation? In the case of the example above, the
> {{BindableProject}} will have the expression to representation the
> subtraction. If I push the post aggregation into druid query, the expression
> of subtraction should be replaced by the representation of the post
> aggregations result. For now, the project expression seems can only point to
> the aggregations results. Since post aggregations have to point to
> aggregations results too, it could not be placed in the parallel level as
> aggregation. Where should I put post aggregations?
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Comment Edited] (CALCITE-1800) JDBC adapter fails to SELECT FROM a UNION query
[
https://issues.apache.org/jira/browse/CALCITE-1800?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020504#comment-16020504
]
MinJi Kim edited comment on CALCITE-1800 at 5/23/17 1:06 AM:
-
Sure thing! I will take a look at it soon.
was (Author: minjikim):
Sure thing! I will take a look it soon.
> JDBC adapter fails to SELECT FROM a UNION query
>
>
> Key: CALCITE-1800
> URL: https://issues.apache.org/jira/browse/CALCITE-1800
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.9.0
>Reporter: Viktor Batytskyi
>Assignee: Julian Hyde
> Fix For: 1.13.0
>
> Original Estimate: 2h
> Remaining Estimate: 2h
>
> Relational representation of the following query fails to be converter to sql
> string in Calcite:
> {code}
> select sum(case when "product_id"=0 then "net_weight" else 0 end) as
> net_weight
> from ( select "product_id", "net_weight" from "product"
> union all
> select "product_id", 0 as "net_weight" from "sales_fact_1997" ) t0
> {code}
> The conversion fails with:
> {code}
> java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplementor$Result
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Aggregate)'
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:528)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:96)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:100)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.ok(RelToSqlConverterTest.java:1559)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.testUnionWrappedInASelect(RelToSqlConverterTest.java:654)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
> at
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
> at
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
> at
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
> at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
> at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
> at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
> at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
> at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
> at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
> at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
> at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
> at
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117)
> at
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42)
> at
> com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262)
> at
> com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84)
> 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 com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
> Caused by: java.lang.reflect.InvocationTargetException
> 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)
> ... 31 more
> Caused by: java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplementor$Result
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Project)
[jira] [Commented] (CALCITE-1800) JDBC adapter fails to SELECT FROM a UNION query
[
https://issues.apache.org/jira/browse/CALCITE-1800?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020504#comment-16020504
]
MinJi Kim commented on CALCITE-1800:
Sure thing! I will take a look it soon.
> JDBC adapter fails to SELECT FROM a UNION query
>
>
> Key: CALCITE-1800
> URL: https://issues.apache.org/jira/browse/CALCITE-1800
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.9.0
>Reporter: Viktor Batytskyi
>Assignee: Julian Hyde
> Fix For: 1.13.0
>
> Original Estimate: 2h
> Remaining Estimate: 2h
>
> Relational representation of the following query fails to be converter to sql
> string in Calcite:
> {code}
> select sum(case when "product_id"=0 then "net_weight" else 0 end) as
> net_weight
> from ( select "product_id", "net_weight" from "product"
> union all
> select "product_id", 0 as "net_weight" from "sales_fact_1997" ) t0
> {code}
> The conversion fails with:
> {code}
> java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplementor$Result
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Aggregate)'
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:528)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:96)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:100)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.ok(RelToSqlConverterTest.java:1559)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.testUnionWrappedInASelect(RelToSqlConverterTest.java:654)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
> at
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
> at
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
> at
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
> at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
> at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
> at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
> at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
> at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
> at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
> at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
> at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
> at
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117)
> at
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42)
> at
> com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262)
> at
> com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84)
> 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 com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
> Caused by: java.lang.reflect.InvocationTargetException
> 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)
> ... 31 more
> Caused by: java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplementor$Result
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Project)'
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:528)
> at
> org.apache.calcite.rel.re
[jira] [Commented] (CALCITE-1787) thetaSketch Support for Druid Adapter
[
https://issues.apache.org/jira/browse/CALCITE-1787?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020575#comment-16020575
]
Julian Hyde commented on CALCITE-1787:
--
Regarding 1. I'm assuming that you want to be able to write "select
thetaSketch(customerId) from sales".
If so, I think that's not a great idea, because it's not declarative. You don't
write "emp MERGE JOIN dept", you write "emp JOIN dept" and let the optimizer
decide which algorithm to use.
I'd prefer we wrote "select count(distinct customerId) approximate (algorithm
thetaSketch) from sales" or just "select count(distinct customerId) from sales"
(using a session preference that thetaSketch or hyperLogLog can be used).
Regarding 2. HISTOGRAM_AGG is still in the code (see SqlStdOperatorTable) but
it is not currently used. When it was used, we would generate plans like this:
{noformat}
SELECT orderId, productId, min(quantity) OVER w, max(quantity) OVER w
FROM Orders
WINDOW w AS (PARTITION BY productId
ORDER BY orderTimestamp
RANGE INTERVAL '1' HOUR PRECEDING)
Project($0, $1, $HistogramMin($2), $HistogramMax($2))
Window($0, $1, HISTOGRAM_AGG($3) over (partition by $1 order by $2 range
interval '1' hour preceding))
Scan(Orders)
{noformat}
As you can see, we compute one aggregate, a histogram (basically TreeSet on top
of a FIFO queue), then we have two extractor functions ($HistogramMin and
$HistogramMax) to get the min and max from it.
> thetaSketch Support for Druid Adapter
> -
>
> Key: CALCITE-1787
> URL: https://issues.apache.org/jira/browse/CALCITE-1787
> Project: Calcite
> Issue Type: New Feature
> Components: druid
>Affects Versions: 1.12.0
>Reporter: Zain Humayun
>Assignee: Julian Hyde
>Priority: Minor
> Fix For: 1.12.0
>
>
> Currently, the Druid adapter does not support the
> [thetaSketch|http://druid.io/docs/latest/development/extensions-core/datasketches-aggregators.html]
> aggregate type, which is used to measure the cardinality of a column
> quickly. Many Druid instances support theta sketches, so I think it would be
> a nice feature to have.
> I've been looking at the Druid adapter, and propose we add a new DruidType
> called {{thetaSketch}} and then add logic in the {{getJsonAggregation}}
> method in class {{DruidQuery}} to generate the {{thetaSketch}} aggregate.
> This will require accessing information about the columns (what data type
> they are) so that the thetaSketch aggregate is only produced if the column's
> type is {{thetaSketch}}.
> Also, I've noticed that a {{hyperUnique}} DruidType is currently defined, but
> a {{hyperUnique}} aggregate is never produced. Since both are approximate
> aggregators, I could also couple in the logic for {{hyperUnique}}.
> I'd love to hear your thoughts on my approach, and any suggestions you have
> for this feature.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Resolved] (CALCITE-1742) Create a read-consistent view of CalciteSchema for each statement compilation
[
https://issues.apache.org/jira/browse/CALCITE-1742?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-1742.
--
Resolution: Fixed
Fix Version/s: 1.13.0
Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/4519ef6e.
> Create a read-consistent view of CalciteSchema for each statement compilation
> -
>
> Key: CALCITE-1742
> URL: https://issues.apache.org/jira/browse/CALCITE-1742
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.12.0
>Reporter: Maryann Xue
>Assignee: Maryann Xue
> Fix For: 1.13.0
>
>
> {{CalciteSchema}} is designed to have two sets of objects, the explicit and
> the implicit. A explicit object means an object we add through explicit
> {{CalciteSchema#addTable}} (or {{CalciteSchema.addFunction}}, etc) calls,
> while an implicit object means an object we get from the underlying
> {{Schema}} object's getXXX methods.
>
> However, in {{CalciteCatalogReader#getTableFrom}}, after a table is resolved
> through {{CalciteSchema.getTable}} method, it will be added to the
> {{CalciteSchema}} again as an explicit object regardless of whether it is
> originally implicit or explicit. So if it happens to be an implicit table,
> any change about that table later on will be shadowed by the newly added
> explicit object and thus cannot be accessed.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Updated] (CALCITE-1717) Move Avatica into its own git repository
[ https://issues.apache.org/jira/browse/CALCITE-1717?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Julian Hyde updated CALCITE-1717: - Fix Version/s: avatica-1.10.0 > Move Avatica into its own git repository > > > Key: CALCITE-1717 > URL: https://issues.apache.org/jira/browse/CALCITE-1717 > Project: Calcite > Issue Type: Bug >Reporter: Julian Hyde >Assignee: Josh Elser > Fix For: avatica-1.10.0 > > > Move Avatica into its own git repository. -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Updated] (CALCITE-1665) HAVING support in RelToSqlConverter
[
https://issues.apache.org/jira/browse/CALCITE-1665?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-1665:
-
Fix Version/s: 1.12.0
> HAVING support in RelToSqlConverter
> ---
>
> Key: CALCITE-1665
> URL: https://issues.apache.org/jira/browse/CALCITE-1665
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Affects Versions: 1.11.0
>Reporter: David Evans
>Assignee: Zhiqiang He
>Priority: Minor
> Fix For: 1.12.0
>
>
> The example on the main docs page: https://calcite.apache.org/docs/ will
> generate the following SQL when run against a JDBC Schema (specifically, with
> a PostgreSQL target database):
> {code}
> SELECT "deptno", MIN("emps"."empid")
> FROM (SELECT "depts"."deptno", MIN("emps"."empid"), COUNT(*) AS "$f2"
> FROM "hr"."depts"
> INNER JOIN "hr"."emps" ON "depts"."deptno" = "emps"."deptno"
> GROUP BY "depts"."deptno") AS "t"
> WHERE "t"."$f2" > 1
> {code}
> This fails since the "emps" table only exists inside the inner select. It
> should be aliasing that result in the inner select and using the outer select
> as a simple pass-through. This appears to be a general issue when combining
> aggregates with `HAVING`
> For an MCVE:
> In postgres: (create a database named "test1"):
> {code}
> CREATE SCHEMA hr;
> CREATE TABLE hr.depts (deptno SERIAL NOT NULL PRIMARY KEY); CREATE TABLE
> hr.emps (empid SERIAL NOT NULL PRIMARY KEY, deptno INT NOT NULL REFERENCES
> hr.depts (deptno));
> INSERT INTO hr.depts VALUES (1), (2);
> INSERT INTO hr.emps (deptno) VALUES (1), (1), (2);
> {code}
> Java:
> {code}
> import org.apache.calcite.adapter.jdbc.JdbcSchema;
> import org.apache.calcite.jdbc.CalciteConnection;
> import org.apache.calcite.schema.Schema;
> import org.apache.calcite.schema.SchemaPlus;
> import org.apache.commons.dbcp.BasicDataSource;
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.ResultSet;
> import java.sql.Statement;
> import java.util.Properties;
> public class Main {
> public static class Employee {
> public int EMPID;
> public int DEPTNO;
> }
> public static class Department {
> public int DEPTNO;
> }
> public static class HrSchema {
> public final Employee[] EMPS = null;
> public final Department[] DEPTS = null;
> }
> public static void main(String[] argv) throws Exception {
> System.out.println("Setup");
> Class.forName("org.apache.calcite.jdbc.Driver");
> Class.forName("org.postgresql.Driver");
> BasicDataSource dataSource = new BasicDataSource();
> dataSource.setUrl("jdbc:postgresql://localhost:5432/test1");
> dataSource.setUsername("myUsername"); // change as required
> dataSource.setPassword("");
> Connection pgConnection = dataSource.getConnection();
> Statement statement = pgConnection.createStatement();
> ResultSet results = statement.executeQuery("select d.deptno,
> min(e.empid)\n"
> + "from hr.emps as e\n"
> + "join hr.depts as d\n"
> + " on e.deptno = d.deptno\n"
> + "group by d.deptno\n"
> + "having count(*) > 1");
> System.out.println("Direct to postgres results:");
> while(results.next()) {
> System.out.println(results.getInt(1) + " : " + results.getInt(2));
> }
> System.out.println("Done");
> results.close();
> statement.close();
> pgConnection.close();
> System.out.println("Closed");
> Properties info = new Properties();
> info.setProperty("lex", "JAVA");
> Connection calConnection =
> DriverManager.getConnection("jdbc:calcite:", info);
> CalciteConnection calciteConnection =
> calConnection.unwrap(CalciteConnection.class);
> SchemaPlus rootSchema = calciteConnection.getRootSchema();
> Schema schema = JdbcSchema.create(rootSchema, "hr", dataSource, null,
> "hr");
> rootSchema.add("hr", schema);
> statement = calciteConnection.createStatement();
>results = statement.executeQuery(
> "select d.deptno, min(e.empid)\n"
> + "from hr.emps as e\n"
> + "join hr.depts as d\n"
> + " on e.deptno = d.deptno\n"
> + "group by d.deptno\n"
> + "having count(*) > 1");
> System.out.println("Via calcite results:");
> while(results.next()) {
> System.out.println(results.getInt(1) + " : " + results.getInt(2));
> }
> System.out.println("Done");
> results.close();
> statement.close();
> calConnection.c
[jira] [Updated] (CALCITE-1787) thetaSketch Support for Druid Adapter
[
https://issues.apache.org/jira/browse/CALCITE-1787?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-1787:
-
Fix Version/s: (was: 1.12.0)
> thetaSketch Support for Druid Adapter
> -
>
> Key: CALCITE-1787
> URL: https://issues.apache.org/jira/browse/CALCITE-1787
> Project: Calcite
> Issue Type: New Feature
> Components: druid
>Affects Versions: 1.12.0
>Reporter: Zain Humayun
>Assignee: Julian Hyde
>Priority: Minor
>
> Currently, the Druid adapter does not support the
> [thetaSketch|http://druid.io/docs/latest/development/extensions-core/datasketches-aggregators.html]
> aggregate type, which is used to measure the cardinality of a column
> quickly. Many Druid instances support theta sketches, so I think it would be
> a nice feature to have.
> I've been looking at the Druid adapter, and propose we add a new DruidType
> called {{thetaSketch}} and then add logic in the {{getJsonAggregation}}
> method in class {{DruidQuery}} to generate the {{thetaSketch}} aggregate.
> This will require accessing information about the columns (what data type
> they are) so that the thetaSketch aggregate is only produced if the column's
> type is {{thetaSketch}}.
> Also, I've noticed that a {{hyperUnique}} DruidType is currently defined, but
> a {{hyperUnique}} aggregate is never produced. Since both are approximate
> aggregators, I could also couple in the logic for {{hyperUnique}}.
> I'd love to hear your thoughts on my approach, and any suggestions you have
> for this feature.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Closed] (CALCITE-1665) HAVING support in RelToSqlConverter
[
https://issues.apache.org/jira/browse/CALCITE-1665?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde closed CALCITE-1665.
Resolved in release 1.12.0 (2017-03-24).
> HAVING support in RelToSqlConverter
> ---
>
> Key: CALCITE-1665
> URL: https://issues.apache.org/jira/browse/CALCITE-1665
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-adapter
>Affects Versions: 1.11.0
>Reporter: David Evans
>Assignee: Zhiqiang He
>Priority: Minor
> Fix For: 1.12.0
>
>
> The example on the main docs page: https://calcite.apache.org/docs/ will
> generate the following SQL when run against a JDBC Schema (specifically, with
> a PostgreSQL target database):
> {code}
> SELECT "deptno", MIN("emps"."empid")
> FROM (SELECT "depts"."deptno", MIN("emps"."empid"), COUNT(*) AS "$f2"
> FROM "hr"."depts"
> INNER JOIN "hr"."emps" ON "depts"."deptno" = "emps"."deptno"
> GROUP BY "depts"."deptno") AS "t"
> WHERE "t"."$f2" > 1
> {code}
> This fails since the "emps" table only exists inside the inner select. It
> should be aliasing that result in the inner select and using the outer select
> as a simple pass-through. This appears to be a general issue when combining
> aggregates with `HAVING`
> For an MCVE:
> In postgres: (create a database named "test1"):
> {code}
> CREATE SCHEMA hr;
> CREATE TABLE hr.depts (deptno SERIAL NOT NULL PRIMARY KEY); CREATE TABLE
> hr.emps (empid SERIAL NOT NULL PRIMARY KEY, deptno INT NOT NULL REFERENCES
> hr.depts (deptno));
> INSERT INTO hr.depts VALUES (1), (2);
> INSERT INTO hr.emps (deptno) VALUES (1), (1), (2);
> {code}
> Java:
> {code}
> import org.apache.calcite.adapter.jdbc.JdbcSchema;
> import org.apache.calcite.jdbc.CalciteConnection;
> import org.apache.calcite.schema.Schema;
> import org.apache.calcite.schema.SchemaPlus;
> import org.apache.commons.dbcp.BasicDataSource;
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.ResultSet;
> import java.sql.Statement;
> import java.util.Properties;
> public class Main {
> public static class Employee {
> public int EMPID;
> public int DEPTNO;
> }
> public static class Department {
> public int DEPTNO;
> }
> public static class HrSchema {
> public final Employee[] EMPS = null;
> public final Department[] DEPTS = null;
> }
> public static void main(String[] argv) throws Exception {
> System.out.println("Setup");
> Class.forName("org.apache.calcite.jdbc.Driver");
> Class.forName("org.postgresql.Driver");
> BasicDataSource dataSource = new BasicDataSource();
> dataSource.setUrl("jdbc:postgresql://localhost:5432/test1");
> dataSource.setUsername("myUsername"); // change as required
> dataSource.setPassword("");
> Connection pgConnection = dataSource.getConnection();
> Statement statement = pgConnection.createStatement();
> ResultSet results = statement.executeQuery("select d.deptno,
> min(e.empid)\n"
> + "from hr.emps as e\n"
> + "join hr.depts as d\n"
> + " on e.deptno = d.deptno\n"
> + "group by d.deptno\n"
> + "having count(*) > 1");
> System.out.println("Direct to postgres results:");
> while(results.next()) {
> System.out.println(results.getInt(1) + " : " + results.getInt(2));
> }
> System.out.println("Done");
> results.close();
> statement.close();
> pgConnection.close();
> System.out.println("Closed");
> Properties info = new Properties();
> info.setProperty("lex", "JAVA");
> Connection calConnection =
> DriverManager.getConnection("jdbc:calcite:", info);
> CalciteConnection calciteConnection =
> calConnection.unwrap(CalciteConnection.class);
> SchemaPlus rootSchema = calciteConnection.getRootSchema();
> Schema schema = JdbcSchema.create(rootSchema, "hr", dataSource, null,
> "hr");
> rootSchema.add("hr", schema);
> statement = calciteConnection.createStatement();
>results = statement.executeQuery(
> "select d.deptno, min(e.empid)\n"
> + "from hr.emps as e\n"
> + "join hr.depts as d\n"
> + " on e.deptno = d.deptno\n"
> + "group by d.deptno\n"
> + "having count(*) > 1");
> System.out.println("Via calcite results:");
> while(results.next()) {
> System.out.println(results.getInt(1) + " : " + results.getInt(2));
> }
> System.out.println("Done");
> results.close();
> statement.close();
> c
[jira] [Commented] (CALCITE-1800) JDBC adapter fails to SELECT FROM a UNION query
[
https://issues.apache.org/jira/browse/CALCITE-1800?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020643#comment-16020643
]
MinJi Kim commented on CALCITE-1800:
It looks like the problem is whether we should create a new AliasContext. The
plan we get after conversion is as follows. After LogicalUnion, we have an
alias "t1" for the subtree including LogicalUnion and below, with row type
(product_id, net_weight).
{code}
LogicalAggregate(group=[{}], NET_WEIGHT=[SUM($0)])
LogicalProject($f0=[CASE(=($0, 0), $1, 0)])
LogicalUnion(all=[true])
LogicalProject(product_id=[$1], net_weight=[$7])
LogicalTableScan(table=[[foodmart, product]])
LogicalProject(product_id=[$0], net_weight=[0])
LogicalTableScan(table=[[foodmart, sales_fact_1997]])
{code}
Sine Unions are SET_OP operations, in the Result.builder(), we set the flag
needNew to true (selectList is null in this case) when visitng LogicalProject
above the Union, and so we enter the following if-clause. Now, in this case,
the LogicalProject is reducing the row type to a single column, so we now
generate an incorrect row type for aliase "t1". I think basically, when the
alias is unchanging, we shouldn't be creating a new alias.
{code}
} else {
boolean qualified =
!dialect.hasImplicitTableAlias() || aliases.size() > 1;
if (needNew) { // <--- this is where the patch fixes so that we don't
enter here.
newContext =
aliasContext(ImmutableMap.of(neededAlias, rel.getRowType()),
qualified);
} else {
newContext = aliasContext(aliases, qualified);
}
}
{code}
At the minimum, I would change the patch so that it checks that aliases.size()
== 1 and that the aliases are the same (i.e. neededAlias is equal to the only
key is aliases). But, I wonder if we should get rid of it all and revert the
code back to what it used to be. Because I think in this case, we have no
selectList, there shouldn't be any rowtype change.
{code}
} else {
boolean qualified =
!dialect.hasImplicitTableAlias() || aliases.size() > 1;
newContext = aliasContext(aliases, qualified);
}
{code}
In this case, only one test fails
RelToSqlConverterTest.testSelectQueryWithGroupByHaving3(). I am looking into
that right now.
> JDBC adapter fails to SELECT FROM a UNION query
>
>
> Key: CALCITE-1800
> URL: https://issues.apache.org/jira/browse/CALCITE-1800
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.9.0
>Reporter: Viktor Batytskyi
>Assignee: Julian Hyde
> Fix For: 1.13.0
>
> Original Estimate: 2h
> Remaining Estimate: 2h
>
> Relational representation of the following query fails to be converter to sql
> string in Calcite:
> {code}
> select sum(case when "product_id"=0 then "net_weight" else 0 end) as
> net_weight
> from ( select "product_id", "net_weight" from "product"
> union all
> select "product_id", 0 as "net_weight" from "sales_fact_1997" ) t0
> {code}
> The conversion fails with:
> {code}
> java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplementor$Result
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Aggregate)'
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:528)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:96)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:100)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.ok(RelToSqlConverterTest.java:1559)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.testUnionWrappedInASelect(RelToSqlConverterTest.java:654)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
> at
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
> at
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
> at
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
> at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(Bl
[jira] [Comment Edited] (CALCITE-1800) JDBC adapter fails to SELECT FROM a UNION query
[
https://issues.apache.org/jira/browse/CALCITE-1800?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020643#comment-16020643
]
MinJi Kim edited comment on CALCITE-1800 at 5/23/17 4:24 AM:
-
It looks like the problem is whether we should create a new AliasContext. The
plan we get after conversion is as follows. After LogicalUnion, we have an
alias "t1" for the subtree including LogicalUnion and below, with row type
(product_id, net_weight).
{code}
LogicalAggregate(group=[{}], NET_WEIGHT=[SUM($0)])
LogicalProject($f0=[CASE(=($0, 0), $1, 0)])
LogicalUnion(all=[true])
LogicalProject(product_id=[$1], net_weight=[$7])
LogicalTableScan(table=[[foodmart, product]])
LogicalProject(product_id=[$0], net_weight=[0])
LogicalTableScan(table=[[foodmart, sales_fact_1997]])
{code}
Sine Unions are SET_OP operations, in the Result.builder(), we set the flag
needNew to true (selectList is null in this case) when visiting LogicalProject
above the Union, and so we enter the following if-clause. Now, in this case,
the LogicalProject is reducing the row type to a single column, so we now
generate an incorrect row type for alias "t1". Basically, when the alias is
unchanging, we shouldn't be creating a new alias.
{code}
} else {
boolean qualified =
!dialect.hasImplicitTableAlias() || aliases.size() > 1;
if (needNew) { // <--- this is where the patch fixes so that we don't
enter here.
newContext =
aliasContext(ImmutableMap.of(neededAlias, rel.getRowType()),
qualified);
} else {
newContext = aliasContext(aliases, qualified);
}
}
{code}
At the minimum, I would change the patch so that it checks that aliases.size()
== 1 and that the aliases are the same (i.e. neededAlias is equal to the only
key is aliases) -- if there are two aliases say "t1" and "t2" in aliases, and
we are collapsing the two aliases into a single alias "t1", even if "t1" is
contained in aliases, we should actually create a new alias. But, I wonder if
we should get rid of it all and revert the code back to what it used to be.
Because I think in this case, we have no selectList, there shouldn't be any
rowtype change.
{code}
} else {
boolean qualified =
!dialect.hasImplicitTableAlias() || aliases.size() > 1;
newContext = aliasContext(aliases, qualified);
}
{code}
In this case, only one test fails
RelToSqlConverterTest.testSelectQueryWithGroupByHaving3(). I am looking into
that right now.
was (Author: minjikim):
It looks like the problem is whether we should create a new AliasContext. The
plan we get after conversion is as follows. After LogicalUnion, we have an
alias "t1" for the subtree including LogicalUnion and below, with row type
(product_id, net_weight).
{code}
LogicalAggregate(group=[{}], NET_WEIGHT=[SUM($0)])
LogicalProject($f0=[CASE(=($0, 0), $1, 0)])
LogicalUnion(all=[true])
LogicalProject(product_id=[$1], net_weight=[$7])
LogicalTableScan(table=[[foodmart, product]])
LogicalProject(product_id=[$0], net_weight=[0])
LogicalTableScan(table=[[foodmart, sales_fact_1997]])
{code}
Sine Unions are SET_OP operations, in the Result.builder(), we set the flag
needNew to true (selectList is null in this case) when visitng LogicalProject
above the Union, and so we enter the following if-clause. Now, in this case,
the LogicalProject is reducing the row type to a single column, so we now
generate an incorrect row type for aliase "t1". I think basically, when the
alias is unchanging, we shouldn't be creating a new alias.
{code}
} else {
boolean qualified =
!dialect.hasImplicitTableAlias() || aliases.size() > 1;
if (needNew) { // <--- this is where the patch fixes so that we don't
enter here.
newContext =
aliasContext(ImmutableMap.of(neededAlias, rel.getRowType()),
qualified);
} else {
newContext = aliasContext(aliases, qualified);
}
}
{code}
At the minimum, I would change the patch so that it checks that aliases.size()
== 1 and that the aliases are the same (i.e. neededAlias is equal to the only
key is aliases). But, I wonder if we should get rid of it all and revert the
code back to what it used to be. Because I think in this case, we have no
selectList, there shouldn't be any rowtype change.
{code}
} else {
boolean qualified =
!dialect.hasImplicitTableAlias() || aliases.size() > 1;
newContext = aliasContext(aliases, qualified);
}
{code}
In this case, only one test fails
RelToSqlConverterTest.testSelectQueryWithGroupByHaving3(). I am looking into
that right now.
> JDBC adapter fails to SELECT FROM a UNION query
> ---
[jira] [Commented] (CALCITE-1800) JDBC adapter fails to SELECT FROM a UNION query
[
https://issues.apache.org/jira/browse/CALCITE-1800?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020677#comment-16020677
]
MinJi Kim commented on CALCITE-1800:
It looks like my previous suggestion is not going to work. In
RelToSqlConverterTest.testSelectQueryWithGroupByHaving3(), we have the
following query plan:
{code}
LogicalProject(product_id=[$0], EXPR$1=[$1])
LogicalFilter(condition=[>($0, 100)])
LogicalProject(product_id=[$0], EXPR$1=[$1])
LogicalFilter(condition=[>($2, 1)])
LogicalAggregate(group=[{0}], EXPR$1=[MIN($1)], agg#1=[COUNT()])
LogicalProject(product_id=[$1], store_id=[$19])
LogicalJoin(condition=[=($1, $15)], joinType=[inner])
LogicalTableScan(table=[[foodmart, product]])
LogicalTableScan(table=[[foodmart, sales_fact_1997]])
{code}
The subtree below "LogicalFilter(condition=[>($0, 100)])" generates the
following sql query, which I believe is correct. Now, with the additional
filter, "LogicalFilter(condition=[>($0, 100)])", we need to make sure that a
new AliasContext is generated.
{code}
SELECT `product`.`product_id`, MIN(`sales_fact_1997`.`store_id`)
FROM `foodmart`.`product`
INNER JOIN `foodmart`.`sales_fact_1997` ON `product`.`product_id` =
`sales_fact_1997`.`product_id`
GROUP BY `product`.`product_id`
HAVING COUNT(*) > 1
{code}
In this case, selectList is null since this is a filter, and we fall through
and never generate a new AliasContext if we use my previously proposed fix.
So, actually we do need the if-else-clause as before. I do think that we need
to be a bit more specific as to when we enter the if-else-clause. We not only
need check that we used the new alias (i.e. needNew is true and neededAlias !=
null), but also check that our aliases is of size 1. Since we could have
aliases = [, ], with neededAlias = t1 (although I
believe this should not happen in our code currently, but I think if our
aliases context has two elements, "t1" and "t2", and we want to re-name the
entire subquery as "t1", that should be a new AliasContext with a single
element).
{code}
} else {
boolean qualified =
!dialect.hasImplicitTableAlias() || aliases.size() > 1;
// basically, we did a subSelect() since needNew is set and neededAlias
is not null
// now, we need to make sure that we need to update the alias context.
// if our aliases map has a single element: ,
// then we don't need to rewrite the alias but otherwise, it should be
updated.
if (needNew // if we have renamed
&& neededAlias != null // can't rename to empty
&& aliases.size() != 1 // Even if "t1" is in aliases, if size is >
1 then we should rename.
&& !aliases.containsKey(neededAlias)) {
newContext = aliasContext(ImmutableMap.of(neededAlias,
rel.getRowType()), qualified);
} else {
newContext = aliasContext(aliases, qualified);
}
}
return new Builder(rel, clauseList, select, newContext, aliases); //
<--- I don't recall why I added the needNew check here, and I don't think it is
necessary.
}
{code}
[~julianhyde] and [~victor.ba...@gmail.com] does this sound reasonable? With
above change, all tests seem to pass too.
> JDBC adapter fails to SELECT FROM a UNION query
>
>
> Key: CALCITE-1800
> URL: https://issues.apache.org/jira/browse/CALCITE-1800
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.9.0
>Reporter: Viktor Batytskyi
>Assignee: Julian Hyde
> Fix For: 1.13.0
>
> Original Estimate: 2h
> Remaining Estimate: 2h
>
> Relational representation of the following query fails to be converter to sql
> string in Calcite:
> {code}
> select sum(case when "product_id"=0 then "net_weight" else 0 end) as
> net_weight
> from ( select "product_id", "net_weight" from "product"
> union all
> select "product_id", 0 as "net_weight" from "sales_fact_1997" ) t0
> {code}
> The conversion fails with:
> {code}
> java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplementor$Result
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Aggregate)'
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:528)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:96)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:100)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.ok(RelToSqlConverterTest.java:1559)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.testUnionWrappedInASelect(RelToSqlConverterTest.java:654)
> a
[jira] [Comment Edited] (CALCITE-1800) JDBC adapter fails to SELECT FROM a UNION query
[
https://issues.apache.org/jira/browse/CALCITE-1800?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020677#comment-16020677
]
MinJi Kim edited comment on CALCITE-1800 at 5/23/17 5:34 AM:
-
It looks like my previous suggestion is not going to work. In
RelToSqlConverterTest.testSelectQueryWithGroupByHaving3(), we have the
following query plan:
{code}
LogicalProject(product_id=[$0], EXPR$1=[$1])
LogicalFilter(condition=[>($0, 100)])
LogicalProject(product_id=[$0], EXPR$1=[$1])
LogicalFilter(condition=[>($2, 1)])
LogicalAggregate(group=[{0}], EXPR$1=[MIN($1)], agg#1=[COUNT()])
LogicalProject(product_id=[$1], store_id=[$19])
LogicalJoin(condition=[=($1, $15)], joinType=[inner])
LogicalTableScan(table=[[foodmart, product]])
LogicalTableScan(table=[[foodmart, sales_fact_1997]])
{code}
The subtree below "LogicalFilter(condition=[>($0, 100)])" generates the
following sql query, which I believe is correct. Now, with the additional
filter, "LogicalFilter(condition=[>($0, 100)])", we need to make sure that a
new AliasContext is generated.
{code}
SELECT `product`.`product_id`, MIN(`sales_fact_1997`.`store_id`)
FROM `foodmart`.`product`
INNER JOIN `foodmart`.`sales_fact_1997` ON `product`.`product_id` =
`sales_fact_1997`.`product_id`
GROUP BY `product`.`product_id`
HAVING COUNT(*) > 1
{code}
In this case, selectList is null since this is a filter, and we fall through
and never generate a new AliasContext if we use my previously proposed fix.
So, actually we do need the if-else-clause as before. I do think that we need
to be a bit more specific as to when we enter the if-else-clause. We not only
need check that we used the new alias (i.e. needNew is true and neededAlias !=
null), but also check that our aliases is of size 1. Since we could have
aliases = [, ], with neededAlias = t1 (although I
believe this should not happen in our code currently, but I think if our
aliases context has two elements, "t1" and "t2", and we want to re-name the
entire subquery as "t1", that should be a new AliasContext with a single
element).
{code}
} else {
boolean qualified =
!dialect.hasImplicitTableAlias() || aliases.size() > 1;
// basically, we did a subSelect() since needNew is set and neededAlias
is not null
// now, we need to make sure that we need to update the alias context.
// if our aliases map has a single element: ,
// then we don't need to rewrite the alias but otherwise, it should be
updated.
if (needNew
&& neededAlias != null
&& (aliases.size() != 1 || !aliases.containsKey(neededAlias))) {
newContext = aliasContext(ImmutableMap.of(neededAlias,
rel.getRowType()), qualified);
} else {
newContext = aliasContext(aliases, qualified);
}
}
return new Builder(rel, clauseList, select, newContext, aliases);
{code}
[~julianhyde] and [~victor.ba...@gmail.com] does this sound reasonable? With
above change, all tests seem to pass too.
was (Author: minjikim):
It looks like my previous suggestion is not going to work. In
RelToSqlConverterTest.testSelectQueryWithGroupByHaving3(), we have the
following query plan:
{code}
LogicalProject(product_id=[$0], EXPR$1=[$1])
LogicalFilter(condition=[>($0, 100)])
LogicalProject(product_id=[$0], EXPR$1=[$1])
LogicalFilter(condition=[>($2, 1)])
LogicalAggregate(group=[{0}], EXPR$1=[MIN($1)], agg#1=[COUNT()])
LogicalProject(product_id=[$1], store_id=[$19])
LogicalJoin(condition=[=($1, $15)], joinType=[inner])
LogicalTableScan(table=[[foodmart, product]])
LogicalTableScan(table=[[foodmart, sales_fact_1997]])
{code}
The subtree below "LogicalFilter(condition=[>($0, 100)])" generates the
following sql query, which I believe is correct. Now, with the additional
filter, "LogicalFilter(condition=[>($0, 100)])", we need to make sure that a
new AliasContext is generated.
{code}
SELECT `product`.`product_id`, MIN(`sales_fact_1997`.`store_id`)
FROM `foodmart`.`product`
INNER JOIN `foodmart`.`sales_fact_1997` ON `product`.`product_id` =
`sales_fact_1997`.`product_id`
GROUP BY `product`.`product_id`
HAVING COUNT(*) > 1
{code}
In this case, selectList is null since this is a filter, and we fall through
and never generate a new AliasContext if we use my previously proposed fix.
So, actually we do need the if-else-clause as before. I do think that we need
to be a bit more specific as to when we enter the if-else-clause. We not only
need check that we used the new alias (i.e. needNew is true and neededAlias !=
null), but also check that our aliases is of size 1. Since we could have
aliases = [, ], with neededAlias = t1 (although I
believe this should not happen in our code currently,
[jira] [Commented] (CALCITE-1800) JDBC adapter fails to SELECT FROM a UNION query
[
https://issues.apache.org/jira/browse/CALCITE-1800?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020695#comment-16020695
]
Julian Hyde commented on CALCITE-1800:
--
[~minjikim], I was thinking on similar lines (but I didn't have the stamina to
turn it into a real fix). Sounds good, and thank you.
[~victor.ba...@gmail.com], What do you think?
> JDBC adapter fails to SELECT FROM a UNION query
>
>
> Key: CALCITE-1800
> URL: https://issues.apache.org/jira/browse/CALCITE-1800
> Project: Calcite
> Issue Type: Bug
> Components: core
>Affects Versions: 1.9.0
>Reporter: Viktor Batytskyi
>Assignee: Julian Hyde
> Fix For: 1.13.0
>
> Original Estimate: 2h
> Remaining Estimate: 2h
>
> Relational representation of the following query fails to be converter to sql
> string in Calcite:
> {code}
> select sum(case when "product_id"=0 then "net_weight" else 0 end) as
> net_weight
> from ( select "product_id", "net_weight" from "product"
> union all
> select "product_id", 0 as "net_weight" from "sales_fact_1997" ) t0
> {code}
> The conversion fails with:
> {code}
> java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplementor$Result
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.calcite.rel.core.Aggregate)'
> at org.apache.calcite.util.ReflectUtil$2.invoke(ReflectUtil.java:528)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.dispatch(RelToSqlConverter.java:96)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visitChild(RelToSqlConverter.java:100)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest$Sql.ok(RelToSqlConverterTest.java:1559)
> at
> org.apache.calcite.rel.rel2sql.RelToSqlConverterTest.testUnionWrappedInASelect(RelToSqlConverterTest.java:654)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
> at
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
> at
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
> at
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
> at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
> at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
> at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
> at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
> at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
> at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
> at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
> at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
> at
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:117)
> at
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42)
> at
> com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:262)
> at
> com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:84)
> 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 com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
> Caused by: java.lang.reflect.InvocationTargetException
> 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)
> ... 31 more
> Caused by: java.lang.RuntimeException: While invoking method 'public
> org.apache.calcite.rel.rel2sql.SqlImplementor$Result
> org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(org.apache.
[jira] [Commented] (CALCITE-1690) Calcite timestamp literals cannot express precision above millisecond, TIMESTAMP(3)
[
https://issues.apache.org/jira/browse/CALCITE-1690?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020701#comment-16020701
]
ASF GitHub Bot commented on CALCITE-1690:
-
Github user asfgit closed the pull request at:
https://github.com/apache/calcite-avatica/pull/9
> Calcite timestamp literals cannot express precision above millisecond,
> TIMESTAMP(3)
> ---
>
> Key: CALCITE-1690
> URL: https://issues.apache.org/jira/browse/CALCITE-1690
> Project: Calcite
> Issue Type: Bug
>Reporter: Remus Rusanu
>Assignee: Julian Hyde
> Fix For: avatica-1.10.0, 1.13.0
>
>
> {{RexBuilder.makeTimestampLiteral}} accepts the TS as a Java Calendar
> instance. Calendar type has only ms precision, thus types like
> {{TIMESTAMP(9)}} cannot be represented.
> This results in incorrect results in Hive due to constant reduction:
> {noformat}
> hive> explain select c17 from testjdbcdriverdatatypetbl where c17='2012-04-22
> 09:00:00.123456789';
> OK
> Plan optimized by CBO.
> Stage-0
> Fetch Operator
> limit:-1
> Select Operator [SEL_2]
> Output:["_col0"]
> Filter Operator [FIL_4]
> predicate:(c17 = 2012-04-22 09:00:00.123)
> TableScan [TS_0]
> Output:["c17"]
> hive> select c17 from testjdbcdriverdatatypetbl where c17='2012-04-22
> 09:00:00.123456789';
> OK
> Time taken: 0.687 seconds
> hive> set hive.cbo.enable=false;
> hive> explain select c17 from testjdbcdriverdatatypetbl where c17='2012-04-22
> 09:00:00.123456789';
> OK
> Stage-0
> Fetch Operator
> limit:-1
> Select Operator [SEL_2]
> Output:["_col0"]
> Filter Operator [FIL_4]
> predicate:(c17 = '2012-04-22 09:00:00.123456789')
> TableScan [TS_0]
> Output:["c17"]
> hive> select c17 from testjdbcdriverdatatypetbl where c17='2012-04-22
> 09:00:00.123456789';
> OK
> 2012-04-22 09:00:00.123
> {noformat}
> Note how with CBO enabled the qualified row is missed. The plan shows that
> the constant was truncated to ms.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
[jira] [Commented] (CALCITE-1690) Calcite timestamp literals cannot express precision above millisecond, TIMESTAMP(3)
[
https://issues.apache.org/jira/browse/CALCITE-1690?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16020703#comment-16020703
]
Julian Hyde commented on CALCITE-1690:
--
Fixed in Avatica in
http://git-wip-us.apache.org/repos/asf/calcite-avatica/commit/3c40e0db; Calcite
work will follow after avatica-1.10.
> Calcite timestamp literals cannot express precision above millisecond,
> TIMESTAMP(3)
> ---
>
> Key: CALCITE-1690
> URL: https://issues.apache.org/jira/browse/CALCITE-1690
> Project: Calcite
> Issue Type: Bug
>Reporter: Remus Rusanu
>Assignee: Julian Hyde
> Fix For: avatica-1.10.0, 1.13.0
>
>
> {{RexBuilder.makeTimestampLiteral}} accepts the TS as a Java Calendar
> instance. Calendar type has only ms precision, thus types like
> {{TIMESTAMP(9)}} cannot be represented.
> This results in incorrect results in Hive due to constant reduction:
> {noformat}
> hive> explain select c17 from testjdbcdriverdatatypetbl where c17='2012-04-22
> 09:00:00.123456789';
> OK
> Plan optimized by CBO.
> Stage-0
> Fetch Operator
> limit:-1
> Select Operator [SEL_2]
> Output:["_col0"]
> Filter Operator [FIL_4]
> predicate:(c17 = 2012-04-22 09:00:00.123)
> TableScan [TS_0]
> Output:["c17"]
> hive> select c17 from testjdbcdriverdatatypetbl where c17='2012-04-22
> 09:00:00.123456789';
> OK
> Time taken: 0.687 seconds
> hive> set hive.cbo.enable=false;
> hive> explain select c17 from testjdbcdriverdatatypetbl where c17='2012-04-22
> 09:00:00.123456789';
> OK
> Stage-0
> Fetch Operator
> limit:-1
> Select Operator [SEL_2]
> Output:["_col0"]
> Filter Operator [FIL_4]
> predicate:(c17 = '2012-04-22 09:00:00.123456789')
> TableScan [TS_0]
> Output:["c17"]
> hive> select c17 from testjdbcdriverdatatypetbl where c17='2012-04-22
> 09:00:00.123456789';
> OK
> 2012-04-22 09:00:00.123
> {noformat}
> Note how with CBO enabled the qualified row is missed. The plan shows that
> the constant was truncated to ms.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
