[thelist] MySQL GROUP BY Anomaly? SOLVED

Jay Blanchard jay.blanchard at niicommunications.com
Thu May 9 14:17:01 CDT 2002


[snip]
it suggests that the times are not all zero
[/snip]

I had a look at them though, and they were. It was weird, I was getting
correct results sometimes, and incorrect others.

[snip]
> Had a look at the table description (DESCRIBE tablename;)
> for the merge table. There was an INDEX set on the RecordDate
> column. ... Removed the INDEX from the MERGE table definition
> and all appears to be working well.
jay, that makes no sense

how could an index (which is, after all, just a set of pointers to the
data) affect whether or not SELECT DISTINCT gives you multiple values?!
[/snip]

It probably has to do with a MERGE (which is a pointer) with an INDEX (as a
pointer) refering to other INDEXED fields.

[snip]
sounds a lot like a bug in MERGE tables
[/snip]

I'll let you know when MySQL Ab responds, but I think so too.

[snip]
would have asked you to run a quick query to do a COUNT() instead of
DISTINCT, and GROUP BY a date function, as joshua suggested
[/snip]

What was the big snapper here is that this was on the crosstab where I was
already GROUPing BY the date field, which worked as long as the entire range
of records were selected, or if one record date was the criteria. Just a
blip when a range of dates were selected, like WHERE RecordDate BETWEEN
'this' AND 'that'. As soon as I removed the INDEX from the MERGE definition
all was (or appears to be) well.

Thanks!

Jay





More information about the thelist mailing list