MySQL query does not work in MemSQL

Off-Topic
1

Hello, we used MySQL in the past, now when we moved to MemSQL, we got few problems when executing queries in PHP.

$query = DB::getInstance()->query("SELECT topics.id as id, topics.forum_id as forum_id, topics.topic_title as topic_title, topics.topic_creator as topic_creator, topics.topic_last_user as topic_last_user, topics.topic_date as topic_date, topics.topic_reply_date as topic_reply_date, topics.topic_views as topic_views, topics.locked as locked, topics.sticky as sticky, topics.label as label, topics.deleted as deleted, posts.id as last_post_id FROM nl2_topics topics LEFT JOIN nl2_posts posts ON topics.id = posts.topic_id AND posts.id = (SELECT MAX(id) FROM nl2_posts p WHERE p.topic_id = topics.id AND p.deleted = 0) WHERE topics.deleted = 0 AND topics.forum_id IN " . $all_topics_forums_string . " ORDER BY topics.topic_reply_date DESC LIMIT 50", array(), PDO::FETCH_ASSOC)->results();

Here’s our SELECT query (executed using PDO)
And here’s the error we get when executing the query

Uncaught PDOException: SQLSTATE[HY000]: General error: 1749 Feature 'Correlated subselect that can not be transformed and does not match on shard keys' is not supported by MemSQL Distributed. in /var/www/html/core/classes/DB.php:51
Stack trace:
2

Try sharding the tables (in the CREATE TABLE statements) on the columns you’re joining on in the correlated subquery: nl2_posts.topic_id and topics.id.

e.g.

memsql> create table t1(a int, b int, shard(b));
Query OK, 0 rows affected (0.08 sec)

memsql> create table t2(b int, c int, shard(b));
Query OK, 0 rows affected (0.08 sec)

memsql> select * from t1 left join t2 on (t2.b =
    ->   (select max(t22.b) from t2 t22 where t22.b = t1.b));
Empty set (0.19 sec)

Or consider rewriting the query using a sequence of common table expressions and pick out the “max” value using a window function (rank()) and filtering on rank() = 1.

3

I have the query below, and I also get the same error.
My shard key is defined as
SHARD KEY times (times,LF_TireInPositionAtLifter)
and it still fails.
I also tried shard key as
SHARD KEY times (times)
with the same result.

Query and table definition are shown below.

select times,
a.LF_TireInPositionAtLifter,
(select b.LF_TireInPositionAtLifter from cloudDB.F3_07 b where b.LF_TireInPositionAtLifter is not null and b.times < a.times order by times asc limit 1)
from
cloudDB.F3_07 a
;

create TABLE cloudDB.F3_07 (
times datetime(6) NOT NULL,
LF_GoodTorque int DEFAULT NULL,
LF_LifterCylinderIsUpPerFestoFeedback int DEFAULT NULL,
LF_TireInPositionAtLifter int DEFAULT NULL,
LF_TireTiltUP_Cmd int DEFAULT NULL,
LR_GoodTorque int DEFAULT NULL,
LR_LifterCylinderIsUpPerFestoFeedback int DEFAULT NULL,
LR_TireInPositionAtLifter int DEFAULT NULL,
LR_TireTiltUP_Cmd float DEFAULT NULL,
LeftFrontExtended int DEFAULT NULL,
LeftFrontRetracted int DEFAULT NULL,
LeftRearExtended int DEFAULT NULL,
LeftRearRetracted int DEFAULT NULL,
RF_GoodTorque int DEFAULT NULL,
RF_TireInPositionAtLifter int DEFAULT NULL,
RF_TireTiltUP_Cmd int DEFAULT NULL,
RR_GoodTorque int DEFAULT NULL,
RR_LifterCylinderIsUpPerFestoFeedback int DEFAULT NULL,
RR_TireInPositionAtLifter int DEFAULT NULL,
RR_TireTiltUP_Cmd int DEFAULT NULL,
RightFrontExtended int DEFAULT NULL,
RightFrontRetracted int DEFAULT NULL,
RightRearExtended int DEFAULT NULL,
RightRearRetracted int DEFAULT NULL,
TWS_LF_PushbuttonPushed int DEFAULT NULL,
TWS_LR_PushbuttonPushed int DEFAULT NULL,
TWS_RF_PushbuttonPushed int DEFAULT NULL,
TWS_RR_PushbuttonPushed int DEFAULT NULL,
VIN varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
Model varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
assetid varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
SHARD KEY times (times,LF_TireInPositionAtLifter)

);

4

Are you missing an equi-join predicate in the subquery? If you sharded on the equi-join column then the query might work).

You might be able to rewrite the query using a window function. It looks like you are looking for LF_TireInPositionAtLifter that is (a) not null and (b) right before the current one. Consider a single table query that uses LAG(1) and ORDER BY times after filtering out NULL LF_TireInPositionAtLifter values.

5

Thanks.

I tried this and it has the same error.
Is this the correct way to equi-join?

select a.times,
case
when a.LF_TireInPositionAtLifter is null then (select b.LF_TireInPositionAtLifter from cloudDB.F3_07 b where b.times < a.times and b.LF_TireInPositionAtLifter is not null order by b.times asc limit 1)
end as LF_TireInPositionAtLifter
from
cloudDB.F3_07 a
join cloudDB.F3_07 b on a.times = b.times
order by a.times desc;

I also tried this variation

select a.times,
case
when a.LF_TireInPositionAtLifter is null then (select b.LF_TireInPositionAtLifter from cloudDB.F3_07 b join cloudDB.F3_07 c on b.times = c.times where c.times < a.times and b.LF_TireInPositionAtLifter is not null order by b.times asc limit 1)
end as LF_TireInPositionAtLifter
from
cloudDB.F3_07 a
order by a.times desc;

I will play with lag also

6

I was thinking about something like your second one but with 2 table variables, not 3, and a join like a.x = b.x inside the subquery.

LAG is probably a better bet.

7

LAG , we can use for immediate previous one based on partition correct.

But if i want Nth previous means, i can’t use LAG.

Hi Am facing the same issue. Any updated/recent solution?