Different results for query Mysql 5.1 and 5.7

0

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.

    
asked by anonymous 01.09.2015 / 12:54

0 answers