Different results for Mysql 5.1 and 5.7 query

Asked

Viewed 183 times

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 had version 5.7.4.

After publishing the project, we started to check some strange behaviors in the results returned in the application on the client server and as we are not using any "advanced" feature in the database, we start debugging the queries and we arrive at a strange situation at least, version 5.7 returns a different result from version 5.1.

We identified the problem in the query below but could 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 returns NULL. But changing te1.id_produto = p.id_produto for te1.id_produto = e.id_produto the value returning becomes 4, and the p.id_produto and the e.id_produto are part of an INNER JOIN of the query or has 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 going on? Is there a 5.7 parameter that should be enabled/disabled for the behavior to be similar in both versions? Our concern is that there is more of this divergence in other implementation consultations.

NOTE: This query is used both in the product details display screen (with the p.id_produto in the WHERE) how much in the list of products (without the p.id_produto) and in this case, without the id_produto the quantity is returned correctly.

  • 1

    Cara already had a question very similar to this, telling me that Mariadb and Mysql gave different results, asked p/ me to send a dump( backup ) and I ran in both and gave the same result, so my tip is to create a test database in both versions, restore the same backup on both and run the same SQL on both, if difference can uproot the file on the net that I even test here p/ you

  • Read my reply here http://answall.com/a/80141/12032

  • 2
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.