Description:
creating a view with select default(col) clause, return an err: denied to user XX for column 'DEFAULT(col)'.
while actually the user can execute select deafult(col) query.
mysql> CREATE VIEW v0(c0, c1, c2, c3, c4) AS SELECT DEFAULT(t0.c0), t0.c0, t0.c0, CAST(t0.c0 AS UNSIGNED), t0.c0 FROM t0 WHE
ERE t0.c0;
ERROR 1143 (42000): create view command denied to user 'root'@'%' for column 'DEFAULT(t0.c0)' in table 'v0'
mysql> SELECT DEFAULT(t0.c0), t0.c0, t0.c0, CAST(t0.c0 AS UNSIGNED), t0.c0 FROM t0 WHERE t0.c0;
+----------------+--------------+--------------+-------------------------+--------------+
| DEFAULT(t0.c0) | c0 | c0 | CAST(t0.c0 AS UNSIGNED) | c0 |
+----------------+--------------+--------------+-------------------------+--------------+
| 001804080000 | 00000.322754 | 00000.322754 | 0 | 00000.322754 |
| 001804080000 | 00000.518663 | 00000.518663 | 1 | 00000.518663 |
| 001804080000 | 00000.228207 | 00000.228207 | 0 | 00000.228207 |
+----------------+--------------+--------------+-------------------------+--------------+
3 rows in set (0.00 sec)
How to repeat:
CREATE TABLE `t0` (
`c0` float unsigned zerofill NOT NULL DEFAULT '001804080000'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into t0 values (00000.322754),(00000.518663),(00000.228207);
mysql> CREATE VIEW v0(c0, c1, c2, c3, c4) AS SELECT DEFAULT(t0.c0), t0.c0, t0.c0, CAST(t0.c0 AS UNSIGNED), t0.c0 FROM t0 WHE
ERE t0.c0;
ERROR 1143 (42000): create view command denied to user 'root'@'%' for column 'DEFAULT(t0.c0)' in table 'v0'
mysql> SELECT DEFAULT(t0.c0), t0.c0, t0.c0, CAST(t0.c0 AS UNSIGNED), t0.c0 FROM t0 WHERE t0.c0;
+----------------+--------------+--------------+-------------------------+--------------+
| DEFAULT(t0.c0) | c0 | c0 | CAST(t0.c0 AS UNSIGNED) | c0 |
+----------------+--------------+--------------+-------------------------+--------------+
| 001804080000 | 00000.322754 | 00000.322754 | 0 | 00000.322754 |
| 001804080000 | 00000.518663 | 00000.518663 | 1 | 00000.518663 |
| 001804080000 | 00000.228207 | 00000.228207 | 0 | 00000.228207 |
+----------------+--------------+--------------+-------------------------+--------------+
3 rows in set (0.00 sec)
if we remove the DEFAULT PART while creating the view, it can success:
mysql> CREATE VIEW v1(c0, c1, c2, c3) AS SELECT t0.c0, t0.c0, CAST(t0.c0 AS UNSIGNED), t0.c0 FROM t0 WHERE t0.c0;
Query OK, 0 rows affected (0.00 sec)
Suggested fix:
expect view v0 can also be created.
