>Description:
if i use the following UNION statement, i do not get the same result as if i'm using
the two selects separately or with a temporary table. i do not know if this is a bug,
but i can not explain this behaviour. maybe someone can help me out...
>How-To-Repeat:
using the following query does get strange results:
(select distinct "a", if(name <> '',name,text1) as name, firstname,
tabPerson.classID, tabPerson.personID, 1 as contentID,
DATE_FORMAT(tabContent.date,'%d.%m.%Y') as fdate,
tabContent.contenttypeID, tabContent.title, tabContent.linkID, tabContent.timestamp
from ((tabPerson
LEFT JOIN tabPerson_Content ON tabPerson_Content.personID = tabPerson.personID)
LEFT JOIN tabContent ON tabContent.contentID = tabPerson_Content.contentID)
where (contenttypeID = 4 or contenttypeID = 9 or
(contenttypeID >= 29 AND contenttypeID <= 32)) AND
(tabContent.classID = 0 OR tabContent.classID = -1 OR tabContent.classID OR
ISNULL(tabContent.classID))
GROUP BY tabPerson.personID
ORDER BY tabContent.timestamp DESC LIMIT 10)
UNION ALL
(select "b", name, firstname, tabPerson.classID, tabPerson.personID,
tabContent.contentID, DATE_FORMAT(tabContent.date,'%d.%m.%Y') as fdate,
tabContent.contenttypeID, tabContent.title, tabContent.linkID, tabContent.timestamp
from ((tabContent
LEFT JOIN tabPerson_Content ON tabPerson_Content.contentID = tabContent.contentID)
LEFT JOIN tabPerson ON tabPerson.personID = tabPerson_Content.personID)
where (tabContent.contenttypeID = 12 or tabContent.contenttypeID = 13 or
(tabContent.contenttypeID >= 26 AND
tabContent.contenttypeID <= 28)) AND (tabContent.classID = 0 OR tabContent.classID =
-1 OR tabContent.classID OR
ISNULL(tabContent.classID))
GROUP BY tabContent.contentID
ORDER BY tabContent.timestamp DESC LIMIT 10)
ORDER BY timestamp DESC
LIMIT 10
while using this query, gets the correct results:
CREATE TEMPORARY TABLE tmp
select distinct "a", if(name <> '',name,text1) as name, firstname,
tabPerson.classID, tabPerson.personID, 1 as contentID,
DATE_FORMAT(tabContent.date,'%d.%m.%Y') as fdate,
tabContent.contenttypeID, tabContent.title, tabContent.linkID, tabContent.timestamp
from ((tabPerson
LEFT JOIN tabPerson_Content ON tabPerson_Content.personID = tabPerson.personID)
LEFT JOIN tabContent ON tabContent.contentID = tabPerson_Content.contentID)
where (contenttypeID = 4 or contenttypeID = 9 or
(contenttypeID >= 29 AND contenttypeID <= 32)) AND
(tabContent.classID = 0 OR tabContent.classID = -1 OR tabContent.classID OR
ISNULL(tabContent.classID))
GROUP BY tabPerson.personID
ORDER BY tabContent.timestamp DESC LIMIT 10;
INSERT INTO tmp
select "b", name, firstname, tabPerson.classID, tabPerson.personID,
tabContent.contentID, DATE_FORMAT(tabContent.date,'%d.%m.%Y') as fdate,
tabContent.contenttypeID, tabContent.title, tabContent.linkID, tabContent.timestamp
from ((tabContent
LEFT JOIN tabPerson_Content ON tabPerson_Content.contentID = tabContent.contentID)
LEFT JOIN tabPerson ON tabPerson.personID = tabPerson_Content.personID)
where (tabContent.contenttypeID = 12 or tabContent.contenttypeID = 13 or
(tabContent.contenttypeID >= 26 AND
tabContent.contenttypeID <= 28)) AND (tabContent.classID = 0 OR tabContent.classID =
-1 OR tabContent.classID OR
ISNULL(tabContent.classID))
GROUP BY tabContent.contentID
ORDER BY tabContent.timestamp DESC LIMIT 10;
SELECT * from tmp
ORDER BY timestamp DESC
LIMIT 10;
DROP TABLE tmp;
>Fix:
>Submitter-Id: <submitter ID>
>Originator: lru
>Organization:
pixworx multimedia
>Category: mysql
>Release: mysql-4.0.10-gamma-standard (Official MySQL-standard binary)
>C compiler: 2.95.3
>C++ compiler: 2.95.3
>Environment:
System: Linux paprika 2.4.18-4GB #1 Wed Mar 27 13:57:05 UTC 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/i486-suse-linux/2.95.3/specs
gcc version 2.95.3 20010315 (SuSE)
Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2
-mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS=''
LIBC:
-rwxr-xr-x 1 root root 1394302 Oct 2 10:06 /lib/libc.so.6
-rw-r--r-- 1 root root 25361748 Oct 2 09:36 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Mar 23 2002 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql' '--with-comment=Official
MySQL-standard binary' '--with-extra-charsets=complex'
'--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile'
'--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static'
'--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro'
'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' '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
