[MCOL-335] Many Window Functions erroniously return 0 Created: 2016-09-27  Updated: 2016-10-06  Resolved: 2016-10-06

Status: Closed
Project: MariaDB ColumnStore
Component/s: PrimProc
Affects Version/s: 1.0.3
Fix Version/s: 1.0.4

Type: Bug Priority: Major
Reporter: David Hall (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS

Sprint: 2016-19

 Description   

Starting with 1.0.3, many of the Windowing Functions return 0 rather than the proper answer. Investigation implicates the the merge with server 10.1.15, though it is possible that another merge caused it.

Example (1.0.2)
MariaDB [tpch1]> select n_nationkey, avg(n_nationkey) over(order by n_nationkey range between unbounded preceding and 15 following) a from nation;

--------------------+

n_nationkey a

--------------------+

0 7.5000
1 8.0000
2 8.5000
3 9.0000
4 9.5000
5 10.0000
6 10.5000
7 11.0000
8 11.5000
9 12.0000
10 12.0000
11 12.0000
12 12.0000
13 12.0000
14 12.0000
15 12.0000
16 12.0000
17 12.0000
18 12.0000
19 12.0000
20 12.0000
21 12.0000
22 12.0000
23 12.0000
24 12.0000

--------------------+

example (1.0.3)
MariaDB [tpch1]> select n_nationkey, avg(n_nationkey) over(order by n_nationkey range between unbounded preceding and 15 following) a from nation;

-------------------+

n_nationkey a

-------------------+

0 0.0000
1 0.0000
2 0.0000
3 0.0000
4 0.0000
5 0.0000
6 0.0000
7 0.0000
8 0.0000
9 0.0000
10 0.0000
11 0.0000
12 0.0000
13 0.0000
14 0.0000
15 0.0000
16 0.0000
17 0.0000
18 0.0000
19 0.0000
20 0.0000
21 0.0000
22 0.0000
23 0.0000
24 0.0000

-------------------+

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2016-09-28 ]

Confirmed that this broke with the merge of 10.1.16 (the 10.1.15 merge is fine). There were some changes around create_tmp_field() in this version which did cause conflicts that I thought I resolved correctly. That would be my first target to look at.

Comment by Andrew Hutchings (Inactive) [ 2016-09-28 ]

So, the cause is as follows:

In 10.1.16 there was a consolidation of temporary field creation functions inside sql_select.cc. Previously when item_func created a temporary field it used result_type() in the switch to determine the type to use (which results in DECIMAL for the above query). The consolidated version uses cmp_type() instead (which results in DOUBLE for the above query).

For the fix I'm going to use a ternary operator to determine whether or not this is a window function and use the correct type accordingly.

Comment by Daniel Lee (Inactive) [ 2016-10-06 ]

Build verified: 1.0.4-1

mcsadmin> getsoft
getsoftwareinfo Tue Oct 4 22:29:41 2016

Name : mariadb-columnstore-platform
Version : 1.0.4
Release : 1
Architecture: x86_64
Install Date: Tue 04 Oct 2016 01:38:36 PM CDT
Group : Applications/Databases
Size : 11506458
License : Copyright (c) 2016 MariaDB Corporation Ab., all rights reserved; redistributable under the terms of the GPL, see the file COPYING for details.
Signature : (none)
Source RPM : mariadb-columnstore-platform-1.0.4-1.src.rpm
Build Date : Fri 30 Sep 2016 01:02:40 PM CDT

MariaDB [tpch1c]> select n_nationkey, avg(n_nationkey) over(order by n_nationkey range between unbounded preceding and 15 following) a from nation;
--------------------+

n_nationkey a

--------------------+

0 7.5000
1 8.0000
2 8.5000
3 9.0000
4 9.5000
5 10.0000
6 10.5000
7 11.0000
8 11.5000
9 12.0000
10 12.0000
11 12.0000
12 12.0000
13 12.0000
14 12.0000
15 12.0000
16 12.0000
17 12.0000
18 12.0000
19 12.0000
20 12.0000
21 12.0000
22 12.0000
23 12.0000
24 12.0000

--------------------+
25 rows in set (0.83 sec)

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