List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:August 11 2010 8:34pm
Subject:Re: STRAIGHT JOIN vs. field names
View as plain text  
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 <mspreitz@stripped> 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   | Create Table                
>                    |
>
> +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | fldsndm | CREATE TABLE `fldsndm` (
>  `p` varchar(200) NOT NULL,
>  `cd` datetime NOT NULL,
>  `cms` smallint(6) NOT NULL,
>  `pip` char(15) NOT NULL,
>  `pport` smallint(6) NOT NULL,
>  `pboot` bigint(20) NOT NULL,
>  `msgid` bigint(20) NOT NULL,
>  `startgtime` bigint(20) NOT NULL,
>  `datalen` int(11) NOT NULL,
>  `toself` tinyint(1) DEFAULT NULL,
>  `sepoch` bigint(20) NOT NULL DEFAULT '0',
>  `c` decimal(11,3) NOT NULL DEFAULT '0.000',
>  UNIQUE KEY `pbm` (`p`,`pboot`,`msgid`)
> ) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
>
> +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> 1 row in set (0.00 sec)
>
> mysql> show create table fldrcv;
>
> +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | Table  | Create Table                
>     |
>
> +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | fldrcv | CREATE TABLE `fldrcv` (
>  `p` varchar(200) NOT NULL,
>  `cd` datetime NOT NULL,
>  `cms` smallint(6) NOT NULL,
>  `pip` char(15) NOT NULL,
>  `pport` smallint(6) NOT NULL,
>  `pboot` bigint(20) DEFAULT NULL,
>  `qip` char(15) NOT NULL,
>  `qport` smallint(6) NOT NULL,
>  `qboot` bigint(20) DEFAULT NULL,
>  `msgid` bigint(20) NOT NULL,
>  `startgtime` bigint(20) NOT NULL,
>  `datalen` int(11) NOT NULL,
>  `q` varchar(200) DEFAULT NULL,
>  `repoch` bigint(20) NOT NULL DEFAULT '0',
>  `c` decimal(11,3) NOT NULL DEFAULT '0.000',
>  KEY `c` (`c`),
>  KEY `pec` (`p`,`repoch`,`c`),
>  KEY `peqms` (`p`,`repoch`,`q`,`msgid`,`startgtime`),
>  KEY `qbm` (`q`,`qboot`,`msgid`),
>  KEY `pbm` (`p`,`pboot`,`msgid`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
>
> +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> 1 row in set (0.00 sec)
>
> mysql> explain extended select * from fldrcv join fldsndm on
> (fldrcv.q=fldsndm.p AND fldrcv.qboot=fldsndm.pboot and
> fldrcv.msgid=fldsndm.msgid);
>
> +----+-------------+---------+------+---------------+------+---------+---------------------------------------------------------------------------------------------+-------+----------+-------------+
> | id | select_type | table   | type | possible_keys | key  | key_len | ref
>              | rows  | filtered | Extra
>       |
>
> +----+-------------+---------+------+---------------+------+---------+---------------------------------------------------------------------------------------------+-------+----------+-------------+
> |  1 | SIMPLE      | fldsndm | ALL  | pbm    
>       | NULL | NULL    |
> NULL                  | 29036 |  
> 100.00 |             |
> |  1 | SIMPLE      | fldrcv  | ref  | qbm    
>       | qbm  | 220     |
>
> bigCell2906_flood.fldsndm.p,bigCell2906_flood.fldsndm.pboot,bigCell2906_flood.fldsndm.msgid
> |   452 |   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=fldsndm.p AND fldrcv.qboot=fldsndm.pboot and
> fldrcv.msgid=fldsndm.msgid);
> ERROR 1054 (42S22): Unknown column 'fldrcv.q' in 'on clause'
> mysql>
>
> Thanks,
> Mike Spreitzer
>



-- 
 - michael dykman
 - mdykman@stripped

 May the Source be with you.
Thread
idle queryMike Spreitzer27 Jul
  • Re: idle queryDan Nelson27 Jul
    • Re: idle queryMike Spreitzer27 Jul
      • Re: idle queryDan Nelson27 Jul
        • Re: idle queryMike Spreitzer27 Jul
          • Re: idle queryDan Nelson27 Jul
            • Re: idle queryMike Spreitzer27 Jul
              • Re: idle queryDan Nelson27 Jul
            • Re: idle queryDan Nelson27 Jul
              • Re: idle queryMike Spreitzer28 Jul
    • RE: idle queryJerry Schwartz27 Jul
    • Re: idle queryMike Spreitzer11 Aug
      • STRAIGHT JOIN vs. field namesMike Spreitzer11 Aug
        • Re: STRAIGHT JOIN vs. field namesMichael Dykman11 Aug
          • Re: STRAIGHT JOIN vs. field namesMike Spreitzer11 Aug
      • Re: idle queryMike Spreitzer12 Aug
        • Re: idle queryMySQL)18 Aug