[MDEV-6322] The PARTITION engine can return wrong query results - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Duplicate
Affects Version/s: 5.5.38, 10.0.11
Fix Version/s: 5.5.39, 10.0.13
Component/s: None
Labels:
- plugins
- upstream
Environment:
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.

Attachments

Issue Links

duplicates

MDEV-6240 Wrong "Impossible where" with LIST partitioning

Closed

relates to

MDEV-5271 Support engine-defined attributes per partition

Closed

links to

Bug #72803 - Wrong "Impossible where" with LIST partitioning

Activity

Ascending order - Click to sort in descending order

Olivier Bertrand created issue - 2014-06-10 13:25

Olivier Bertrand made changes - 2014-06-10 13:37

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-5271~~ [ ~~MDEV-5271~~ ]

Elena Stepanova made changes - 2014-06-10 14:22

Assignee

Elena Stepanova [ elenst ]

Elena Stepanova added a comment - 2014-06-10 15: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';

Elena Stepanova added a comment - 2014-06-10 15: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' ;

Elena Stepanova made changes - 2014-06-10 15:10

Link

This issue duplicates ~~MDEV-6240~~ [ ~~MDEV-6240~~ ]

Elena Stepanova made changes - 2014-06-10 15:10

Fix Version/s		10.0.12 [ 15201 ]
Fix Version/s		5.5.39 [ 15800 ]
Affects Version/s		5.5.38 [ 15400 ]
Affects Version/s		10.0.11 [ 15200 ]
Affects Version/s	10.0.10-galera [ 15500 ]
Assignee	Elena Stepanova [ elenst ]	Sergei Petrunia [ psergey ]
Labels	plugins	plugins upstream

Elena Stepanova made changes - 2014-06-10 15:11

Remote Link

This issue links to "Bug #72803 - Wrong "Impossible where" with LIST partitioning (Web Link)" [ 17800 ]

Olivier Bertrand added a comment - 2014-06-10 17:11

Please see my comment in ~~MDEV-6240~~

Olivier Bertrand added a comment - 2014-06-10 17:11 Please see my comment in MDEV-6240

Sergei Golubchik made changes - 2014-06-13 15:06

Workflow

defaullt [ 42307 ]

MariaDB v2 [ 42736 ]

Sergei Golubchik made changes - 2014-06-16 13:27

Fix Version/s		10.0.13 [ 16000 ]
Fix Version/s	10.0.12 [ 15201 ]

Sergei Petrunia added a comment - 2014-07-23 21:50

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

Sergei Petrunia added a comment - 2014-07-23 21:50 The same issue as MDEV-6240 , closing as duplicate.

Sergei Petrunia made changes - 2014-07-23 21:50

Fix Version/s		5.5.39 [ 16301 ]
Fix Version/s	5.5 [ 15800 ]
Resolution		Duplicate [ 3 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2014-07-24 10:04

Fix Version/s		10.0.13 [ 16300 ]
Fix Version/s	10.0 [ 16000 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 42736 ]

MariaDB v3 [ 63136 ]

Sergei Golubchik made changes - 2021-12-06 21:40

Workflow

MariaDB v3 [ 63136 ]

MariaDB v4 [ 147948 ]

People

Assignee:: Sergei Petrunia

Reporter:: Olivier Bertrand

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2014-06-10 13:25

Updated:: 2014-07-24 10:04

Resolved:: 2014-07-23 21:50

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration