Re: "select distinct" doesn't coalesce NULL rows - Paul DuBois - com.mysql.lists.bugs

5 messages in com.mysql.lists.bugsRe: "select distinct" doesn't coalesc...

From	Sent On	Attachments
Matthias Urlichs	14 Mar 2003 13:58
Alexander Keremidarski	14 Mar 2003 18:15
Paul DuBois	14 Mar 2003 19:04
Alexander Keremidarski	15 Mar 2003 05:42
Heikki Tuuri	15 Mar 2003 10:30

Subject:	Re: "select distinct" doesn't coalesce NULL rows
From:	Paul DuBois (pa...@snake.net)
Date:	03/14/2003 07:04:16 PM
List:	com.mysql.lists.bugs

At 4:16 +0200 3/15/03, Alexander Keremidarski wrote:

Matias,

Matthias Urlichs wrote: <cut>

It shows what mysql thinks it does when processing the SELECT statement. Anyway, I'm attaching a test case.

Reproduce with: $ mysql -uroot test </tmp/pi $ mysql -uroot -e"select distinct mperson from person" test | cat mperson NULL NULL NULL 1

With your test I got correct result:

mysql> select distinct mperson from person; +---------+ | mperson | +---------+ | NULL | | 1 | +---------+

As per EXPLAIN result is retrieved from Index - if it was MyISAM table I would suspect corrupted Index. However your table is InnoDB so it is not possible.

The fact that on your server SELECT DISTINCT .. return 3 out of 6 NULL values is very strange to say the least.

Another data point. I took the data set and tried the test on Mac OS X. I got back two NULL values and the 1.

So it looks like the NULL values are being eliminated non-deterministically. :-)

Can you try creating new table with same data and see if you will get different results?

CREATE TABLE newtbl TYPE=Innodb SELECT * FROM person;

Later you can add same indexes and check again.

Best regards