Multistage join queries and adaptive routing compatibility

[dang-stripe]

We are testing some multistage queries with joins and adaptive routing turned on on a table using 3 replicas, 30 servers each. We joined against another much smaller table 90 replicas, 1 instance each on the same tenant. This led to all queries for the smaller table to only be handled by 2 of the 90 servers. This happened with adaptive routing on.

We tried turning it off, and we saw queries start routing to the other 90 servers. It seems like there might be some incompatibility or undefined behavior between multistage and adaptive routing.

If multistage + adaptive routing are not compatible, is it possible to disable adaptive routing for multistage queries by default? Or is this a bug in the code?

cc @Jackie-Jiang @jadami10 @priyen-stripe

[dang-stripe]

@priyen-stripe could you include a sample query that was seeing this behavior?

[Jackie-Jiang]

@vvivekiyer Can you help take a look at this? Also should we allow adaptive server selector for strict replica group based instance selector?

[priyen]

cc @dang-stripe

anonymized_table AS (
  SELECT data_table.date,
    entity_id,
    SUM(FLOOR(metric_value / rate_from.rate * rate_to.rate)) as transformed_value
  FROM data_table
  
    JOIN conversion_rates rate_to ON rate_to.unit = 'base_unit'
    AND data_table.date = rate_to.rate_start_date
    JOIN conversion_rates rate_from ON rate_from.unit = data_table.unit
    AND rate_from.rate_start_date = data_table.date
  GROUP BY
    data_table.date,
    entity_id
)
SELECT date,
  entity_id,
  transformed_value
FROM anonymized_table
ORDER BY entity_id,
  date DESC;

data_table is the table with 3 replicas (30 servers each) conversion_rates table is the one with 1 server per replica and 90 replicas. It is not set as a dim table but we treat it like one