MySQL Bugs: #9696: SELECT DISTINCT returns too few rows

Bug #9696	SELECT DISTINCT returns too few rows
Submitted:	6 Apr 2005 21:38	Modified:	20 Apr 2005 1:07
Reporter:	Mardy	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	Linux (Linux Debian unstable)
Assigned to:		CPU Architecture:	Any

Description:
I'm using the video database program from http://www.splitbrain.org/Programming/PHP/VideoDB/
Since I upgraded to mysql 4.1.10 (from 4.0.X) the following query won't work (issued from the commandline mysql client, or from a python program):

SELECT DISTINCT discid FROM videodata WHERE discid LIKE 'str%'

Without the "DISTINCT", I get:

str 1
str 1
str 1
str 2
str 2
str 3
str 3
str 3

while with the "DISTINCT" clause I just get "str 1"! I tryed to create a new table, but I wasn't able to reproduce the problem: on that table it worked ok... :-/
Here is the line from a "show table status" command for the "videodata" table:

| videodata  | MyISAM |       7 | Dynamic    |  304 |            924 |      281040 |      4294967295 |       266240 |         0 |            315 | 2004-10-20 23:12:51 | 2005-04-06 23:17:40 | 2005-03-05 19:42:42 | utf8_general_ci |     NULL |   

Anything wrong?

How to repeat:
I tryed to create from scratch a table and execute a query like that, but everything worked. :-(

Actually, it looks like Mysql behaviour changed, in respect to handling varchar fields. Now it just considers the fields values up to the lenght specified when the table was created...
I don't know if this is a bug or just the correct behaviour; to me, it looks very troublesome when someone is upgrading from an order version (if he uses the varchar fields for more than their length in some SELECT query, they might not work properly).

I was unable to reproduce this bug w/4.1.11 from bk:

mysql> SELECT DISTINCT discid FROM videodata WHERE discid LIKE 'str%';
+--------+
| discid |
+--------+
| str 1  |
| str 2  |
| str 3  |
+--------+
3 rows in set (0.00 sec)

Can you please provide us with a test-case that successfully reproduces this bug ?

Thanks

The problem only occurs when upgrading from a previous version. It looks like mysql versions <4.1 allowed storing a string of length m > n in a VARCHAR(n) column. When upgrading, the exceeding characters in these columns are not lost, but the become unusable in queries: you can retrieve them with a SELECT, but just the first n bytes are considered in WHERE clauses.
So I guess this is not really a bug: mysql 4.1 is quite coherent, but users upgrading from previous versions should be warned.

There is information in our Manual about upgrading 4.0 > 4.1:

http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html