UNION error when using same columns and add CORRESPONDING option

[mathiasrw]

When I UNION a table I expect that what I select from the second table to be unioned with the first table based on the order

CREATE TABLE t1(a int,b int); 
INSERT INTO t1 VALUES(1,2); 
SELECT a, b FROM t1 UNION  SELECT b, a FROM t1;

in http://alasql.org/console/ I would expect

#   a   b   
1   1   2
2   2   1

but I get

#   b   a
1   2   1

The issue can also be seen for statements like

SELECT 1, 2 UNION SELECT 3, 4;

[agershun]

I think that the problem in my wrong understanding of UNION operator.
I will redesign it in the way, like from your example:

    CREATE TABLE t1(a int,b int); 
    INSERT INTO t1 VALUES(1,2);  
    SELECT a, b FROM t1 UNION  SELECT b, a FROM t1;
    -- gives
    #   a   b   
    1   1   2
    2   2   1

[mathiasrw]

👍

[mathiasrw]

Is this on the road map?

[agershun]

... yes. I will do it ASAP. but not for 100% for current version, because Alasql does not keep order of columns in the record. So, it is impossible to predict results of this union operation:

    SELECT * FROM one UNION SELECT * FROM two

For Alasql2 it should be ok (I want to keep internal data in the format of array instead of object).

[agershun]

It is hard to fix this...

[mathiasrw]

is it because we do not track the original order?

[agershun]

I need to think how to convert merging arrays. Probably as a first step AlaSQL need to convert them to array of arrays, then merge, and then convert to array of objects back

[pietersv]

Whoa -- AlaSQL pays attention to the column names and matches columns when stacking tables vertically without worrying about order? For me that is an awesome capability, which stats packages like SAS and SPSS offer, so would love to keep it personally. But I believe that @mathiasrw is correct that SQL typically stacks only by column order and ignores the name, which is my only big complaint with SQL.

http://stackoverflow.com/questions/5201653/union-tables-by-field-name-i-e-columns-order-cannot-be-guaranteed

[agershun]

Hmm... I like this CORRESPONDING notation (thank you, Peter!).

"These forty years now, I've been speaking in prose without knowing it! "
(The Bourgeois Gentleman, Moliere)

At least we can keep for compatibility two versions of UNION/UNION ALL/INTERSECT/EXCEPT operators: one with keeping order and one - based on column names. Like:

    SELECT a,b FROM one UNION ALL SELECT b,a FROM one
    -- gives
    -- a b
    -- 1 2
    -- 2 1

and

    SELECT a,b FROM one UNION ALL CORRESPONDING  SELECT b,a FROM one
    -- gives
    -- a b
    -- 1 2
    -- 1 2

[mathiasrw]

Love the CORRESPONDING notation... 👍

[agershun]

I FIXED THIS!!! See test254.js. Now we can release 0.1!!!

[mathiasrw]

Yayyyyy :)

so so awesome :)