Repository navigation
A rename after a join, aggr, or union produces invalid SQL on PostgreSQL #1676
Description
A rename after a join, a left join, an aggr, or a union produces SQL that mixes backticks into PostgreSQL identifiers, and the query fails with syntax error at or near "as". MySQL is not affected.
This is the follow-up raised in the reviews of #1643. #1643 keeps table_info in Heading.set_primary_key, and #1657 keeps it in Heading.make_subquery_heading. Heading.join() (src/datajoint/heading.py, the return Heading(attrs, lineage_available=...) line) still builds the joined heading without table_info. A later proj() rename therefore reaches Heading.select with no adapter and falls back to a backtick-quoted name. aggr (through join.heading.set_primary_key) and Union (through arg1.heading.join(arg2.heading)) inherit the joined heading, so they fail the same way.
Reproducible example (PostgreSQL)
@schema
class A(dj.Manual):
definition = """
a : int32
---
x : int32
"""
@schema
class B(dj.Manual):
definition = """
-> A
k : int32
---
b : int32
"""
A.insert([(1, 10), (2, 20), (3, 30)])
B.insert([(1, 1, 100), (1, 2, 101), (2, 1, 200)])| Query | SQL on PostgreSQL |
|---|---|
(A * B).proj(c="b") |
SELECT "a","k",`b` as "c" FROM ... JOIN ... USING ("a") |
B.join(A, left=True).proj(c="x") |
SELECT "a","k",`x` as "c" FROM ... LEFT JOIN ... |
A.aggr(B, "x", n="count(*)").proj(xx="x") |
SELECT "a",`x` as "xx" FROM ... GROUP BY "a" |
A.aggr(B, n="count(*)").proj(m="n") |
SELECT "a",`n` as "m" FROM (SELECT ... GROUP BY "a") as "$c" |
((B & "a=1").proj() + (B & "a=2").proj()).proj(kk="k") |
SELECT "a",`k` as "kk" FROM (... UNION ...) |
Every one raises QuerySyntaxError: syntax error at or near "as" on PostgreSQL 15, on master and on master with #1643 and #1657 applied. All return the correct rows on MySQL.
Suggested fix
Carry table_info through the join, as Heading.select already does:
return Heading(attrs, table_info=self.table_info or other.table_info, lineage_available=...)With this one-line change, all the cases above pass on both backends. A regression test parametrized over both backends should cover a rename after *, a left join, aggr (both a grouped attribute and a computed one), a union, and a subquery over a join.
Activity