>Description:
Hello sirs,
Mr. Benjamin Pflugmann told me to send this problem to
[EMAIL PROTECTED]
I have a database structure as follows (simplyfied) :
CREATE TABLE masterTABLE (
pid int(11) unsigned NOT NULL default '0',
c1id int(11) unsigned default NULL,
c2id int(11) unsigned default NULL,
value int(11) unsigned NOT NULL default '0',
UNIQUE KEY pid2 (pid,c1id,c2id),
UNIQUE KEY pid (pid,value)
) TYPE=MyISAM;
INSERT INTO masterTABLE VALUES (1, 1, NULL, 1);
INSERT INTO masterTABLE VALUES (1, 2, NULL, 2);
INSERT INTO masterTABLE VALUES (1, NULL, 3, 3);
INSERT INTO masterTABLE VALUES (1, 4, NULL, 4);
INSERT INTO masterTABLE VALUES (1, 5, NULL, 5);
CREATE TABLE childTABLE1 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE1 VALUES (1, 'Yes');
INSERT INTO childTABLE1 VALUES (2, 'No');
INSERT INTO childTABLE1 VALUES (4, 'Yes');
INSERT INTO childTABLE1 VALUES (5, 'No');
CREATE TABLE childTABLE2 (
id int(11) unsigned NOT NULL default '0',
active enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
INSERT INTO childTABLE2 VALUES (3, 'Yes');
If I do this query:
SELECT MAX( value )
FROM masterTABLE AS m
LEFT JOIN childTABLE1 AS c1
ON m.c1id = c1.id AND
c1.active = 'Yes'
LEFT JOIN childTABLE2 AS c2
ON m.c2id = c2.id
AND c2.active = 'Yes'
WHERE m.pid=1
AND (c1.id IS NOT NULL OR c2.id IS NOT NULL)
the Result will be "5" which is probably wrong.
The expected Result ist "4".
The correct Result will be returned if you remove
both UNIQUE KEYs (pid and pid2) from Table masterTABLE.
This behaviour ist tested on MySQL 3.23.51 and now even
on MySQL 3.23.53.
TIA
>How-To-Repeat:
Code Sample above
>Fix:
??
>Submitter-Id: <submitter ID>
>Originator: Stephan Skusa
>Organization: Lippe-Net Online-Service
>MySQL support: none
>Synopsis: Strange behaviour of MAX() in combination with UNIQUE KEY's
>Severity: non-critical
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.53 (Source distribution)
>Environment:
System: Linux akademos 2.2.18 #1 Fri Jan 19 22:10:35 GMT 2001 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/i486-suse-linux/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='-O6' CXX='gcc' CXXFLAGS='-O6' LDFLAGS=''
LIBC:
-rwxr-xr-x 1 root root 1382179 Jan 19 2001 /lib/libc.so.6
-rw-r--r-- 1 root root 2585872 Jan 19 2001 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Jan 19 2001 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql-3.23.53
--with-charset=german1 --with-innodb CC=gcc CFLAGS=-O6 CXXFLAGS=-O6 CXX=gcc
---------------------------------------------------------------------
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
