release/src/router/mysql/mysql-test/suite/funcs_1/r/is_cml_myisam.result

   1 SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
   2 USE test;
   3 DROP TABLE IF EXISTS t1;
   4 CREATE TABLE t1
   5 (
   6 f1 CHAR         UNICODE,
   7 f2 CHAR(0)      UNICODE,
   8 f3 CHAR(10)     UNICODE,
   9 f5 VARCHAR(0)   UNICODE,
  10 f6 VARCHAR(255) UNICODE,
  11 f7 VARCHAR(260) UNICODE,
  12 f8 TEXT         UNICODE,
  13 f9 TINYTEXT     UNICODE,
  14 f10 MEDIUMTEXT  UNICODE,
  15 f11 LONGTEXT    UNICODE
  16 ) ENGINE = MyISAM;
  17 SELECT * FROM information_schema.columns
  18 WHERE table_schema LIKE 'test%'
  19 ORDER BY table_schema, table_name, column_name;
  20 TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     ORDINAL_POSITION        COLUMN_DEFAULT  IS_NULLABLE     DATA_TYPE       CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION       NUMERIC_SCALE   CHARACTER_SET_NAME      COLLATION_NAME  COLUMN_TYPE     COLUMN_KEY      EXTRA   PRIVILEGES      COLUMN_COMMENT
  21 NULL    test    t1      f1      1       NULL    YES     char    1       2       NULL    NULL    ucs2    ucs2_general_ci char(1)                 select,insert,update,references
  22 NULL    test    t1      f10     9       NULL    YES     mediumtext      8388607 16777215        NULL    NULL    ucs2    ucs2_general_ci mediumtext                      select,insert,update,references
  23 NULL    test    t1      f11     10      NULL    YES     longtext        2147483647      4294967295      NULL    NULL    ucs2    ucs2_general_ci longtext                        select,insert,update,references
  24 NULL    test    t1      f2      2       NULL    YES     char    0       0       NULL    NULL    ucs2    ucs2_general_ci char(0)                 select,insert,update,references
  25 NULL    test    t1      f3      3       NULL    YES     char    10      20      NULL    NULL    ucs2    ucs2_general_ci char(10)                        select,insert,update,references
  26 NULL    test    t1      f5      4       NULL    YES     varchar 0       0       NULL    NULL    ucs2    ucs2_general_ci varchar(0)                      select,insert,update,references
  27 NULL    test    t1      f6      5       NULL    YES     varchar 255     510     NULL    NULL    ucs2    ucs2_general_ci varchar(255)                    select,insert,update,references
  28 NULL    test    t1      f7      6       NULL    YES     varchar 260     520     NULL    NULL    ucs2    ucs2_general_ci varchar(260)                    select,insert,update,references
  29 NULL    test    t1      f8      7       NULL    YES     text    32767   65535   NULL    NULL    ucs2    ucs2_general_ci text                    select,insert,update,references
  30 NULL    test    t1      f9      8       NULL    YES     tinytext        127     255     NULL    NULL    ucs2    ucs2_general_ci tinytext                        select,insert,update,references
  31 ##########################################################################
  32 # Show the quotient of CHARACTER_OCTET_LENGTH and CHARACTER_MAXIMUM_LENGTH
  33 ##########################################################################
  34 SELECT DISTINCT
  35 CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
  36 DATA_TYPE,
  37 CHARACTER_SET_NAME,
  38 COLLATION_NAME
  39 FROM information_schema.columns
  40 WHERE table_schema LIKE 'test%'
  41 AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH = 1
  42 ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML;
  43 COL_CML DATA_TYPE       CHARACTER_SET_NAME      COLLATION_NAME
  44 SELECT DISTINCT
  45 CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
  46 DATA_TYPE,
  47 CHARACTER_SET_NAME,
  48 COLLATION_NAME
  49 FROM information_schema.columns
  50 WHERE table_schema LIKE 'test%'
  51 AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH <> 1
  52 ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML;
  53 COL_CML DATA_TYPE       CHARACTER_SET_NAME      COLLATION_NAME
  54 2.0000  char    ucs2    ucs2_general_ci
  55 2.0000  longtext        ucs2    ucs2_general_ci
  56 2.0000  mediumtext      ucs2    ucs2_general_ci
  57 2.0000  text    ucs2    ucs2_general_ci
  58 2.0000  varchar ucs2    ucs2_general_ci
  59 2.0079  tinytext        ucs2    ucs2_general_ci
  60 SELECT DISTINCT
  61 CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
  62 DATA_TYPE,
  63 CHARACTER_SET_NAME,
  64 COLLATION_NAME
  65 FROM information_schema.columns
  66 WHERE table_schema LIKE 'test%'
  67 AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH IS NULL
  68 ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML;
  69 COL_CML DATA_TYPE       CHARACTER_SET_NAME      COLLATION_NAME
  70 NULL    char    ucs2    ucs2_general_ci
  71 NULL    varchar ucs2    ucs2_general_ci
  72 --> CHAR(0) is allowed (see manual), and here both CHARACHTER_* values
  73 --> are 0, which is intended behavior, and the result of 0 / 0 IS NULL
  74 SELECT CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
  75 TABLE_SCHEMA,
  76 TABLE_NAME,
  77 COLUMN_NAME,
  78 DATA_TYPE,
  79 CHARACTER_MAXIMUM_LENGTH,
  80 CHARACTER_OCTET_LENGTH,
  81 CHARACTER_SET_NAME,
  82 COLLATION_NAME,
  83 COLUMN_TYPE
  84 FROM information_schema.columns
  85 WHERE table_schema LIKE 'test%'
  86 ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;
  87 COL_CML TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     DATA_TYPE       CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  CHARACTER_SET_NAME      COLLATION_NAME  COLUMN_TYPE
  88 2.0000  test    t1      f1      char    1       2       ucs2    ucs2_general_ci char(1)
  89 NULL    test    t1      f2      char    0       0       ucs2    ucs2_general_ci char(0)
  90 2.0000  test    t1      f3      char    10      20      ucs2    ucs2_general_ci char(10)
  91 NULL    test    t1      f5      varchar 0       0       ucs2    ucs2_general_ci varchar(0)
  92 2.0000  test    t1      f6      varchar 255     510     ucs2    ucs2_general_ci varchar(255)
  93 2.0000  test    t1      f7      varchar 260     520     ucs2    ucs2_general_ci varchar(260)
  94 2.0000  test    t1      f8      text    32767   65535   ucs2    ucs2_general_ci text
  95 2.0079  test    t1      f9      tinytext        127     255     ucs2    ucs2_general_ci tinytext
  96 2.0000  test    t1      f10     mediumtext      8388607 16777215        ucs2    ucs2_general_ci mediumtext
  97 2.0000  test    t1      f11     longtext        2147483647      4294967295      ucs2    ucs2_general_ci longtext
  98 DROP TABLE t1;