If you add a 3rd type to the query does it return triple results?
I haven't need to use a multiple Primary Key, so I wonder if that's part
of the problem. Would you be interested in removing the Primary Key and
recreate it as a UNIQUE?
jim...
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? It sure looks like a nasty one, considering the
> fact that it's basically a simple query on a single table that returns the wrong results.
> But then, I'm not an SQL guru either, though this looks too simple to be a guru-sized
> bug!
>
> The bug occurs on my RedHat Linux 5.2 Intel box running mysql 3.22.25, as well as my
> Win98 PC running 3.22.23b.
>
> I've attached a mysqldump of the table for those that may want to try it themselves.
>
> (The tables shown here will look very nice if you can use a fixed with font like
> Courier).
>
> Thanks,
> David
>
> ----- Original Message -----
> From: David Wall
> To: mysql-support@stripped
> Cc: David A.E. Wall
> Sent: Wednesday, July 14, 1999 12:25 PM
> Subject: Odd table behavior
>
> I have a simple table, EventDateTbl that contains the following columns:
>
> mysql> describe EventDateTbl;
> +------------+---------+------+-----+------------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +------------+---------+------+-----+------------+-------+
> | event_date | date | | PRI | 0000-00-00 | |
> | type | int(11) | | PRI | 0 | |
> | event_id | int(11) | | PRI | 0 | |
> +------------+---------+------+-----+------------+-------+
> 3 rows in set (0.00 sec)
>
> ---------------
>
> A simple date range select seems okay:
>
> mysql> select event_date,type,event_id from EventDateTbl
> WHERE event_date >= "1999-07-01" AND event_date < "1999-07-15" ORDER BY
> event_date;
> +------------+--------+----------+
> | event_date | type | event_id |
> +------------+--------+----------+
> | 1999-07-10 | 100100 | 24 |
> | 1999-07-11 | 100100 | 25 |
> | 1999-07-13 | 100600 | 0 |
> | 1999-07-13 | 100600 | 4 |
> | 1999-07-13 | 100600 | 26 |
> | 1999-07-14 | 100600 | 10 |
> +------------+--------+----------+
> 6 rows in set (0.00 sec)
>
> ---------------
>
> And, if I add a single type to the WHERE, it also seems to work:
>
> mysql> select event_date,type,event_id from EventDateTbl
> WHERE event_date >= "1999-07-01" AND event_date < "1999-07-15"
> AND (type=100600) ORDER BY event_date;
> +------------+--------+----------+
> | event_date | type | event_id |
> +------------+--------+----------+
> | 1999-07-13 | 100600 | 0 |
> | 1999-07-13 | 100600 | 4 |
> | 1999-07-13 | 100600 | 26 |
> | 1999-07-14 | 100600 | 10 |
> +------------+--------+----------+
> 4 rows in set (0.01 sec)
>
> ---------------
>
> 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;
> +------------+--------+----------+
> | event_date | type | event_id |
> +------------+--------+----------+
> | 1999-07-10 | 100100 | 24 | <-- July 10th
> | 1999-07-11 | 100100 | 25 |
> | 1999-07-13 | 100600 | 0 |
> | 1999-07-13 | 100600 | 4 |
> | 1999-07-13 | 100600 | 26 |
> | 1999-07-14 | 100600 | 10 |
> | 1999-07-10 | 100100 | 24 | <-- July 10th DUP, despite order by Date
> | 1999-07-11 | 100100 | 25 |
> | 1999-07-13 | 100600 | 0 |
> | 1999-07-13 | 100600 | 4 |
> | 1999-07-13 | 100600 | 26 |
> | 1999-07-14 | 100600 | 10 |
> +------------+--------+----------+
> 12 rows in set (0.01 sec)
>
