List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 11 1999 6:10am
Subject:Re: Duplicate simple SELECT results
View as plain text  
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:

  SELECT DISTINCT
        event_date, type,event_id,
        CONCAT(event_date) AS sortkey
  FROM
        EventDateTbl
  WHERE
        event_date >= "1999-07-01"
    AND event_date < "1999-07-15"
    AND (type=100600 OR type=100100)
  ORDER BY
        sortkey;

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
duplicates.

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:

  SELECT DISTINCT
        CONCAT(event_date) AS ev_date, type,event_id
  FROM
        EventDateTbl
  WHERE
        event_date BETWEEN '1999-07-01' AND '1999-07-14'
    AND type IN (100100, 100600)
  ORDER BY
        ev_date;

Regards,
  Martin
-- 
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
Thread
Duplicate simple SELECT resultsDavid Wall20 Jul
  • Re: Duplicate simple SELECT resultsSasha Pachev20 Jul
  • Re: Duplicate simple SELECT resultsJim Faucette20 Jul
  • Re: Duplicate simple SELECT resultsDavid Wall20 Jul
  • Duplicate simple SELECT resultssinisa20 Jul
  • Re: Duplicate simple SELECT resultsDavid Wall21 Jul
  • Re: Duplicate simple SELECT resultsMartin Ramsch11 Aug
    • Re: Duplicate simple SELECT resultsMichael Widenius12 Aug
Re: Error Code 28?Nils Valentin12 Aug
Re: Error Code 28?Nils Valentin12 Aug