Query failed with error RESOURCE_EXHAUSTED (ORDER BY without ORDER BY )

Question

I have a query of the below form, running on a large table (~60GB). I figure the WITH OFFSET is some sort of ORDER BY under the hood? Any ideas how to heal this problem are welcome.

I get the following error: Job xxx failed with error RESOURCE_EXHAUSTED: Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: 112% of limit. Top memory consumer(s): ORDER BY operations: 98% other/unattributed: 2% ; JobID: xxx

WITH
  test_data AS (
  SELECT
    CAST(CURRENT_TIMESTAMP() as DATETIME) datetime,
    'xyz' AS grp,
    '["7f9f98fh9g4ef393d3h5", "chg3g33f26949hg6067d", "g477e5973ec04g7c3232", "0de1ec83304d761he786", "3c1h1f153530g90g35c2", "946637g145h48322686f"]' AS ids
  UNION ALL
  SELECT
    CAST(CURRENT_TIMESTAMP() as DATETIME) datetime,
    'abc' AS grp,
    '["7f9f98fh9g4ef393d3h5", "chg3g33fdsfsdfs49hg6067d", "g477e5973ec04g7c3232", "0de1ec83304d761he786", "3c1h1f153530g90g35c2", "946637g145h48322686f"]' AS ids ),

  as_list AS (
    SELECT
      datetime,
      grp,
      id,
      pos
    FROM
     test_data, UNNEST(SPLIT(REGEXP_REPLACE(JSON_EXTRACT(ids,'$'), r'[\[\]\"]', ''), ',')) AS id WITH OFFSET AS pos)

SELECT
  *
FROM
  as_list
ORDER BY
  grp, datetime

The sample query runs well. Do you have any sample query that actually fails? Something missing in the question: Why do you need to use WITH OFFSET? — Felipe Hoffa
@FelipeHoffa I do need the with offset to get the position of the element in question. The above query is only a sample, the actual query runs on a much larger data set (60GB). — Fabian Bosler
@FabianBosler - is it larger in terms of rows or in terms of size of individual row? — Mikhail Berlyant
@MikhailBerlyant In terms of number of number of rows, couple of million rows. Not too many columns (40 or so). Does that answer your question? — Fabian Bosler
Actually I think I have messed up here and overlooked an ORDER BY at the very end of the query. Apologise. — Fabian Bosler

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2019-06-12T17:20:00

2

votes

To address that issue just either remove ORDER BY or add LIMIT

Query failed with error RESOURCE_EXHAUSTED (ORDER BY without ORDER BY )

1 Answers