Inconsistent COUNT with DISTINCT

Question

I have partitioned a table by the field daynumber. During the day I write into this table logs and then calculate some stats. The table is big; each day I have ~3M new rows. The field myField is indexed.

This query

SELECT COUNT(DISTINCT myField) FROM mytable WHERE daynumber=somevalue; 

returns 0, which is a mistake.

This query

SELECT COUNT(*) FROM (SELECT DISTINCT(myField) FROM mytable WHERE daynumber=somevalue) t;

returns the correct value.

For some daynumber values, the first query works fine. I tried to repeir that partition but that had no effect. Any suggestions?

---

update

table scheme looks like

CREATE TABLE `mytable` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `daynumber` INT(10) UNSIGNED NOT NULL,
  `myField` VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    ... other fields
  PRIMARY KEY (`daynumber`,`id`),
  KEY `myField` (`myField`(20))
) ENGINE=MYISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
PARTITION BY LIST (daynumber)
(PARTITION day_810 VALUES IN (810) ENGINE = MyISAM,
 PARTITION day_811 VALUES IN (811) ENGINE = MyISAM,
 PARTITION day_812 VALUES IN (812) ENGINE = MyISAM
....)

Answer 1

this is because your myfield contains NULL value

SELECT COUNT(DISTINCT coalesce(myField, '') )
FROM mytable WHERE daynumber=somevalue; 

coalesce is sort of convert NULL to ''
might not what your ask for, but will return the correct count (still)