Why Does SELECT `*` Fail in MySQL? Uncovering a Hidden Bug Fixed in 5.7.31

1. Introduction

In MySQL, backticks are used to quote identifiers, while the asterisk * is a wildcard. Using backticks around * (select `*` ...) is invalid in standard MySQL because there is no column named *. select `*` from table; However, a customer's database accepted the statement, while a test instance rejected it, prompting investigation.

2. Testing and Verification

Extensive single‑variable testing showed the issue is unrelated to sql_mode or character set. The problem does not appear in higher MySQL versions, suggesting it was fixed in a later release. By binary searching between low and high versions, the reproducing version was identified.

create table t1(id int,name varchar(255));
insert into t1 value(1,'chenyuan'),(2,'liu');
select `*` from t1;  -- works on some versions
... (repeated successful queries) ...
select `*` from t1;  -- eventually returns ERROR 1054 (Unknown column '*')

3. Conclusion

The defect was fixed in MySQL 5.7.31. The bug stemmed from the parser treating a back‑ticked * the same as an unquoted *, expanding it to all columns, making it impossible to select a literal * column (Bug #30528450).

It is possible to define a column named * , but SELECT `*` was treated identically to SELECT *, making it impossible to select only this column; the asterisk was expanded to all columns.

Patch 5.7.31 (commit 5c8c085b…) and the corresponding 8.0 patch resolve the issue by removing the special handling of * in Item_field::itemize() and introducing Item_asterisk.

4. Final Thoughts

This article targets DBAs and support engineers who may not read C++ code, demonstrating a systematic, binary‑search‑based approach to pinpointing database bugs.

Tip: Use the dbdeployer tool to quickly spin up different MySQL versions for testing.