Why does the output order of sql products change?

Question

MySQL

The problem is that with each query to the database, the order of the output of the products changes, while there is sorting: ORDER BY p.sort_order ASC, LCASE(p.name) ASC
Here is the full query:

SELECT p.product_id, MIN(p2s.price) AS min_price FROM product p LEFT JOIN product_to_supplier p2s ON p2s.product_id=p.product_id WHERE p.status = '1' AND p.date_available <= NOW() AND p2s.price>0 AND p2s.quantity>0  AND p.manufacturer_id = '5011' GROUP BY p.product_id ORDER BY p.sort_order ASC, LCASE(p.name) ASC LIMIT 0,15
;

If I remove the condition p.manufacturer_id = '5011' or AND p2s.price>0 from the query, then the sorting (sequence) in the table remains unchanged, as I need.
Here's what happens to the data if I execute sql several times:
I execute it for the first time and it is output like this: I execute the query

again and I get this:

That is, every time I execute the query, the sequence of products output changes.
Question: How to fix this and why is this happening? Too many conditions?

Answer 1

No, it's just that neither `p`.`sort_order` nor `p`.`name` are included in the selection. And in older versions of MySQL, when grouping, any fields that are not included in the grouping condition or in aggregate functions are taken from any row that is in the group. In new versions, such a request will generally give an error.

SELECT `p`.`product_id`, `p2s`.`price`
  FROM `product` AS `p`
  JOIN (
    SELECT `product_id`, MIN(`price`) AS `min_price`
      FROM `product_to_supplier`
      WHERE `price` > 0 AND `quantity` > 0
      GROUP BY `product_id`
  ) AS `p2s` ON `p2s`.`product_id` = `p`.`product_id`
  WHERE `p`.`status` = '1' AND `p`.`date_available` <= NOW() 
    AND `p`.`manufacturer_id` = '5011' 
  ORDER BY `p`.`sort_order` ASC, LCASE(`p`.`name`) ASC
  LIMIT 0,15