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
|
Attachments
Activity
The issue is percentile functions have a different syntax compared to normal window functions.
|
We have a print function for window functions which prints like
|
function_name ( parameters) over (partition_clause order_by_clause frame_clause )
|
With percentile function we have a different syntax like
|
function_name( parameters) within group (order_by_clause ) over (partition_by_clause)
|
Thanks! Reproducible with percentile_cont and median functions, wrong syntax of the query in .frm files
TYPE=VIEWquery=select `test`.`book_rating`.`name` AS `name`,percentile_cont(0.5) over ( partition by `test`.`book_rating`.`name` order by `test`.`book_rating`.`star_rating`) AS `pc` from `test`.`book_rating`md5=d9316a01967571315e1d5aa12dc0f4f8updatable=0algorithm=0definer_user=rootdefiner_host=localhostsuid=2with_check_option=0timestamp=2018-09-05 14:12:34create-version=2source=SELECT name, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY star_rating) \n OVER (PARTITION BY name) AS pc \n FROM book_ratingclient_cs_name=utf8connection_cl_name=utf8_general_ciview_body_utf8=select `test`.`book_rating`.`name` AS `name`,percentile_cont(0.5) over ( partition by `test`.`book_rating`.`name` order by `test`.`book_rating`.`star_rating`) AS `pc` from `test`.`book_rating`mariadb-version=100310