List:General Discussion« Previous MessageNext Message »
From:Jim Faucette Date:July 20 1999 5:46pm
Subject:Re: Duplicate simple SELECT results
View as plain text  
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)
>
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