Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-6355

RelToSqlConverter[ORDER BY] generates an incorrect order by when NULLS LAST is used in non-projected field

    XMLWordPrintableJSON

Details

    Description

       

      We are using RelToSqlConverter, and seeing issues with it generating invalid queries when using DESC NULLS LAST, specifically.

       

      For example, this test query:

       

      select "product_id"
      from "product"
      where "net_weight" is not null
      group by "product_id"
      order by MAX("net_weight") desc 

      Gets resolved correctly, with a subquery, to:

       

      SELECT "product_id"
      FROM (SELECT "product_id", MAX("net_weight") AS "EXPR$1"
      FROM "foodmart"."product"
      WHERE "net_weight" IS NOT NULL
      GROUP BY "product_id"
      ORDER BY 2 DESC) AS "t3" 

       

       

      However, if I specify `desc nulls last`:

       

      select "product_id"
      from "product"
      where "net_weight" is not null
      group by "product_id"
      order by MAX("net_weight") desc nulls last 

      It creates an invalid query (order by 2, but only one field was projected):

       

       

      SELECT "product_id"
      FROM "foodmart"."product"
      WHERE "net_weight" IS NOT NULL
      GROUP BY "product_id"
      ORDER BY 2 DESC NULLS LAST 

       

       

       

      Trying to troubleshoot it, it appears that without the `NULLS LAST`, we have the following instance:

       

      SqlBasicCall -> SqlNumericLiteral 

       

       

      But when including it, it gets wrapped in another call:

       

      SqlBasicCall -> SqlBasicCall -> SqlNumericLiteral 

       

       

      So the hasSortByOrdinal method ends up returning false, which causes `needNewSubQuery` to incorrectly report false too.

       

      It appears that the best way to deal with this is by using a recursion to find numeric literals - but let me know if there are better ideas. 

       

      I plan to take a stab at this since I got enough context.

       

       

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              brunocvcunha Bruno Volpato
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: