Repository navigation
Joins drop dj.Top: A * (B & dj.Top(n)) and (A & dj.Top(n)) * B ignore the limit #1656
Description
What happens
When an operand of a join is limited with dj.Top, the join ignores the limit. The ORDER BY and LIMIT never reach the SQL, and the result is the join of the full tables. Nothing warns.
This holds for either operand, for both operands, and when dj.Top is combined with another restriction. It also holds for the operations that are built on an inner join: aggr(..., exclude_nonmatching=True) and dj.U(...).aggr(...).
Reproduction
On master (3c912c4), the same on MySQL 8.0 and PostgreSQL 15:
import datajoint as dj
schema = dj.Schema("top_join_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)
"""
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"},
])
top_child = dj.Top(1, order_by="child_id DESC, parent_id") # alone: child (1, 2)
top_parent = dj.Top(1, order_by="parent_id") # alone: parent 1| Query | Expected (parent_id, child_id) |
Actual |
|---|---|---|
Parent * (Child & top_child) |
(1, 2) |
(1, 1), (1, 2), (2, 1) |
(Parent & top_parent) * Child |
(1, 1), (1, 2) |
(1, 1), (1, 2), (2, 1) |
(Parent & top_parent) * (Child & top_child) |
(1, 2) |
(1, 1), (1, 2), (2, 1) |
Parent * ((Child & "color='blue'") & dj.Top(1, order_by="parent_id DESC")) |
(2, 1) |
(1, 2), (2, 1) |
Parent.aggr(Child & top_child, n="count(child_id)", exclude_nonmatching=True) |
{1: 1} |
{1: 2, 2: 1} |
dj.U("color").aggr(Child & top_child, n="count(*)") |
{blue: 1} |
{blue: 2, red: 1} |
The SQL for the first three is the plain join, with no ORDER BY or LIMIT:
SELECT `parent_id`,`child_id`,`color`,`label` FROM `parent` JOIN `child` USING (`parent_id`)The fourth keeps the condition and drops only the limit: ... JOIN child USING (parent_id) WHERE ((color='blue')).
Each operand on its own is correct: Child & top_child returns (1, 2), and Parent & top_parent returns parent 1.
Cause
QueryExpression.restrict stores a dj.Top in _top, not in restriction. join() builds the result from both operands' support, restriction and _joins, and never looks at _top. A limited operand is joined as a bare table, so its limit is lost.
Expected
A join should treat an operand with _top like any other expression that needs a subquery: wrap it, so the ORDER BY/LIMIT applies to that operand before the join. The same rule covers self and other, and inner and left joins. #1655 already does this for the right operand of a left join (#1650), where dj.Top counts as a restriction. Inner joins and the left operand are not covered there.
Related: MySQL rejects a restriction by a limited expression
On MySQL, Parent & (Child & top_child) and Parent - (Child & top_child) raise:
pymysql.err.NotSupportedError: (1235, "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'")
PostgreSQL returns the correct rows (parent 1, and parents 2 and 3). The cause is different: the limited expression is used directly inside IN (...), and MySQL does not accept a LIMIT there. Wrapping it in a derived table (IN (SELECT * FROM (... LIMIT 1) AS t)) is the usual workaround. It can be fixed together with the joins or split into its own issue. (Child & top_child).proj(...) is correct on both backends.
Activity
dimitri-yatsenko commentedon Nov 6, 2026
Anti-restriction cases
I tested every anti-restriction case on
master+ #1655, on MySQL 8.0 and PostgreSQL 15, with the same data as above.A - dj.Top(n)is not supported today, in any position. Each of these raisesDataJointError: Invalid restriction type Top(limit=1, order_by=[...], offset=0)on both backends:Parent - dj.Top(1, order_by="parent_id")Child - top_childParent - (Child - top_child)(Parent - dj.Top(1, order_by="parent_id")) * ChildParent * (Child - top_child)Parent.aggr(Child - top_child, ...), with or withoutexclude_nonmatchingChild.extend(Parent - dj.Top(1, order_by="parent_id"))-turns intorestrict(Not(Top(...))). That path skips theisinstance(restriction, Top)branch inrestrict()and reachesmake_condition, which does not handleTop.The complement is already expressible with
offset:A - dj.Top(n, order_by=o)meansA & dj.Top(None, order_by=o, offset=n). Givenoffsetwith no limit,Topuses an unbounded limit.Parent & dj.Top(None, order_by="parent_id", offset=1)returns parents 2 and 3 on both backends.Proposal: keep
A - dj.Top(...)unsupported, so there is noNot(Top)to implement. Replace the generic "Invalid restriction type" error with one that names the alternative, for example:dj.Top cannot be negated; use dj.Top(None, order_by=..., offset=n) for the rows after the first n.The offset form loses its limit in joins too, the same as above, because it is the same
_top:(Parent & dj.Top(None, order_by="parent_id", offset=1)) * Child(2, 1)(1, 1), (1, 2), (2, 1)Parent * (Child & dj.Top(None, order_by="child_id DESC, parent_id", offset=1))(1, 1), (2, 1)(1, 1), (1, 2), (2, 1)Parent.aggr(Child & dj.Top(None, order_by="child_id DESC, parent_id", offset=1), n="count(child_id)"){1: 1, 2: 1, 3: 0}Anti-restriction by a limited expression (
A - (B & dj.Top(n))) is the MySQL 1235 case from the description. PostgreSQL returns the correct rows (parents 2 and 3); MySQL raisesNotSupportedError: (1235, "... 'LIMIT & IN/ALL/ANY/SOME subquery'"). Its restriction counterpartA & (B & dj.Top(n))does the same.The fix for this issue should test the offset form on either operand of
*,aggr(..., exclude_nonmatching=True)anddj.U.aggr, and bothA & (B & dj.Top(n))andA - (B & dj.Top(n))on MySQL. IfA - dj.Top(...)stays unsupported, it should also test that the error names the offset form.