AVG over text joins the strings: AVG of '12' and '3' gives 61.5 #2763

AVG over text adds the strings together instead of ignoring them, so it returns a wrong number. SUM ignores text. Seen while checking the sqllogictest run (evidence/slt_lang_aggfunc.test), reproduced on develop (06211a0) and 4.20.1:

alasql('SELECT SUM(y) AS s, AVG(y) AS a FROM ?', [[{y: '12'}, {y: '3'}]]);
// alasql: [{a: 61.5}]   ('12' + '3' joins to '123', divided by 2); SUM gives no value
// SQLite: s = 15, a = 7.5

alasql('SELECT SUM(y) AS s, AVG(y) AS a FROM ?', [[{y: '12'}, {y: 3}]]);
// alasql: [{s: 3, a: 61.5}]
// SQLite: s = 15, a = 7.5

Cause: the AVG aggregate in src/423groupby.js adds each value with _SUM += (y || 0), which joins strings, and counts every value that is not NULL. SUM checks for numbers first.

Fix (4.x)

AVG follows the rule of SUM: values that are not numbers are ignored in the sum and in the count. The examples above then give no value and a = 3. Nobody can rely on 61.5, so this is not breaking.

Not changed

• Text that is not a number gives no value in AVG and SUM, as today. In 5.0 that value becomes null with the rest of the NULL work (Return null, not undefined, for SQL NULL: what it means for the code #2588).
• Reading text as a number ('12' is 12, 'abc' is 0), as SQLite does, is for the sqlite dialect: AVG and SUM read text as a number in the sqlite dialect: '12' is 12, 'abc' is 0 #2774. Engines disagree here (PostgreSQL rejects SUM over text), so the alasql dialect keeps ignoring text.

Related: #1605, #2481.