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

Aperta
#1,676 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 6 giorni

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
3/5
Tempo stimato
1-2 giorni
Idoneità per principianti
78/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Attiva
Stack tecnologico
mysql, postgresql, python
Ambito
backend, databases

Direzione di ricerca

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.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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.

Lingua principale
Python
Stelle
198
Fork
103
Merge medio
6g 1h
PR unite (30g)
10

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di datajoint/datajoint-python

Tutte le issue di datajoint/datajoint-python

Issue simili

Altre issue su Python

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.