Strange NULL.
am 09.08.2002 13:20:53 von Andrew ShirrayevFrom: andrews
To: mysql@lists.mysql.com
Subject: Strange NULL.
>Description:
Strange NULL in reply. If use count(distinct ..) as argument a function
and alias present in "Order By"
>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 | * | // NULL in xID !!!!!!!!!!!!!!!
| 3 | NULL | *** | // it's wrong !!!!!!!!!!!!!!!
| 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:
>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 bugs-thread12343@lists.mysql.com
To unsubscribe, e-mail