alasql: Incorrect result when JOINing a subselect

Hi Team,

I found that joins are not working(not giving the desired result). Please follow below steps to recreate problem.

//Create table one
CREATE TABLE one (a INT, b INT);
INSERT INTO one VALUES(1,10),(2,20);

//Create Table two
CREATE TABLE two (a INT, c INT);
INSERT INTO two VALUES(1,100),(2,200);

Now when I run the following query

SELECT t0.`a` AS a, c -b  AS bc FROM (SELECT a,b FROM one) t0 JOIN (SELECT a,c FROM two) t1 ON t0.`a`=t1.`a`;

Result is:

-----------
#	a	c
1      1    100
2	2    200

But the expected result is(if u run in any other relational db)

#  a  bc
1  1  90
2  2  180

Thanks

About this issue

Original URL
State: open
Created 7 years ago
Comments: 15 (8 by maintainers)

Most upvoted comments

The problem is in joining a subselect.

Demonstration of the problem: http://jsfiddle.net/L11cnfbu/

For now you can get by if you put the result from your subselects into a (temporary) table (or in this example use the name two instead of the subselect) you can also put your subselect into a parameter (as shown in the jsfiddle code)

Any inputs from @agershun why a join on a subselect will reset the list of column names in use?

The right question here would be why the main query returns the subselect results. And bobviously this is because the join is not finished properly! This is relevant otherwise more confusion is brought instead as this is not related to columns… at this fisrt stage.

Once solved there is another issue (second stage): the subselect support in joins is incomplete (in fact, there is no code to resolve defColumns and it takes a default resolution which is wrong to get the right data from the scope).

Therefore, to resolve this issue = bug fix in joins + add missing code to support the use case.

pabloBelt on Feb 12, 2023

I actually got the JOIN working with a WITH clause!

WITH
foo AS (
  SELECT geo, SUM(num) AS num
  FROM table1
  WHERE
    type = "B"
  GROUP BY geo
),
bar AS (
  SELECT geo, SUM(num) AS num
  FROM table1
  WHERE
    type = "A"
  GROUP BY geo
)
SELECT
  foo.geo AS aggregation,
  foo.num / bar.num AS val
FROM foo
JOIN bar
ON foo.geo = bar.geo;

Thanks for your approach though, it can be very useful to reuse the temp tables.

The strange thing is that using the (unsupported) sub-SELECT there is no error given, just the result of the last sub-SELECT. Is that intended?

caesarsol on Mar 30, 2018