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 (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:
    • 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

            Activity

              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: