MySQL Bugs: #99037: unknown colum X in field list where X is derived from an expression

Bug #99037	unknown colum X in field list where X is derived from an expression
Submitted:	23 Mar 2020 13:36	Modified:	26 Mar 2020 13:42
Reporter:	Anthony Marston	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	Windows (Windows 10)
Assigned to:		CPU Architecture:	Any

Description:
I have a SELECT query which contains "(expression) AS alias" which is followed by a second expression which contains a reference to the alias from the first expression. For example, the first expression is:
(SELECT MAX(seq_no) FROM foo_status_hist WHERE foo_status_hist.foo_id=foo.foo_id) AS max_seq_no
The second expression is:
(SELECT status_desc FROM foo_status_hist WHERE foo_status_hist.foo_id=foo.foo_id AND foo_status_hist.seq_no=max_seq_no) AS max_status_desc
The third expression is:
(max_seq_no+1) AS seq_no_plus
While the second and third expressions used to work, for some reason the third one now fails.

How to repeat:
Here is a test script:
CREATE TABLE `foo` (
  `foo_id` INT(11) NOT NULL,
  `short_name` VARCHAR(40) NULL DEFAULT NULL,
  `long_description` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`foo_id`)
) ENGINE=INNODB;

CREATE TABLE `foo_status_hist` (
  `foo_id` INT NOT NULL,
  `seq_no` INT NOT NULL,
  `status_desc` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`foo_id`, `seq_no`)
) ENGINE=INNODB;

INSERT INTO `foo` VALUES(1, 'short1', 'long1');
INSERT INTO `foo` VALUES(2, 'short2', 'long2');
INSERT INTO `foo` VALUES(3, 'short3', 'long3');
INSERT INTO `foo` VALUES(4, 'short4', 'long4');

INSERT INTO `foo_status_hist` VALUES(1, 1, 'status1');
INSERT INTO `foo_status_hist` VALUES(1, 2, 'status2');
INSERT INTO `foo_status_hist` VALUES(2, 1, 'status1');
INSERT INTO `foo_status_hist` VALUES(2, 2, 'status2');
INSERT INTO `foo_status_hist` VALUES(3, 1, 'status1');
INSERT INTO `foo_status_hist` VALUES(3, 2, 'status2');
INSERT INTO `foo_status_hist` VALUES(4, 1, 'status1');
INSERT INTO `foo_status_hist` VALUES(4, 2, 'status2');

SELECT foo.*
, (SELECT MIN(seq_no) FROM foo_status_hist WHERE foo_status_hist.foo_id=foo.foo_id) AS min_seq_no
, (SELECT MAX(seq_no) FROM foo_status_hist WHERE foo_status_hist.foo_id=foo.foo_id) AS max_seq_no
, (SELECT status_desc FROM foo_status_hist WHERE foo_status_hist.foo_id=foo.foo_id AND foo_status_hist.seq_no=min_seq_no) AS min_status_desc
, (SELECT status_desc FROM foo_status_hist WHERE foo_status_hist.foo_id=foo.foo_id AND foo_status_hist.seq_no=max_seq_no) AS max_status_desc
, (max_seq_no+1) AS seq_no_plus
FROM foo

Suggested fix:
Allow the 3rd expression to work just like the 2nd expression.

Hi Mr. Marston,

Thank you for your bug report.

We need more info from you. You are writing about second and third expression. 

You have eight expressions and six SELECT list elements in your query.

So, which expression is actually a problem ???

I am talking about occurrences of "(expression) AS alias", and in that sample query I supplied there are 5;
1) the first ends in "AS min_seq_no"
2) the second ends in "AS max_seq_no"
3) the third ends in "AS min_status_desc"
4) the fourth ends in "AS max_status_desc"
5) the fifth ends in "AS seq_no_plus"

The 3rd, 4th and 5th contain references to the results of earlier expressions, but while the 3rd and 4th work as expected the 5th one fails with "Unknown column 'max_seq_no' in field list".

In my original post I should have referred to the 5h expression instead of the 3rd.

Hi Mr. Marston,

Thank you for clarifying your point.

However, this is not a bug.

This is concisely explained in our Reference Manual:

"
An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column.
"

Standard SQL prohibits usage of the column alias in all other cases (like in the WHERE clause), unless a nested query uses the outer reference, like in your query.

Not a bug.

I find this very strange as it used to work in earlier versions. It seems perfectly logical to me that once you have evaluated an expression to produce a result with an alias name then you should be able to refer to that expression's result by its name in other parts of the query without having to re-evaluate the expression all over again.

Saying that its use in allowed in some circumstances but not in others implies that there is a problem when you refer to an expression's result in those other circumstances, but what exactly is the problem?

Hi,

It could have worked in some previous version, but then it is possible that we had to adapt to the standard.

Still, in which version did it exactly work ???

Just as reference below Oracle DB gives the error on same column, just error message different:

Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> SELECT foo.*
  2  , (SELECT MIN(seq_no) FROM foo_status_hist WHERE foo_status_hist.foo_id=foo.foo_id) AS min_seq_no
  3  , (SELECT MAX(seq_no) FROM foo_status_hist WHERE foo_status_hist.foo_id=foo.foo_id) AS max_seq_no
  4  , (SELECT status_desc FROM foo_status_hist WHERE foo_status_hist.foo_id=foo.foo_id AND foo_status_hist.seq_no=min_seq_no) AS min_status_desc
  5  , (SELECT status_desc FROM foo_status_hist WHERE foo_status_hist.foo_id=foo.foo_id AND foo_status_hist.seq_no=max_seq_no) AS max_status_desc
  6  , (max_seq_no+1) AS seq_no_plus
  7  FROM foo;
, (max_seq_no+1) AS seq_no_plus
   *
ERROR at line 6:
ORA-00904: "MAX_SEQ_NO": invalid identifier

> in which version did it exactly work ???
I know that it worked in version 5, but I cannot remember ever using this particular query since I upgraded to version 8.

As you stated that this usage of an alias can be used in a subselect I changed the line from "(max_seq_no+1) AS seq_no_plus" to "(SELECT max_seq_no+1) AS seq_no_plus" and it worked.