List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 12 2005 2:42pm
Subject:re: possible bug in mysql 5.0.13
View as plain text  
James Black <jblack@stripped> wrote on 10/12/2005 09:57:51 AM:

> -----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-----
> 

Just to help the rest of us, here is his query reformatted only (no 
changes except for spacing and tabbing). Some of the longer CONCAT() 
functions will wrap but the majority of it should be much easier to read:

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;

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
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