List:Bugs« Previous MessageNext Message »
From:sasha Date:November 14 2000 6:14pm
Subject:Re: BUG: SELECT on TEXT column (see secret/select-text-bug.tar.gz)
View as plain text  
"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
       <___/
Thread
BUG: SELECT on TEXT column (see secret/select-text-bug.tar.gz)Jeremy D. Zawodny14 Nov
  • Re: BUG: SELECT on TEXT column (see secret/select-text-bug.tar.gz)sasha14 Nov
    • Re: BUG: SELECT on TEXT column (see secret/select-text-bug.tar.gz)Jeremy D. Zawodny15 Nov
  • PATCH: SELECT on TEXT column (see secret/select-text-bug.tar.gz)Michael Widenius18 Nov