Pim-community-dev: MSSQL Server Group by problem

[henryktews]

url:
/rest/attribute/

error:
An exception occurred while executing 'SELECT p0_.[id] AS id_0, p0_.[sort_order] AS sort_order_1, p0_.[useable_as_grid_filter] AS useable_as_grid_filter_2, p0_.[max_characters] AS max_characters_3, p0_.[validation_rule] AS validation_rule_4, p0_.[validation_regexp] AS validation_regexp_5, p0_.[wysiwyg_enabled] AS wysiwyg_enabled_6, p0_.[number_min] AS number_min_7, p0_.[number_max] AS number_max_8, p0_.[decimals_allowed] AS decimals_allowed_9, p0_.[negative_allowed] AS negative_allowed_10, p0_.[date_min] AS date_min_11, p0_.[date_max] AS date_max_12, p0_.[metric_family] AS metric_family_13, p0_.[default_metric_unit] AS default_metric_unit_14, p0_.[max_file_size] AS max_file_size_15, p0_.[allowed_extensions] AS allowed_extensions_16, p0_.[minimumInputLength] AS minimumInputLength_17, p0_.[is_required] AS is_required_18, p0_.[is_unique] AS is_unique_19, p0_.[is_localizable] AS is_localizable_20, p0_.[is_scopable] AS is_scopable_21, p0_.[code] AS code_22, p0_.[entity_type] AS entity_type_23, p0_.[attribute_type] AS attribute_type_24, p0_.[backend_type] AS backend_type_25, p0_.[properties] AS properties_26, p0_.[created] AS created_27, p0_.[updated] AS updated_28, p0_.group_id AS group_id_29 FROM pim_catalog_attribute p0_ LEFT JOIN pim_catalog_attribute_group p1_ ON p0_.group_id = p1_.id GROUP BY p0_.[id] ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY':

SQLSTATE [42000, 8120]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Column 'pim_catalog_attribute.sort_order' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SQLSTATE [42000, 8180]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared.

Problem is in:
/src/Pim/Bundle/EnrichBundle/Doctrine/ORM/Repository/AttributeSearchableRepository.php
line: 160

[ahocquard]

Hello,

Akeneo PIM should not be installed with MS SQL Server.
Please check the system requirements: https://docs.akeneo.com/latest/install_pim/manual/system_requirements/system_requirements.html

It has to be installed with Mysql >= 5.7.8 as database.

Regards,
Alexandre

[tmotyl]

Hi @ahocquard,
I know about the system requirements.
The question really is whether Akeneo would accept improvements in that are or not.

We have a customer for whom we're making Akeneo work on MSSQL.
If you're interested in the improvements in this area, we can prepare pull requests after discussing with somebody from the Core Team of Akeneo, which solution is acceptable for you.
If not, we will keep the improvements for ourselves.

Please also keep in mind that improvements in this area also helps with MySQL, as MySQL is also going into direction of being more strict with SQL standards. It also opens a way to use Akeneo with other DBMS like postgresql.

If you have any questions or concerns, please drop me a line on the Akeneo slack.

[BitOne]

Hello @tmotyl ,

We would be definitely opened to improvements on that point !
You could even play with MySQL ONLY_FULL_GROUP_BY configuration parameter so you could get very similar error to what you get with MSSQL.
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

Once reproduced on MySQL, we would be happy to have a look at the improvements!

Don't hesitate to open another PR with your code.

Thank you in advance !

[BitOne]

Hello @tmotyl ,

After discussing with the team, it appears we are already compliant with proper GROUP BY syntax (with ONLY_FULL_GROUP_BY mode). So I stand corrected (thanks @ahocquard ) ;) !

The query you provided is valid as per the section T301 "Functional Dependencies" of the SQL99 specifications.

In our case, the id column is the one used in the GROUP_BY. And as this column is the primary key, it validates the fact that all data belonging to the same row as the primary key are linked to this id, so they can be selected from the id, and don't need any aggregation function nor they need to be present on the GROUP BY side.

So the problem lies in MSSQL server (or maybe your version) which doesn't seem to implement it in Transact-SQL.