You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2016/12/20 20:01:10 UTC
[1/2] calcite git commit: [CALCITE-1540] Support multiple columns in
PARTITION BY clause of window function (Hongbin Ma)
Repository: calcite
Updated Branches:
refs/heads/master 34c744107 -> a28378237
[CALCITE-1540] Support multiple columns in PARTITION BY clause of window function (Hongbin Ma)
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/00509094
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/00509094
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/00509094
Branch: refs/heads/master
Commit: 005090949c88a469613bc37f95a80fd531760cd9
Parents: 34c7441
Author: Hongbin Ma <ma...@apache.org>
Authored: Sun Dec 18 23:02:49 2016 -0800
Committer: Julian Hyde <jh...@apache.org>
Committed: Mon Dec 19 12:44:18 2016 -0800
----------------------------------------------------------------------
.../adapter/enumerable/EnumerableWindow.java | 36 ++++++++++++----
.../calcite/adapter/enumerable/PhysType.java | 5 ++-
.../adapter/enumerable/PhysTypeImpl.java | 8 ++--
core/src/test/resources/sql/winagg.iq | 45 ++++++++++++++++++++
4 files changed, 80 insertions(+), 14 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/00509094/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java
index 8e0dd99..5b5c418 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java
@@ -29,6 +29,7 @@ import org.apache.calcite.linq4j.tree.Expressions;
import org.apache.calcite.linq4j.tree.ParameterExpression;
import org.apache.calcite.linq4j.tree.Primitive;
import org.apache.calcite.linq4j.tree.Statement;
+import org.apache.calcite.linq4j.tree.Types;
import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptCost;
import org.apache.calcite.plan.RelOptPlanner;
@@ -689,15 +690,32 @@ public class EnumerableWindow extends Window implements EnumerableRel {
final ParameterExpression v_ =
Expressions.parameter(inputPhysType.getJavaRowType(),
builder2.newName("v"));
- final DeclarationStatement declare =
- Expressions.declare(
- 0, "key",
- inputPhysType.selector(
- v_,
- group.keys.asList(),
- JavaRowFormat.CUSTOM));
- builder2.add(declare);
- final ParameterExpression key_ = declare.parameter;
+
+ Pair<Type, List<Expression>> selector =
+ inputPhysType.selector(v_, group.keys.asList(), JavaRowFormat.CUSTOM);
+ final ParameterExpression key_;
+ if (selector.left instanceof Types.RecordType) {
+ Types.RecordType keyJavaType = (Types.RecordType) selector.left;
+ List<Expression> initExpressions = selector.right;
+ key_ = Expressions.parameter(keyJavaType, "key");
+ builder2.add(Expressions.declare(0, key_, null));
+ builder2.add(
+ Expressions.statement(
+ Expressions.assign(key_, Expressions.new_(keyJavaType))));
+ List<Types.RecordField> fieldList = keyJavaType.getRecordFields();
+ for (int i = 0; i < initExpressions.size(); i++) {
+ Expression right = initExpressions.get(i);
+ builder2.add(
+ Expressions.statement(
+ Expressions.assign(
+ Expressions.field(key_, fieldList.get(i)), right)));
+ }
+ } else {
+ DeclarationStatement declare =
+ Expressions.declare(0, "key", selector.right.get(0));
+ builder2.add(declare);
+ key_ = declare.parameter;
+ }
builder2.add(
Expressions.statement(
Expressions.call(
http://git-wip-us.apache.org/repos/asf/calcite/blob/00509094/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysType.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysType.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysType.java
index 0cf1dfe..20ae559 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysType.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysType.java
@@ -140,8 +140,9 @@ public interface PhysType {
List<Integer> usedFields,
JavaRowFormat targetFormat);
- /** Generates a selector for the given fields from an expression. */
- Expression selector(
+ /** Generates a selector for the given fields from an expression.
+ * Only used by EnumerableWindow. */
+ Pair<Type, List<Expression>> selector(
ParameterExpression parameter,
List<Integer> fields,
JavaRowFormat targetFormat);
http://git-wip-us.apache.org/repos/asf/calcite/blob/00509094/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java
index f321572..49b44ba 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java
@@ -193,7 +193,7 @@ public class PhysTypeImpl implements PhysType {
targetPhysType.record(expressions), parameter);
}
- public Expression selector(
+ public Pair<Type, List<Expression>> selector(
ParameterExpression parameter,
List<Integer> fields,
JavaRowFormat targetFormat) {
@@ -210,9 +210,11 @@ public class PhysTypeImpl implements PhysType {
project(fields, targetFormat);
switch (format) {
case SCALAR:
- return parameter;
+ return Pair.of(parameter.getType(),
+ Collections.<Expression>singletonList(parameter));
default:
- return targetPhysType.record(fieldReferences(parameter, fields));
+ return Pair.of(targetPhysType.getJavaRowType(),
+ fieldReferences(parameter, fields));
}
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/00509094/core/src/test/resources/sql/winagg.iq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/winagg.iq b/core/src/test/resources/sql/winagg.iq
index c6013fa..40ef65f 100644
--- a/core/src/test/resources/sql/winagg.iq
+++ b/core/src/test/resources/sql/winagg.iq
@@ -42,6 +42,51 @@ order by sum1, sum2;
!ok
+# [CALCITE-1540] Support multiple columns in PARTITION BY clause of window function
+select gender,deptno,
+ count(*) over (partition by gender,deptno) as count1
+from emp;
+
++--------+--------+--------+
+| GENDER | DEPTNO | COUNT1 |
++--------+--------+--------+
+| F | 10 | 1 |
+| F | 30 | 2 |
+| F | 30 | 2 |
+| F | 50 | 1 |
+| F | 60 | 1 |
+| F | | 1 |
+| M | 10 | 1 |
+| M | 20 | 1 |
+| M | 50 | 1 |
++--------+--------+--------+
+(9 rows)
+
+!ok
+
+# Partition by same column twice.
+# (It's degenerate, but I believe it's valid SQL.)
+select gender,deptno,
+ count(*) over (partition by gender,gender) as count1
+from emp;
+
++--------+--------+--------+
+| GENDER | DEPTNO | COUNT1 |
++--------+--------+--------+
+| F | 10 | 6 |
+| F | 30 | 6 |
+| F | 30 | 6 |
+| F | 50 | 6 |
+| F | 60 | 6 |
+| F | | 6 |
+| M | 10 | 3 |
+| M | 20 | 3 |
+| M | 50 | 3 |
++--------+--------+--------+
+(9 rows)
+
+!ok
+
!if (false) {
select *, first_value(deptno) over () from emp;
ename | deptno | gender | first_value
[2/2] calcite git commit: [CALCITE-1414] Add RAND_INTEGER function,
which returns a random integer modulo N (Julian Feinauer)
Posted by jh...@apache.org.
[CALCITE-1414] Add RAND_INTEGER function, which returns a random integer modulo N (Julian Feinauer)
Close apache/calcite#339
A previous PR I forgot to close:
Close apache/calcite#334
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/a2837823
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/a2837823
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/a2837823
Branch: refs/heads/master
Commit: a28378237fdaeef66a74476d30b10bcb5999f2b2
Parents: 0050909
Author: julian <j....@pragmaticminds.de>
Authored: Thu Dec 15 11:32:19 2016 +0100
Committer: Julian Hyde <jh...@apache.org>
Committed: Tue Dec 20 10:23:11 2016 -0800
----------------------------------------------------------------------
.../calcite/adapter/enumerable/RexImpTable.java | 3 ++
.../apache/calcite/runtime/SqlFunctions.java | 8 +++
.../apache/calcite/sql/fun/SqlRandInteger.java | 54 ++++++++++++++++++++
.../calcite/sql/fun/SqlStdOperatorTable.java | 5 ++
.../org/apache/calcite/util/BuiltInMethod.java | 1 +
.../calcite/sql/test/SqlOperatorBaseTest.java | 9 ++++
site/_docs/reference.md | 1 +
7 files changed, 81 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/a2837823/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index ddcb8dc..8b55c22 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -157,6 +157,7 @@ import static org.apache.calcite.sql.fun.SqlStdOperatorTable.OVERLAY;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.PLUS;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.POSITION;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.POWER;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.RAND_INTEGER;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.RANK;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.REINTERPRET;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ROW;
@@ -244,6 +245,8 @@ public class RexImpTable {
defineMethod(LN, "ln", NullPolicy.STRICT);
defineMethod(LOG10, "log10", NullPolicy.STRICT);
defineMethod(ABS, "abs", NullPolicy.STRICT);
+ defineMethod(RAND_INTEGER, BuiltInMethod.RAND_INTEGER.method,
+ NullPolicy.STRICT);
// datetime
defineImplementor(DATETIME_PLUS, NullPolicy.STRICT,
http://git-wip-us.apache.org/repos/asf/calcite/blob/a2837823/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index 87b5528..2fd6586 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -46,6 +46,7 @@ import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
+import java.util.Random;
import java.util.TimeZone;
import java.util.concurrent.atomic.AtomicLong;
import java.util.regex.Pattern;
@@ -1052,6 +1053,13 @@ public class SqlFunctions {
return b0.abs();
}
+ // Random Function
+
+ @NonDeterministic
+ public static int randInteger(int n) {
+ return new Random().nextInt(n);
+ }
+
// Helpers
/** Helper for implementing MIN. Somewhat similar to LEAST operator. */
http://git-wip-us.apache.org/repos/asf/calcite/blob/a2837823/core/src/main/java/org/apache/calcite/sql/fun/SqlRandInteger.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlRandInteger.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlRandInteger.java
new file mode 100644
index 0000000..82302af
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlRandInteger.java
@@ -0,0 +1,54 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.sql.fun;
+
+import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlSyntax;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.ReturnTypes;
+
+/**
+ * The <code>RAND_INTEGER</code> function.
+ */
+public class SqlRandInteger extends SqlFunction {
+ //~ Constructors -----------------------------------------------------------
+
+ public SqlRandInteger() {
+ super(
+ "RAND_INTEGER",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.INTEGER,
+ null,
+ OperandTypes.NUMERIC,
+ SqlFunctionCategory.NUMERIC);
+ }
+
+ //~ Methods ----------------------------------------------------------------
+
+ public SqlSyntax getSyntax() {
+ return SqlSyntax.FUNCTION;
+ }
+
+ // Plans referencing context variables should never be cached
+ public boolean isDynamicFunction() {
+ return true;
+ }
+}
+
+// End SqlRandInteger.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/a2837823/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 18f48d2..1511909 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -230,6 +230,11 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
OperandTypes.DIVISION_OPERATOR);
/**
+ * Random Integer.
+ */
+ public static final SqlRandInteger RAND_INTEGER = new SqlRandInteger();
+
+ /**
* Internal integer arithmetic division operator, '<code>/INT</code>'. This
* is only used to adjust scale for numerics. We distinguish it from
* user-requested division since some personalities want a floating-point
http://git-wip-us.apache.org/repos/asf/calcite/blob/a2837823/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index 47be44b..9968b86 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -259,6 +259,7 @@ public enum BuiltInMethod {
OVERLAY3(SqlFunctions.class, "overlay", String.class, String.class, int.class,
int.class),
POSITION(SqlFunctions.class, "position", String.class, String.class),
+ RAND_INTEGER(SqlFunctions.class, "randInteger", int.class),
TRUNCATE(SqlFunctions.class, "truncate", String.class, int.class),
TRUNCATE_OR_PAD(SqlFunctions.class, "truncateOrPad", String.class, int.class),
TRIM(SqlFunctions.class, "trim", boolean.class, boolean.class, String.class,
http://git-wip-us.apache.org/repos/asf/calcite/blob/a2837823/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
index de58609..63ec1f9 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
@@ -3809,6 +3809,15 @@ public abstract class SqlOperatorBaseTest {
tester.checkNull("log10(cast(null as real))");
}
+ @Test public void testRandomFunc() {
+ tester.setFor(SqlStdOperatorTable.RAND_INTEGER);
+ for (int i = 0; i < 100; i++) {
+ // Result must always be between 0 and 10, inclusive.
+ tester.checkScalarApprox("rand_integer(11)", "INTEGER NOT NULL", 5.0,
+ 5.0);
+ }
+ }
+
@Test public void testAbsFunc() {
tester.setFor(SqlStdOperatorTable.ABS);
tester.checkScalarExact("abs(-1)", "1");
http://git-wip-us.apache.org/repos/asf/calcite/blob/a2837823/site/_docs/reference.md
----------------------------------------------------------------------
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 6b4c4ab..9a8c9bf 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -963,6 +963,7 @@ The operator precedence and associativity, highest to lowest.
| EXP(numeric) | Returns *e* raised to the power of *numeric*
| CEIL(numeric) | Rounds *numeric* up, and returns the smallest number that is greater than or equal to *numeric*
| FLOOR(numeric) | Rounds *numeric* down, and returns the largest number that is less than or equal to *numeric*
+| RAND_INTEGER(numeric) | Generates a random integer between 0 and *numeric* - 1 inclusive
### Character string operators and functions