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