>Description:
Strange NULL in reply.
>How-To-Repeat:
mysql> CREATE TABLE S (ID1 int, ID2 int, ID int NOT NULL
AUTO_INCREMENT,PRIMARY KEY(ID));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into S values
(1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> # my request (strange NULL in reply)
mysql> select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level
-> from S left join S as yS on S.ID1 between yS.ID1 and yS.ID2
-> group by xID order by xID1;
+-----+------+-------+
| xID | xID1 | Level |
+-----+------+-------+
| 1 | NULL | * | // !!!!!!!!!!!!!!!
| 3 | NULL | *** | // !!!!!!!!!!!!!!!
| 2 | 2 | ** |
| 4 | 185 | **** |
+-----+------+-------+
4 rows in set (0.00 sec)
mysql> # small changes in REQ for solve problem.
mysql> # w/o "repeat('*',....)"
mysql> select S.ID as xID, S.ID1 as xID1, count(distinct yS.ID) as Level
-> from S left join S as yS on S.ID1 between yS.ID1 and yS.ID2
-> group by xID order by xID1;
+-----+------+-------+
| xID | xID1 | Level |
+-----+------+-------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 134 | 3 |
| 4 | 185 | 4 |
+-----+------+-------+
4 rows in set (0.00 sec)
mysql>
mysql> # w/o "distinct"
mysql> select S.ID as xID, S.ID1 as xID1, repeat('*',count(yS.ID)) as Level
-> from S left join S as yS on S.ID1 between yS.ID1 and yS.ID2
-> group by xID order by xID1;
+-----+------+-------+
| xID | xID1 | Level |
+-----+------+-------+
| 1 | 1 | * |
| 2 | 2 | ** |
| 3 | 134 | *** |
| 4 | 185 | **** |
+-----+------+-------+
4 rows in set (0.00 sec)
mysql> # w/o "order by"
mysql> select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level
-> from S left join S as yS on S.ID1 between yS.ID1 and yS.ID2
-> group by xID;
+-----+------+-------+
| xID | xID1 | Level |
+-----+------+-------+
| 1 | 1 | * |
| 2 | 2 | ** |
| 3 | 134 | *** |
| 4 | 185 | **** |
+-----+------+-------+
4 rows in set (0.00 sec)
mysql>
mysql> # w/o alias in "order by"
mysql> select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level
-> from S left join S as yS on S.ID1 between yS.ID1 and yS.ID2
-> group by xID order by S.ID1;
+-----+------+-------+
| xID | xID1 | Level |
+-----+------+-------+
| 1 | 1 | * |
| 2 | 2 | ** |
| 3 | 134 | *** |
| 4 | 185 | **** |
+-----+------+-------+
4 rows in set (0.00 sec)
mysql>
mysql> drop table S;
Query OK, 0 rows affected (0.00 sec)
=================================================================================
================ repeat w/o reply
=================================================================================
CREATE TABLE S (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID));
insert into S values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL);
# my request (strange NULL in reply)
select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level
from S left join S as yS on S.ID1 between yS.ID1 and yS.ID2
group by xID order by xID1;
# small changes in REQ for solve problem.
# w/o "repeat('*',....)"
select S.ID as xID, S.ID1 as xID1, count(distinct yS.ID) as Level
from S left join S as yS on S.ID1 between yS.ID1 and yS.ID2
group by xID order by xID1;
# w/o "distinct"
select S.ID as xID, S.ID1 as xID1, repeat('*',count(yS.ID)) as Level
from S left join S as yS on S.ID1 between yS.ID1 and yS.ID2
group by xID order by xID1;
# w/o "order by"
select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level
from S left join S as yS on S.ID1 between yS.ID1 and yS.ID2
group by xID;
# w/o alias in "order by"
select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level
from S left join S as yS on S.ID1 between yS.ID1 and yS.ID2
group by xID order by S.ID1;
drop table S;
>Fix:
Small changes in reqest :-(
>Submitter-Id: <submitter ID>
>Originator: Andrew Shirrayev
>Organization:
>MySQL support: none
>Synopsis: count(distinct)&OrderBy&... generate strange NULL
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.49 (Source distribution)
>Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.49, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.49-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 1 day 15 hours 35 min 15 sec
Threads: 1 Questions: 61 Slow queries: 0 Opens: 24 Flush tables: 1 Open
tables: 6 Queries per second avg:
>Environment:
System: Linux temphost 2.4.18 #1 SMP Fri Apr 12 00:24:28 MSD 2002 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
gcc version 2.95.4 20011002 (Debian prerelease)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 May 14 13:53 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x 1 root root 1153784 Apr 28 13:57 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2390922 Apr 28 13:58 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Apr 28 13:58 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr --exec-prefix=/usr
--libexecdir=/usr/sbin --datadir=/usr/share --sysconfdir=/etc/mysql
--localstatedir=/var/lib/mysql --includedir=/usr/include --infodir=/usr/share/info
--mandir=/usr/share/man --enable-shared --with-libwrap --enable-assembler
--with-berkeley-db --with-innodb --enable-static --enable-shared --enable-local-infile
--with-raid --enable-thread-safe-client --without-readline
--with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-mysqld-user=mysql
--without-bench --with-client-ldflags=-lstdc++ --with-extra-charsets=all
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
