Bug #59946 | "Unknown column in from clause" | ||
---|---|---|---|
Submitted: | 4 Feb 2011 14:13 | Modified: | 4 Feb 2011 20:09 |
Reporter: | Margaret MacDonald | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Parser | Severity: | S2 (Serious) |
Version: | 5.1.45 | OS: | Windows (W2KSP4) |
Assigned to: | CPU Architecture: | Any |
[4 Feb 2011 14:13]
Margaret MacDonald
[4 Feb 2011 14:31]
Valeriy Kravchuk
Please, send exact CREATE TABLE statements for both tables used.
[4 Feb 2011 17:37]
Margaret MacDonald
'CREATE TABLE TableB ( CommonFld INT UNSIGNED NOT NULL DEFAULT 0, Created DATETIME NOT NULL DEFAULT 0, Deleted BOOLEAN NOT NULL DEFAULT 0, OwnerID INT UNSIGNED NOT NULL DEFAULT 0, CategoryID INT UNSIGNED NOT NULL DEFAULT 0, CategoryName VARCHAR(255) NOT NULL DEFAULT "", Priority TINYINT UNSIGNED NOT NULL DEFAULT 0, Title VARCHAR(128) NOT NULL DEFAULT "", Body TEXT NOT NULL, ActionWanted VARCHAR(255) NOT NULL DEFAULT "", LastMod TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, DisplayFrom DATE NOT NULL DEFAULT 0, DisplayThru DATE NOT NULL DEFAULT 0, UNIQUE KEY RECID (CommonFld,DisplayThru), KEY OwnerID (OwnerID), KEY DisplayFrom (DisplayFrom), KEY DisplayThru (DisplayThru) ) PARTITION BY RANGE( TO_DAYS( DisplayThru ) ) ( PARTITION P00 VALUES LESS THAN (TO_DAYS("2010-05-01")), PARTITION P01 VALUES LESS THAN (TO_DAYS("2011-06-01")), PARTITION P02 VALUES LESS THAN (TO_DAYS("2011-07-01")), PARTITION P03 VALUES LESS THAN (TO_DAYS("2011-08-01")), PARTITION P04 VALUES LESS THAN (TO_DAYS("2011-09-01")), PARTITION P05 VALUES LESS THAN (TO_DAYS("2011-10-01")), PARTITION P06 VALUES LESS THAN (TO_DAYS("2011-11-01")), PARTITION P07 VALUES LESS THAN (TO_DAYS("2011-12-01")), PARTITION P08 VALUES LESS THAN (TO_DAYS("2012-01-01")), PARTITION P09 VALUES LESS THAN MAXVALUE ) ' ; 'CREATE TABLE TableF ( CommonFldD INT UNSIGNED NOT NULL DEFAULT 0, Deleted BOOLEAN NOT NULL DEFAULT 0, OwnerID INT UNSIGNED NOT NULL DEFAULT 0, DomainID INT UNSIGNED NOT NULL DEFAULT 0, Level TINYINT UNSIGNED NOT NULL DEFAULT 0, KEY RECID (CommonFld), KEY OwnerID (OwnerID), KEY DomainID (DomainID) ) PARTITION BY RANGE ( DomainID ) ( PARTITION P01 VALUES LESS THAN (100000), PARTITION P02 VALUES LESS THAN (200000), PARTITION P03 VALUES LESS THAN (300000), PARTITION P04 VALUES LESS THAN (400000), PARTITION P05 VALUES LESS THAN (500000), PARTITION P06 VALUES LESS THAN (600000), PARTITION P07 VALUES LESS THAN (700000), PARTITION P08 VALUES LESS THAN (800000), PARTITION P09 VALUES LESS THAN (900000), PARTITION P10 VALUES LESS THAN MAXVALUE )' ;
[4 Feb 2011 17:58]
Valeriy Kravchuk
This is what I've seen when copy/pasted your table definitions: macbook-pro:5.1 openxs$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.56-debug Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE TableB ( -> CommonFld INT UNSIGNED NOT NULL DEFAULT 0, -> Created DATETIME NOT NULL DEFAULT 0, -> Deleted BOOLEAN NOT NULL DEFAULT 0, -> -> OwnerID INT UNSIGNED NOT NULL DEFAULT 0, -> -> CategoryID INT UNSIGNED NOT NULL DEFAULT 0, -> CategoryName VARCHAR(255) NOT NULL DEFAULT "", -> -> Priority TINYINT UNSIGNED NOT NULL DEFAULT 0, -> Title VARCHAR(128) NOT NULL DEFAULT "", -> Body TEXT NOT NULL, -> ActionWanted VARCHAR(255) NOT NULL DEFAULT "", -> LastMod TIMESTAMP NOT NULL DEFAULT -> CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> DisplayFrom DATE NOT NULL DEFAULT 0, -> DisplayThru DATE NOT NULL DEFAULT 0, -> -> UNIQUE KEY RECID (CommonFld,DisplayThru), -> KEY OwnerID (OwnerID), -> KEY DisplayFrom (DisplayFrom), -> KEY DisplayThru (DisplayThru) -> ) -> PARTITION BY RANGE( TO_DAYS( DisplayThru ) ) -> ( -> PARTITION P00 VALUES LESS THAN (TO_DAYS("2010-05-01")), -> PARTITION P01 VALUES LESS THAN (TO_DAYS("2011-06-01")), -> PARTITION P02 VALUES LESS THAN (TO_DAYS("2011-07-01")), -> PARTITION P03 VALUES LESS THAN (TO_DAYS("2011-08-01")), -> PARTITION P04 VALUES LESS THAN (TO_DAYS("2011-09-01")), -> PARTITION P05 VALUES LESS THAN (TO_DAYS("2011-10-01")), -> PARTITION P06 VALUES LESS THAN (TO_DAYS("2011-11-01")), -> PARTITION P07 VALUES LESS THAN (TO_DAYS("2011-12-01")), -> PARTITION P08 VALUES LESS THAN (TO_DAYS("2012-01-01")), -> PARTITION P09 VALUES LESS THAN MAXVALUE -> ) -> ; Query OK, 0 rows affected (0.16 sec) mysql> CREATE TABLE TableF ( -> CommonFldD INT UNSIGNED NOT NULL DEFAULT 0, -> Deleted BOOLEAN NOT NULL DEFAULT 0, -> -> OwnerID INT UNSIGNED NOT NULL DEFAULT 0, -> DomainID INT UNSIGNED NOT NULL DEFAULT 0, -> -> Level TINYINT UNSIGNED NOT NULL DEFAULT 0, -> -> KEY RECID (CommonFld), -> KEY OwnerID (OwnerID), -> KEY DomainID (DomainID) -> ) -> PARTITION BY RANGE ( DomainID ) -> ( -> PARTITION P01 VALUES LESS THAN (100000), -> PARTITION P02 VALUES LESS THAN (200000), -> PARTITION P03 VALUES LESS THAN (300000), -> PARTITION P04 VALUES LESS THAN (400000), -> PARTITION P05 VALUES LESS THAN (500000), -> PARTITION P06 VALUES LESS THAN (600000), -> PARTITION P07 VALUES LESS THAN (700000), -> PARTITION P08 VALUES LESS THAN (800000), -> PARTITION P09 VALUES LESS THAN (900000), -> PARTITION P10 VALUES LESS THAN MAXVALUE -> ); ERROR 1072 (42000): Key column 'CommonFld' doesn't exist in table Note that second table can not be created. Why? Because of this index declaration: KEY RECID (CommonFld), that refers to non-existent column. Instead, you have column with a slightly different name: CommonFldD INT UNSIGNED NOT NULL DEFAULT 0, Probably you have the same problem with your SELECT... Please, check and/or fix column name(s).
[4 Feb 2011 18:32]
Margaret MacDonald
argggh, sorry, no, that extra D is a typo...it's not in the actual table creation. Both tables created fine, and have been read successfuly many times using the (very!) slow access method I'm trying to replace.
[4 Feb 2011 18:45]
Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php You provided the query: SELECT * FROM ( SELECT DISTINCT CommonFld FROM TableB LIMIT N OFFSET M ) AS B JOIN ( SELECT OtherField FROM TableF ) AS F USING ( CommonFld ) But there is no CommonFld in ( SELECT OtherField FROM TableF ) AS F. So closing as "Not a Bug"
[4 Feb 2011 20:09]
Margaret MacDonald
Thank you. It took me a few moments to understand what you were talking about. I think the error message could be more informative, or the docs changed. It's not at all obvious to someone not a specialist in databases that subqueries are treated as virtual tables, or that the linking field in a join must be explicitly selected by those virtual-table subqueries. One might reasonably suppose that the interpreter would handle that under the table (I now know it doesn't, but there's no reason it couldn't--it does many other more complicated things "for free") A better error message might be "USING column must appear in all subqueries"