Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Duplicate
-
5.5.38, 10.0.11
-
None
-
This example was done on Windows 7
Description
Here is an example, the table:
CREATE TABLE `myp` ( |
`CustomerID` varchar(5) DEFAULT NULL, |
`CompanyName` varchar(40) DEFAULT NULL, |
`ContactName` varchar(30) DEFAULT NULL, |
`ContactTitle` varchar(30) DEFAULT NULL, |
`Address` varchar(60) DEFAULT NULL, |
`City` varchar(15) DEFAULT NULL, |
`Region` varchar(15) DEFAULT NULL, |
`PostalCode` varchar(10) DEFAULT NULL, |
`Country` varchar(15) NOT NULL, |
`Phone` varchar(24) DEFAULT NULL, |
`Fax` varchar(24) DEFAULT NULL |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
PARTITION BY LIST COLUMNS(Country) |
(PARTITION p1 VALUES IN ('Germany','Austria','Switzerland','Poland'), |
PARTITION p2 VALUES IN ('USA','Canada','Mexico'), |
PARTITION p3 VALUES IN ('Spain','Portugal','Italy'), |
PARTITION p4 VALUES IN ('UK','Ireland'), |
PARTITION p5 VALUES IN ('France','Belgium'), |
PARTITION p6 VALUES IN ('Sweden','Finland','Denmark','Norway'), |
PARTITION p7 VALUES IN ('Venezuela','Argentina','Brazil'); |
If it is populated by:
INSERT INTO myp SELECT * FROM customers; |
All works well (customers is a copy of the well known MS Access sample table).
It can be verified by:
SELECT partition_name, table_rows FROM information_schema.partitions WHERE table_name = 'myp'; |
This returns:
| partition_name | table_rows |
|---|---|
| p1 | 16 |
| p2 | 21 |
| p3 | 10 |
| p4 | 8 |
| p5 | 13 |
| p6 | 7 |
| p7 | 16 |
It is also possible to check that the myp table contains the customers data:
SELECT * FROM myp; |
SELECT CustomerID, City, Country FROM myp LIMIT 16, 10; |
The last query returning:
| CustomerID | City | Country |
|---|---|---|
| ANATR | México D.F. | Mexico |
| ANTON | México D.F. | Mexico |
| BOTTM | Tsawassen | Canada |
| CENTC | México D.F. | Mexico |
| GREAL | Eugene | USA |
| HUNGC | Elgin | USA |
| LAUGB | Vancouver | Canada |
| LAZYK | Walla Walla | USA |
| LETSS | San Francisco | USA |
| LONEP | Portland | USA |
Therefore, all seems right so far until we execute the query:
SELECT * FROM myp WHERE Country = 'USA'; |
It returns "0 rows selected in 0.00 sec"!!
Trying to understand, the query
EXPLAIN PARTITIONS SELECT * FROM myp WHERE Country = 'USA'; |
Returns:
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | <null> | <null> | <null> | <null> | <null> | <null> | <null> | 0 | Impossible WHERE noticed after reading const tables |
This is a critical bug, one that returns incorrect results.
