Issue with correlated queries
The following example should return [{a1:1}] but returns [].
alasql('create table t1 (a1 double precision, b1 double precision);')
alasql('create table t2 (a2 double precision, b2 double precision);')
alasql.tables.t1.data = [
{ "a1": 1.0, "b1": 1.0 }, { "a1": 1.0, "b1": 2.0 },
{ "a1": 2.0, "b1": 3.0 },
{ "a1": 3.0, "b1": 1.0 }, { "a1": 3.0, "b1": 2.0 }, { "a1": 3.0, "b1": 3.0 },
];
alasql.tables.t2.data = [
{ "a2": 7.0, "b2": 7.0 },
{ "a2": 7.0, "b2": 8.0 }
];
select a1 from t1 group by a1 having exists
(select a2 from t2 group by a2 having sum(1+0*b1) = 2);See: https://jsfiddle.net/Ltpxzgqh/1/
The semantics of t1 group by a1 is to split the table t1 into the intermediate tables [{a1:1, b1:1}, {a1:1, b1:2}], [{a1:2, b1:3}], and [{a1:3, b1:1}, {a1:3, b1:2}, {a1:3, b1:3}]. The expression select a2 from t2 group by a2 having sum(1+0*b1) = 2 is thus executed three times in three different contexts.
The expression t2 group by a2 always creates the table [{a2:7, b2:7}, {a2:7, b2:8}] and
the condition having sum(1+0*b1) = 2 tests if the number of occurrences of b1 is two on each intermediate tables created by the first part of the query. This condition is true only on [{a1:1, b1:1}, {a1:1, b1:2}]. So the expected final result is [{a1:1}].
Source: AlaSQL/alasql