Details
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
|
Attachments
Issue Links
- duplicates
-
MDEV-24226 10.3.26 rejects syntax that 10.3.25 accepts
-
- Closed
-
- is duplicated by
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
Just to add, after upgrade to affected version, also existing views becames corrupted.
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).
what do you means, views are corrupted? 10.3.25 appears to be working to me.
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
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.
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
Bug was probably introduced in 10.3.25.