Hi, Michael!
> Really? In 4.0? In which character set is '' equivalent to '%' to LIKE?
Thank you for the advertence, I was just going to figure it out:)
You are right, I haven't should asked to show me the collation.
Still I'd like to the see the value of character_set system variable.
> So? 5.0.12 is not for production. It's still beta, which makes it
Yes, for a pity it is still a beta, however, our common desire is to see
it as a production ready. And nobody thwarts the person to check
the problem on the latest release, as I usually suggest :).
Thank you one more time, I'll be careful.
Michael Stassen <[EMAIL PROTECTED]> wrote:
> [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
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
