Sub-select with "spaceship operator" causes a error 1749

Help
1

Hi all,

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.

Query:

SELECT
    COALESCE(server_domains.name, server_domains.ip) as nickname,
    player_sessions.server_id,
    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'
FROM
    player_sessions
LEFT JOIN
    server_domains ON player_sessions.server_domain_id = server_domains.id
WHERE
    quit_at BETWEEN "2022-05-10" AND "2022-05-17"
GROUP BY
    server_domains.id
ORDER BY
    total desc

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.

Thanks for your time.

2

Hi Charlie! :sun_with_face:

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:

  1. Avoid that type of subselect
  2. Make the inner subselect’s table (player_sessions) a reference table
  3. 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 :raised_hands:

1 Like
3

Hey Maria!

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.