Bug #43809 Select Distinct with Where clause generated error 1178 (42000)
Submitted: 23 Mar 2009 19:12 Modified: 24 Mar 2009 13:56
Reporter: Edward Huang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S1 (Critical)
Version:Community 5.1.30 OS:Any
Assigned to: CPU Architecture:Any

[23 Mar 2009 19:12] Edward Huang
Description:
Perform a "SELECT DISTINCT field1 FROM TEST WHERE field1 <= 10000;" produced "ERROR 1178 (42000): The storage engine for the table doesn't support this functionality"

The table is using INNODB engine with partition by hash on field1 with 2 partitions.  

When this table contained only 2 records, the select statement returned the expected result.  As soon as the third record is added, the select statement would fail with the above mentioned error condition.

I've tried this on Community edition 5.1.30 on both Linux and Windows platform and got the same result.

How to repeat:
CREATE TABLE  TEST (
  field1 int NOT NULL,
  KEY index1 (field1)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin /*!50100 PARTITION BY HASH (field1) PARTITIONS 2  */;

INSERT INTO TEST values(1);
INSERT INTO TEST values(2);

SELECT DISTINCT field1 FROM TEST WHERE field1 <= 10000;" 

INSERT INTO TEST values(3);

SELECT DISTINCT field1 FROM TEST WHERE field1 <= 10000;"
[23 Mar 2009 19:18] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior. Additionally version 5.1.30 is old. Please try with current version 5.1.32 and if problem still exists provide error log file.
[23 Mar 2009 19:19] Sveta Smirnova
Additional note: in my environment I get wrong results without error message with version 5.1.30 and correct result with version 5.1.32
[24 Mar 2009 13:21] Edward Huang
In 5.1.30 if you run this test using mysql command line, you will see the error message.

I've just tested Community Edition 5.1.32 and confirmed that the problem is fixed.

Switching over to 5.1.32, do we have to rebuild databases built with 5.1.30?
[24 Mar 2009 13:54] Miguel Solorzano
Thank you for the bug report. Fixed in 5.1.32.
[24 Mar 2009 13:56] Edward Huang
Do I need to rebuild the databases moving from 5.1.30 to 5.1.32?
[24 Mar 2009 14:45] Miguel Solorzano
Please read the changes done since the version you have at:

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-x.html

Thanks in advance.