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

LP:1007981 - Wrong result for a hash index look-up if the index is unique and the key is NULL

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      The MySQL reference (http://dev.mysql.com/doc/refman/5.0/en/create-index.html) says:
      A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

      According to the above a look-up into a unique index over a nullable column may return several records if the key value is null.
      However iMariaDB 5.1/5.2/5.3/5.5 always returns not more than one row when using a null key to perform a look-up into an unique hash index. This can be seen from the following:

      MariaDB [test]> CREATE TABLE t1
      -> (
      -> pk INT PRIMARY KEY,
      -> val INT,
      -> UNIQUE KEY USING HASH(val)
      -> ) ENGINE=MEMORY;
      Query OK, 0 rows affected (0.01 sec)

      MariaDB [test]> INSERT INTO t1 VALUES (1, NULL);
      Query OK, 1 row affected (0.00 sec)

      MariaDB [test]> INSERT INTO t1 VALUES (2, NULL);
      Query OK, 1 row affected (0.00 sec)

      MariaDB [test]> INSERT INTO t1 VALUES (3, 1);
      Query OK, 1 row affected (0.00 sec)

      MariaDB [test]> INSERT INTO t1 VALUES (4, NULL);
      Query OK, 1 row affected (0.00 sec)

      MariaDB [test]> EXPLAIN SELECT * FROM t1 WHERE val IS NULL;
      --------------------------------------------------------------------+

      id select_type table type possible_keys key key_len ref rows Extra

      --------------------------------------------------------------------+

      1 SIMPLE t1 ref val val 5 const 1 Using where

      --------------------------------------------------------------------+
      1 row in set (0.01 sec)

      MariaDB [test]> SELECT * FROM t1 WHERE val IS NULL;
      --------+

      pk val

      --------+

      4 NULL

      --------+
      1 row in set (0.00 sec)

      MariaDB [test]> SELECT * FROM t1;
      --------+

      pk val

      --------+

      1 NULL
      2 NULL
      3 1
      4 NULL

      --------+
      4 rows in set (0.00 sec)

      (see also http://bugs.mysql.com/bug.php?id=44771)

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            ratzpo Rasmus Johansson added a comment -

            Launchpad bug id: 1007981

            Show
            ratzpo Rasmus Johansson added a comment - Launchpad bug id: 1007981

              People

              • Assignee:
                sanja Oleksandr Byelkin
                Reporter:
                igor Igor Babaev
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: