Unchanged order of layered navigation filters not the same as in admin

[rbayet]

In CE 2.3.0-beta32, the ability to change the relative position of layered navigation filters for a category works as expected but when no changes are applied, the default order of attributes show in admin is not the one applied to the attributes related layered navigation filters.

Preconditions

Magento Version : CE 2.3.0-beta32 + sample data

ElasticSuite Version : 2.6.3

Environment : Developer

Third party modules : N/A

Steps to reproduce

1. Go in admin and in front to any Category for which NO change have been applied to "Display Settings > Layered Navigation Filters"
2. Compare the order of the attributes between admin and front

Expected result

1. The order of attributes in admin should be reflected on the order of the attributes related navigation filters

Actual result

1. The attribute navigation filters in front do not respect the default order described in admin

Enclosed screenshots are of the "Men > Bottoms > Shorts" category.

[rbayet]

OK, this one is a bit weird.

It is definitely linked to the PR #1164 made for #1153 which introduces only in admin a SQL join on eav_entity_attribute to apply a condition on the (relevant) attribute_set_id(s), which in turns adds a GROUP BY on the attribute_id.
(Because the same attribute could belong to several attribute sets, for instance: price).

Hence, the implicit ordering with the GROUP BY is (1) attribute position ASC (2) attribute id ASC : all attributes with the same default position of 0 are ascendingly sorted by their attribute id. price is before color, etc.
Thus the ordering of attributes as it appears in admin changes before and after the PR #1164 .

But on a 2.2.5 (CE or EE), in frontend, the SQL query which does not bear that extra join and GROUP BY still generates the same ordering as in admin.
When executed directly on the 2.3.0 DB (exact same MySQL version), this is not the case.
No structural changes were introduced in the tables involved that could explain the discrepancy.

Anyway the same problem could appear on any 2.2.x release, according to storage engine, MySQL version (which could or not enforce the implicit ordering on attribute_id), moon phase, etc.
To eliminate such possibility, the best approach would be to add an explicit additional ordering ASC on the attribute id in both context, admin and front.

@romainruaud long story short, it should be fixed pre-emptively in 2.6.x.

[romainruaud]

Fixed by #1194