Hi team, is there a way to filter __time based on subquery? I tried a simple query like below

WITH a AS (
  SELECT druid_time
  FROM dim_map
  WHERE id = 'abc'
  LIMIT 1
)
SELECT *
FROM agg_table
WHERE __time = (SELECT druid_time FROM a)

But got error

Error: INVALID_INPUT (ADMIN)
Query could not be planned. A possible reason is [Aggregation [SINGLE_VALUE($0)] is not supported]

I also tried using

IN

instead of

=

, but it end up with a join which is bad and scans all segments. I know I can use two separate queries, but I am working with Looker so this is not an option. Any suggestion is appreciated!

https://github.com/apache/druid/pull/15700 Druid 30 fixes this issue. Please upgrade to Druid 30 if you are on an older version. In case you are on Druid 30, or if the issue still persists after upgrading lmk

although note that even though the

=

will work, you will still get a join that has to hit all segments!

Thanks all for the info!