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

Check whether JDBC adapter generates "GROUP BY ()" against Oracle, DB2, MSSQL

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • None
    • None
    • None
    • None

    Description

      Oracle, DB2 and MSSQL have non-standard semantics for "GROUP BY ()". Standard behavior is to always return one "grand total" row, but Oracle, DB2 and MSSQL return no rows if the input is empty.

      Calcite's semantics is that "GROUP BY ()" always returns one row, and the JDBC adapter currently assumes that all back ends have the same semantics. On back ends that have different semantics, some queries might be giving incorrect results.

      I suggest the following remedy:

      • Add a SqlDialect┬ámethod boolean omitGrandTotalOnEmptyInput()
      • Run the test suite, and see whether we ever generate "GROUP BY ()" on one of the affected dialects. Try to write a test case where we do this.
      • Modify the dialects to generate safe SQL in these cases (possibly "GROUP BY ()", or possibly something else). As the above article notes, it is particularly difficult to find SQL that works for MSSQL, because it bumps into the no-constants rule (see CALCITE-4702)

      Attachments

        Issue Links

          Activity

            People

              nobigo xiong duan
              julianhyde Julian Hyde
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated: