List:General Discussion« Previous MessageNext Message »
From:David Wall Date:July 17 1999 2:08am
Subject:Re: Duplicate rows on simple query on simple table
View as plain text  
I tried that, but with it saying select event_date,type,count(type) and it
still shows two sets of results.  They are duplicates.  And if I change the
IN clause to have three numbers, the results would show up three times --
not just duplicates.  At first, I figured there must be something wrong with
the GROUP BY clause or the like, but that doesn't seem true either, since
the following query will return a result set with 3 correct sets from the
query -- but who wants 3 answers <smile!>

select event_date,type,event_id from EventDateTbl where event_date >=
"1999-07-01" and event_date < "1999-08-01" and type in
(100600,101000,100800) order by event_date;

Even if the select part were wrong (which it is not, I don't think), the
order by clause should have required that all of the entries with the same
event_date be in a series, but instead it goes from the 1st to the 31st of
July, then does that exact list again, then does that exact list again.  It
doesn't put three of the 7/1s, followed by 3 of the 7/2s,... to three 7/31s.

David

----- Original Message -----
From: John Steele <jsteele@stripped>
To: David Wall <dwall@stripped>
Sent: Friday, July 16, 1999 9:17 AM
Subject: Re: Duplicate rows on simple query on simple table


> Try this:
>
> select event_date,type,event_id from EventDateTbl
> WHERE event_date >= "1999-07-01" AND event_date < "1999-07-15"
> AND type IN (100600,100100) GROUP BY event_date,type
>
> Replace the event_id with COUNT(type) for your original need.
>
> John
>
> At 7/17/99 12:08 AM, David Wall wrote:
> >I have a bug on a rather simple table not a particularly tricky query
that
> >is returning duplicate rows.  It occurs under Linux (Redhat 5.2) using
> >version 3.22.21 and also under Win98 using version 3.22.23b.
> >
> >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 repeats:
> >
> >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 |
> >| 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 |
> >| 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)
> >
> >This is odd to me because I have an ORDER BY that should put everything
in
> >date order, but that's not the case.  I first noted this with a slightly
> >more complex query (the one I really want to work, but I couldn't even
get
> >the trivial one to work).
> >
> >mysql> select event_date,type,COUNT(type) from EventDateTbl
> >WHERE event_date >= "1999-07-01" AND event_date < "1999-07-15"
> >AND (type=100600 OR type=100100) GROUP BY event_date,type;
> >+------------+--------+-------------+
> >| event_date | type   | COUNT(type) |
> >+------------+--------+-------------+
> >| 1999-07-10 | 100100 |           1 |
> >| 1999-07-11 | 100100 |           1 |
> >| 1999-07-13 | 100600 |           3 |
> >| 1999-07-14 | 100600 |           1 |
> >| 1999-07-10 | 100100 |           1 |
> >| 1999-07-11 | 100100 |           1 |
> >| 1999-07-13 | 100600 |           3 |
> >| 1999-07-14 | 100600 |           1 |
> >+------------+--------+-------------+
> >8 rows in set (0.08 sec)
> >
> >When I specify NO "type=nnn" in the WHERE CLAUSE, or when I have just ONE
> >"AND type=nnn" it works fine, but if I put two types in, separated by
ORs, I
> >get the odd results.
> >
> >What am I doing wrong?
> >
> >Thanks,
> >David Wall
> >
> >
> >For those that care, here's an mysqldump of that table...
> >
> ># MySQL dump 5.13
> >#
> ># Host: localhost    Database: eastside
> >#--------------------------------------------------------
> ># Server version 3.22.21-log
> >
> >#
> ># Table structure for table 'EventDateTbl'
> >#
> >CREATE TABLE EventDateTbl (
> >  event_date date DEFAULT '0000-00-00' NOT NULL,
> >  type int(11) DEFAULT '0' NOT NULL,
> >  event_id int(11) DEFAULT '0' NOT NULL,
> >  PRIMARY KEY (event_date,type,event_id),
> >  KEY event_id (event_id)
> >);
> >
> >#
> ># Dumping data for table 'EventDateTbl'
> >#
> >
> >INSERT INTO EventDateTbl VALUES ('1999-07-10',100100,24);
> >INSERT INTO EventDateTbl VALUES ('1999-07-11',100100,25);
> >INSERT INTO EventDateTbl VALUES ('1999-07-13',100600,0);
> >INSERT INTO EventDateTbl VALUES ('1999-07-13',100600,4);
> >INSERT INTO EventDateTbl VALUES ('1999-07-13',100600,26);
> >INSERT INTO EventDateTbl VALUES ('1999-07-14',100600,10);
> >INSERT INTO EventDateTbl VALUES ('1999-07-15',100600,16);
> >INSERT INTO EventDateTbl VALUES ('1999-07-15',100800,45);
> >INSERT INTO EventDateTbl VALUES ('1999-07-15',101000,47);
> >INSERT INTO EventDateTbl VALUES ('1999-07-16',100800,46);
> >INSERT INTO EventDateTbl VALUES ('1999-07-20',100600,5);
> >INSERT INTO EventDateTbl VALUES ('1999-07-20',100600,27);
> >INSERT INTO EventDateTbl VALUES ('1999-07-21',100600,11);
> >INSERT INTO EventDateTbl VALUES ('1999-07-22',100600,17);
> >INSERT INTO EventDateTbl VALUES ('1999-07-23',100100,39);
> >INSERT INTO EventDateTbl VALUES ('1999-07-24',100100,39);
> >INSERT INTO EventDateTbl VALUES ('1999-07-24',100500,40);
> >INSERT INTO EventDateTbl VALUES ('1999-07-25',100100,39);
> >INSERT INTO EventDateTbl VALUES ('1999-07-27',100600,1);
> >INSERT INTO EventDateTbl VALUES ('1999-07-27',100600,6);
> >INSERT INTO EventDateTbl VALUES ('1999-07-27',100600,28);
> >INSERT INTO EventDateTbl VALUES ('1999-07-28',100600,12);
> >INSERT INTO EventDateTbl VALUES ('1999-07-29',100500,41);
> >INSERT INTO EventDateTbl VALUES ('1999-07-29',100600,18);
> >INSERT INTO EventDateTbl VALUES ('1999-07-30',100500,41);
> >INSERT INTO EventDateTbl VALUES ('1999-07-31',100500,41);
> >INSERT INTO EventDateTbl VALUES ('1999-08-01',100700,34);
> >INSERT INTO EventDateTbl VALUES ('1999-08-03',100600,7);
> >INSERT INTO EventDateTbl VALUES ('1999-08-03',100600,29);
> >INSERT INTO EventDateTbl VALUES ('1999-08-04',100600,13);
> >INSERT INTO EventDateTbl VALUES ('1999-08-05',100500,42);
> >INSERT INTO EventDateTbl VALUES ('1999-08-05',100600,19);
> >INSERT INTO EventDateTbl VALUES ('1999-08-06',100500,42);
> >INSERT INTO EventDateTbl VALUES ('1999-08-07',100500,42);
> >INSERT INTO EventDateTbl VALUES ('1999-08-08',100500,42);
> >INSERT INTO EventDateTbl VALUES ('1999-08-10',100600,2);
> >INSERT INTO EventDateTbl VALUES ('1999-08-10',100600,9);
> >INSERT INTO EventDateTbl VALUES ('1999-08-10',100600,30);
> >INSERT INTO EventDateTbl VALUES ('1999-08-11',100600,14);
> >INSERT INTO EventDateTbl VALUES ('1999-08-12',100600,20);
> >INSERT INTO EventDateTbl VALUES ('1999-08-17',100500,8);
> >INSERT INTO EventDateTbl VALUES ('1999-08-17',100600,31);
> >INSERT INTO EventDateTbl VALUES ('1999-08-18',100600,15);
> >INSERT INTO EventDateTbl VALUES ('1999-08-19',100600,22);
> >INSERT INTO EventDateTbl VALUES ('1999-08-24',100600,3);
> >INSERT INTO EventDateTbl VALUES ('1999-08-24',100600,32);
> >INSERT INTO EventDateTbl VALUES ('1999-08-27',100500,43);
> >INSERT INTO EventDateTbl VALUES ('1999-08-31',100600,33);
> >INSERT INTO EventDateTbl VALUES ('1999-09-17',100100,37);
> >INSERT INTO EventDateTbl VALUES ('1999-09-18',100100,37);
> >INSERT INTO EventDateTbl VALUES ('1999-09-19',100100,37);
> >INSERT INTO EventDateTbl VALUES ('2000-12-18',100700,38);
> >
>
> --
> /* SteeleSoft Consulting    John Steele - Systems Analyst/Programmer
>  *  We also walk dogs...    jsteele@stripped
>  */
>
>

Thread
Duplicate rows on simple query on simple tableDavid Wall17 Jul
Re: Duplicate rows on simple query on simple tableDavid Wall17 Jul