[MDEV-23859] view: arithmetic of column is wrong whereas underlying select works correctly Created: 2020-09-30  Updated: 2020-10-06  Resolved: 2020-10-04

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: 10.4.14, 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: mabto Assignee: Sergei Golubchik
Resolution: Duplicate Votes: 0
Labels: None
Environment:

opensuse tumbleweed x86_64 kernel 5.8.10-1-default


Attachments: File bug.sql    
Issue Links:
Duplicate
is duplicated by MDEV-23656 view: removal of parentheses results ... Closed

 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 |
## +-------+-----+---------+



 Comments   
Comment by Alice Sherepa [ 2020-10-01 ]

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)

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