AlaSQL/alasql

When using SELECT * all FUNC() are returned as a distinct columns even if they are defined "as `col_name`"

phantasma2983 opened this issue · 5 comments

phantasma2983 commented

If we give it this data and this query:

var data = [{a:1,b:1,c:1},{a:1,b:2,c:1},{a:1,b:3,c:1}, {a:2,b:1,c:1}];
var res = alasql('SELECT *, COUNT(a) AS d FROM ? GROUP BY a', [data] );

the result is:
[{
  a: 1,
  b: 1,
  c: 1,
  COUNT(a): 3, <--- ???
  d: 3
}, {
  a: 2,
  b: 1,
  c: 1,
  COUNT(a): 1, <--- ???
  d: 1
}]
adarshjhaa100 commented

Hi @mathiasrw would like to work on this if nobody's picked it up yet.

  • ❤️1
adarshjhaa100 commented

Hello @mathiasrw found the issue. There's a flaw in how the selectgfn method for Query is formed behind the scenes specifically for the current issue. When there's a select * in addition to there being column(s) with alias in the query, the selectgfn looks something like this:

(function anonymous(g,params,alasql) {
    var y;
    var r = {};
    for(var k in g) 
    {
        r[k]=g[k]
    };

    r['d']=(y=g['COUNT(a)'],y===y?y:undefined);
    delete r['COUNT(a)']
    return r
})

Let's consider the above example provided in the issue:

at the stage where gfn or selectgfn is called for the query in queryfn3,
image

the object for query.group[i] will be of the format : {a:"", b:"", c:"", 'COUNT(a)':"" } . Also, we have an alias for COUNT(a) which is d. Calling the function above with this data would result in an object of the form {a:"", b:"", c:"", 'COUNT(a)':"", d:"" } .
Clearly this signifies that Select * that should pull in all the columns of the data source, rather pulls in all columns of the current state of data which doesn't work in this case.

Based on my preliminary investigations, here are some of the possible solutions:

  1. Find out a way to identify whether a specific column is from the data source (can probably use the keys of of query.params[i] or source.data to identify this, or could use a flag during initialization of Query object for the same). This solution could have a significant impact and could potentially have some side effects. So, we need to be careful with this.

  2. After calling selectgfn, remove those entries from the data for which the key and value don't match in the query.groupColumns. Something like this:
    image

This gives the expected output:
image

The second solution doesn't seem to have any side affects when I ran the test cases.

mathiasrw commented

Very very nice work diving to the core of this issue. Would you be OK to make a pull request with your proposed fix? Have a look in https://github.com/AlaSQL/alasql/blob/develop/CONTRIBUTING.md if you have any doubts, or ping me here.

adarshjhaa100 commented

Hi @mathiasrw, have raised the PR with fix as well as test cases. Do let me know in case you see any issues.

  • 🎉1
mathiasrw commented

Released as part of v4.1.10

  • 👍1