MySQL Bugs: #38958: 'SELECT DISTINCT ... EXISTS' returns wrong result set, if used with indexs

Bug #38958	'SELECT DISTINCT ... EXISTS' returns wrong result set, if used with indexs
Submitted:	22 Aug 2008 10:56	Modified:	27 Aug 2008 15:31
Reporter:	Gregor Weckbecker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	MySQL 5.0.51a-3ubuntu5.1	OS:	Linux (ubuntu)
Assigned to:		CPU Architecture:	Any

Description:
There is some mistake while handling a query of the form: 'SELECT DISTINCT ... WHERE EXISTS ...). If follow the description below, you might expect that the following query 

SELECT distinct taggable_id AS tagbl_id, taggable_type AS tagbl_type 
FROM `taggings` 
WHERE (
(EXISTS 
(SELECT taggable_id,taggable_type 
FROM taggings 
WHERE tag_id = 2 AND taggable_id = tagbl_id AND taggable_type = tagbl_type)
)
);

with the given data return one row, but it returns zero. If you drop the index you get the expected result. If you rephrase the query to 

SELECT * FROM (
SELECT taggable_id AS tagbl_id, taggable_type AS tagbl_type 
FROM `taggings` 
WHERE (
(EXISTS 
(SELECT taggable_id,taggable_type 
FROM taggings 
WHERE tag_id = 2 AND taggable_id = tagbl_id AND taggable_type = tagbl_type)
)
);
)

it also works fine!

How to repeat:
Create the Table:

CREATE TABLE `taggings` (
  `id` int(11) NOT NULL auto_increment,
  `tag_id` int(11) default NULL,
  `taggable_id` int(11) default NULL,
  `taggable_type` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `tag_id` (`tag_id`,`taggable_id`,`taggable_type`)
) ENGINE=MyISAM;

Insert some data: 

INSERT INTO `taggings` (tag_id, taggable_id, taggable_type) VALUES (1,1,'ContentItem'),(2,1,'ContentItem');

Execute the query: 

SELECT DISTINCT  taggable_id, taggable_type, taggable_id AS tagbl_id, taggable_type AS tagbl_type FROM `taggings` WHERE ((EXISTS (SELECT taggable_id,taggable_type FROM taggings WHERE tag_id = 2 AND taggable_id = tagbl_id AND taggable_type = tagbl_type)));

Drop the index: 

ALTER TABLE taggings DROP INDEX `tag_id`

and query again!

Thank you for the bug report. Could you please try with the latest released version 5.0.67. Thanks in advance.

I've tried the newest mysql - release: It simply rejects the query with the message: 

mysql> SELECT distinct taggable_id AS tagbl_id, taggable_type AS tagbl_type 
    -> FROM `taggings` 
    -> WHERE (
    -> (EXISTS 
    -> (SELECT taggable_id,taggable_type 
    -> FROM taggings 
    -> WHERE tag_id = 2 AND taggable_id = tagbl_id AND taggable_type = tagbl_type)
    -> )
    -> );
ERROR 1054 (42S22): Unknown column 'tagbl_id' in 'where clause'

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Additional Info:

The initial bug report was a bug, but MySQL behaviour is correct with the latest version.

Statements of the form
SELECT select_list FROM from_tables WHERE where_conditions;
are resolved in this order: from_tables, where_conditions,
select_list. So at the time that a DBMS evaluates where_conditions
it knows nothing about names that will be established with
select_list. For example:
CREATE TABLE t (s1 INT);
SELECT s1 AS s2 FROM t WHERE s2 = 5;
gives the same error.