Re: "select distinct" doesn"t coalesce NULL rows

Re: "select distinct" doesn"t coalesce NULL rows

am 14.03.2003 22:58:51 von Matthias Urlichs

Hi,

[ resent to bugs ]
> > "select distinct FOO from BAR" reports multiple NULL rows
>
> I don't see this in my 4.1.0 server, and I don't see it demonstrated
> in the message below, either. What is the EXPLAIN output supposed to
> show?
>
It shows what mysql thinks it does when processing the SELECT statement.
Anyway, I'm attaching a test case.

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


$ cat /tmp/pi
-- MySQL dump 10.0
--
-- Host: localhost Database: pop
---------------------------------------------------------
-- Server version 4.1.0-alpha-debug-log

--
-- Table structure for table 'person'
--

DROP TABLE IF EXISTS person;
CREATE TABLE person (
descr int(11) default NULL,
id int(11) NOT NULL default '0',
kunde int(11) NOT NULL default '0',
nameid int(11) default NULL,
abtid int(11) default NULL,
mailid int(11) default NULL,
foneid int(11) default NULL,
faxid int(11) default NULL,
pagerid int(11) default NULL,
isdnid int(11) default NULL,
dest varchar(4) default NULL,
adrid int(11) default NULL,
ausweis int(11) default NULL,
zusatz int(11) default NULL,
suche int(11) default NULL,
username int(11) default NULL,
passwort int(11) default NULL,
uid int(11) NOT NULL default '0',
pwsubdir int(11) default NULL,
pwuse bigint(20) NOT NULL default '0',
udomain int(11) default NULL,
uip int(11) default NULL,
proto smallint(6) default NULL,
maxconn tinyint(4) default NULL,
ulocip int(11) default NULL,
uremip int(11) default NULL,
prefcall smallint(6) default NULL,
tarif int(11) default NULL,
satz tinyint(4) default NULL,
mperson int(11) default NULL,
gebtag smallint(6) default NULL,
gebjahr smallint(6) default NULL,
funktion int(11) default NULL,
ustid varchar(10) default NULL,
timestamp timestamp NOT NULL,
PRIMARY KEY (id),
KEY suche (suche),
KEY username (username),
KEY uid (uid),
KEY mailid (mailid),
KEY mperson (mperson),
KEY timestamp (timestamp)
) TYPE=InnoDB CHARSET=latin1;

--
-- Dumping data for table 'person'
--

