ignite-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Stanislav Lukyanov (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (IGNITE-11829) Distribute joins fail if number of tables > 7
Date Sat, 25 May 2019 01:15:00 GMT

    [ 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.<init>(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.<init>(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)

Mime
View raw message