List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:October 12 2005 3:49pm
Subject:Re: possible bug in mysql 5.0.13
View as plain text  
James,

You can reproduce that error by writing ...

  SELECT ...
  FROM a, b INNER JOIN c ON a.x=c.y

The error goes away if you instead write ...

  SELECT ...
  FROM b, a INNER JOIN c ON A.x=c.y

so you might try swapping

FROM items i
, nams.netids n 

PB

-----

SGreen@stripped wrote:

>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
>  
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.344 / Virus Database: 267.11.14/130 - Release Date: 10/12/2005
>
>  
>

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.14/130 - Release Date: 10/12/2005
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