[MDEV-6322] The PARTITION engine can return wrong query results Created: 2014-06-10  Updated: 2014-07-24  Resolved: 2014-07-23

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.38, 10.0.11
Fix Version/s: 5.5.39, 10.0.13

Type: Bug Priority: Critical
Reporter: Olivier Bertrand Assignee: Sergei Petrunia
Resolution: Duplicate Votes: 0
Labels: plugins, upstream
Environment:

This example was done on Windows 7

Issue Links:
Duplicate
duplicates MDEV-6240 Wrong "Impossible where" with LIST pa... Closed
Relates
relates to MDEV-5271 Support engine-defined attributes per... Closed

 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.

 Comments   
Comment by Elena Stepanova [ 2014-06-10 ]

It is likely to be a duplicate of MDEV-6240, assigning to psergey to confirm (and maybe re-check after MDEV-6240 is fixed). Here is the test case for this bug report:

--source include/have_partition.inc
 
 
 
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')
 
);
 
 
 
INSERT INTO myp (CustomerID, City, Country) VALUES 
('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');
 
 
 
SELECT * FROM myp WHERE Country = 'USA';

Comment by Olivier Bertrand [ 2014-06-10 ]

Please see my comment in MDEV-6240

Comment by Sergei Petrunia [ 2014-07-23 ]

The same issue as MDEV-6240, closing as duplicate.

Generated at Thu Feb 08 07:11:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.