List:General Discussion« Previous MessageNext Message »
From:Mike Spreitzer Date:August 11 2010 8:25pm
Subject:STRAIGHT JOIN vs. field names
View as plain text  
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

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