Bug #118123 ORDER BY func(col_name) is bound to the wrong column

• Submitted: 7 May 8:37
• Modified: 12 May 11:22
• Reporter: Jingqi Tian (OCA)
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[7 May 8:37] Jingqi Tian

Description:
When there is an alias in the select list with the same name as the column name in base_table, ORDER BY func(col_name) is bound to the wrong column.

How to repeat:
1. Create table and insert data

create table t1 (id int primary key, col1 int, col2 int);
insert into t1 values (1, 2, 1), (2, 1, 2);

2. Execute SQL:

If we execute `SELECT id AS col1, col2 FROM t1 ORDER BY col1;`, we will get:

+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
|    2 |    2 |
+------+------+

However, if we execute `SELECT id AS col1, col2 FROM t1 ORDER BY col1 + 1;`, we will get:

+------+------+
| col1 | col2 |
+------+------+
|    2 |    2 |
|    1 |    1 |
+------+------+

In the first sql, the `col1` in order list is bound to t1.id. But in the second sql, the `col1` in order list is bound to t1.col1.

I think which column `col1` is bound to shouldn't be affected by whether it's in a function. So it should be a bug.

[12 May 11:22] MySQL Verification Team

Hi,

This behavior is not a bug. The possible outcomes of this scenario is to either break and return error that there is ambiguity or to pick up one of the possible columns. We apologize MySQL picked one you did not expect.

Kind regards.