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:
None 
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 10:59] Didier G.
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)
[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)