Bug #100317 Sorting changed in since two patch versions

• Submitted: 24 Jul 2020 12:53
• Modified: 24 Jul 2020 13:52
• Reporter: Soner Sayakci
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.21
• OS: Any
• CPU Architecture: x86
• Tags: sorting

[24 Jul 2020 12:53] Soner Sayakci

Description:
Our CI Fails with some tests about sorting. The result is wrong sorted. The test suite runs multiple mysql versions:

MySQL 5.7, MariaDB 10.1-10.4, MySQL 8.0.19 => all sorting tests passes as expected
MySQL 8.0.20, 8.0.21 fails. I didn't found anything in the Changelog for that.

My guess:

When multiple items have the same sort in older versions the insert order decided the sort. And since 8.0.20 its something else.

How to repeat:
Query:
SELECT s0_.name         AS name_0,
       s0_.onclick      AS onclick_1,
       s0_.class        AS class_2,
       s0_.controller   AS controller_3,
       s0_.action       AS action_4,
       s0_.shortcut     AS shortcut_5,
       s0_.id           AS id_6,
       s0_.position     AS position_7,
       s0_.active       AS active_8,
       s0_.pluginID     AS pluginID_9,
       s0_.parent       AS parent_10,
       s0_.content_type AS content_type_11,
       s0_.pluginID     AS pluginID_12,
       s0_.parent       AS parent_13
FROM s_core_menu s0_
         LEFT JOIN s_core_plugins s1_ ON s0_.pluginID = s1_.id
WHERE s0_.active = 1
  AND (s0_.pluginID IS NULL OR s1_.active = 1)
ORDER BY s0_.parent ASC, s0_.position ASC

(See Attachment for the Table)

Since 8.0.20 it starts with Inhalte
All other versions are starting with Artikel

[24 Jul 2020 12:53] Soner Sayakci

Table Dump of s_core_menu and s_core_plugin

Attachment: shopware (1).sql (application/sql, text), 20.91 KiB.

[24 Jul 2020 13:52] MySQL Verification Team

Hi Mr. Sayakci,

Thank you for your bug report.

However, this is not a bug.

Simply, column `name` is not included in the sort order, so its ordering is irrelevant. 

There was a change in 8.0.20 that improves performance, which make you see a change. In SQL, if you want certain column to be in some sort order, you have to include it appropriately.

Not a bug.