Feature 'Correlated subselect that can not be transformed and does not match on shard keys

[ricardo-lobo]

Hello.

We are in the process of migrating a MySQL database to Singlestore and we found some issues when trying to execute some queries generated by the singlestoredb-laravel-driver.

For example, the following query:

select
  `segments`.*,
  `auto_segments_customers`.`virtual_customer_id` as `pivot_virtual_customer_id`,
  `auto_segments_customers`.`segment_id` as `pivot_segment_id`,
  `auto_segments_customers`.`created_at` as `pivot_created_at`,
  `auto_segments_customers`.`updated_at` as `pivot_updated_at`,
  `auto_segments_customers`.`domain_id` as `pivot_domain_id`,
  `auto_segments_customers`.`domain_type` as `pivot_domain_type`
from
  `segments`
  inner join `auto_segments_customers` on `segments`.`id` = `auto_segments_customers`.`segment_id`
where
  `auto_segments_customers`.`virtual_customer_id` in (2)
  and (
    (
      `segments`.`domain_id` = 1
      and `segments`.`domain_type` = 'wholesalers'
    )
    or (
      `segments`.`domain_id` in (1)
      and `segments`.`domain_type` = 'manufacturers'
      and exists (
        select
          *
        from
          `segment_types`
        where
          `segments`.`segment_type_id` = `segment_types`.`id`
      )
    )
    or (
      `segments`.`domain_id` in (1)
      and `segments`.`domain_type` = 'manufacturer-branches'
      and exists (
        select
          *
        from
          `segment_types`
        where
          `segments`.`segment_type_id` = `segment_types`.`id`
      )
    )
  )
order by
  IF(
    (
      segments.domain_id = 1
      AND segments.domain_type = 'wholesalers'
    ),
    0,
    1
  )

Returns the following error:

ERROR 1749 ER_DISTRIBUTED_FEATURE_LOCKDOWN: Feature 'Correlated subselect that can not be transformed and does not match on shard keys' is not supported by SingleStore Distributed.

Can you help understanding the problem here and how we should solve it?

Thank you

[carlsverre]

Unfortunately, SingleStore has some limitations when planning correlated subselects. These limitations are related to the shard keys of the underlying table(s). Another customer has hit a similar issue which was answered on our forums: https://www.singlestore.com/forum/t/mysql-query-does-not-work-in-memsql/992/2

In this case, in order to run those exists subqueries, I believe that both segments and segment_types will need to be sharded on segment_type_id and id respectively. This will allow the two tables to be naturally joined on each partition without requiring any kind of network reshuffle.

Alternatively, you may be able to rewrite this query as a join operation.

If you are a SingleStore customer (either on-prem or via managed service) I recommend reaching out to SingleStore support for query planning assistance. Alternatively, please raise this issue on SingleStore's forums as that is where we offer general community support. Will close this ticket here as this issue is not directly related to the Laravel driver.