From issues-return-96254-archive-asf-public=cust-asf.ponee.io@ignite.apache.org Sat May 25 01:15:03 2019 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [207.244.88.153]) by mx-eu-01.ponee.io (Postfix) with SMTP id 1DF60180671 for ; Sat, 25 May 2019 03:15:03 +0200 (CEST) Received: (qmail 55092 invoked by uid 500); 25 May 2019 01:15:02 -0000 Mailing-List: contact issues-help@ignite.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@ignite.apache.org Delivered-To: mailing list issues@ignite.apache.org Received: (qmail 55079 invoked by uid 99); 25 May 2019 01:15:02 -0000 Received: from mailrelay1-us-west.apache.org (HELO mailrelay1-us-west.apache.org) (209.188.14.139) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 25 May 2019 01:15:02 +0000 Received: from jira-lw-us.apache.org (unknown [207.244.88.139]) by mailrelay1-us-west.apache.org (ASF Mail Server at mailrelay1-us-west.apache.org) with ESMTP id 2DCEAE2B4F for ; Sat, 25 May 2019 01:15:01 +0000 (UTC) Received: from jira-lw-us.apache.org (localhost [127.0.0.1]) by jira-lw-us.apache.org (ASF Mail Server at jira-lw-us.apache.org) with ESMTP id 94E8C20FEB for ; Sat, 25 May 2019 01:15:00 +0000 (UTC) Date: Sat, 25 May 2019 01:15:00 +0000 (UTC) From: "Stanislav Lukyanov (JIRA)" To: issues@ignite.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Commented] (IGNITE-11829) Distribute joins fail if number of tables > 7 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/IGNITE-11829?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16847997#comment-16847997 ] Stanislav Lukyanov commented on IGNITE-11829: --------------------------------------------- The workaround for this issue is two group some of the JOINs into a subquery: {code} SELECT * FROM public.PERSON P1 JOIN public.PERSON P2 ON P1.ID = P2.ID JOIN public.PERSON P3 ON P1.ID = P3.ID JOIN public.PERSON P4 ON P1.ID = P4.ID JOIN public.PERSON P5 ON P1.ID = P5.ID JOIN public.PERSON P6 ON P1.ID = P6.ID JOIN ( select P7.ID as ID, P7.NAME as P7NAME, P8.NAME as P8NAME FROM public.PERSON P7 JOIN public.PERSON P8 ON P7.ID = P8.ID ) P78 ON P1.ID = P78.ID {code} > Distribute joins fail if number of tables > 7 > --------------------------------------------- > > Key: IGNITE-11829 > URL: https://issues.apache.org/jira/browse/IGNITE-11829 > Project: Ignite > Issue Type: Improvement > Components: sql > Affects Versions: 2.7 > Reporter: Stanislav Lukyanov > Priority: Major > Labels: newbie > > Distributed joins fail with ArrayIndexOutOfBounds when the total number of tables is > 7. > Example: > {code} > try (Ignite ignite = Ignition.start("examples/config/example-ignite.xml");) { > IgniteCache cache = ignite.createCache("foo"); > cache.query(new SqlFieldsQuery("CREATE TABLE Person(ID INTEGER PRIMARY KEY, NAME VARCHAR(100));")); > cache.query(new SqlFieldsQuery("INSERT INTO Person(ID, NAME) VALUES (1, 'Ed'), (2, 'Ann'), (3, 'Emma');")); > cache.query(new SqlFieldsQuery("SELECT *\n" + > "FROM PERSON P1\n" + > "JOIN PERSON P2 ON P1.ID = P2.ID\n" + > "JOIN PERSON P3 ON P1.ID = P3.ID\n" + > "JOIN PERSON P4 ON P1.ID = P4.ID\n" + > "JOIN PERSON P5 ON P1.ID = P5.ID\n" + > "JOIN PERSON P6 ON P1.ID = P6.ID\n" + > "JOIN PERSON P7 ON P1.ID = P7.ID\n" + > "JOIN PERSON P8 ON P1.ID = P8.ID").setDistributedJoins(true).setEnforceJoinOrder(false)); > } > {code} > throws > {code} > Exception in thread "main" javax.cache.CacheException: General error: "java.lang.ArrayIndexOutOfBoundsException" [50000-197] > at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:832) > at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:765) > at org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:403) > at org.apache.ignite.examples.ExampleNodeStartup.main(ExampleNodeStartup.java:60) > Caused by: class org.apache.ignite.internal.processors.query.IgniteSQLException: General error: "java.lang.ArrayIndexOutOfBoundsException" [50000-197] > at org.apache.ignite.internal.processors.query.h2.QueryParser.parseH2(QueryParser.java:454) > at org.apache.ignite.internal.processors.query.h2.QueryParser.parse0(QueryParser.java:156) > at org.apache.ignite.internal.processors.query.h2.QueryParser.parse(QueryParser.java:121) > at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1191) > at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2261) > at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2257) > at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:53) > at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2767) > at org.apache.ignite.internal.processors.query.GridQueryProcessor.lambda$querySqlFields$1(GridQueryProcessor.java:2277) > at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuerySafe(GridQueryProcessor.java:2297) > at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2250) > at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2177) > at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:817) > ... 3 more > Caused by: org.h2.jdbc.JdbcSQLException: General error: "java.lang.ArrayIndexOutOfBoundsException" [50000-197] > at org.h2.message.DbException.getJdbcSQLException(DbException.java:357) > at org.h2.message.DbException.get(DbException.java:168) > at org.h2.message.DbException.convert(DbException.java:307) > at org.h2.message.DbException.toSQLException(DbException.java:280) > at org.h2.message.TraceObject.logAndConvert(TraceObject.java:357) > at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:308) > at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.prepare(GridSqlQuerySplitter.java:1770) > at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.split0(GridSqlQuerySplitter.java:299) > at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.split(GridSqlQuerySplitter.java:219) > at org.apache.ignite.internal.processors.query.h2.QueryParser.parseH2(QueryParser.java:415) > ... 15 more > Caused by: java.lang.ArrayIndexOutOfBoundsException > at java.lang.System.arraycopy(Native Method) > at org.apache.ignite.internal.processors.query.h2.opt.join.CollocationModel.childFilters(CollocationModel.java:240) > at org.apache.ignite.internal.processors.query.h2.opt.join.CollocationModel.buildCollocationModel(CollocationModel.java:733) > at org.apache.ignite.internal.processors.query.h2.opt.join.CollocationModel.distributedMultiplier(CollocationModel.java:667) > at org.apache.ignite.internal.processors.query.h2.opt.GridH2IndexBase.getDistributedMultiplier(GridH2IndexBase.java:128) > at org.apache.ignite.internal.processors.query.h2.opt.H2TableScanIndex.getCost(H2TableScanIndex.java:94) > at org.h2.table.Table.getBestPlanItem(Table.java:714) > at org.h2.table.TableFilter.getBestPlanItem(TableFilter.java:224) > at org.h2.table.Plan.calculateCost(Plan.java:121) > at org.h2.command.dml.Optimizer.calculateBruteForceSome(Optimizer.java:137) > at org.h2.command.dml.Optimizer.calculateBestPlan(Optimizer.java:87) > at org.h2.command.dml.Optimizer.optimize(Optimizer.java:239) > at org.h2.command.dml.Select.preparePlan(Select.java:1018) > at org.h2.command.dml.Select.prepare(Select.java:884) > at org.h2.command.Parser.prepareCommand(Parser.java:283) > at org.h2.engine.Session.prepareLocal(Session.java:611) > at org.h2.engine.Session.prepareCommand(Session.java:549) > at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247) > at org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:76) > at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:304) > ... 19 more > {code} > or if run with -ea > {code} > Exception in thread "main" java.lang.AssertionError > at org.apache.ignite.internal.processors.query.h2.opt.join.CollocationModel.childFilters(CollocationModel.java:237) > at org.apache.ignite.internal.processors.query.h2.opt.join.CollocationModel.buildCollocationModel(CollocationModel.java:733) > at org.apache.ignite.internal.processors.query.h2.opt.join.CollocationModel.distributedMultiplier(CollocationModel.java:667) > at org.apache.ignite.internal.processors.query.h2.opt.GridH2IndexBase.getDistributedMultiplier(GridH2IndexBase.java:128) > at org.apache.ignite.internal.processors.query.h2.opt.H2TableScanIndex.getCost(H2TableScanIndex.java:94) > at org.h2.table.Table.getBestPlanItem(Table.java:714) > at org.h2.table.TableFilter.getBestPlanItem(TableFilter.java:224) > at org.h2.table.Plan.calculateCost(Plan.java:121) > at org.h2.command.dml.Optimizer.calculateBruteForceSome(Optimizer.java:137) > at org.h2.command.dml.Optimizer.calculateBestPlan(Optimizer.java:87) > at org.h2.command.dml.Optimizer.optimize(Optimizer.java:239) > at org.h2.command.dml.Select.preparePlan(Select.java:1018) > at org.h2.command.dml.Select.prepare(Select.java:884) > at org.h2.command.Parser.prepareCommand(Parser.java:283) > at org.h2.engine.Session.prepareLocal(Session.java:611) > at org.h2.engine.Session.prepareCommand(Session.java:549) > at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1247) > at org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:76) > at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:304) > at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.prepare(GridSqlQuerySplitter.java:1770) > at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.split0(GridSqlQuerySplitter.java:299) > at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.split(GridSqlQuerySplitter.java:219) > at org.apache.ignite.internal.processors.query.h2.QueryParser.parseH2(QueryParser.java:415) > at org.apache.ignite.internal.processors.query.h2.QueryParser.parse0(QueryParser.java:156) > at org.apache.ignite.internal.processors.query.h2.QueryParser.parse(QueryParser.java:121) > at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:1191) > at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2261) > at org.apache.ignite.internal.processors.query.GridQueryProcessor$3.applyx(GridQueryProcessor.java:2257) > at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:53) > at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2767) > at org.apache.ignite.internal.processors.query.GridQueryProcessor.lambda$querySqlFields$1(GridQueryProcessor.java:2277) > at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuerySafe(GridQueryProcessor.java:2297) > at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2250) > at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2177) > at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:817) > at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:765) > at org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:403) > at org.apache.ignite.examples.ExampleNodeStartup.main(ExampleNodeStartup.java:60) > {code} > The reason for 7 being the magic number seems to be H2's Optimizer.MAX_BRUTE_FORCE_FILTERS = 7. When exceeding that, the behavior of H2 code changes - to something Ignite doesn't expect. > Apparently enforceJoinOrder=true is a workaround - in that case Ignite doesn't really try to compute the cost of the joins and doesn't execute the buggy code path. -- This message was sent by Atlassian JIRA (v7.6.3#76005)