← Back to branch summary

~ubuntu-branches/ubuntu/precise/mysql-5.1/precise

« back to all changes in this revision

Viewing changes to mysql-test/suite/funcs_1/r/is_cml_innodb.result

  • Committer: Bazaar Package Importer
  • Author(s): Norbert Tretkowski
  • Date: 2010-03-17 14:56:02 UTC
  • Revision ID: james.westby@ubuntu.com-20100317145602-x7e30l1b2sb5s6w6
Tags: upstream-5.1.45
Import upstream version 5.1.45

Show diffs side-by-side

added added

removed removed

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