Bug #74962 Matching in char field type is not correct
Submitted: 21 Nov 2014 13:24 Modified: 21 Nov 2014 14:20
Reporter: Armen Ghahramanyan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.5/5.6 OS:Any
Assigned to: CPU Architecture:Any

[21 Nov 2014 13:24] Armen Ghahramanyan 
Description:
1) create table 
CREATE TABLE IF NOT EXISTS `in_docs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `doc_type` smallint(6) NOT NULL DEFAULT '0',
  `sender_code` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `orig_reg_number` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `orig_sent_date` date DEFAULT NULL,
  `followup_doc_id` int(11) NOT NULL,
  `reg_year` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `reg_number` int(11) NOT NULL,
  `paper_reg_number` varchar(25) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `title` text COLLATE utf8_unicode_ci NOT NULL,
  `contact_id` int(11) NOT NULL DEFAULT '0',
  `contact_info` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `reg_date` date NOT NULL DEFAULT '0000-00-00',
  `deadline` date DEFAULT NULL,
  `ext_response_status` enum('none','accept','reject') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'none',
  `deadline_notes` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `creator_user_id` smallint(11) NOT NULL DEFAULT '0',
  `access_level_id` smallint(6) NOT NULL DEFAULT '0',
  `fully_electronic` tinyint(1) NOT NULL DEFAULT '0',
  `media_type_id` smallint(6) NOT NULL DEFAULT '0',
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `reg_year_and_number` (`reg_year`,`reg_number`),
  KEY `reg_date` (`reg_date`),
  KEY `orig_reg_number` (`orig_reg_number`),
  KEY `contact_info` (`contact_info`),
  KEY `title` (`title`(333)),
  KEY `followup_doc_id` (`followup_doc_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci PACK_KEYS=0 COMMENT='Incoming Documents' AUTO_INCREMENT=31824 ;

2) Then import data
INSERT INTO `in_docs` (`reg_year`, `id`, `reg_number`) VALUES
('11', 31659, 33),
('11', 31660, 34),
('11', 31657, 31),
('11', 31658, 32),
('11', 31656, 30),
('11', 31655, 29),
('11', 31654, 28),
('11', 31652, 26),
('11', 31653, 27),
('11', 31651, 25),
('11', 31650, 24),
('11', 31649, 23),
('11', 31648, 22),
('11', 31647, 21),
('11', 31646, 20),
('11', 31645, 19),
('11', 31644, 18),
('11', 31643, 17),
('11', 31642, 16),
('11', 31640, 14),
('11', 31641, 15),
('11', 31639, 13),
('11', 31638, 12),
('11', 31637, 11),
('11', 31636, 10),
('11', 31635, 9),
('11', 31634, 8),
('11', 31633, 7),
('11', 31632, 6),
('11', 31630, 4),
('11', 31631, 5),
('11', 31629, 3),
('11', 31627, 1),
('11', 31628, 2),
('12', 31661, 1),
('12', 31662, 2),
('12', 31663, 3),
('12', 31664, 4),
('12', 31665, 5),
('12', 31666, 6),
('12', 31667, 7),
('12', 31668, 8),
('12', 31669, 9),
('12', 31670, 10),
('12', 31671, 11),
('12', 31672, 12),
('12', 31673, 13),
('12', 31674, 14),
('12', 31675, 15),
('12', 31676, 16),
('12', 31677, 17),
('12', 31678, 18),
('12', 31679, 19),
('12', 31680, 20),
('12', 31681, 21),
('12', 31682, 22),
('12', 31683, 23),
('12', 31684, 24),
('12', 31685, 25),
('12', 31686, 26),
('12', 31687, 27),
('12', 31688, 28),
('12', 31689, 29),
('12', 31690, 30),
('12', 31691, 31),
('12', 31692, 32),
('12', 31693, 33),
('12', 31694, 34),
('13', 31695, 15),
('13', 31696, 1235),
('13', 31697, 1236),
('13', 31698, 1237),
('13', 31699, 1238),
('13', 31700, 1239),
('13', 31701, 1240),
('13', 31702, 1241),
('13', 31703, 1242),
('13', 31704, 1243),
('13', 31705, 1244),
('13', 31706, 1245),
('13', 31707, 1246),
('13', 31708, 1247),
('13', 31709, 1248),
('13', 31710, 1249),
('13', 31711, 1250),
('13', 31712, 1251),
('13', 31713, 1252),
('13', 31714, 1253),
('13', 31715, 1254),
('13', 31716, 1255),
('13', 31717, 1256),
('13', 31718, 1257),
('13', 31719, 1258),
('13', 31720, 1259),
('13', 31721, 1260),
('13', 31722, 1261),
('13', 31723, 1262),
('13', 31724, 1263),
('13', 31725, 1264),
('13', 31726, 1265),
('13', 31727, 1266),
('13', 31728, 1267),
('13', 31729, 1268),
('13', 31730, 1269),
('13', 31731, 1270),
('13', 31732, 1271),
('13', 31733, 1272),
('13', 31734, 1273),
('13', 31735, 1274),
('13', 31736, 1275),
('13', 31737, 1276),
('13', 31738, 1277),
('13', 31739, 1278),
('13', 31740, 1279),
('13', 31741, 1280),
('13', 31742, 1281),
('13', 31743, 1282),
('13', 31744, 1283),
('13', 31745, 1284),
('13', 31746, 1285),
('13', 31747, 1286),
('13', 31748, 1287),
('13', 31749, 1288),
('13', 31750, 1289),
('13', 31751, 1290),
('13', 31752, 1291),
('13', 31753, 1292),
('13', 31754, 1293),
('13', 31755, 1294),
('13', 31756, 1295),
('13', 31757, 1296),
('13', 31758, 1297),
('13', 31759, 1298),
('13', 31760, 1299),
('13', 31761, 1300),
('13', 31762, 1301),
('13', 31763, 1302),
('13', 31764, 1303),
('13', 31765, 1304),
('13', 31766, 1305),
('13', 31767, 1306),
('13', 31768, 1307),
('13', 31769, 1308),
('13', 31770, 1309),
('13', 31771, 1310),
('13', 31772, 1311),
('13', 31773, 1312),
('13', 31774, 1313),
('13', 31775, 1314),
('13', 31776, 1315),
('13', 31777, 1316),
('13', 31778, 1317),
('13', 31779, 1318),
('13', 31780, 1319),
('13', 31781, 1322),
('13', 31782, 1321),
('14', 31783, 1),
('14', 31784, 2),
('14', 31785, 3),
('14', 31786, 4),
('14', 31787, 5),
('14', 31788, 6),
('14', 31789, 7),
('14', 31790, 8),
('14', 31791, 9),
('14', 31792, 10),
('14', 31793, 11),
('14', 31794, 12),
('14', 31795, 13),
('14', 31796, 14),
('14', 31797, 15),
('14', 31798, 16),
('14', 31799, 17),
('14', 31800, 18),
('14', 31801, 19),
('14', 31802, 20),
('14', 31803, 21),
('14', 31804, 22),
('14', 31805, 23),
('14', 31806, 24),
('14', 31807, 25),
('14', 31808, 26),
('14', 31809, 27),
('14', 31810, 28),
('14', 31811, 29),
('14', 31812, 30),
('14', 31813, 31),
('14', 31814, 32),
('14', 31815, 33),
('14', 31816, 34),
('14', 31817, 35),
('14', 31818, 36),
('14', 31819, 37),
('14', 31820, 38),
('14', 31821, 39),
('14', 31822, 40),
('14', 31823, 41);
3) Then execute query
"SELECT IF(MAX(reg_number) <= 0, 1, MAX(reg_number) + 1) FROM in_docs WHERE reg_year='14-1'"
IT will return 42 but there are not row where reg_year="14-1" (in addition reg_year is char(2))

