Bug #53971 Cardinality NULL of index over date field
Submitted: 25 May 2010 12:34 Modified: 1 Jul 2010 9:30
Reporter: Martin Hieden Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DB2SE for IBM i Severity:S3 (Non-critical)
Version:5.1.39 OS:IBM i (V6R1)
Assigned to: Assigned Account CPU Architecture:Any
Tags: cardinality, date

[25 May 2010 12:34] Martin Hieden 
Description:
Creating an index over a date field (and therefore DATE 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).

How to repeat:
CREATE DATABASE `date`;

CREATE TABLE `date`.`test_tbl` (
`test_fld` DATE NOT NULL 
)
ENGINE = IBMDB2I CHARACTER SET utf8 COLLATE utf8_general_ci;

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

INSERT INTO `date`.`test_tbl` (
`test_fld` 
)
VALUES (
'2010-01-01'
), (
'2010-01-01'
), (
'2010-05-25'
);

ANALYZE TABLE date.test_tbl;

SHOW INDEX FROM date.test_tbl;

Suggested fix:
Seems to correspond to Bug #49205.
[26 May 2010 14:51] Kyle Joiner 
verified as described
[24 Jun 2010 16:24] Tim Clark 
This has been fixed with a PTF for IBM i.

v5r4m0
SI40257

6.1
SI40255

7.1
SI40254
[1 Jul 2010 9:30] Martin Hieden 
After installing the mentioned PTF, cardinality returned is not NULL anymore, but still not correct. For the example given above, cardinality is returned as 3 instead of 2.