List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:July 20 1999 5:42pm
Subject:Re: Duplicate simple SELECT results
View as plain text  
David:

This looks like a weird bug in the query optimizer. Monty will probably
write a patch for it as soon as he gets back from vacation. In the
meantime, try re-writing the query (you will need to experiment a bit,
maybe a different order of constraints in WHERE) in such a way that
mysqld will not execute the buggy portion of the query optimizer.

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)
> 
> ---------------
> 
> Per another suggestion, I tried changing the 'type' clause to be:
>       AND type IN (100600,100100)
> but the results are the same.
> 
> 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
> ---------------------------------------------
> David A. E. Wall
> Vice President Production
> ExperTrade Corporation
> 724 17th Avenue
> Kirkland, WA 98033 USA
> Tel 425.822.4465    dwall@stripped
> Fax 425.822.4465    www.expertrade.com
> 
> Here's the table dump for those that are interested:
> 
> # MySQL dump 6.0
> #
> # Host: localhost    Database: eastside
> #--------------------------------------------------------
> # Server version 3.22.25-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-19',100700,56);
> 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);

-- 
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
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