Problem
CTE alias do not appear to work as part of a View definition. The view will be created without error, but selecting from the view results in
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'alias2 as (select 2 AS `two`), select `alias1`.`one` AS `one`,`alias2`.`two` AS ' at line 1
|
Reproduce
Select works as expected with CTE
with alias1 as (select 1 as one), alias2 as (select 2 as two) select one, two from alias1, alias2;
|
+-----+-----+
|
| one | two |
|
+-----+-----+
|
| 1 | 2 |
|
+-----+-----+
|
1 row in set (0.00 sec)
|
View get created without error with the same CTE
create view vw as with alias1 as (select 1 as one), alias2 as (select 2 as two) select one, two from alias1, alias2;
|
Query OK, 0 rows affected (0.01 sec)
|
Query fails against the view
select * from vw;
|
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'alias2 as (select 2 AS `two`), select `alias1`.`one` AS `one`,`alias2`.`two` AS ' at line 1
|
Solution
If this is a valid construct, then the query should execute as expected (per the un-view version).
If this is an invalid construct, then the create view should fail.
Workaround
None at present
|