Incompatibility with Mysql 5.7 (expression # 1 of the ORDER BY clause is not in the SELECT list)

Question

When I execute the following request, I got an Exception

Error code: 3065 Expression # 1 of the ORDER BY clause is not in the SELECT list, refers to the column "webstore.level_depth", which is not in the SELECT list; it is incompatible with DISTINCT

My request

SELECT DISTINCT c.id_parent, c.id_category, cl.name, cl.description, cl.link_rewrite FROM `pj_category_shop` cs, `pj_category` c INNER JOIN `pj_category_lang` cl ON (c.`id_category` = cl.`id_category` AND cl.`id_lang` = 1 AND cl.id_shop = 2 ) WHERE (c.`active` = 1 OR c.`id_category` = 2) AND cs.`id_category` = c.`id_category` AND cs.`id_shop` = 2 AND c.`id_category` != 1 AND `level_depth` <= 2 AND c.id_category IN (SELECT id_category FROM `pj_category_group` WHERE `id_group` IN (3)) ORDER BY `level_depth` ASC, cl.`name` ASC; 

I do not understand why this is happening .. ??

Answer 1

I found the answer to my question. In fact, mysql 5.7 contains " ONLY_FULL_GROUP_BY " in sql mode. Therefore, we cannot execute orderby on an element that is not in the select.we list, change it from

 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' 

in

 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' 

We can do this by running the following queries

 SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' 

Answer 2

Column

ORDER BY must be the column specified in the SELECT list

Add c.level_depth to your c.level_depth

Try:

 SELECT DISTINCT c.id_parent, c.id_category, cl.name, cl.description, cl.link_rewrite, c.level_depth FROM `pj_category_shop` cs, `pj_category` c INNER JOIN `pj_category_lang` cl ON (c.`id_category` = cl.`id_category` AND cl.`id_lang` = 1 AND cl.id_shop = 2 ) WHERE (c.`active` = 1 OR c.`id_category` = 2) AND cs.`id_category` = c.`id_category` AND cs.`id_shop` = 2 AND c.`id_category` != 1 AND `level_depth` <= 2 AND c.id_category IN (SELECT id_category FROM `pj_category_group` WHERE `id_group` IN (3)) ORDER BY c.`level_depth` ASC, cl.`name` ASC; 

Answer 3

Sql Feature Order by is the name that is proposed to be used to order selected columns based on the column specified in the following syntax: Column Order_Name ASC / DESC

So, if you do not add a column in which you decide to receive a set of orders in the select clause, you will get this error.

Answer 4

 SELECT DISTINCT c.id_parent, c.id_category, cl.name, cl.description, cl.link_rewrite FROM 'pj_category_shop' cs, 'pj_category' c INNER JOIN 'pj_category_lang' cl ON (c.'id_category' = cl.'id_category' AND cl.'id_lang' = 1 AND cl.id_shop = 2 ) WHERE (c.'active' = 1 OR c.'id_category' = 2) ORDER BY c.'level_depth' ASC, cl.'name' ASC AND cs.'id_category' = c.'id_category' AND cs.'id_shop' = 2 AND c.'id_category' != 1 AND 'level_depth' <= 2 AND c.id_category IN (SELECT id_category FROM 'pj_category_group' WHERE 'id_group' IN (3)); 

To summarize, you should have ORDER BY in the context of the SELECT , in this case, with WHERE , FROM and INNER JOIN .