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)
