SELECT Mysql with wrong result

Asked

Viewed 120 times

-2

Let’s take the table below as an example: 

id  empresa  forneedor
    1   10       105
    2   10       102
    3   10       105
    4   10       112
    5   10       105

Using company and supplier as indexes.

Making SELECT * FROM table WHERE empresa = 10; -> returns 5 lines that is expected.

Making SELECT * from table WHERE fornecedor = 105; -> returns 3 lines that is expected.

Making SELECT * from table WHERE empresa = 10 AND fornecedor = 105; -> returns only 1 line, result nay expected.

The table is in INNODB format

I’ve used the OTPIMIZE TABLE command, but it hasn’t solved.

Does anyone have any idea where the problem is?

I’ve detected that the problem is occurring with partitioned and index_merge tables.
I believe it is a similar problem to this https://bugs.mysql.com/bug.php?id=70588.

mysql select-sql

  • Can you create a Sqlfiddle reproducing this problem?

  • Post your problem in: stackoverflow.com

  • Did you not want the operator OR instead of AND

  • 1

    I would say that it is impossible, such a bug would have already been reported and fixed, check the typing of the columns , if the record actually exists , syntax (a distinct in place of a "*") etc. , I repeat, it would be such a crass error that someone would have reported , impossible this in an SGBD with millions of users and installations. I should have said improbable but I take risks on impossible even. abs

  • Motta, I accept your opinion, I agree, but it is happening. It is an isolated fact on a test basis, so I am not desperate. But I am not able to fix it. I have restored the backup of the official database. Problem persists, I have recreated the keys of the table. Problem persists. The same query being executed in my official database returns the lines correctly

1 answer

1


Problem solved.

Updated Mysql version to 5.6.19 (was 5.5.?).

Problem with Partified table and multiple indexes. More information here link

Thanks to those who tried to help.

Browser other questions tagged mysql select-sql

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