Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL)
-
10.1.13
Description
Test case |
CREATE TABLE B ( i INT ); |
INSERT INTO B VALUES (1),(2); |
|
CREATE VIEW v1 AS |
SELECT 3 AS three, COUNT(*) FROM B GROUP BY three; |
|
SELECT * FROM v1; |
Result |
MariaDB [test]> SELECT * FROM v1; |
ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
Here is the reason (note the constant in GROUP BY):
MariaDB [test]> SHOW CREATE VIEW v1 \G |
*************************** 1. row ***************************
|
View: v1 |
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 3 AS `three`,count(0) AS `COUNT(*)` from `B` group by 3 |
character_set_client: utf8
|
collation_connection: utf8_general_ci
|
Reproducible on current 5.5.48, 10.0.24, 10.1.12.
Also reproducible on MySQL 5.5, but fixed in MySQL 5.6:
MySQL [test]> SELECT * FROM v1; |
+-------+----------+ |
| three | COUNT(*) | |
+-------+----------+ |
| 3 | 2 |
|
+-------+----------+ |
1 row in set (0.00 sec) |
|
MySQL [test]> SHOW CREATE VIEW v1 \G |
*************************** 1. row ***************************
|
View: v1 |
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 3 AS `three`,count(0) AS `COUNT(*)` from `B` group by `three` |
character_set_client: utf8
|
collation_connection: utf8_general_ci
|
1 row in set (0.00 sec) |