Generated table alias is not being used correctly in the rest of the query

[vitorcx]

Describe the bug When a make a complex filter, i get this error:

asyncpg.exceptions.UndefinedTableError: invalid reference to FROM-clause entry for table "sc9ce3_agenda"

Examining the generated SQL query, I notice that the references to columns of the table that received an alias, still include the schema.

SELECT * -- I've replaced all columns with "*" to make this text smaller
FROM exampleschema.tableA
LEFT OUTER JOIN exampleschema.agenda AS sc9ce3_agenda
ON sc9ce3_agenda.id=tableA.agenda_id
JOIN (SELECT tableA.id
		FROM exampleschema.tableA
		LEFT OUTER JOIN exampleschema.agenda AS sc9ce3_agenda -- ALIAS BEING DEFINED (SHOULDN'T BE USED WITHOUT "SHCEMA." IN THE NEXT CLAUSES ?)
		ON sc9ce3_agenda.id=tableA.agenda_id --SCHEMA.ALIAS_TABLE.COLUM (Error) shouldn't be: ALIAS_TABLE_COLUMN ?
		WHERE exampleschema.tableA.excluido = false
		AND exampleschema.sc9ce3_agenda.data_inicio_agenda <= '2022-01-17T11:25:14.867901' --SCHEMA.ALIAS_TABLE.COLUM (Error)
		AND exampleschema.sc9ce3_agenda.data_fim_agenda >= '2022-01-17T11:25:14.867901' --SCHEMA.ALIAS_TABLE.COLUM (Error)
		AND NOT (exampleschema.tableA.excluido = false
		AND exampleschema.sc9ce3_agenda.data_inicio_agenda <= '2022-01-17T11:25:14.867901' --SCHEMA.ALIAS_TABLE.COLUM (Error)
		AND exampleschema.sc9ce3_agenda.data_fim_agenda >= '2022-01-17T11:25:14.867901' --SCHEMA.ALIAS_TABLE.COLUM (Error)
		AND exampleschema.tableA.data_inabilitada = '2022-01-17T11:25:14.867901')
		GROUP BY tableA.id
		ORDER BY min(exampleschema.tableA.id), min(sc9ce3_exampleschema.agenda.id) -- ALIAS_SCHEMA.TABLE.COLUMN (Error) Alias before schema. It should work?
 		LIMIT :param_1) AS limit_query 
ON limit_query.id=tableA.id 
WHERE exampleschema.tableA.excluido = false
AND exampleschema.sc9ce3_agenda.data_inicio_agenda <= '2022-01-17T11:25:14.867901' --SCHEMA.ALIAS_TABLE.COLUM (Error)
AND exampleschema.sc9ce3_agenda.data_fim_agenda >= '2022-01-17T11:25:14.867901' --SCHEMA.ALIAS_TABLE.COLUM (Error)
AND NOT (exampleschema.tableA.excluido = false
AND exampleschema.sc9ce3_agenda.data_inicio_agenda <= '2022-01-17T11:25:14.867901' --SCHEMA.ALIAS_TABLE.COLUM (Error)
AND exampleschema.sc9ce3_agenda.data_fim_agenda >= '2022-01-17T11:25:14.867901' --SCHEMA.ALIAS_TABLE.COLUM (Error)
AND exampleschema.tableA.data_inabilitada = '2022-01-17T11:25:14.867901')
ORDER BY tableA.id , sc9ce3_agenda.id;

Sorry for the big query, I added some comments to the relevant lines.

To Reproduce Model definitions:

database = databases.Database(settings.db_url)
metadata = sqlalchemy.MetaData(schema="exampleschema")

class BaseMeta(ormar.ModelMeta):
    metadata = metadata
    database = database

class Agenda(ormar.Model):
    class Meta(BaseMeta):
        tablename = "agenda"
    id: int = ormar.BigInteger(primary_key=True)
    nome: str = ormar.Text(nullable=True)
    descricao: str = ormar.Text(nullable=True)
    data_inicio_agenda: datetime.date = ormar.Date(nullable=True)
    data_fim_agenda: datetime.date = ormar.Date(nullable=True)
    agenda_publica: bool = ormar.Boolean(nullable=True)
    criado_em: datetime = ormar.DateTime(nullable=False, timezone=True)
    atualizado_em: datetime = ormar.DateTime(nullable=False, timezone=True)
    excluido: bool = ormar.Boolean(nullable=True)
    excluido_em: datetime = ormar.DateTime(nullable=True, timezone=True)

class TableA(ormar.Model):
    class Meta(BaseMeta):
        tablename = "tableA"
    id: int = ormar.BigInteger(primary_key=True)
    agenda_id: Agenda = ormar.ForeignKey(Agenda, nullable=True) --FOREIGN KEY TO AGENDA
    data_inabilitada: datetime.date = ormar.Date(nullable=True)
    criado_em: datetime = ormar.DateTime(nullable=False, timezone=True)
    atualizado_em: datetime = ormar.DateTime(nullable=False, timezone=True)
    excluido: bool = ormar.Boolean(nullable=True)
    excluido_em: datetime = ormar.DateTime(nullable=True, timezone=True)

Python/ormar query:

filtro_data = {
            **({'agenda_id__nome__icontains': nome} if nome is not None else {}),
            **({'agenda_id__agenda_publica'} if agenda_publica is not None else {}),
            'excluido': False,
        }
data_atual = datetime.today()
agendas = await TableA.objects.prefetch_related('agenda_id')\
                .filter(**filtro_data)\
                .filter(agenda_id__data_inicio_agenda__lte=data_atual, agenda_id__data_fim_agenda__gte=data_atual)\
                .exclude(data_inabilitada=data_atual)\
                .all()

Expected behavior Expected the following query to be generated:

SELECT * -- I've replaced all columns with "*" to make this text smaller
FROM exampleschema.tableA
LEFT OUTER JOIN exampleschema.agenda AS sc9ce3_agenda
ON sc9ce3_agenda.id=tableA.agenda_id
JOIN (SELECT tableA.id
		FROM exampleschema.tableA
		LEFT OUTER JOIN exampleschema.agenda AS sc9ce3_agenda -- ALIAS BEING DEFINED (SHOULDN'T BE USED WITHOUT "SHCEMA." IN THE NEXT CLAUSES ?)
		ON sc9ce3_agenda.id=tableA.agenda_id --SCHEMA.ALIAS_TABLE.COLUM (Error) shouldn't be: ALIAS_TABLE_COLUMN ?
		WHERE exampleschema.tableA.excluido = false
		AND sc9ce3_agenda.data_inicio_agenda <= '2022-01-17T11:25:14.867901'  --ALIAS_TABLE.COLUM (Without schema)
		AND sc9ce3_agenda.data_fim_agenda >= '2022-01-17T11:25:14.867901' --ALIAS_TABLE.COLUM (Without schema)
		AND NOT (exampleschema.tableA.excluido = false
		AND sc9ce3_agenda.data_inicio_agenda <= '2022-01-17T11:25:14.867901'  --ALIAS_TABLE.COLUM (Without schema)
		AND sc9ce3_agenda.data_fim_agenda >= '2022-01-17T11:25:14.867901'  --ALIAS_TABLE.COLUM (Without schema)
		AND exampleschema.tableA.data_inabilitada = '2022-01-17T11:25:14.867901')
		GROUP BY tableA.id
		ORDER BY min(exampleschema.tableA.id), min(sc9ce3_agenda.id) -- ALIAS_TABLE.COLUMN (Without schema)
 		LIMIT :param_1) AS limit_query 
ON limit_query.id=tableA.id 
WHERE exampleschema.tableA.excluido = false
AND sc9ce3_agenda.data_inicio_agenda <= '2022-01-17T11:25:14.867901' --ALIAS_TABLE.COLUM (Without schema)
AND sc9ce3_agenda.data_fim_agenda >= '2022-01-17T11:25:14.867901' --ALIAS_TABLE.COLUM (Without schema)
AND NOT (exampleschema.tableA.excluido = false
AND sc9ce3_agenda.data_inicio_agenda <= '2022-01-17T11:25:14.867901' --ALIAS_TABLE.COLUM (Without schema)
AND sc9ce3_agenda.data_fim_agenda >= '2022-01-17T11:25:14.867901' --ALIAS_TABLE.COLUM (Without schema)
AND exampleschema.tableA.data_inabilitada = '2022-01-17T11:25:14.867901')
ORDER BY tableA.id , sc9ce3_agenda.id;

Versions:

• Database backend used (mysql/sqlite/postgress): postgres
• Python 3.9.9
• ormar 0.10.23
• pydantic 1.8.2
• fastapi 0.70.1

[collerek]

Yes, the schema is not supported at that moment, related to #287 Can you check after installing from git with the branch from https://github.com/collerek/ormar/issues/287#issuecomment-1007319230

[LonelyVikingMichael]

I'm getting the same error, also using a different Postgres schema. So far its only occuring when chaining paginate() or limit() on a query containing fetch_related()

@collerek I did install the branch you mentioned above, this introduced a different set of issues, but it may just be due to the way I've set things up.

It looks like the patch by @alexreznikoff requires declaring the schema on the Model Meta as opposed to sqlalchemy.MetaData(schema='non_default_schema')

I tried going this route, but it caused the intersection entity created by a ManyToMany field not detecting the parent tables during tests when calling metadata.create_all(engine) - even when manually declared with the through_model=MyModel parameter