Subquery error when using DataList::subtract() on a query with a limit

[kinglozzer]

Need to create a simple test case for this when I get time, but for now... pseudo-code:

$items = $this->ManyManyItems()->sort('SortOrder')->limit($limit);
// SQL error: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
$otherItems = SomeItems::get()->subtract($items);

Example of a raw query that’s failing:

SELECT DISTINCT "SiteTree_Live"."ClassName", "SiteTree_Live"."Created", "SiteTree_Live"."LastEdited", "SiteTree_Live"."URLSegment", "SiteTree_Live"."Title", "SiteTree_Live"."MenuTitle", "SiteTree_Live"."Content", "SiteTree_Live"."MetaDescription", "SiteTree_Live"."ExtraMeta", "SiteTree_Live"."ShowInMenus", "SiteTree_Live"."ShowInSearch", "SiteTree_Live"."Sort", "SiteTree_Live"."HasBrokenFile", "SiteTree_Live"."HasBrokenLink", "SiteTree_Live"."ReportClass", "SiteTree_Live"."CanViewType", "SiteTree_Live"."CanEditType", "SiteTree_Live"."Priority", "SiteTree_Live"."MetaTitle", "SiteTree_Live"."Version", "SiteTree_Live"."ParentID", "Page_Live"."AsideImageID", "BlogPost_Live"."PublishDate", "BlogPost_Live"."IsPublic", "BlogPost_Live"."FeaturedImageID", "BlogPost_Live"."PublisherID", "SiteTree_Live"."ID", CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END AS "RecordClassName"
FROM "SiteTree_Live"
LEFT JOIN "Page_Live" ON "Page_Live"."ID" = "SiteTree_Live"."ID"
LEFT JOIN "BlogPost_Live" ON "BlogPost_Live"."ID" = "SiteTree_Live"."ID"
WHERE ("SiteTree_Live"."ClassName" = 'BlogPost')
    AND ("BlogPost_Live"."IsPublic" = '1')
    AND ("SiteTree_Live"."ID" NOT IN
        SELECT DISTINCT "SiteTree_Live"."ID"
        FROM "SiteTree_Live"
        LEFT JOIN "Page_Live" ON "Page_Live"."ID" = "SiteTree_Live"."ID"
        LEFT JOIN "BlogPost_Live" ON "BlogPost_Live"."ID" = "SiteTree_Live"."ID"
        INNER JOIN "HomePage_PinnedNews" ON "HomePage_PinnedNews"."BlogPostID" = "SiteTree_Live"."ID"
        WHERE ("HomePage_PinnedNews"."HomePageID" = '1')
            AND ("SiteTree_Live"."ClassName" IN ('BlogPost','EventBlogPost'))
            AND (PublishDate < '2015-03-30 16:15:49')
        LIMIT 2))
    AND ("SiteTree_Live"."ClassName" IN ('BlogPost','EventBlogPost'))
    AND (PublishDate < '2015-03-30 16:15:49')
ORDER BY "PublishDate" DESC
LIMIT 2

[kinglozzer]

The issue is actually because I’m doing DataList::subtract($items) further on, which triggers a subquery. Not sure if this is something we should be fixing or not - it’s a bit of an edge-case, but then again the framework shouldn’t really be generating invalid SQL.

[kinglozzer]

Just confirming this is still an issue. Example that triggers the issue on a clean install:

$exclude = SiteTree::get()->limit(1);
$pages = SiteTree::get()->subtract($exclude);

[dhensby]

This seems like a slightly strange use case. When do you want to remove items from a list which is limited to only a few results? Wouldn't you want to exclude the entire set?

[kinglozzer]

Okay thinking about it again, perhaps this is something we can’t fix in core. The error is MySQL-specific anyway and says “This version of MySQL doesn't yet support...”, and the docs also say “MySQL does not support LIMIT in subqueries for certain subquery operators”. All very non-specific!