drill-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From par...@apache.org
Subject [6/6] drill git commit: DRILL-2465: Fix multiple DatabaseMetaData.getColumns() bugs.
Date Fri, 27 Mar 2015 17:49:24 GMT
DRILL-2465: Fix multiple DatabaseMetaData.getColumns() bugs.

- Added test DatabaseMetaDataGetColumnsTest.
- (Renamed Drill2128GetColumnsBugsTest to
  Drill2128GetColumnsDataTypeNotTypeCodeIntBugsTest.)
- Fixed/implemented various columns:
  - Added COLUMN_SIZE (big CASE expression handling lots of cases).
  - Fixed DECIMAL_DIGITS.
  - Fixed NUM_PREC_RADIX.
  - ~Fixed REMARKS ('' -> NULL).
  - ~Fixed COLUMN_DEF ('' -> NULL).
  - Fixed CHARACTER_OCTET_LENGTH.
  - Fixed ORDINAL_POSITION.
  - Fixed SCOPE_CATALOG, SCOPE_SCHEMA, SCOPE_TABLE,
  - Fixed SOURCE_DATA_TYPE.
- Note:  INTERVAL types have only *interim* implementations.
  (INFORMATION_SCHEMA.COLUMNS fixes are needed for completion.)
- Added canonical data type name strings in DATA_TYPE -> TYPE_NAME CASE
  expression (for robustness for expected upcoming INFORMATION_SCHEMA.COLUMNS
  standard-compliance bug fixing).


Project: http://git-wip-us.apache.org/repos/asf/drill/repo
Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/20efb2fb
Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/20efb2fb
Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/20efb2fb

Branch: refs/heads/master
Commit: 20efb2fbc8176242c94505bcaab662586d66ba4d
Parents: 8796fd1
Author: dbarclay <dbarclay@maprtech.com>
Authored: Sat Mar 21 00:45:41 2015 -0700
Committer: Parth Chandra <pchandra@maprtech.com>
Committed: Fri Mar 27 10:19:43 2015 -0700

----------------------------------------------------------------------
 .../java/org/apache/drill/jdbc/MetaImpl.java    |  409 ++-
 .../jdbc/DatabaseMetaDataGetColumnsTest.java    | 2779 ++++++++++++++++++
 .../jdbc/test/Drill2128GetColumnsBugsTest.java  |  169 --
 ...etColumnsDataTypeNotTypeCodeIntBugsTest.java |  169 ++
 4 files changed, 3261 insertions(+), 265 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/drill/blob/20efb2fb/exec/jdbc/src/main/java/org/apache/drill/jdbc/MetaImpl.java
