SQL-API: Preset series limit sub query is not supported

[mitalauskas]

Failed SQL

QUERY: SELECT Date_trunc('day', invoice_paid_at) AS invoice_paid_at,
       brand                              AS brand,
       Measure(aov.average_order_value)   AS average_order_value
FROM   "aov"
       JOIN (SELECT brand                            AS brand__,
                    Measure(aov.average_order_value) AS mme_inner__
             FROM   "aov"
             WHERE  invoice_paid_at >= Str_to_date('2024-05-01 00:00:00.000000',
                                               'YYYY-MM-DD HH24:MI:SS.US')
                    AND invoice_paid_at <
                        Str_to_date('2024-06-01 00:00:00.000000',
                        'YYYY-MM-DD HH24:MI:SS.US')
             GROUP  BY brand
             ORDER  BY mme_inner__ DESC
             LIMIT  5) AS series_limit
         ON brand = brand__
WHERE  invoice_paid_at >= Str_to_date('2024-05-01 00:00:00.000000',
                                 'YYYY-MM-DD HH24:MI:SS.US')
       AND invoice_paid_at < Str_to_date('2024-06-01 00:00:00.000000',
                             'YYYY-MM-DD HH24:MI:SS.US')
GROUP  BY Date_trunc('day', invoice_paid_at),
          brand
ORDER  BY average_order_value DESC
LIMIT  10000 

Logical Plan Error: Error during rewrite: Use __cubeJoinField to join Cubes.

Version: v0.35.52

Additional context In Preset, I'm trying to add a series limit on my dimension split. However, as it's trying to run a subquery, Cube is spitting out an error.

[igorlukanin]

@mitalauskas Thanks for reporting this! Currently, this kind of self-join is not supported in the SQL API. We can use this issue to track it further. The current workaround would be to hold on using the Preset feature that causes this.

[mitalauskas]

Hey, Igor. I also noticed that the same issue happens when trying to use limiting filters (for limiting series in charts) in Tableau.