Description:
A CREATE VIEW whose HAVING clause contains a correlated scalar subquery is
accepted, but the view is unusable from the moment of creation, in three
observable ways that all trace back to one root cause:
1. **The stored view definition loses the subquery's select-list alias.**
`information_schema.views.VIEW_DEFINITION` for the view shows the
subquery's ORDER BY referencing a bare column name whose defining alias
(`VAR_SAMP(t_a.v) AS c_0`) was stripped during view normalization.
2. **The view cannot be queried.** `SELECT * FROM v6` immediately fails:
ERROR 1356 (HY000): View 'bug\_repro.v6' references invalid table(s) or
column(s) or function(s) or definer/invoker of view lack rights to use them
The dangling ORDER BY name cannot be re-resolved when the stored
definition is re-parsed.
3. **SHOW CREATE VIEW and mysqldump print a different, also-invalid
definition.** Printing resolves the dangling name by falling back to an
outer same-name column, producing
`order by `t\_a`.`c\_0` desc limit 1`. Replaying that output (or a full
mysqldump of the database) fails:
ERROR 1054 (42S22): Unknown column 'bug\_repro\_restore.t\_a.c\_0' in 'order clause'
so a mysqldump backup of any database containing such a view cannot be
restored.
The same dangling name therefore takes two different paths: re-parsing
fails (1054/1356), printing mis-binds and emits an apparently valid but
unrestorable definition.
Measured on MySQL Community Server 8.0.46 (Linux x86\_64). The original
statement is legal: in the subquery, ORDER BY `c_0` binds to the subquery's
own select-list alias, which MySQL accepts at CREATE time.
Trigger recipe (each element measured necessary): HAVING (not WHERE) +
correlated scalar subquery (aggregate referencing an outer column) +
select-list alias on that aggregate + ORDER BY referencing the alias +
LIMIT. Variants measured NOT affected: WHERE context (alias kept, ORDER BY
dropped entirely from the definition -- view works); non-correlated
subquery (ORDER BY dropped at creation -- view works); removing DESC
(changes nothing); outer table lacking the same-name column (definition
keeps a dangling bare name, SELECT still 1356, replay still 1054).
How to repeat:
CREATE DATABASE bug_repro;
USE bug_repro;
CREATE TABLE t_a (c_0 INT, v INT);
CREATE TABLE t_b (w INT);
-- Legal statement: ORDER BY `c_0` binds to the subquery's own select-list
-- alias (VAR_SAMP(t_a.v) AS c_0). Accepted without error.
CREATE VIEW v6 AS
SELECT t_a.v AS y FROM t_a
HAVING 1 = (SELECT VAR_SAMP(t_a.v) AS c_0 FROM t_b ORDER BY c_0 DESC LIMIT 1);
1. The view is unusable immediately:
```sql
SELECT * FROM v6;
```
```
ERROR 1356 (HY000): View 'bug_repro.v6' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
```
2. The stored definition lost the alias:
```sql
SELECT view_definition FROM information_schema.views WHERE table_name = 'v6';
```
```
select `bug_repro`.`t_a`.`v` AS `y` from `bug_repro`.`t_a` having (1 = (select var_samp(`bug_repro`.`t_a`.`v`) from `bug_repro`.`t_b` order by `c_0` desc limit 1))
```
3. SHOW CREATE VIEW prints a mis-bound definition:
```sql
SHOW CREATE VIEW v6;
```
```
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
VIEW `v6` AS select `t_a`.`v` AS `y` from `t_a` having (1 = (select
var_samp(`t_a`.`v`) from `t_b` order by `t_a`.`c_0` desc limit 1))
```
4. A full mysqldump of the database cannot be restored:
```
$ mysqldump --set-gtid-purged=OFF bug_repro > dump.sql
$ mysql -e 'CREATE DATABASE bug_repro_restore'
$ mysql bug_repro_restore < dump.sql
```
```
ERROR 1054 (42S22) at line 84: Unknown column 'bug_repro_restore.t_a.c_0' in 'order clause'
```
Suggested fix:
During view normalization, keep the subquery select-list alias that ORDER
BY references (i.e. do not strip `AS c_0` while leaving an ORDER BY Item\_ref
pointing at it), or substitute the aliased expression into the ORDER BY
clause. Alternatively, reject the CREATE VIEW at creation time if the
normalized definition does not round-trip; today the server accepts a
statement whose stored definition it cannot itself re-parse.
