Details
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)
|
Attachments
Issue Links
- duplicates
-
MDEV-23859 view: arithmetic of column is wrong whereas underlying select works correctly
-
- Closed
-
- relates to
-
-
- Open
-
-
-
- Closed
-
- links to
Activity
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.
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.
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`