[MDEV-23656] view: removal of parentheses results in wrong result Created: 2020-09-03  Updated: 2021-04-19  Resolved: 2020-10-23

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: 10.5.5, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.35, 10.3.26, 10.4.16, 10.5.7

Type: Bug Priority: Blocker
Reporter: Georg Richter Assignee: Sergei Golubchik
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Duplicate
duplicates MDEV-23859 view: arithmetic of column is wrong w... Closed
Relates
relates to MDEV-17406 Problems with LIKE using a parenthesi... Open
relates to MDEV-17408 VIEW is incorrectly defined for a com... Closed

 Description   

might be related to MDEV-13673:

Parentheses in view were optimized out, which ends up in wrong result:

 
 
mysql> create table t1 (a int, b decimal(10,2));
 
Query OK, 0 rows affected (0,02 sec)
 
 
 
mysql> insert into t1 values (1, 10.2);
 
Query OK, 1 row affected (0,01 sec)
 
 
 
mysql> create view v1 as select avg(b) / (2 + a) from t1;
 
Query OK, 0 rows affected (0,00 sec)
 
 
 
mysql> select * from v1;
 
+------------------+
 
| avg(b) / (2 + a) |
 
+------------------+
 
|     6.1000000000 |
 
+------------------+
 
1 row in set (0,00 sec)
 
 
 
mysql> select avg(b) / (2 + a) from t1;
 
+------------------+
 
| avg(b) / (2 + a) |
 
+------------------+
 
|     3.4000000000 |
 
+------------------+
 
1 row in set (0,00 sec)



 Comments   
Comment by Alice Sherepa [ 2020-09-03 ]

Correct results in 5.5-10.1, but in 10.2-10.5 view was created as:

 CREATE VIEW `v1` AS select avg(`t1`.`b`) / 2 + `t1`.`a` AS `avg(b) / (2 + a)` from `t1`

Comment by Georg Richter [ 2020-09-03 ]

See also Stackoverflow

Comment by Geoff Montee (Inactive) [ 2020-10-01 ]

The documentation team noticed that MySQL sometimes adds superfluous parentheses to view definitions.

For example, if you create this view:

CREATE OR REPLACE VIEW current_dept_emp AS
 
    SELECT l.emp_no, dept_no, l.from_date, l.to_date
 
    FROM dept_emp d
 
        INNER JOIN dept_emp_latest_date l
 
        ON d.emp_no=l.emp_no AND d.from_date=l.from_date AND l.to_date = d.to_date;

It becomes this on MariaDB 10.2:

 /*!50001 CREATE ALGORITHM=UNDEFINED */
 
 /*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER */
 
 /*!50001 VIEW `current_dept_emp` AS select `l`.`emp_no` AS `emp_no`,`d`.`dept_no` AS `dept_no`,`l`.`from_date` AS `from_date`,`l`.`to_date` AS `to_date` from (`dept_emp` `d` join `dept_emp_latest_date` `l` on(`d`.`emp_no` = `l`.`emp_no` and `d`.`from_date` = `l`.`from_date` and `l`.`to_date` = `d`.`to_date`)) */;

But it becomes this on MySQL 5.7:

 /*!50001 CREATE ALGORITHM=UNDEFINED */
 
 /*!50013 DEFINER=`root`@`%` SQL SECURITY DEFINER */
 
 /*!50001 VIEW `current_dept_emp` AS select `l`.`emp_no` AS `emp_no`,`d`.`dept_no` AS `dept_no`,`l`.`from_date` AS `from_date`,`l`.`to_date` AS `to_date` from (`dept_emp` `d` join `dept_emp_latest_date` `l` on(((`d`.`emp_no` = `l`.`emp_no`) and (`d`.`from_date` = `l`.`from_date`) and (`l`.`to_date` = `d`.`to_date`)))) */;

Maybe MySQL does this as a way to prevent bugs like this, but I'm not sure.

Comment by Sergei Golubchik [ 2020-10-03 ]

Yes, MySQL just adds parentheses whenever possible just in case. MariaDB did that too until 10.2. Since then it tries to produce more readable expressions by only adding parentheses where needed.

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