SELECT DISTINCTROW/ORDER BY on multiple tables

Description:
When using an ORDER BY clause on a multiple table SELECT DISTINCT=
ROW,
it appears that the ORDER BY column is taken into account for distincti=
on
even when it isn't a returned column in hte resultset. This does not
appear to be the case when you perform the select from one table.

How-To-Repeat:
# ----------------------------
# Creating tables for the test
# ----------------------------

CREATE TABLE table1 (email varchar(50), infoID BIGINT, dateentered
DATETIME);
CREATE TABLE table2 (infoID BIGINT, shipcode varchar(10));

# -----------------
# Populating table1
# -----------------

INSERT INTO table1(email, infoID, dateentered) VALUES
('test1@testdomain.com', 1, '2002-07-30 22:56:38'),
('test1@testdomain.com', 1, '2002-07-27 22:58:16'),
('test2@testdomain.com', 1, '2002-06-19 15:22:19'),
('test2@testdomain.com', 2, '2002-06-18 14:23:47'),
('test3@testdomain.com', 1, '2002-05-19 22:17:32');

INSERT INTO table2(infoID, shipcode) VALUES
(1, 'Z001'),
(2, 'R002');

# ------------------------------
# Reading out correct statements
# ------------------------------

SELECT DISTINCTROW email, shipcode FROM table1, table2 WHERE
table1.infoID=3Dtable2.infoID;
SELECT DISTINCTROW email FROM table1 ORDER BY dateentered DESC;

# -----------------------------
# Reading out erratic statement
# -----------------------------

SELECT DISTINCTROW email, shipcode FROM table1, table2 WHERE
table1.infoID=3Dtable2.infoID
ORDER BY dateentered DESC;

# -----------
# Cleaning up
# -----------

DROP TABLE table1;
DROP TABLE table2;?


Fix:
Use unordered data

Submitter-Id: stcuppet@us.ibm.com
Originator: Stephen Cuppett
Organization: IBM, eServer zSeries development
MySQL support: none
Synopsis:
Severity: serious
Priority: medium
Category: mysql
Class: sw-bug
Release: mysql-3.23.51 (Official MySQL RPM)
Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.51, 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.51-Max
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 2 hours 29 min 51 sec

Threads: 1 Questions: 1 Slow queries: 0 Opens: 6 Flush tables: 1 O=
pen
tables: 0 Queries per second avg: 0.000

Environment:

System: Linux cuppett-udp4298546uds.endicott.ibm.com 2.4.18-3 #1 Thu Ap=
r 18
07:37:53 EDT 2002 i686 unknown
Architecture: i686

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-112)
Compilation info: CC=3D'gcc' CFLAGS=3D'-O6 -fno-omit-frame-pointer -mp=
entium'
CXX=3D'gcc' CXXFLAGS=3D'-O6 -fno-omit-frame-pointer
-felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS=3D''=

LIBC:
lrwxrwxrwx 1 root root 13 Jul 29 07:00 /lib/libc.so.6 =
->
libc-2.2.5.so
-rwxr-xr-x 1 root root 1260480 Jul 9 11:58 /lib/libc-2.2.5=
..so
-rw-r--r-- 1 root root 2312370 Jul 9 11:25 /usr/lib/libc.a=

-rw-r--r-- 1 root root 178 Jul 9 11:19 /usr/lib/libc.s=
o
Configure command: ./configure --disable-shared --with-mysqld-ldflags
=3D-all-static --with-client-ldflags=3D-all-static
--with-other-libc=3D/usr/local/mysql-glibc --without-berkeley-db
--without-innodb --enable-assembler --enable-local-infile
--with-mysqld-user=3Dmysql --with-unix-socket-path=3D/var/lib/mysql/mys=
ql.sock
--prefix=3D/ --with-extra-charsets=3Dcomplex --exec-prefix=3D/usr
--libexecdir=3D/usr/sbin --sysconfdir=3D/etc --datadir=3D/usr/share
--localstatedir=3D/var/lib/mysql --infodir=3D/usr/info
--includedir=3D/usr/include --mandir=3D/usr/man '--with-comment=3DOffic=
ial MySQL
RPM' CC=3Dgcc 'CFLAGS=3D-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAG=
S=3D-O6
-fno-omit-frame-pointer -felide-constructors
-fno-exceptions -fno-rtti -mpentium' CXX=3Dgcc
=



