Incorrect result when JOINing a subselect

[aravinds502]

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

[mathiasrw]

Thank you for a very well described bug.

We might need @agershun to give a hint to where the but could be located?

[mathiasrw]

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?

[aravinds502]

@mathiasrw Thank you for your very quick reply and work around suggested.

Could you please let us know when we can expect the support for joining the subselect. Just curious, as it is highly required for one of our current project.

[mathiasrw]

All code here is added when people feel like contributing. There is no timeline.

As soon as someone provides a PR with a fix I will release a new version.

Another alternative is to pay to get the fix implemented. Let me know if that has any interest to you.

[sasha-alias]

not sure, is this the part of the code related to the issue?
looks like some implementation started but commented out, most probably not finished.

[mathiasrw]

Hmmmmm. Interesting...

[besquared]

+1 just ran into this today.

[caesarsol]

Hi, a solution for this would be very appreciated!

In the meanwhile, could you describe what is the most straight-ahead workaround?

[besquared]

I haven't worked on this in a few month (I'll be getting back to it soon) but right now you have to materialize each of the sub-query result sets and pass them in using question marks in the final statement.

var sub = alasql("SELECT * FROM my_sub_table WHERE...");
var sub2 = alasql("SELECT * FROM my_sub_table2 WHERE...");
var final = alasql("SELECT * FROM my_table ? ON (...) JOIN ? ON (...) WHERE ...", [sub, sub2]);

This works and is pretty fast since it's all in memory and whatnot.

[caesarsol]

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?

[mathiasrw]

I actually got the JOIN working with a WITH clause!

thank you for a great input @caesarsol

[caesarsol]

Glad you found it useful!
Let me know if a documentation PR can be useful to include this info in the README, and where you think it might be inserted.

[mathiasrw]

That would be awesome!

[randomlysa]

is there any way to make this work using JS templates strings? I'm getting "cannot read property 'team' of undefined" - or is there a better way to do this?

    const x = alasql(
      `
      WITH
      homeScore AS (
        SELECT team, SUM(homeScore) as homePoints FROM boys WHERE homeAway = "H" GROUP BY team
      ),
      awayScore AS (
        SELECT team, SUM(awayScore) as awayPoints FROM boys WHERE homeAway = "A" GROUP BY team
      )
      SELECT *
      FROM boys
      JOIN homeScore
      ON awayScore.team = homeScore.team
      `
    );

edit: I was able to use this syntax

const x = alasql(
      `WITH
      homeScore AS (
        SELECT team, SUM(homeScore) as homePoints FROM boys WHERE homeAway = 'H' GROUP BY team
      ),
      awayScore AS (
        SELECT team, SUM(awayScore) as awayPoints FROM boys WHERE homeAway = 'A' GROUP BY team
      )
      SELECT *
      FROM homeScore
      JOIN  awayScore
      USING team
      `
    );

[pabloBelt]

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.