Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6322

The PARTITION engine can return wrong query results

        Details

        • Type: Bug
        • Status: Closed
        • 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:
        • 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.

            Gliffy Diagrams

              Attachments

                Issue Links

                duplicates

                Bug - A problem which impairs or prevents the functions of the product. MDEV-6240 Wrong "Impossible where" with LIST partitioning

                • Major - Major loss of function.
                • Closed
                relates to

                Task - A task that needs to be done. MDEV-5271 engine attributes per partition

                • Major - Major loss of function.
                • Open
                links to

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

                  Activity

                  Ascending order - Click to sort in descending order
                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  It is likely to be a duplicate of MDEV-6240, assigning to Sergei Petrunia 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';
                  Show
                  elenst Elena Stepanova added a comment - It is likely to be a duplicate of MDEV-6240 , assigning to Sergei Petrunia 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';
                  Hide
                  Permalink
                  bertrandop Olivier Bertrand added a comment -

                  Please see my comment in MDEV-6240

                  Show
                  bertrandop Olivier Bertrand added a comment - Please see my comment in MDEV-6240
                  Hide
                  Permalink
                  psergey Sergei Petrunia added a comment -

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

                  Show
                  psergey Sergei Petrunia added a comment - The same issue as MDEV-6240 , closing as duplicate.

                    People

                    • Assignee:
                      psergey Sergei Petrunia
                      Reporter:
                      bertrandop Olivier Bertrand
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      4 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: