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

Add DESCRIBE SCHEMA/DATABASE/TABLE/query

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.8.0
    • Component/s: core
    • Labels:
      None

      Description

      Add DESCRIBE
      statement so it can support the following:

      DESCRIBE [SCHEMA | DATABASE] name;
      DESCRIBE table_name;
      

      Example, usage in MySql

        Issue Links

          Activity

          Hide
          julianhyde Julian Hyde added a comment -

          Resolved in release 1.8.0 (2016-06-13).

          Show
          julianhyde Julian Hyde added a comment - Resolved in release 1.8.0 (2016-06-13).
          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/a065200a with fix up in http://git-wip-us.apache.org/repos/asf/calcite/commit/da875a67 .
          Hide
          arina Arina Ielchiieva added a comment -

          Julian, I have closed my PR as it was outdated. I have implemented it before we agreed with you on final scope. Sorry for delay in implementing it.
          Anyway, I have reviewed your changes and they look good.

          Show
          arina Arina Ielchiieva added a comment - Julian, I have closed my PR as it was outdated. I have implemented it before we agreed with you on final scope. Sorry for delay in implementing it. Anyway, I have reviewed your changes and they look good.
          Hide
          julianhyde Julian Hyde added a comment -

          I have implemented 2, 3, 4, 5 (partially), 6, 7 in my branch https://github.com/julianhyde/calcite/tree/1168-describe. Arina Ielchiieva, Please review and if OK I will check in.

          I logged CALCITE-1221 to complete 1 and 5.

          Show
          julianhyde Julian Hyde added a comment - I have implemented 2, 3, 4, 5 (partially), 6, 7 in my branch https://github.com/julianhyde/calcite/tree/1168-describe . Arina Ielchiieva , Please review and if OK I will check in. I logged CALCITE-1221 to complete 1 and 5.
          Hide
          julianhyde Julian Hyde added a comment -

          Review comments:

          1. I see that DESCRIBE DATABASE currently does the same as DESCRIBE SCHEMA. That's OK, I suppose, but misleading.
          2. Since JDBC's DatabaseMetaData has database, catalog, schema, we should also have a DESCRIBE CATALOG command.
          3. I don't understand why you want to allow a character literal for a column, e.g. DESCRIBE emp 'deptno'. An identifier is sufficient, e.g. DESCRIBE emp "deptno". Remove the former.
          4. Need to allow the optional TABLE keyword, e.g. DESCRIBE TABLE emp as an alternative to DESCRIBE emp.
          5. Need to add DESCRIBE statement and DESCRIBE STATEMENT statement.
          6. There is no test for compound identifier
          7. We should not allow compound column name
          Show
          julianhyde Julian Hyde added a comment - Review comments: I see that DESCRIBE DATABASE currently does the same as DESCRIBE SCHEMA . That's OK, I suppose, but misleading. Since JDBC's DatabaseMetaData has database, catalog, schema, we should also have a DESCRIBE CATALOG command. I don't understand why you want to allow a character literal for a column, e.g. DESCRIBE emp 'deptno' . An identifier is sufficient, e.g. DESCRIBE emp "deptno" . Remove the former. Need to allow the optional TABLE keyword, e.g. DESCRIBE TABLE emp as an alternative to DESCRIBE emp . Need to add DESCRIBE statement and DESCRIBE STATEMENT statement . There is no test for compound identifier We should not allow compound column name
          Hide
          julianhyde Julian Hyde added a comment -

          Yes, and remember that name and table_name can be composite. E.g. DESCRIBE SCHEMA myDb.mySchema, DESCRIBE sales.emps, DESCRIBE myDb.mySchema.emps, DESCRIBE select * from t. And all names can be quoted the same way as identifiers in a query (double-quote by default).

          Show
          julianhyde Julian Hyde added a comment - Yes, and remember that name and table_name can be composite. E.g. DESCRIBE SCHEMA myDb.mySchema , DESCRIBE sales.emps , DESCRIBE myDb.mySchema.emps , DESCRIBE select * from t . And all names can be quoted the same way as identifiers in a query (double-quote by default).
          Hide
          arina Arina Ielchiieva added a comment -

          To confirm, you suggest the following syntax:
          1. DESCRIBE SCHEMA | DATABASE name
          2. DESCRIBE TABLE table_name [column_name | expression]
          3. DESCRIBE sql_statement (select * from t)
          4. DESCRIBE table_name [column_name | expression] (only if 3rd point wasn't met)
          Correct?

          Show
          arina Arina Ielchiieva added a comment - To confirm, you suggest the following syntax: 1. DESCRIBE SCHEMA | DATABASE name 2. DESCRIBE TABLE table_name [column_name | expression] 3. DESCRIBE sql_statement (select * from t) 4. DESCRIBE table_name [column_name | expression] (only if 3rd point wasn't met) Correct?
          Hide
          julianhyde Julian Hyde added a comment -

          Read the SQL standard at http://jtc1sc32.org/doc/N1951-2000/32N1964T-text_for_ballot-FCD_9075-2.pdf. Without the TABLE keyword we'd clash with DESCRIBE <SQL statement name>.

          Maybe TABLE could be optional, and without it, we'd look for a table only if the named statement did not exist. (And it wouldn't because at present we don't support named statements.)

          Show
          julianhyde Julian Hyde added a comment - Read the SQL standard at http://jtc1sc32.org/doc/N1951-2000/32N1964T-text_for_ballot-FCD_9075-2.pdf . Without the TABLE keyword we'd clash with DESCRIBE <SQL statement name> . Maybe TABLE could be optional, and without it, we'd look for a table only if the named statement did not exist. (And it wouldn't because at present we don't support named statements.)
          Hide
          arina Arina Ielchiieva added a comment -

          1. describe table statement usually doesn't use TABLE keyword:

          DESCRIBE table_name:
          

          2. What syntax is expected from DESCRIBE query?

          Show
          arina Arina Ielchiieva added a comment - 1. describe table statement usually doesn't use TABLE keyword: DESCRIBE table_name: 2. What syntax is expected from DESCRIBE query?
          Hide
          julianhyde Julian Hyde added a comment -

          I don't think we should use MySQL as a model. They are not very good at reading the SQL standard before they implement features.

          And in fact recent versions of the standard have "DESCRIBE query". I don't want to clash with that.

          I propose that we keep EXPLAIN as is, but add DESCRIBE SCHEMA name, DESCRIBE TABLE name, DESCRIBE query.

          Show
          julianhyde Julian Hyde added a comment - I don't think we should use MySQL as a model. They are not very good at reading the SQL standard before they implement features. And in fact recent versions of the standard have "DESCRIBE query". I don't want to clash with that. I propose that we keep EXPLAIN as is, but add DESCRIBE SCHEMA name, DESCRIBE TABLE name, DESCRIBE query.

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              arina Arina Ielchiieva
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development