Query `containsAllStartingWith` returns wrong results with Postgres when the number of matching array elements differs from the number of prefixes

未关闭 适合新手
  • 哪里不对？
#10,871 1 条评论 0 个 reaction 已指派 0 人 在 GitHub 查看

维护者通常 1 天内回复

还没有人认领这个 Issue。

评估

难度
2/5
预计耗时
1-3 小时
新手友好度
84/100
Issue 类型
缺陷
描述清晰度
描述清楚
活跃度
活跃
技术栈
javascript, postgresql
领域
backend, database

调研方向

从 src/Adapters/Storage/Postgres/sql/array/contains-all-regex.sql 开始，并将其匹配逻辑与 issue 中描述的 MongoDB 语义进行比较。然后找到现有的 spec containsAllStartingWith single regex value should return corresponding matching results，检查多个数组元素匹配一个前缀，或一个元素匹配多个前缀的情况。完成标准是 Postgres 在 issue 的三个示例中与 MongoDB 得出相同结果。

由索引模型根据 Issue 内容生成。

描述

New Issue Checklist
Issue Description

With the Postgres adapter, containsAllStartingWith (REST: $all with ^\Q...\E prefix regexes on an Array field) is implemented by the SQL helper array_contains_all_regex in src/Adapters/Storage/Postgres/sql/array/contains-all-regex.sql. The helper counts the array elements that match any of the patterns and compares that count with the number of patterns:

SELECT RES.CNT = jsonb_array_length("values")
FROM (SELECT COUNT(*) as CNT FROM jsonb_array_elements_text("array") as elt
      WHERE elt LIKE ANY (SELECT jsonb_array_elements_text("values"))) as RES

This is not the semantics of the operator on MongoDB ($all: [/^\Qa\E/, ...]: every pattern must match at least one element). The count comparison gives wrong results whenever the number of matching elements differs from the number of patterns:

  1. False negative when more elements match than patterns were given: ['aaa', 'ab'] with prefix a → 2 matching elements ≠ 1 pattern → false.
  2. False negative when one element satisfies several patterns: ['aaa'] with prefixes a and aa → 1 ≠ 2 → false.
  3. False positive when several elements match one pattern while another pattern matches nothing: ['aaa', 'abb'] with prefixes a and b → 2 = 2 → true, although no element starts with b.

MongoDB returns the opposite result in all three cases. The existing spec containsAllStartingWith single regex value should return corresponding matching results passes on Postgres only because its test data has exactly one matching element per object.

Steps to reproduce
const object = new Parse.Object('TestObject');
object.set('strings', ['aaa', 'ab']);
await object.save();

const query = new Parse.Query('TestObject');
query.containsAllStartingWith('strings', ['a']);
const results = await query.find();

REST equivalent: where={"strings":{"$all":[{"$regex":"^\\Qa\\E"}]}}.

SQL-level check against the test database (the helper is created by the adapter):

SELECT array_contains_all_regex('["aaa","ab"]'::jsonb, '["a%"]'::jsonb);       -- false, expected true
SELECT array_contains_all_regex('["aaa"]'::jsonb, '["a%","aa%"]'::jsonb);      -- false, expected true
SELECT array_contains_all_regex('["aaa","abb"]'::jsonb, '["a%","b%"]'::jsonb); -- true, expected false
Actual Outcome

Postgres: the query in the example returns no results; MongoDB returns the object. The SQL helper returns the values noted in the comments above.

Expected Outcome

The same results as MongoDB: an object matches when every prefix matches at least one array element, independent of how many elements match. For example, count the patterns that have at least one matching element and compare that count with the number of patterns.

Environment

Server

  • Parse Server version: 9.11.1-alpha.2
  • Operating system: macOS 27.0.1
  • Local or remote host: local

Database

  • System (MongoDB or Postgres): Postgres
  • Database version: PostgreSQL 16 with PostGIS 3.5 (postgis/postgis:16-3.5)
  • Local or remote host: local Docker

Client

  • SDK (iOS, Android, JavaScript, PHP, Unity, etc): JavaScript / REST
  • SDK version: 8.7.0
Logs

n/a

主要语言
JavaScript
星标
21.4k
派生
4.8k
平均合并
7 小时 8 分钟
30 天内合并 PR
65

环境准备

从这里开始

  1. 先读完整个 Issue，再读项目的贡献指南。
  2. 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
  3. Fork 仓库，在一个分支上完成修改。
  4. 提交 Pull Request，并在描述里引用这个 Issue 编号。

parse-community/parse-server 的其他 Issue

查看 parse-community/parse-server 的全部 Issue

相似的 Issue

更多 JavaScript Issue

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。