The bug when 2 tables are joined

[Max-Makhrov]

The sample select:

SELECT \
data1.[0], data1.[1], data1.[2], data1.[3], data1.[4], data1.[5], data1.[6], data1.[7], data1.[8], data1.[9], data1.[10],\
data2.[0], data2.[1], data2.[2], data2.[3], data2.[4], data2.[5], data2.[6], data2.[7], data2.[8], data2.[9] \
FROM ? AS data1 JOIN ? AS data2 ON data1.[0] = data2.[0]", [data1, data2]);

This gives me the wrong results. Anonimous tables can't have the same alias. Here's another similar test in Sheets: select a.Col2, b.Col2 does not work.

I used this fix:

SELECT data1.[0] col000, data1.[1] col001, data1.[2] col002, data1.[3] col003, data1.[4] col004, data1.[5] col005, data1.[6] col006, data1.[7] col007, data1.[8] col008, data1.[9] col009, data1.[10] col010, data2.[0], data2.[1], data2.[2], data2.[3], data2.[4], data2.[5], data2.[6], data2.[7], data2.[8], data2.[9] FROM ? AS data1 JOIN ? AS data2 ON data1.[0] = data2.[0]
created artificial alias to the same column indexes from select.

It has to be an issue related to AlaSql, not this library, but since it has a simple fix, I thought it worth to post this issue here.

[oshliaer]

It's a feature of AlaSQL

• JOIN should return fields from both tables AlaSQL/alasql#547
• joinstar option not working AlaSQL/alasql#1004

[Max-Makhrov]

The sample code:

function example01() {
  var alasql = AlaSQLGS.load();
  alasql.options.joinstar = 'underscore';
  var data1 = SpreadsheetApp.openById(
    '14I34TM84uBnVILA3KQh9Y2LZr_Tr_fMsDQI2kYfht-E'
  )
    .getSheets()[0]
    .getDataRange()
    .getValues();
  var data2 = SpreadsheetApp.openById(
    '10uS4_YD4z2Ea-UnxkdydxB6QAKJ9GQUQX4Ozvw0F13o'
  )
    .getSheets()[0]
    .getDataRange()
    .getValues();

  var query =
    'SELECT MATRIX data1.*, data2.* \
     FROM ? AS data1 JOIN ? AS data2 ON data1.[0] = data2.[0]';

  var res = alasql(query, [data1, data2]);

  Logger.log(res);
}

overwrites the results from data1 with corresponding columns of data2.

The resulting row is
[1.0, a, Sunday, Tuesday, Saturday, Friday, Thursday, Monday, Saturday, Friday, 62.0]:

Expected resulting row:
[1.0, aSkRYRPO, 86.0, 9.0, 18.0, 28.0, 81.0, 23.0, 74.0, 72.0, 62.0, 1.0, a, Sunday, Tuesday, Saturday, Friday, Thursday, Monday, Saturday, Friday]

The suggested fix: alasql.options.joinstar = 'underscore' does not work. The right solution is to convert the query:
select a.*, b.* → select a.[0] a0, b.[0] b0

1. convert * to the list of columns
2. add fake aliases for each column.

[oshliaer]

@Max-Makhrov , unfortunately I'm only to learn how to AlsSQL AST works. It's not simply as I thought.

For an example you can write

select a.[0] as a0, b.[0] as b0

The next query is also valid

select a.[0] a0, b.[0] b0

Should we replace as before anything?

[Max-Makhrov]

Should we replace as before anything?

as is an optional keyword.

[oshliaer]

If this will implemented in the library, all options must be considered.

Also select a.[0] a0, b.[0] as b0