ORDER BY with CASE doesn't work on UNION

[4skinSkywalker]

I'm trying this query

SELECT country, COUNT(*) AS competitors
FROM foreignCompetitors
GROUP BY country

UNION

SELECT "Total: " AS country, COUNT(*) AS competitors
FROM foreignCompetitors

ORDER BY 
    CASE
        WHEN country = "France" THEN 1
        WHEN country = "Germany" THEN 2
        WHEN country = "USA" THEN 3
        WHEN country = "UK" THEN 4
        ELSE 5
    END;

But the ORDER BY clause isn't doing anything, as seen here:

But if I remove the CASE and just put the ORDER BY country it works, as seen here:

I can confirm that the CASE normally works in the ORDER BY clause, here an example query:

SELECT country, COUNT(*) AS competitors
FROM foreignCompetitors
GROUP BY country
ORDER BY 
    CASE
        WHEN country = "France" THEN 1
        WHEN country = "Germany" THEN 2
        WHEN country = "USA" THEN 3
        WHEN country = "UK" THEN 4
        ELSE 5
    END;

Results:

[mathiasrw]

Does it work if you wrap the unions as a subquery?

SELECT *
FROM (
    SELECT country, COUNT(*) AS competitors
    FROM foreignCompetitors
    GROUP BY country

    UNION

    SELECT 'Total:' AS country, COUNT(*) AS competitors
    FROM foreignCompetitors
)
ORDER BY
    CASE
        WHEN country = 'France' THEN 1
        WHEN country = 'Germany' THEN 2
        WHEN country = 'USA' THEN 3
        WHEN country = 'UK' THEN 4
        WHEN country = 'Total:' THEN 5
        ELSE 6
    END;

[priyank-R]

Hi @mathiasrw - I was looking into this issue to see if I can contribute to it.

When running the query you suggested, I'm getting the following error:

dist\alasql.fs.js:7960
                        throw new Error('Data source number ' + h + ' in undefined');
                        ^

Error: Data source number 0 in undefined
    at doJoin (D:\alasql\alasql\dist\alasql.fs.js:7960:10)
    at queryfn3 (D:\alasql\alasql\dist\alasql.fs.js:7390:2)
    at queryfn2 (D:\alasql\alasql\dist\alasql.fs.js:7371:9)
    at Object.eval [as datafn] (eval at <anonymous> (D:\alasql\alasql\dist\alasql.fs.js:8919:20), <anonymous>:3:57)
    at D:\alasql\alasql\dist\alasql.fs.js:7324:19
    at Array.forEach (<anonymous>)
    at queryfn (D:\alasql\alasql\dist\alasql.fs.js:7322:16)
    at Query.statement [as unionfn] (D:\alasql\alasql\dist\alasql.fs.js:8458:15)
    at queryfn3 (D:\alasql\alasql\dist\alasql.fs.js:7500:15)
    at queryfn2 (D:\alasql\alasql\dist\alasql.fs.js:7371:9)

Same goes when running the unmodified query shared by @4skinSkywalker.

Here's the code I'm trying to run:

const alasql = require('alasql')


const data = [
    {
        country: 'France'
    },
    {
        country: 'UK'
    },
    {
        country: 'China'
    },
    {
        country: 'USA'
    },
    {
        country: 'Germany'
    },
]

const query = `
    SELECT *
    FROM (
        SELECT country, COUNT(*) AS competitors
        FROM ?
        GROUP BY country

        UNION

        SELECT 'Total:' AS country, COUNT(*) AS competitors
        FROM ?
    )
    ORDER BY
        CASE
            WHEN country = 'France' THEN 1
            WHEN country = 'Germany' THEN 2
            WHEN country = 'USA' THEN 3
            WHEN country = 'UK' THEN 4
            WHEN country = 'Total:' THEN 5
            ELSE 6
        END;
`

console.log(alasql(query, [data]))

[mathiasrw]

@priyank-R Thank you for chipping in.

Would you like to work on a pull request to fix this?

[priyank-R]

@mathiasrw Yes, I'd love to take this up.

[priyank-R]

@4skinSkywalker Could you share the version of the libary that you're using.
I'm unable to reproduce the problem because the Union clause itself is breaking for me, so I'm curious how are you able to get any form of output anyways.

Trying to see why union is breaking meanwhile.

[4skinSkywalker]

@4skinSkywalker Could you share the version of the libary that you're using. I'm unable to reproduce the problem because the Union clause itself is breaking for me, so I'm curious how are you able to get any form of output anyways.

Trying to see why union is breaking meanwhile.

AlaSQL v4.10.1

Use this to recreate the very same table I used:

[
    "DROP TABLE IF EXISTS foreignCompetitors;",
    "CREATE TABLE foreignCompetitors (competitor TEXT, country TEXT);",
    "INSERT INTO foreignCompetitors VALUES ('Acme Corp', 'USA');",
    "INSERT INTO foreignCompetitors VALUES ('GLOBEX', 'USA');",
    "INSERT INTO foreignCompetitors VALUES ('Openmedia', 'France');",
    "INSERT INTO foreignCompetitors VALUES ('K-bam', 'USA');",
    "INSERT INTO foreignCompetitors VALUES ('Hatdrill', 'UK');",
    "INSERT INTO foreignCompetitors VALUES ('Hexgreen', 'Germany');",
    "INSERT INTO foreignCompetitors VALUES ('D-ranron', 'France');",
    "INSERT INTO foreignCompetitors VALUES ('Faxla', 'Spain');"
],

[priyank-R]

@4skinSkywalker You're right, your query is giving the same result to me.
However, the one suggested by @mathiasrw seems to be honoring the CASE clause.

Does it work if you wrap the unions as a subquery?

SELECT *
FROM (
    SELECT country, COUNT(*) AS competitors
    FROM foreignCompetitors
    GROUP BY country

    UNION

    SELECT 'Total:' AS country, COUNT(*) AS competitors
    FROM foreignCompetitors
)
ORDER BY
    CASE
        WHEN country = 'France' THEN 1
        WHEN country = 'Germany' THEN 2
        WHEN country = 'USA' THEN 3
        WHEN country = 'UK' THEN 4
        WHEN country = 'Total:' THEN 5
        ELSE 6
    END;

[4skinSkywalker]

@4skinSkywalker You're right, your query is giving the same result to me. However, the one suggested by @mathiasrw seems to be honoring the CASE clause.

Does it work if you wrap the unions as a subquery?

SELECT *
FROM (
    SELECT country, COUNT(*) AS competitors
    FROM foreignCompetitors
    GROUP BY country

    UNION

    SELECT 'Total:' AS country, COUNT(*) AS competitors
    FROM foreignCompetitors
)
ORDER BY
    CASE
        WHEN country = 'France' THEN 1
        WHEN country = 'Germany' THEN 2
        WHEN country = 'USA' THEN 3
        WHEN country = 'UK' THEN 4
        WHEN country = 'Total:' THEN 5
        ELSE 6
    END;

I didn't test as I had already switched to PGLite soon after, but if it's working on your part then feel free to close it.

[priyank-R]

@mathiasrw We can close this issue since your suggestion worked

[mathiasrw]

@4skinSkywalker Thank you for helping Priyank out even after you transitioned to PGlite.