RANK() OVER (ORDER BY x) reversed in recent update

[chaslain]

Context: The "x" column is a scoring column. The higher the score, the rank is closer to 0.

After building a new version of my application (but not updating the version of the sqlite package), the version was and still is v1.14.22, I noticed that I am getting opposite query ordering as before my application update.

I reverted to the old code and noticed the issue still persisted, but it did not occur in production.

I pulled down the production binary and ran it locally, noticing that the the production version, when ran locally, gave the correct result. The two versions have the exact same query.

I am guessing this is to do with a portion of the C libraries. I notice in the sqlite3 command line, the output matches how it is now.

Was this a bug fix? Is this the correct behavior?

Since even with the old version of the code, I cannot replicate the older behavior, but I can with the older production binary, what on my disk changed to make the change to ORDER BY x DESC rather than ORDER BY x?

[rittneje]

Please provide the full query, sample data set, expected output, and actual output.

Also are you compiling with any build tags?

[chaslain]

@rittneje

        SELECT rank, elo FROM (
        SELECT RANK() OVER (ORDER BY elo) rank, elo, tg_id
        FROM user
        ) x
        WHERE tg_id = 1846306122;

This is the query. The previous functionality was as if there was as DESC inside the ORDER BY.

Example data: (tg_id column and elo respectively)

1846306122|1455
6170115934|1545

Output when query was run in version compiled in June for user id 1846306122: 2|1455 Output when query was run by version compiled today for user id 1846306122: 1|1455

I think the output is correct now, it was supposed to have the DESC inside it from the beginning, but I cannot figure out how recompiling brought about this change.

I have not done anything unique throughout either of the compilation processes with the exception of setting the CGO_ENABLED environment variable to 1 the first time I compiled this project in late May.

Since then, I assume the same shared objects have been linked, including today.

[chaslain]

If it helps, I can provide the binaries, both compiled from identical source code and go mod projects, exhibiting the different outcomes, but they come with a lot of setup to get running.