From: Michael Widenius
Date: April 25 1999 7:38pm
Subject: SELECT not returning all matching rows
List-Archive: http://lists.mysql.com/mysql/2360
Message-Id: <14115.28308.837096.228804@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "Ken" == Ken Williams <ken@stripped> writes:

>> Description:
Ken> This is from a mysql session.  Note that the first SELECT statement
Ken> should have returned the row that the second SELECT statement returned
Ken> too.

mysql> show columns from messages;
Ken> +---------+-----------------------+------+-----+---------+-------+
Ken> | Field   | Type                  | Null | Key | Default | Extra |
Ken> +---------+-----------------------+------+-----+---------+-------+
Ken> | thread  | mediumint(8) unsigned |      | PRI | 0       |       |
Ken> | message | varchar(4)            |      | PRI |         |       |
Ken> +---------+-----------------------+------+-----+---------+-------+
Ken> 2 rows in set (0.00 sec)

mysql> select * from messages where thread=116225;
Ken> +--------+---------+
Ken> | thread | message |
Ken> +--------+---------+
Ken> | 116225 | 0       |
Ken> +--------+---------+
Ken> 1 row in set (0.00 sec)

mysql> select * from messages where message = 'abc';
Ken> +--------+---------+
Ken> | thread | message |
Ken> +--------+---------+
Ken> | 116225 | abc     |
Ken> +--------+---------+
Ken> 1 row in set (0.78 sec)

mysql> show index from messages;
Ken> +----------+------------+----------+--------------+-------------+-----------+-------------+----------+
Ken> | Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
Ken> +----------+------------+----------+--------------+-------------+-----------+-------------+----------+
Ken> | messages |          0 | thread   |            1 | thread      | A         |       47216 |     NULL |
Ken> | messages |          0 | thread   |            2 | message     | A         |       47216 |     NULL |
Ken> +----------+------------+----------+--------------+-------------+-----------+-------------+----------+
Ken> 2 rows in set (0.00 sec)


Ken> isamchk didn't find any problems with the messages table, even with
Ken> the -e flag.  Help?

Hi!

This looks very confusing!

Any change you could upload the table to
ftp://www.mysql.com/pub/mysql/secret so that we at TCX can take a look 
at this.

If this is a bug, then one of the following suggestions may fix this
problem:

- Change message from VARCHAR to CHAR.
- Remove message from the 'thread' index.

Regards,
Monty