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