Bug #15308 Problem of Order with Enum Column in Primary Key
| Submitted: | 29 Nov 2005 10:59 | Modified: | 20 Jun 2010 0:43 |
|---|---|---|---|
| Reporter: | Didier G. | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.0.16/5.0.17 BK | OS: | Linux (Debian 2.6/Linux Suse) |
| Assigned to: | CPU Architecture: | Any |
[29 Nov 2005 14:35]
MySQL Verification Team
This bug not happend on 4.1.XX and only affects InnODB engine on 5.0.17.
miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.17-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database db7;
Query OK, 1 row affected (0.04 sec)
mysql> use db7
Database changed
mysql> CREATE TABLE `findrecord` (
-> `ind` enum('0','1','2') NOT NULL default '0',
-> `string1` varchar(250) NOT NULL,
-> PRIMARY KEY (`ind`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO `findrecord` VALUES ('1', '');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `findrecord` VALUES ('2', '');
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> SELECT * FROM `findrecord` ORDER BY string1;
ERROR 1032 (HY000): Can't find record in 'findrecord'
mysql>
mysql> ALTER TABLE `findrecord` ADD INDEX ( `string1` );
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM `findrecord` ORDER BY string1;
+-----+---------+
| ind | string1 |
+-----+---------+
| 1 | |
| 2 | |
+-----+---------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE `findrecord` ADD `string2` VARCHAR( 250 ) NOT NULL ;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM `findrecord` ORDER BY string2;
ERROR 1032 (HY000): Can't find record in 'findrecord'
mysql>
mysql> ALTER TABLE `findrecord` CHANGE `ind` `ind` TINYINT NOT NULL DEFAULT '0';
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT * FROM `findrecord` ORDER BY string2;
+-----+---------+---------+
| ind | string1 | string2 |
+-----+---------+---------+
| 2 | | |
| 3 | | |
+-----+---------+---------+
2 rows in set (0.01 sec)
miguel@hegel:~/dbs/5.0> bin/mysql -uroot db7
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.17-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `findrecord` (
-> `ind` enum('0','1','2') NOT NULL default '0',
-> `string1` varchar(250) NOT NULL,
-> PRIMARY KEY (`ind`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO `findrecord` VALUES ('1', '');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `findrecord` VALUES ('2', '');
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> SELECT * FROM `findrecord` ORDER BY string1;
+-----+---------+
| ind | string1 |
+-----+---------+
| 1 | |
| 2 | |
+-----+---------+
2 rows in set (0.00 sec)
miguel@hegel:~/dbs/4.1> bin/mysql -uroot db7
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.16-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `findrecord` (
-> `ind` enum('0','1','2') NOT NULL default '0',
-> `string1` varchar(250) NOT NULL,
-> PRIMARY KEY (`ind`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> INSERT INTO `findrecord` VALUES ('1', '');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `findrecord` VALUES ('2', '');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT * FROM `findrecord` ORDER BY string1;
+-----+---------+
| ind | string1 |
+-----+---------+
| 1 | |
| 2 | |
+-----+---------+
2 rows in set (0.00 sec)
[29 Nov 2005 14:58]
Didier G.
So do I have to come back to a 4.1 version ? or remain in 5.015? .... what are you planning to do?
[29 Nov 2005 15:15]
Didier G.
Is it a "ipod grade" bug?
[29 Nov 2005 16:20]
Heikki Tuuri
Hi! I will look at this in the next hour. Regards, Heikki
[29 Nov 2005 17:40]
Heikki Tuuri
Jan,
the reason for the bug is that in:
} else {
/* Here we handle all other data types except the
true VARCHAR, BLOB and TEXT. Note that the column
value we store may be also in a column prefix
index. */
CHARSET_INFO* cs;
ulint len;
const mysql_byte* src_start;
int error=0;
if (is_null) {
buff += key_part->length;
continue;
}
cs = key_part->field->charset();
src_start = record + key_part->offset;
if (key_part->length > 0 && cs->mbmaxlen > 1) {
len = (ulint) cs->cset->well_formed_len(cs,
(const char *) src_start,
(const char *) src_start + key_part->le\
ngth,
key_part->length / cs->mbmaxlen,
&error);
} else {
len = key_part->length;
}
memcpy(buff, src_start, len);
buff+=len;
/* Pad the unused space with spaces */
if (len < key_part->length) {
len = key_part->length - len;
memset(buff, ' ', len);
buff+=len;
}
}
you are accessing key_part->field->charset() even though the type may not be a string type! I checked that for an INT, charset->mb_maxlen == 1, while for an ENUM, charset->mb_maxlen == 3! That is, MySQL thinks the ENUM is a UTF-8 string though it really is stored in a binary format. But it might as well crash since charset is not a sensible attribute for an INT.
Fix: only if:
(mysql_type == MYSQL_TYPE_VAR_STRING
|| mysql_type == MYSQL_TYPE_STRING
|| mysql_type == MYSQL_TYPE_BIT)
&& !(field->real_type() == FIELD_TYPE_ENUM
|| field->real_type() == FIELD_TYPE_SET))
do access key_part->field->charset(). Otherwise, do not look at the charset! Check inside gdb that MYSQL_TYPE_BIT has a sensible charset. Check it separately in 4.1 and 5.0.
Regards,
Heikki
I have derived string types from:
ha_innodb.cc:
if (field->real_type() == FIELD_TYPE_ENUM
|| field->real_type() == FIELD_TYPE_SET) {
/* MySQL has field->type() a string type for these, but the
data is actually internally stored as an unsigned integer
code! */
*unsigned_flag = DATA_UNSIGNED; /* MySQL has its own unsigned
flag set to zero, even though
internally this is an unsigned
integer type */
return(DATA_INT);
}
switch (field->type()) {
/* NOTE that we only allow string types in DATA_MYSQL
and DATA_VARMYSQL */
case MYSQL_TYPE_VAR_STRING: /* old <= 4.1 VARCHAR */
case MYSQL_TYPE_VARCHAR: /* new >= 5.0.3 true VARCHAR */
if (field->binary()) {
return(DATA_BINARY);
} else if (strcmp(
field->charset()->name,
"latin1_swedish_ci") == 0) {
return(DATA_VARCHAR);
} else {
return(DATA_VARMYSQL);
}
case MYSQL_TYPE_BIT:
case MYSQL_TYPE_STRING: if (field->binary()) {
return(DATA_FIXBINARY);
} else if (strcmp(
field->charset()->name,
"latin1_swedish_ci") == 0) {
return(DATA_CHAR);
} else {
return(DATA_MYSQL);
}
case FIELD_TYPE_NEWDECIMAL:
return(DATA_FIXBINARY);
[29 Nov 2005 17:43]
Heikki Tuuri
Jan, this bug came from the bug fix of http://bugs.mysql.com/bug.php?id=14056 It is not yet in any published 4.1 version. Fix this quickly, so that it does not get into any 4.1 release! Regards, Heikki
[30 Nov 2005 6:29]
Jan Lindström
Heikki, bug #14056 is not fixed on 4.1. Should it be fixed there also ?
[30 Nov 2005 8:06]
Jan Lindström
Bit type seems to have correct charset information, below information from gdb:
Breakpoint 1, ha_innobase::store_key_val_for_row (this=0x8c72930, keynr=0,
buff=0x8c72b58 '¥' <repeats 16 times>, "test", buff_len=1, record=0x8c5a748 "") at ha_innodb.cc:2842
2842 KEY* key_info = table->key_info + keynr;
(gdb) next
2843 KEY_PART_INFO* key_part = key_info->key_part;
(gdb)
2844 KEY_PART_INFO* end = key_part + key_info->key_parts;
(gdb)
2845 char* buff_start = buff;
(gdb)
2852 DBUG_ENTER("store_key_val_for_row");
(gdb)
2879 bzero(buff, buff_len);
(gdb)
2881 for (; key_part != end; key_part++) {
(gdb)
2882 is_null = FALSE;
(gdb)
2884 if (key_part->null_bit) {
(gdb)
2895 field = key_part->field;
(gdb)
2896 mysql_type = field->type();
(gdb)
2898 if (mysql_type == MYSQL_TYPE_VARCHAR) {
(gdb)
2956 } else if (mysql_type == FIELD_TYPE_TINY_BLOB
(gdb)
3025 int error=0;
(gdb)
3027 if (is_null) {
(gdb)
3037 if (mysql_type == MYSQL_TYPE_VAR_STRING
(gdb)
3043 cs = key_part->field->charset();
(gdb)
3044 src_start = record + key_part->offset;
(gdb) p *cs
$1 = {number = 63, primary_number = 0, binary_number = 0, state = 817, csname = 0x851d053 "binary",
name = 0x851d053 "binary", comment = 0x851d05a "", tailoring = 0x0, ctype = 0x86353a0 "", to_lower = 0x86354c0 "",
to_upper = 0x86354c0 "", sort_order = 0x0, contractions = 0x0, sort_order_big = 0x0, tab_to_uni = 0x0,
tab_from_uni = 0x0, caseinfo = 0x86188a0,
state_map = 0x86501a0 "\022\001\001\001\001\001\001\001\001\032\032\032\032\032", '\001' <repeats 18 times>, "\032\b!\v\002\001\021\n\001\001\025\001\001\001\020\024\016\016\016\016\016\016\016\016\016\016\027\026\t\b\b\001\030\002\037", '\002' <repeats 11 times>, " \002\002\002\002\002\002\002\002\002\036\002\002\001\023\001\001\002\033\002\037", '\002' <repeats 11 times>, " \002\002\002\002\002\002\002\002\002\036\002\002\001\021", '\001' <repeats 75 times>...,
ident_map = 0x86502a0 "", strxfrm_multiply = 1, caseup_multiply = 1 '\001', casedn_multiply = 1 '\001',
mbminlen = 1, mbmaxlen = 1, min_sort_char = 0, max_sort_char = 255, pad_char = 0 '\0',
escape_with_backslash_is_dangerous = 0 '\0', cset = 0x8635640, coll = 0x8635600}
(gdb) p mysql_type
$2 = MYSQL_TYPE_BIT
Regards,
Jan
[30 Nov 2005 11:21]
Heikki Tuuri
Jan, good luck that this bug did not get into any 4.1 version. But http://bugs.mysql.com/bug.php?id=14056 should be fixed also in the 4.1 tree. Regards, Heikki
[1 Dec 2005 11:58]
Jan Lindström
Fix for 5.0 proposed.
[17 Jan 2006 16:18]
Alexander Ivanov
Fixed in 5.0.19.
[18 Jan 2006 0:11]
Mike Hillyer
Added note to 5.0.19 changelog:
<listitem>
<para>
Performing an <literal>ORDER BY</literal> on an
indexed <literal>ENUM</literal> column returned error. (Bug
#15308)
</para>
</listitem>
[5 May 2010 15:23]
Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 1:44]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 6:04]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:32]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 7:00]
Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 22:53]
Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[17 Jun 2010 12:08]
Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:54]
Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:35]
Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.075 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: Hi, Since version 5.0.16, some query on my database don't work.(But work great in version 5.0.15) Especially those with Enum in Primary Key. Query generate a error when a column in order clause is not indexed How to repeat: -- -- Structure of table `findrecord` -- CREATE TABLE `findrecord` ( `ind` enum('0','1','2') NOT NULL default '0', `string1` varchar(250) NOT NULL, PRIMARY KEY (`ind`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Insert data in table `findrecord` -- INSERT INTO `findrecord` VALUES ('1', ''); INSERT INTO `findrecord` VALUES ('2', ''); -- -- ! Can't order by `string1` on table `findrecord` ! -- SELECT * FROM `findrecord` ORDER BY string1; mysql> ERROR 1032 (HY000): Can't find record in 'findrecord' -------------------------------------------------------------------- -- 1) Put an index on the column -------------------------------------------------------------------- ALTER TABLE `findrecord` ADD INDEX ( `string1` ); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 -- -- Now, i can order by `string1` on table `findrecord` -- SELECT * FROM `findrecord` ORDER BY string1; +-----+---------+ | ind | string1| +-----+---------+ | 1 | | | 2 | | +-----+---------+ 2 rows in set (0.00 sec) -------------------------------------------------------------------- -- Now, I add a column `string2` on table `findrecord` -------------------------------------------------------------------- ALTER TABLE `findrecord` ADD `string2` VARCHAR( 250 ) NOT NULL ; -- -- ! Can't order by `string2` on table `findrecord` ! -- SELECT * FROM `findrecord` ORDER BY string2; mysql> ERROR 1032 (HY000): Can't find record in 'findrecord' -------------------------------------------------------------------- -- 2) Modify type of column `ind` on table `findrecord` -------------------------------------------------------------------- ALTER TABLE `findrecord` CHANGE `ind` `ind` TINYINT NOT NULL DEFAULT '0'; -- -- Now, i can order by `string2` on table `findrecord` -- SELECT * FROM `findrecord` ORDER BY string2; +-----+---------+ | ind | string1| +-----+---------+ | 2 | | | 3 | | +-----+---------+ 2 rows in set (0.00 sec)