Restricting by a dj.Top expression fails on MySQL (error 1235)

Abierto
#1,669 0 comentarios 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 6 días

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
3/5
Tiempo estimado
1-2 días
Aptitud para principiantes
62/100
Tipo de issue
Error
Claridad
Bien especificado
Estado de actividad
Activo
Stack tecnológico
mysql, postgresql, python, sql
Área
databases

Línea de trabajo

Start at make_condition in condition.py around line 455, where a query-expression restriction is rendered as an IN subquery, and check how a _top adds ORDER BY and LIMIT to that SQL. Run the reproducer from the issue against MySQL 8.0 and PostgreSQL 16 to confirm the error 1235 on MySQL and the count of 6 on PostgreSQL. Done when the query returns 6 on both backends, with the MySQL-compatible form the issue describes (a derived table) as one option.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

bug

Using a dj.Top expression as a restriction (S & (T & dj.Top(...))) fails on MySQL, which does not support LIMIT inside an IN subquery. PostgreSQL returns the correct result.

Reproduced on master @ 3c912c46 against MySQL 8.0 and PostgreSQL 16. Connection via DJ_HOST/DJ_USER/DJ_PASS/DJ_BACKEND. Found while benchmarking subquery pruning (#1665).

Reproducible example
import datajoint as dj

schema = dj.Schema("repro_top")


@schema
class T(dj.Manual):
    definition = """
    t_id : int32
    ---
    x : int32
    y : int32
    """


@schema
class S(dj.Manual):
    definition = """
    -> T
    s_id : int32
    """


T.insert([(i, i, 100 - i) for i in range(10)], skip_duplicates=True)
S.insert([(i, j) for i in range(10) for j in range(2)], skip_duplicates=True)

q = S & (T & dj.Top(3, "x"))  # S rows whose T is among the 3 with the smallest x
print(len(q.to_dicts()))  # expected: 6
Actual

MySQL:

pymysql.err.NotSupportedError: (1235, "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'")

PostgreSQL: 6 (correct).

Expected

6 on both backends.

Cause

make_condition (condition.py:455) renders a query-expression restriction as (attrs) IN (<condition.make_sql(attrs)>). When the condition has a _top, its SQL ends in ORDER BY ... LIMIT n, which MySQL rejects inside IN.

The usual workaround is to wrap it in a derived table, IN (SELECT ... FROM (<subquery with LIMIT>) AS x). MySQL accepts that form, and it is equivalent on PostgreSQL.

Lenguaje dominante
Python
Estrellas
198
Forks
103
Merge medio
6 d 1 h
PR fusionados (30 d)
10

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de datajoint/datajoint-python

Todos los issues de datajoint/datajoint-python

Issues similares

Más issues de Python

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.