On Tue, 1999-07-20 10:20:29 -0700, David Wall wrote:
> I've sent this out twice, with no response from anyone on the list or TCX.
> Do people agree that this is a bug?
Yes, you definitely were right and it is a bug in MySQL!
In the announcment for V3.23.2, Monty writes:
| Changes in release 3.23.2
| * Fixed range optimizer bug: `SELECT * FROM table_name WHERE
| key_part1 >= const AND (key_part2 = const OR key_part2 = const)'.
| The bug was that some rows could be duplicated in the result.
> But, if make it select on two different types, I get odd looking
>results with repeating results, and if I add another type=n in the
>query, I'll get three results, etc.:
> mysql> select event_date,type,event_id from EventDateTbl
> WHERE event_date >= "1999-07-01" AND event_date < "1999-07-15"
> AND (type=100600 OR type=100100) ORDER BY event_date;
If you can't wait for an update and _need_ to get this query working now,
I found a work-around for this bug:
CONCAT(event_date) AS sortkey
event_date >= "1999-07-01"
AND event_date < "1999-07-15"
AND (type=100600 OR type=100100)
The trick is to make the field used as sorting key a calculated field.
Now the sorting is okay, and using DISTINCT gets rid of the wrong
If your application doesn't depend on the column name "event_date",
you also could merge "event_date" and "sortkey" into one field again.
So my favourite query looks like that:
CONCAT(event_date) AS ev_date, type,event_id
event_date BETWEEN '1999-07-01' AND '1999-07-14'
AND type IN (100100, 100600)
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26 E4 EC 80 58 7B 31 3A D7