----------------------------------------------------------------------
diff --git a/exec/jdbc/src/main/java/org/apache/drill/jdbc/MetaImpl.java b/exec/jdbc/src/main/java/org/apache/drill/jdbc/MetaImpl.java
index 99e0d22..4ff626e 100644
--- a/exec/jdbc/src/main/java/org/apache/drill/jdbc/MetaImpl.java
+++ b/exec/jdbc/src/main/java/org/apache/drill/jdbc/MetaImpl.java
@@ -34,10 +34,51 @@ import org.apache.drill.common.util.DrillStringUtils;
 
 
 public class MetaImpl implements Meta {
+  private static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(MetaImpl.class);
+
+  // TODO:  Use more central version of these constants if availabe.
+
+  /** Radix used to report precision and scale of integral exact numeric types. */
+  private static final int RADIX_INTEGRAL = 10;
+  /** Radix used to report precision and scale of non-integral exact numeric
+      types (DECIMAL). */
+  private static final int RADIX_DECIMAL = 10;
+  /** Radix used to report precision and scale of approximate numeric types
+      (FLOAT, etc.). */
+  private static final int RADIX_APPROXIMATE = 10;
+  /** Radix used to report precisions of interval types. */
+  private static final int RADIX_INTERVAL = 10;
+
+  /** (Maximum) precision of TINYINT. */
+  private static final int PREC_TINYINT  = 3;
+  /** (Maximum) precision of SMALLINT. */
+  private static final int PREC_SMALLINT = 5;
+  /** (Maximum) precision of INTEGER. */
+  private static final int PREC_INTEGER  = 10;
+  /** (Maximum) precision of BIGINT. */
+  private static final int PREC_BIGINT   = 19;
+
+  /** Precision of FLOAT. */
+  private static final int PREC_FLOAT  =  7;
+  /** Precision of DOUBLE. */
+  private static final int PREC_DOUBLE = 15;
+  /** Precision of REAL. */
+  private static final int PREC_REAL   = PREC_DOUBLE;
+
+  /** Scale of INTEGER types. */
+  private static final int SCALE_INTEGRAL = 0;
+  /** JDBC conventional(?) scale value for FLOAT. */
+  private static final int SCALE_FLOAT = 7;
+  /** JDBC conventional(?) scale value for DOUBLE. */
+  private static final int SCALE_DOUBLE = 15;
+  /** JDBC conventional(?) scale value for REAL. */
+  private static final int SCALE_REAL = SCALE_DOUBLE;
+
+  /** (Apparent) maximum precision for starting unit of INTERVAL type. */
+  private static final int PREC_INTERVAL_LEAD_MAX = 10;
+  /** (Apparent) maximum fractional seconds precision for INTERVAL type. */
+  private static final int PREC_INTERVAL_TRAIL_MAX = 9;
 
-  static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(MetaImpl.class);
-
-  static final Driver DRIVER = new Driver();
 
   final DrillConnectionImpl connection;
 
@@ -126,145 +167,323 @@ public class MetaImpl implements Meta {
     return s(sb.toString());
   }
 
-  public ResultSet getColumns(String catalog, Pat schemaPattern, Pat tableNamePattern, Pat
columnNamePattern) {
+  /**
+   * Implements @link DatabaseMetaData#getColumns()}.
+   */
+  public ResultSet getColumns(String catalog, Pat schemaPattern,
+                              Pat tableNamePattern, Pat columnNamePattern) {
     StringBuilder sb = new StringBuilder();
-    // TODO:  Fix the various remaining bugs and resolve the various questions
-    // noted below.
+    // TODO:  Resolve the various questions noted below.
     sb.append(
-        "SELECT \n"
-        // getColumns INFORMATION_SCHEMA.COLUMNS   getColumns()
-        // column     source column or             column name
-        // number     expression
-        // -------    ------------------------     -------------
-        + /*  1 */ "  TABLE_CATALOG            as  TABLE_CAT, \n"
-        + /*  2 */ "  TABLE_SCHEMA             as  TABLE_SCHEM, \n"
-        + /*  3 */ "  TABLE_NAME               as  TABLE_NAME, \n"
-        + /*  4 */ "  COLUMN_NAME              as  COLUMN_NAME, \n"
-
+        "SELECT "
+        // getColumns   INFORMATION_SCHEMA.COLUMNS        getColumns()
+        // column       source column or                  column name
+        // number       expression
+        // -------      ------------------------          -------------
+        + /*  1 */ "\n  TABLE_CATALOG                 as  TABLE_CAT, "
+        + /*  2 */ "\n  TABLE_SCHEMA                  as  TABLE_SCHEM, "
+        + /*  3 */ "\n  TABLE_NAME                    as  TABLE_NAME, "
+        + /*  4 */ "\n  COLUMN_NAME                   as  COLUMN_NAME, "
+
+        /*    5                                           DATA_TYPE */
         // TODO:  Resolve the various questions noted below for DATA_TYPE.
-        /*    5  (DATA_TYPE) */
-        + "  CASE \n"
+        + "\n  CASE DATA_TYPE "
         // (All values in JDBC 4.0/Java 7 java.sql.Types except for types.NULL:)
 
-        // TODO:  RESOLVE:  How does ARRAY appear in COLUMNS.DATA_TYPE?
-        // - Only at end (with no maximum size, as "VARCHAR(65535) ARRAY")?
-        // - Possibly with maximum size (as "... ARRAY[10]")?
-        // (SQL source syntax:
-        //   <array type> ::=
-        //     <data type> ARRAY
-        //       [ <left bracket or trigraph> <maximum cardinality> <right
bracket or trigraph> ]
-        + "    WHEN DATA_TYPE LIKE '% ARRAY'    THEN " + Types.ARRAY
-
-        + "    WHEN DATA_TYPE = 'BIGINT'        THEN " + Types.BIGINT
-        + "    WHEN DATA_TYPE = 'BINARY'        THEN " + Types.BINARY
+        // Exact-match cases:
+        + "\n    WHEN 'BIGINT'                      THEN " + Types.BIGINT
+        + "\n    WHEN 'BINARY'                      THEN " + Types.BINARY
         // Resolve:  Not seen in Drill yet.  Can it appear?:
-        + "    WHEN DATA_TYPE = 'BIT'           THEN " + Types.BIT
+        + "\n    WHEN 'BIT'                         THEN " + Types.BIT
         // Resolve:  Not seen in Drill yet.  Can it appear?:
-        + "    WHEN DATA_TYPE = 'BLOB'          THEN " + Types.BLOB
-        + "    WHEN DATA_TYPE = 'BOOLEAN'       THEN " + Types.BOOLEAN
+        + "\n    WHEN 'BLOB', 'BINARY LARGE OBJECT' THEN " + Types.BLOB
+        + "\n    WHEN 'BOOLEAN'                     THEN " + Types.BOOLEAN
 
-        + "    WHEN DATA_TYPE = 'CHAR'          THEN " + Types.CHAR
+        + "\n    WHEN 'CHAR', 'CHARACTER'           THEN " + Types.CHAR
         // Resolve:  Not seen in Drill yet.  Can it appear?:
-        + "    WHEN DATA_TYPE = 'CLOB'          THEN " + Types.CLOB
+        + "\n    WHEN 'CLOB', 'CHARACTER LARGE OBJECT' "
+        + "\n                                       THEN " + Types.CLOB
 
         // Resolve:  Not seen in Drill yet.  Can it appear?:
-        + "    WHEN DATA_TYPE = 'DATALINK'      THEN " + Types.DATALINK
-        + "    WHEN DATA_TYPE = 'DATE'          THEN " + Types.DATE
-        + "    WHEN DATA_TYPE = 'DECIMAL'       THEN " + Types.DECIMAL
+        + "\n    WHEN 'DATALINK'                    THEN " + Types.DATALINK
+        + "\n    WHEN 'DATE'                        THEN " + Types.DATE
+        + "\n    WHEN 'DECIMAL'                     THEN " + Types.DECIMAL
         // Resolve:  Not seen in Drill yet.  Can it appear?:
-        + "    WHEN DATA_TYPE = 'DISTINCT'      THEN " + Types.DISTINCT
-        + "    WHEN DATA_TYPE = 'DOUBLE'        THEN " + Types.DOUBLE
+        + "\n    WHEN 'DISTINCT'                    THEN " + Types.DISTINCT
+        + "\n    WHEN 'DOUBLE', 'DOUBLE PRECISION'  THEN " + Types.DOUBLE
+
+        + "\n    WHEN 'FLOAT'                       THEN " + Types.FLOAT
 
-        + "    WHEN DATA_TYPE = 'FLOAT'         THEN " + Types.FLOAT
+        + "\n    WHEN 'INTEGER'                     THEN " + Types.INTEGER
 
-        + "    WHEN DATA_TYPE = 'INTEGER'       THEN " + Types.INTEGER
+        // Drill's INFORMATION_SCHEMA's COLUMNS currently has
+        // "INTERVAL_YEAR_MONTH" and "INTERVAL_DAY_TIME" instead of SQL standard
+        // 'INTERVAL'.
+        + "\n    WHEN 'INTERVAL', "
+        + "\n         'INTERVAL_YEAR_MONTH', "
+        + "\n         'INTERVAL_DAY_TIME'           THEN " + Types.OTHER
 
         // Resolve:  Not seen in Drill yet.  Can it ever appear?:
-        + "    WHEN DATA_TYPE = 'JAVA_OBJECT'   THEN " + Types.JAVA_OBJECT
+        + "\n    WHEN 'JAVA_OBJECT'                 THEN " + Types.JAVA_OBJECT
 
         // Resolve:  Not seen in Drill yet.  Can it appear?:
-        + "    WHEN DATA_TYPE = 'LONGNVARCHAR'  THEN " + Types.LONGNVARCHAR
+        + "\n    WHEN 'LONGNVARCHAR'                THEN " + Types.LONGNVARCHAR
         // Resolve:  Not seen in Drill yet.  Can it appear?:
-        + "    WHEN DATA_TYPE = 'LONGVARBINARY' THEN " + Types.LONGVARBINARY
+        + "\n    WHEN 'LONGVARBINARY'               THEN " + Types.LONGVARBINARY
         // Resolve:  Not seen in Drill yet.  Can it appear?:
-        + "    WHEN DATA_TYPE = 'LONGVARCHAR'   THEN " + Types.LONGVARCHAR
+        + "\n    WHEN 'LONGVARCHAR'                 THEN " + Types.LONGVARCHAR
 
         // Resolve:  Not seen in Drill yet.  Can it appear?:
-        + "    WHEN DATA_TYPE = 'NCHAR'         THEN " + Types.NCHAR
+        + "\n    WHEN 'NCHAR', 'NATIONAL CHARACTER' THEN " + Types.NCHAR
         // Resolve:  Not seen in Drill yet.  Can it appear?:
-        + "    WHEN DATA_TYPE = 'NCLOB'         THEN " + Types.NCLOB
+        + "\n    WHEN 'NCLOB', 'NATIONAL CHARACTER LARGE OBJECT' "
+        + "\n                                       THEN " + Types.NCLOB
         // TODO:  Resolve following about NULL (and then update comment and code):
         // It is not clear whether Types.NULL can represent a type (perhaps the
         // type of the literal NULL when no further type information is known?) or
         // whether 'NULL' can appear in INFORMATION_SCHEMA.COLUMNS.DATA_TYPE.
         // For now, since it shouldn't hurt, include 'NULL'/Types.NULL in mapping.
-        + "    WHEN DATA_TYPE = 'NULL'          THEN " + Types.NULL
+        + "\n    WHEN 'NULL'                        THEN " + Types.NULL
         // (No NUMERIC--Drill seems to map any to DECIMAL currently.)
-        + "    WHEN DATA_TYPE = 'NUMERIC'       THEN " + Types.NUMERIC
+        + "\n    WHEN 'NUMERIC'                     THEN " + Types.NUMERIC
         // Resolve:  Not seen in Drill yet.  Can it appear?:
-        + "    WHEN DATA_TYPE = 'NVARCHAR'      THEN " + Types.NVARCHAR
+        + "\n    WHEN 'NVARCHAR', 'NATIONAL CHARACTER VARYING' "
+        + "\n                                       THEN " + Types.NVARCHAR
 
         // Resolve:  Unexpectedly, has appeared in Drill.  Should it?
-        + "    WHEN DATA_TYPE = 'OTHER'         THEN " + Types.OTHER
+        + "\n    WHEN 'OTHER'                       THEN " + Types.OTHER
 
-        + "    WHEN DATA_TYPE = 'REAL'          THEN " + Types.REAL
+        + "\n    WHEN 'REAL'                        THEN " + Types.REAL
         // SQL source syntax:
         //   <reference type> ::=
         //     REF <left paren> <referenced type> <right paren> [ <scope
clause> ]
         // Resolve:  Not seen in Drill yet.  Can it appear?:
-        + "    WHEN DATA_TYPE = 'REF'           THEN " + Types.REF
+        + "\n    WHEN 'REF'                         THEN " + Types.REF
         // Resolve:  Not seen in Drill yet.  Can it appear?:
-        + "    WHEN DATA_TYPE = 'ROWID'         THEN " + Types.ROWID
+        + "\n    WHEN 'ROWID'                       THEN " + Types.ROWID
 
-        + "    WHEN DATA_TYPE = 'SMALLINT'      THEN " + Types.SMALLINT
+        + "\n    WHEN 'SMALLINT'                    THEN " + Types.SMALLINT
         // Resolve:  Not seen in Drill yet.  Can it appear?:
-        + "    WHEN DATA_TYPE = 'SQLXML'        THEN " + Types.SQLXML
+        + "\n    WHEN 'SQLXML'                      THEN " + Types.SQLXML
 
-        // TODO:  RESOLVE:  How does "STRUCT" appear?
-        // - Only at beginning (as "STRUCT(INTEGER sint, BOOLEAN sboolean")?
-        // - Otherwise too?
-        + "    WHEN DATA_TYPE LIKE 'STRUCT(%'   THEN " + Types.STRUCT
+        + "\n    WHEN 'TIME'                        THEN " + Types.TIME
+        + "\n    WHEN 'TIMESTAMP'                   THEN " + Types.TIMESTAMP
+        + "\n    WHEN 'TINYINT'                     THEN " + Types.TINYINT
 
-        + "    WHEN DATA_TYPE = 'TIME'          THEN " + Types.TIME
-        + "    WHEN DATA_TYPE = 'TIMESTAMP'     THEN " + Types.TIMESTAMP
-        + "    WHEN DATA_TYPE = 'TINYINT'       THEN " + Types.TINYINT
+        + "\n    WHEN 'VARBINARY', 'BINARY VARYING' THEN " + Types.VARBINARY
+        + "\n    WHEN 'VARCHAR', 'CHARACTER VARYING' "
+        + "\n                                       THEN " + Types.VARCHAR
 
-        + "    WHEN DATA_TYPE = 'VARBINARY'     THEN " + Types.VARBINARY
-        + "    WHEN DATA_TYPE = 'VARCHAR'       THEN " + Types.VARCHAR
+        + "\n    ELSE"
+        // Pattern-match cases:
+        + "\n      CASE "
+
+        // TODO:  RESOLVE:  How does ARRAY appear in COLUMNS.DATA_TYPE?
+        // - Only at end (with no maximum size, as "VARCHAR(65535) ARRAY")?
+        // - Possibly with maximum size (as "... ARRAY[10]")?
+        // - Then, how should it appear in JDBC ("ARRAY"? "... ARRAY"?)
+        // (SQL source syntax:
+        //   <array type> ::=
+        //     <data type> ARRAY
+        //       [ <left bracket or trigraph> <maximum cardinality>
+        //         <right bracket or trigraph> ]
+        + "\n        WHEN DATA_TYPE LIKE '% ARRAY'  THEN " + Types.ARRAY
 
         // TODO:  RESOLVE:  How does MAP appear in COLUMNS.DATA_TYPE?
         // - Only at end?
         // - Otherwise?
         // TODO:  RESOLVE:  Should it map to Types.OTHER or something else?
         // Has appeared in Drill.  Should it?
-        + "    WHEN DATA_TYPE LIKE '% MAP'      THEN " + Types.OTHER
-
-        + "    ELSE                                  " + Types.OTHER
-        + "  END                               as  DATA_TYPE, \n"
-
-        + /*  6 */ "  DATA_TYPE                as  TYPE_NAME, \n"
-        ///*  7 */  FIXME:  BUG:  There should be: COLUMN_SIZE
-        + /*  8 */ "  CHARACTER_MAXIMUM_LENGTH as  BUFFER_LENGTH, \n"
-        //  FIXME:  BUG:  Many of the following are wrong.
-        + /*  9 */ "  NUMERIC_PRECISION        as  DECIMAL_PRECISION, \n" // FIXME:  BUG:
 Should be "DECIMAL_DIGITS"
-        + /* 10 */ "  NUMERIC_PRECISION_RADIX  as  NUM_PREC_RADIX, \n"
-        + /* 11 */ "  " + DatabaseMetaData.columnNullableUnknown
-        +             "                        as  NULLABLE, \n"
-        + /* 12 */ "  ''                       as  REMARKS, \n"
-        + /* 13 */ "  ''                       as  COLUMN_DEF, \n"
-        + /* 14 */ "  0                        as  SQL_DATA_TYPE, \n"
-        + /* 15 */ "  0                        as  SQL_DATETIME_SUB, \n"
-        + /* 16 */ "  4                        as  CHAR_OCTET_LENGTH, \n"
-        + /* 17 */ "  1                        as  ORDINAL_POSITION, \n"
-        + /* 18 */ "  'YES'                    as  IS_NULLABLE, \n"
-        + /* 19 */ "  ''                       as  SCOPE_CATALOG,"
-        + /* 20 */ "  ''                       as  SCOPE_SCHEMA, \n"
-        + /* 21 */ "  ''                       as  SCOPE_TABLE, \n"
-        + /* 22 */ "  ''                       as  SOURCE_DATA_TYPE, \n"
-        + /* 23 */ "  ''                       as  IS_AUTOINCREMENT, \n"
-        + /* 24 */ "  ''                       as  IS_GENERATEDCOLUMN \n"
-        + "FROM INFORMATION_SCHEMA.COLUMNS \n"
-        + "WHERE 1=1 ");
+        + "\n        WHEN DATA_TYPE LIKE '% MAP'    THEN " + Types.OTHER
+
+        // TODO:  RESOLVE:  How does "STRUCT" appear?
+        // - Only at beginning (as "STRUCT(INTEGER sint, BOOLEAN sboolean")?
+        // - Otherwise too?
+        // - Then, how should it appear in JDBC ("STRUCT"? "STRUCT(...)"?)
+        + "\n        WHEN DATA_TYPE LIKE 'STRUCT(%' THEN " + Types.STRUCT
+
+        + "\n        ELSE                                " + Types.OTHER
+        + "\n      END "
+        + "\n  END                                    as  DATA_TYPE, "
+
+        /*    6                                           TYPE_NAME */
+        // Map Drill's current info. schema values to what SQL standard
+        // specifies (for DATA_TYPE)--and assume that that's what JDBC wants.
+        + "\n  CASE DATA_TYPE "
+        + "\n    WHEN 'INTERVAL_YEAR_MONTH', "
+        + "\n         'INTERVAL_DAY_TIME'     THEN 'INTERVAL'"
+        // TODO:  Resolve how non-scalar types should appear in
+        // INFORMATION_SCHEMA.COLUMNS and here in JDBC:
+        // - "ARRAY" or "... ARRAY"?
+        // - "MAP" or "... MAP"?
+        // - "STRUCT" or "STRUCT(...)"?
+        + "\n    ELSE                               DATA_TYPE "
+        + "\n  END                                    as TYPE_NAME, "
+
+        /*    7                                           COLUMN_SIZE */
+        /* "... COLUMN_SIZE ....
+         * For numeric data, this is the maximum precision.
+         * For character data, this is the length in characters.
+         * For datetime datatypes, this is the length in characters of the String
+         *   representation (assuming the maximum allowed precision of the
+         *   fractional seconds component).
+         * For binary data, this is the length in bytes.
+         * For the ROWID datatype, this is the length in bytes.
+         * Null is returned for data types where the column size is not applicable."
+         *
+         * Note:  "Maximum precision" seems to mean the maximum number of
+         * significant decimal digits that can appear (not the number of digits
+         * that can be counted on, and not the maximum number of characters
+         * needed to display a value).
+         */
+        + "\n  CASE DATA_TYPE "
+
+        // "For numeric data, ... the maximum precision":
+        //   TODO:  Change literals to references to declared constant fields:
+        // - exact numeric types:
+        //   (in decimal digits, coordinated with NUM_PREC_RADIX = 10)
+        + "\n    WHEN 'TINYINT'                      THEN " + PREC_TINYINT
+        + "\n    WHEN 'SMALLINT'                     THEN " + PREC_SMALLINT
+        + "\n    WHEN 'INTEGER'                      THEN " + PREC_INTEGER
+        + "\n    WHEN 'BIGINT'                       THEN " + PREC_BIGINT
+        + "\n    WHEN 'DECIMAL', 'NUMERIC'           THEN NUMERIC_PRECISION "
+        // - approximate numeric types:
+        //   (in decimal digits, coordinated with NUM_PREC_RADIX = 10)
+        // TODO:  REVISIT:  Should these be in bits or decimal digits (with
+        //   NUM_PREC_RADIX coordinated)?  INFORMATION_SCHEMA.COLUMNS's value
+        //   are supposed to be in bits (per the SQL spec.).  What does JDBC
+        //   require and allow?
+        + "\n    WHEN 'FLOAT'                        THEN " + PREC_FLOAT
+        + "\n    WHEN 'DOUBLE'                       THEN " + PREC_DOUBLE
+        + "\n    WHEN 'REAL'                         THEN " + PREC_REAL
+
+        // "For character data, ... the length in characters":
+        // TODO:  BUG:  DRILL-2459:  For CHARACTER / CHAR, length is not in
+        // CHARACTER_MAXIMUM_LENGTH but in NUMERIC_PRECISION.
+        // Workaround:
+        + "\n    WHEN 'VARCHAR', 'CHARACTER VARYING' "
+        + "\n                                    THEN CHARACTER_MAXIMUM_LENGTH "
+        + "\n    WHEN 'CHAR', 'CHARACTER', "
+        + "\n         'NCHAR', 'NATIONAL CHAR', 'NATIONAL CHARACTER' "
+        + "\n                                        THEN NUMERIC_PRECISION "
+
+        // "For datetime datatypes ... length ... String representation
+        // (assuming the maximum ... precision of ... fractional seconds ...)":
+        + "\n    WHEN 'DATE'            THEN 10 "              // YYYY-MM-DD
+        + "\n    WHEN 'TIME'            THEN "
+        + "\n      CASE "
+        + "\n        WHEN NUMERIC_PRECISION > 0 "              // HH:MM:SS.sss
+        + "\n                           THEN          8 + 1 + NUMERIC_PRECISION"
+        + "\n        ELSE                             8"       // HH:MM:SS
+        + "\n      END "
+        + "\n    WHEN 'TIMESTAMP'       THEN "
+        + "\n      CASE "                          // date + "T" + time (above)
+        + "\n        WHEN NUMERIC_PRECISION > 0 "
+        + "                             THEN 10 + 1 + 8 + 1 + NUMERIC_PRECISION"
+        + "\n        ELSE                    10 + 1 + 8"
+        + "\n      END "
+
+        // TODO:  DRILL-2531:  When DRILL-2519 is fixed, use start and end unit
+        // and start-unit precision to implement maximum width more precisely
+        // (narrowly) than this workaround:
+        // For INTERVAL_YEAR_MONTH, maximum width is from "P1234567890Y12M"
+        // (5 + apparent maximum start unit precision of 10)
+        // unit precision):
+        + "\n    WHEN 'INTERVAL_YEAR_MONTH' "
+        + "\n                                        THEN 5 + "
+                                                          + PREC_INTERVAL_LEAD_MAX
+        // For INTERVAL_DAY_TIME, maximum width is from
+        // "P1234567890D12H12M12.123456789S" (12 + apparent maximum start unit
+        // precision of 10 + apparent maximum seconds fractional precision of 9):
+        + "\n    WHEN 'INTERVAL_DAY_TIME' "
+        + "\n                                        THEN 12 + "
+                                                          + ( PREC_INTERVAL_LEAD_MAX
+                                                             + PREC_INTERVAL_TRAIL_MAX )
+
+        // "For binary data, ... the length in bytes":
+        // BUG:  DRILL-2459:  BINARY and BINARY VARYING / VARBINARY length is
+        // not in CHARACTER_MAXIMUM_LENGTH but in NUMERIC_PRECISION.
+        // Workaround:
+        + "\n    WHEN 'VARBINARY', 'BINARY VARYING', "
+        + "\n         'BINARY'                       THEN NUMERIC_PRECISION "
+
+        // "For ... ROWID datatype...": Not in Drill?
+
+        // "Null ... for data types [for which] ... not applicable.":
+        + "\n    ELSE                                     NULL "
+        + "\n  END                                    as  COLUMN_SIZE, "
+
+        + /*  8 */ "\n  CHARACTER_MAXIMUM_LENGTH      as  BUFFER_LENGTH, "
+
+        /*    9                                           DECIMAL_DIGITS */
+        + "\n  CASE  DATA_TYPE"
+        + "\n    WHEN 'TINYINT', "
+        + "\n         'SMALLINT', "
+        + "\n         'INTEGER', "
+        + "\n         'BIGINT'                       THEN " + SCALE_INTEGRAL
+        + "\n    WHEN 'DECIMAL', "
+        + "\n         'NUMERIC'                      THEN NUMERIC_SCALE "
+        + "\n    WHEN 'FLOAT'                        THEN " + SCALE_FLOAT
+        + "\n    WHEN 'DOUBLE'                       THEN " + SCALE_DOUBLE
+        + "\n    WHEN 'REAL'                         THEN " + SCALE_REAL
+        + "\n    WHEN 'INTERVAL'                     THEN NUMERIC_SCALE "
+        + "\n    WHEN 'INTERVAL_YEAR_MONTH'          THEN 0 "
+        + "\n    WHEN 'INTERVAL_DAY_TIME'            THEN NUMERIC_SCALE "
+        + "\n  END                                    as  DECIMAL_DIGITS, "
+
+        /*   10                                           NUM_PREC_RADIX */
+        + "\n  CASE DATA_TYPE "
+        + "\n    WHEN 'TINYINT', "
+        + "\n         'SMALLINT', "
+        + "\n         'INTEGER', "
+        + "\n         'BIGINT'                       THEN " + RADIX_INTEGRAL
+        + "\n    WHEN 'DECIMAL', "
+        + "\n         'NUMERIC'                      THEN " + RADIX_DECIMAL
+        + "\n    WHEN 'FLOAT', "
+        + "\n         'DOUBLE', "
+        + "\n         'REAL'                         THEN " + RADIX_APPROXIMATE
+        + "\n    WHEN 'INTERVAL_YEAR_MONTH', "
+        + "\n         'INTERVAL_DAY_TIME'            THEN " + RADIX_INTERVAL
+        + "\n    ELSE                                     NULL"
+        + "\n  END                                    as  NUM_PREC_RADIX, "
+
+        /*   11                                           NULLABLE */
+        + "\n  CASE IS_NULLABLE "
+        + "\n    WHEN 'YES'      THEN " + DatabaseMetaData.columnNullable
+        + "\n    WHEN 'NO'       THEN " + DatabaseMetaData.columnNoNulls
+        + "\n    WHEN ''         THEN " + DatabaseMetaData.columnNullableUnknown
+        + "\n    ELSE                 -1"
+        + "\n  END                                    as  NULLABLE, "
+
+        + /* 12 */ "\n  CAST( NULL as VARCHAR )       as  REMARKS, "
+        + /* 13 */ "\n  CAST( NULL as VARCHAR )       as  COLUMN_DEF, "
+        + /* 14 */ "\n  0                             as  SQL_DATA_TYPE, "
+        + /* 15 */ "\n  0                             as  SQL_DATETIME_SUB, "
+
+        /*   16                                           CHAR_OCTET_LENGTH */
+        + "\n  CASE DATA_TYPE"
+        + "\n    WHEN 'VARCHAR', 'CHARACTER VARYING' "
+        + "\n                                 THEN 4 * CHARACTER_MAXIMUM_LENGTH "
+        + "\n    WHEN 'CHAR', 'CHARACTER', "
+        + "\n         'NCHAR', 'NATIONAL CHAR', 'NATIONAL CHARACTER' "
+        // TODO:  BUG:  DRILL-2459:  For CHARACTER / CHAR, length is not in
+        // CHARACTER_MAXIMUM_LENGTH but in NUMERIC_PRECISION.  Workaround:
+        + "\n                                 THEN 4 * NUMERIC_PRECISION "
+        + "\n    ELSE                              NULL "
+        + "\n  END                                    as  CHAR_OCTET_LENGTH, "
+
+        + /* 17 */ "\n  1 + ORDINAL_POSITION          as  ORDINAL_POSITION, "
+        + /* 18 */ "\n  IS_NULLABLE                   as  IS_NULLABLE, "
+        + /* 19 */ "\n  CAST( NULL as VARCHAR )       as  SCOPE_CATALOG, "
+        + /* 20 */ "\n  CAST( NULL as VARCHAR )       as  SCOPE_SCHEMA, "
+        + /* 21 */ "\n  CAST( NULL as VARCHAR )       as  SCOPE_TABLE, "
+        // TODO:  Change to SMALLINT when it's implemented (DRILL-2470):
+        + /* 22 */ "\n  CAST( NULL as INTEGER )       as  SOURCE_DATA_TYPE, "
+        + /* 23 */ "\n  ''                            as  IS_AUTOINCREMENT, "
+        + /* 24 */ "\n  ''                            as  IS_GENERATEDCOLUMN "
+
+        + "\n  FROM INFORMATION_SCHEMA.COLUMNS "
+        + "\n  WHERE 1=1 ");
 
     if (catalog != null) {
       sb.append("\n  AND TABLE_CATALOG = '" + DrillStringUtils.escapeSql(catalog) + "'");
@@ -272,16 +491,14 @@ public class MetaImpl implements Meta {
     if (schemaPattern.s != null) {
       sb.append("\n  AND TABLE_SCHEMA like '" + DrillStringUtils.escapeSql(schemaPattern.s)
+ "'");
     }
-
     if (tableNamePattern.s != null) {
       sb.append("\n  AND TABLE_NAME like '" + DrillStringUtils.escapeSql(tableNamePattern.s)
+ "'");
     }
-
     if (columnNamePattern.s != null) {
       sb.append("\n  AND COLUMN_NAME like '" + DrillStringUtils.escapeSql(columnNamePattern.s)
+ "'");
     }
 
-    sb.append(" ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME");
+    sb.append("\n ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME");
 
     return s(sb.toString());
   }


Mime
View raw message