[MDEV-24194] View definition corruption Created: 2020-11-11  Updated: 2021-04-19  Resolved: 2020-12-12

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.3.25, 10.3.26, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.37, 10.3.28, 10.4.18, 10.5.9

Type: Bug Priority: Blocker
Reporter: azurit Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

Debian Buster 64bit

Issue Links:
Duplicate
duplicates MDEV-24226 10.3.26 rejects syntax that 10.3.25 a... Closed
is duplicated by MDEV-24254 CHECK (ISNULL(code)=ISNULL(class)) sy... Closed
is duplicated by MDEV-24641 Check constraint parser incorrectly i... Closed
is duplicated by MDEV-24817 Undocumented IS operator precendence ... Closed
PartOf

 Description   

Not sure in which version was this bug introduced (at least 10.3.25) but version 10.3.26 draw the attention on it.

Do this:

CREATE TABLE `test_table` (`ID` INTEGER(0)) ENGINE=MyISAM;
 
 
CREATE VIEW `test_view` AS SELECT * FROM `test_table` WHERE ISNULL(ID)=0;
 
 
 
SHOW CREATE VIEW test_view;

Result in 10.3.25 (see the changed/corrupted ISNULL() part):

+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
 
| View      | Create View                                                                                                                                                                           | character_set_client | collation_connection |
 
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
 
| test_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test_view` AS select `test_table`.`ID` AS `ID` from `test_table` where `test_table`.`ID` is null = 0 | latin1               | latin1_swedish_ci    |
 
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

Result in 10.3.26:

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 '= 0' at line 1



 Comments   
Comment by azurit [ 2020-11-11 ]

Bug was probably introduced in 10.3.25.

Comment by azurit [ 2020-11-11 ]

Just to add, after upgrade to affected version, also existing views becames corrupted.

Comment by azurit [ 2020-11-23 ]

This issue was marked as it duplicates MDEV-24226 but this isn't true - it duplicates MDEV-24226 only partially. MDEV-24226 is only about invalid syntax after 10.3.25 BUT this issue also indicates problems in 10.3.25 (views are corrupted from 10.3.25 including).

Comment by Sergei Golubchik [ 2020-11-23 ]

what do you means, views are corrupted? 10.3.25 appears to be working to me.

Comment by azurit [ 2020-11-23 ]

This doesn't seems to be correct syntax to me (or is it?):

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test_view` AS select `test_table`.`ID` AS `ID` from `test_table` where `test_table`.`ID` is null = 0

Comment by Sergei Golubchik [ 2020-11-23 ]

Yes, the syntax `test_table`.`ID` is null = 0 is fine. The bug was that 10.3.26 stopped recognizing it. Next version, 10.3.28, will have it fixed.

Comment by Stéphane Guiraud [ 2020-12-09 ]

If it can help someone, the impacted versions seems to be:
10.5.7 10.5.8
10.4.16 10.4.17
10.3.26 10.3.27
10.2.35 10.2.36

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