-1
1. select count(*) from tableX where code = "XYZ";
2. select count(*) from tableX where code like "%XYZ";

Result for query 1 is 18734. <== Not Correct

Result for query 2 is 93003. <== Correct

We know that query 2's count is correct based on independent verification.

We expect these two queries to have the exact same count for each because we know that no rows in tableX have a code that ends with "XYZ", so the wildcard at the beginning shouldn't affect the query.

Why would these queries produce different counts?

We have already researched the differences between "=" comparison and "like" string comparison, but based on all our verification checks, we still don't understand why this would give us different counts

We have confirmed the following:

We are using MySQL version 5.5.40-0ubuntu0.12.04.1.

Community
  • 1
  • 1
Matthew Robinson
  • 91
  • 1
  • 9
  • 2
    why don't you join the 2 result sets and examine the extra rows? – Fabricator May 16 '16 at 22:05
  • how do you know the result for query 2 is correct when *no rows in tableX have a code that ends with "XYZ"* – Jeff Puckett May 16 '16 at 22:06
  • The first one will fail to catch all of the `code = 'blahXYZ'` that the second one catches. That's what the `%` does. – Laurel May 16 '16 at 22:06
  • If you are absolutely sure of your data, you might want to check your table for corruption. Also, are these the actual queries, or simplified versions of them? – Uueerdo May 16 '16 at 22:14
  • Possible duplicate of [Difference between LIKE and = in MYSQL?](http://stackoverflow.com/questions/1003381/difference-between-like-and-in-mysql) – arhey May 17 '16 at 04:41
  • 1. I have joined the result sets and examined the extra rows. I saw nothing that would indicate why this is happening. – Matthew Robinson May 17 '16 at 12:46
  • 2. As for the results in both queries, the code field always appears exactly the same in each row, and its value is "XYZ". There is nothing before or after the "XYZ" in both appearance and when attempting to find extra spaces or hidden characters in the field itself. – Matthew Robinson May 17 '16 at 12:48
  • 3. The queries presented are the exact type of queries we're trying (very simple), though substituting different values for the "XYZ" portion gives mixed results. Sometimes the counts will be the same, but sometimes they will be different depending on the code. From all the result sets we've examined so far, we can't pinpoint what would cause the differences. I think that corruption of the table is a definite possibility.. – Matthew Robinson May 17 '16 at 12:52

3 Answers3

0

Try this in order to get your answer:

SELECT code
FROM tableX 
WHERE code LIKE "%XYZ"
AND code <> "XYZ"
LIMIT 10

My guess is that some of your codes end with a lowercase xyz, and since LIKE is case-insensitive, it matched these where = did not.

kloddant
  • 1,026
  • 12
  • 19
0

where code = "XYZ"; gives exact match whereas where code LIKE "%XYZ"; includes partial match as well. In your case, there could be an extra space present which is giving wrong count. Consider trimming before comparing like

where UPPER(TRIM(code)) = 'XYZ';
Rahul
  • 76,197
  • 13
  • 71
  • 125
0

We restarted the server that the database resides on, we re-ran the queries, and now they all are producing the expected, correct results...

We'll have to look into possibilities for why this "fixed" the issue.

Matthew Robinson
  • 91
  • 1
  • 9