relationship with `secondary` generates incorrect query

[tanj]

I've got a relationship where I use the secondary clause to set the correct sort order. Flask Restless NG v 2.2.4 generates an incorrect query to build the relationship. The incorrect query results in 237 records, while the correct query (expected query) returns 53 records. If I remove the secondary and order_by I get the correct number of records, but not sorted.

Incorrect query

SELECT 
"tTestsheet_1"."ixTestsheet" AS "tTestsheet_1_ixTestsheet",
"tReport"."ixReport" AS "tReport_ixReport" 
FROM "tTestsheet" AS "tTestsheet_1" 
JOIN ("tReport" AS "tReport_1" 
    JOIN "tDevice" AS "tDevice_1" ON "tReport_1"."ixDevice" = "tDevice_1"."ixDevice") ON "tTestsheet_1"."ixTestsheet" = "tReport_1"."ixTestsheet" 

JOIN "tReport" ON "tDevice_1"."ixDevice" = "tReport"."ixDevice" 
WHERE "tTestsheet_1"."ixTestsheet" IN (%(primary_keys_1)s) ORDER BY "tDevice_1"."ixParentDevice" NULLS first;

Expected query

SELECT 
"tTestsheet_1"."ixTestsheet" AS "tTestsheet_1_ixTestsheet",
"tReport"."ixReport" AS "tReport_ixReport" 
FROM "tTestsheet" AS "tTestsheet_1" 
JOIN "tReport" on "tReport"."ixTestsheet" = "tTestsheet_1"."ixTestsheet" 
JOIN "tDevice" ON "tDevice"."ixDevice" = "tReport"."ixDevice" 
WHERE "tTestsheet_1"."ixTestsheet" IN (91) ORDER BY "tDevice"."ixParentDevice" NULLS FIRST

Query with secondary and order_by removed

SELECT "tReport"."ixTestsheet" AS "tReport_ixTestsheet", "tReport"."ixReport" AS "tReport_ixReport"
FROM "tReport"
WHERE "tReport"."ixTestsheet" IN (%(primary_keys_1)s)

Query sqlalchemy generates when accessing the relationship with secondary and order_by

SELECT
"tReport"."ixReport" AS "tReport_ixReport",
"tReport"."ixDevice" AS "tReport_ixDevice",
"tReport"."ixTestsheet" AS "tReport_ixTestsheet",
FROM "tReport"
JOIN "tDevice" ON "tReport"."ixDevice" = "tDevice"."ixDevice"
WHERE %(param_1)s = "tReport"."ixTestsheet" AND "tDevice"."ixDevice" = "tReport"."ixDevice"
ORDER BY "tDevice"."ixParentDevice" NULLS FIRST

Models (simplied to just show relationships)

TTestsheet

class TTestsheet(Base, CRUDMixin):
    __tablename__ = "tTestsheet"

    ixTestsheet = db.Column(db.Integer, primary_key=True)

    report = db.relationship(
        "TReport",
        cascade="all, delete",
        passive_deletes=True,
        back_populates="testsheet",
        overlaps="report, device",
        single_parent=True,
        secondary="join(TReport, TDevice, TReport.ixDevice == TDevice.ixDevice)",
        order_by="nulls_first(TDevice.ixParentDevice)",
    )

TReport

class TReport(Base, CRUDMixin):
    __tablename__ = "tReport"

    ixReport = db.Column(db.Integer, primary_key=True)
    ixDevice = db.Column(
        db.Integer,
        db.ForeignKey("tDevice.ixDevice", ondelete="CASCADE"),
        nullable=False,
    )
    ixTestsheet = db.Column(
        db.Integer,
        db.ForeignKey("tTestsheet.ixTestsheet", ondelete="CASCADE"),
        nullable=False,
    )

TDevice

class TDevice(Base, CRUDMixin):
    __tablename__ = "tDevice"

    ixDevice = db.Column(db.Integer, primary_key=True)
    ixParentDevice = db.Column(
        db.Integer, db.ForeignKey("tDevice.ixDevice", ondelete="CASCADE"), nullable=True
    )

System Details

Python: 3.8.10 flask: 2.0.1 SQLAlchemy: 1.4.22 flask-restless-ng: 2.2.4

[mrevutskyi]

Thank you for the detailed report! I have a couple more questions:

1. What DB do you use? MariaDB does not seem to recognize NULLS FIRST
2. Do you use flask-sqlalchemy ? if yes, what version?

[tanj]

flask-sqlalchemy: 2.5.1 DB is postgres

[mrevutskyi]

Thank you. I see that queries are different, and know how to disable that. But I'm trying to build a test case that will catch that, and so far both queries give me correct number of relationships in the API response.

Is there specific scenario when you get incorrect result? Is it in relationship or in include? and what is minimum required entities in db to reproduce?

[mrevutskyi]

I was able to reproduce, will release a fix soon

[mrevutskyi]

Could you please try v2.2.5 ?

[tanj]

Thank you! You got that sorted so quickly! I really appreciate your responsiveness.

I can confirm that the fix works.

Answers to some of your questions (for completeness)

Is there specific scenario when you get incorrect result? Is it in relationship or in include?

It is in the relationship itself. No include is needed. It is present in all queries.

what is minimum required entities in db to reproduce?

I can provide example data from my database if you still need it, but sounds like you were able to create a test case.

[mrevutskyi]

Thank you for the confirmation! Yes, I was able to create a test case that reproduces it