Bug #87918 Unknown column in on clause even the column exists
Submitted: 29 Sep 2017 14:30 Modified: 29 Sep 2017 18:24
Reporter: Martin Cetkovsk Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7.19 OS:Oracle Linux (Docker)
Assigned to: CPU Architecture:Any

[29 Sep 2017 14:30] Martin Cetkovsk
Description:
The mentioned SELECT command raises Error Code: 1054. Unknown column 'ps.employeeId' in 'on clause'.

The script runs without an error is I remove the second table in the FROM clause

    ,
            tableFO AS `fo`

or replace the ON clause

        `wageAssessment`.`employeeId` = `ps`.`employeeId`

with

        `wageAssessment`.`employeeId` = 4

or when converting to using an explicit CROSS JOIN by replacing

        tablePS AS `ps`,
        tableFO AS `fo`

WITH

        tablePS AS `ps`
    CROSS JOIN
        tableFO AS `fo`

How to repeat:
CREATE TABLE tablePS (
	`id` INT UNSIGNED auto_increment,
    `employeeId` INT UNSIGNED,
    PRIMARY KEY `id` (`id`)
);

CREATE TABLE tableFO (
	`id` INT UNSIGNED auto_increment,
    PRIMARY KEY `id` (`id`)
);

CREATE TABLE tableWA (
	`id` INT UNSIGNED auto_increment,
    `employeeId` INT UNSIGNED,
    PRIMARY KEY `id` (`id`)
);

SELECT
    `ps`.`employeeId`
FROM
    tablePS AS `ps`
CROSS JOIN
    tableFO AS `fo`
LEFT JOIN
    tableWA AS `wa`
ON
    `wa`.`employeeId` = `ps`.`employeeId`;
[29 Sep 2017 18:11] Miguel Solorzano
Thank you for the bug report. I couldn't repeat:

miguel@tikal:~ $ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE x;
Query OK, 1 row affected (0.00 sec)

mysql> USE x
Database changed
mysql> CREATE TABLE tablePS (
    -> `id` INT UNSIGNED auto_increment,
    ->     `employeeId` INT UNSIGNED,
    ->     PRIMARY KEY `id` (`id`)
    -> );
Query OK, 0 rows affected (0.72 sec)

mysql> 
mysql> CREATE TABLE tableFO (
    -> `id` INT UNSIGNED auto_increment,
    ->     PRIMARY KEY `id` (`id`)
    -> );
Query OK, 0 rows affected (0.74 sec)

mysql> 
mysql> CREATE TABLE tableWA (
    -> `id` INT UNSIGNED auto_increment,
    ->     `employeeId` INT UNSIGNED,
    ->     PRIMARY KEY `id` (`id`)
    -> );
Query OK, 0 rows affected (0.67 sec)
                                                                                                                                                                                                
mysql>                                                                                                                                                                                          
mysql> SELECT                                                                                                                                                                                   
    ->     `ps`.`employeeId`                                                                                                                                                                    
    -> FROM
    ->     tablePS AS `ps`
    -> CROSS JOIN
    ->     tableFO AS `fo`
    -> LEFT JOIN
    ->     tableWA AS `wa`
    -> ON
    ->     `wa`.`employeeId` = `ps`.`employeeId`;
Empty set (0.00 sec)

mysql> exit
Bye
miguel@tikal:~ $ sudo lsb_release -a
[sudo] password for miguel: 
LSB Version:    :core-4.1-amd64:core-4.1-noarch:cxx-4.1-amd64:cxx-4.1-noarch:desktop-4.1-amd64:desktop-4.1-noarch:languages-4.1-amd64:languages-4.1-noarch:printing-4.1-amd64:printing-4.1-noarch
Distributor ID: CentOS
Description:    CentOS Linux release 7.4.1708 (Core) 
Release:        7.4.1708
Codename:       Core
miguel@tikal:~ $
[29 Sep 2017 18:24] Martin Cetkovsk
I apologize for placing a wrong snippet in the How to repro field.
You should be able to repro this one: https://bugs.mysql.com/bug.php?id=87921