How to repeat:
See my description
[21 Nov 2014 13:56] MySQL Verification Team 
-> ('14', 31823, 41);
Query OK, 197 rows affected, 3 warnings (0.02 sec)
Records: 197  Duplicates: 0  Warnings: 3

mysql 5.6 > SELECT IF(MAX(reg_number) <= 0, 1, MAX(reg_number) + 1) FROM in_docs WHERE reg_year='14-1';
+--------------------------------------------------+
| IF(MAX(reg_number) <= 0, 1, MAX(reg_number) + 1) |
+--------------------------------------------------+
|                                               42 |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.6 > ALTER TABLE in_docs DROP INDEX `reg_year_and_number`;
Query OK, 197 rows affected (0.08 sec)
Records: 197  Duplicates: 0  Warnings: 0

mysql 5.6 > SELECT IF(MAX(reg_number) <= 0, 1, MAX(reg_number) + 1) FROM in_docs WHERE reg_year='14-1';
+--------------------------------------------------+
| IF(MAX(reg_number) <= 0, 1, MAX(reg_number) + 1) |
+--------------------------------------------------+
|                                             NULL |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.6 > SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+--------------------------------+
| Variable_name           | Value                          |
+-------------------------+--------------------------------+
| innodb_version          | 5.6.23                         |
| protocol_version        | 10                             |
| slave_type_conversions  |                                |
| version                 | 5.6.23                         |
| version_comment         | Source distribution revno 6246 |
| version_compile_machine | x86_64                         |
| version_compile_os      | Win64                          |
+-------------------------+--------------------------------+
7 rows in set (0.00 sec)

mysql 5.6 >
[21 Nov 2014 14:16] Armen Ghahramanyan 
But there are not any row where you can find '14-1'.
Is it equal with (SELECT IF(MAX(reg_number) <= 0, 1, MAX(reg_number) + 1) FROM in_docs WHERE false;) ?
[21 Nov 2014 14:17] MySQL Verification Team 
Thank you for the bug report.
[21 Nov 2014 14:20] Armen Ghahramanyan 
But I want to know is it bug or just my wrong usage?
Thank you.