| 1 |
ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci; |
| 2 |
CREATE TABLE t1 |
| 3 |
( |
| 4 |
t1_BIGINT BIGINT, |
| 5 |
t1_BIGINT_UNSIGNED BIGINT UNSIGNED, |
| 6 |
t1_BINARY_100 BINARY(100), |
| 7 |
t1_BIT_2 BIT(2), |
| 8 |
t1_BIT_20 BIT(20), |
| 9 |
t1_BLOB BLOB, |
| 10 |
t1_CHAR_100 CHAR(100), |
| 11 |
t1_CHAR_100_BINARY CHAR(100) BINARY, |
| 12 |
t1_DATE DATE, |
| 13 |
t1_DATETIME DATETIME, |
| 14 |
t1_DATETIME_6 DATETIME(6), |
| 15 |
t1_DECIMAL_10_3 DECIMAL(10,3), |
| 16 |
t1_DECIMAL_10_3_UNSIGNED DECIMAL(10,3) UNSIGNED, |
| 17 |
t1_DOUBLE DOUBLE, |
| 18 |
t1_DOUBLE_UNSIGNED DOUBLE UNSIGNED, |
| 19 |
t1_ENUM ENUM('a', 'b', 'c'), |
| 20 |
t1_ENUM_BINARY ENUM('a','b') BINARY, |
| 21 |
t1_ENUM_256 ENUM('a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9', |
| 22 |
'a10', 'a11', 'a12', 'a13', 'a14', 'a15', 'a16', 'a17', 'a18', 'a19', |
| 23 |
'a20', 'a21', 'a22', 'a23', 'a24', 'a25', 'a26', 'a27', 'a28', 'a29', |
| 24 |
'a30', 'a31', 'a32', 'a33', 'a34', 'a35', 'a36', 'a37', 'a38', 'a39', |
| 25 |
'a40', 'a41', 'a42', 'a43', 'a44', 'a45', 'a46', 'a47', 'a48', 'a49', |
| 26 |
'a50', 'a51', 'a52', 'a53', 'a54', 'a55', 'a56', 'a57', 'a58', 'a59', |
| 27 |
'a60', 'a61', 'a62', 'a63', 'a64', 'a65', 'a66', 'a67', 'a68', 'a69', |
| 28 |
'a70', 'a71', 'a72', 'a73', 'a74', 'a75', 'a76', 'a77', 'a78', 'a79', |
| 29 |
'a80', 'a81', 'a82', 'a83', 'a84', 'a85', 'a86', 'a87', 'a88', 'a89', |
| 30 |
'a90', 'a91', 'a92', 'a93', 'a94', 'a95', 'a96', 'a97', 'a98', 'a99', |
| 31 |
'a100', 'a101', 'a102', 'a103', 'a104', 'a105', 'a106', 'a107', 'a108', |
| 32 |
'a109', 'a110', 'a111', 'a112', 'a113', 'a114', 'a115', 'a116', 'a117', |
| 33 |
'a118', 'a119', 'a120', 'a121', 'a122', 'a123', 'a124', 'a125', 'a126', |
| 34 |
'a127', 'a128', 'a129', 'a130', 'a131', 'a132', 'a133', 'a134', 'a135', |
| 35 |
'a136', 'a137', 'a138', 'a139', 'a140', 'a141', 'a142', 'a143', 'a144', |
| 36 |
'a145', 'a146', 'a147', 'a148', 'a149', 'a150', 'a151', 'a152', 'a153', |
| 37 |
'a154', 'a155', 'a156', 'a157', 'a158', 'a159', 'a160', 'a161', 'a162', |
| 38 |
'a163', 'a164', 'a165', 'a166', 'a167', 'a168', 'a169', 'a170', 'a171', |
| 39 |
'a172', 'a173', 'a174', 'a175', 'a176', 'a177', 'a178', 'a179', 'a180', |
| 40 |
'a181', 'a182', 'a183', 'a184', 'a185', 'a186', 'a187', 'a188', 'a189', |
| 41 |
'a190', 'a191', 'a192', 'a193', 'a194', 'a195', 'a196', 'a197', 'a198', |
| 42 |
'a199', 'a200', 'a201', 'a202', 'a203', 'a204', 'a205', 'a206', 'a207', |
| 43 |
'a208', 'a209', 'a210', 'a211', 'a212', 'a213', 'a214', 'a215', 'a216', |
| 44 |
'a217', 'a218', 'a219', 'a220', 'a221', 'a222', 'a223', 'a224', 'a225', |
| 45 |
'a226', 'a227', 'a228', 'a229', 'a230', 'a231', 'a232', 'a233', 'a234', |
| 46 |
'a235', 'a236', 'a237', 'a238', 'a239', 'a240', 'a241', 'a242', 'a243', |
| 47 |
'a244', 'a245', 'a246', 'a247', 'a248', 'a249', 'a250', 'a251', 'a252', |
| 48 |
'a253', 'a254', 'a255', 'a256'), |
| 49 |
t1_FLOAT FLOAT, |
| 50 |
t1_FLOAT_UNSIGNED FLOAT UNSIGNED, |
| 51 |
t1_INT INT, |
| 52 |
t1_INT_UNSIGNED INT UNSIGNED, |
| 53 |
t1_LONGBLOB LONGBLOB, |
| 54 |
t1_LONGTEXT LONGTEXT, |
| 55 |
t1_MEDIUMBLOB MEDIUMBLOB, |
| 56 |
t1_MEDIUMINT MEDIUMINT, |
| 57 |
t1_MEDIUMINT_UNSIGNED MEDIUMINT UNSIGNED, |
| 58 |
t1_MEDIUMTEXT MEDIUMTEXT, |
| 59 |
t1_SET SET('a', 'b', 'c'), |
| 60 |
t1_SET_BINARY SET('a','b') BINARY, |
| 61 |
t1_SET_9 SET('a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9'), |
| 62 |
t1_SMALLINT SMALLINT, |
| 63 |
t1_SMALLINT_UNSIGNED SMALLINT UNSIGNED, |
| 64 |
t1_TEXT TEXT, |
| 65 |
t1_TIME TIME, |
| 66 |
t1_TIME_4 TIME(4), |
| 67 |
t1_TIMESTAMP TIMESTAMP, |
| 68 |
t1_TIMESTAMP_5 TIMESTAMP(5), |
| 69 |
t1_TINYBLOB TINYBLOB, |
| 70 |
t1_TINYINT TINYINT, |
| 71 |
t1_TINYINT_UNSIGNED TINYINT UNSIGNED, |
| 72 |
t1_TINYTEXT TINYTEXT, |
| 73 |
t1_VARBINARY_100 VARBINARY(100), |
| 74 |
t1_VARCHAR_10 VARCHAR(10), |
| 75 |
t1_VARCHAR_10_BINARY VARCHAR(10) BINARY, |
| 76 |
t1_VARCHAR_500 VARCHAR(500), |
| 77 |
t1_VARCHAR_500_BINARY VARCHAR(500) BINARY, |
| 78 |
t1_YEAR_2 YEAR(2), |
| 79 |
t1_YEAR_4 YEAR(4), |
| 80 |
t1_CHAR_0 CHAR(0), |
| 81 |
t1_MYSQL_0 CHAR(0) CHARACTER SET utf8, |
| 82 |
t1_VARCHAR_0 VARCHAR(0), |
| 83 |
t1_VARMYSQL_0 VARCHAR(0) CHARACTER SET utf8 |
| 84 |
) ENGINE=InnoDB; |
| 85 |
Warnings: |
| 86 |
Warning 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use 'YEAR(4)' instead |
| 87 |
INSERT INTO t1 () VALUES (); |
| 88 |
SELECT |
| 89 |
name, |
| 90 |
CASE mtype |
| 91 |
WHEN 1 THEN "DATA_VARCHAR" |
| 92 |
WHEN 2 THEN "DATA_CHAR" |
| 93 |
WHEN 3 THEN "DATA_FIXBINARY" |
| 94 |
WHEN 4 THEN "DATA_BINARY" |
| 95 |
WHEN 5 THEN "DATA_BLOB" |
| 96 |
WHEN 6 THEN "DATA_INT" |
| 97 |
WHEN 7 THEN "DATA_SYS_CHILD" |
| 98 |
WHEN 8 THEN "DATA_SYS" |
| 99 |
WHEN 9 THEN "DATA_FLOAT" |
| 100 |
WHEN 10 THEN "DATA_DOUBLE" |
| 101 |
WHEN 11 THEN "DATA_DECIMAL" |
| 102 |
WHEN 12 THEN "DATA_VARMYSQL" |
| 103 |
WHEN 13 THEN "DATA_MYSQL" |
| 104 |
WHEN 63 THEN "DATA_MTYPE_MAX" |
| 105 |
ELSE mtype |
| 106 |
END AS mtype, |
| 107 |
IF((prtype & 512) = 512,"UNSIGNED","") AS is_unsigned |
| 108 |
FROM information_schema.INNODB_SYS_COLUMNS |
| 109 |
WHERE name LIKE "t1\_%" |
| 110 |
ORDER BY name; |
| 111 |
name mtype is_unsigned |
| 112 |
t1_BIGINT DATA_INT |
| 113 |
t1_BIGINT_UNSIGNED DATA_INT UNSIGNED |
| 114 |
t1_BINARY_100 DATA_FIXBINARY |
| 115 |
t1_BIT_2 DATA_FIXBINARY UNSIGNED |
| 116 |
t1_BIT_20 DATA_FIXBINARY UNSIGNED |
| 117 |
t1_BLOB DATA_BLOB |
| 118 |
t1_CHAR_0 DATA_CHAR |
| 119 |
t1_CHAR_100 DATA_CHAR |
| 120 |
t1_CHAR_100_BINARY DATA_MYSQL |
| 121 |
t1_DATE DATA_INT |
| 122 |
t1_DATETIME DATA_FIXBINARY |
| 123 |
t1_DATETIME_6 DATA_FIXBINARY |
| 124 |
t1_DECIMAL_10_3 DATA_FIXBINARY |
| 125 |
t1_DECIMAL_10_3_UNSIGNED DATA_FIXBINARY UNSIGNED |
| 126 |
t1_DOUBLE DATA_DOUBLE |
| 127 |
t1_DOUBLE_UNSIGNED DATA_DOUBLE UNSIGNED |
| 128 |
t1_ENUM DATA_INT UNSIGNED |
| 129 |
t1_ENUM_256 DATA_INT UNSIGNED |
| 130 |
t1_ENUM_BINARY DATA_INT UNSIGNED |
| 131 |
t1_FLOAT DATA_FLOAT |
| 132 |
t1_FLOAT_UNSIGNED DATA_FLOAT UNSIGNED |
| 133 |
t1_INT DATA_INT |
| 134 |
t1_INT_UNSIGNED DATA_INT UNSIGNED |
| 135 |
t1_LONGBLOB DATA_BLOB |
| 136 |
t1_LONGTEXT DATA_BLOB |
| 137 |
t1_MEDIUMBLOB DATA_BLOB |
| 138 |
t1_MEDIUMINT DATA_INT |
| 139 |
t1_MEDIUMINT_UNSIGNED DATA_INT UNSIGNED |
| 140 |
t1_MEDIUMTEXT DATA_BLOB |
| 141 |
t1_MYSQL_0 DATA_MYSQL |
| 142 |
t1_SET DATA_INT UNSIGNED |
| 143 |
t1_SET_9 DATA_INT UNSIGNED |
| 144 |
t1_SET_BINARY DATA_INT UNSIGNED |
| 145 |
t1_SMALLINT DATA_INT |
| 146 |
t1_SMALLINT_UNSIGNED DATA_INT UNSIGNED |
| 147 |
t1_TEXT DATA_BLOB |
| 148 |
t1_TIME DATA_FIXBINARY |
| 149 |
t1_TIMESTAMP DATA_FIXBINARY UNSIGNED |
| 150 |
t1_TIMESTAMP_5 DATA_FIXBINARY UNSIGNED |
| 151 |
t1_TIME_4 DATA_FIXBINARY |
| 152 |
t1_TINYBLOB DATA_BLOB |
| 153 |
t1_TINYINT DATA_INT |
| 154 |
t1_TINYINT_UNSIGNED DATA_INT UNSIGNED |
| 155 |
t1_TINYTEXT DATA_BLOB |
| 156 |
t1_VARBINARY_100 DATA_BINARY |
| 157 |
t1_VARCHAR_0 DATA_VARCHAR |
| 158 |
t1_VARCHAR_10 DATA_VARCHAR |
| 159 |
t1_VARCHAR_10_BINARY DATA_VARMYSQL |
| 160 |
t1_VARCHAR_500 DATA_VARCHAR |
| 161 |
t1_VARCHAR_500_BINARY DATA_VARMYSQL |
| 162 |
t1_VARMYSQL_0 DATA_VARMYSQL |
| 163 |
t1_YEAR_2 DATA_INT UNSIGNED |
| 164 |
t1_YEAR_4 DATA_INT UNSIGNED |
| 165 |
DROP TABLE t1; |
| 166 |
# |
| 167 |
# MDEV-17815 Assertion failed in btr_node_ptr_max_size for CHAR(0) |
| 168 |
# |
| 169 |
CREATE TABLE t1 (c CHAR(0), KEY(c)) ENGINE=InnoDB; |
| 170 |
INSERT INTO t1 VALUES (''); |
| 171 |
DROP TABLE t1; |
| 172 |
# |
| 173 |
# MDEV-18039 Assertion failed in btr_node_ptr_max_size for VARCHAR(0) |
| 174 |
# |
| 175 |
CREATE TABLE t1 (c VARCHAR(0), KEY(c)) ENGINE=InnoDB; |
| 176 |
INSERT INTO t1 VALUES (''); |
| 177 |
DROP TABLE t1; |
| 178 |
# |
| 179 |
# MDEV-19216 Assertion ...SYS_FOREIGN failed in btr_node_ptr_max_size |
| 180 |
# |
| 181 |
CREATE TABLE t1 (b BINARY(0), v VARBINARY(0), KEY(b), KEY(v)) ENGINE=InnoDB; |
| 182 |
INSERT INTO t1 SET b='',v=''; |
| 183 |
DROP TABLE t1; |
| 184 |
# End of 10.4 tests |
| 185 |
ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci; |