Alasql: UNION ignores ORDER BY

[tmrk]

I'm trying to use UNION ALL for selecting two different columns of sometimes the same rows in order to create a list of dates, see below a simplified version of what I'm trying to do:

alasql('SELECT [projectID], [Date1] FROM ? UNION ALL SELECT [projectID], [Date2] FROM ?',[projects])

As far as I know, this should work. However, no matter what I do, as long as UNION or UNION ALL is present in the code I get the following error:

alasql.js:6580 Uncaught Error: Data source number 0 in undefined
    doJoin                      @ alasql.js:6580
    queryfn3                    @ alasql.js:6086
    queryfn2                    @ alasql.js:6061
    (anonymous function)        @ VM3835:3
    (anonymous function)        @ alasql.js:6012
    queryfn                     @ alasql.js:6009
    tatement                    @ alasql.js:7031
    queryfn3                    @ alasql.js:6172
    queryfn2                    @ alasql.js:6061
    (anonymous function)        @ VM3833:3
    (anonymous function)        @ alasql.js:6012
    queryfn                     @ alasql.js:6009
    statement                   @ alasql.js:7031
    alasql.dexec                @ alasql.js:4304
    alasql.exec                 @ alasql.js:4255
    alasql                      @ alasql.js:128
    (anonymous function)        @ functions.js:1635     => this is where I have the above code
    (anonymous function)        @ alasql.js:7044
    queryfn3                    @ alasql.js:6330
    queryfn2                    @ alasql.js:6061
    (anonymous function)        @ alasql.js:15353
    xhr.onload                  @ alasql.js:3443

(I used the latest alasql.js 0.2.6 version.)

Am I doing something wrong, or is this a bug?

[mathiasrw]

Thank you for a really well described error.

Does @agershun have any inputs on this?

[tmrk]

Any news on this?

For now, I am using the following method to chain my results like this:

alasql('SELECT * FROM ? ORDER BY [deadline]',[
    alasql('SELECT [projectID], DATE([date1]) AS [deadline] FROM ?',[projects]).concat(
    alasql('SELECT [projectID], DATE([date2]) AS [deadline] FROM ?',[projects])).concat(
    alasql('SELECT [projectID], DATE([date3]) AS [deadline] FROM ?',[projects]))
]);

But I have a feeling that a functioning UNION would be faster (not to mention cleaner):

alasql(
    'SELECT [projectID], DATE([date1]) AS [deadline] FROM ? UNION ALL '+
    'SELECT [projectID], DATE([date2]) AS [deadline] FROM ? UNION ALL '+
    'SELECT [projectID], DATE([date3]) AS [deadline] FROM ? '+
    'ORDER BY [deadline]'
,[projects]);

... especially because it would require only one alasql call to the database.

Anyway, I just thought I'd share, maybe others might have the same problem for now.

[mathiasrw]

just saw your original sql

alasql('SELECT [projectID], [Date1] FROM ? UNION ALL SELECT [projectID], [Date2] FROM ?',[projects])

first ? will give the first index (projects) and second ? will give the second index - but there is no data in the second index.

Please try again with something like

alasql('SELECT [projectID], [Date1] FROM $0 UNION ALL SELECT [projectID], [Date2] FROM $0',[projects])

or

alasql('SELECT [projectID], [Date1] FROM $projects UNION ALL SELECT [projectID], [Date2] FROM $projects',{projects:projects})

Depending on what style you prefer...

[tmrk]

first ? will give the first index (projects) and second ? will give the second index - but there is no data in the second index.

You sir are absolutely right.

However, one issue still remains: ORDER BY does not seem to have an effect on the joined results. If I take my code from above:

alasql(
    'SELECT [projectID], DATE([date1]) AS [deadline] FROM $0 UNION ALL '+
    'SELECT [projectID], DATE([date2]) AS [deadline] FROM $0 UNION ALL '+
    'SELECT [projectID], DATE([date3]) AS [deadline] FROM $0 '+
    'ORDER BY [deadline]'
,[projects]);

The result is just like ORDER BY wasn't there. I tried adding ASC or DESC, no change.

Any ideas?

[tmrk]

I made a jsfiddle to demonstrate ORDER BY failing: https://jsfiddle.net/gf845cfp/

[mathiasrw]

As you have no WHERE statement the order by semanticly belongs to the second select of the union all.

I tried to put a (...) around, but the parser can not handle it.

So - the solution for you is to add WHERE 1 before your ORDER BY

https://jsfiddle.net/gf845cfp/1/

UPDATE: I need new glasses...

[tmrk]

So - the solution for you is to add WHERE 1 before your ORDER BY

https://jsfiddle.net/gf845cfp/1/

I'm sorry, but it still doesn't seem to work, or am I missing something?

The fact that it produces the exact same order if you use ASC or DESC, to me, shows that it's not working.

(To specify, I want it to order by price in the example, so the result should be in the order of: 100 200 300 400.)

[mathiasrw]

Omg. Im so sorry - I looked in the wrong collumn to verify it worked !

Ill mark it as a bug again

[mathiasrw]

Hi @tmrk

Had a look with fresh eyes. I dont know how I could miss this. The problem is that its sorting on an alias - and for some reason its not working for UNION

The solution is th have the union as a subquery - not both results have same sorting:

https://jsfiddle.net/hu0hzsrn/

[tmrk]

The solution is th have the union as a subquery

Thank you! I confirm that this is working.

Sorting on alias shouldn't be a problem otherwise, as it's valid SQL.

(See the same example here: http://sqlfiddle.com/#!9/3983f/1 - maybe an idea for future SQL compliance?)

Anyway, thanks Mathias!

[mathiasrw]

Glad I could help.

Sorting on alias shouldn't be a problem otherwise, as it's valid SQL.

True. I will make an "fresh" issue on the matter.

[mathiasrw]

Hmm. Looks like it works in when there is no sub or union

https://jsfiddle.net/0dozpgvq/

I will reopen this to make sure I remember to identify the problem.