1

Can someone explain this... before I have myself committed? The first result set should have two results, same as the second, no?

mysql> SELECT * FROM kuru_footwear_2.customer_address_entity_varchar
    -> WHERE attribute_id=31 AND entity_id=324134;

+----------+----------------+--------------+-----------+-------+
| value_id | entity_type_id | attribute_id | entity_id | value |
+----------+----------------+--------------+-----------+-------+
|   885263 |              2 |           31 |    324134 | NULL  |
+----------+----------------+--------------+-----------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM kuru_footwear_2.customer_address_entity_varchar
    -> WHERE value_id=885263 OR value_id=950181;
+----------+----------------+--------------+-----------+-------+
| value_id | entity_type_id | attribute_id | entity_id | value |
+----------+----------------+--------------+-----------+-------+
|   885263 |              2 |           31 |    324134 | NULL  |
|   950181 |              2 |           31 |    324134 | NULL  |
+----------+----------------+--------------+-----------+-------+
2 rows in set (0.00 sec)

attribute_id is a SMALLINT(5)

entity_id is a INT(10)

Rooster242
  • 911
  • 3
  • 10
  • 19
  • 1
    Please post code here as plain-text, not as images that can be hard to read, can’t be copy-pasted to help test code or use in answers, and are hostile to those who use screen readers. You can edit your question to add the code in the body of your question. Use the `{}` button to format any blocks of code, or indent with four spaces for the same effect. – tadman Feb 15 '17 at 22:11
  • 2
    What are those values defined as in the schema? If they're character fields there could be spaces or invisible characters involved. Another thing might be a damaged index. Does `REPAIR TABLE` fix it? – tadman Feb 15 '17 at 22:12
  • 1
    @tadman I was thinking the same thing – Rick james Feb 15 '17 at 22:13
  • Can you make a sqlfiddle that demonstrates the problem? – Barmar Feb 15 '17 at 22:14
  • Updated the post with text and value types. – Rooster242 Feb 15 '17 at 22:19
  • No, the first select uses AND and the second OR. Result #2 of query #2 fails in the first query because the value_id is not 885263. – Mike Feb 15 '17 at 22:20
  • @Mike The first query doesn't search by value_id. – Rooster242 Feb 15 '17 at 22:21
  • @Rooster242: My bad - misread the query. Are you looking at a view or a table? – Mike Feb 15 '17 at 22:22
  • @tadman "The storage engine for the table doesn't support repair" – Rooster242 Feb 15 '17 at 22:24
  • Is it pertinent that there is a unique index on entity_id+attribute_id? I was looking for duplicates when I discovered this peculiarity. – Rooster242 Feb 15 '17 at 22:34
  • 1
    @Rooster242 "Have you tried turning it off and on again?" You could have a bugged out query cache. Also, try `SELECT value_id, entity_type_id, entity_type_id=2, attribute_id, attribute_id=31 FROM kuru_footwear_2.customer_address_entity_varchar WHERE value_id IN (885263, value_id=950181)` Do those values properly register as being equal? Try narrowing it down to the problem being `entity_type_id` or `attribute_id` specifically. – tadman Feb 15 '17 at 22:38
  • BTW, the number in parentheses after an integer data type is almost meaningless – Strawberry Feb 15 '17 at 22:42
  • To expand on what @tadman said, to clear the query cache you need to use the command `RESET QUERY CACHE;` You'll require RELOAD privileges in order to execute that command. – e_i_pi Feb 15 '17 at 22:45
  • @Strawberry Not necessarily. There's a big difference between `INT(11)` and `INT(1)`. – tadman Feb 15 '17 at 22:47
  • @tadman there's no difference - http://rextester.com/GMZQ67374 – Strawberry Feb 15 '17 at 22:51
  • @Strawberry Ah, that's [MySQL being MySQL again](https://dev.mysql.com/doc/refman/5.7/en/numeric-type-attributes.html). On other platforms it does matter, but I see here it's more a suggestion. – tadman Feb 15 '17 at 23:00
  • The index is defined as UNIQUE KEY `UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID` (`entity_id`,`attribute_id`). I changed it it to be non-unique and now I correctly get two results from the first query. So, it seems MySQL ignores invalid duplicates with a simple select on unique fields. If I add GROUP BY to the query (while still unique) with a COUNT(*) it counts both rows. As designed? – Rooster242 Feb 15 '17 at 23:02
  • Yup. I can't speak for other RDBMSs but in MySQL it only means something in the context of zerofill – Strawberry Feb 15 '17 at 23:03
  • 1
    @Rooster242 If there's a unique index, you shouldn't be able to have those duplicates in the first place. And the query optimizer knows that, so it only returns 1 row that matches the unique index. Something is severely screwed up in your table if it has duplicates. – Barmar Feb 15 '17 at 23:42
  • @Barmar Agreed. I have no idea how the duplicates got in there in the first place (happened before my tenure) but I'm tasked with fixing them. Makes sense that MySQL would stop after finding the first unique row for performance reasons. Adding IGNORE INDEX to the first query returns both rows. If you want to throw up an answer I'll accept it. – Rooster242 Feb 15 '17 at 23:54

2 Answers2

1

The problem is that you have a unique index on (entity_id,attribute_id). The query optimizer notices this when you write a query whose WHERE clause is covered by the index, and only returns 1 row since the uniqueness of the index implies that there's at most one matching row.

I'm not sure how you can have those duplicates in the first place, it seems like there's something corrupted in the table. Adding a unique index to a table will normally remove any duplicates. In fact, this is often suggested as a way to get rid of duplicates in a table, see How do I delete all the duplicate records in a MySQL table without temp tables.

Community
  • 1
  • 1
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Removing and re-adding the unique index is how I'm fixing a lot of the duplicates. It keeps the first one it finds and removes the rest. In some cases the data needs to be merged so I have to do that manually. As for how the duplicates got in there in the first place, it must be that someone or some software is/was using IGNORE INDEX. I'm investigating that too. Thanks for the help! – Rooster242 Feb 16 '17 at 16:37
-2

In the first statement's selection (the 'WHERE' clause), you are using AND; in the second statement, you are using OR. This boils down to the definition of these logical operators. MySQL's official documentation doesn't say much about these other than that AND and OR are their own natural logical operators. If this is confusing, you may want to read up on basic Boolean Algebra.

nasukkin
  • 2,460
  • 1
  • 12
  • 19