[jira] [Commented] (CALCITE-1840) Date floor is broken when used with reflective schema
[
https://issues.apache.org/jira/browse/CALCITE-1840?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16049735#comment-16049735
]
Chris Baynes commented on CALCITE-1840:
---
This is with the latest version from master
> Date floor is broken when used with reflective schema
> -
>
> Key: CALCITE-1840
> URL: https://issues.apache.org/jira/browse/CALCITE-1840
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Chris Baynes
>Assignee: Julian Hyde
>
> Using the everyTypes reflective schema the following query:
> {code:java}
> select floor("sqlDate" to month) from "s"."everyTypes"
> {code}
> fails with:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Long cannot be cast to
> java.sql.Date
> at
> org.apache.calcite.avatica.util.AbstractCursor$DateAccessor.getDate(AbstractCursor.java:1031)
> at
> org.apache.calcite.avatica.util.AbstractCursor$DateAccessor.getLong(AbstractCursor.java:1052)
> at
> org.apache.calcite.avatica.util.AbstractCursor$AccessorImpl.getInt(AbstractCursor.java:305)
> at
> org.apache.calcite.avatica.util.AbstractCursor$DateAccessor.getString(AbstractCursor.java:1044)
> at
> org.apache.calcite.avatica.AvaticaResultSet.getString(AvaticaResultSet.java:257)
> at
> org.apache.calcite.test.CalciteAssert$ResultSetFormatter.rowToString(CalciteAssert.java:1720)
> at
> org.apache.calcite.test.CalciteAssert$ResultSetFormatter.toStringList(CalciteAssert.java:1738)
> at
> org.apache.calcite.test.CalciteAssert.toStringList(CalciteAssert.java:611)
> at org.apache.calcite.test.CalciteAssert$9.apply(CalciteAssert.java:383)
> at org.apache.calcite.test.CalciteAssert$9.apply(CalciteAssert.java:375)
> at
> org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:533)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1266)
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Created] (CALCITE-1843) Convert a RelOptCost to a totally-ordered value
JD Zheng created CALCITE-1843:
-
Summary: Convert a RelOptCost to a totally-ordered value
Key: CALCITE-1843
URL: https://issues.apache.org/jira/browse/CALCITE-1843
Project: Calcite
Issue Type: Improvement
Components: core
Reporter: JD Zheng
Assignee: Julian Hyde
Currently, we have dead code in VolcanoCost.java
{code:java}
public boolean isLe(RelOptCost other) {
VolcanoCost that = (VolcanoCost) other;
if (true) {
return this == that
|| this.rowCount <= that.rowCount;
}
return (this == that)
|| ((this.rowCount <= that.rowCount)
&& (this.cpu <= that.cpu)
&& (this.io <= that.io));
}
public boolean isLt(RelOptCost other) {
if (true) {
VolcanoCost that = (VolcanoCost) other;
return this.rowCount < that.rowCount;
}
return isLe(other) && !equals(other);
}
{code}
The reason for the “if (false)” is that we found that costs don’t work well if
they form only a partial order, not a total order. If you have two RelNodes R1
and R2 in an equivalent set, and they have costs C1 and C2, and neither C1 <=
C2 nor C2 <= C1 is true, which is the Volcano planner to pick? It will tend to
pick the one that it saw first, and that is bad news because it is arbitrary
and non-deterministic.
So, we should probably find a way to convert a RelOptCost to a totally-ordered
value, such as by applying weights to cpu, io and memory cost and returning a
double.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Created] (CALCITE-1842) Wrong order of inputs for makeCost() call in Sort.computeSelfCost()
JD Zheng created CALCITE-1842:
-
Summary: Wrong order of inputs for makeCost() call in
Sort.computeSelfCost()
Key: CALCITE-1842
URL: https://issues.apache.org/jira/browse/CALCITE-1842
Project: Calcite
Issue Type: Bug
Components: core
Reporter: JD Zheng
Assignee: Julian Hyde
Original code in Sort.java
{code:java}
@Override public RelOptCost computeSelfCost(RelOptPlanner planner,
RelMetadataQuery mq) {
// Higher cost if rows are wider discourages pushing a project through a
// sort.
double rowCount = mq.getRowCount(this);
double bytesPerRow = getRowType().getFieldCount() * 4;
return planner.getCostFactory().makeCost(
Util.nLogN(rowCount) * bytesPerRow, rowCount, 0);
{code}
The last line should be
{code:java}
return planner.getCostFactory().makeCost(
rowCount/*rowCount*/, Util.nLogN(rowCount) * bytesPerRow/*cpu*/,
0/*io*/);
{code}
The wrong order will make the planner choose the wrong physical plan. For
example, if the druid query has a limit of 10 with 10+ dimensions, the
optimizer will choose not push the "limit" down to druid instead choose
scanning entire data source in druid.
The fix is very easy, the gain is huge as the performance of the wrong plan is
really bad. Hope it will be picked up by the next release.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Commented] (CALCITE-1838) Support hive's udtf grammar.
[ https://issues.apache.org/jira/browse/CALCITE-1838?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16049499#comment-16049499 ] Julian Hyde commented on CALCITE-1838: -- Since this issue is a duplicate, let's continue the discussion at CALCITE-1581. You should also check out the CROSS APPLY syntax, added in 1.11. https://issues.apache.org/jira/browse/CALCITE-1472 > Support hive's udtf grammar. > > > Key: CALCITE-1838 > URL: https://issues.apache.org/jira/browse/CALCITE-1838 > Project: Calcite > Issue Type: New Feature > Components: core >Affects Versions: 1.12.0 >Reporter: Sihua Zhou >Assignee: Julian Hyde >Priority: Blocker > > Now Calcite doesn't support the hive form of UDTF, eg: > _select udtf(arg1, arg2) as (seg1, seg2, seg3) from table_ > Instead, it provide a table function. But we found that hive's udtf form is > more easier for users to understand. > We are building a stream sql framework with calcite, which allow user to > process data stream by write sql. And most users are migrated from using hive > to process offline data. So, use hive's udtf is more friendly to them. > Can you add the feature to support hive udtf in calcite? We really need it. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1840) Date floor is broken when used with reflective schema
[
https://issues.apache.org/jira/browse/CALCITE-1840?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16049476#comment-16049476
]
Julian Hyde commented on CALCITE-1840:
--
Which version? We've fixed similar issues recently.
> Date floor is broken when used with reflective schema
> -
>
> Key: CALCITE-1840
> URL: https://issues.apache.org/jira/browse/CALCITE-1840
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Chris Baynes
>Assignee: Julian Hyde
>
> Using the everyTypes reflective schema the following query:
> {code:java}
> select floor("sqlDate" to month) from "s"."everyTypes"
> {code}
> fails with:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Long cannot be cast to
> java.sql.Date
> at
> org.apache.calcite.avatica.util.AbstractCursor$DateAccessor.getDate(AbstractCursor.java:1031)
> at
> org.apache.calcite.avatica.util.AbstractCursor$DateAccessor.getLong(AbstractCursor.java:1052)
> at
> org.apache.calcite.avatica.util.AbstractCursor$AccessorImpl.getInt(AbstractCursor.java:305)
> at
> org.apache.calcite.avatica.util.AbstractCursor$DateAccessor.getString(AbstractCursor.java:1044)
> at
> org.apache.calcite.avatica.AvaticaResultSet.getString(AvaticaResultSet.java:257)
> at
> org.apache.calcite.test.CalciteAssert$ResultSetFormatter.rowToString(CalciteAssert.java:1720)
> at
> org.apache.calcite.test.CalciteAssert$ResultSetFormatter.toStringList(CalciteAssert.java:1738)
> at
> org.apache.calcite.test.CalciteAssert.toStringList(CalciteAssert.java:611)
> at org.apache.calcite.test.CalciteAssert$9.apply(CalciteAssert.java:383)
> at org.apache.calcite.test.CalciteAssert$9.apply(CalciteAssert.java:375)
> at
> org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:533)
> at
> org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1266)
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Resolved] (CALCITE-1839) There is any way to rewrite and forceful remove Join,Filter etc.. from RelNode or SqlNode?
[ https://issues.apache.org/jira/browse/CALCITE-1839?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Julian Hyde resolved CALCITE-1839. -- Resolution: Invalid Invalid. This is a question, not a bug. Ask on the dev list (or stack overflow if you prefer). > There is any way to rewrite and forceful remove Join,Filter etc.. from > RelNode or SqlNode? > -- > > Key: CALCITE-1839 > URL: https://issues.apache.org/jira/browse/CALCITE-1839 > Project: Calcite > Issue Type: Improvement >Reporter: Sanjai Verma >Assignee: Julian Hyde >Priority: Minor > > I have a sql Query : > select a.addr, b.src from a inner join b on a.cust_id=b.cust_id where > b.cust_id=1 > I convert the query to SqlNode than optimize RelNode: > LogicalProject(addr=[$0], src=[$24]) > LogicalJoin(condition=[=($17, $47)], joinType=[inner]) > LogicalFilter(condition=[=($17, 1)]) > JdbcTableScan(table=[[, a]]) > LogicalFilter(condition=[=($26, 1)]) > JdbcTableScan(table=[[, b]]) > It is ok.Calcite provide me a optimized query... > but i have another table c with fields addr,src as in table a and b. > I want to rewrite the query: > select c.addr,c.src from c where c.cust_id=1 > Is possible ? -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1841) Create handlers for Jdbc dialect specific generated sql
[ https://issues.apache.org/jira/browse/CALCITE-1841?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16049364#comment-16049364 ] Jess Balint commented on CALCITE-1841: -- Why not do this before unparsing by transforming the SqlNode tree? > Create handlers for Jdbc dialect specific generated sql > --- > > Key: CALCITE-1841 > URL: https://issues.apache.org/jira/browse/CALCITE-1841 > Project: Calcite > Issue Type: Improvement > Components: jdbc-adapter >Reporter: Chris Baynes >Assignee: Julian Hyde > > Currently the only way to generate different sql for dialects is to switch on > the dialect in the unparse method. This is used quite heavily in e.g. > SqlFloorFunction, but there are also switches in: > * SUBSTRING() > * SqlDateLiteral quoting > * SqlTimestampLiteral quoting > * Dialects using different interval literals (e.g. Hsqldb uses & MM > rather than YEAR & MONTH) > * limit/offset construction > * mysql isnull function > * type differences (*) > It would be great to have dialect specific handlers to deal with these, > making testing & addition of new handlers (new dialects, or new overrides for > a given function) much easier in the future. > One suggested path to approach this: > https://issues.apache.org/jira/browse/CALCITE-1798?focusedCommentId=16031609=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-16031609 > (*) There is a comment in SqlImplementor that reads "MySQL doesn't have a > VARCHAR type, only CHAR.". Not sure if this was for a very old version of > mysql, but it's certainly not true anymore. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Created] (CALCITE-1841) Create handlers for Jdbc dialect specific generated sql
Chris Baynes created CALCITE-1841: - Summary: Create handlers for Jdbc dialect specific generated sql Key: CALCITE-1841 URL: https://issues.apache.org/jira/browse/CALCITE-1841 Project: Calcite Issue Type: Improvement Components: jdbc-adapter Reporter: Chris Baynes Assignee: Julian Hyde Currently the only way to generate different sql for dialects is to switch on the dialect in the unparse method. This is used quite heavily in e.g. SqlFloorFunction, but there are also switches in: * SUBSTRING() * SqlDateLiteral quoting * SqlTimestampLiteral quoting * Dialects using different interval literals (e.g. Hsqldb uses & MM rather than YEAR & MONTH) * limit/offset construction * mysql isnull function * type differences (*) It would be great to have dialect specific handlers to deal with these, making testing & addition of new handlers (new dialects, or new overrides for a given function) much easier in the future. One suggested path to approach this: https://issues.apache.org/jira/browse/CALCITE-1798?focusedCommentId=16031609=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-16031609 (*) There is a comment in SqlImplementor that reads "MySQL doesn't have a VARCHAR type, only CHAR.". Not sure if this was for a very old version of mysql, but it's certainly not true anymore. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Updated] (CALCITE-1839) There is any way to rewrite and forceful remove Join,Filter etc.. from RelNode or SqlNode?
[ https://issues.apache.org/jira/browse/CALCITE-1839?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Sanjai Verma updated CALCITE-1839: -- Description: I have a sql Query : select a.addr, b.src from a inner join b on a.cust_id=b.cust_id where b.cust_id=1 I convert the query to SqlNode than optimize RelNode: LogicalProject(addr=[$0], src=[$24]) LogicalJoin(condition=[=($17, $47)], joinType=[inner]) LogicalFilter(condition=[=($17, 1)]) JdbcTableScan(table=[[, a]]) LogicalFilter(condition=[=($26, 1)]) JdbcTableScan(table=[[, b]]) It is ok.Calcite provide me a optimized query... but i have another table c with fields addr,src as in table a and b. I want to rewrite the query: select c.addr,c.src from c where c.cust_id=1 Is possible ? > There is any way to rewrite and forceful remove Join,Filter etc.. from > RelNode or SqlNode? > -- > > Key: CALCITE-1839 > URL: https://issues.apache.org/jira/browse/CALCITE-1839 > Project: Calcite > Issue Type: Improvement >Reporter: Sanjai Verma >Assignee: Julian Hyde >Priority: Minor > > I have a sql Query : > select a.addr, b.src from a inner join b on a.cust_id=b.cust_id where > b.cust_id=1 > I convert the query to SqlNode than optimize RelNode: > LogicalProject(addr=[$0], src=[$24]) > LogicalJoin(condition=[=($17, $47)], joinType=[inner]) > LogicalFilter(condition=[=($17, 1)]) > JdbcTableScan(table=[[, a]]) > LogicalFilter(condition=[=($26, 1)]) > JdbcTableScan(table=[[, b]]) > It is ok.Calcite provide me a optimized query... > but i have another table c with fields addr,src as in table a and b. > I want to rewrite the query: > select c.addr,c.src from c where c.cust_id=1 > Is possible ? -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (CALCITE-1840) Date floor is broken when used with reflective schema
[
https://issues.apache.org/jira/browse/CALCITE-1840?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16049134#comment-16049134
]
Chris Baynes commented on CALCITE-1840:
---
It's instructive to see how a more complex query fails (floor in group by, and
another field in the select):
{code}
select floor("sqlDate" to month), min("sqlTimestamp")
from "s"."everyTypes"
group by floor("sqlDate" to month)
{code}
now fails with:
{code}
Caused by: java.lang.ClassCastException: java.lang.Long cannot be cast to
java.sql.Date
at Baz$2.apply(Unknown Source)
at Baz$2.apply(Unknown Source)
at
org.apache.calcite.linq4j.EnumerableDefaults.groupBy_(EnumerableDefaults.java:828)
at
org.apache.calcite.linq4j.EnumerableDefaults.groupBy(EnumerableDefaults.java:761)
at
org.apache.calcite.linq4j.DefaultEnumerable.groupBy(DefaultEnumerable.java:302)
at Baz.bind(Unknown Source)
at
org.apache.calcite.jdbc.CalcitePrepare$CalciteSignature.enumerable(CalcitePrepare.java:335)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:294)
at
org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:559)
at
org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:550)
at
org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:204)
at
org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:67)
at
org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:44)
at
org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:630)
at
org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:607)
at
org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:638)
at
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:149)
{code}
The interesting part of the generated code is here:
{code:java}
public Object current() {
final org.apache.calcite.test.ReflectiveSchemaTest.EveryType current =
(org.apache.calcite.test.ReflectiveSchemaTest.EveryType)
inputEnumerator.current();
final java.sql.Date inp16_ = current.sqlDate;
return new Object[] {
inp16_ == null ? (Long) null :
Long.valueOf(org.apache.calcite.avatica.util.DateTimeUtils.unixDateFloor(org.apache.calcite.avatica.util.TimeUnitRange.MONTH,
org.apache.calcite.runtime.SqlFunctions.floor(org.apache.calcite.runtime.SqlFunctions.toIntOptional(inp16_).intValue(),
$L4J$C$Integer_valueOf_8640_intValue_))),
current.sqlTimestamp};
}
static final int $L4J$C$Integer_valueOf_8640_intValue_ =
Integer.valueOf(8640).intValue();
...
};
return child.groupBy(new org.apache.calcite.linq4j.function.Function1() {
public java.sql.Date apply(Object[] a0) {
return (java.sql.Date) a0[0];
}
public Object apply(Object a0) {
return apply(
(Object[]) a0);
}
}
{code}
The first problem is the casting of a Long to Date (in apply), but once that's
fixed there's another problem:
SqlFunctions.floor - inp16_ (the Date) is cast to an int (which already
converts from unix timestamp millis to days since epoch), and when floored with
the large 8640 (millis per day) will be zero.
I think the floor is just unnecessary here.
> Date floor is broken when used with reflective schema
> -
>
> Key: CALCITE-1840
> URL: https://issues.apache.org/jira/browse/CALCITE-1840
> Project: Calcite
> Issue Type: Bug
> Components: core
>Reporter: Chris Baynes
>Assignee: Julian Hyde
>
> Using the everyTypes reflective schema the following query:
> {code:java}
> select floor("sqlDate" to month) from "s"."everyTypes"
> {code}
> fails with:
> {code}
> Caused by: java.lang.ClassCastException: java.lang.Long cannot be cast to
> java.sql.Date
> at
> org.apache.calcite.avatica.util.AbstractCursor$DateAccessor.getDate(AbstractCursor.java:1031)
> at
> org.apache.calcite.avatica.util.AbstractCursor$DateAccessor.getLong(AbstractCursor.java:1052)
> at
> org.apache.calcite.avatica.util.AbstractCursor$AccessorImpl.getInt(AbstractCursor.java:305)
> at
> org.apache.calcite.avatica.util.AbstractCursor$DateAccessor.getString(AbstractCursor.java:1044)
> at
> org.apache.calcite.avatica.AvaticaResultSet.getString(AvaticaResultSet.java:257)
> at
> org.apache.calcite.test.CalciteAssert$ResultSetFormatter.rowToString(CalciteAssert.java:1720)
> at
> org.apache.calcite.test.CalciteAssert$ResultSetFormatter.toStringList(CalciteAssert.java:1738)
> at
> org.apache.calcite.test.CalciteAssert.toStringList(CalciteAssert.java:611)
>
[jira] [Created] (CALCITE-1840) Date floor is broken when used with reflective schema
Chris Baynes created CALCITE-1840:
-
Summary: Date floor is broken when used with reflective schema
Key: CALCITE-1840
URL: https://issues.apache.org/jira/browse/CALCITE-1840
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Chris Baynes
Assignee: Julian Hyde
Using the everyTypes reflective schema the following query:
{code:java}
select floor("sqlDate" to month) from "s"."everyTypes"
{code}
fails with:
{code}
Caused by: java.lang.ClassCastException: java.lang.Long cannot be cast to
java.sql.Date
at
org.apache.calcite.avatica.util.AbstractCursor$DateAccessor.getDate(AbstractCursor.java:1031)
at
org.apache.calcite.avatica.util.AbstractCursor$DateAccessor.getLong(AbstractCursor.java:1052)
at
org.apache.calcite.avatica.util.AbstractCursor$AccessorImpl.getInt(AbstractCursor.java:305)
at
org.apache.calcite.avatica.util.AbstractCursor$DateAccessor.getString(AbstractCursor.java:1044)
at
org.apache.calcite.avatica.AvaticaResultSet.getString(AvaticaResultSet.java:257)
at
org.apache.calcite.test.CalciteAssert$ResultSetFormatter.rowToString(CalciteAssert.java:1720)
at
org.apache.calcite.test.CalciteAssert$ResultSetFormatter.toStringList(CalciteAssert.java:1738)
at
org.apache.calcite.test.CalciteAssert.toStringList(CalciteAssert.java:611)
at org.apache.calcite.test.CalciteAssert$9.apply(CalciteAssert.java:383)
at org.apache.calcite.test.CalciteAssert$9.apply(CalciteAssert.java:375)
at
org.apache.calcite.test.CalciteAssert.assertQuery(CalciteAssert.java:533)
at
org.apache.calcite.test.CalciteAssert$AssertQuery.returns(CalciteAssert.java:1266)
{code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Created] (CALCITE-1839) There is any way to rewrite and forceful remove Join,Filter etc.. from RelNode or SqlNode?
Sanjai Verma created CALCITE-1839: - Summary: There is any way to rewrite and forceful remove Join,Filter etc.. from RelNode or SqlNode? Key: CALCITE-1839 URL: https://issues.apache.org/jira/browse/CALCITE-1839 Project: Calcite Issue Type: Improvement Reporter: Sanjai Verma Assignee: Julian Hyde Priority: Minor -- This message was sent by Atlassian JIRA (v6.4.14#64029)
