[MCOL-3659] AVG_DISTINCT Window Function returns values with incorrect scale Created: 2019-12-10  Updated: 2019-12-12  Resolved: 2019-12-11

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: None
Fix Version/s: 1.4.2

Type: Bug Priority: Minor
Reporter: Gagan Goel (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Sprint: 2019-06

 Description   

The following example demonstrates the incorrect scale of the resulting values, which is reduced by a factor of 10^4:

MariaDB [tpch1]> SELECT AVG(distinct salary) OVER (PARTITION BY depname) from empsalary where empno < 10000 order by empno;
 
+--------------------------------------------------+
 
| AVG(distinct salary) OVER (PARTITION BY depname) |
 
+--------------------------------------------------+
 
|                                           0.4900 |
 
|                                           0.3700 |
 
|                                           0.4900 |
 
|                                           0.4900 |
 
|                                           0.3700 |
 
|                                           0.4975 |
 
|                                           0.4975 |
 
|                                           0.4975 |
 
|                                           0.4975 |
 
|                                           0.4975 |
 
|                                           0.4975 |
 
+--------------------------------------------------+
 
11 rows in set, 1 warning (2 min 16.044 sec)



 Comments   
Comment by Daniel Lee (Inactive) [ 2019-12-11 ]

Build verified: 1.4.2-1 source

Made build from latest Github source

server:
commit 0587394d2c5d8e6ac927ac15d1e55c40bc82b4fa
Author: Alexey Bychko <abychko@gmail.com>
Date: Wed Dec 4 15:49:00 2019 +0700

MENT-532 install cdb for windows containers on Azure

installed windbg with chocolatey, set PATH before MTR call

engine
commit 914fa570faa3091e4cb2a021c67859880468c606
Merge: 344c1d4 49994f7
Author: Roman Nozdrin <drrtuy@gmail.com>
Date: Tue Dec 10 19:26:20 2019 +0200

Merge pull request #970 from LinuxJedi/fix-warnings

Fix warnings found in DEBUG combined build

Reproduced the issue in 1.4.1-1

MariaDB [tpch1m]> select avg(l_extendedprice) from lineitem;
----------------------

avg(l_extendedprice)

----------------------

25441.198731

----------------------
1 row in set (0.015 sec)

select avg(distinct l_extendedprice) over (partition by l_orderkey) from lineitem where l_orderkey < 1000 order by l_orderkey;

.
.
.

0.013905
0.013905
0.013058
0.013058
0.013058
0.013058
0.013058
0.024134
0.024134
0.024134
0.024134
0.024134
0.024134

--------------------------------------------------------------
1004 rows in set (0.026 sec)

Verified fix in 1.4.2-1

select avg(distinct l_extendedprice) over (partition by l_orderkey) from lineitem where l_orderkey < 1000 order by l_orderkey;

.
.
.

13905.720000
13905.720000
13058.362000
13058.362000
13058.362000
13058.362000
13058.362000
24134.195000
24134.195000
24134.195000
24134.195000
24134.195000
24134.195000

--------------------------------------------------------------
1004 rows in set (0.082 sec)

Generated at Thu Feb 08 02:44:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.