INSERT FROM SELECT results in SpoolingHashAggregateIterator (short-circuiting) ran out of memory error

[jobinba]

For big table INSERT FROM SELECT statement results in internal spanner error:

SpoolingHashAggregateIterator (short-circuiting) ran out of memory during new row

This is because inside

protected long getEstimatedRecordCount(Select select) throws SQLException {

this sql
String sql = "SELECT COUNT(*) AS C FROM (" + select.toString() + ") Q";

exceeding the memory limit for spanner operations

Inner query is basically doing a DISTINCT operation. Spanner currently implements such operations in a streaming manner, which prevents the aggregation from spilling to disk.

To avoid this error please add useless aggregate, for example MAX

SELECT COUNT(*), MAX({first_column}) AS C FROM (" + select.toString() + ")

This will cause spanner to spill to disk instead of failing if it run out of memory.

[olavloite]

Thanks for the detailed report. I'll have a look at it.

[olavloite]

@jobinba Could you check if it would be enough to do add a useless aggregate on a constant value instead of an actual column? (I guess you have a Spanner database with a table big enough to try this out)
I.e. would this query work: SELECT COUNT(*), MAX(1) AS C FROM (" + select.toString() + ") Q"

That would avoid the necessity of determining the first (or any other) column of the inner query at that point and keeping that part of the code as simple as possible.

[jobinba]

I've tried this yesterday and MAX(1) is not enough to trick optimizer. In my tests {first_column} is also not enough: if {first_column} is included in GROUP BY list of internal select, than spanner still using streaming mode.

Better solution might be to catch this particular exception in getEstimatedRecordCount() and return Integer.MAX_VALUE

[olavloite]

The AbstractTablePartWorker doesn't need to know the exact number of records returned by the query, only if the number exceeds the threshold for entering into extended mode. By putting a limit on the count query, the exception should be avoided and the query should be a lot faster.

[olavloite]

Fixed in 1.0.4.

[jobinba]

Verified. Thanks a lot for a quick fix.