"Jeremy D. Zawodny" wrote:
>
> MySQL folks...
>
> The below is the README file from select-text-bug.tar.gz which I have
> just uploaded to /pub/mysql/secret on support.mysql.com. It describes
> what I think is a bug in SELECTing on a TEXT column in the current
> MySQL (3.23.27) on FreeBSD and Linux.
>
> Please let me know if you need any more information. The MyISAM tables
> are included in the archive.
>
> As always, thanks for your help...
>
> Jeremy
>
> --- README ---
>
> MySQL folks,
>
> In this archive is a MyISAM table called BankRateNews which exhibits
> some strange behavior under several versions of MySQL 3.23.xx (where
> xx is 25, 26, and 27) on both Linux and FreeBSD.
>
> There is no crash involved, but some queries don't return the expected
> results. The following is (roughly) a log of what I've done. Text
> blocks beginning with `*** ' are my commentary to illustrate what I
> believe to be wrong.
>
> Please let me know if you're able to reproduce this or not.
>
> Thanks,
>
> Jeremy
>
> ---snip---
>
> mysql> show tables;
> +----------------+
> | Tables_in_test |
> +----------------+
> | BankRateNews |
> +----------------+
> 1 row in set (0.00 sec)
>
> mysql> check table BankRateNews;
> +-------------------+-------+----------+----------+
> | Table | Op | Msg_type | Msg_text |
> +-------------------+-------+----------+----------+
> | test.BankRateNews | check | status | OK |
> +-------------------+-------+----------+----------+
> 1 row in set (0.06 sec)
>
> mysql> select count(*) from BankRateNews;
> +----------+
> | count(*) |
> +----------+
> | 231 |
> +----------+
> 1 row in set (0.04 sec)
>
> *** So far so good. Now let's look at the data a bit.
>
> mysql> select distinct(category) from BankRateNews;
> +----------+
> | category |
> +----------+
> | atm |
> | persona |
> | consume |
> | savings |
> | creditc |
> | onlineb |
> | checkin |
> | autoloa |
> | mortgag |
> | creditu |
> | moneyma |
> +----------+
> 11 rows in set (0.02 sec)
>
> mysql> select count(*) as cnt, category from BankRateNews group by category;
> +-----+----------+
> | cnt | category |
> +-----+----------+
> | 6 | atm |
> | 19 | autoloa |
> | 12 | checkin |
> | 23 | consume |
> | 55 | creditc |
> | 6 | creditu |
> | 1 | moneyma |
> | 52 | mortgag |
> | 36 | onlineb |
> | 10 | persona |
> | 11 | savings |
> +-----+----------+
> 11 rows in set (0.06 sec)
>
> *** Now the fun begins. I will not be able to selet any of the records
> which havea category of 'atm'.
>
> mysql> select count(*) from BankRateNews where category = 'atm';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.00 sec)
>
> *** But others work just fine, as you'll see.
>
> mysql> select count(*) from BankRateNews where category = 'autoloa';
> +----------+
> | count(*) |
> +----------+
> | 19 |
> +----------+
> 1 row in set (0.00 sec)
>
> mysql> select count(*) from BankRateNews where category = 'checkin';
> +----------+
> | count(*) |
> +----------+
> | 12 |
> +----------+
> 1 row in set (0.01 sec)
>
> *** And so on. Note that the following *DOES* work, however:
>
> mysql> select count(*) from BankRateNews where category like 'at%';
> +----------+
> | count(*) |
> +----------+
> | 6 |
> +----------+
> 1 row in set (0.00 sec)
>
> *** The 'category' colum was crated as a TEXT column. I suspected that
> there might be a problem with trailing space, so I tried these...
>
> mysql> select count(*) from BankRateNews where category = 'atm';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.00 sec)
>
> mysql> select count(*) from BankRateNews where category = 'atm ';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.00 sec)
>
> mysql> select count(*) from BankRateNews where category = 'atm ';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.00 sec)
>
> mysql> select count(*) from BankRateNews where category = 'atm ';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.00 sec)
>
> mysql> select count(*) from BankRateNews where category = 'atm ';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.00 sec)
>
> mysql> select count(*) from BankRateNews where category = 'atm ';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.00 sec)
>
> mysql> select count(*) from BankRateNews where category = 'atm ';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.01 sec)
>
> mysql> select count(*) from BankRateNews where category = 'atm ';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.01 sec)
>
> mysql> select count(*) from BankRateNews where category = 'atm ';
> +----------+
> | count(*) |
> +----------+
> | 0 |
> +----------+
> 1 row in set (0.00 sec)
>
> *** But those yielding nothing. And I'd have expected any more space
> than that to have show up earlier in the disctinct query which did
> work.
>
> And, just to be sure, I ran mysqldump on the data and it showed
> *no* trailing whitespace after the 'atm' in those columns.
>
> If I convert it to a VARCHAR, the problem will go away.
>
> mysql> alter table BankRateNews modify category varchar(15) not null;
> Query OK, 231 rows affected (0.10 sec)
>
> *** And now the query that didn't work actually does...
>
> mysql> select count(*) from BankRateNews where category = 'atm';
> +----------+
> | count(*) |
> +----------+
> | 6 |
> +----------+
> 1 row in set (0.00 sec)
>
> *** That's the end of the demonstration. If I've overlooked something
> obvious or you need more details regarding the setup, let me
> know.
>
> Thanks,
>
> Jeremy (jzawodn@stripped)
Thanks for the bug report - I was able to repeat it. The problem I imagine is in
some MyISAM key reading function - Monty would have had a patch by now, but he
is travelling. Nice exercise for me to study MyISAM code :-)
On a side note, not make any excuses for the bug, TEXT is not a very good type
for the category column in this case ( you probably know this already :-) ).
ENUM or SMALLINT with a reference to a separate category table would be better.
--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sasha Pachev <sasha@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA
<___/
