Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-5843

Incorrect values of INFORMATION_SCHEMA.TABLES.TABLE_TYPE

    XMLWordPrintableJSON

    Details

      Description

      As far as I can see, the values available in Drill include:
      TABLE
      VIEW
      SYSTEM_TABLE

      In a draft of SQL standard SQL:2011 Part 11: Information and Definition Schemas (SQL/Schemata), the available values should be (source: SQL:201x (preliminary)):
      BASE TABLE
      VIEW
      GLOBAL TEMPORARY
      LOCAL TEMPORARY
      SYSTEM VERSIONED

      Some common databases, which have implemented INFORMATION_SCHEMA.TABLES view, show "BASE TABLE" instead of "TABLE" and use " " instead of "_" as delimiter in the values of TABLE_TYPE:
      MySQL 5.7
      PostgreSQL 9.1
      DB2 for i5/OS

      In conclusion, "TABLE" should be replaced by "BASE TABLE" and "SYSTEM_TABLE" by "SYSTEM TABLE" in INFORMATION_SCHEMA.TABLES.TABLE_TYPE.
      Besides, the words in other existing or future values of TABLE_TYPE should also be delimited by " " instead of "_", for example, "GLOBAL TEMPORARY" instead of "GLOBAL_TEMPORARY".

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              second88 second88
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: