aggregate functions avg/min/max
50 views
Skip to first unread message
Hannes
May 7, 2013, 3:47:22 AM5/7/13
to opti...@googlegroups.com
Hi,
regarding aggregate functions seems like sum is working but avg/min/max not. The following class defines our test schema:
public static class MyTable {
public String mykey = "foo";
public Integer myvalue = 1;
}
public static class MySchema {
public MyTable[] mytable = new MyTable[] { new MyTable() };
}
---
Selecting avg from myvalue gives the following error:
Exception in thread "main" java.lang.RuntimeException: cannot implement aggregate AVG($0)
at net.hydromatic.optiq.rules.java.JavaRules$EnumerableAggregateRel.implement(JavaRules.java:923)
at net.hydromatic.optiq.rules.java.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:66)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl$OptiqPreparingStmt.implement(OptiqPrepareImpl.java:445)
at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:225)
at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:127)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare_(OptiqPrepareImpl.java:184)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepareSql(OptiqPrepareImpl.java:135)
at net.hydromatic.optiq.jdbc.OptiqStatement.parseQuery(OptiqStatement.java:402)
at net.hydromatic.optiq.jdbc.OptiqStatement.executeQuery(OptiqStatement.java:80)
---
Selecting max from myvalue gives the following error:
Exception in thread "main" java.lang.RuntimeException: while resolving method 'greater' in class class net.hydromatic.optiq.runtime.SqlFunctions
at net.hydromatic.linq4j.expressions.Types.lookupMethod(Types.java:314)
at net.hydromatic.linq4j.expressions.Expressions.call(Expressions.java:409)
at net.hydromatic.linq4j.expressions.Expressions.call(Expressions.java:421)
at net.hydromatic.optiq.rules.java.RexImpTable$MinMaxImplementor2.implementAdd(RexImpTable.java:757)
at net.hydromatic.optiq.rules.java.JavaRules$EnumerableAggregateRel.implement(JavaRules.java:999)
at net.hydromatic.optiq.rules.java.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:66)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl$OptiqPreparingStmt.implement(OptiqPrepareImpl.java:445)
at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:225)
at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:127)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare_(OptiqPrepareImpl.java:184)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepareSql(OptiqPrepareImpl.java:135)
at net.hydromatic.optiq.jdbc.OptiqStatement.parseQuery(OptiqStatement.java:402)
at net.hydromatic.optiq.jdbc.OptiqStatement.executeQuery(OptiqStatement.java:80)
at controllers.SqlTest.main(SqlTest.java:53)
Caused by: java.lang.NoSuchMethodException: net.hydromatic.optiq.runtime.SqlFunctions.greater(int, java.lang.Integer)
at java.lang.Class.getMethod(Unknown Source)
at net.hydromatic.linq4j.expressions.Types.lookupMethod(Types.java:306)
... 13 more
---
Selecting min from myvalue gives the following error:
Exception in thread "main" java.lang.RuntimeException: while resolving method 'lesser' in class class net.hydromatic.optiq.runtime.SqlFunctions
at net.hydromatic.linq4j.expressions.Types.lookupMethod(Types.java:314)
at net.hydromatic.linq4j.expressions.Expressions.call(Expressions.java:409)
at net.hydromatic.linq4j.expressions.Expressions.call(Expressions.java:421)
at net.hydromatic.optiq.rules.java.RexImpTable$MinMaxImplementor2.implementAdd(RexImpTable.java:757)
at net.hydromatic.optiq.rules.java.JavaRules$EnumerableAggregateRel.implement(JavaRules.java:999)
at net.hydromatic.optiq.rules.java.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:66)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl$OptiqPreparingStmt.implement(OptiqPrepareImpl.java:445)
at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:225)
at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:127)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare_(OptiqPrepareImpl.java:184)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepareSql(OptiqPrepareImpl.java:135)
at net.hydromatic.optiq.jdbc.OptiqStatement.parseQuery(OptiqStatement.java:402)
at net.hydromatic.optiq.jdbc.OptiqStatement.executeQuery(OptiqStatement.java:80)
at controllers.SqlTest.main(SqlTest.java:207)
Caused by: java.lang.NoSuchMethodException: net.hydromatic.optiq.runtime.SqlFunctions.lesser(int, java.lang.Integer)
at java.lang.Class.getMethod(Unknown Source)
at net.hydromatic.linq4j.expressions.Types.lookupMethod(Types.java:306)
... 13 more
If I change myvalue from Integer to int, I get the following:
Exception in thread "main" java.lang.RuntimeException: Error while compiling generated Java code:
public static class Record1_0 {
public Integer f0;
public Record1_0(Integer f0) {
this.f0 = f0;
}
public boolean equals(Object o) {
if (this == o) {
return true;
}
if (!(o instanceof Record1_0)) {
return false;
}
return net.hydromatic.optiq.runtime.Utilities.equal(this.f0, ((Record1_0) o).f0);
}
public int hashCode() {
int h = 0;
h = net.hydromatic.optiq.runtime.Utilities.hash(h, this.f0);
return h;
}
public int compareTo(Record1_0 that) {
int c;
c = net.hydromatic.optiq.runtime.Utilities.compareNullsLast(this.f0, that.f0);
if (c != 0) {
return c;
}
return 0;
}
public String toString() {
return "{f0=" + this.f0 + "}";
}
}
public net.hydromatic.linq4j.Enumerable execute(final net.hydromatic.optiq.DataContext root) {
final net.hydromatic.linq4j.Enumerable _inputEnumerable = net.hydromatic.linq4j.Linq4j.asEnumerable(((controllers.SqlTest.MySchema) ((net.hydromatic.optiq.impl.java.ReflectiveSchema) root.getSubSchema("TEST")).getTarget()).mytable);
final net.hydromatic.linq4j.AbstractEnumerable child = new net.hydromatic.linq4j.AbstractEnumerable(){
public net.hydromatic.linq4j.Enumerator enumerator() {
return new net.hydromatic.linq4j.Enumerator(){
public final net.hydromatic.linq4j.Enumerator inputEnumerator = _inputEnumerable.enumerator();
public void reset() {
inputEnumerator.reset();
}
public boolean moveNext() {
return inputEnumerator.moveNext();
}
public Object current() {
return ((controllers.SqlTest.MyTable) inputEnumerator.current()).myvalue;
}
};
}
};
return net.hydromatic.linq4j.Linq4j.singletonEnumerable(child.aggregate(new net.hydromatic.linq4j.function.Function0() {
public Object apply() {
return new Record1_0(
(Integer) null);
}
}
.apply(), new net.hydromatic.linq4j.function.Function2() {
public Record1_0 apply(Record1_0 acc, Integer in) {
acc.f0 = acc.f0 == null ? in : (int) net.hydromatic.optiq.runtime.SqlFunctions.lesser(acc.f0.intValue(), in);
return acc;
}
public Record1_0 apply(Object acc, Object in) {
return apply(
(Record1_0) acc,
(Integer) in);
}
}
, new net.hydromatic.linq4j.function.Function1() {
public Integer apply(Record1_0 acc) {
return acc.f0;
}
public Object apply(Object acc) {
return apply(
(Record1_0) acc);
}
}
));
}
public java.lang.reflect.Type getElementType() {
return int.class;
}
at net.hydromatic.optiq.jdbc.Helper.wrap(Helper.java:38)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl$OptiqPreparingStmt.implement(OptiqPrepareImpl.java:467)
at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:225)
at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:127)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare_(OptiqPrepareImpl.java:184)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepareSql(OptiqPrepareImpl.java:135)
at net.hydromatic.optiq.jdbc.OptiqStatement.parseQuery(OptiqStatement.java:402)
at net.hydromatic.optiq.jdbc.OptiqStatement.executeQuery(OptiqStatement.java:80)
at controllers.SqlTest.main(SqlTest.java:207)
Caused by: org.codehaus.commons.compiler.CompileException: Line 68, Column 94: Invocation of constructor/method with actual parameter type(s) "int, java.lang.Integer" is ambiguous: "public static java.lang.Comparable net.hydromatic.optiq.runtime.SqlFunctions.lesser(java.lang.Comparable, java.lang.Comparable)" vs. "public static int net.hydromatic.optiq.runtime.SqlFunctions.lesser(int, int)"
at org.codehaus.janino.UnitCompiler.compileError(UnitCompiler.java:9014)
at org.codehaus.janino.UnitCompiler.findMostSpecificIInvocable(UnitCompiler.java:6938)
at org.codehaus.janino.UnitCompiler.findMostSpecificIInvocable(UnitCompiler.java:6696)
at org.codehaus.janino.UnitCompiler.findIMethod(UnitCompiler.java:6589)
...
at net.hydromatic.optiq.prepare.OptiqPrepareImpl$OptiqPreparingStmt.implement(OptiqPrepareImpl.java:459)
... 7 more
Thanks a lot.
best regards, -Hannes-
Julian Hyde
May 10, 2013, 6:38:53 PM5/10/13
to opti...@googlegroups.com
I've fixed MIN/MAX in https://github.com/julianhyde/optiq/commit/b5f4ca084cd76220c35b8639338fe0685f958b9f and https://github.com/julianhyde/linq4j/commit/d68942781e9bf76d38610052545fc43ffb605440.
Working on AVG now.
Julian
Hannes
May 13, 2013, 6:38:17 AM5/13/13
to opti...@googlegroups.com
Hi Julian, excellent, thank you very much.
best regards, -Hannes-
Reply all
Reply to author
Forward
0 new messages