[MDEV-17137] Syntax errors with VIEW using MEDIAN Created: 2018-09-05  Updated: 2020-08-25  Resolved: 2018-10-16

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions, Views
Affects Version/s: 10.3.9, 10.3
Fix Version/s: 10.3.11

Type: Bug Priority: Major
Reporter: Hartmut Holzgraefe Assignee: Varun Gupta (Inactive)
Resolution: Fixed Votes: 0
Labels: 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



 Comments   
Comment by Alice Sherepa [ 2018-09-05 ]

Thanks! Reproducible with percentile_cont and median functions, wrong syntax of the query in .frm files 

TYPE=VIEW
 
query=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=d9316a01967571315e1d5aa12dc0f4f8
 
updatable=0
 
algorithm=0
 
definer_user=root
 
definer_host=localhost
 
suid=2
 
with_check_option=0
 
timestamp=2018-09-05 14:12:34
 
create-version=2
 
source=SELECT name, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY star_rating) \n  OVER (PARTITION BY name) AS pc \n  FROM book_rating
 
client_cs_name=utf8
 
connection_cl_name=utf8_general_ci
 
view_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

Comment by Varun Gupta (Inactive) [ 2018-09-12 ] 

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)

Comment by Varun Gupta (Inactive) [ 2018-09-12 ]

Patch
http://lists.askmonty.org/pipermail/commits/2018-September/012916.html

Comment by Sergei Petrunia [ 2018-10-11 ]

Review input provided over email

Generated at Thu Feb 08 08:34:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.