mysql 5.7 issue

[vaszev]

SELECT DISTINCT id_7 FROM (SELECT f0_.is_deleted AS is_deleted_0, f0_.version AS version_1, f0_.status AS status_2, f0_.file AS file_3, f0_.size AS size_4, f0_.checksum AS checksum_5, f0_.obu_type AS obu_type_6, f0_.id AS id_7, f0_.deleted AS deleted_8, f0_.created AS created_9, f0_.edited AS edited_10 FROM firmware f0_) dctrn_result ORDER BY size_4 ASC LIMIT 10 OFFSET 0

MySQL says:
#3065 - Expression #1 of ORDER BY clause is not in SELECT list, references column 'dctrn_result.size_4' which is not in SELECT list; this is incompatible with DISTINCT

There is the same problem here:
https://github.com/Elgg/Elgg/issues/8121 which refers to the http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html#mysql-nutshell-deprecations

In my example, the right query would look like this:
SELECT DISTINCT id_7,size_4 FROM (SELECT f0_.is_deleted AS is_deleted_0, f0_.version AS version_1, f0_.status AS status_2, f0_.file AS file_3, f0_.size AS size_4, f0_.checksum AS checksum_5, f0_.obu_type AS obu_type_6, f0_.id AS id_7, f0_.deleted AS deleted_8, f0_.created AS created_9, f0_.edited AS edited_10 FROM firmware f0_) dctrn_result ORDER BY size_4 ASC LIMIT 10 OFFSET 0

So the solution is to put the field into the SELECT before use in ORDER BY.
I hope You find this issue helpful.

[cedric-g]

Hi, the bundle only add some expressions to the where clause of the given query builder, it don't deals with the other parts of the query, so I don't get what could be wrong with the bundle it self.

[vaszev]

I just want to inform you that issue with mysql version 5.7

By the way I used to use your bundle with a filter form to update my query like this:
$this->get('lexik_form_filter.query_builder_updater')->addFilterConditions($filterForm, $queryBuilder);

and here came the mysql error. Please note that your updater work fine with mysql 5.6.

[cedric-g]

ok, which form types do you use ? do you have any custom types or apply_filter ?

[vaszev]

This is a regular form created by createForm where I use $builder->add('version', 'Lexik\Bundle\FormFilterBundle\Filter\Form\Type\TextFilterType');

The queryBuilder uses a single "orderBy" method, and here comes the trouble.