[IGNITE-18173] SQL: implement EVERY and SOME aggregate functions

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 2.15
• Component/s: sql
• Labels: calcite ignite-3
• Release Note: SQL Calcite: Added EVERY/SOME aggregate functions
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Aleksey Plekhanov
• Reporter: Andrey Khitrin
• Created: 16/Nov/22 08:48
• Updated: 23/Feb/23 12:26
• Resolved: 18/Nov/22 12:58

Description

Aggregate functions EVERY and SOME are part of SQL standard. Unfortunately, they're not implemented in AI3 beta1 yet. Could you please implement them?

In AI2, they work in the following manner:

create table tmp_table_age_name_wage (key_field INT PRIMARY KEY,AGE INT,field1 VARCHAR,field2 INT);
insert into tmp_table_age_name_wage (key_field,AGE,field1,field2) values (1, 42,'John',100000);
insert into tmp_table_age_name_wage (key_field,AGE,field1,field2) values (2, 43,'Jack',50000);
insert into tmp_table_age_name_wage (key_field,AGE,field1,field2) values (3, 42,'Jen',30000);
insert into tmp_table_age_name_wage (key_field,AGE,field1,field2) values (4, 42,'Jim',70000);
insert into tmp_table_age_name_wage (key_field,AGE,field1,field2) values (5, 41,'Jess',30000);
insert into tmp_table_age_name_wage (key_field,AGE,field1,field2) values (6, 50,'Joe',40000);
insert into tmp_table_age_name_wage (key_field,AGE,field1,field2) values (7, 43,'Jeff',20000);
insert into tmp_table_age_name_wage (key_field,AGE,field1,field2) values (8, 32,'Joel',80000);
insert into tmp_table_age_name_wage (key_field,AGE,field1,field2) values (9, 33,'Joe',30000);
insert into tmp_table_age_name_wage (key_field,AGE,field1,field2) values (10, 41,'Jill',90000);
SELECT EVERY(AGE > 20) FROM tmp_table_age_name_wage;            --> true
SELECT EVERY(AGE > 40) FROM tmp_table_age_name_wage;            --> false
SELECT SOME(field2 = 90000) FROM tmp_table_age_name_wage;       --> true
SELECT SOME(field2 <> 90000) FROM tmp_table_age_name_wage;      --> true

Issue Links

• is part of: IGNITE-12248 Apache Calcite based query execution engine
• links to: GitHub Pull Request #10384