James Black <[EMAIL PROTECTED]> wrote on 07/18/2005 08:42:31 AM:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> I have a query that uses a subquery that works under mysql 4.1 and
> 5.0.6, but doesn't work on 5.0.7 or 5.0.9
>
> I am trying to understand why it may be having a problem so I can make a
> test case to demonstrate it, and am hoping that someone may have an
idea.
>
> SELECT i.label, i.itemtype, it.name, i.rid, i.status, l.locname,
> i.lid,
> (SELECT IF(p.fullname IS NULL, (SELECT CONCAT(sna.fname, ' ',
> sna.mname, ' ', sna.lname, ' ', sna.gen) FROM nams.names AS sna WHERE
> sna.badge=sne.badge), p.fullname) AS fullname FROM nams.netids AS sne
> LEFT OUTER JOIN nams.prefs AS p ON(sne.badge=p.badge) WHERE
> sne.badge=c.badge) AS UserFullname,
> i.layoutx, i.layouty, i.theta FROM items AS i INNER JOIN
> locations AS l ON(i.lid=l.lid) INNER JOIN itemtypes AS it
> ON(i.itemtype=it.itemtype) LEFT OUTER JOIN curuse AS c ON(c.rid=i.rid)
> WHERE l.locname='iclab' ORDER BY i.status, i.label;
>
>
> If I take out the following part then the query works fine.
> (SELECT IF(p.fullname IS NULL, (SELECT CONCAT(sna.fname, ' ',
> sna.mname, ' ', sna.lname, ' ', sna.gen) FROM nams.names AS sna WHERE
> sna.badge=sne.badge), p.fullname) AS fullname FROM nams.netids AS sne
> LEFT OUTER JOIN nams.prefs AS p ON(sne.badge=p.badge) WHERE
> sne.badge=c.badge) AS UserFullname,
>
>
> - --
> "Love is mutual self-giving that ends in self-recovery." Fulton Sheen
> James Black [EMAIL PROTECTED]
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (MingW32)
> Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
>
> iD8DBQFC26O3ikQgpVn8xrARArFEAKCPBVaU85aA4gR0DOjcoN5lNxqvuwCghLAO
> bGNL/jNf65hw6u8RBOFR2Eg=
> =N9SC
> -----END PGP SIGNATURE-----
(same original query, unfolded and tabified)
SELECT i.label
, i.itemtype
, it.name
, i.rid
, i.status
, l.locname
, i.lid
, (
SELECT IF(
p.fullname IS NULL
, (
SELECT CONCAT(sna.fname, '
',sna.mname, ' ', sna.lname, ' ', sna.gen)
FROM nams.names AS sna
WHERE sna.badge=sne.badge
)
, p.fullname
) AS fullname
FROM nams.netids AS sne
LEFT OUTER JOIN nams.prefs AS p
ON (sne.badge=p.badge)
WHERE sne.badge=c.badge
) AS UserFullname
, i.layoutx
, i.layouty
, i.theta
FROM items AS i
INNER JOIN locations AS l
ON (i.lid=l.lid)
INNER JOIN itemtypes AS it
ON (i.itemtype=it.itemtype)
LEFT OUTER JOIN curuse AS c
ON (c.rid=i.rid)
WHERE l.locname='iclab'
ORDER BY i.status, i.label;
Can you show us examples of "correct" and "incorrect" results and point
out the differences? Can you post the definitions (SHOW CREATE TABLE
xxx\G) for the following tables: items, curuse, netids, prefs, & names?
Have you verified identical data between the two versions you are
comparing? Have you compared the EXPLAIN results for both versions? How
well did the non-subquery version that I posted on the 13th perform?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
