azure - 宇宙: DISTINCT results with JOIN and ORDER BY

Question

我正在尝试编写一个查询，该查询使用 JOIN 对数组中的位置执行地理空间匹配。我让它工作了，但添加了 DISTINCT 以便消除重复(查询 A):

SELECT DISTINCT VALUE
    u
FROM
    u
JOIN loc IN u.locations
WHERE
    ST_WITHIN(
        {'type':'Point','coordinates':[loc.longitude,loc.latitude]},
        {'type':'Polygon','coordinates':[[[-108,-43],[-108,-40],[-110,-40],[-110,-43],[-108,-43]]]})

但是，我随后发现不支持将 DISTINCT 与延续标记结合使用，除非您还添加 ORDER BY:

System.ArgumentException: Distict query requires a matching order by in order to return a continuation token. If you would like to serve this query through continuation tokens, then please rewrite the query in the form 'SELECT DISTINCT VALUE c.blah FROM c ORDER BY c.blah' and please make sure that there is a range index on 'c.blah'. 

所以我尝试像这样添加ORDER BY(查询B):

SELECT DISTINCT VALUE
    u
FROM
    u
JOIN loc IN u.locations
WHERE
    ST_WITHIN(
        {'type':'Point','coordinates':[loc.longitude,loc.latitude]},
        {'type':'Polygon','coordinates':[[[-108,-43],[-108,-40],[-110,-40],[-110,-43],[-108,-43]]]})
ORDER BY
    u.created

问题是，DISTINCT 似乎不再生效，因为它返回了两次相同的记录。

要重现此情况，请使用以下数据创建一个文档:

{
    "id": "b6dd3e9b-e6c5-4e5a-a257-371e386f1c2e",
    "locations": [
        {
            "latitude": -42,
            "longitude": -109
        },
        {
            "latitude": -42,
            "longitude": -109
        }
    ],
    "created": "2019-03-06T03:43:52.328Z"
}

然后运行上面的查询 A。尽管两个位置都与谓词匹配，但您将得到一个结果。如果删除 DISTINCT，您将获得相同的文档两次。

现在运行查询 B，您将看到它两次返回同一文档，尽管有 DISTINCT 子句。

我在这里做错了什么？

Answer 1

确实重现了您的问题，根据我的研究，这似乎是 cosmos db distinct 查询中的缺陷。请引用此链接:Provide support for DISTINCT .

This feature is broke in the data explorer. Because cosmos can only return 100 results per page at a time, the distinct keyword will only apply to a single page. So, if your result set contains more than 100 results, you may still get duplicates back - they will simply be on separately paged result sets.

您可以描述您自己的情况并对这个反馈案例进行投票。