Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.9, 10.3(EOL)
-
None
Description
How to reproduce, setup:
CREATE TABLE t1(val int);
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
|
CREATE VIEW v1 AS SELECT MEDIAN(val) OVER() FROM t1;
|
All of the above statements succeed without warnings.
Direct median query works:
MariaDB [test]> select median(val) OVER () FROM t1;
|
+---------------------+
|
| median(val) OVER () |
|
+---------------------+
|
| 2.0000000000 |
|
| 2.0000000000 |
|
| 2.0000000000 |
|
+---------------------+
|
3 rows in set (0.00 sec)
|
Doing this by using the view doesn't:
MariaDB [test]> SELECT * FROM v1;
|
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 'over ( order by `test`.`t1`.`val` desc) AS `MEDIAN(val) OVER()` from `test`.`t1`' at line 1
|
And even SHOW CREATE VIEW fails with the same syntax error:
MariaDB [test]> SHOW CREATE VIEW v1;
|
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 'over ( order by `test`.`t1`.`val` desc) AS `MEDIAN(val) OVER()` from `test`.`t1`' at line 1
|
Also the view shows up in SHOW TABLES output, but not in the INFORMATION_SCHEMA.VIEWS table:
MariaDB [test]> show tables;
|
+----------------+
|
| Tables_in_test |
|
+----------------+
|
| t1 |
|
| v1 |
|
+----------------+
|
|
|
MariaDB [test]> SELECT * FROM INFORMATION_SCHEMA.VIEWS;
|
Empty set (0.00 sec)
|
The views v1.frm file looks like this:
TYPE=VIEW
|
query=select percentile_cont(0.5) over ( order by `test`.`t1`.`val` desc) AS `MEDIAN(val) OVER()` from `test`.`t1`
|
md5=69b78fe9f2a70d478f26a26535b7a029
|
updatable=0
|
algorithm=0
|
definer_user=
|
definer_host=localhost
|
suid=2
|
with_check_option=0
|
timestamp=2018-09-05 11:11:08
|
create-version=2
|
source=SELECT MEDIAN(val) OVER() FROM t1
|
client_cs_name=utf8
|
connection_cl_name=utf8_general_ci
|
view_body_utf8=select percentile_cont(0.5) over ( order by `test`.`t1`.`val` desc) AS `MEDIAN(val) OVER()` from `test`.`t1`
|
mariadb-version=100309
|