dj.U(...).aggr gives wrong results or fails when the group is a union, an aggregation, or a computed projection
维护者通常 6 天内回复
还没有人认领这个 Issue。
评估
- 难度
- 4/5
- 预计耗时
- 3-5 天
- 新手友好度
- 72/100
- Issue 类型
- 缺陷
- 描述清晰度
- 描述清楚
- 活跃度
- 活跃
- 技术栈
- mysql, postgresql, python
调研方向
Read U.aggr in src/datajoint/expression.py and compare its construction with Aggregation.create and the regular aggr path. Reproduce the listed cases on MySQL or PostgreSQL, including unions, aggregations, and computed projections; the issue says group.make_subquery() yields the expected results. Done means those cases return the expected values without changing the current SQL for plain tables and joins.
由索引模型根据 Issue 内容生成。
描述
What happens
dj.U(...).aggr(group, ...) should aggregate over the rows of group. When group is a plain table, a join, or a restriction of these, it does. When group is a union, an aggregation, or a projection with a computed attribute, it isn't aggregating the rows that group returns:
- Union: the second operand is dropped without a warning.
- Aggregation: the inner grouping is lost. Depending on the computation, the result is a wrong count with no error, an SQL error, or a missing column. A restriction applied before the inner grouping is dropped too.
- Projection with a computed attribute, aggregated per attribute: SQL error.
A dj.U(...) & expr group, whose DISTINCT is dropped in the same place, is covered by #1660 together with the joins that drop it.
The regular aggr (Parent.aggr(group, ...)) handles the same groups correctly.
Reproduction
On master (3c912c46), the same on MySQL 8.0 and PostgreSQL:
import datajoint as dj
schema = dj.Schema("u_aggr_repro")
@schema
class Parent(dj.Manual):
definition = """
parent_id : int32
---
label : varchar(8)
"""
@schema
class Child(dj.Manual):
definition = """
-> Parent
child_id : int32
---
color : varchar(8)
"""
@schema
class ParentInfo(dj.Manual):
definition = """
-> Parent
---
info : varchar(8)
"""
Parent.insert([
{"parent_id": 1, "label": "a"},
{"parent_id": 2, "label": "b"},
{"parent_id": 3, "label": "a"},
])
Child.insert([
{"parent_id": 1, "child_id": 1, "color": "red"},
{"parent_id": 1, "child_id": 2, "color": "blue"},
{"parent_id": 2, "child_id": 1, "color": "blue"},
])
ParentInfo.insert([
{"parent_id": 1, "info": "x"},
{"parent_id": 2, "info": "y"},
{"parent_id": 3, "info": "z"},
])
u = (Parent.proj() & "parent_id=1") + (ParentInfo.proj() & "parent_id > 1")
len(u) # 3
dj.U().aggr(u, n="count(*)").fetch1("n") # 1, expected 3
per_parent = Parent.aggr(Child, n="count(child_id)") # n: parent 1 -> 2, 2 -> 1, 3 -> 0
dj.U().aggr(per_parent, total="sum(n)").fetch1("total") # error, expected 3
dj.U().aggr(per_parent, k="count(*)").fetch1("k") # 4, expected 3
| Query | Expected | MySQL 8.0 | PostgreSQL |
|---|---|---|---|
dj.U().aggr(u, n="count(*)") |
3 |
1 |
1 |
dj.U("parent_id").aggr(u, n="count(*)") |
{1: 1, 2: 1, 3: 1} |
{1: 1} |
{1: 1} |
dj.U().aggr(per_parent, total="sum(n)") |
3 |
error 1140 (only_full_group_by) |
GroupingError |
dj.U("label").aggr(Parent.aggr(Child, "label", n="count(child_id)"), total="sum(n)") |
{a: 2, b: 1} |
error 1055 (only_full_group_by) |
GroupingError |
dj.U().aggr(per_parent, k="count(*)") |
3 |
4 |
4 |
dj.U().aggr((Parent & "parent_id < 3").aggr(Child, n="count(child_id)"), k="count(*)") |
2 |
4 |
4 |
dj.U().aggr(per_parent & "n > 0", k="count(*)") |
2 |
Unknown column 'n' in 'where clause' |
column "n" does not exist |
dj.U("parent_id").aggr(Child.proj(score="child_id * 10"), m="max(score)") |
{1: 20, 2: 10} |
error 1055 (only_full_group_by) |
GroupingError |
Four of the eight return a wrong number with no error.
The SQL shows what is lost. For the union, the parent_info side is gone:
SELECT count(*) as `n` FROM (SELECT `parent_id` FROM `parent` WHERE ( (parent_id=1))) as `$0`
For the aggregation, the inner GROUP BY parent_id is gone, so count(*) counts the joined rows (4) instead of the parents (3), and sum(n) fails:
SELECT count(*) as `k` FROM `parent` LEFT JOIN `child` USING (`parent_id`)
SELECT sum(n) as `total` FROM (SELECT `parent_id`,count(child_id) as `n` FROM `parent` LEFT JOIN `child` USING (`parent_id`)) as `$1`
These cases work:
dj.U().aggr(Child.proj(score="child_id * 10"), m="max(score)")returns20on both backends. With no grouping attribute, the inner query has noGROUP BYto break.- Grouping by a computed or renamed attribute works:
dj.U("score").aggr(Child.proj(score="child_id * 10"), n="count(*)")anddj.U("pid").aggr(Child.proj(pid="parent_id"), n="count(*)"). - A union that is restricted again,
dj.U().aggr(u & "parent_id > 1", n="count(*)"), returns2. Restricting a union already wraps it in a subquery. Parent.aggr(u, n="count(*)")andParent.aggr(Child.proj(score="child_id * 10"), m="max(score)")are correct.
Cause
U.aggr (src/datajoint/expression.py:1532) builds the aggregation from three parts of the group and sets its own grouping (lines 1567–1573):
result._heading = group.heading.set_primary_key(list(self.primary_key))
result._support = group.support
result._joins = group._joins
result._left_restrict = group.restriction
result._grouping_attributes = list(self.primary_key)
These three parts describe a table, a join, or a restriction completely. Other groups lose something:
- Union:
Union.createputs both operands in_support(line 1429) and has no_joins.from_clause()(lines 105–128) pairs each entry of_supportafter the first with an entry of_joins. With no_joins, only the first operand reaches theFROMclause. - Aggregation: its
_supportand_joinsare the joined tables. Itsrestrictionholds theHAVINGconditions, and its_left_restrictand_grouping_attributeshold theWHEREandGROUP BY.U.aggrkeeps the tables and movesHAVINGintoWHERE, wherenis not a column yet. It drops the innerWHEREandGROUP BY.nkeeps its expression,count(child_id), in the heading. - Computed attribute:
scorekeeps its expression in the aggregation's heading. Becausemax(score)refers to a computed attribute,proj()wraps the aggregation in a subquery (line 613). The inner query then selects all ofChild's columns underGROUP BY parent_id.
The regular aggr covers these groups. Aggregation.create wraps a group with computed attributes in a subquery (line 1336), and join() wraps aggregation operands and operands built from several tables (lines 404–421). Before 2.0, U.aggr called Aggregation.create. Commit ba81cb8f (first released in 2.0.0) replaced that call with the direct construction above, which also skips the computed-attribute check.
Expected
The values in the "Expected" column above. In U.aggr, wrap the group in a subquery unless it is a table, a join, or a restriction of these. That covers a Union, an Aggregation, and a group with computed attributes (group.heading.new_attributes); a DISTINCT group (group._distinct) is #1660. Calling group.make_subquery() by hand already gives the expected value for every row of the table, on both backends. Plain tables and joins should keep the current SQL with no subquery.
PR #1657 adds the same wrapping in this function for a group limited by dj.Top (if group._top is not None: group = group.make_subquery()) and lists this bug under "Not changed here". The fix can extend that condition. Neither change alone covers the other's cases.
- 主要语言
- Python
- 星标
- 198
- 派生
- 103
- 平均合并
- 6 天 1 小时
- 30 天内合并 PR
- 10
环境准备
- 提供 Dockerfile 或 Docker Compose 文件
- 没有 Pull Request 模板
- 阅读贡献指南
从这里开始
- 先读完整个 Issue,再读项目的贡献指南。
- 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
- Fork 仓库,在一个分支上完成修改。
- 提交 Pull Request,并在描述里引用这个 Issue 编号。
datajoint/datajoint-python 的其他 Issue
-
bug
难度 2/5 1-3 小时 新手友好度 78/100
datajoint/datajoint-python#1703 ·
维护者通常 6 天内回复
-
bug
难度 2/5 1-3 小时 新手友好度 78/100
datajoint/datajoint-python#1702 ·
维护者通常 6 天内回复
-
bug
难度 2/5 1-3 小时 新手友好度 78/100
datajoint/datajoint-python#1701 ·
维护者通常 6 天内回复
-
bug
难度 2/5 1-3 小时 新手友好度 74/100
datajoint/datajoint-python#1691 ·
维护者通常 6 天内回复
-
dependencies needs-discussion
难度 2/5 1-3 小时 新手友好度 68/100
datajoint/datajoint-python#1687 ·
维护者通常 6 天内回复
查看 datajoint/datajoint-python 的全部 Issue
相似的 Issue
-
needs-triage
难度 1/5 1-3 小时 新手友好度 85/100
gke-labs/kube-agents#6620 ·
维护者通常 1 天内回复
-
bug
难度 2/5 1-3 小时 新手友好度 75/100
open-webui/open-webui#34719 · 2 条评论 ·
维护者通常 1 天内回复
-
cognition
难度 2/5 1-3 小时 新手友好度 82/100
维护者通常 1 天内回复
-
One non-object JSON record makes the session catalog exit可能已有人在做 @he-yufeng 今天认领。 未关闭
难度 2/5 1-3 小时 新手友好度 68/100
维护者通常 1 天内回复
-
难度 1/5 1 小时以内 新手友好度 78/100
collabnix/awesome-mcp-lists#433 ·
维护者通常 4 天内回复