Repository: ignite Updated Branches: refs/heads/ignite-3477-master c2d8bd9a4 -> c449d6e93
IGNITE-4524: Index direct lookup for SQL MIN() and MAX() #1749 - Fixes #1749. Signed-off-by: Sergi Vladykin <[email protected]> Project: http://git-wip-us.apache.org/repos/asf/ignite/repo Commit: http://git-wip-us.apache.org/repos/asf/ignite/commit/035b0fa8 Tree: http://git-wip-us.apache.org/repos/asf/ignite/tree/035b0fa8 Diff: http://git-wip-us.apache.org/repos/asf/ignite/diff/035b0fa8 Branch: refs/heads/ignite-3477-master Commit: 035b0fa8712489b0ba86bd612611f258c2e85f5d Parents: c2d8bd9 Author: skalashnikov <[email protected]> Authored: Mon Apr 10 10:19:03 2017 +0300 Committer: Sergi Vladykin <[email protected]> Committed: Mon Apr 10 10:19:03 2017 +0300 ---------------------------------------------------------------------- .../cache/database/tree/BPlusTree.java | 103 ++++- .../apache/ignite/internal/util/IgniteTree.java | 14 + .../processors/database/BPlusTreeSelfTest.java | 28 +- .../query/h2/database/H2TreeIndex.java | 16 +- .../query/h2/opt/GridH2TreeIndex.java | 29 +- .../query/h2/IgniteSqlQueryMinMaxTest.java | 376 +++++++++++++++++++ .../query/h2/opt/GridH2TableSelfTest.java | 50 +++ .../IgniteCacheQuerySelfTestSuite.java | 2 + 8 files changed, 603 insertions(+), 15 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/ignite/blob/035b0fa8/modules/core/src/main/java/org/apache/ignite/internal/processors/cache/database/tree/BPlusTree.java ---------------------------------------------------------------------- diff --git a/modules/core/src/main/java/org/apache/ignite/internal/processors/cache/database/tree/BPlusTree.java b/modules/core/src/main/java/org/apache/ignite/internal/processors/cache/database/tree/BPlusTree.java index f3d020b..e5fd0ab 100644 --- a/modules/core/src/main/java/org/apache/ignite/internal/processors/cache/database/tree/BPlusTree.java +++ b/modules/core/src/main/java/org/apache/ignite/internal/processors/cache/database/tree/BPlusTree.java @@ -251,7 +251,13 @@ public abstract class BPlusTree<L, T extends L> extends DataStructure implements g.backId(0L); // Usually we'll go left down and don't need it. int cnt = io.getCount(pageAddr); - int idx = findInsertionPoint(io, pageAddr, 0, cnt, g.row, g.shift); + + int idx; + if (g.findLast) + idx = io.isLeaf()? cnt - 1: -cnt - 1; //(-cnt - 1) mimics not_found result of findInsertionPoint + //in case of cnt = 0 we end up in 'not found' branch below with idx being 0 after fix() adjustment + else + idx = findInsertionPoint(io, pageAddr, 0, cnt, g.row, g.shift); boolean found = idx >= 0; @@ -949,6 +955,76 @@ public abstract class BPlusTree<L, T extends L> extends DataStructure implements } } + /** {@inheritDoc} */ + @Override public T findFirst() throws IgniteCheckedException { + checkDestroyed(); + + try { + long firstPageId; + + long metaPage = acquirePage(metaPageId); + try { + firstPageId = getFirstPageId(metaPageId, metaPage, 0); + } + finally { + releasePage(metaPageId, metaPage); + } + + long page = acquirePage(firstPageId); + try { + long pageAddr = readLock(firstPageId, page); + + try { + BPlusIO<L> io = io(pageAddr); + + int cnt = io.getCount(pageAddr); + + if (cnt == 0) + return null; + + return getRow(io, pageAddr, 0); + } + finally { + readUnlock(firstPageId, page, pageAddr); + } + } + finally { + releasePage(firstPageId, page); + } + } + catch (IgniteCheckedException e) { + throw new IgniteCheckedException("Runtime failure on first row lookup", e); + } + catch (RuntimeException e) { + throw new IgniteException("Runtime failure on first row lookup", e); + } + catch (AssertionError e) { + throw new AssertionError("Assertion error on first row lookup", e); + } + } + + /** {@inheritDoc} */ + @SuppressWarnings("unchecked") + @Override public T findLast() throws IgniteCheckedException { + checkDestroyed(); + + try { + GetOne g = new GetOne(null, null, true); + doFind(g); + + return (T)g.row; + } + catch (IgniteCheckedException e) { + throw new IgniteCheckedException("Runtime failure on last row lookup", e); + } + catch (RuntimeException e) { + throw new IgniteException("Runtime failure on last row lookup", e); + } + catch (AssertionError e) { + throw new AssertionError("Assertion error on last row lookup", e); + } + } + /** * @param row Lookup row for exact match. * @param x Implementation specific argument, {@code null} always means that we need to return full detached data row. @@ -960,7 +1036,7 @@ public abstract class BPlusTree<L, T extends L> extends DataStructure implements checkDestroyed(); try { - GetOne g = new GetOne(row, x); + GetOne g = new GetOne(row, x, false); doFind(g); @@ -2251,13 +2327,18 @@ public abstract class BPlusTree<L, T extends L> extends DataStructure implements /** If this operation is a part of invoke. */ Invoke invoke; + /** Ignore row passed, find last row */ + boolean findLast; + /** * @param row Row. + * @param findLast find last row. */ - Get(L row) { - assert row != null; + Get(L row, boolean findLast) { + assert findLast ^ row != null; this.row = row; + this.findLast = findLast; } /** @@ -2270,6 +2351,7 @@ public abstract class BPlusTree<L, T extends L> extends DataStructure implements fwdId = g.fwdId; backId = g.backId; shift = g.shift; + findLast = g.findLast; } /** @@ -2372,9 +2454,10 @@ public abstract class BPlusTree<L, T extends L> extends DataStructure implements /** * @param row Row. * @param x Implementation specific argument. + * @param findLast Ignore row passed, find last row */ - private GetOne(L row, Object x) { - super(row); + private GetOne(L row, Object x, boolean findLast) { + super(row, findLast); this.x = x; } @@ -2405,7 +2488,7 @@ public abstract class BPlusTree<L, T extends L> extends DataStructure implements * @param cursor Cursor. */ GetCursor(L lower, int shift, ForwardCursor cursor) { - super(lower); + super(lower, false); assert shift != 0; // Either handle range of equal rows or find a greater row after concurrent merge. @@ -2468,7 +2551,7 @@ public abstract class BPlusTree<L, T extends L> extends DataStructure implements * @param needOld {@code True} If need return old value. */ private Put(T row, boolean needOld) { - super(row); + super(row, false); this.needOld = needOld; } @@ -2789,7 +2872,7 @@ public abstract class BPlusTree<L, T extends L> extends DataStructure implements * @param clo Closure. */ private Invoke(L row, Object x, final InvokeClosure<T> clo) { - super(row); + super(row, false); assert clo != null; @@ -3089,7 +3172,7 @@ public abstract class BPlusTree<L, T extends L> extends DataStructure implements * @param needOld {@code True} If need return old value. */ private Remove(L row, boolean needOld) { - super(row); + super(row, false); this.needOld = needOld; } http://git-wip-us.apache.org/repos/asf/ignite/blob/035b0fa8/modules/core/src/main/java/org/apache/ignite/internal/util/IgniteTree.java ---------------------------------------------------------------------- diff --git a/modules/core/src/main/java/org/apache/ignite/internal/util/IgniteTree.java b/modules/core/src/main/java/org/apache/ignite/internal/util/IgniteTree.java index 7eae0d5..396b8a4 100644 --- a/modules/core/src/main/java/org/apache/ignite/internal/util/IgniteTree.java +++ b/modules/core/src/main/java/org/apache/ignite/internal/util/IgniteTree.java @@ -64,6 +64,20 @@ public interface IgniteTree<L, T> { public GridCursor<T> find(L lower, L upper) throws IgniteCheckedException; /** + * Returns a value mapped to the lowest key, or {@code null} if tree is empty + * @return Value. + * @throws IgniteCheckedException If failed. + */ + public T findFirst() throws IgniteCheckedException; + + /** + * Returns a value mapped to the greatest key, or {@code null} if tree is empty + * @return Value. + * @throws IgniteCheckedException If failed. + */ + public T findLast() throws IgniteCheckedException; + + /** * Removes the mapping for a key from this tree if it is present. * * @param key Key whose mapping is to be removed from the tree. http://git-wip-us.apache.org/repos/asf/ignite/blob/035b0fa8/modules/core/src/test/java/org/apache/ignite/internal/processors/database/BPlusTreeSelfTest.java ---------------------------------------------------------------------- diff --git a/modules/core/src/test/java/org/apache/ignite/internal/processors/database/BPlusTreeSelfTest.java b/modules/core/src/test/java/org/apache/ignite/internal/processors/database/BPlusTreeSelfTest.java index 9e5ca70..dd89406 100644 --- a/modules/core/src/test/java/org/apache/ignite/internal/processors/database/BPlusTreeSelfTest.java +++ b/modules/core/src/test/java/org/apache/ignite/internal/processors/database/BPlusTreeSelfTest.java @@ -121,7 +121,7 @@ public class BPlusTreeSelfTest extends GridCommonAbstractTest { protected void assertNoLocks() { assertTrue(TestTree.checkNoLocks()); } - + /** {@inheritDoc} */ @Override protected void beforeTest() throws Exception { long seed = System.nanoTime(); @@ -1198,6 +1198,32 @@ public class BPlusTreeSelfTest extends GridCommonAbstractTest { } /** + * @throws IgniteCheckedException If failed. + */ + public void testFindFirstAndLast() throws IgniteCheckedException { + MAX_PER_PAGE = 5; + + TestTree tree = createTestTree(true); + + Long first = tree.findFirst(); + assertNull(first); + + Long last = tree.findLast(); + assertNull(last); + + for (long idx = 1L; idx <= 10L; ++idx) + tree.put(idx); + + first = tree.findFirst(); + assertEquals((Long)1L, first); + + last = tree.findLast(); + assertEquals((Long)10L, last); + + assertNoLocks(); + } + + /** * @param canGetRow Can get row from inner page. * @throws Exception If failed. */ http://git-wip-us.apache.org/repos/asf/ignite/blob/035b0fa8/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/database/H2TreeIndex.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/database/H2TreeIndex.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/database/H2TreeIndex.java index 51a997b..1ea3204 100644 --- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/database/H2TreeIndex.java +++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/database/H2TreeIndex.java @@ -39,6 +39,7 @@ import org.apache.ignite.spi.indexing.IndexingQueryFilter; import org.h2.engine.Session; import org.h2.index.Cursor; import org.h2.index.IndexType; +import org.h2.index.SingleRowCursor; import org.h2.message.DbException; import org.h2.result.SearchRow; import org.h2.result.SortOrder; @@ -300,12 +301,23 @@ public class H2TreeIndex extends GridH2IndexBase { /** {@inheritDoc} */ @Override public boolean canGetFirstOrLast() { - return false; + return true; } /** {@inheritDoc} */ @Override public Cursor findFirstOrLast(Session session, boolean b) { - throw new UnsupportedOperationException(); + try { + int seg = threadLocalSegment(); + + H2Tree tree = treeForRead(seg); + + GridH2Row row = b ? tree.findFirst(): tree.findLast(); + + return new SingleRowCursor(row); + } + catch (IgniteCheckedException e) { + throw DbException.convert(e); + } } /** {@inheritDoc} */ http://git-wip-us.apache.org/repos/asf/ignite/blob/035b0fa8/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/opt/GridH2TreeIndex.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/opt/GridH2TreeIndex.java b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/opt/GridH2TreeIndex.java index d28b99e..4a12c78 100644 --- a/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/opt/GridH2TreeIndex.java +++ b/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/opt/GridH2TreeIndex.java @@ -20,6 +20,7 @@ package org.apache.ignite.internal.processors.query.h2.opt; import java.util.Collection; import java.util.Comparator; import java.util.List; +import java.util.Map; import java.util.NavigableMap; import java.util.concurrent.ConcurrentSkipListMap; import org.apache.ignite.IgniteCheckedException; @@ -35,6 +36,7 @@ import org.apache.ignite.spi.indexing.IndexingQueryFilter; import org.h2.engine.Session; import org.h2.index.Cursor; import org.h2.index.IndexType; +import org.h2.index.SingleRowCursor; import org.h2.message.DbException; import org.h2.result.SearchRow; import org.h2.result.SortOrder; @@ -380,12 +382,23 @@ public class GridH2TreeIndex extends GridH2IndexBase implements Comparator<GridS /** {@inheritDoc} */ @Override public boolean canGetFirstOrLast() { - return false; + return true; } /** {@inheritDoc} */ @Override public Cursor findFirstOrLast(Session ses, boolean first) { - throw DbException.throwInternalError(); + try { + int seg = threadLocalSegment(); + + IgniteTree t = treeForRead(seg); + + GridH2Row row = (GridH2Row)(first ? t.findFirst() : t.findLast()); + + return new SingleRowCursor(row); + } + catch (IgniteCheckedException e) { + throw DbException.convert(e); + } } /** {@inheritDoc} */ @@ -548,6 +561,18 @@ public class GridH2TreeIndex extends GridH2IndexBase implements Comparator<GridS } /** {@inheritDoc} */ + @Override public GridH2Row findFirst() throws IgniteCheckedException { + Map.Entry<GridSearchRowPointer, GridH2Row> first = tree.firstEntry(); + return (first == null) ? null : first.getValue(); + } + + /** {@inheritDoc} */ + @Override public GridH2Row findLast() throws IgniteCheckedException { + Map.Entry<GridSearchRowPointer, GridH2Row> last = tree.lastEntry(); + return (last == null) ? null : last.getValue(); + } + + /** {@inheritDoc} */ @Override public GridH2Row remove(GridSearchRowPointer key) { return tree.remove(key); } http://git-wip-us.apache.org/repos/asf/ignite/blob/035b0fa8/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/IgniteSqlQueryMinMaxTest.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/IgniteSqlQueryMinMaxTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/IgniteSqlQueryMinMaxTest.java new file mode 100644 index 0000000..0f50d7e --- /dev/null +++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/IgniteSqlQueryMinMaxTest.java @@ -0,0 +1,376 @@ +/* + * 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; + +import org.apache.ignite.Ignite; +import org.apache.ignite.IgniteCache; +import org.apache.ignite.cache.query.QueryCursor; +import org.apache.ignite.cache.query.SqlFieldsQuery; +import org.apache.ignite.cache.query.annotations.QuerySqlField; +import org.apache.ignite.configuration.CacheConfiguration; +import org.apache.ignite.configuration.IgniteConfiguration; +import org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi; +import org.apache.ignite.spi.discovery.tcp.ipfinder.vm.TcpDiscoveryVmIpFinder; +import org.apache.ignite.testframework.junits.common.GridCommonAbstractTest; + +import java.util.List; + +/** Test for SQL min() and max() optimization */ +public class IgniteSqlQueryMinMaxTest extends GridCommonAbstractTest { + /** IP finder. */ + private static final TcpDiscoveryVmIpFinder IP_FINDER = new TcpDiscoveryVmIpFinder(true); + + /** Name of the cache for test */ + private static final String CACHE_NAME = "intCache"; + + /** Name of the second test cache */ + private static final String CACHE_NAME_2 = "valCache"; + + /** {@inheritDoc} */ + @Override protected void beforeTest() throws Exception { + super.beforeTest(); + + startGrids(4); + } + + /** {@inheritDoc} */ + @Override protected void afterTest() throws Exception { + super.afterTest(); + + stopAllGrids(); + } + + /** {@inheritDoc} */ + @Override protected IgniteConfiguration getConfiguration(String gridName) throws Exception { + IgniteConfiguration cfg = super.getConfiguration(gridName); + + TcpDiscoverySpi spi = (TcpDiscoverySpi)cfg.getDiscoverySpi(); + + spi.setIpFinder(IP_FINDER); + + CacheConfiguration<?, ?> ccfg = new CacheConfiguration<>(); + ccfg.setIndexedTypes(Integer.class, Integer.class); + ccfg.setName(CACHE_NAME); + + CacheConfiguration<?, ?> ccfg2 = new CacheConfiguration<>(); + ccfg2.setIndexedTypes(Integer.class, ValueObj.class); + ccfg2.setName(CACHE_NAME_2); + + cfg.setCacheConfiguration(ccfg, ccfg2); + + if ("client".equals(gridName)) + cfg.setClientMode(true); + + return cfg; + } + + /** Check min() and max() functions in queries */ + public void testQueryMinMax() throws Exception { + try (Ignite client = startGrid("client")) { + IgniteCache<Integer, ValueObj> cache = client.cache(CACHE_NAME_2); + + int count = 1_000; + for (int idx = 0; idx < count; ++idx) + cache.put(idx, new ValueObj(count - idx - 1, 0)); + + long start = System.currentTimeMillis(); + QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery("select min(_key), max(_key) from ValueObj")); + List<List<?>> result = cursor.getAll(); + assertEquals(1, result.size()); + assertEquals(0, result.get(0).get(0)); + assertEquals(count - 1, result.get(0).get(1)); + if (log.isDebugEnabled()) + log.debug("Elapsed(1): " + (System.currentTimeMillis() - start)); + + start = System.currentTimeMillis(); + cursor = cache.query(new SqlFieldsQuery("select min(idxVal), max(idxVal) from ValueObj")); + result = cursor.getAll(); + assertEquals(1, result.size()); + assertEquals(0, result.get(0).get(0)); + assertEquals(count - 1, result.get(0).get(1)); + if (log.isDebugEnabled()) + log.debug("Elapsed(2): " + (System.currentTimeMillis() - start)); + + start = System.currentTimeMillis(); + cursor = cache.query(new SqlFieldsQuery("select min(nonIdxVal), max(nonIdxVal) from ValueObj")); + result = cursor.getAll(); + assertEquals(1, result.size()); + assertEquals(0, result.get(0).get(0)); + assertEquals(count - 1, result.get(0).get(1)); + if (log.isDebugEnabled()) + log.debug("Elapsed(3): " + (System.currentTimeMillis() - start)); + } + } + + /** Check min() and max() on empty cache */ + public void testQueryMinMaxEmptyCache() throws Exception { + try (Ignite client = startGrid("client")) { + IgniteCache<Integer, ValueObj> cache = client.cache(CACHE_NAME_2); + + QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery("select min(idxVal), max(idxVal) from ValueObj")); + List<List<?>> result = cursor.getAll(); + assertEquals(1, result.size()); + assertEquals(2, result.get(0).size()); + assertNull(result.get(0).get(0)); + assertNull(result.get(0).get(1)); + } + } + + /** + * Check min() and max() over _key use correct index + * Test uses value object cache + */ + public void testMinMaxQueryPlanOnKey() throws Exception { + try (Ignite client = startGrid("client")) { + IgniteCache<Integer, ValueObj> cache = client.cache(CACHE_NAME_2); + + QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery("explain select min(_key), max(_key) from ValueObj")); + List<List<?>> result = cursor.getAll(); + assertEquals(2, result.size()); + assertTrue(((String) result.get(0).get(0)).contains("_key_PK")); + assertTrue(((String) result.get(0).get(0)).contains("direct lookup")); + } + } + + /** + * Check min() and max() over value fields use correct index. + * Test uses value object cache + */ + public void testMinMaxQueryPlanOnFields() throws Exception { + try (Ignite client = startGrid("client")) { + IgniteCache<Integer, ValueObj> cache = client.cache(CACHE_NAME_2); + + QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery("explain select min(idxVal), max(idxVal) from ValueObj")); + List<List<?>> result = cursor.getAll(); + assertEquals(2, result.size()); + assertTrue(((String)result.get(0).get(0)).contains("idxVal_idx")); + assertTrue(((String)result.get(0).get(0)).contains("direct lookup")); + } + } + + /** + * Check min() and max() over _key uses correct index + * Test uses primitive cache + */ + public void testSimpleMinMaxQueryPlanOnKey() throws Exception { + try (Ignite client = startGrid("client")) { + IgniteCache<Integer, Integer> cache = client.cache(CACHE_NAME); + + QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery("explain select min(_key), max(_key) from Integer")); + List<List<?>> result = cursor.getAll(); + assertEquals(2, result.size()); + assertTrue(((String)result.get(0).get(0)).contains("_key_PK")); + assertTrue(((String)result.get(0).get(0)).contains("direct lookup")); + } + } + + /** + * Check min() and max() over _val uses correct index. + * Test uses primitive cache + */ + public void testSimpleMinMaxQueryPlanOnValue() throws Exception { + try (Ignite client = startGrid("client")) { + IgniteCache<Integer, Integer> cache = client.cache(CACHE_NAME); + + QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery("explain select min(_val), max(_val) from Integer")); + List<List<?>> result = cursor.getAll(); + assertEquals(2, result.size()); + assertTrue(((String)result.get(0).get(0)).contains("_val_idx")); + assertTrue(((String)result.get(0).get(0)).contains("direct lookup")); + } + } + + /** Check min() and max() over group */ + public void testGroupMinMax() throws Exception { + try (Ignite client = startGrid("client")) { + IgniteCache<Integer, ValueObj> cache = client.cache(CACHE_NAME_2); + + int count = 1_000; + int groupSize = 100; + for (int idx = 0; idx < count; ++idx) + cache.put(idx, new ValueObj(count - idx - 1, groupSize)); + + QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery( + "select groupVal, min(idxVal), max(idxVal), min(nonIdxVal), max(nonIdxVal) " + + " from ValueObj group by groupVal order by groupVal")); + List<List<?>> result = cursor.getAll(); + + assertEquals(count / groupSize, result.size()); + + for (int idx = 0; idx < result.size(); ++idx) { + assertEquals(idx, result.get(idx).get(0));//groupVal + int min = idx * groupSize; + int max = (idx + 1) * groupSize - 1; + assertEquals(min, result.get(idx).get(1));//min(idxVal) + assertEquals(max, result.get(idx).get(2));//max(idxVal) + assertEquals(min, result.get(idx).get(3));//min(nonIdxVal) + assertEquals(max, result.get(idx).get(4));//max(nonIdxVal) + } + } + } + + /** Check min() and max() over group with having clause */ + public void testGroupHavingMinMax() throws Exception { + try (Ignite client = startGrid("client")) { + IgniteCache<Integer, ValueObj> cache = client.cache(CACHE_NAME_2); + + int count = 1_000; + int groupSize = 100; + for (int idx = 0; idx < count; ++idx) + cache.put(idx, new ValueObj(count - idx - 1, groupSize)); + + QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery( + "select groupVal, min(idxVal), max(idxVal), min(nonIdxVal), max(nonIdxVal) " + + "from ValueObj group by groupVal having min(idxVal) = ?" ).setArgs(0)); + + List<List<?>> result = cursor.getAll(); + assertEquals(1, result.size()); + assertEquals(0, result.get(0).get(0));//groupVal + assertEquals(0, result.get(0).get(1));//min(idxVal) + assertEquals(groupSize - 1, result.get(0).get(2));//max(idxVal) + assertEquals(0, result.get(0).get(3));//min(nonIdxVal) + assertEquals(groupSize - 1, result.get(0).get(4));//max(nonIdxVal) + + cursor = cache.query(new SqlFieldsQuery( + "select groupVal, min(idxVal), max(idxVal), min(nonIdxVal), max(nonIdxVal) " + + "from ValueObj group by groupVal having max(idxVal) = ?" ).setArgs(count - 1)); + + result = cursor.getAll(); + assertEquals(1, result.size()); + assertEquals((count - 1)/groupSize, result.get(0).get(0));//groupVal + assertEquals(count - groupSize, result.get(0).get(1));//min(idxVal) + assertEquals(count - 1, result.get(0).get(2));//max(idxVal) + assertEquals(count - groupSize, result.get(0).get(3));//min(nonIdxVal) + assertEquals(count - 1, result.get(0).get(4));//max(nonIdxVal) + } + } + + /** Check min() and max() over group with joins */ + public void testJoinGroupMinMax() throws Exception { + try (Ignite client = startGrid("client")) { + IgniteCache<Integer, Integer> cache = client.cache(CACHE_NAME); + IgniteCache<Integer, ValueObj> cache2 = client.cache(CACHE_NAME_2); + + int count = 1_000; + int groupSize = 100; + for (int idx = 0; idx < count; ++idx) { + cache.put(idx, idx); + cache2.put(idx, new ValueObj(count - idx - 1, groupSize)); + } + + //join a.key = b.key, collocated + QueryCursor<List<?>> cursor = cache.query( + new SqlFieldsQuery("select b.groupVal, min(a._key), max(a._key), min(a._val), max(a._val), " + + "min(b._key), max(b._key), min(b.idxVal), max(b.idxVal), min(b.nonIdxVal), max(b.nonIdxVal) " + + "from \"intCache\".Integer a, \"valCache\".ValueObj b where a._key = b._key " + + "group by b.groupVal order by b.groupVal")); + + List<List<?>> result = cursor.getAll(); + assertEquals(count / groupSize, result.size()); + for (int idx = 0; idx < result.size(); ++idx) { + assertEquals(idx, result.get(idx).get(0)); + int min = idx * groupSize; + int max = (idx + 1) * groupSize - 1; + int revMin = count - max - 1; + int revMax = count - min - 1; + + assertEquals(revMin, result.get(idx).get(1));//min(a._key) + assertEquals(revMax, result.get(idx).get(2));//max(a._key) + assertEquals(revMin, result.get(idx).get(3));//min(a._val) + assertEquals(revMax, result.get(idx).get(4));//max(a._val) + assertEquals(revMin, result.get(idx).get(5));//min(b._key) + assertEquals(revMax, result.get(idx).get(6));//max(b_key) + assertEquals(min, result.get(idx).get(7));//min(b.idxVal) + assertEquals(max, result.get(idx).get(8));//max(b.idxVal), + assertEquals(min, result.get(idx).get(9));//min(b.nonIdxVal) + assertEquals(max, result.get(idx).get(10));//max(b.nonIdxVal) + } + + //join a.key = b.val, non-collocated + cursor = cache.query( + new SqlFieldsQuery("select b.groupVal, min(a._key), max(a._key), min(a._val), max(a._val), " + + "min(b._key), max(b._key), min(b.idxVal), max(b.idxVal), min(b.nonIdxVal), max(b.nonIdxVal) " + + "from \"intCache\".Integer a, \"valCache\".ValueObj b where a._key = b.idxVal " + + "group by b.groupVal order by b.groupVal") + .setDistributedJoins(true)); + + result = cursor.getAll(); + + assertEquals(count / groupSize, result.size()); + for (int idx = 0; idx < result.size(); ++idx) { + assertEquals(idx, result.get(idx).get(0)); + int min = idx * groupSize; + int max = (idx + 1) * groupSize - 1; + int revMin = count - max - 1; + int revMax = count - min - 1; + + assertEquals(min, result.get(idx).get(1));//min(a._key) + assertEquals(max, result.get(idx).get(2));//max(a._key) + assertEquals(min, result.get(idx).get(3));//min(a._val) + assertEquals(max, result.get(idx).get(4));//max(a._val) + assertEquals(revMin, result.get(idx).get(5));//min(b._key) + assertEquals(revMax, result.get(idx).get(6));//max(b_key) + assertEquals(min, result.get(idx).get(7));//min(b.idxVal) + assertEquals(max, result.get(idx).get(8));//max(b.idxVal), + assertEquals(min, result.get(idx).get(9));//min(b.nonIdxVal) + assertEquals(max, result.get(idx).get(10));//max(b.nonIdxVal) + } + } + } + + /** Value object for test cache */ + public class ValueObj { + /** */ + @QuerySqlField(index = true) + private final int idxVal; + + /** */ + @QuerySqlField + private final int nonIdxVal; + + /** used for grouping */ + @QuerySqlField + private final int groupVal; + + /** */ + public ValueObj(int v, int g) { + this.idxVal = v; + this.nonIdxVal = v; + this.groupVal = (g == 0) ? v : v / g; + } + + /** {@inheritDoc} */ + @Override public int hashCode() { + return idxVal; + } + + /** {@inheritDoc} */ + @Override public boolean equals(Object o) { + if (this == o) + return true; + + if (!(o instanceof ValueObj)) + return false; + + ValueObj other = (ValueObj)o; + return idxVal == other.idxVal && + nonIdxVal == other.nonIdxVal && + groupVal == other.groupVal; + } + } +} http://git-wip-us.apache.org/repos/asf/ignite/blob/035b0fa8/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/opt/GridH2TableSelfTest.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/opt/GridH2TableSelfTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/opt/GridH2TableSelfTest.java index f9388ef..5dd3f65 100644 --- a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/opt/GridH2TableSelfTest.java +++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/opt/GridH2TableSelfTest.java @@ -39,6 +39,7 @@ import org.apache.ignite.internal.util.lang.GridCursor; import org.apache.ignite.internal.util.typedef.F; import org.apache.ignite.testframework.junits.common.GridCommonAbstractTest; import org.h2.Driver; +import org.h2.index.Cursor; import org.h2.index.Index; import org.h2.result.Row; import org.h2.result.SearchRow; @@ -509,6 +510,55 @@ public class GridH2TableSelfTest extends GridCommonAbstractTest { assertEquals(ids.length - deleted.get(), rs.getInt(1)); } + + /** + * @throws Exception If failed. + */ + public void testIndexFindFirstOrLast() throws Exception { + Index index = tbl.getIndexes().get(2); + assertTrue(index instanceof GridH2TreeIndex); + assertTrue(index.canGetFirstOrLast()); + + //find first on empty data + Cursor cursor = index.findFirstOrLast(null, true); + assertFalse(cursor.next()); + assertNull(cursor.get()); + + //find last on empty data + cursor = index.findFirstOrLast(null, false); + assertFalse(cursor.next()); + assertNull(cursor.get()); + + //fill with data + int rows = 100; + long t = System.currentTimeMillis(); + Random rnd = new Random(); + UUID min = null; + UUID max = null; + + for (int i = 0 ; i < rows; i++) { + UUID id = UUID.randomUUID(); + if (min == null || id.compareTo(min) < 0) + min = id; + if (max == null || id.compareTo(max) > 0) + max = id; + GridH2Row row = row(id, t++, id.toString(), rnd.nextInt(100)); + ((GridH2TreeIndex)index).put(row); + } + + //find first + cursor = index.findFirstOrLast(null, true); + assertTrue(cursor.next()); + assertEquals(min, cursor.get().getValue(0).getObject()); + assertFalse(cursor.next()); + + //find last + cursor = index.findFirstOrLast(null, false); + assertTrue(cursor.next()); + assertEquals(max, cursor.get().getValue(0).getObject()); + assertFalse(cursor.next()); + } + /** * Check query plan to correctly select index. * http://git-wip-us.apache.org/repos/asf/ignite/blob/035b0fa8/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteCacheQuerySelfTestSuite.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteCacheQuerySelfTestSuite.java b/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteCacheQuerySelfTestSuite.java index adfc50e..2ce4072 100644 --- a/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteCacheQuerySelfTestSuite.java +++ b/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteCacheQuerySelfTestSuite.java @@ -98,6 +98,7 @@ import org.apache.ignite.internal.processors.query.IgniteSqlSegmentedIndexSelfTe import org.apache.ignite.internal.processors.query.IgniteSqlSplitterSelfTest; import org.apache.ignite.internal.processors.query.h2.GridH2IndexingInMemSelfTest; import org.apache.ignite.internal.processors.query.h2.GridH2IndexingOffheapSelfTest; +import org.apache.ignite.internal.processors.query.h2.IgniteSqlQueryMinMaxTest; import org.apache.ignite.internal.processors.query.h2.opt.GridH2TableSelfTest; import org.apache.ignite.internal.processors.query.h2.sql.BaseH2CompareQueryTest; import org.apache.ignite.internal.processors.query.h2.sql.GridQueryParsingTest; @@ -184,6 +185,7 @@ public class IgniteCacheQuerySelfTestSuite extends TestSuite { suite.addTestSuite(IndexingSpiQueryTxSelfTest.class); suite.addTestSuite(IgniteCacheMultipleIndexedTypesTest.class); + suite.addTestSuite(IgniteSqlQueryMinMaxTest.class); // Fields queries. suite.addTestSuite(SqlFieldsQuerySelfTest.class);
