I’ve been writing a query which will pull in the unique players that each domain (from within my system), however I’m having an issue. Sometimes the
server_domain_id can be null, so I added the “spaceship operator”, but now received an error.
COALESCE(server_domains.name, server_domains.ip) as nickname,
count(*) AS total,
(SELECT COUNT(DISTINCT player_id) FROM player_sessions ps2 WHERE player_sessions.server_domain_id <=> ps2.server_domain_id) as 'unique_players'
server_domains ON player_sessions.server_domain_id = server_domains.id
quit_at BETWEEN "2022-05-10" AND "2022-05-17"
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.
Thanks for your time.
Glad you asked about this. In this case…
(SELECT COUNT(DISTINCT player_id) FROM player_sessions ps2 WHERE player_sessions.server_domain_id <=> ps2.server_domain_id) as ‘unique_players’;
This type of subselect only works if the tables in the subselect match the shard key of the tables outside. Here we have a few options:
- Avoid that type of subselect
- Make the inner subselect’s table (player_sessions) a reference table
- Make player_sessions and server_domains match the shard key (e.g. make them shard on player_sessions.server_domain_id and server_domains.id)
Note that 2 and 3 would have impact on other queries on those tables.
Please let us know if this is helpful and keep us updated. Thanks
Thanks for your time with the reply, annoyingly player_sessions has a lot of data so modifying the table would probably be out the equation.
Is there any alternative to a subselect to query to get this figure? I know at worst I could just perform 2 separate queries and merge the data, but if I can do it in 1 then its even better.