It appears that if one side of an arithmetic operator (or function like DIV) is an aggregate and the other is a Window Function, then somehow it gets the operator twice as a sub function of each side. When each is executed, the other side isn't available. Whichever gets done last is the final result.
ExeMgr spins up threads for each, so in the following query:
select count
/ sum(d1) over () from tb1 group by d1;
The count will get done in one thread and perform the divide of count with unknown in the denominator.
The sum(d1) will get done in another thread and perform the divide with unknown in the numerator and sum(d1) in the denominator.
On my machine, the second result is returned.
Somehow, the setup code is acting as if the '/' is inside of each function, rather than the root of the tree.
This does not happen if we do aggregate <op> aggregate. Only if one side is a Window Function.
The title of this MCOL says incorrect in a specific case. That is incorrect. It's a much more general case involving more than one cause.
After fixing the original compaint of agg/win, I thought to play around with more complex arithmetic formula involving window and aggregate functions and got a crash. Simplifying it down, it turns out something simple doesn't work:
MariaDB [dhall]> select sum(d1) over() / c1 from tb1;
ERROR 1815 (HY000): Internal error: IDB-9024: 'c1' is not in tuple.
Whereas aggregate:
MariaDB [dhall]> select sum(d1) / c1 from tb1 group by c1;
--------------------
| sum(d1) / c1 |
--------------------
| 0.2714285714285714 |
| 2.267515923566879 |
| 0.7087378640776698 |
--------------------
The c1 is not in tuple error implies that the setup logic doesn't realize that c1 has to be in the projection list. Figuring out just where to make a small change isn't always easy.
Interestingly.
MariaDB [dhall]> select c1,sum(d1) over() / c1 from tb1;
-------------------------+
| c1 | sum(d1) over() / c1 |
-------------------------+
| 3.14 | 4.095541401273885 |
| 5.15 | 2.4970873786407766 |
| 7.7 | 1.67012987012987 |
-------------------------+
This works because c1 is already in the projection list because we specifically asked for it.
However, fixing this little problem may or may not cause this to work:
MariaDB [dhall]> select c1*sum(d1) over() / count from tb1 group by c1, d1;
--------------------------------
| c1*sum(d1) over() / count |
--------------------------------
| 0.0000000000007071900395043137 |
| NULL |
| NULL |
--------------------------------
As you can see, this returns garbage.
Test case PR #121
For QA: The bug is manifest any time a Window Function is used with another function or arithmetic operator. See the regression tests in working_tpch1_compareLogOnly/windowFunctions/MCOL-3343.sql for examples. You can then try any combination of Window Functions, aggregates and simple columns to try to break it. Use any database for a ref if you want. Notice that MariaDB Server may allow some combinations without the proper GROUP BY clause, but CS does not allow such things.
Once this bug is closed, then MCOL-3338 should be tested and closed
Build verified: 1.2.5-1 nightly
erver commit:
f44f7d9
engine commit:
4e477ab
Verified test case in the ticket.