Description:
Please see https://bugs.mysql.com/bug.php?id=103225 for background and context.
In MySQL 8.0.20 a feature was added which improved sort times in certain cases (e.g. with JSON columns):
- https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html (Bug #30400985, Bug #30804356)
However, this came at a tremendous UX cost -- making reliable JSON data type usage in production nearly impossible.
Unless I'm missing something...the suggestions made in Bug#103225 are not acceptable IMO. There's no reliable way AFAIK for a user to deterministically query the related state to calculate what the necessary sort buffer size would be before executing the query. And even if there were, suggesting that this is somehow a reasonable UX is not great (to say the least):
- Determine when you are executing a query that will be doing a sort using a JSON column
- Try and estimate what size of sort buffer will be required
- Save the current sort_buffer_size session variable
- Set that session variable to the estimated size needed
- Execute your query
- Set the session variable back to its original value
Improving the performance is nice, but of course not if it makes a feature (virtually) unusable. I would enable the older behavior if I could, but there does not appear to be an optimizer switch for this? Aren't behavioral changes made after 8.0 GA supposed to be either backwards compatible or opt-in? This seems like a violation of those guarantees, no?
Please let me know if I'm missing/misunderstanding something or can provide additional information.
Thank you!
How to repeat:
N/A
Suggested fix:
A similar problem was fixed in MariaDB this way: https://github.com/mariadb/server/commit/db56f9b852
