[EMAIL PROTECTED] writes:
> >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
>
Hi!
This query worked perfectly for me.
I have got result set with 5 int columns.
Regards,
Sinisa
____ __ _____ _____ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
/*/ \*\ Developers Team
---------------------------------------------------------------------
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
