[
https://issues.apache.org/jira/browse/CALCITE-5388?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17636252#comment-17636252
]
Benchao Li edited comment on CALCITE-5388 at 11/20/22 1:26 AM:
---------------------------------------------------------------
Further more, I spotted another two interesting related problems.
1, There is a {{EnumerableSort}} in the plan, which is not necessary
2, In the final generated code, "comparator" is not reused while merging two
blocks (currently "tempList" is reused), after digging more, I found that
{{{}ParameterExpression{}}}'s {{equals}} and {{hashCode}} is implemented
different from other Expressions.
These two problems does not directly related to this issue, we can open other
Jira issues to track them.
Below is the full generated code:
{code:java}
public org.apache.calcite.linq4j.Enumerable bind(final
org.apache.calcite.DataContext root) {
int prevStart;
int prevEnd;
final java.util.Comparator comparator = new java.util.Comparator(){
public int compare(Integer v0, Integer v1) {
final int c;
c = org.apache.calcite.runtime.Utilities.compare(v0, v1);
if (c != 0) {
return c;
}
return 0;
}
public int compare(Object o0, Object o1) {
return this.compare((Integer) o0, (Integer) o1);
}
};
final java.util.List tempList = (java.util.List)
org.apache.calcite.linq4j.Linq4j.asEnumerable(new Integer[] {
1,
2}).into(new java.util.ArrayList());
final java.util.Iterator iterator =
org.apache.calcite.runtime.SortedMultiMap.singletonArrayIterator(comparator,
tempList);
final java.util.ArrayList _list = new java.util.ArrayList(
tempList.size());
Long a0w0 = (Long) null;
while (iterator.hasNext()) {
final Object[] _rows = (Object[]) iterator.next();
prevStart = -1;
prevEnd = 2147483647;
for (int i = 0; i < _rows.length; ++i) {
if (i != prevEnd) {
int actualStart = i < prevEnd ? 0 : prevEnd + 1;
prevEnd = i;
a0w0 = Long.valueOf(i - 0 + 1);
}
_list.add(new Object[] {
org.apache.calcite.runtime.SqlFunctions.toInt(_rows[i]),
a0w0});
}
}
tempList.clear();
final org.apache.calcite.linq4j.Enumerable _inputEnumerable =
org.apache.calcite.linq4j.Linq4j.asEnumerable(_list);
final org.apache.calcite.linq4j.AbstractEnumerable child = 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 Object input_value = current[1];
final Object input_value0 = current[0];
return new Object[] {
input_value,
input_value0};
}
};
}
};
int prevStart0;
int prevEnd0;
final java.util.Comparator comparator0 = new java.util.Comparator(){
public int compare(Integer v0, Integer v1) {
final int c;
c = org.apache.calcite.runtime.Utilities.compare(v0, v1);
if (c != 0) {
return c;
}
return 0;
}
public int compare(Object o0, Object o1) {
return this.compare((Integer) o0, (Integer) o1);
}
};
final java.util.Iterator iterator0 =
org.apache.calcite.runtime.SortedMultiMap.singletonArrayIterator(comparator0,
tempList);
final java.util.ArrayList _list0 = new java.util.ArrayList(
tempList.size());
Long a0w00 = (Long) null;
while (iterator0.hasNext()) {
final Object[] _rows = (Object[]) iterator0.next();
prevStart0 = -1;
prevEnd0 = 2147483647;
for (int i = 0; i < _rows.length; ++i) {
if (i != prevEnd0) {
int actualStart = i < prevEnd0 ? 0 : prevEnd0 + 1;
prevEnd0 = i;
a0w00 = Long.valueOf(i - 0 + 1);
}
_list0.add(new Object[] {
org.apache.calcite.runtime.SqlFunctions.toInt(_rows[i]),
a0w00});
}
}
tempList.clear();
final org.apache.calcite.linq4j.Enumerable _inputEnumerable0 =
org.apache.calcite.linq4j.Linq4j.asEnumerable(_list0);
final org.apache.calcite.linq4j.AbstractEnumerable child0 = 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 =
_inputEnumerable0.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 Object input_value = current[1];
final Object input_value0 = current[0];
return new Object[] {
input_value,
input_value0};
}
};
}
};
final org.apache.calcite.linq4j.Enumerable _inputEnumerable1 =
child.orderBy(new org.apache.calcite.linq4j.function.Function1() {
public int apply(Object[] v) {
return org.apache.calcite.runtime.SqlFunctions.toInt(v[1]);
}
public Object apply(Object v) {
return apply(
(Object[]) v);
}
}
, org.apache.calcite.linq4j.function.Functions.nullsComparator(false,
false)).hashJoin(child0.orderBy(new
org.apache.calcite.linq4j.function.Function1() {
public int apply(Object[] v) {
return org.apache.calcite.runtime.SqlFunctions.toInt(v[1]);
}
public Object apply(Object v) {
return apply(
(Object[]) v);
}
}
, org.apache.calcite.linq4j.function.Functions.nullsComparator(false,
false)), new org.apache.calcite.linq4j.function.Function1() {
public int apply(Object[] v1) {
return org.apache.calcite.runtime.SqlFunctions.toInt(v1[1]);
}
public Object apply(Object v1) {
return apply(
(Object[]) v1);
}
}
, new org.apache.calcite.linq4j.function.Function1() {
public int apply(Object[] v1) {
return org.apache.calcite.runtime.SqlFunctions.toInt(v1[1]);
}
public Object apply(Object v1) {
return apply(
(Object[]) v1);
}
}
, new org.apache.calcite.linq4j.function.Function2() {
public Object[] apply(Object[] left, Object[] right) {
return new Object[] {
left[0],
left[1],
right[0],
right[1]};
}
public Object[] apply(Object left, Object right) {
return apply(
(Object[]) left,
(Object[]) right);
}
}
, null, false, false, null);
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 =
_inputEnumerable1.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 Object input_value = current[0];
final Object input_value0 = current[1];
final Object input_value1 = current[3];
final Object input_value2 = current[2];
return new Object[] {
input_value,
input_value0,
input_value1,
input_value2};
}
};
}
};
}
public Class getElementType() {
return java.lang.Object[].class;
}
{code}
was (Author: libenchao):
Further more, I spotted another two interesting related problems.
1, There is a {{EnumerableSort}} in the plan, which is not necessary
2, In the final generated code, "comparator" is not reused (currently
"tempList" is reused), after digging more, I found that
{{ParameterExpression}}'s {{equals}} and {{hashCode}} is implemented different
from other {{Expression}}s.
These two problems does not directly related to this issue, we can open other
Jira issues to track them.
Below is the full generated code:
{code:java}
public org.apache.calcite.linq4j.Enumerable bind(final
org.apache.calcite.DataContext root) {
int prevStart;
int prevEnd;
final java.util.Comparator comparator = new java.util.Comparator(){
public int compare(Integer v0, Integer v1) {
final int c;
c = org.apache.calcite.runtime.Utilities.compare(v0, v1);
if (c != 0) {
return c;
}
return 0;
}
public int compare(Object o0, Object o1) {
return this.compare((Integer) o0, (Integer) o1);
}
};
final java.util.List tempList = (java.util.List)
org.apache.calcite.linq4j.Linq4j.asEnumerable(new Integer[] {
1,
2}).into(new java.util.ArrayList());
final java.util.Iterator iterator =
org.apache.calcite.runtime.SortedMultiMap.singletonArrayIterator(comparator,
tempList);
final java.util.ArrayList _list = new java.util.ArrayList(
tempList.size());
Long a0w0 = (Long) null;
while (iterator.hasNext()) {
final Object[] _rows = (Object[]) iterator.next();
prevStart = -1;
prevEnd = 2147483647;
for (int i = 0; i < _rows.length; ++i) {
if (i != prevEnd) {
int actualStart = i < prevEnd ? 0 : prevEnd + 1;
prevEnd = i;
a0w0 = Long.valueOf(i - 0 + 1);
}
_list.add(new Object[] {
org.apache.calcite.runtime.SqlFunctions.toInt(_rows[i]),
a0w0});
}
}
tempList.clear();
final org.apache.calcite.linq4j.Enumerable _inputEnumerable =
org.apache.calcite.linq4j.Linq4j.asEnumerable(_list);
final org.apache.calcite.linq4j.AbstractEnumerable child = 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 Object input_value = current[1];
final Object input_value0 = current[0];
return new Object[] {
input_value,
input_value0};
}
};
}
};
int prevStart0;
int prevEnd0;
final java.util.Comparator comparator0 = new java.util.Comparator(){
public int compare(Integer v0, Integer v1) {
final int c;
c = org.apache.calcite.runtime.Utilities.compare(v0, v1);
if (c != 0) {
return c;
}
return 0;
}
public int compare(Object o0, Object o1) {
return this.compare((Integer) o0, (Integer) o1);
}
};
final java.util.Iterator iterator0 =
org.apache.calcite.runtime.SortedMultiMap.singletonArrayIterator(comparator0,
tempList);
final java.util.ArrayList _list0 = new java.util.ArrayList(
tempList.size());
Long a0w00 = (Long) null;
while (iterator0.hasNext()) {
final Object[] _rows = (Object[]) iterator0.next();
prevStart0 = -1;
prevEnd0 = 2147483647;
for (int i = 0; i < _rows.length; ++i) {
if (i != prevEnd0) {
int actualStart = i < prevEnd0 ? 0 : prevEnd0 + 1;
prevEnd0 = i;
a0w00 = Long.valueOf(i - 0 + 1);
}
_list0.add(new Object[] {
org.apache.calcite.runtime.SqlFunctions.toInt(_rows[i]),
a0w00});
}
}
tempList.clear();
final org.apache.calcite.linq4j.Enumerable _inputEnumerable0 =
org.apache.calcite.linq4j.Linq4j.asEnumerable(_list0);
final org.apache.calcite.linq4j.AbstractEnumerable child0 = 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 =
_inputEnumerable0.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 Object input_value = current[1];
final Object input_value0 = current[0];
return new Object[] {
input_value,
input_value0};
}
};
}
};
final org.apache.calcite.linq4j.Enumerable _inputEnumerable1 =
child.orderBy(new org.apache.calcite.linq4j.function.Function1() {
public int apply(Object[] v) {
return org.apache.calcite.runtime.SqlFunctions.toInt(v[1]);
}
public Object apply(Object v) {
return apply(
(Object[]) v);
}
}
, org.apache.calcite.linq4j.function.Functions.nullsComparator(false,
false)).hashJoin(child0.orderBy(new
org.apache.calcite.linq4j.function.Function1() {
public int apply(Object[] v) {
return org.apache.calcite.runtime.SqlFunctions.toInt(v[1]);
}
public Object apply(Object v) {
return apply(
(Object[]) v);
}
}
, org.apache.calcite.linq4j.function.Functions.nullsComparator(false,
false)), new org.apache.calcite.linq4j.function.Function1() {
public int apply(Object[] v1) {
return org.apache.calcite.runtime.SqlFunctions.toInt(v1[1]);
}
public Object apply(Object v1) {
return apply(
(Object[]) v1);
}
}
, new org.apache.calcite.linq4j.function.Function1() {
public int apply(Object[] v1) {
return org.apache.calcite.runtime.SqlFunctions.toInt(v1[1]);
}
public Object apply(Object v1) {
return apply(
(Object[]) v1);
}
}
, new org.apache.calcite.linq4j.function.Function2() {
public Object[] apply(Object[] left, Object[] right) {
return new Object[] {
left[0],
left[1],
right[0],
right[1]};
}
public Object[] apply(Object left, Object right) {
return apply(
(Object[]) left,
(Object[]) right);
}
}
, null, false, false, null);
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 =
_inputEnumerable1.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 Object input_value = current[0];
final Object input_value0 = current[1];
final Object input_value1 = current[3];
final Object input_value2 = current[2];
return new Object[] {
input_value,
input_value0,
input_value1,
input_value2};
}
};
}
};
}
public Class getElementType() {
return java.lang.Object[].class;
}
{code}
> No result when using ROW_NUMBER in two common table expressions
> ---------------------------------------------------------------
>
> Key: CALCITE-5388
> URL: https://issues.apache.org/jira/browse/CALCITE-5388
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.32.0
> Reporter: Magnus Mogren
> Priority: Major
>
> This SQL produces no result.
>
> {code:java}
> with
> CTE1(rownr1, val1) as ( select ROW_NUMBER() OVER(ORDER BY id ASC), id
> from (values (1), (2)) as Vals1(id) ),
> CTE2(rownr2, val2) as ( select ROW_NUMBER() OVER(ORDER BY id ASC), id
> from (values (1), (2)) as Vals2(id) )
> select
> CTE1.rownr1,
> CTE1.val1,
> CTE2.rownr2,
> CTE2.val2
> from
> CTE1,
> CTE2
> where
> CTE1.val1 = CTE2.val2{code}
>
> However, if you remove CTE.rownr2 from the selected columns it produces the 2
> rows as expected:
> |ROWNR1|VAL1|VAL2|
> |1|1|1|
> |2|2|2|
>
> Same type of problem occurs of you try to compare the two rownr columns. No
> result for this:
> {code:java}
> with
> CTE1(rownr1, val1) as ( select ROW_NUMBER() OVER(ORDER BY id ASC), id
> from (values (1), (2)) as Vals1(id) ),
> CTE2(rownr2, val2) as ( select ROW_NUMBER() OVER(ORDER BY id ASC), id
> from (values (1), (2)) as Vals2(id) )
> select
> CTE1.val1,
> CTE2.val2
> from
> CTE1,
> CTE2
> where
> CTE1.rownr1 = CTE2.rownr2{code}
>
> Does calcite get confused over the fact that two ROW_NUMBER functions are
> used among the common table expressions?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