------------------------------------------------------------ ---------
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-thread12273@lists.mysql.com
To unsubscribe, e-mail

Re: SELECT DISTINCTROW/ORDER BY on multiple tables

Stephen T Cuppett writes:
> Description:
> When using an ORDER BY clause on a multiple table SELECT DISTINCTROW,
> it appears that the ORDER BY column is taken into account for distinction
> even when it isn't a returned column in hte resultset. This does not
> appear to be the case when you perform the select from one table.
>


hi!

The above is not a bug but expected behaviours.

According to the valid SQL standards, (see ANSI SQL92), hidden columns
are first included in select list and thereafter the further steps are
processed on the query.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ 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-thread12275@lists.mysql.com
To unsubscribe, e-mail

Re: SELECT DISTINCTROW/ORDER BY on multiple tables

Hi!

>>>>> "Sinisa" == Sinisa Milivojevic writes:

Sinisa> Stephen T Cuppett writes:
>> Description:
>> When using an ORDER BY clause on a multiple table SELECT DISTINCTROW,
>> it appears that the ORDER BY column is taken into account for distinction
>> even when it isn't a returned column in hte resultset. This does not
>> appear to be the case when you perform the select from one table.


Sinisa> hi!

Sinisa> The above is not a bug but expected behaviours.

Sinisa> According to the valid SQL standards, (see ANSI SQL92), hidden columns
Sinisa> are first included in select list and thereafter the further steps are
Sinisa> processed on the query.

I am not 100 % sure about the following but I think that the ANSI SQL92
doesn't allows one to use a distinct query where you have a column
in the ORDER BY that is not part of the selected columns.

MySQL solves this by adding the ORDER BY columns as hidden columns to
the query. The disadvantage of this is that these are part of the
DISTINCT process.

Regards,
Monty

------------------------------------------------------------ ---------
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-thread12340@lists.mysql.com
To unsubscribe, e-mail

SELECT DISTINCTROW/ORDER BY on multiple tables

Hi!

>>>>> "Stephen" == Stephen T Cuppett writes:

Stephen> Description:
Stephen> When using an ORDER BY clause on a multiple table SELECT DISTINCTROW,
Stephen> it appears that the ORDER BY column is taken into account for distinction
Stephen> even when it isn't a returned column in hte resultset. This does not
Stephen> appear to be the case when you perform the select from one table.

Stephen> How-To-Repeat:



Stephen> SELECT DISTINCTROW email, shipcode FROM table1, table2 WHERE
Stephen> table1.infoID=table2.infoID
Stephen> ORDER BY dateentered DESC;

Here is a now finally a patch for this (will be in either 4.0.3 or 4.0.4)

((/my/mysql-4.0)) bk diffs -c sql/sql_select.cc
===== sql/sql_select.cc 1.188 vs edited =====
*** /tmp/sql_select.cc-1.188-16040 Thu Aug 8 21:38:09 2002
--- edited/sql/sql_select.cc Thu Aug 15 16:11:45 2002
***************
*** 641,646 ****
--- 641,648 ----
DBUG_PRINT("info",("Creating tmp table"));
thd->proc_info="Creating tmp table";

+ join.tmp_table_param.hidden_field_count=(all_fields.elements -
+ fields.elements);
if (!(tmp_table =
create_tmp_table(thd,&join.tmp_table_param,all_fields,
((!simple_group && !procedure &&

Regards,
Monty
CTO of MySQL AB

------------------------------------------------------------ ---------
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-thread12355@lists.mysql.com
To unsubscribe, e-mail