[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

[seantleonard]

Can you provide an example of the unintended predicates and unexpected GraphQL response?

[jimmybugraiser]

Hi Sean, thanks for the fast followup. Greatly appreciated. Here is the predicate (best attempt with obfuscation to match the above)

WHERE ([table0].[columnB] != @param1 AND [table0].[columnC] = @param2) AND (([table0].[columnA] < @param3) OR ([table0].[columnA] = @param4 AND [table0].[id] > @param5))
ORDER BY [table0].[columnA] DESC, [table0].[id] ASC
FOR JSON PATH, INCLUDE_NULL_VALUES

When supplying a pagination token (i.e. after: someBase64EncodedPagingToken==), it adds the predicate on columnA to assist in paging. However, this paging appears to only return values where columnA is null for ASC sort and non-null values for column A when columnA is DESC.

With no sorting applied, it returns all values (i.e. null and non-null). There are no JOINs on columnA in the GraphQL query issued.