[MCOL-3435] Aggregate and Window functions can't co-exist Created: 2019-08-15 Updated: 2020-01-20 Resolved: 2020-01-20 |
|
| Status: | Closed |
| Project: | MariaDB ColumnStore |
| Component/s: | ExeMgr |
| Affects Version/s: | 1.1.6, 1.2.5 |
| Fix Version/s: | 1.4 |
| Type: | Task | Priority: | Major |
| Reporter: | David Hall (Inactive) | Assignee: | David Hall (Inactive) |
| Resolution: | Won't Fix | Votes: | 0 |
| Labels: | None | ||
| Sprint: | 2019-06, 2020-1 |
| Description |
|
A simple query containing both a window function and an aggregate will cause an eroneous syntax err: MariaDB [dhall]> select avg(comm_total) as wk_comm_avg, max(trade_date) over () as max_week from book_entry_cs1; The error is thrown by tupleaggregatestep.cpp prep2PhasesAggregate() (for this case) during setup. It checks for window functions, but the items inside the window function are erroneously marked as not in a group by. They don't need to be because they're in a window function. It uses jobInfo.windowSet to look for window functions. These are entered into the set by WindowFunctionStep::checkWindowFunction(). The parts of the window function are not added. Investigation needs to made on whether they should just be added to the windowSet, or should something else be done. These queries work in InnoDB. |
| Comments |
| Comment by David Hall (Inactive) [ 2019-09-12 ] | ||||||||||||||||||||||
|
The fix is to add the elements of the Window Function as implied group by with the caveat that an element that is a nested aggregate not be grouped. It appears that other DB's insist that they be in an explicit group by, but MariaDB does not. Both Postgres and Oracle were tested. | ||||||||||||||||||||||
| Comment by David Hall (Inactive) [ 2019-09-12 ] | ||||||||||||||||||||||
|
regressiontest PR#140 | ||||||||||||||||||||||
| Comment by Daniel Lee (Inactive) [ 2019-12-10 ] | ||||||||||||||||||||||
|
Build verified: 1.2.6-1 engine commit: I did the following test on both ColumnStore and InnoDB tables. 1) Create DBT3 orders table InnoDB and ColumnStore tables returned difference resultsets (see below). Sending the ticket back to further investigation. InnoDB return 1 row: MariaDB [tpch1i]> select avg(o_totalprice) from orders;
-------------------
------------------- ColumnStore table return 1500 rows MariaDB [tpch1m]> select avg(o_totalprice) as a, max(o_orderkey) over () as b from orders;
--------------
.
-------------- |