Description:
When we join two or more tables, select distinct and order by on a
column, we get problems.
How-To-Repeat:
mysql> select * from test;
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 1 | 3 |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from test as a1, test as a2;
+------+------+------+------+
| a | b | a | b |
+------+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 1 | 1 |
| 1 | 3 | 1 | 1 |
| 1 | 1 | 2 | 2 |
| 2 | 2 | 2 | 2 |
| 1 | 3 | 2 | 2 |
| 1 | 1 | 1 | 3 |
| 2 | 2 | 1 | 3 |
| 1 | 3 | 1 | 3 |
+------+------+------+------+
9 rows in set (0.00 sec)
mysql> select * from test as a1, test as a2 order by a2.b;
+------+------+------+------+
| a | b | a | b |
+------+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 1 | 1 |
| 1 | 3 | 1 | 1 |
| 1 | 1 | 2 | 2 |
| 2 | 2 | 2 | 2 |
| 1 | 3 | 2 | 2 |
| 1 | 1 | 1 | 3 |
| 2 | 2 | 1 | 3 |
| 1 | 3 | 1 | 3 |
+------+------+------+------+
9 rows in set (0.00 sec)
This is the problem:
mysql> select distinct a1.a from test as a1, test as a2 order by a2.b;
+------+
| a |
+------+
| 1 |
| 2 |
| 1 |
| 2 |
| 1 |
| 2 |
+------+
6 rows in set (0.00 sec)
mysql> select distinct a1.a from test as a1, test as a2 order by a1.b;
+------+
| a |
+------+
| 1 |
| 2 |
| 1 |
+------+
3 rows in set (0.01 sec)
Interestingly, the query without the "order by" works... :)
mysql> select distinct a1.a from test as a1, test as a2;
+------+
| a |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
Fix:
Err... avoid putting order by or distinct together? If you need both
... err... bad luck.
Submitter-Id: <submitter ID>
Originator:
Organization:
MySQL support: [none | licence | email support | extended email support ]
Synopsis: error on distinct when used with joined table and order by
Severity: serious
Priority: medium
Category: mysql
Class: sw-bug
Release: mysql-3.23.49 (Source distribution)
>Environment:
System: Linux t1.trellian.com 2.4.18 #1 Wed Jun 5 14:20:33 EST 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 Jul 22 21:23 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x 1 root root 1153784 Jul 18 21:53 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2390922 Jul 18 21:53 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Jul 18 21:53 /usr/lib/libc.so
-rw-r--r-- 1 root root 726660 Mar 24 15:56 /usr/lib/libc-client.so.2001
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
