Bug #87921 Unknown column in on clause even the column exists

• Submitted: 29 Sep 2017 18:22
• Modified: 30 Sep 2017 19:29
• Reporter: Martin Cetkovsk
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.7.19
• OS: Oracle Linux (Docker)
• CPU Architecture: Any

[29 Sep 2017 18:22] 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

        `wa`.`employeeId` = `ps`.`employeeId`

with

        `wa`.`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`,
    tableFO AS `fo`
LEFT JOIN
    tableWA AS `wa`
ON
    `wa`.`employeeId` = `ps`.`employeeId`;

[30 Sep 2017 19:29] MySQL Verification Team

Thank you for the bug report.

https://dev.mysql.com/doc/refman/5.7/en/join.html

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-log Source distribution 2017-SEP-06

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 5.7 > create database db;
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > use db
Database changed
mysql 5.7 > CREATE TABLE tablePS (
    -> `id` INT UNSIGNED auto_increment,
    ->     `employeeId` INT UNSIGNED,
    ->     PRIMARY KEY `id` (`id`)
    -> );
Query OK, 0 rows affected (0.06 sec)

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

mysql 5.7 >
mysql 5.7 > CREATE TABLE tableWA (
    -> `id` INT UNSIGNED auto_increment,
    ->     `employeeId` INT UNSIGNED,
    ->     PRIMARY KEY `id` (`id`)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql 5.7 >
mysql 5.7 > SELECT
    ->     `ps`.`employeeId`
    -> FROM
    ->     tablePS AS `ps`,
    ->     tableFO AS `fo`
    -> LEFT JOIN
    ->     tableWA AS `wa`
    -> ON
    ->     `wa`.`employeeId` = `ps`.`employeeId`;
ERROR 1054 (42S22): Unknown column 'ps.employeeId' in 'on clause'

mysql 5.7 > SELECT
    ->     `ps`.`employeeId`
    -> FROM
    ->     (tablePS AS `ps`,
    ->     tableFO AS `fo`)
    -> LEFT JOIN
    ->     tableWA AS `wa`
    -> ON
    ->     `wa`.`employeeId` = `ps`.`employeeId`;
Empty set (0.00 sec)