1
DROP DATABASE IF EXISTS test1;
6
DROP TABLE IF EXISTS t1, t2, t4, t10, t11;
7
CREATE TABLE t1 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = ndb;
8
CREATE TABLE t2 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = ndb;
9
CREATE TABLE t4 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = ndb;
10
CREATE TABLE t10 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = ndb;
11
CREATE TABLE t11 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = ndb;
12
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t1;
13
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t2;
14
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t4;
15
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t10;
16
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t11;
17
drop TABLE if exists t3;
18
CREATE TABLE t3 (f1 char(20), f2 char(20), f3 integer) ENGINE = ndb;
19
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t3.txt' INTO TABLE t3;
20
drop database if exists test4;
21
CREATE database test4;
23
CREATE TABLE t6 (f1 char(20), f2 char(25), f3 date, f4 int, f5 char(25), f6 int) ENGINE = ndb;
24
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t4.txt' INTO TABLE t6;
26
drop TABLE if exists t7, t8;
27
CREATE TABLE t7 (f1 char(20), f2 char(25), f3 date, f4 int) ENGINE = ndb;
28
CREATE TABLE t8 (f1 char(20), f2 char(25), f3 date, f4 int) ENGINE = ndb;
29
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' INTO TABLE t7;
31
Warning 1265 Data truncated for column 'f3' at row 1
32
Warning 1265 Data truncated for column 'f3' at row 2
33
Warning 1265 Data truncated for column 'f3' at row 3
34
Warning 1265 Data truncated for column 'f3' at row 4
35
Warning 1265 Data truncated for column 'f3' at row 5
36
Warning 1265 Data truncated for column 'f3' at row 6
37
Warning 1265 Data truncated for column 'f3' at row 7
38
Warning 1265 Data truncated for column 'f3' at row 8
39
Warning 1265 Data truncated for column 'f3' at row 9
40
Warning 1265 Data truncated for column 'f3' at row 10
41
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t7.txt' INTO TABLE t8;
43
Warning 1265 Data truncated for column 'f3' at row 1
44
Warning 1265 Data truncated for column 'f3' at row 2
45
Warning 1265 Data truncated for column 'f3' at row 3
46
Warning 1265 Data truncated for column 'f3' at row 4
47
Warning 1265 Data truncated for column 'f3' at row 5
48
Warning 1265 Data truncated for column 'f3' at row 6
49
Warning 1265 Data truncated for column 'f3' at row 7
50
Warning 1265 Data truncated for column 'f3' at row 8
51
Warning 1265 Data truncated for column 'f3' at row 9
52
Warning 1265 Data truncated for column 'f3' at row 10
53
drop TABLE if exists t9;
54
CREATE TABLE t9 (f1 int, f2 char(25), f3 int) ENGINE = ndb;
55
LOAD DATA INFILE 'MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' INTO TABLE t9;
56
SELECT * FROM information_schema.columns
57
WHERE table_schema LIKE 'test%'
58
ORDER BY table_schema, table_name, column_name;
59
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 STORAGE FORMAT
60
NULL test t1 f1 1 NULL YES char 20 20 NULL NULL latin1 latin1_swedish_ci char(20) select,insert,update,references Default Default
61
NULL test t1 f2 2 NULL YES char 25 25 NULL NULL latin1 latin1_swedish_ci char(25) select,insert,update,references Default Default
62
NULL test t1 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL date select,insert,update,references Default Default
63
NULL test t1 f4 4 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
64
NULL test t1 f5 5 NULL YES char 25 25 NULL NULL latin1 latin1_swedish_ci char(25) select,insert,update,references Default Default
65
NULL test t1 f6 6 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
66
NULL test t10 f1 1 NULL YES char 20 20 NULL NULL latin1 latin1_swedish_ci char(20) select,insert,update,references Default Default
67
NULL test t10 f2 2 NULL YES char 25 25 NULL NULL latin1 latin1_swedish_ci char(25) select,insert,update,references Default Default
68
NULL test t10 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL date select,insert,update,references Default Default
69
NULL test t10 f4 4 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
70
NULL test t10 f5 5 NULL YES char 25 25 NULL NULL latin1 latin1_swedish_ci char(25) select,insert,update,references Default Default
71
NULL test t10 f6 6 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
72
NULL test t11 f1 1 NULL YES char 20 20 NULL NULL latin1 latin1_swedish_ci char(20) select,insert,update,references Default Default
73
NULL test t11 f2 2 NULL YES char 25 25 NULL NULL latin1 latin1_swedish_ci char(25) select,insert,update,references Default Default
74
NULL test t11 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL date select,insert,update,references Default Default
75
NULL test t11 f4 4 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
76
NULL test t11 f5 5 NULL YES char 25 25 NULL NULL latin1 latin1_swedish_ci char(25) select,insert,update,references Default Default
77
NULL test t11 f6 6 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
78
NULL test t2 f1 1 NULL YES char 20 20 NULL NULL latin1 latin1_swedish_ci char(20) select,insert,update,references Default Default
79
NULL test t2 f2 2 NULL YES char 25 25 NULL NULL latin1 latin1_swedish_ci char(25) select,insert,update,references Default Default
80
NULL test t2 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL date select,insert,update,references Default Default
81
NULL test t2 f4 4 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
82
NULL test t2 f5 5 NULL YES char 25 25 NULL NULL latin1 latin1_swedish_ci char(25) select,insert,update,references Default Default
83
NULL test t2 f6 6 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
84
NULL test t3 f1 1 NULL YES char 20 20 NULL NULL latin1 latin1_swedish_ci char(20) select,insert,update,references Default Default
85
NULL test t3 f2 2 NULL YES char 20 20 NULL NULL latin1 latin1_swedish_ci char(20) select,insert,update,references Default Default
86
NULL test t3 f3 3 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
87
NULL test t4 f1 1 NULL YES char 20 20 NULL NULL latin1 latin1_swedish_ci char(20) select,insert,update,references Default Default
88
NULL test t4 f2 2 NULL YES char 25 25 NULL NULL latin1 latin1_swedish_ci char(25) select,insert,update,references Default Default
89
NULL test t4 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL date select,insert,update,references Default Default
90
NULL test t4 f4 4 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
91
NULL test t4 f5 5 NULL YES char 25 25 NULL NULL latin1 latin1_swedish_ci char(25) select,insert,update,references Default Default
92
NULL test t4 f6 6 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
93
NULL test t7 f1 1 NULL YES char 20 20 NULL NULL latin1 latin1_swedish_ci char(20) select,insert,update,references Default Default
94
NULL test t7 f2 2 NULL YES char 25 25 NULL NULL latin1 latin1_swedish_ci char(25) select,insert,update,references Default Default
95
NULL test t7 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL date select,insert,update,references Default Default
96
NULL test t7 f4 4 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
97
NULL test t8 f1 1 NULL YES char 20 20 NULL NULL latin1 latin1_swedish_ci char(20) select,insert,update,references Default Default
98
NULL test t8 f2 2 NULL YES char 25 25 NULL NULL latin1 latin1_swedish_ci char(25) select,insert,update,references Default Default
99
NULL test t8 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL date select,insert,update,references Default Default
100
NULL test t8 f4 4 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
101
NULL test t9 f1 1 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
102
NULL test t9 f2 2 NULL YES char 25 25 NULL NULL latin1 latin1_swedish_ci char(25) select,insert,update,references Default Default
103
NULL test t9 f3 3 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
104
NULL test4 t6 f1 1 NULL YES char 20 20 NULL NULL latin1 latin1_swedish_ci char(20) select,insert,update,references Default Default
105
NULL test4 t6 f2 2 NULL YES char 25 25 NULL NULL latin1 latin1_swedish_ci char(25) select,insert,update,references Default Default
106
NULL test4 t6 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL date select,insert,update,references Default Default
107
NULL test4 t6 f4 4 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
108
NULL test4 t6 f5 5 NULL YES char 25 25 NULL NULL latin1 latin1_swedish_ci char(25) select,insert,update,references Default Default
109
NULL test4 t6 f6 6 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references Default Default
110
##########################################################################
111
# Show the quotient of CHARACTER_OCTET_LENGTH and CHARACTER_MAXIMUM_LENGTH
112
##########################################################################
114
CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
118
FROM information_schema.columns
119
WHERE table_schema LIKE 'test%'
120
AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH = 1
121
ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML;
122
COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME
123
1.0000 char latin1 latin1_swedish_ci
125
CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
129
FROM information_schema.columns
130
WHERE table_schema LIKE 'test%'
131
AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH <> 1
132
ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML;
133
COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME
135
CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
139
FROM information_schema.columns
140
WHERE table_schema LIKE 'test%'
141
AND CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH IS NULL
142
ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML;
143
COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME
146
--> CHAR(0) is allowed (see manual), and here both CHARACHTER_* values
147
--> are 0, which is intended behavior, and the result of 0 / 0 IS NULL
148
SELECT CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
153
CHARACTER_MAXIMUM_LENGTH,
154
CHARACTER_OCTET_LENGTH,
158
FROM information_schema.columns
159
WHERE table_schema LIKE 'test%'
160
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;
161
COL_CML TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE
162
1.0000 test t1 f1 char 20 20 latin1 latin1_swedish_ci char(20)
163
1.0000 test t1 f2 char 25 25 latin1 latin1_swedish_ci char(25)
164
NULL test t1 f3 date NULL NULL NULL NULL date
165
NULL test t1 f4 int NULL NULL NULL NULL int(11)
166
1.0000 test t1 f5 char 25 25 latin1 latin1_swedish_ci char(25)
167
NULL test t1 f6 int NULL NULL NULL NULL int(11)
168
1.0000 test t10 f1 char 20 20 latin1 latin1_swedish_ci char(20)
169
1.0000 test t10 f2 char 25 25 latin1 latin1_swedish_ci char(25)
170
NULL test t10 f3 date NULL NULL NULL NULL date
171
NULL test t10 f4 int NULL NULL NULL NULL int(11)
172
1.0000 test t10 f5 char 25 25 latin1 latin1_swedish_ci char(25)
173
NULL test t10 f6 int NULL NULL NULL NULL int(11)
174
1.0000 test t11 f1 char 20 20 latin1 latin1_swedish_ci char(20)
175
1.0000 test t11 f2 char 25 25 latin1 latin1_swedish_ci char(25)
176
NULL test t11 f3 date NULL NULL NULL NULL date
177
NULL test t11 f4 int NULL NULL NULL NULL int(11)
178
1.0000 test t11 f5 char 25 25 latin1 latin1_swedish_ci char(25)
179
NULL test t11 f6 int NULL NULL NULL NULL int(11)
180
1.0000 test t2 f1 char 20 20 latin1 latin1_swedish_ci char(20)
181
1.0000 test t2 f2 char 25 25 latin1 latin1_swedish_ci char(25)
182
NULL test t2 f3 date NULL NULL NULL NULL date
183
NULL test t2 f4 int NULL NULL NULL NULL int(11)
184
1.0000 test t2 f5 char 25 25 latin1 latin1_swedish_ci char(25)
185
NULL test t2 f6 int NULL NULL NULL NULL int(11)
186
1.0000 test t3 f1 char 20 20 latin1 latin1_swedish_ci char(20)
187
1.0000 test t3 f2 char 20 20 latin1 latin1_swedish_ci char(20)
188
NULL test t3 f3 int NULL NULL NULL NULL int(11)
189
1.0000 test t4 f1 char 20 20 latin1 latin1_swedish_ci char(20)
190
1.0000 test t4 f2 char 25 25 latin1 latin1_swedish_ci char(25)
191
NULL test t4 f3 date NULL NULL NULL NULL date
192
NULL test t4 f4 int NULL NULL NULL NULL int(11)
193
1.0000 test t4 f5 char 25 25 latin1 latin1_swedish_ci char(25)
194
NULL test t4 f6 int NULL NULL NULL NULL int(11)
195
1.0000 test t7 f1 char 20 20 latin1 latin1_swedish_ci char(20)
196
1.0000 test t7 f2 char 25 25 latin1 latin1_swedish_ci char(25)
197
NULL test t7 f3 date NULL NULL NULL NULL date
198
NULL test t7 f4 int NULL NULL NULL NULL int(11)
199
1.0000 test t8 f1 char 20 20 latin1 latin1_swedish_ci char(20)
200
1.0000 test t8 f2 char 25 25 latin1 latin1_swedish_ci char(25)
201
NULL test t8 f3 date NULL NULL NULL NULL date
202
NULL test t8 f4 int NULL NULL NULL NULL int(11)
203
NULL test t9 f1 int NULL NULL NULL NULL int(11)
204
1.0000 test t9 f2 char 25 25 latin1 latin1_swedish_ci char(25)
205
NULL test t9 f3 int NULL NULL NULL NULL int(11)
206
1.0000 test4 t6 f1 char 20 20 latin1 latin1_swedish_ci char(20)
207
1.0000 test4 t6 f2 char 25 25 latin1 latin1_swedish_ci char(25)
208
NULL test4 t6 f3 date NULL NULL NULL NULL date
209
NULL test4 t6 f4 int NULL NULL NULL NULL int(11)
210
1.0000 test4 t6 f5 char 25 25 latin1 latin1_swedish_ci char(25)
211
NULL test4 t6 f6 int NULL NULL NULL NULL int(11)