Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
chaslain opened this issue Oct 5, 2024 · 3 comments
Open

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

chaslain opened this issue Oct 5, 2024 · 3 comments

Comments

@chaslain
Copy link

chaslain commented Oct 5, 2024

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
Copy link
Collaborator

rittneje commented Oct 5, 2024

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

Also are you compiling with any build tags?

@chaslain
Copy link
Author

chaslain commented Oct 5, 2024

@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
Copy link
Author

chaslain commented Oct 5, 2024

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants