Today, Sinisa Milivojevic ([EMAIL PROTECTED]) wrote:
> [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
Sorry, this is my fault for not describing the problem better.
Here's the result I get with DISTINCT (I've given the columns
names to make the table smaller, but not changed the SQL from what
I sent in the bug report):
+---+
| i |
+---+
| 1 |
| 2 |
+---+
+---+-----+-----+-----+----------------+
| i | vv1 | vv2 | vv3 | @vv1+@vv2+@vv3 |
+---+-----+-----+-----+----------------+
| 1 | 1 | 0 | 1 | 1 |
| 2 | 1 | 0 | 0 | 1 |
+---+-----+-----+-----+----------------+
Note that the sum is not correct: the last column values
should be 2 and 1, not 1 and 1. With DISTINCT, the sum
value is always correct for the very last row, and the
sum value for all other rows is the same as the
sum value in the last row!
Here's the result without DISTINCT:
+---+
| i |
+---+
| 1 |
| 2 |
+---+
+---+-----+-----+-----+----------------+
| i | vv1 | vv2 | vv3 | @vv1+@vv2+@vv3 |
+---+-----+-----+-----+----------------+
| 1 | 1 | 0 | 1 | 2 |
| 2 | 1 | 0 | 0 | 1 |
+---+-----+-----+-----+----------------+
As you can see, the sums are now correct.
After I posted the bug report, I noticed that the same problem
occurs if I used ORDER BY. Probably a hint in there somewhere ;)
Sorry for the poor report!
Gary
---------------------------------------------------------------------
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
