Sqlalchemy Why does order_by return different data and expectations when used with paging operations?

whether you use slice or limit/offset to do paging, there will be problems as long as you sort and then paginate. Why?
there is a problem when PS: only shows that the amount of data per page is less than the total amount of data. Why?

result = session_read.query(cls).order_by(cls.guild_activity.desc()).slice((page_index - 1) * page_size, page_index * page_size).all()

result = session_read.query(cls).order_by(cls.guild_activity.desc()).offset((page_index - 1) * page_size).limit(page_size).all()

Dec.29,2021

I encountered exactly the same problem that has now been solved. The cause of this problem is not caused by sqlalchemy, you can set SQLALCHEMY_ECHO = True to analyze the SQL statement to know that there is no problem. The database I use is the sort of mysql,mysql that will cause the above problem if your sort column has many of the same values. Specific reasons can be found in this article, thanks to the great god of this article. https://www.jianshu.com/p/1e8.


because the primary key is not used

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1be646f-317c3.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1be646f-317c3.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?