A rename after a join, aggr, or union produces invalid SQL on PostgreSQL

Offen
#1,676 0 Kommentare 0 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen

Maintainer antworten meist innerhalb von 6 Tagen

Dieses Issue hat noch niemand übernommen.

Bewertung

Schwierigkeit
3/5
Geschätzter Aufwand
1-2 Tage
Anfängerfreundlichkeit
78/100
Issue-Typ
Bug
Klarheit
Klar beschrieben
Aktivitätsstatus
Aktiv
Tech-Stack
mysql, postgresql, python
Bereich
backend, databases

Rechercherichtung

Start in src/datajoint/heading.py at Heading.join() and read how Heading.select() uses table_info to quote renamed attributes. Check the existing regression tests for joins, projections, aggregation, and union, then reproduce the examples on PostgreSQL and MySQL. Done means renames after each listed operation produce valid SQL and return correct rows on both backends.

Vom Indexierungsmodell aus dem Issue-Text verfasst.

Beschreibung

bug

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.

Vorherrschende Sprache
Python
Sterne
198
Forks
103
Ø Merge
6 T. 1 Std.
Gemergte PRs (30 T.)
10

Entwicklungsumgebung

Erste Schritte

  1. Lesen Sie das ganze Issue und danach den Beitragsleitfaden des Projekts.
  2. Schreiben Sie ins Issue, dass Sie es übernehmen — das erspart doppelte Arbeit.
  3. Forken Sie das Repository und arbeiten Sie in einem Branch.
  4. Öffnen Sie einen Pull Request, der die Issue-Nummer nennt.

Mehr aus datajoint/datajoint-python

Alle Issues in datajoint/datajoint-python

Ähnliche Issues

Weitere Issues zu Python

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.