[MCOL-3482] IDB-9024 - is not in tuple - window function and subselects Created: 2019-09-05  Updated: 2023-03-06  Resolved: 2023-03-06

Status: Closed
Project: MariaDB ColumnStore
Component/s: N/A
Affects Version/s: 1.2.5
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: antoine Assignee: Unassigned
Resolution: Won't Do Votes: 1
Labels: None
Environment:

debian 9



 Description   

window functions such as sum() over() or avg() over() with a subselect throw 'is not in tuple' error.

only on columnstore tables, not on innodb or tokudb tables

drop table if exists t;
create table t (a decimal(7,2),b decimal(7,2)) engine=ColumnStore;
insert into t(a,b) values (1,1),(2,3),(3,4);
 
-- query 1, works fine
-- for each row, return the percentage that the sum of 2 columns a and b represents in the total of all rows
select
(a+b)/sum(a+b) over()
from t;
 
-- query 2, Error 1815, IDB-9024, 'b' is not in tuple
-- same as query1, just an extra subselect
select
(a+b)/sum(a+b) over()
from (
	select a,b from t
)t1;
 
-- query 3, works fine
-- same as query1, just sum(a) instead of sum(a+b)
select
a/sum(a) over()
from (
	select a,b from t
)t1;


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