ignite-sql - order by, top, offset, limit, having
Project: http://git-wip-us.apache.org/repos/asf/incubator-ignite/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-ignite/commit/fb4a74c3 Tree: http://git-wip-us.apache.org/repos/asf/incubator-ignite/tree/fb4a74c3 Diff: http://git-wip-us.apache.org/repos/asf/incubator-ignite/diff/fb4a74c3 Branch: refs/heads/ignite-sql Commit: fb4a74c30865bbc111c1b4d0aab5fd8fd3f3b8d6 Parents: 7e8ea4f Author: S.Vladykin <svlady...@gridgain.com> Authored: Mon Feb 2 03:31:34 2015 +0300 Committer: S.Vladykin <svlady...@gridgain.com> Committed: Mon Feb 2 03:31:34 2015 +0300 ---------------------------------------------------------------------- .../processors/query/h2/IgniteH2Indexing.java | 9 +- .../processors/query/h2/sql/GridSqlColumn.java | 6 ++ .../processors/query/h2/sql/GridSqlElement.java | 25 ++++- .../query/h2/sql/GridSqlQueryParser.java | 27 +++-- .../query/h2/sql/GridSqlQuerySplitter.java | 92 +++++++++++------ .../processors/query/h2/sql/GridSqlSelect.java | 100 +++++++++++++++++-- .../query/h2/sql/GridSqlSortColumn.java | 76 ++++++++++++++ .../cache/GridCacheCrossCacheQuerySelfTest.java | 60 ++++++++++- .../query/h2/sql/GridQueryParsingTest.java | 1 + 9 files changed, 343 insertions(+), 53 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/fb4a74c3/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/IgniteH2Indexing.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/IgniteH2Indexing.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/IgniteH2Indexing.java index 94d154c..b89a05a 100644 --- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/IgniteH2Indexing.java +++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/IgniteH2Indexing.java @@ -262,7 +262,7 @@ public class IgniteH2Indexing implements GridQueryIndexing { stmt.executeUpdate("SET SCHEMA \"" + schema + '"'); if (log.isDebugEnabled()) - log.debug("Initialized H2 schema for queries on space: " + schema); + log.debug("Set schema: " + schema); c.schema(schema); } @@ -768,7 +768,12 @@ public class IgniteH2Indexing implements GridQueryIndexing { return new GridFinishedFutureEx<>(e); } - return queryTwoStep(space, GridSqlQuerySplitter.split(c, sqlQry, params)); + GridCacheTwoStepQuery twoStepQry = GridSqlQuerySplitter.split(c, sqlQry, params); + + if (log.isDebugEnabled()) + log.debug("Parsed query: `" + sqlQry + "` into two step query: " + twoStepQry); + + return queryTwoStep(space, twoStepQry); } /** http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/fb4a74c3/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlColumn.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlColumn.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlColumn.java index 31acbe0..c334629 100644 --- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlColumn.java +++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlColumn.java @@ -35,6 +35,12 @@ public class GridSqlColumn extends GridSqlElement implements GridSqlValue { this.sqlText = sqlText; } + /** + * @return Simple unqualified column with only name. + */ + public GridSqlColumn simplify() { + return new GridSqlColumn(null, colName, colName); + } /** * @return Column name. http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/fb4a74c3/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlElement.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlElement.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlElement.java index 35140b5..698cc16 100644 --- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlElement.java +++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlElement.java @@ -29,6 +29,14 @@ public abstract class GridSqlElement implements Cloneable { } /** + * Clears all children. + */ + public void clearChildren() { + if (size() != 0) + children = new ArrayList<>(); + } + + /** * @param expr Expr. * @return {@code this}. */ @@ -58,7 +66,7 @@ public abstract class GridSqlElement implements Cloneable { /** {@inheritDoc} */ @SuppressWarnings({"CloneCallsConstructors", "CloneDoesntDeclareCloneNotSupportedException"}) - @Override protected GridSqlElement clone() { + @Override public GridSqlElement clone() { try { GridSqlElement res = (GridSqlElement)super.clone(); @@ -70,4 +78,19 @@ public abstract class GridSqlElement implements Cloneable { throw new IllegalStateException(e); } } + + /** + * @param idx Index. + * @param child New child. + */ + public void child(int idx, GridSqlElement child) { + children.set(idx, child); + } + + /** + * @return Number of children. + */ + public int size() { + return children.size(); + } } http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/fb4a74c3/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQueryParser.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQueryParser.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQueryParser.java index 7a7c0e6..2865db9 100644 --- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQueryParser.java +++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQueryParser.java @@ -193,7 +193,7 @@ public class GridSqlQueryParser { Expression where = CONDITION.get(select); res.where(parseExpression(where)); - Set<TableFilter> allFilers = new HashSet<>(select.getTopFilters()); + Set<TableFilter> allFilters = new HashSet<>(select.getTopFilters()); GridSqlElement from = null; @@ -209,7 +209,7 @@ public class GridSqlQueryParser { from = from == null ? gridFilter : new GridSqlJoin(from, gridFilter); - allFilers.remove(filter); + allFilters.remove(filter); filter = filter.getJoin(); } @@ -217,7 +217,7 @@ public class GridSqlQueryParser { res.from(from); - assert allFilers.isEmpty(); + assert allFilters.isEmpty(); ArrayList<Expression> expressions = select.getExpressions(); @@ -233,12 +233,13 @@ public class GridSqlQueryParser { res.addGroupExpression(parseExpression(expressions.get(idx))); } - assert0(select.getHaving() == null, select); - int havingIdx = HAVING_INDEX.get(select); - if (havingIdx >= 0) + if (havingIdx >= 0) { + res.havingColumn(havingIdx); + res.having(parseExpression(expressions.get(havingIdx))); + } for (int i = 0; i < select.getColumnCount(); i++) res.addSelectExpression(parseExpression(expressions.get(i))); @@ -249,10 +250,20 @@ public class GridSqlQueryParser { int[] indexes = sortOrder.getQueryColumnIndexes(); int[] sortTypes = sortOrder.getSortTypes(); - for (int i = 0; i < indexes.length; i++) - res.addSort(parseExpression(expressions.get(indexes[i])), sortTypes[i]); + for (int i = 0; i < indexes.length; i++) { + int colIdx = indexes[i]; + int type = sortTypes[i]; + + res.addSort(parseExpression(expressions.get(colIdx)), new GridSqlSortColumn(colIdx, + (type & SortOrder.DESCENDING) == 0, + (type & SortOrder.NULLS_FIRST) != 0, + (type & SortOrder.NULLS_LAST) != 0)); + } } + res.limit(parseExpression(select.getLimit())); + res.offset(parseExpression(select.getOffset())); + return res; } http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/fb4a74c3/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQuerySplitter.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQuerySplitter.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQuerySplitter.java index cc40f9e..c04cce2 100644 --- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQuerySplitter.java +++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlQuerySplitter.java @@ -50,56 +50,75 @@ public class GridSqlQuerySplitter { * @return Two step query. */ public static GridCacheTwoStepQuery split(Connection conn, String query, Object[] params) { - // TODO possibly get column types from query. GridSqlSelect srcQry = GridSqlQueryParser.parse(conn, query); - if (srcQry.groups().isEmpty()) { // Simple case. - String tbl0 = table(0); + final String mergeTable = table(0); - GridCacheTwoStepQuery res = new GridCacheTwoStepQuery("select * from " + tbl0); - - res.addMapQuery(tbl0, srcQry.getSQL(), params); - - return res; - } + GridSqlSelect mapQry = srcQry.clone(); + GridSqlSelect rdcQry = new GridSqlSelect().from(table(mergeTable)); // Split all select expressions into map-reduce parts. List<GridSqlElement> mapExps = new ArrayList<>(srcQry.allExpressions()); - GridSqlElement[] rdcExps = new GridSqlElement[srcQry.select().size()]; - for (int i = 0, len = mapExps.size(); i < len; i++) + for (int i = 0, len = mapExps.size(); i < len; i++) // Remember len because mapExps list can grow. splitSelectExpression(mapExps, rdcExps, i); - // Build map query. - GridSqlSelect mapQry = srcQry.clone(); - + // Fill select expressions. mapQry.clearSelect(); for (GridSqlElement exp : mapExps) mapQry.addSelectExpression(exp); - mapQry.clearGroups(); + for (GridSqlElement rdcExp : rdcExps) + rdcQry.addSelectExpression(rdcExp); + + // -- GROUP BY + if (!srcQry.groups().isEmpty()) { + mapQry.clearGroups(); - for (int col : srcQry.groupColumns()) - mapQry.addGroupExpression(column(((GridSqlAlias)mapExps.get(col)).alias())); + for (int col : srcQry.groupColumns()) + mapQry.addGroupExpression(column(((GridSqlAlias)mapExps.get(col)).alias())); - // TODO sort support + for (int col : srcQry.groupColumns()) + rdcQry.addGroupExpression(column(((GridSqlAlias)mapExps.get(col)).alias())); + } - // Reduce query. - GridSqlSelect rdcQry = new GridSqlSelect(); + // -- HAVING + if (srcQry.having() != null) { + // TODO Find aggregate functions in HAVING clause. + rdcQry.whereAnd(column(columnName(srcQry.havingColumn()))); - for (GridSqlElement rdcExp : rdcExps) - rdcQry.addSelectExpression(rdcExp); + mapQry.having(null); + } + + // -- ORDER BY + if (!srcQry.sort().isEmpty()) { + for (GridSqlSortColumn sortCol : srcQry.sort().values()) + rdcQry.addSort(column(((GridSqlAlias)mapExps.get(sortCol.column())).alias()), sortCol); + } + + // -- LIMIT + if (srcQry.limit() != null) + rdcQry.limit(srcQry.limit()); - rdcQry.from(new GridSqlTable(null, table(0))); + // -- OFFSET + if (srcQry.offset() != null) { + mapQry.offset(null); - for (int col : srcQry.groupColumns()) - rdcQry.addGroupExpression(column(((GridSqlAlias)mapExps.get(col)).alias())); + rdcQry.offset(srcQry.offset()); + } + + // -- DISTINCT + if (srcQry.distinct()) { + mapQry.distinct(false); + rdcQry.distinct(true); + } + // Build resulting two step query. GridCacheTwoStepQuery res = new GridCacheTwoStepQuery(rdcQry.getSQL()); - res.addMapQuery(table(0), mapQry.getSQL(), params); + res.addMapQuery(mergeTable, mapQry.getSQL(), params); return res; } @@ -127,7 +146,7 @@ public class GridSqlQuerySplitter { String mapAggAlias = columnName(idx); switch (agg.type()) { - case AVG: // SUM( AVG(CAST(x AS DECIMAL))*COUNT(x) )/SUM( COUNT(x) ). + case AVG: // SUM( AVG(CAST(x AS DOUBLE))*COUNT(x) )/SUM( COUNT(x) ). //-- COUNT(x) map GridSqlElement cntMapAgg = aggregate(agg.distinct(), COUNT).addChild(agg.child()); @@ -139,9 +158,9 @@ public class GridSqlQuerySplitter { mapSelect.add(cntMapAgg); - //-- AVG(CAST(x AS DECIMAL)) map + //-- AVG(CAST(x AS DOUBLE)) map mapAgg = aggregate(agg.distinct(), AVG).addChild( // Add function argument. - function(CAST).setCastType("DECIMAL").addChild(agg.child())); + function(CAST).setCastType("DOUBLE").addChild(agg.child())); //-- SUM( AVG(x)*COUNT(x) )/SUM( COUNT(x) ) reduce GridSqlElement sumUpRdc = aggregate(false, SUM).addChild( @@ -196,7 +215,12 @@ public class GridSqlQuerySplitter { } else { if (alias == null) { // Generate alias if none. - alias = alias(columnName(idx), mapSelect.get(idx)); + GridSqlElement expr = mapSelect.get(idx); + + String aliasName = expr instanceof GridSqlColumn ? ((GridSqlColumn)expr).columnName() : + columnName(idx); + + alias = alias(aliasName, expr); mapSelect.set(idx, alias); } @@ -249,4 +273,12 @@ public class GridSqlQuerySplitter { private static GridSqlFunction function(GridSqlFunctionType type) { return new GridSqlFunction(type); } + + /** + * @param name Table name. + * @return Table. + */ + private static GridSqlTable table(String name) { + return new GridSqlTable(null, name); + } } http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/fb4a74c3/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlSelect.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlSelect.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlSelect.java index ee4d6c8..e8da8f5 100644 --- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlSelect.java +++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlSelect.java @@ -9,7 +9,6 @@ package org.apache.ignite.internal.processors.query.h2.sql; -import org.h2.result.*; import org.h2.util.*; import java.util.*; @@ -43,7 +42,44 @@ public class GridSqlSelect implements Cloneable { private GridSqlElement having; /** */ - private Map<GridSqlElement, Integer> sort = new LinkedHashMap<>(); + private int havingCol = -1; + + /** */ + private Map<GridSqlElement,GridSqlSortColumn> sort = new LinkedHashMap<>(); + + /** */ + private GridSqlElement offset; + + /** */ + private GridSqlElement limit; + + /** + * @return Offset. + */ + public GridSqlElement offset() { + return offset; + } + + /** + * @param offset Offset. + */ + public void offset(GridSqlElement offset) { + this.offset = offset; + } + + /** + * @param limit Limit. + */ + public void limit(GridSqlElement limit) { + this.limit = limit; + } + + /** + * @return Limit. + */ + public GridSqlElement limit() { + return limit; + } /** * @return Distinct. @@ -102,7 +138,7 @@ public class GridSqlSelect implements Cloneable { buff.resetCount(); - for (Map.Entry<GridSqlElement, Integer> entry : sort.entrySet()) { + for (Map.Entry<GridSqlElement,GridSqlSortColumn> entry : sort.entrySet()) { buff.appendExceptFirst(", "); GridSqlElement expression = entry.getKey(); @@ -114,18 +150,24 @@ public class GridSqlSelect implements Cloneable { else buff.append('=').append(StringUtils.unEnclose(expression.getSQL())); - int type = entry.getValue(); + GridSqlSortColumn type = entry.getValue(); - if ((type & SortOrder.DESCENDING) != 0) + if (!type.asc()) buff.append(" DESC"); - if ((type & SortOrder.NULLS_FIRST) != 0) + if (type.nullsFirst()) buff.append(" NULLS FIRST"); - else if ((type & SortOrder.NULLS_LAST) != 0) + else if (type.nullsLast()) buff.append(" NULLS LAST"); } } + if (limit != null) + buff.append(" LIMIT ").append(StringUtils.unEnclose(limit.getSQL())); + + if (offset != null) + buff.append(" OFFSET ").append(StringUtils.unEnclose(offset.getSQL())); + return buff.toString(); } @@ -164,6 +206,9 @@ public class GridSqlSelect implements Cloneable { * @param expression Expression. */ public void addSelectExpression(GridSqlElement expression) { + if (expression == null) + throw new NullPointerException(); + select.add(expression); } @@ -186,6 +231,9 @@ public class GridSqlSelect implements Cloneable { * @param expression Expression. */ public void addGroupExpression(GridSqlElement expression) { + if (expression == null) + throw new NullPointerException(); + groups.add(expression); } @@ -212,9 +260,12 @@ public class GridSqlSelect implements Cloneable { /** * @param from From element. + * @return {@code this}. */ - public void from(GridSqlElement from) { + public GridSqlSelect from(GridSqlElement from) { this.from = from; + + return this; } /** @@ -232,6 +283,21 @@ public class GridSqlSelect implements Cloneable { } /** + * @param condition Adds new WHERE condition using AND operator. + * @return {@code this}. + */ + public GridSqlSelect whereAnd(GridSqlElement condition) { + if (condition == null) + throw new NullPointerException(); + + GridSqlElement old = where(); + + where(old == null ? condition : new GridSqlOperation(GridSqlOperationType.AND, old, condition)); + + return this; + } + + /** * @return Having. */ public GridSqlElement having() { @@ -246,9 +312,23 @@ public class GridSqlSelect implements Cloneable { } /** + * @param col Index of HAVING column. + */ + public void havingColumn(int col) { + this.havingCol = col; + } + + /** + * @return Index of HAVING column. + */ + public int havingColumn() { + return havingCol; + } + + /** * @return Sort. */ - public Map<GridSqlElement, Integer> sort() { + public Map<GridSqlElement,GridSqlSortColumn> sort() { return sort; } @@ -263,7 +343,7 @@ public class GridSqlSelect implements Cloneable { * @param expression Expression. * @param sortType The sort type bit mask (SortOrder.DESCENDING, SortOrder.NULLS_FIRST, SortOrder.NULLS_LAST). */ - public void addSort(GridSqlElement expression, int sortType) { + public void addSort(GridSqlElement expression, GridSqlSortColumn sortType) { sort.put(expression, sortType); } http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/fb4a74c3/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlSortColumn.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlSortColumn.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlSortColumn.java new file mode 100644 index 0000000..4a3384c --- /dev/null +++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/sql/GridSqlSortColumn.java @@ -0,0 +1,76 @@ +/* + * 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.ignite.internal.processors.query.h2.sql; + +/** + * Sort order for ORDER BY clause. + */ +public class GridSqlSortColumn { + /** */ + private final int col; + + /** */ + private final boolean asc; + + /** */ + private final boolean nullsFirst; + + /** */ + private final boolean nullsLast; + + /** + * @param col Column index. + * @param asc Ascending. + * @param nullsFirst Nulls go first. + * @param nullsLast Nulls go last. + */ + public GridSqlSortColumn(int col, boolean asc, boolean nullsFirst, boolean nullsLast) { + this.col = col; + this.asc = asc; + this.nullsFirst = nullsFirst; + this.nullsLast = nullsLast; + } + + /** + * @return Column index. + */ + public int column() { + return col; + } + + /** + * @return {@code true} For ASC order. + */ + public boolean asc() { + return asc; + } + + /** + * @return {@code true} If {@code null}s must go first. + */ + public boolean nullsFirst() { + return nullsFirst; + } + + /** + * @return {@code true} If {@code null}s must go last. + */ + public boolean nullsLast() { + return nullsLast; + } +} http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/fb4a74c3/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/GridCacheCrossCacheQuerySelfTest.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/GridCacheCrossCacheQuerySelfTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/GridCacheCrossCacheQuerySelfTest.java index 42d1bf7..1211301 100644 --- a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/GridCacheCrossCacheQuerySelfTest.java +++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/cache/GridCacheCrossCacheQuerySelfTest.java @@ -114,7 +114,7 @@ public class GridCacheCrossCacheQuerySelfTest extends GridCommonAbstractTest { GridCacheTwoStepQuery q = new GridCacheTwoStepQuery("select cast(sum(x) as long) from _cnts_ where ? = ?", 1, 1); - q.addMapQuery("_cnts_", "select count(*) x from \"partitioned\".FactPurchase where ? = ?", 2 ,2); + q.addMapQuery("_cnts_", "select count(*) x from \"partitioned\".FactPurchase where ? = ?", 2, 2); Object cnt = qx.execute(cache, q).get().iterator().next().get(0); @@ -155,7 +155,9 @@ public class GridCacheCrossCacheQuerySelfTest extends GridCommonAbstractTest { assertFalse(set1.isEmpty()); assertEquals(set0, set1); - X.println("___ AVG MIN MAX SUM COUNT(*) COUNT(x)"); + X.println("___ GROUP BY AVG MIN MAX SUM COUNT(*) COUNT(x)"); + + Set<String> names = new HashSet<>(); for (List<?> o : qx.executeTwoStepQuery("partitioned", "select p.name, avg(f.price), min(f.price), max(f.price), sum(f.price), count(*), " + @@ -164,7 +166,61 @@ public class GridCacheCrossCacheQuerySelfTest extends GridCommonAbstractTest { "where p.id = f.productId " + "group by f.productId, p.name").get()) { X.println("___ -> " + o); + + assertTrue(names.add((String)o.get(0))); + assertEquals(i(o, 4), i(o, 2) + i(o, 3)); } + + X.println("___ SUM HAVING"); + + for (List<?> o : qx.executeTwoStepQuery("partitioned", + "select p.name, sum(f.price) s " + + "from FactPurchase f, \"replicated\".DimProduct p " + + "where p.id = f.productId " + + "group by f.productId, p.name " + + "having s >= 15").get()) { + X.println("___ -> " + o); + + assertTrue(i(o, 1) >= 15); + } + + X.println("___ DISTINCT ORDER BY TOP"); + + int top = 6; + + for (List<?> o : qx.executeTwoStepQuery("partitioned", + "select top 3 distinct productId " + + "from FactPurchase f " + + "order by productId desc ").get()) { + X.println("___ -> " + o); + + assertEquals(top--, o.get(0)); + } + + X.println("___ DISTINCT ORDER BY OFFSET LIMIT"); + + top = 5; + + for (List<?> o : qx.executeTwoStepQuery("partitioned", + "select distinct productId " + + "from FactPurchase f " + + "order by productId desc " + + "limit 2 offset 1").get()) { + X.println("___ -> " + o); + + assertEquals(top--, o.get(0)); + } + + assertEquals(3, top); + } + + /** + * @param l List. + * @param idx Index. + * @return Int. + */ + private static int i(List<?> l, int idx){ + return ((Number)l.get(idx)).intValue(); } /** @throws Exception If failed. */ http://git-wip-us.apache.org/repos/asf/incubator-ignite/blob/fb4a74c3/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryParsingTest.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryParsingTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryParsingTest.java index d31ec34..49ce6d3 100644 --- a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryParsingTest.java +++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/sql/GridQueryParsingTest.java @@ -113,6 +113,7 @@ public class GridQueryParsingTest extends GridCommonAbstractTest { checkQuery("select p.*, street from Person p, Address a"); checkQuery("select p.name, a.street from Person p, Address a"); checkQuery("select distinct p.name, a.street from Person p, Address a"); + checkQuery("select distinct name, street from Person, Address group by old"); checkQuery("select distinct name, street from Person, Address"); checkQuery("select p1.name, a2.street from Person p1, Address a1, Person p2, Address a2");
