[EMAIL PROTECTED] wrote:
> Hi all,
>
> I've got a question. We were testing something on our mysql server (
> 4.0.21) with MyISAM tables.
>
> When we executed the query "select * from people where name like '';" we
> expected the same results as "select * from people where name='';" but it
> didn't.
Your expectation was correct.
> The like function returned everything instead of only the people without a
> name. Is this known (and correct) behaviour? It does not sound logical to
> me. I couldn't find anything about it on the mysql website.
You didn't find it on the mysql site because that shouldn't happen.
> Thanks in advance!
>
> With regards,
>
> Casper Gondelach
Gleb Paharenko wrote:
Hello.
I was unable to repeat this situation on my MySQL 5.0.12. See:
So? 5.0.12 is not for production. It's still beta, which makes it
unsuitable as a reference for correctness, I think. You ought to compare to
the latest stable version, 4.0.26 or 4.1.14. I know you like to encourage
people to upgrade to the latest version whenever possible, but you ought to
make sure the version is relevant first. If it isn't, upgrading won't help.
<snip>
Check if this issue exists on the latest release (4.1.14 now). In my
opinion, this problem might be related to character sets.
Really? In 4.0? In which character set is '' equivalent to '%' to LIKE?
Let's test to see if the version is the problem:
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 4.0.21 |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE TABLE lt (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(8), INDEX (name));
Query OK, 0 rows affected (0.16 sec)
mysql> INSERT INTO lt (name)
VALUES ('apple'), ('orange'), ('plum'), ('pear'), ('cherry'), ('');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM lt WHERE name LIKE 'p%';
+----+------+
| id | name |
+----+------+
| 4 | pear |
| 3 | plum |
+----+------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM lt WHERE name LIKE '';
+----+------+
| id | name |
+----+------+
| 6 | |
+----+------+
1 row in set (0.02 sec)
My conclusion is that this works in my copy of 4.0.21 (the OP's version), at
least for this simple test. I've tried with and without the index on name,
with and without the existence of an empty name row. All work as expected.
Casper, you've told us you have 4.0.21. Is it an official binary from
MySQL, or something else? What platform is it on? As Gleb suggests, what
is the output of
SHOW CREATE TABLE people;
Is
select * from people where name like '';
the exact query you used, or is this a simplification of the real query? If
the latter, what's the real query? What is the definition of table
people? Can you show us a few rows of output from your query?
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
