Dear Ignite specialists,
When running following SQL query, that join a few tables:
SELECT COUNT(p.platformCode)
FROM "platformCache".Platform p
LEFT JOIN (
SELECT
h1.platformCode,
SUM(CASEWHEN(h1.nodeType = ?, 1, 0)) AS numWebServiceNodes,
SUM(CASEWHEN(h1.nodeType = ?, 1, 0)) AS numContentfulServiceNodes,
SUM(CASEWHEN(h1.nodeType = ?, 1, 0)) AS numNotificationServiceNodes
FROM "serviceNodeCache".ServiceNode h1
WHERE h1.connected = TRUE AND h1.leftTimestamp IS NULL
GROUP BY h1.platformCode
) s
ON s.platformCode = p.platformCode
LEFT JOIN (
SELECT
h1.platformCode,
COUNT(h1.gsid) numGlobalSessions
FROM "globalSessionCache".GlobalSession h1
GROUP BY h1.platformCode
) g
ON g.platformCode = p.platformCode
LEFT JOIN (
SELECT
h1.platformCode,
SUM(h2.numGlobalSessionsConnections) numGlobalSessionsConnections
FROM "globalSessionCache".GlobalSession h1
LEFT JOIN (
SELECT h.gsid, COUNT(h.gsid) numGlobalSessionsConnections
FROM "globalSessionConnectionCache".GlobalSessionConnection h
WHERE h.disconnectTimestamp IS NULL
GROUP BY h.gsid
) h2
ON h2.gsid = h1.gsid
GROUP BY h1.platformCode
) gc
ON gc.platformCode = p.platformCode
;
we receive following exception:
Caused by: org.gridgain.internal.h2.jdbc.JdbcSQLNonTransientException: General error: "class org.apache.ignite.IgniteException: Fetched result set was too large. IGNITE_SQL_MERGE_TABLE_MAX_SIZE(10000) should be increased."; SQL statement:
SELECT
COUNT("P__Z0__PLATFORMCODE") AS "__X0"
FROM (SELECT
"P__Z0__PLATFORMCODE" AS "P__Z0__PLATFORMCODE"
FROM (SELECT
"__C0_0" AS "P__Z0__PLATFORMCODE"
FROM "PUBLIC"."__T0"
ORDER BY 1) AS "__Z9"
LEFT OUTER JOIN (SELECT
"__C1_0" AS "PLATFORMCODE",
CAST(CAST(SUM("__C1_1") AS BIGINT) AS BIGINT) AS "NUMWEBSERVICENODES",
CAST(CAST(SUM("__C1_2") AS BIGINT) AS BIGINT) AS "NUMCONTENTFULSERVICENODES",
CAST(CAST(SUM("__C1_3") AS BIGINT) AS BIGINT) AS "NUMNOTIFICATIONSERVICENODES"
FROM "PUBLIC"."__T1"
GROUP BY "__C1_0"
ORDER BY 1) AS "S__Z2"
ON "S__Z2"."PLATFORMCODE" = "P__Z0__PLATFORMCODE"
ORDER BY 1) AS "__Z11"
LEFT OUTER JOIN (SELECT
"__C2_0" AS "PLATFORMCODE",
CAST(SUM("__C2_1") AS BIGINT) AS "NUMGLOBALSESSIONS"
FROM "PUBLIC"."__T2"
GROUP BY "__C2_0"
ORDER BY 1) AS "G__Z4"
ON "G__Z4"."PLATFORMCODE" = "P__Z0__PLATFORMCODE"
LEFT OUTER JOIN (SELECT
"H1__Z5__PLATFORMCODE" AS "PLATFORMCODE",
SUM("H2__Z7"."NUMGLOBALSESSIONSCONNECTIONS") AS "NUMGLOBALSESSIONSCONNECTIONS"
FROM (SELECT
"__C3_0" AS "H1__Z5__GSID",
"__C3_1" AS "H1__Z5__PLATFORMCODE"
FROM "PUBLIC"."__T3"
ORDER BY 1) AS "__Z10"
LEFT OUTER JOIN (SELECT
"__C4_0" AS "GSID",
CAST(SUM("__C4_1") AS BIGINT) AS "NUMGLOBALSESSIONSCONNECTIONS"
FROM "PUBLIC"."__T4"
GROUP BY "__C4_0"
ORDER BY 1) AS "H2__Z7"
ON "H2__Z7"."GSID" = "H1__Z5__GSID"
GROUP BY "H1__Z5__PLATFORMCODE") AS "GC__Z8"
ON "GC__Z8"."PLATFORMCODE" = "P__Z0__PLATFORMCODE" [50000-199]
at org.gridgain.internal.h2.message.DbException.getJdbcSQLException(DbException.java:503)
at org.gridgain.internal.h2.message.DbException.getJdbcSQLException(DbException.java:427)
at org.gridgain.internal.h2.message.DbException.get(DbException.java:194)
at org.gridgain.internal.h2.message.DbException.convert(DbException.java:347)
at org.gridgain.internal.h2.command.Command.executeQuery(Command.java:219)
at org.gridgain.internal.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:115)
at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:876)
... 69 common frames omitted
Caused by: org.apache.ignite.IgniteException: Fetched result set was too large. IGNITE_SQL_MERGE_TABLE_MAX_SIZE(10000) should be increased.
at org.apache.ignite.internal.processors.query.h2.twostep.AbstractReducer.checkBounds(AbstractReducer.java:195)
at org.apache.ignite.internal.processors.query.h2.twostep.SortedReducer.checkBounds(SortedReducer.java:206)
at org.apache.ignite.internal.processors.query.h2.twostep.AbstractReducer.find(AbstractReducer.java:165)
at org.apache.ignite.internal.processors.query.h2.twostep.AbstractReduceIndexAdapter.find(AbstractReduceIndexAdapter.java:75)
at org.gridgain.internal.h2.index.BaseIndex.find(BaseIndex.java:132)
at org.gridgain.internal.h2.index.IndexCursor.find(IndexCursor.java:190)
at org.gridgain.internal.h2.table.TableFilter.next(TableFilter.java:524)
at org.gridgain.internal.h2.command.dml.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1938)
at org.gridgain.internal.h2.result.LazyResult.hasNext(LazyResult.java:101)
at org.gridgain.internal.h2.result.LazyResult.next(LazyResult.java:60)
at org.gridgain.internal.h2.command.dml.Select.queryFlat(Select.java:751)
at org.gridgain.internal.h2.command.dml.Select.queryWithoutCache(Select.java:904)
at org.gridgain.internal.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:151)
at org.gridgain.internal.h2.command.dml.Query.query(Query.java:415)
at org.gridgain.internal.h2.command.dml.Query.query(Query.java:397)
at org.gridgain.internal.h2.index.ViewIndex.find(ViewIndex.java:288)
at org.gridgain.internal.h2.index.ViewIndex.find(ViewIndex.java:160)
at org.gridgain.internal.h2.index.BaseIndex.find(BaseIndex.java:132)
at org.gridgain.internal.h2.index.IndexCursor.find(IndexCursor.java:190)
at org.gridgain.internal.h2.table.TableFilter.next(TableFilter.java:524)
at org.gridgain.internal.h2.command.dml.Select$LazyResultGroupSorted.fetchNextRow(Select.java:1996)
at org.gridgain.internal.h2.result.LazyResult.hasNext(LazyResult.java:101)
at org.gridgain.internal.h2.result.LazyResult.next(LazyResult.java:60)
at org.gridgain.internal.h2.command.dml.Select.queryGroupSorted(Select.java:316)
at org.gridgain.internal.h2.command.dml.Select.queryWithoutCache(Select.java:894)
at org.gridgain.internal.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:151)
at org.gridgain.internal.h2.command.dml.Query.query(Query.java:415)
at org.gridgain.internal.h2.command.dml.Query.query(Query.java:397)
at org.gridgain.internal.h2.index.ViewIndex.find(ViewIndex.java:288)
at org.gridgain.internal.h2.index.ViewIndex.find(ViewIndex.java:160)
at org.gridgain.internal.h2.index.BaseIndex.find(BaseIndex.java:132)
at org.gridgain.internal.h2.index.IndexCursor.find(IndexCursor.java:190)
at org.gridgain.internal.h2.table.TableFilter.next(TableFilter.java:524)
at org.gridgain.internal.h2.table.TableFilter.next(TableFilter.java:594)
at org.gridgain.internal.h2.table.TableFilter.next(TableFilter.java:594)
at org.gridgain.internal.h2.command.dml.Select.gatherGroup(Select.java:536)
at org.gridgain.internal.h2.command.dml.Select.queryGroup(Select.java:503)
at org.gridgain.internal.h2.command.dml.Select.queryWithoutCache(Select.java:897)
at org.gridgain.internal.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:151)
at org.gridgain.internal.h2.command.dml.Query.query(Query.java:415)
at org.gridgain.internal.h2.command.dml.Query.query(Query.java:397)
at org.gridgain.internal.h2.command.CommandContainer.query(CommandContainer.java:145)
at org.gridgain.internal.h2.command.Command.executeQuery(Command.java:202)
... 71 common frames omitted
When running the query, from the ./sqlline.sh tool, all works fine - for both, running from server node or from a client node.
But, when running it through the application, using:
...
igniteCache.query(new SqlFieldsQuery(...).setArgs(...)).getAll()
...
we receive upper exception.
By looking deeper into the LEFT JOIN s, it looks that one of them groups > 10000 rows:
0: jdbc:ignite:thin://127.0.0.1/> SELECT CAST(h1.platformCode AS VARCHAR),
. . . . . . . . . . . . . . . . > COUNT(h1.gsid) numGlobalSessions
. . . . . . . . . . . . . . . . > FROM "globalSessionCache".GlobalSession h1
. . . . . . . . . . . . . . . . > GROUP BY h1.platformCode
. . . . . . . . . . . . . . . . > ;
+----------------------------------+--------------------------------+
| CAST(H1.PLATFORMCODE AS VARCHAR) | NUMGLOBALSESSIONS |
+----------------------------------+--------------------------------+
| -415685662 | 115 |
| 44623367 | 155 |
| -315116152 | 2272 |
| 1257703305 | 8621 |
+----------------------------------+--------------------------------+
But, the subqueries (LEFT JOINs) only accumulate/group by the records. Is there even a need to transfer the whole records?
Any help, how to solve the exception is appreciated. Thanks ahead, Felix
We could yet try much.
We've simply extracted the SQL query from the logs, and tried to run it via sqlline.sh and via GridGain Control Center. Both works for us ...
We would ask for little help first, what causes this exception? So, what exactly is our problem?
Of course, we can simply increase the parameter IGNITE_SQL_MERGE_TABLE_MAX_SIZE as suggested.
But will we have the same issue, when having more data?
We would expect the query to also work with millions of records ...
