List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:April 25 1999 7:38pm
Subject:SELECT not returning all matching rows
View as plain text  
>>>>> "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
Thread
SELECT not returning all matching rowsKen Williams24 Apr
  • SELECT not returning all matching rowsMichael Widenius25 Apr
    • Re: SELECT not returning all matching rowsKen Williams4 May