MySQL Bugs: #13263: EXISTS subquery optimization depends on columns SELECTed

Bug #13263	EXISTS subquery optimization depends on columns SELECTed
Submitted:	16 Sep 2005 12:45	Modified:	19 Mar 2008 11:41
Reporter:	Matthias Pigulla	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi all,

the manual says:

"If a subquery returns any rows at all, then EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE. ... Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it doesn't matter." 

http://dev.mysql.com/doc/mysql/en/exists-and-not-exists-subqueries.html

However, please consider the following query below and note that there's an index on file.document_id. When changing the "SELECT id" in the subquery to "SELECT document_id" or "SELECT 1", the subquery is executed *using index*. 

It should always be that way :).

How to repeat:
CREATE TABLE document (
  id smallint(5) unsigned NOT NULL auto_increment,
  name varchar(250) NOT NULL default '',
  PRIMARY KEY  (id)
);

CREATE TABLE file (
  id smallint(5) unsigned NOT NULL auto_increment,
  document_id smallint(5) unsigned NOT NULL default '0',
  file longblob,
  PRIMARY KEY  (id),
  KEY document_id (document_id)
);

INSERT INTO document VALUES(1, 'test');
INSERT INTO document VALUES(2, 'test 2');
INSERT INTO document VALUES(3, 'test 3');
INSERT INTO file VALUES(1, 1, NULL);
INSERT INTO file VALUES(2, 1, NULL);
INSERT INTO file VALUES(3, 2, NULL);

EXPLAIN
SELECT t1.id, t1.name,
IF ( 
        EXISTS ( 
                SELECT id
                FROM file WHERE document_id = t1.id
                ),
        "file_exists", "no file"
)
FROM document AS t1;

EXPLAIN
SELECT t1.id, t1.name,
IF ( 
        EXISTS ( 
                SELECT 1
                FROM file WHERE document_id = t1.id
                ),
        "file_exists", "no file"
)
FROM document AS t1;

Plans are the same on newer 4.1.14 version (for InnoDB tables at least):

mysql> SELECT t1.id, t1.name,
    ->   IF (EXISTS (SELECT id FROM file WHERE document_id = t1.id), 'file_exists', 'no file') as existance
    -> FROM document AS t1;
+----+--------+-------------+
| id | name   | existance   |
+----+--------+-------------+
|  1 | test   | file_exists |
|  2 | test 2 | file_exists |
|  3 | test 3 | no file     |
+----+--------+-------------+
3 rows in set (0.00 sec)

mysql> explain SELECT t1.id, t1.name,
    ->   IF (EXISTS (SELECT id FROM file WHERE document_id = t1.id), 'file_exists', 'no file') as existance
    -> FROM document AS t1;
+----+--------------------+-------+------+---------------+-------------+--------
-+------------+------+-------------+
| id | select_type        | table | type | possible_keys | key         | key_len
 | ref        | rows | Extra       |
+----+--------------------+-------+------+---------------+-------------+--------
-+------------+------+-------------+
|  1 | PRIMARY            | t1    | ALL  | NULL          | NULL        |    NULL
 | NULL       |    3 |             |
|  2 | DEPENDENT SUBQUERY | file  | ref  | document_id   | document_id |       2
 | test.t1.id |    1 | Using index |
+----+--------------------+-------+------+---------------+-------------+--------
-+------------+------+-------------+
2 rows in set (0.03 sec)

mysql> explain SELECT t1.id, t1.name,
    ->   IF (EXISTS (SELECT 1 FROM file WHERE document_id = t1.id), 'file_exists
', 'no file') as existance
    -> FROM document AS t1;
+----+--------------------+-------+------+---------------+-------------+--------
-+------------+------+-------------+
| id | select_type        | table | type | possible_keys | key         | key_len
 | ref        | rows | Extra       |
+----+--------------------+-------+------+---------------+-------------+--------
-+------------+------+-------------+
|  1 | PRIMARY            | t1    | ALL  | NULL          | NULL        |    NULL
 | NULL       |    3 |             |
|  2 | DEPENDENT SUBQUERY | file  | ref  | document_id   | document_id |       2
 | test.t1.id |    1 | Using index |
+----+--------------------+-------+------+---------------+-------------+--------
-+------------+------+-------------+
2 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.00 sec)

Please, try the newer version - 4.1.11 is a bit dated...

First off - don't know if severity should be set to 5 because that option is not explained in the list of severity levels.

Just checked the issue again and I was able to reproduce it on 5.0.18-nt.

Here's the test:

mysql> CREATE TABLE document (
    ->   id smallint(5) unsigned NOT NULL auto_increment,
    ->   name varchar(250) NOT NULL default '',
    ->   PRIMARY KEY  (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> CREATE TABLE file (
    ->   id smallint(5) unsigned NOT NULL auto_increment,
    ->   document_id smallint(5) unsigned NOT NULL default '0',
    ->   file longblob,
    ->   PRIMARY KEY  (id),
    ->   KEY document_id (document_id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> INSERT INTO document VALUES(1, 'test');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO document VALUES(2, 'test 2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO document VALUES(3, 'test 3');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO file VALUES(1, 1, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO file VALUES(2, 1, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO file VALUES(3, 2, NULL);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> EXPLAIN
    -> SELECT t1.id, t1.name,
    -> IF (
    ->         EXISTS (
    ->                 SELECT id
    ->                 FROM file WHERE document_id = t1.id
    ->                 ),
    ->         "file_exists", "no file"
    -> )
    -> FROM document AS t1 \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra:
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: file
         type: ref
possible_keys: document_id
          key: document_id
      key_len: 2
          ref: bug.t1.id
         rows: 2
        Extra:
2 rows in set (0.00 sec)

mysql>
mysql> EXPLAIN
    -> SELECT t1.id, t1.name,
    -> IF (
    ->         EXISTS (
    ->                 SELECT 1
    ->                 FROM file WHERE document_id = t1.id
    ->                 ),
    ->         "file_exists", "no file"
    -> )
    -> FROM document AS t1 \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra:
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: file
         type: ref
possible_keys: document_id
          key: document_id
      key_len: 2
          ref: bug.t1.id
         rows: 2
        Extra: Using index
2 rows in set (0.00 sec)

mysql>
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.0.18-nt |
+-----------+
1 row in set (0.00 sec)

mysql>

Just checked and the bug still exists at least with 5.0.45 on Mac OS.

Please try to reproduce with MyISAM tables.