List:General Discussion« Previous MessageNext Message »
From:James Black Date:October 12 2005 1:57pm
Subject:re: possible bug in mysql 5.0.13
View as plain text  
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Due to the complexity of my query I don't know how to get this down to a
simple test case to demonstrate the error.

This works under mysql 4.1.8 btw, so it is failing due to a change
introduced recently.  It also worked under mysql 5.0.9, but I haven't
tested any of the beta versions between 9 and 13.

Any suggestions as to what I may look at to make this simpler?

I will be working on it in the meantime, to see what I find.

mysql> INSERT INTO curuse(rid, start, badge, ip, card_type, fullname,
dept, college, campus)
SELECT 12612,1129124442, n.badge, 0,
(SELECT role FROM nams.names WHERE badge=n.badge),
(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.netid='jblack') AS fullnameTable,
(SELECT d.dabbr FROM nams.affiliations f, nams.roles r, nams.departments
d WHERE f.role=r.code AND f.deptid=d.deptid AND f.badge=n.badge AND
d.dabbr !='' ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1), (SELECT
f.college FROM nams.affiliations f, nams.roles r, nams.colleges d WHERE
f.role=r.code AND f.college=d.code AND f.badge=n.badge AND d.code !=''
ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1), (SELECT f.campus FROM
nams.affiliations f, nams.roles r, nams.departments d WHERE
f.role=r.code AND f.deptid=d.deptid AND f.badge=n.badge AND d.dabbr !=''
ORDER BY r.priority DESC, f.orgdate DESC LIMIT 1) FROM items i,
nams.netids n INNER JOIN nams.names AS na ON n.badge=na.badge INNER JOIN
nams.affiliations AS a ON a.badge=na.badge INNER JOIN nams.roles AS r ON
a.role=r.code

LEFT OUTER JOIN curuse AS c ON (c.rid=i.rid)

WHERE r.lmsvalid='Y' AND a.enddate IS NULL AND i.rid=1999 AND
n.netid='jblack' AND (i.status='A' OR c.badge IS NULL) LIMIT 1;

ERROR 1054 (42S22): Unknown column 'i.rid' in 'on clause'

+----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+----------------+
| rid | int(11)  | NO | PRI | NULL | auto_increment |
| lid | int(11)  | NO | MUL | 0 | |
| itemtype | char(4)  | NO | MUL | | |
| label | char(12) | NO | UNI | | |
| status | char(1)  | NO | | | |
| layoutx | int(11)  | YES | | NULL | |
| layouty | int(11)  | YES | | NULL | |
| theta | int(11)  | YES | | NULL | |
+----------+----------+------+-----+---------+----------------+
8 rows in set (0.02 sec) mysql> describe curuse;

+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| rid | int(11)  | NO | PRI | 0 | |
| start | int(11)  | NO | | 0 | |
| badge | int(11)  | NO | MUL | 0 | |
| card_type | char(2)  | NO | | | |
| dept | char(3)  | NO | | | |
| college | char(2)  | NO | | | |
| campus | char(1)  | NO | || |
| fullname | varchar(24) | NO | | | |
| ip | varchar(40) | NO | PRI | ||
| alive | int(11)  | NO | | 0 | |
+-----------+-------------+------+-----+---------+-------+
10 rows in set (0.02 sec)
- --
"Love is mutual self-giving that ends in self-recovery." Fulton Sheen
James Black    james@stripped
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFDTRZfikQgpVn8xrARAo5LAJ0fkAmKtxtMHHGWMY2kcnSOuXVCqQCbB4sH
A4h+LzTgcZns66WLG2xOp9c=
=YIgR
-----END PGP SIGNATURE-----
Thread
re: possible bug in mysql 5.0.13James Black12 Oct
  • re: possible bug in mysql 5.0.13SGreen12 Oct
    • Re: possible bug in mysql 5.0.13Peter Brawley12 Oct
      • Re: possible bug in mysql 5.0.13James Black12 Oct
      • Re: possible bug in mysql 5.0.13James Black12 Oct
        • Re: possible bug in mysql 5.0.13SGreen12 Oct
          • Re: possible bug in mysql 5.0.13James Black12 Oct
          • Re: possible bug in mysql 5.0.13Peter Brawley12 Oct
            • Re: possible bug in mysql 5.0.13SGreen13 Oct