Details
Description
calculation of view column is wrong (by factor 1/10000). The column (3rd) is an arithmetic expression with an embedded select.
## create view v1 as select "error" , count(*) cnt , count(*) / (( select count(*) from t1 b where ## b.c3 = a.c3 ) / 100 ) "%" from t1 a where c1 < 5 group by c3 ;
|
## select * from v1 ;
|
## +-------+-----+------------+
|
## | error | 4 | 0.00666700 |
|
## +-------+-----+------------+
|
When executing the views underlying select directly, the correct value is returned.
## select "ok " , count(*) cnt , count(*) / (( select count(*) from t1 b where b.c3 = a.c3 ) / 100 ) ## "%" from t1 a where c1 < 5 group by c3 ;
|
## +-------+-----+---------+
|
## | ok | 4 | 66.6667 |
|
## +-------+-----+---------+
|
|
Attachments
Issue Links
- is duplicated by
-
MDEV-23656 view: removal of parentheses results in wrong result
-
- Closed
-
Thanks for the report! Reproduced as described on 10.2-10.5, problem in parenthesis
5.5-10.1 returns correct results
create table t1 (c1 int,c2 int,c3 int);
insert into t1 values (1,3,1),(2,1,1),(3,0,1),(4,0,1),(5,0,1),(6,0,1);
MariaDB [test]> SELECT count(*) cnt,
-> count(*)/((SELECT count(*) FROM t1 b WHERE b.c3 = a.c3)/100) "%"
-> FROM t1 a WHERE c1 < 5 GROUP BY c3;
+-----+---------+
| cnt | % |
+-----+---------+
| 4 | 66.6667 |
+-----+---------+
1 row in set (0.01 sec)
MariaDB [test]> create view v1 as
-> SELECT count(*) cnt,
-> count(*)/((SELECT count(*) FROM t1 b WHERE b.c3 = a.c3)/100) "%"
-> FROM t1 a WHERE c1 < 5 GROUP BY c3;
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> select * from v1 ;
+-----+------------+
| cnt | % |
+-----+------------+
| 4 | 0.00666700 |
+-----+------------+
1 row in set (0.00 sec)
MariaDB [test]> show create view v1;
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select count(0) AS `cnt`,count(0) / (select count(0) from `t1` `b` where `b`.`c3` = `a`.`c3`) / 100 AS `%` from `t1` `a` where `a`.`c1` < 5 group by `a`.`c3` | utf8 | utf8_general_ci |
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)