GridGain Community Edition

Expand all | Collapse all

UNION ALL does not work with ORDER BY

  • 1.  UNION ALL does not work with ORDER BY

     
    Posted 03-26-2020 02:26 PM
    SELECT a, b FROM "table1".table1 table1 ORDER BY a DESC
    UNION ALL
    SELECT a, b FROM "table1".table1 table1 ORDER BY a ASC

    The query above does not work. Individually, UNION ALL works and ORDER BY works, but does not work when together. Can some one please help?


    ------------------------------
    Rahul Gaba
    Software developer
    ------------------------------


  • 2.  RE: UNION ALL does not work with ORDER BY

    Posted 03-26-2020 05:03 PM
    I did some research and it looks like this syntax(ORDER BY for each part in UNION ALL) usually is not supported by databases.

    There are some workarounds that can be used in this case, for example:

    SELECT a, b, b, NULL FROM "table1".table1 table1
    UNION ALL
    SELECT a, b, NULL, b FROM "table1".table1 table1
    ORDER BY 
    3 DESC, 4 ASC

    But I'm not sure if this is a best option. What are you trying to achieve here? What is the use case? Maybe I could suggest a better solution.

    ------------------------------
    Evgenii Zhuravlev
    Team Lead
    GridGain Systems
    ------------------------------



  • 3.  RE: UNION ALL does not work with ORDER BY

     
    Posted 03-26-2020 06:17 PM

    Hi Evgenii,

     

    Well, we have several such SQLs which we run against our databases using multiple engines (Impala, Hive, etc) all of these support such queries.

     

    The use case is to have data available in Gridgain cache and fire same SQL queries on the cache against hitting Hive/Impala queries against our database.

    Before we onboard Gridgain fully, we tried to hit some queries and test if it works for our SQLs, we can't modify SQL implementations as we have many of them.

     

    Please try these queries on Hive, they do work. Please suggest if this can be supported in Gridgain.

     

     

    Thanks

    Rahul