Bug #872 InnoDB subquery returns no results under special conditions

• Submitted: 18 Jul 2003 9:53
• Modified: 21 Jul 2003 6:23
• Reporter: Ronald Jeremy
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 4.1.1
• OS: Linux (redhat linux 8)
• CPU Architecture: Any

[18 Jul 2003 9:53] Ronald Jeremy

Description:
The following conditions lead to no results being incorrectly returned from a query:

--Tables are InnoDB (myisam works correctly)
--An index is on one of the joined fields (dropping index returns correcct results.)
--A single column in the 'WHERE' of a subquery is also used twice in the outer query.  In below example, if any one of the three 'takentime' columns is replaced by 'placedtime' the query returns correct results.

The results of the query below should return:
customerid  cnt   
----------  ------
1           1     
2           1     
but instead return no results.

How to repeat:
CREATE TABLE cust (
customerid int(1) PRIMARY KEY
)TYPE=innodb;
CREATE TABLE orders (
  orderid int(1) PRIMARY KEY,
  customerid int(1),
  takentime TIMESTAMP(+0),
  placedtime TIMESTAMP(+0),
  INDEX cust_fk (customerid)
)TYPE=innodb;
INSERT INTO cust (customerid) values (1);
INSERT INTO cust (customerid) values (2);
INSERT INTO orders (orderid,customerid,takentime,placedtime) values (1,1,'20030718112400','20030718122400');
INSERT INTO orders (orderid,customerid,takentime,placedtime) values (2,2,'20030718112400','20030718122400');

SELECT DISTINCT O.customerid, COUNT(DISTINCT O.orderid) AS cnt FROM (
   SELECT DISTINCT C2.customerid
   FROM cust C2 INNER JOIN orders O2 ON C2.customerid = O2.customerid
   WHERE O2.takentime < '20030818112400'
) tmp, orders O
WHERE O.customerid = tmp.customerid AND O.takentime < '20030918112400' AND O.takentime > '20030518112400'
GROUP BY O.customerid;

[21 Jul 2003 6:23] Heikki Tuuri

Hi!

I tested this in my local MySQL/InnoDB-4.1.1 development tree.

I got the right result with both MyISAM and InnoDB tables.

Regards,

Heikki

heikki@hundin:~/mysql-4.1/client> mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.1-alpha-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE cust (
    -> customerid int(1) PRIMARY KEY
    -> )TYPE=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE orders (
    ->   orderid int(1) PRIMARY KEY,
    ->   customerid int(1),
    ->   takentime TIMESTAMP(+0),
    ->   placedtime TIMESTAMP(+0),
    ->   INDEX cust_fk (customerid)
    -> )TYPE=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO cust (customerid) values (1);
Query OK, 1 row affected (3.74 sec)

mysql> INSERT INTO cust (customerid) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO orders (orderid,customerid,takentime,placedtime) values
    -> (1,1,'20030718112400','20030718122400');
Query OK, 1 row affected (0.92 sec)

mysql> INSERT INTO orders (orderid,customerid,takentime,placedtime) values
    -> (2,2,'20030718112400','20030718122400');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT DISTINCT O.customerid, COUNT(DISTINCT O.orderid) AS cnt FROM (
    ->    SELECT DISTINCT C2.customerid
    ->    FROM cust C2 INNER JOIN orders O2 ON C2.customerid = O2.customerid
    ->    WHERE O2.takentime < '20030818112400'
    -> ) tmp, orders O
    -> WHERE O.customerid = tmp.customerid AND O.takentime < '20030918112400' AN
D
    -> O.takentime > '20030518112400'
    -> GROUP BY O.customerid;
+------------+-----+
| customerid | cnt |
+------------+-----+
|          1 |   1 |
|          2 |   1 |
+------------+-----+
2 rows in set (53.79 sec)

mysql>