带OFFSET时DISTINCT列的EXISTS查询在多数据库表现不一致

测试表定义 #

首先创建测试表并插入数据：

CREATE TABLE demo (
   id integer PRIMARY KEY,
   name varchar(10)
);

INSERT INTO demo VALUES (1, 'test');
INSERT INTO demo VALUES (2, 'test');

基础查询验证 #

以下两个查询语义一致，均能正确返回去重后的结果：

SELECT DISTINCT name
FROM demo
WHERE name = 'test';

SELECT DISTINCT name
FROM demo
WHERE name = 'test'
-- 只要数值大于查询结果条数，具体值无关
LIMIT 10
OFFSET 0;

返回结果：

name
----
test

EXISTS子查询的行为差异 #

OFFSET为0的情况 #

以下查询在三个数据库中均返回正确结果：

SELECT EXISTS(
    SELECT DISTINCT name
    FROM demo
    WHERE name = 'test'
    LIMIT 10
    OFFSET 0
);

• SQLite、MySQL返回1
• PostgreSQL返回t

OFFSET为1的情况 #

当OFFSET值大于DISTINCT查询应返回的条数时，不同数据库出现行为差异：

SELECT EXISTS(
    SELECT DISTINCT name
    FROM demo
    WHERE name = 'test'
    LIMIT 10
    OFFSET 1 -- 注意OFFSET值：比DISTINCT查询应返回的条数大1
);

• SQLite、MySQL返回1
• PostgreSQL返回f

从结果来看，PostgreSQL是将OFFSET应用于DISTINCT后的查询结果，符合预期；而SQLite和MySQL中似乎DISTINCT的优先级更高，导致OFFSET未正确作用于去重后的结果。

GROUP BY替代DISTINCT的情况 #

将DISTINCT替换为GROUP BY后，行为再次出现差异：

SELECT EXISTS(
    SELECT name
    FROM demo
    WHERE name = 'test'
    GROUP BY name
    LIMIT 10
    OFFSET 1
);

• SQLite返回正确结果0
• MySQL仍返回错误的1

标准与疑问 #

根据普遍认知，SQL标准规定LIMIT/OFFSET是最后执行的逻辑，这意味着PostgreSQL的行为是符合标准的。请问这是PostgreSQL中曾经修复过的bug吗？

测试环境 #

• SQLite 3.36.0
• MySQL 8.0.28-0ubuntu0.20.04.3
• PostgreSQL 14.2 (Debian 14.2-1.pgdg110+1) on x86_64-pc-linux-gnu，由gcc (Debian 10.2.1-6) 10.2.1 20210110编译，64位

内容的提问来源于stack exchange，提问作者filpa