[MDEV-31859] SHOW CREATE VIEW generates broken SQL when week() is used in View Created: 2023-08-06  Updated: 2023-09-10  Resolved: 2023-09-10

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.11
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Marcel Richter Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None
Environment:

Debian bookworm


 Description   

CREATE VIEW generates broken SQL on week() function

creating this Example View, to get the week of the current date works.

create view view_name as select (week(curdate())) ;

with SHOW CREATE for it
show create view view_name \G

generates this broken SQL, it adds an "," without any following value.
CREATE VIEW `view_name` AS select week(curdate(),)

 Comments   
Comment by Alice Sherepa [ 2023-08-07 ]

What version of MariaDB do you use? I tried to repeat on 10.11.4 and it works as expected:

MariaDB [test]> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.11.4-MariaDB |
 
+-----------------+
 
1 row in set (0,000 sec)
 
 
 
MariaDB [test]> create view view_name as select (week(curdate())) ;
 
Query OK, 0 rows affected (0,030 sec)
 
 
 
MariaDB [test]> show create view view_name;
 
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
 
| View      | Create View                                                                                                                                   | character_set_client | collation_connection |
 
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
 
| view_name | CREATE ALGORITHM=UNDEFINED DEFINER=`alice`@`localhost` SQL SECURITY DEFINER VIEW `view_name` AS select week(curdate()) AS `(week(curdate()))` | utf8mb3              | utf8mb3_general_ci   |
 
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
 
1 row in set (0,001 sec)
 
 
 
MariaDB [test]> CREATE ALGORITHM=UNDEFINED DEFINER=`alice`@`localhost` SQL SECURITY DEFINER VIEW `view_name1` AS select week(curdate()) AS `(week(curdate()))`;
 
Query OK, 0 rows affected (0,022 sec)

Generated at Thu Feb 08 10:27:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.