| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.7.19 | OS: | Oracle Linux (Docker) |
| Assigned to: | CPU Architecture: | Any | |
[29 Sep 2017 18:11]
MySQL Verification Team
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
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`;