>Description:
It appears that if a column generated by a select is
a sum of variables generated in other columns of the
select (see example below!), the column will have a
bogus result if the select is 'DISTINCT'. If the
DISTINCT keyword is dropped, the column is generated
properly, even in a case where the output is no changed
by specifying DISTINCT.
>How-To-Repeat:
CREATE TABLE source (
i int not null,
v int not null,
index (i)
);
insert into source values (1,1);
insert into source values (1,3);
insert into source values (2,1);
create table joinkeys (
i int not null,
unique (i)
);
insert into joinkeys
select
distinct
i
from source;
select * from joinkeys;
# This select is actually an "insert into X select" in the
# production code, but that part isn't necessary to see the
# problem.
select
distinct
joinkeys.i,
@vv1:=if(sv1.i,1,0),
@vv2:=if(sv2.i,1,0),
@vv3:=if(sv3.i,1,0),
@vv1+@vv2+@vv3
from
joinkeys
left join source as sv1 on sv1.i=joinkeys.i and sv1.v=1
left join source as sv2 on sv2.i=joinkeys.i and sv2.v=2
left join source as sv3 on sv3.i=joinkeys.i and sv3.v=3
;
>Fix:
Sorry, I haven't a clue! In the SQL I'm attempting to
optimize (inherited code) I don't actually need the DISTINCT,
but had tried using it and noticed this problem.
>Submitter-Id: <submitter ID>
>Originator: Gary Shea
>Organization:
GTS Design Consulting
>MySQL support: licence
>Synopsis: Problem with variable-generated SELECT column with DISTINCT
>Severity:
>Priority:
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.33 (Source distribution)
>Environment:
System: FreeBSD eggther.videoaxs.com 4.2-STABLE FreeBSD 4.2-STABLE #1: Tue Jan 30
14:13:24 GMT 2001 [EMAIL PROTECTED]:/usr/src/sys/compile/EGGTHER i386
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.2 19991024 (release)
Compilation info: CC='cc' CFLAGS='-O -pipe' CXX='c++' CXXFLAGS='-O -pipe
-felide-constructors -fno-rtti -fno-exceptions' LDFLAGS=''
LIBC:
-r--r--r-- 1 root wheel 1169390 Jan 30 05:11 /usr/lib/libc.a
lrwxr-xr-x 1 root wheel 9 Jan 30 05:11 /usr/lib/libc.so -> libc.so.4
-r--r--r-- 1 root wheel 559484 Jan 30 05:11 /usr/lib/libc.so.4
Configure command: ./configure --localstatedir=/var/db/mysql --without-perl
--without-debug --without-readline --without-bench --with-mit-threads=no
--with-libwrap --with-low-memory --enable-assembler --prefix=/usr/local
i386--freebsd4.2
Perl: This is perl, version 5.005_03 built for i386-freebsd
---------------------------------------------------------------------
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
