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

Help
1

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

2

A correlated subselect is a nested SELECT statement that has a table.field from outside it. The query has two nested select statements and both of them are correlated.

The optimizer for some reason is not able to transform it to another form it can handle.

Maybe you can shard both segment_types and segments on the respective join columns? Or, if you can change the query, modify it to not use correlated subselects but still mean the same thing.

1 Like