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

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

            Dates

              Created:
              Updated: