GROUP BY returns 1 invalid result if no rows meet condition

[silverhawk184]

If there is a query that has no results, but contains a GROUP BY, it returns one result regardless, with the data of "undefined"

console.log(alasql('SELECT Name FROM ? WHERE 1=0 GROUP BY Name',[[{Name:'test'}]]));
returns: [{Name: 'undefined'}] but should be []

[silverhawk184]

Another issue with GROUP BY is that any field in the result that is not in the group is 'undefined'. MySQL simply returns the data from the first retrieved row.

alasql('SELECT Type,Name FROM ? GROUP BY Type',[[{Type:'PT',Name:'Steve'},{Type:'PT',Name:'Dan'},{Type:'FT',Name:'John'}]]);

Returns

[{Name: undefined, Type: "PT"},{Name: undefined, Type: "FT"}]

Should be

[{Name: "Steve", Type: "PT"},{Name: "John", Type: "FT"}]

[agershun]

Thank you! For the second question you should:
a) add Name to the GROUP BY clause
b) Use FIRST() aggregator

var res = alasql('SELECT Type,Name FROM ? GROUP BY Type, Name',[...);

or

var res = alasql('SELECT Type,FIRST(Name) FROM ? GROUP BY Type',[...]);

For the first question - sorry, it still in progress...

[agershun]

Try this example in jsFiddle

[silverhawk184]

Yes, for first question, I am able to get away with this as I already always check for results:

var res=alasql('SELECT pkey,Name FROM ? WHERE 1=0 GROUP BY Name',[[{Name:'test'}]]));
if(res.length>0 && typeof res[0].pkey !== 'undefined'){} //use a field that is guaranteed to never be undefined

[silverhawk184]

Sorry, just a tweak on your example for others. (It works exactly as I would have expected it to from within mySQL)

var res = alasql('SELECT Type,FIRST(Name) FROM ? GROUP BY Type',[...]);

returns [[{Type:"foo","FIRST(Name)":"bar"}]]
Make sure you include the as statement.

var res = alasql('SELECT Type,FIRST(Name) AS Name FROM ? GROUP BY Type',[...]);

will return [[{Type:"foo",Name:"bar"}]]

Is there a way you can test if the fields are requested naked, to automatically apply the FROM() function when used in a GROUP BY? Maybe in an upcoming version?

[mathiasrw]

👍

[agershun]

Is there a way you can test if the fields are requested naked, to automatically apply the FROM() function when used in a GROUP BY?

I think, that the standard SQL reaction is to throw a error like "column is not found". I think, Alasql can check this, but only for SELECT with GROUP BY clause. Plus, it is hard to recognize these "naked" columns in expressions, like:

    SELECT mycol+1 FROM mytable GROUP BY othercol

[mathiasrw]

Is this issue resolved?

[dogma-systems]

There is news about this bug? Thanks in advance

[mathiasrw]

Well... its now returning [{}] instead of the original problem - but its still not correct.

Any help to resolve this would be awesome.

http://jsfiddle.net/t780pnz1/

[tran4o]

adding HAVING count(*) > 0 seems to fix this problem