MySQL Bugs: #12672: Select involving 3 times the same InnoDB table causes next subquery work wrong

Bug #12672	Select involving 3 times the same InnoDB table causes next subquery work wrong
Submitted:	19 Aug 2005 12:00	Modified:	20 Jun 2010 22:40
Reporter:	Alain Knaff	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.11/BK source	OS:	Linux (Linux/Windows)
Assigned to:		CPU Architecture:	Any

Description:
When performing a select involving 3 times the same table (either directly in the FROM clause or in subselects), indexes on that table are corrupted in such a way that SELECT DISTINCT of an indexed column from that table with a subselect returns bad results.

How to repeat:
Execute the following SQL statements on a new database:

CREATE TABLE t1 (id1 int, v1 int,
	PRIMARY KEY (id1), INDEX idx (v1)) ENGINE = INNODB;
CREATE TABLE t2 (id2 int) ENGINE = INNODB;

INSERT  INTO t1(id1,v1) VALUES (1,11),(2,11),(3,12),(4,12);
INSERT  INTO t2(id2) VALUES (4);

-- The select below will trigger the bug. Commenting it out makes the test
-- case succeed
SELECT 1 FROM t1 t11,t1 t12,t1 t13 WHERE 1=0;

-- First select: should show 12, but does not
select distinct v1 from t1 where id1 in (select id2 from t2);

-- Second select: we just add a bogus constant column, and presto, it
-- works as it should!
select distinct v1,1 from t1 where id1 in (select id2 from t2);

What it should show is:
v1
12
v1      1
12      1

What it does show is
v1      1
12      1

==> i.e. the first select distinct returns an erroneous result. The second one shows the correct result (probably because the constant 1 confuses the optimizer enough that it doesn't notice that it can use the faulty index)

Example sql commands to reproduce the bug

Attachment: bugexample.sql (text/x-objcsrc), 629 bytes.

Here is one example which selects only twice from the same table, and produces the bug

SELECT 1 FROM t1 UNION ALL SELECT 1 FROM t1;

Thank you for the bug report.

This only affects InnoDB engine and 5.0.XX server.
MyISAM -> 5.0.XX and version 4.1.XX -> InnoDB aren't
affected.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.12-beta-debug

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

mysql> create database t1;
Query OK, 1 row affected (0.04 sec)

mysql> use t1;
Database changed
mysql> CREATE TABLE t1 (id1 int, v1 int,
    -> PRIMARY KEY (id1), INDEX idx (v1)) ENGINE = INNODB;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t2 (id2 int) ENGINE = INNODB;
Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> INSERT  INTO t1(id1,v1) VALUES (1,11),(2,11),(3,12),(4,12);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT  INTO t2(id2) VALUES (4);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT 1 FROM t1 t11,t1 t12,t1 t13 WHERE 1=0;
Empty set (0.00 sec)

mysql> select distinct v1 from t1 where id1 in (select id2 from t2);
Empty set (0.00 sec)

mysql> select distinct v1,1 from t1 where id1 in (select id2 from t2);
+------+---+
| v1   | 1 |
+------+---+
|   12 | 1 |
+------+---+
1 row in set (0.01 sec)

mysql> create database t2;
Query OK, 1 row affected (0.03 sec)

mysql> use t2;
Database changed
mysql> CREATE TABLE t1 (id1 int, v1 int,
    -> PRIMARY KEY (id1), INDEX idx (v1)) engine = MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t2 (id2 int) ENGINE = MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT  INTO t1(id1,v1) VALUES (1,11),(2,11),(3,12),(4,12);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT  INTO t2(id2) VALUES (4);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT 1 FROM t1 t11,t1 t12,t1 t13 WHERE 1=0;
Empty set (0.00 sec)

mysql> select distinct v1 from t1 where id1 in (select id2 from t2);
+------+
| v1   |
+------+
|   12 |
+------+
1 row in set (0.01 sec)

mysql> 

miguel@hegel:~/dbs/4.1> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.14-debug-log

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

mysql> create database t1;
Query OK, 1 row affected (0.02 sec)

mysql> use t1;
Database changed
mysql> CREATE TABLE t1 (id1 int, v1 int,
    -> PRIMARY KEY (id1), INDEX idx (v1)) ENGINE = INNODB;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t2 (id2 int) ENGINE = INNODB;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT  INTO t1(id1,v1) VALUES (1,11),(2,11),(3,12),(4,12);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT  INTO t2(id2) VALUES (4);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT 1 FROM t1 t11,t1 t12,t1 t13 WHERE 1=0;
Empty set (0.00 sec)

mysql> select distinct v1 from t1 where id1 in (select id2 from t2);
+------+
| v1   |
+------+
|   12 |
+------+
1 row in set (0.01 sec)

mysql>

Hi!

This is probably a bug in the MySQL optimizer. Running that dummy statement:

SELECT 1 FROM t1 t11,t1 t12,t1 t13 WHERE 1=0;

might leave the table handle in an inconsistent state, which would then confuse the optimizer.

I stepped through the last SELECT in these statements:

CREATE TABLE t1 (id1 int, v1 int,
    PRIMARY KEY (id1), INDEX idx (v1)) ENGINE = INNODB;
CREATE TABLE t2 (id2 int) ENGINE = INNODB;
INSERT  INTO t1(id1,v1) VALUES (1,11),(2,11),(3,12),(4,12);
INSERT  INTO t2(id2) VALUES (4);
SELECT 1 FROM t1 t11,t1 t12,t1 t13 WHERE 1=0; # dummy select statement
select distinct v1 from t1 where id1 in (select id2 from t2);

This gives a wrong result. The wrong execution:

1. MySQL retrieves the last row from t1 on the index on v1. InnoDB returns (4, 12).
2. MySQL retrieves the first row from t1 on the index on v1. InnoDB returns (1, 11).
3. MySQL retrieves the first row in table t2. InnoDB returns (4).
4. MySQL retrieves the next row from t2. InnoDB returns no rows.
5. MySQL retrieves rows from t1 on the index on v1 where v1 > 11. InnoDB returns (3, 12).
6. MySQL retrieves the first row from t2. InnoDB returns (4).
7. MySQL retrieves rows from t1 on the index on v1 where v1 > 12. InnoDB returns no rows.

=> MySQL returns 'Empty set'.

InnoDB did return correct answers to MySQL's calls.

During the wrong execution, MySQL also asks from InnoDB ::info(HA_STATUS_VARIABLE) from table t1. InnoDB estimates the row count at 4 (correct value). Also from table t2. InnoDB estimates 1 (correct value).
MySQL does not call ::records_in_range().

The above execution looks very strange! I do not see the logic how it could calculate the value for select distinct v1 from t1 where id1 in (select id2 from t2).

When I removed that dummy select statement, the execution of the last select started like this:

1. MySQL retrieves the first row of t1 on the index on v1. InnoDB returns(1, 11).
2. MySQL retrieves the first row from t2. InnoDB returns (4).
...

Very different right from the start.

I am changing the category of this bug report to 'Optimizer'.

Regards,

Heikki

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28976

The fix was pushed into 5.0.12.

Noted in 5.0.12 changelog.

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)