[Bug]: GraphQL pagination with order by statements omits values

[jimmybugraiser]

What happened?

We've noticed a potential issue with the SQL generation within the Data API Builder, specifically when handling GraphQL queries that include pagination and sorting. The SQL output appears to introduce unintended predicates affecting the sorting column. These predicates seem to cause records with null values in the sorting column to be excluded from the results, varying with the sort order (ascending showing null values and with descending excluding null values).

Version

1.1.7

What database are you using?

Azure SQL

What hosting model are you using?

AppService

Which API approach are you accessing DAB through?

GraphQL

Relevant log output

query SomeQuery {
    someentityA(first: 10, orderBy: {columnA:DESC}, filter: {
            and: [{ columnB: { neq: "EntityA" } }
            { or: [ { columnC: { eq: "CountryB" } } ] }
        ]}) {
            items {
                id
                columnD
                columnE
                columnF
                columnG
                columnH
                columnI
                columnJ
                columnK
                columnL
                columnM
                columnN
                columnO
                someentityB(first: 10000) {
                    items {
                        id
                        columnP
                        someentityC(first: 10000) {
                            items {
                                id
                                columnQ
                                columnR
                                columnS
                                columnT
                                columnU
                                columnV
                                // ... rest of the query
                            }
                        }
                    }
                }
            }
}

Code of Conduct

• [X] I agree to follow this project's Code of Conduct