second88 created DRILL-5843:
-------------------------------
Summary: Incorrect values of INFORMATION_SCHEMA.TABLES.TABLE_TYPE
Key: DRILL-5843
URL: https://issues.apache.org/jira/browse/DRILL-5843
Project: Apache Drill
Issue Type: Bug
Components: Metadata
Reporter: second88
Priority: Minor
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)|https://wiki.postgresql.org/wiki/Developer_FAQ#Where_can_I_get_a_copy_of_the_SQL_standards.3F]):
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|https://dev.mysql.com/doc/refman/5.7/en/tables-table.html]
[PostgreSQL
9.1|https://www.postgresql.org/docs/9.1/static/infoschema-tables.html]
[DB2 for
i5/OS|https://www.ibm.com/support/knowledgecenter/ssw_i5_54/db2/rbafzmstcatalogans.htm#cattables]
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".
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