INSERT INTO person (descr, id, kunde, nameid, abtid, mailid, foneid, faxid,
pagerid, isdnid, dest, adrid, ausweis, zusatz, suche, username, passwort,
uid, pwsubdir, pwuse, udomain, uip, proto, maxconn, ulocip, uremip,
prefcall, tarif, satz, mperson, gebtag, gebjahr, funktion, ustid,
timestamp) VALUES
(1482,1,1,32097,NULL,2627,7629,7630,NULL,NULL,NULL,5076,NULL ,NULL,5483,3259,37627,1210,NULL,2,71,NULL,NULL,NULL,NULL,NUL L,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2002-04-26
07:33:09');
INSERT INTO person (descr, id, kunde, nameid, abtid, mailid, foneid, faxid,
pagerid, isdnid, dest, adrid, ausweis, zusatz, suche, username, passwort,
uid, pwsubdir, pwuse, udomain, uip, proto, maxconn, ulocip, uremip,
prefcall, tarif, satz, mperson, gebtag, gebjahr, funktion, ustid,
timestamp) VALUES
(NULL,2,1,796,NULL,7628,482843,7630,32502,NULL,NULL,5076,NUL L,NULL,0,14,8956,201,NULL,256,0,NULL,NULL,NULL,NULL,NULL,1,N ULL,NULL,NULL,222,1969,NULL,NULL,'2003-02-21
22:31:33');
INSERT INTO person (descr, id, kunde, nameid, abtid, mailid, foneid, faxid,
pagerid, isdnid, dest, adrid, ausweis, zusatz, suche, username, passwort,
uid, pwsubdir, pwuse, udomain, uip, proto, maxconn, ulocip, uremip,
prefcall, tarif, satz, mperson, gebtag, gebjahr, funktion, ustid,
timestamp) VALUES
(NULL,3,1,797,NULL,447542,14424,7630,11109,NULL,NULL,5076,NU LL,NULL,0,823,421730,200,107100,8609857794,NULL,NULL,NULL,NU LL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2002-10-0 9
09:25:17');
INSERT INTO person (descr, id, kunde, nameid, abtid, mailid, foneid, faxid,
pagerid, isdnid, dest, adrid, ausweis, zusatz, suche, username, passwort,
uid, pwsubdir, pwuse, udomain, uip, proto, maxconn, ulocip, uremip,
prefcall, tarif, satz, mperson, gebtag, gebjahr, funktion, ustid,
timestamp) VALUES
(NULL,4,831,798,NULL,445,77216,49335,504384,NULL,NULL,1355,N ULL,NULL,0,10,400296,202,5604,10757407042,2727,NULL,NULL,NUL L,NULL,NULL,NULL,NULL,NULL,1,608,1966,NULL,NULL,'2002-11-14
12:21:34');
INSERT INTO person (descr, id, kunde, nameid, abtid, mailid, foneid, faxid,
pagerid, isdnid, dest, adrid, ausweis, zusatz, suche, username, passwort,
uid, pwsubdir, pwuse, udomain, uip, proto, maxconn, ulocip, uremip,
prefcall, tarif, satz, mperson, gebtag, gebjahr, funktion, ustid,
timestamp) VALUES
(NULL,5,1,75845,NULL,507,17686,NULL,36950,NULL,NULL,NULL,NUL L,NULL,824,12651,400357,204,NULL,10757407042,NULL,NULL,NULL, NULL,NULL,NULL,1,NULL,NULL,NULL,301,1969,NULL,NULL,'2002-04- 26
07:33:09');
INSERT INTO person (descr, id, kunde, nameid, abtid, mailid, foneid, faxid,
pagerid, isdnid, dest, adrid, ausweis, zusatz, suche, username, passwort,
uid, pwsubdir, pwuse, udomain, uip, proto, maxconn, ulocip, uremip,
prefcall, tarif, satz, mperson, gebtag, gebjahr, funktion, ustid,
timestamp) VALUES
(NULL,234,744,425119,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NU LL,425735,0,425120,NULL,0,NULL,0,NULL,NULL,NULL,NULL,NULL,NU LL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2002-04-26
07:33:09');
INSERT INTO person (descr, id, kunde, nameid, abtid, mailid, foneid, faxid,
pagerid, isdnid, dest, adrid, ausweis, zusatz, suche, username, passwort,
uid, pwsubdir, pwuse, udomain, uip, proto, maxconn, ulocip, uremip,
prefcall, tarif, satz, mperson, gebtag, gebjahr, funktion, ustid,
timestamp) VALUES
(NULL,298,2,0,NULL,26,0,0,NULL,NULL,NULL,NULL,NULL,NULL,0,0, NULL,0,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,'2002-04-26
07:33:09');
$

--
Matthias Urlichs | noris network AG | http://smurf.noris.de/




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13966@lists.mysql.com
To unsubscribe, e-mail

Re: "select distinct" doesn"t coalesce NULL rows

am 15.03.2003 03:16:20 von Alexander Keremidarski

Matias,

Matthias Urlichs wrote:


> It shows what mysql thinks it does when processing the SELECT statement.
> Anyway, I'm attaching a test case.
>
> Reproduce with:
> $ mysql -uroot test > $ 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.

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

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

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

--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13968@lists.mysql.com
To unsubscribe, e-mail

Re: "select distinct" doesn"t coalesce NULL rows

am 15.03.2003 04:04:38 von Paul DuBois

At 4:16 +0200 3/15/03, Alexander Keremidarski wrote:
>Matias,
>
>Matthias Urlichs wrote:
>
>
>>It shows what mysql thinks it does when processing the SELECT
>>statement. Anyway, I'm attaching a test case.
>>
>>Reproduce with:
>>$ mysql -uroot test >>$ 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.
>
>mysql> select mperson from person;
>+---------+
>| mperson |
>+---------+
>| NULL |
>| NULL |
>| NULL |
>| NULL |
>| NULL |
>| NULL |
>| 1 |
>+---------+
>
>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
>
>--
> MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
> For technical support contracts, visit https://order.mysql.com/?ref=msal
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
> <___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13969@lists.mysql.com
To unsubscribe, e-mail

Re: "select distinct" doesn"t coalesce NULL rows

am 15.03.2003 14:42:43 von Alexander Keremidarski

Mathias,

Matthias Urlichs wrote:
> Hi,

Thanks a lot for your report. With your Innodb files I was able to repeat it and
write smaller repeatable test case.

Ther reason I did not confirmed it first time was that query often returns correct
result when first executed.

Thanks also to Paul who confirmed it too.

Entered as http://bugs.mysql.com/bug.php?id=154

Best regards

--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13971@lists.mysql.com
To unsubscribe, e-mail