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:
None 
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
Description:
I have 2 tables related by some column CommonFld.  TableB has basic records, and each CommonFld is unique within it.  TableF has zero or more records related to each record in TableB.  I want to paginate the data by selecting N records at some offset M from TableB JOINED with the relevant records if any from TableF.

This fails with an "Unknown column CommonFld in FROM clause":

SELECT * FROM ( SELECT DISTINCT CommonFld FROM TableB LIMIT N OFFSET M ) AS B
         JOIN ( SELECT OtherField FROM TableF ) AS F
         USING ( CommonFld )  

How to repeat:
See above
[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"