IGNITE-9632: SQL: support IN statement with constants/params for partition pruning. This closes #4857.
Project: http://git-wip-us.apache.org/repos/asf/ignite/repo Commit: http://git-wip-us.apache.org/repos/asf/ignite/commit/d669da15 Tree: http://git-wip-us.apache.org/repos/asf/ignite/tree/d669da15 Diff: http://git-wip-us.apache.org/repos/asf/ignite/diff/d669da15 Branch: refs/heads/ignite-9720 Commit: d669da154f79e48a833a754a59606b65159406c3 Parents: d76ff54 Author: devozerov <voze...@gridgain.com> Authored: Tue Oct 23 15:15:23 2018 +0300 Committer: devozerov <voze...@gridgain.com> Committed: Tue Oct 23 15:15:23 2018 +0300 ---------------------------------------------------------------------- .../query/h2/sql/GridSqlQuerySplitter.java | 137 +++++++++++-- .../InOperationExtractPartitionSelfTest.java | 201 +++++++++++++++++++ .../query/h2/twostep/JoinSqlTestHelper.java | 9 +- .../IgniteCacheQuerySelfTestSuite2.java | 3 + 4 files changed, 331 insertions(+), 19 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/ignite/blob/d669da15/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 ca9c5bb..b19dd14 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 @@ -53,6 +53,7 @@ import org.apache.ignite.internal.util.typedef.internal.S; import org.h2.command.Prepared; import org.h2.command.dml.Query; import org.h2.command.dml.SelectUnion; +import org.h2.table.Column; import org.h2.table.IndexColumn; import org.h2.value.Value; import org.jetbrains.annotations.Nullable; @@ -2342,6 +2343,64 @@ public class GridSqlQuerySplitter { return null; } + case IN: { + // Operation should contain at least two children: left (column) and right (const or column). + if (op.size() < 2) + return null; + + // Left operand should be column. + GridSqlAst left = op.child(); + + GridSqlColumn leftCol; + + if (left instanceof GridSqlColumn) + leftCol = (GridSqlColumn)left; + else + return null; + + // Can work only with Ignite's tables. + if (!(leftCol.column().getTable() instanceof GridH2Table)) + return null; + + CacheQueryPartitionInfo[] res = new CacheQueryPartitionInfo[op.size() - 1]; + + for (int i = 1; i < op.size(); i++) { + GridSqlAst right = op.child(i); + + GridSqlConst rightConst; + GridSqlParameter rightParam; + + if (right instanceof GridSqlConst) { + rightConst = (GridSqlConst)right; + rightParam = null; + } + else if (right instanceof GridSqlParameter) { + rightConst = null; + rightParam = (GridSqlParameter)right; + } + else + // One of members of "IN" list is neither const, nor param, so we do no know it's partition. + // As this is disjunction, not knowing partition of a single element leads to unknown partition + // set globally. Hence, returning null. + return null; + + CacheQueryPartitionInfo cur = getCacheQueryPartitionInfo( + leftCol.column(), + rightConst, + rightParam, + ctx + ); + + // Same thing as above: single unknown partition in disjunction defeats optimization. + if (cur == null) + return null; + + res[i - 1] = cur; + } + + return res; + } + default: return null; } @@ -2362,39 +2421,85 @@ public class GridSqlQuerySplitter { GridSqlElement left = op.child(0); GridSqlElement right = op.child(1); - if (!(left instanceof GridSqlColumn)) + GridSqlColumn leftCol; + + if (left instanceof GridSqlColumn) + leftCol = (GridSqlColumn)left; + else return null; - if (!(right instanceof GridSqlConst) && !(right instanceof GridSqlParameter)) + if (!(leftCol.column().getTable() instanceof GridH2Table)) return null; - GridSqlColumn column = (GridSqlColumn)left; + GridSqlConst rightConst; + GridSqlParameter rightParam; - if (!(column.column().getTable() instanceof GridH2Table)) + if (right instanceof GridSqlConst) { + rightConst = (GridSqlConst)right; + rightParam = null; + } + else if (right instanceof GridSqlParameter) { + rightConst = null; + rightParam = (GridSqlParameter)right; + } + else return null; - GridH2Table tbl = (GridH2Table) column.column().getTable(); + return getCacheQueryPartitionInfo(leftCol.column(), rightConst, rightParam, ctx); + } + + /** + * Extracts the partition if possible + * @param leftCol Column on the lsft side. + * @param rightConst Constant on the right side. + * @param rightParam Parameter on the right side. + * @param ctx Kernal Context. + * @return partition info, or {@code null} if none identified + * @throws IgniteCheckedException If failed. + */ + @Nullable private static CacheQueryPartitionInfo getCacheQueryPartitionInfo( + Column leftCol, + GridSqlConst rightConst, + GridSqlParameter rightParam, + GridKernalContext ctx + ) throws IgniteCheckedException { + assert leftCol != null; + assert leftCol.getTable() != null; + assert leftCol.getTable() instanceof GridH2Table; + + GridH2Table tbl = (GridH2Table)leftCol.getTable(); GridH2RowDescriptor desc = tbl.rowDescriptor(); IndexColumn affKeyCol = tbl.getAffinityKeyColumn(); - int colId = column.column().getColumnId(); + int colId = leftCol.getColumnId(); if ((affKeyCol == null || colId != affKeyCol.column.getColumnId()) && !desc.isKeyColumn(colId)) return null; - if (right instanceof GridSqlConst) { - GridSqlConst constant = (GridSqlConst)right; - - return new CacheQueryPartitionInfo(ctx.affinity().partition(tbl.cacheName(), - constant.value().getObject()), null, null, -1, -1); + if (rightConst != null) { + int part = ctx.affinity().partition(tbl.cacheName(), rightConst.value().getObject()); + + return new CacheQueryPartitionInfo( + part, + null, + null, + -1, + -1 + ); + } + else if (rightParam != null) { + return new CacheQueryPartitionInfo( + -1, + tbl.cacheName(), + tbl.getName(), + leftCol.getType(), + rightParam.index() + ); } - - GridSqlParameter param = (GridSqlParameter) right; - - return new CacheQueryPartitionInfo(-1, tbl.cacheName(), tbl.getName(), - column.column().getType(), param.index()); + else + return null; } /** http://git-wip-us.apache.org/repos/asf/ignite/blob/d669da15/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/twostep/InOperationExtractPartitionSelfTest.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/twostep/InOperationExtractPartitionSelfTest.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/twostep/InOperationExtractPartitionSelfTest.java new file mode 100644 index 0000000..d27fc52 --- /dev/null +++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/twostep/InOperationExtractPartitionSelfTest.java @@ -0,0 +1,201 @@ +/* + * 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.twostep; + +import java.util.Arrays; +import java.util.Collections; +import java.util.List; +import java.util.concurrent.atomic.LongAdder; +import org.apache.ignite.IgniteCache; +import org.apache.ignite.IgniteException; +import org.apache.ignite.cache.CacheMode; +import org.apache.ignite.cache.query.FieldsQueryCursor; +import org.apache.ignite.cache.query.SqlFieldsQuery; +import org.apache.ignite.cluster.ClusterNode; +import org.apache.ignite.configuration.CacheConfiguration; +import org.apache.ignite.configuration.IgniteConfiguration; +import org.apache.ignite.internal.managers.communication.GridIoMessage; +import org.apache.ignite.internal.processors.query.h2.twostep.msg.GridH2QueryRequest; +import org.apache.ignite.lang.IgniteInClosure; +import org.apache.ignite.plugin.extensions.communication.Message; +import org.apache.ignite.spi.communication.tcp.TcpCommunicationSpi; +import org.apache.ignite.testframework.junits.common.GridCommonAbstractTest; + +import static org.apache.ignite.internal.processors.query.h2.twostep.JoinSqlTestHelper.ORG; +import static org.apache.ignite.internal.processors.query.h2.twostep.JoinSqlTestHelper.ORG_COUNT; + +/** */ +public class InOperationExtractPartitionSelfTest extends GridCommonAbstractTest { + /** */ + private static final int NODES_COUNT = 8; + + /** */ + private static IgniteCache<String, JoinSqlTestHelper.Organization> orgCache; + + /** */ + private static LongAdder cnt = new LongAdder(); + + /** {@inheritDoc} */ + @Override protected IgniteConfiguration getConfiguration(String gridName) throws Exception { + IgniteConfiguration cfg = super.getConfiguration(gridName); + + cfg.setCommunicationSpi(new TcpCommunicationSpi() { + /** {@inheritDoc} */ + @Override public void sendMessage(ClusterNode node, Message msg, IgniteInClosure<IgniteException> ackC) { + assert msg != null; + + if (GridIoMessage.class.isAssignableFrom(msg.getClass())) { + GridIoMessage gridMsg = (GridIoMessage)msg; + + if (GridH2QueryRequest.class.isAssignableFrom(gridMsg.message().getClass())) + cnt.increment(); + } + + super.sendMessage(node, msg, ackC); + } + }); + + return cfg; + } + + /** {@inheritDoc} */ + @Override protected void beforeTestsStarted() throws Exception { + startGridsMultiThreaded(NODES_COUNT, false); + + orgCache = ignite(0).getOrCreateCache(new CacheConfiguration<String, JoinSqlTestHelper.Organization>(ORG) + .setCacheMode(CacheMode.PARTITIONED) + .setIndexedTypes(String.class, JoinSqlTestHelper.Organization.class) + ); + + awaitPartitionMapExchange(); + + JoinSqlTestHelper.populateDataIntoOrg(orgCache); + + try (FieldsQueryCursor<List<?>> cur = orgCache.query(new SqlFieldsQuery( + "SELECT * FROM Organization org WHERE org.id = '" + ORG + 0 + "'"))) { + + assert cur != null; + + List<List<?>> rows = cur.getAll(); + + assert rows.size() == 1; + } + + try (FieldsQueryCursor<List<?>> cur = orgCache.query(new SqlFieldsQuery( + "SELECT * FROM Organization org WHERE org.id = ?").setArgs(ORG + 0))) { + + assert cur != null; + + List<List<?>> rows = cur.getAll(); + + assert rows.size() == 1; + } + } + + /** {@inheritDoc} */ + @Override protected void afterTestsStopped() throws Exception { + orgCache = null; + + stopAllGrids(); + } + + /** */ + public void testAlternativeUsageOfIn(){ + try (FieldsQueryCursor<List<?>> cur = orgCache.query(new SqlFieldsQuery( + "SELECT * FROM Organization org WHERE org._KEY IN (SELECT subOrg._KEY FROM Organization subOrg)"))) { + + assertNotNull(cur); + + List<List<?>> rows = cur.getAll(); + + assertEquals(ORG_COUNT, rows.size()); + } + } + + /** */ + public void testEmptyList() { + testInOperator(Collections.emptyList(), null, 0L, NODES_COUNT - 1); + } + + /** */ + public void testSingleValueList() { + testInOperator(Collections.singletonList(ORG + 0), null, 1L, 1); + testInOperator(Collections.singletonList(ORG + 1), null, 1L, 1); + testInOperator(Collections.singletonList(ORG + String.valueOf(ORG_COUNT - 1)), null, 1L, 1); + testInOperator(Collections.singletonList("ORG"), null, 0L, 1); + testInOperator(Collections.singletonList("?"), new String[] {ORG + 0}, 1L, 1); + testInOperator(Collections.singletonList("?"), new String[] {ORG + 2}, 1L, 1); + testInOperator(Collections.singletonList("?"), new String[] {ORG + String.valueOf(ORG_COUNT - 1)}, 1L, 1); + testInOperator(Collections.singletonList("?"), new String[] {"ORG"}, 0L, 1); + } + + /** */ + public void testMultipleValueList() { + testInOperator(Arrays.asList(ORG + 0, ORG + 3, ORG + String.valueOf(ORG_COUNT - 1)), null, 3, 3); + testInOperator(Arrays.asList("ORG", ORG + 0, ORG + 4, ORG + String.valueOf(ORG_COUNT - 1)), null, 3, 4); + testInOperator(Arrays.asList(ORG + 0, ORG + 5, ORG + String.valueOf(ORG_COUNT - 1), "ORG"), null, 3, 4); + testInOperator(Arrays.asList(ORG + 0, ORG + 6, "MID", ORG + String.valueOf(ORG_COUNT - 1), "ORG"), null, 3, 5); + + final List<String> allArgs3 = Arrays.asList("?", "?", "?"); + final List<String> allArgs4 = Arrays.asList("?", "?", "?", "?"); + + testInOperator(allArgs3, new String[] {ORG + 0, ORG + 7, ORG + String.valueOf(ORG_COUNT - 1)}, 3, 3); + testInOperator(allArgs4, new String[] {"ORG", ORG + 0, ORG + 8, ORG + String.valueOf(ORG_COUNT - 1)}, 3, 4); + testInOperator(allArgs4, new String[] {ORG + 0, ORG + 9, ORG + String.valueOf(ORG_COUNT - 1), "ORG"}, 3, 4); + testInOperator(allArgs4, new String[] {ORG + 0, "MID", ORG + String.valueOf(ORG_COUNT - 1), "ORG"}, 2, 4); + + testInOperator( + Arrays.asList("?", ORG + 9, ORG + String.valueOf(ORG_COUNT - 1), "?"), + new String[] {ORG + 0, "ORG"}, + 3, + 4 + ); + testInOperator( + Arrays.asList("?", "?", ORG + String.valueOf(ORG_COUNT - 1), "ORG"), + new String[] {ORG + 0, "MID"}, + 2, + 4 + ); + } + + /** + * + * @param cnst Constants and parameters('?'). + * @param args Values of parameters. + * @param expRes Expected result. + * @param maxReq Maximum number of requests to process query. + */ + private void testInOperator(List<String> cnst, Object[] args, long expRes, int maxReq) { + int curIdx = cnt.intValue(); + + String toIn = cnst.size() == 0 ? "" : String.valueOf("'" + String.join("','", cnst) + "'") + .replace("'?'", "?"); + + try (FieldsQueryCursor<List<?>> cur = orgCache.query(new SqlFieldsQuery( + "SELECT * FROM Organization org WHERE org._KEY IN (" + toIn + ")").setArgs(args))) { + + assertNotNull(cur); + + List<List<?>> rows = cur.getAll(); + + assertEquals(expRes, rows.size()); + + assertTrue(cnt.intValue() - curIdx <= maxReq); + } + } +} http://git-wip-us.apache.org/repos/asf/ignite/blob/d669da15/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/twostep/JoinSqlTestHelper.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/twostep/JoinSqlTestHelper.java b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/twostep/JoinSqlTestHelper.java index fe7821a..3c9509d 100644 --- a/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/twostep/JoinSqlTestHelper.java +++ b/modules/indexing/src/test/java/org/apache/ignite/internal/processors/query/h2/twostep/JoinSqlTestHelper.java @@ -25,7 +25,10 @@ import org.apache.ignite.cache.query.annotations.QuerySqlField; */ public class JoinSqlTestHelper { /** */ - private static final int ORG_COUNT = 100; + static final String ORG = "org"; + + /** */ + static final int ORG_COUNT = 100; /** */ private static final int PERSON_PER_ORG_COUNT = 10; @@ -43,7 +46,7 @@ public class JoinSqlTestHelper { for (int i = 0; i < ORG_COUNT; i++) { Organization org = new Organization(); - org.setId("org" + i); + org.setId(ORG + i); org.setName("Organization #" + i); @@ -61,7 +64,7 @@ public class JoinSqlTestHelper { for (int i = 0; i < ORG_COUNT; i++) { Organization org = new Organization(); - org.setId("org" + i); + org.setId(ORG + i); org.setName("Organization #" + i); http://git-wip-us.apache.org/repos/asf/ignite/blob/d669da15/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteCacheQuerySelfTestSuite2.java ---------------------------------------------------------------------- diff --git a/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteCacheQuerySelfTestSuite2.java b/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteCacheQuerySelfTestSuite2.java index dba046b..ac8d10a 100644 --- a/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteCacheQuerySelfTestSuite2.java +++ b/modules/indexing/src/test/java/org/apache/ignite/testsuites/IgniteCacheQuerySelfTestSuite2.java @@ -54,6 +54,7 @@ import org.apache.ignite.internal.processors.query.h2.twostep.CacheQueryMemoryLe import org.apache.ignite.internal.processors.query.h2.twostep.CreateTableWithDateKeySelfTest; import org.apache.ignite.internal.processors.query.h2.twostep.DisappearedCacheCauseRetryMessageSelfTest; import org.apache.ignite.internal.processors.query.h2.twostep.DisappearedCacheWasNotFoundMessageSelfTest; +import org.apache.ignite.internal.processors.query.h2.twostep.InOperationExtractPartitionSelfTest; import org.apache.ignite.internal.processors.query.h2.twostep.NonCollocatedRetryMessageSelfTest; import org.apache.ignite.internal.processors.query.h2.twostep.RetryCauseMessageSelfTest; import org.apache.ignite.internal.processors.query.h2.twostep.TableViewSubquerySelfTest; @@ -123,6 +124,8 @@ public class IgniteCacheQuerySelfTestSuite2 extends TestSuite { suite.addTestSuite(DisappearedCacheCauseRetryMessageSelfTest.class); suite.addTestSuite(DisappearedCacheWasNotFoundMessageSelfTest.class); + suite.addTestSuite(InOperationExtractPartitionSelfTest.class); + suite.addTestSuite(TableViewSubquerySelfTest.class); return suite;