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: