release/src/router/mysql/mysql-test/suite/funcs_1/r/is_cml_memory.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 ) ENGINE = MEMORY;
  13 SELECT * FROM information_schema.columns
  14 WHERE table_schema LIKE 'test%'
  15 ORDER BY table_schema, table_name, column_name;
  16 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
  17 NULL    test    t1      f1      1       NULL    YES     char    1       2       NULL    NULL    ucs2    ucs2_general_ci char(1)                 select,insert,update,references
  18 NULL    test    t1      f2      2       NULL    YES     char    0       0       NULL    NULL    ucs2    ucs2_general_ci char(0)                 select,insert,update,references
  19 NULL    test    t1      f3      3       NULL    YES     char    10      20      NULL    NULL    ucs2    ucs2_general_ci char(10)                        select,insert,update,references
  20 NULL    test    t1      f5      4       NULL    YES     varchar 0       0       NULL    NULL    ucs2    ucs2_general_ci varchar(0)                      select,insert,update,references
  21 NULL    test    t1      f6      5       NULL    YES     varchar 255     510     NULL    NULL    ucs2    ucs2_general_ci varchar(255)                    select,insert,update,references
  22 NULL    test    t1      f7      6       NULL    YES     varchar 260     520     NULL    NULL    ucs2    ucs2_general_ci varchar(260)                    select,insert,update,references
  23 ##########################################################################
  24 # Show the quotient of CHARACTER_OCTET_LENGTH and CHARACTER_MAXIMUM_LENGTH
  25 ##########################################################################
  26 SELECT DISTINCT
  27 CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
  28 DATA_TYPE,
  29 CHARACTER_SET_NAME,
  30 COLLATION_NAME
  31 FROM information_schema.columns
  32 WHERE table_schema LIKE 'test%'
  33 AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH = 1
  34 ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML;
  35 COL_CML DATA_TYPE       CHARACTER_SET_NAME      COLLATION_NAME
  36 SELECT DISTINCT
  37 CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
  38 DATA_TYPE,
  39 CHARACTER_SET_NAME,
  40 COLLATION_NAME
  41 FROM information_schema.columns
  42 WHERE table_schema LIKE 'test%'
  43 AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH <> 1
  44 ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML;
  45 COL_CML DATA_TYPE       CHARACTER_SET_NAME      COLLATION_NAME
  46 2.0000  char    ucs2    ucs2_general_ci
  47 2.0000  varchar ucs2    ucs2_general_ci
  48 SELECT DISTINCT
  49 CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
  50 DATA_TYPE,
  51 CHARACTER_SET_NAME,
  52 COLLATION_NAME
  53 FROM information_schema.columns
  54 WHERE table_schema LIKE 'test%'
  55 AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH IS NULL
  56 ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML;
  57 COL_CML DATA_TYPE       CHARACTER_SET_NAME      COLLATION_NAME
  58 NULL    char    ucs2    ucs2_general_ci
  59 NULL    varchar ucs2    ucs2_general_ci
  60 --> CHAR(0) is allowed (see manual), and here both CHARACHTER_* values
  61 --> are 0, which is intended behavior, and the result of 0 / 0 IS NULL
  62 SELECT CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
  63 TABLE_SCHEMA,
  64 TABLE_NAME,
  65 COLUMN_NAME,
  66 DATA_TYPE,
  67 CHARACTER_MAXIMUM_LENGTH,
  68 CHARACTER_OCTET_LENGTH,
  69 CHARACTER_SET_NAME,
  70 COLLATION_NAME,
  71 COLUMN_TYPE
  72 FROM information_schema.columns
  73 WHERE table_schema LIKE 'test%'
  74 ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;
  75 COL_CML TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     DATA_TYPE       CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  CHARACTER_SET_NAME      COLLATION_NAME  COLUMN_TYPE
  76 2.0000  test    t1      f1      char    1       2       ucs2    ucs2_general_ci char(1)
  77 NULL    test    t1      f2      char    0       0       ucs2    ucs2_general_ci char(0)
  78 2.0000  test    t1      f3      char    10      20      ucs2    ucs2_general_ci char(10)
  79 NULL    test    t1      f5      varchar 0       0       ucs2    ucs2_general_ci varchar(0)
  80 2.0000  test    t1      f6      varchar 255     510     ucs2    ucs2_general_ci varchar(255)
  81 2.0000  test    t1      f7      varchar 260     520     ucs2    ucs2_general_ci varchar(260)
  82 DROP TABLE t1;