Bug #49205 Cardinality NULL of index over UTF-8 collation field

• Submitted: 30 Nov 2009 11:53
• Modified: 1 Jul 2010 9:32
• Reporter: Martin Hieden
• Status: Verified
• Category: MySQL Server: DB2SE for IBM i
• Severity: S2 (Serious)
• Version: 5.1.39
• OS: IBM i
• CPU Architecture: Any
• Tags: cardinality, utf8_general_ci

[30 Nov 2009 11:53] Martin Hieden

Description:
Creating an index over a utf-8 collation field (and therefore GRAPHIC field in DB2) the index is always shown with cardinality NULL, independant of the actual table content.

If you look at the cardinality using iSeries Navigator, it is shown correctly (both, the MySQL and the DB2 index).

How to repeat:
CREATE DATABASE `coll` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE `coll`.`test_tbl` (
`test_fld` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL 
)
ENGINE = IBMDB2I CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE `coll`.`test_tbl` ADD INDEX `test_idx` ( `test_fld` );

INSERT INTO `coll`.`test_tbl` (
`test_fld` 
)
VALUES (
'abc'
), (
'xyz'
);

show index from coll.test_tbl;

[30 Nov 2009 11:54] Martin Hieden

forgot to mention the version

[15 Dec 2009 15:48] Tim Clark

This is a bug. However, to correctly reproduce the bug, an ANALYZE TABLE statement must be run prior to the SHOW INDEX statement. As described above, the behavior (reporting NULL cardinality) is technically correct (and is identical to MyISAM's.)

[15 Dec 2009 16:20] Tim Clark

I believe that the source of this bug lies in the operating system support code. Consequently, an IBM i PTF will be required to fix the behavior and obtain correct cardinality statistics for unicode columns. I will update this bug report with information about the PTF when it becomes available.

[29 Apr 2010 13:12] Martin Hieden

Any news on a PTF?

[24 Jun 2010 16:23] Tim Clark

This has been fixed with a PTF for IBM i.

v5r4m0
SI40257

6.1
SI40255

7.1
SI40254

[1 Jul 2010 9:32] Martin Hieden

After installing the mentioned PTF, cardinality returned is not NULL anymore, but still not correct.

If you add a third record with 'abc' as value to the example given above, cardinality is returned as 3 instead of 2.