We developed a project for a client using MySQL 5.1.68 and at the time of publication we identified that the client's hosting was version 5.7.4.
Once the project has been published, we begin to see some strange behaviors in the results returned in the application on the client server, and as we are not using any "advanced" features in the database, we begin to debug the queries and arrive at a strange situation , version 5.7 returns a result other than version 5.1.
We have identified the problem in the query below but we can not understand why. The subquery that returns the amount SELECT SUM(te1.quantidade) .... te2.id_atributo_valor = tb.id_atributo_valor
in 5.1 returns 4, correct information. While in 5.7 it returns NULL
. But changing% with% of% with% with% return value becomes 4, with te1.id_produto = p.id_produto
and te1.id_produto = e.id_produto
being part of an INNER JOIN query, ie having the same value.
SELECT DISTINCT
e.id_estoque,
tb.id_atributo_valor,
p.id_produto,
(
SELECT SUM(te1.quantidade)
FROM produtos_estoque te1
INNER JOIN estoque_valores_atributo te2 ON te1.id_estoque = te2.id_estoque
WHERE te1.id_produto = p.id_produto AND te2.id_atributo_valor = tb.id_atributo_valor
) quantidade
FROM
produtos p
INNER JOIN
produtos_estoque e ON p.id_produto = e.id_produto
LEFT JOIN
(
SELECT t1.id_atributo_valor, t1.id_atributo, t2.separar, t0.id_estoque, t3.thumb
FROM produtos_estoque t
INNER JOIN estoque_valores_atributo t0 ON t.id_estoque = t0.id_estoque
INNER JOIN atributos_valores t1 ON t0.id_atributo_valor = t1.id_atributo_valor
INNER JOIN atributos t2 ON t1.id_atributo = t2.id_atributo AND separar = 1
LEFT JOIN produtos_estoque_fotos t3 ON t1.id_atributo_valor = t3.id_atributo_valor AND t3.principal = 1 AND t.id_produto = t3.id_produto
) tb ON e.id_estoque = tb.id_estoque
WHERE
p.excluido = '0'
AND p.id_produto = '157'
AND e.inativo = '0'
AND e.valor != '0.00'
AND ((tb.separar = 1 AND tb.thumb IS NOT NULL) OR tb.separar IS NULL)
GROUP BY tb.id_atributo_valor , tb.separar = 1 , p.id_produto
ORDER BY p.id_produto DESC
Does anyone have any idea what might be happening? Is there any parameter of 5.7 that should be enabled / disabled so that the behavior is similar in both versions? Our concern is that there may be some more divergence from this in other application queries.
NOTE: This query is used both in the product details display screen (with p.id_produto
in WHERE) and product list (without e.id_produto
) and in this case without p.id_produto
a quantity is returned correctly.