From: Michael Dykman Date: August 11 2010 8:34pm Subject: Re: STRAIGHT JOIN vs. field names List-Archive: http://lists.mysql.com/mysql/222496 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable The relationship looks righteous enough but I note that you use 'straight join' in your expression, rather than 'straight_join' as indicated in the manual (http://dev.mysql.com/doc/refman/5.1/en/join.html). Perhaps the message is a red herring and your trouble is elsewhere? - michael dykman On Wed, Aug 11, 2010 at 4:25 PM, Mike Spreitzer wrote= : > Why is it that a field name that works fine for a JOIN is invalid in a > STRAIGHT JOIN? > > mysql> show create table fldsndm; > +---------+--------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------------------+ > | Table =A0 | Create Table =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0| > +---------+--------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------------------+ > | fldsndm | CREATE TABLE `fldsndm` ( > =A0`p` varchar(200) NOT NULL, > =A0`cd` datetime NOT NULL, > =A0`cms` smallint(6) NOT NULL, > =A0`pip` char(15) NOT NULL, > =A0`pport` smallint(6) NOT NULL, > =A0`pboot` bigint(20) NOT NULL, > =A0`msgid` bigint(20) NOT NULL, > =A0`startgtime` bigint(20) NOT NULL, > =A0`datalen` int(11) NOT NULL, > =A0`toself` tinyint(1) DEFAULT NULL, > =A0`sepoch` bigint(20) NOT NULL DEFAULT '0', > =A0`c` decimal(11,3) NOT NULL DEFAULT '0.000', > =A0UNIQUE KEY `pbm` (`p`,`pboot`,`msgid`) > ) ENGINE=3DMEMORY DEFAULT CHARSET=3Dlatin1 | > +---------+--------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------------------+ > 1 row in set (0.00 sec) > > mysql> show create table fldrcv; > +--------+---------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------------------+ > | Table =A0| Create Table =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | > +--------+---------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------------------+ > | fldrcv | CREATE TABLE `fldrcv` ( > =A0`p` varchar(200) NOT NULL, > =A0`cd` datetime NOT NULL, > =A0`cms` smallint(6) NOT NULL, > =A0`pip` char(15) NOT NULL, > =A0`pport` smallint(6) NOT NULL, > =A0`pboot` bigint(20) DEFAULT NULL, > =A0`qip` char(15) NOT NULL, > =A0`qport` smallint(6) NOT NULL, > =A0`qboot` bigint(20) DEFAULT NULL, > =A0`msgid` bigint(20) NOT NULL, > =A0`startgtime` bigint(20) NOT NULL, > =A0`datalen` int(11) NOT NULL, > =A0`q` varchar(200) DEFAULT NULL, > =A0`repoch` bigint(20) NOT NULL DEFAULT '0', > =A0`c` decimal(11,3) NOT NULL DEFAULT '0.000', > =A0KEY `c` (`c`), > =A0KEY `pec` (`p`,`repoch`,`c`), > =A0KEY `peqms` (`p`,`repoch`,`q`,`msgid`,`startgtime`), > =A0KEY `qbm` (`q`,`qboot`,`msgid`), > =A0KEY `pbm` (`p`,`pboot`,`msgid`) > ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 | > +--------+---------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------------------+ > 1 row in set (0.00 sec) > > mysql> explain extended select * from fldrcv join fldsndm on > (fldrcv.q=3Dfldsndm.p AND fldrcv.qboot=3Dfldsndm.pboot and > fldrcv.msgid=3Dfldsndm.msgid); > +----+-------------+---------+------+---------------+------+---------+---= ---------------------------------------------------------------------------= ---------------+-------+----------+-------------+ > | id | select_type | table =A0 | type | possible_keys | key =A0| key_len = | ref > =A0 =A0 =A0 =A0 =A0 =A0 =A0| rows =A0| filtered | Extra =A0 =A0 =A0 | > +----+-------------+---------+------+---------------+------+---------+---= ---------------------------------------------------------------------------= ---------------+-------+----------+-------------+ > | =A01 | SIMPLE =A0 =A0 =A0| fldsndm | ALL =A0| pbm =A0 =A0 =A0 =A0 =A0 |= NULL | NULL =A0 =A0| > NULL =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 29036 | =A0 100.00 | =A0 =A0 = =A0 =A0 =A0 =A0 | > | =A01 | SIMPLE =A0 =A0 =A0| fldrcv =A0| ref =A0| qbm =A0 =A0 =A0 =A0 =A0= | qbm =A0| 220 =A0 =A0 | > bigCell2906_flood.fldsndm.p,bigCell2906_flood.fldsndm.pboot,bigCell2906_f= lood.fldsndm.msgid > | =A0 452 | =A0 100.00 | Using where | > +----+-------------+---------+------+---------------+------+---------+---= ---------------------------------------------------------------------------= ---------------+-------+----------+-------------+ > 2 rows in set, 1 warning (0.00 sec) > > mysql> explain extended select * from fldrcv straight join fldsndm on > (fldrcv.q=3Dfldsndm.p AND fldrcv.qboot=3Dfldsndm.pboot and > fldrcv.msgid=3Dfldsndm.msgid); > ERROR 1054 (42S22): Unknown column 'fldrcv.q' in 'on clause' > mysql> > > Thanks, > Mike Spreitzer > --=20 =A0- michael dykman =A0- mdykman@stripped =A0May the Source be with you.