When I try to fetch a single value for each row via a sub query, I get the error ERROR 1749 ER_DISTRIBUTED_FEATURE_LOCKDOWN: Feature 'Scalar subselect where outer table is not a sharded table' is not supported by MemSQL Distributed.
Is there something wrong with the SQL or the table creation script?
select x.*,
(select last_visited_ts from link li2 where li2.id=x.url_link_id) last_visited_ts
from (
select l.url_link_id,
count(1) pageviews,
count(distinct l.sessionid_id) user_sessions
from log l
where l.timestamp > date_add(current_date(), interval -1000 day)
and l.dg_intern_extern_ie = 'I'
group by l.url_link_id
order by pageviews desc
limit 10000
) x;
CREATE TABLE log (
ID bigint not null,
TIMESTAMP DATETIME,
URL_LINK_ID bigint,
SESSIONID_ID bigint,
SESSIONORDER bigint,
DG_INTERN_EXTERN_IE bigint,
month_date date,
key(TIMESTAMP, SESSIONID_ID, SESSIONORDER, month_date) using clustered columnstore,
shard(url_link_id))
CREATE TABLE link (
ID bigint not null,
DOMAIN_ID bigint not null,
INTERN_EXTERN_IEB bigint not null,
CREATED_DATE DATE,
url varchar(4000) not null,
title varchar(4000) not null,
last_visited_ts DATE,
key(id) using clustered columnstore,
shard(id));