Adding an ORDER BY statement to a query without flattening results leads to "Cannot query the cross product of repeated fields"

Question

Query: 
    "SELECT * FROM [table] ORDER BY id DESC LIMIT 10"
    AllowLargeResults = true
    FlattenResults = false

table schema:
    [
        {
            "name": "id",
            "type": "STRING",
            "mode": "NULLABLE"
        },
        {
            "name": "repeated_field_1",
            "type": "STRING",
            "mode": "REPEATED"
        },
        {
            "name": "repeated_field_2",
            "type": "STRING",
            "mode": "REPEATED"
        }
    ]

The query "SELECT * FROM [table] LIMIT 10" works just fine. I get this error when I add an order by clause, even though the order by does not mention either repeated field.

Is there any way to make this work?

Answer 1

The ORDER BY clause causes BigQuery to automatically flatten the output of a query, causing your query to attempt to generate a cross product of repeated_field_1 and repeated_field_2.

If you don't care about preserving the repeatedness of the fields, you could explicitly FLATTEN both of them, which will cause your query to generate the cross product that the original query is complaining about.

SELECT *
FROM FLATTEN(FLATTEN([table], repeated_field_1), repeated_field_2)
ORDER BY id DESC
LIMIT 10

Other than that, I don't have a good workaround for your query to both ORDER BY and also output repeated fields.

See also: BigQuery flattens result when selecting into table with GROUP BY even with “noflatten_results” flag on